并行指的是？
两面数据是完全一样的嘛，条件的返回数据是多少
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差不多 远程计划稍微会慢点 还有从你的表结构信息创建的索引 索引冗余了
是的，我发现了，我去对应表的节点，单独查就比较快了！！列存是我刚加的，快一点。
如何有效避免走远程计划呢，我这是分布式的
单独的一个 查询sql ，解析其执行计划居然是个远程的，这个现象也不对。可以退出会话重新登录一下，然后再explain 一下。
早期OB 版本分析解析执行计划就容易有这个问题，被你会话当前的环境影响了。
此外，如果还有疑点，那就直接跑sql，看 show trace 里执行路径是否有远程。以及单独开窗口去抓取sql的实际执行计划，看实际执行计划是不是远程执行计划。如果是就很不合理了。
你直连到这个节点查询一下 再发一下执行计划 你的架构是什么样的 发一下
你查询一下该表的leader信息
SELECT * FROM oceanbase.DBA_OB_TABLE_LOCATIONS
WHERE DATABASE_NAME=‘test’ and TABLE_NAME=‘t1’ and ROLE=‘LEADER’ and TABLE_TYPE=‘USER TABLE’;
这个租户分布3个uint
昨天有查询这个
mysql> SELECT * FROM oceanbase.DBA_OB_TABLE_LOCATIONS
WHERE DATABASE_NAME='stock' and TABLE_NAME='stock_cbr' and ROLE='LEADER' and TABLE_TYPE='USER TABLE';
+---------------+------------+----------+------------+----------------+-------------------+------------+---------------+-----------+-------+-------+---------------+----------+--------+--------------+-----------------+-----------+-----------------+---------------+----------+
| DATABASE_NAME | TABLE_NAME | TABLE_ID | TABLE_TYPE | PARTITION_NAME | SUBPARTITION_NAME | INDEX_NAME | DATA_TABLE_ID | TABLET_ID | LS_ID | ZONE | SVR_IP | SVR_PORT | ROLE | REPLICA_TYPE | DUPLICATE_SCOPE | OBJECT_ID | TABLEGROUP_NAME | TABLEGROUP_ID | SHARDING |
+---------------+------------+----------+------------+----------------+-------------------+------------+---------------+-----------+-------+-------+---------------+----------+--------+--------------+-----------------+-----------+-----------------+---------------+----------+
| stock | stock_cbr | 500065 | USER TABLE | NULL | NULL | NULL | NULL | 200029 | 1003 | zone1 | 192.168.10.45 | 2882 | LEADER | FULL | NONE | 500065 | NULL | NULL | NULL |
+---------------+------------+----------+------------+----------------+-------------------+------------+---------------+-----------+-------+-------+---------------+----------+--------+--------------+-----------------+-----------+-----------------+---------------+----------+
1 row in set (0.03 sec)
你是通过odp执行的语句么？还是在某一个节点上直连查询的？
我就navicat连接对应的节点，查的
那你的navicat是通过直连的 还是odp呢 默认情况下 直连是2881端口 odp是2883端口 你要是改了端口就是改了以后的端口
直连的2881哦
好的 那你通过obclient直连这个节点192.168.10.45 执行这个语句 看看 发一下explain extended sql 执行计划 再看看
这个就是之前的发的那个了。我走obd的2883吧，感谢你的回答
好的 直连查询走本地的执行计划 耗时和mysql几乎一样的 如果是远程的执行计划 耗时会慢点
是的，直连性能很好