【 使用环境 】测试环境
【 OB or 其他组件 】
【 使用版本 】4.3.2
【问题描述】我同样的机器环境下，把mysql的表和数据复制到OB上，测试两者查询性能，我就随便查了下20000多条数据，发现OB慢过mysql
explain发现，ob这里没有走索引，全部扫描了？
查看文档好像说什么ob索引最大16K之类的，请问什么情况这是？OB的索引机制是全新的吗？如果重新建索引我该怎么修改优化查询效率，机制和mysql不一样了？
1 个赞
OB这边光看执行计划确实是没走索引，而且应该是一个远程执行计划。。MySQL那边是走了索引的？可以对比下看看
1 个赞
如果是刚导过来的数据 数据量比较大 可以先做一次手动合并，在手动执行一次统计信息
–执行手动合并
ALTER SYSTEM MAJOR FREEZE;
–查看租户合并状态
select * from CDB_OB_MAJOR_COMPACTION where TENANT_ID =’’;
–收集指定 Schema 中所有对象的统计信息
CALL DBMS_STATS.GATHER_SCHEMA_STATS (‘zhejiang’, degree=>64,no_invalidate=>TRUE);
–执行计划
explain extend sql
执行完 发一下 执行计划的文本
1 个赞
mysql那边走索引的
1 个赞
合并租户，执行了收集。还是一样，其实这是昨天迁移的，昨夜凌晨自动执行了合并租户我看记录
看起来还是不走索引
1 个赞
如果我把数据量弄小一点，就走索引了。。。
1 个赞
麻烦你把表结构信息信息发一下 还有详细的执行计划弄一个文本 发出来 整个看一下
1 个赞
表结构，3个索引
CREATE TABLE `stock_cbr` (
`id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT 'id,自动增长',
`code` varchar(12) DEFAULT NULL COMMENT '股票代码',
`name` varchar(12) DEFAULT NULL COMMENT '股票名字',
`open` decimal(10,2) DEFAULT NULL COMMENT '开盘价',
`high` decimal(10,2) DEFAULT NULL COMMENT '最高价',
`low` decimal(10,2) DEFAULT NULL COMMENT '最低价',
`close` decimal(10,2) DEFAULT NULL COMMENT '收盘价',
`pre_close` decimal(10,2) DEFAULT NULL COMMENT '昨收',
`first_buy` decimal(10,2) DEFAULT NULL COMMENT '买一',
`first_sell` decimal(10,2) DEFAULT NULL COMMENT '卖一',
`limit_up_price` decimal(10,2) DEFAULT NULL COMMENT '涨停价',
`limit_down_price` decimal(10,2) DEFAULT NULL COMMENT '跌停价',
`volume` decimal(10,0) DEFAULT NULL COMMENT '成交量(手)',
`amount` decimal(10,2) DEFAULT NULL COMMENT '成交额(万)',
`price_change` decimal(10,2) DEFAULT NULL COMMENT '价格变动',
`change_percent` decimal(10,2) DEFAULT NULL COMMENT '涨跌幅(%)',
`pb` decimal(10,2) DEFAULT NULL COMMENT '市净率(%)',
`pe` decimal(10,2) DEFAULT NULL COMMENT '市盈率(%)',
`mkt_cap` decimal(10,4) DEFAULT NULL COMMENT '总市值(单位亿)',
`nmc` decimal(10,4) DEFAULT NULL COMMENT '流通市值(单位亿)',
`tun` decimal(10,2) DEFAULT NULL COMMENT '换手率(%)',
`per` decimal(10,2) DEFAULT NULL COMMENT 'per(未知)',
`per_d` decimal(10,2) DEFAULT NULL COMMENT 'per_d(未知)',
`nta` decimal(10,2) DEFAULT NULL COMMENT 'nta',
`quantity` decimal(10,2) DEFAULT NULL COMMENT '量比',
`deal_date` date DEFAULT NULL COMMENT '日期',
PRIMARY KEY (`id`),
KEY `code_index` (`code`) BLOCK_SIZE 16384 LOCAL,
KEY `dealdate` (`deal_date`) BLOCK_SIZE 16384 LOCAL,
KEY `index_code_date` (`code`, `deal_date`) BLOCK_SIZE 16384 LOCAL
)
然后我执行了一个简单查询
SELECT * from stock_cbr WHERE
deal_date BETWEEN '2024-08-15' AND '2024-08-16'
1 个赞
这个执行计划太简单了 没法看全部的信息 麻烦再提供一下
–执行计划
explain extend sql
执行完 发一下 执行计划的文本
1 个赞
==========================================================
|ID|OPERATOR |NAME |EST.ROWS|EST.TIME(us)|
----------------------------------------------------------
|0 |EXCHANGE IN REMOTE | |10713 |148690 |
|1 |└─EXCHANGE OUT REMOTE| |10713 |103794 |
|2 | └─TABLE FULL SCAN |stock_cbr|10713 |2744 |
==========================================================
Outputs & filters:
-------------------------------------
0 - output([stock_cbr.id], [stock_cbr.code], [stock_cbr.name], [stock_cbr.open], [stock_cbr.high], [stock_cbr.low], [stock_cbr.close], [stock_cbr.pre_close],
[stock_cbr.first_buy], [stock_cbr.first_sell], [stock_cbr.limit_up_price], [stock_cbr.limit_down_price], [stock_cbr.volume], [stock_cbr.amount], [stock_cbr.price_change],
[stock_cbr.change_percent], [stock_cbr.pb], [stock_cbr.pe], [stock_cbr.mkt_cap], [stock_cbr.nmc], [stock_cbr.tun], [stock_cbr.per], [stock_cbr.per_d],
[stock_cbr.nta], [stock_cbr.quantity], [stock_cbr.deal_date]), filter(nil)
1 - output([stock_cbr.id], [stock_cbr.code], [stock_cbr.name], [stock_cbr.open], [stock_cbr.high], [stock_cbr.low], [stock_cbr.close], [stock_cbr.pre_close],
[stock_cbr.first_buy], [stock_cbr.first_sell], [stock_cbr.limit_up_price], [stock_cbr.limit_down_price], [stock_cbr.volume], [stock_cbr.amount], [stock_cbr.price_change],
[stock_cbr.change_percent], [stock_cbr.pb], [stock_cbr.pe], [stock_cbr.mkt_cap], [stock_cbr.nmc], [stock_cbr.tun], [stock_cbr.per], [stock_cbr.per_d],
[stock_cbr.nta], [stock_cbr.quantity], [stock_cbr.deal_date]), filter(nil)
2 - output([stock_cbr.id], [stock_cbr.code], [stock_cbr.name], [stock_cbr.open], [stock_cbr.high], [stock_cbr.low], [stock_cbr.close], [stock_cbr.pre_close],
[stock_cbr.first_buy], [stock_cbr.first_sell], [stock_cbr.limit_up_price], [stock_cbr.limit_down_price], [stock_cbr.volume], [stock_cbr.amount], [stock_cbr.price_change],
[stock_cbr.change_percent], [stock_cbr.pb], [stock_cbr.pe], [stock_cbr.mkt_cap], [stock_cbr.nmc], [stock_cbr.tun], [stock_cbr.per], [stock_cbr.per_d],
[stock_cbr.nta], [stock_cbr.quantity], [stock_cbr.deal_date]), filter([cast(stock_cbr.deal_date, DATETIME(-1, -1)) >= INTERNAL_FUNCTION('2024-08-15', 114,
17)], [cast(stock_cbr.deal_date, DATETIME(-1, -1)) <= INTERNAL_FUNCTION('2024-08-16', 112, 17)]), rowset=256
access([stock_cbr.id], [stock_cbr.deal_date], [stock_cbr.code], [stock_cbr.name], [stock_cbr.open], [stock_cbr.high], [stock_cbr.low], [stock_cbr.close],
[stock_cbr.pre_close], [stock_cbr.first_buy], [stock_cbr.first_sell], [stock_cbr.limit_up_price], [stock_cbr.limit_down_price], [stock_cbr.volume], [stock_cbr.amount],
[stock_cbr.price_change], [stock_cbr.change_percent], [stock_cbr.pb], [stock_cbr.pe], [stock_cbr.mkt_cap], [stock_cbr.nmc], [stock_cbr.tun], [stock_cbr.per],
[stock_cbr.per_d], [stock_cbr.nta], [stock_cbr.quantity]), partitions(p0)
is_index_back=false, is_global_index=false, filter_before_indexback[false,false],
range_key([stock_cbr.id]), range(MIN ; MAX)always true
是这个吗
1 个赞
你的问题 不仅仅是 OB 的问题，还是索引原理理解的问题。
索引的最佳场景是大海捞针，从大量记录中定位少数记录。
如果那些记录在表的数据块上是连续的，一个索引范围扫描加连续几个数据块的扫描数据就返回来了。这是最好的结果。
如果那些记录在数据块上不是连续的，索引里每定位一笔记录就要去表里取一下，这个叫回表。然后继续根据索引里下一个索引记录去表的数据块里取一下。如果连续索引对应表的记录位置确实彼此交叉重复，这就是个坏的场景。
根据索引记录定位表数据块记录效率虽然很高（延时很低），但是大量这种取数据操作总的耗时会很大。 多大量就会发生转变呢，这个不确定。
索引有时候走索引可能会适得其反，还不如一个全表扫描就把结果捞回来的快。
像上面这种时间列上的范围查询，数据库 sql 引擎在 走索引还是走全表扫描 都有自己的判断规则，都有自己的临界值。这个临界值mysql跟ob的肯定不一样，因为彼此的sql引擎原理、内存原理、存储引擎原理都完全不一样。所以你改变日期范围后，执行计划会变，性能会变。
面对同样的数据量同样的查询，mysql和ob各自做出不同的选择。
当然下一个问题就是既然都会自己选，OB的为什么更慢。那也有多方面原因。一方面上面的执行计划显示 OB 里是个远程执行计划（不过这个信息还是不准的，还要看实际执行计划。姑且认为二者是一致的）。另外一方面没有人说 OB一定比mysql快，也没有人说 OB 一定比mysql慢。所以不要带着这样的观点去看。
当你在 OB上跑sql，应该想的是如何跑的更快。比如说规避远程执行计划，或者用并行。上面测试表数据量也不大，也许第二次执行 ob 就更快了。OB把数据都缓存了，当然mysql也会缓存。不过二者的内存缓存机制又不一样。孰优孰劣，看场景了。看综合的场景。越是简单的场景比对，越缺乏说服力。
sysbench 很简单，benchmarksql tpcc 比较复杂，那个 tpc 组织制定的那个 tpc-c 场景就更加严格复杂。当然，再复杂也必过客户的业务。越复杂，这种对比结果就 越不好解释。因为变量太多了。
索引这个原理在大部分数据库都是通用的。上面的图是从网上oracle 文章中盗用的。
2 个赞
并行指的是？
两面数据是完全一样的嘛，条件的返回数据是多少
1、你这个发的还是不全 这样执行 执行一次数据量大的 执行一次数据量小的 把所有的信息分别粘贴到一个文本上 发一下
explain extended SELECT * from stock_cbr WHERE
deal_date BETWEEN ‘2024-08-15’ AND ‘2024-08-16’;
2、trace_id获取的方式
SET ob_enable_show_trace=‘ON’;
注意：需要在同一个会话中执行
obclient [test]> SELECT * from stock_cbr WHERE
deal_date BETWEEN ‘2024-08-15’ AND ‘2024-08-16’;
obclient [test]> select last_trace_id();
±----------------------------------+
| last_trace_id() |
±----------------------------------+
| YB420BA1CC68-000615A0A8EA6511-0-0 |
±----------------------------------+
1 row in set (0.002 sec)
3、你可以用obdiag 收集执行的信息 贴一下 分析分析
obdiag gather scene run --scene=observer.perf_sql --env “{db_connect=’-hxx -Pxx -uxx -pxx -Dxx’, trace_id=‘xx’}”
obdiag文档
https://www.oceanbase.com/docs/common-obdiag-cn-1000000001102504
应该和远程计划有关，mysql那边的是单机本身，我在ob对应这个表所在的节点那里执行，效率和mysql差不多了
在对应节点执行有走索引吗
mysql> explain extended SELECT * from stock_cbr WHERE
deal_date BETWEEN '2024-08-15' AND '2024-08-16'
-> ;
+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Query Plan |
+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| =============================================================== |
| |ID|OPERATOR |NAME |EST.ROWS|EST.TIME(us)| |
| --------------------------------------------------------------- |
| |0 |EXCHANGE IN REMOTE | |10713 |147619 | |
| |1 |└─EXCHANGE OUT REMOTE | |10713 |102723 | |
| |2 | └─COLUMN TABLE FULL SCAN|stock_cbr|10713 |1672 | |
| =============================================================== |
| Outputs & filters: |
| ------------------------------------- |
| 0 - output([stock_cbr.id(0x7f6ae92255c0)], [stock_cbr.code(0x7f6ae92258f0)], [stock_cbr.name(0x7f6ae9225c20)], [stock_cbr.open(0x7f6ae9225f50)], [stock_cbr.high(0x7f6ae9226280)], |
| [stock_cbr.low(0x7f6ae92265b0)], [stock_cbr.close(0x7f6ae92268e0)], [stock_cbr.pre_close(0x7f6ae9226c10)], [stock_cbr.first_buy(0x7f6ae9226f40)], [stock_cbr.first_sell(0x7f6ae9227270)], |
| [stock_cbr.limit_up_price(0x7f6ae92275a0)], [stock_cbr.limit_down_price(0x7f6ae92278d0)], [stock_cbr.volume(0x7f6ae9227c00)], [stock_cbr.amount(0x7f6ae9227f30)], |
| [stock_cbr.price_change(0x7f6ae9228260)], [stock_cbr.change_percent(0x7f6ae9228590)], [stock_cbr.pb(0x7f6ae922a7d0)], [stock_cbr.pe(0x7f6ae922ab00)], [stock_cbr.mkt_cap(0x7f6ae922ae30)], |
| [stock_cbr.nmc(0x7f6ae922b160)], [stock_cbr.tun(0x7f6ae922b490)], [stock_cbr.per(0x7f6ae922b7c0)], [stock_cbr.per_d(0x7f6ae922baf0)], [stock_cbr.nta(0x7f6ae922be20)], |
| [stock_cbr.quantity(0x7f6ae922c150)], [stock_cbr.deal_date(0x7f6ae9221860)]), filter(nil) |
| 1 - output([stock_cbr.id(0x7f6ae92255c0)], [stock_cbr.code(0x7f6ae92258f0)], [stock_cbr.name(0x7f6ae9225c20)], [stock_cbr.open(0x7f6ae9225f50)], [stock_cbr.high(0x7f6ae9226280)], |
| [stock_cbr.low(0x7f6ae92265b0)], [stock_cbr.close(0x7f6ae92268e0)], [stock_cbr.pre_close(0x7f6ae9226c10)], [stock_cbr.first_buy(0x7f6ae9226f40)], [stock_cbr.first_sell(0x7f6ae9227270)], |
| [stock_cbr.limit_up_price(0x7f6ae92275a0)], [stock_cbr.limit_down_price(0x7f6ae92278d0)], [stock_cbr.volume(0x7f6ae9227c00)], [stock_cbr.amount(0x7f6ae9227f30)], |
| [stock_cbr.price_change(0x7f6ae9228260)], [stock_cbr.change_percent(0x7f6ae9228590)], [stock_cbr.pb(0x7f6ae922a7d0)], [stock_cbr.pe(0x7f6ae922ab00)], [stock_cbr.mkt_cap(0x7f6ae922ae30)], |
| [stock_cbr.nmc(0x7f6ae922b160)], [stock_cbr.tun(0x7f6ae922b490)], [stock_cbr.per(0x7f6ae922b7c0)], [stock_cbr.per_d(0x7f6ae922baf0)], [stock_cbr.nta(0x7f6ae922be20)], |
| [stock_cbr.quantity(0x7f6ae922c150)], [stock_cbr.deal_date(0x7f6ae9221860)]), filter(nil) |
| 2 - output([stock_cbr.id(0x7f6ae92255c0)], [stock_cbr.code(0x7f6ae92258f0)], [stock_cbr.name(0x7f6ae9225c20)], [stock_cbr.open(0x7f6ae9225f50)], [stock_cbr.high(0x7f6ae9226280)], |
| [stock_cbr.low(0x7f6ae92265b0)], [stock_cbr.close(0x7f6ae92268e0)], [stock_cbr.pre_close(0x7f6ae9226c10)], [stock_cbr.first_buy(0x7f6ae9226f40)], [stock_cbr.first_sell(0x7f6ae9227270)], |
| [stock_cbr.limit_up_price(0x7f6ae92275a0)], [stock_cbr.limit_down_price(0x7f6ae92278d0)], [stock_cbr.volume(0x7f6ae9227c00)], [stock_cbr.amount(0x7f6ae9227f30)], |
| [stock_cbr.price_change(0x7f6ae9228260)], [stock_cbr.change_percent(0x7f6ae9228590)], [stock_cbr.pb(0x7f6ae922a7d0)], [stock_cbr.pe(0x7f6ae922ab00)], [stock_cbr.mkt_cap(0x7f6ae922ae30)], |
| [stock_cbr.nmc(0x7f6ae922b160)], [stock_cbr.tun(0x7f6ae922b490)], [stock_cbr.per(0x7f6ae922b7c0)], [stock_cbr.per_d(0x7f6ae922baf0)], [stock_cbr.nta(0x7f6ae922be20)], |
| [stock_cbr.quantity(0x7f6ae922c150)], [stock_cbr.deal_date(0x7f6ae9221860)]), filter([cast(stock_cbr.deal_date(0x7f6ae9221860), DATETIME(-1, -1))(0x7f6ae9221f60) |
| >= INTERNAL_FUNCTION('2024-08-15', 114, 17)(0x7f6ae923a800)(0x7f6ae9220150)], [cast(stock_cbr.deal_date(0x7f6ae9221860), DATETIME(-1, -1))(0x7f6ae9223a90) |
| <= INTERNAL_FUNCTION('2024-08-16', 112, 17)(0x7f6ae923b6a0)(0x7f6ae92208c0)]), rowset=256 |
| access([stock_cbr.id(0x7f6ae92255c0)], [stock_cbr.deal_date(0x7f6ae9221860)], [stock_cbr.code(0x7f6ae92258f0)], [stock_cbr.name(0x7f6ae9225c20)], |
| [stock_cbr.open(0x7f6ae9225f50)], [stock_cbr.high(0x7f6ae9226280)], [stock_cbr.low(0x7f6ae92265b0)], [stock_cbr.close(0x7f6ae92268e0)], [stock_cbr.pre_close(0x7f6ae9226c10)], |
| [stock_cbr.first_buy(0x7f6ae9226f40)], [stock_cbr.first_sell(0x7f6ae9227270)], [stock_cbr.limit_up_price(0x7f6ae92275a0)], [stock_cbr.limit_down_price(0x7f6ae92278d0)], |
| [stock_cbr.volume(0x7f6ae9227c00)], [stock_cbr.amount(0x7f6ae9227f30)], [stock_cbr.price_change(0x7f6ae9228260)], [stock_cbr.change_percent(0x7f6ae9228590)], |
| [stock_cbr.pb(0x7f6ae922a7d0)], [stock_cbr.pe(0x7f6ae922ab00)], [stock_cbr.mkt_cap(0x7f6ae922ae30)], [stock_cbr.nmc(0x7f6ae922b160)], [stock_cbr.tun(0x7f6ae922b490)], |
| [stock_cbr.per(0x7f6ae922b7c0)], [stock_cbr.per_d(0x7f6ae922baf0)], [stock_cbr.nta(0x7f6ae922be20)], [stock_cbr.quantity(0x7f6ae922c150)]), partitions(p0) |
| is_index_back=false, is_global_index=false, filter_before_indexback[false,false], |
| range_key([stock_cbr.id(0x7f6ae92255c0)]), range(MIN ; MAX)always true |
| Used Hint: |
| ------------------------------------- |
| /*+ |
| |
| */ |
| Qb name trace: |
| ------------------------------------- |
| stmt_id:0, stmt_type:T_EXPLAIN |
| stmt_id:1, SEL$1 |
| Outline Data: |
| ------------------------------------- |
| /*+ |
| BEGIN_OUTLINE_DATA |
| FULL(@"SEL$1" "stock"."stock_cbr"@"SEL$1") |
| USE_COLUMN_TABLE(@"SEL$1" "stock"."stock_cbr"@"SEL$1") |
| OPTIMIZER_FEATURES_ENABLE('4.3.2.0') |
| END_OUTLINE_DATA |
| */ |
| Optimization Info: |
| ------------------------------------- |
| stock_cbr: |
| table_rows:91048 |
| physical_range_rows:91048 |
| logical_range_rows:91048 |
| index_back_rows:0 |
| output_rows:910 |
| table_dop:1 |
| dop_method:Table DOP |
| avaiable_index_name:[index_date, index_code_date, stock_cbr] |
| pruned_index_name:[index_code_date] |
| stats version:0 |
| dynamic sampling level:0 |
| estimation method:[DEFAULT, STORAGE] |
| Plan Type: |
| REMOTE |
| Note: |
| Degree of Parallelisim is 1 because of table property |
+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
74 rows in set (0.09 sec)
mysql> select last_trace_id();
+-----------------------------------+
| last_trace_id() |
+-----------------------------------+
| YB42C0A80A2F-0006201D0FBBFA30-0-0 |
+-----------------------------------+
1 row in set (0.04 sec)
没有，一样的，但是快点就是了
从你发的几次执行计划看 都是走的远程执行计划 并不是本地 最后一次你发的 从执行计划看 应该是列存的执行计划 你可以设置一下 走本地执行计划应该和mysql差不多 远程计划稍微会慢点 还有从你的表结构信息创建的索引 索引冗余了
是的，我发现了，我去对应表的节点，单独查就比较快了！！列存是我刚加的，快一点。
如何有效避免走远程计划呢，我这是分布式的