一、引言
MySQL 与TiDB 都是开源数据库里面使用比较广泛的数据库，在日常的使用中，也会习惯性的对一些语句的执行过程和执行效率做下对比，接下来笔者就以一些特定的语句来对比下两款数据库优化器的执行过程和效率
备注:笔者测试语句的orders表数据来源均来自tpch，可以由tiup bench tpch ...导入生成这些数据
测试的TiDB版本为6.5.3,mysql 版本为8.0.30
二、count(distinct) 语句执行效果
假设有这么一条语句,非常简单就是对O_TOTALPRICE 字段进行去重然后聚合(O_TOTALPRICE 字段有索引)
select count(distinct O_TOTALPRICE) from orders;
我们看下在MySQL上的执行计划(O_TOTALPRICE 字段有索引)
mysql> explain select count(distinct O_TOTALPRICE) from orders;
+----+-------------+--------+------------+-------+---------------------------------------------------+--------------------+---------+------+---------+----------+-------------------------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+--------+------------+-------+---------------------------------------------------+--------------------+---------+------+---------+----------+-------------------------------------+
| 1 | SIMPLE | orders | NULL | range | index_O_ORDERDATE_O_TOTALPRICE,index_O_TOTALPRICE | index_O_TOTALPRICE | 7 | NULL | 1462376 | 100.00 | Using index for group-by (scanning) |
+----+-------------+--------+------------+-------+---------------------------------------------------+--------------------+---------+------+---------+----------+-------------------------------------+
1 row in set, 1 warning (0.01 sec)
mysql> explain analyze select count(distinct O_TOTALPRICE) from orders;
+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| EXPLAIN |
+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| -> Aggregate: count(distinct orders.O_TOTALPRICE) (cost=292475.20 rows=1) (actual time=1611.925..1611.926 rows=1 loops=1)
-> Covering index skip scan for deduplication on orders using index_O_TOTALPRICE (cost=146237.60 rows=1462376) (actual time=17.200..1563.704 rows=1464556 loops=1)
|
+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (1.61 sec)
通过上述的执行计划分析，我们可以看到索引的级别是range 级别，并且 Extra 出现了Using index for group-by (scanning)
接下来我们再对比下TiDB的执行计划(O_TOTALPRICE 字段有索引)
mysql> explain select count(distinct O_TOTALPRICE) from orders;
+--------------------------+------------+-----------+------------------------------------------------------+------------------------------------------------------------+
| id | estRows | task | access object | operator info |
+--------------------------+------------+-----------+------------------------------------------------------+------------------------------------------------------------+
| StreamAgg_6 | 1.00 | root | | funcs:count(distinct tpch2.orders.o_totalprice)->Column#10 |
| └─IndexReader_14 | 1498900.00 | root | | index:IndexFullScan_13 |
| └─IndexFullScan_13 | 1498900.00 | cop[tikv] | table:orders, index:index_O_TOTALPRICE(O_TOTALPRICE) | keep order:false |
+--------------------------+------------+-----------+------------------------------------------------------+------------------------------------------------------------+
3 rows in set (0.01 sec)
mysql> explain analyze select count(distinct O_TOTALPRICE) from orders;
+--------------------------+------------+---------+-----------+------------------------------------------------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+------------------------------------------------------------+---------+------+
| id | estRows | actRows | task | access object | execution info | operator info | memory | disk |
+--------------------------+------------+---------+-----------+------------------------------------------------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+------------------------------------------------------------+---------+------+
| StreamAgg_6 | 1.00 | 1 | root | | time:1.32s, loops:2 | funcs:count(distinct tpch2.orders.o_totalprice)->Column#10 | 64.0 MB | N/A |
| └─IndexReader_14 | 1498900.00 | 1498900 | root | | time:764.6ms, loops:1470, cop_task: {num: 56, max: 74.9ms, min: 1.26ms, avg: 30.3ms, p95: 60.3ms, max_proc_keys: 50144, p95_proc_keys: 50144, tot_proc: 728ms, tot_wait: 5ms, rpc_num: 59, rpc_time: 1.7s, copr_cache_hit_ratio: 0.00, distsql_concurrency: 15}, backoff{regionMiss: 2ms} | index:IndexFullScan_13 | 3.83 MB | N/A |
| └─IndexFullScan_13 | 1498900.00 | 1498900 | cop[tikv] | table:orders, index:index_O_TOTALPRICE(O_TOTALPRICE) | tikv_task:{proc max:26ms, min:0s, avg: 12ms, p80:23ms, p95:26ms, iters:1680, tasks:56}, scan_detail: {total_process_keys: 1498900, total_process_keys_size: 68949400, total_keys: 1498956, get_snapshot_time: 6.56ms, rocksdb: {key_skipped_count: 1498900, block: {cache_hit_count: 348, read_count: 1105, read_byte: 9.34 MB, read_time: 6.72ms}}} | keep order:false | N/A | N/A |
+--------------------------+------------+---------+-----------+------------------------------------------------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+------------------------------------------------------------+---------+------+
3 rows in set (1.33 sec)
mysql> select count( O_TOTALPRICE) from orders;
+----------------------+
| count( O_TOTALPRICE) |
+----------------------+
| 1498900 |
+----------------------+
1 row in set (0.51 sec)
我们发现TiDB和MySQL一样虽然说都用到了O_TOTALPRICE 字段的索引，但是TiDB却把整个索引的数据全扫描了一遍了，而MySQL的却只对索引进行range 级别的索引扫描，从这一点上,笔者认为TiDB 这一点上应该可以改进
对于count(distinct O_TOTALPRICE) 这种语句，其实MySQL是有自己的优化策略的，这种优化策略叫做
Loose Index Scan,如果用了Loose Index Scan，那么执行计划会显示 Using index for group-by (scanning)。
其实对于count(distinct O_TOTALPRICE) 这种语句其实本质是要统计这个字段去重后还剩下多少个值，那也就是说在执行过程中如果是遇到不同的值加1就行了，也就是说对于相同的值扫描1个就行了，没必要每个值都进行扫描，而基于此MySQL的Loose Index Scan刚好可以利用这个特性，来达到跳跃扫描的目的，只需要扫描索引的部分数据就可以达到目的。
官方文档对此也有说明:
https://dev.mysql.com/doc/refman/8.0/en/group-by-optimization.html#loose-index-scan
三、count(*) from (.... order by)优化器的改写效果
假设有这么一条语句
select count(*) from (select * from orders order by O_TOTALPRICE)t;
当然这条语句有些地方写的是多余的，count(*) 这种聚合反正只返回一条数据，根本没必要order by xxx，所以直接select count(*) from orders 就可以了，但是这样的语句谁也没法说就一定不会出现，如果出现了那么我们希望在优化器这一层直接把语句改写了，毕竟order by在不能用到索引的情况下,代价还是很昂贵的,下面我们来看下，这2条语句在MySQL和TiDB 上的执行效果、
MySQL上的执行效果
mysql> explain select count(*) from (select * from orders order by O_TOTALPRICE)t;
+----+-------------+--------+------------+-------+---------------+--------------------------------+---------+------+---------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+--------+------------+-------+---------------+--------------------------------+---------+------+---------+----------+-------------+
| 1 | SIMPLE | orders | NULL | index | NULL | index_O_ORDERDATE_O_TOTALPRICE | 10 | NULL | 1139400 | 100.00 | Using index |
+----+-------------+--------+------------+-------+---------------+--------------------------------+---------+------+---------+----------+-------------+
1 row in set, 1 warning (0.01 sec)
mysql> show warnings;
+-------+------+--------------------------------------------------------------------+
| Level | Code | Message |
+-------+------+--------------------------------------------------------------------+
| Note | 1003 | /* select#1 */ select count(0) AS `count(*)` from `tpch2`.`orders` |
+-------+------+--------------------------------------------------------------------+
1 row in set (0.00 sec)
mysql> explain analyze select count(*) from (select * from orders order by O_TOTALPRICE)t;
+-------------------------------------------------------------------------+
| EXPLAIN |
+-------------------------------------------------------------------------+
| -> Count rows in orders (actual time=490.758..490.758 rows=1 loops=1)
|
+-------------------------------------------------------------------------+
1 row in set (0.49 sec)
在MySQL上我们可以看到优化器直接将这条语句改写为了
select count(0) AS `count(*)` from `tpch2`.`orders`
这种改写是符合我们的预期的，避免了由于排序和子查询的带来的额外开销
TiDB上的执行效果
mysql> explain select count(*) from (select * from orders order by O_TOTALPRICE)t;
+----------------------------+------------+-----------+---------------+---------------------------+
| id | estRows | task | access object | operator info |
+----------------------------+------------+-----------+---------------+---------------------------+
| StreamAgg_8 | 1.00 | root | | funcs:count(1)->Column#10 |
| └─Sort_13 | 1498900.00 | root | | tpch2.orders.o_totalprice |
| └─TableReader_12 | 1498900.00 | root | | data:TableFullScan_11 |
| └─TableFullScan_11 | 1498900.00 | cop[tikv] | table:orders | keep order:false |
+----------------------------+------------+-----------+---------------+---------------------------+
4 rows in set (0.00 sec)
mysql> explain analyze select count(*) from (select * from orders order by O_TOTALPRICE)t
-> ;
+----------------------------+------------+---------+-----------+---------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+---------------------------+---------+---------+
| id | estRows | actRows | task | access object | execution info | operator info | memory | disk |
+----------------------------+------------+---------+-----------+---------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+---------------------------+---------+---------+
| StreamAgg_8 | 1.00 | 1 | root | | time:3.22s, loops:2 | funcs:count(1)->Column#10 | 40.3 KB | N/A |
| └─Sort_13 | 1498900.00 | 1498900 | root | | time:3.2s, loops:1465 | tpch2.orders.o_totalprice | 69.1 MB | 0 Bytes |
| └─TableReader_12 | 1498900.00 | 1498900 | root | | time:611ms, loops:1468, cop_task: {num: 55, max: 86.3ms, min: 1.13ms, avg: 26.5ms, p95: 73.3ms, max_proc_keys: 50144, p95_proc_keys: 50144, tot_proc: 864ms, tot_wait: 9ms, rpc_num: 55, rpc_time: 1.45s, copr_cache_hit_ratio: 0.00, distsql_concurrency: 15} | data:TableFullScan_11 | 3.83 MB | N/A |
| └─TableFullScan_11 | 1498900.00 | 1498900 | cop[tikv] | table:orders | tikv_task:{proc max:52ms, min:0s, avg: 14.4ms, p80:25ms, p95:48ms, iters:1682, tasks:55}, scan_detail: {total_process_keys: 1498900, total_process_keys_size: 227047584, total_keys: 1500055, get_snapshot_time: 4.45ms, rocksdb: {key_skipped_count: 1500000, block: {cache_hit_count: 4139}}} | keep order:false | N/A | N/A |
+----------------------------+------------+---------+-----------+---------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+---------------------------+---------+---------+
4 rows in set (3.22 sec)
然后在TiDB上从执行计划来看我们并没有看到这条语句被改写了，执行了额外的排序,笔者个人认为在一些特定的场景下面，TiDB优化器的改写功能还是要增强的
四、index join 的执行差异
语句
select a.C_NAME,b.price from customer a left join (select o_custkey,sum(O_TOTALPRICE) as price from orders group by o_custkey) b on a.C_CUSTKEY=b.O_CUSTKEY where a.C_PHONE='23-768-687-3665';
MySQL的执行计划，可以看到MySQL是可以走index join的
mysql> explain select a.C_NAME,b.price from customer a left join (select o_custkey,sum(O_TOTALPRICE) as price from orders group by o_custkey) b on a.C_CUSTKEY=b.O_CUSTKEY where a.C_PHONE='23-768-687-3665';
+----+-------------+------------+------------+-------+-----------------+-----------------+---------+-------------------+---------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+------------+------------+-------+-----------------+-----------------+---------+-------------------+---------+----------+-------+
| 1 | PRIMARY | a | NULL | ref | index_C_PHONE | index_C_PHONE | 60 | const | 1 | 100.00 | NULL |
| 1 | PRIMARY | <derived2> | NULL | ref | <auto_key0> | <auto_key0> | 8 | tpch2.a.C_CUSTKEY | 10 | 100.00 | NULL |
| 2 | DERIVED | orders | NULL | index | index_o_custkey | index_o_custkey | 8 | NULL | 1411606 | 100.00 | NULL |
+----+-------------+------------+------------+-------+-----------------+-----------------+---------+-------------------+---------+----------+-------+
3 rows in set, 1 warning (0.00 sec)
mysql> explain analyze select a.C_NAME,b.price from customer a left join (select o_custkey,sum(O_TOTALPRICE) as price from orders group by o_custkey) b on a.C_CUSTKEY=b.O_CUSTKEY where a.C_PHONE='23-768-687-3665';
+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| EXPLAIN |
+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| -> Nested loop left join (cost=141163.45 rows=1411606) (actual time=7043.245..7043.250 rows=1 loops=1)
-> Index lookup on a using index_C_PHONE (C_PHONE='23-768-687-3665') (cost=0.35 rows=1) (actual time=0.053..0.057 rows=1 loops=1)
-> Index lookup on b using <auto_key0> (o_custkey=a.C_CUSTKEY) (actual time=7043.189..7043.190 rows=1 loops=1)
-> Materialize (cost=431035.52..431035.52 rows=1411606) (actual time=7043.183..7043.183 rows=99996 loops=1)
-> Group aggregate: sum(orders.O_TOTALPRICE) (cost=289874.92 rows=1411606) (actual time=0.814..6943.477 rows=99996 loops=1)
-> Index scan on orders using index_o_custkey (cost=148714.32 rows=1411606) (actual time=0.809..6781.082 rows=1500000 loops=1)
|
+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (7.05 sec)
TiDB 的语句的执行效果
mysql> explain analyze select a.C_NAME,b.price from customer a left join (select o_custkey,sum(O_TOTALPRICE) as price from orders group by o_custkey) b on a.C_CUSTKEY=b.O_CUSTKEY where a.C_PHONE='23-768-687-3665';
+------------------------------------+-------------+----------+-----------+---------------------------------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+-------------------------------------------------------------------------------------------------------------------------------+----------+---------+
| id | estRows | actRows | task | access object | execution info | operator info | memory | disk |
+------------------------------------+-------------+----------+-----------+---------------------------------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+-------------------------------------------------------------------------------------------------------------------------------+----------+---------+
| HashJoin_13 | 1.01 | 1 | root | | time:45.8s, loops:2, build_hash_table:{total:25.9ms, fetch:25.9ms, build:54.1µs}, probe:{concurrency:5, total:3m49.1s, max:45.8s, probe:304.1ms, fetch:3m48.8s} | left outer join, equal:[eq(tpch.customer.c_custkey, tpch.orders.o_custkey)] | 42.0 KB | 0 Bytes |
| ├─IndexLookUp_31(Build) | 1.01 | 1 | root | | time:25.8ms, loops:2, index_task: {total_time: 7.21ms, fetch_handle: 7.21ms, build: 929ns, wait: 1.27µs}, table_task: {total_time: 16ms, num: 1, concurrency: 5}, next: {wait_index: 9.86ms, wait_table_lookup_build: 0s, wait_table_lookup_resp: 15.9ms} | | 17.8 KB | N/A |
| │ ├─IndexRangeScan_29(Build) | 1.01 | 1 | cop[tikv] | table:a, index:index_C_PHONE(C_PHONE) | time:7.2ms, loops:3, cop_task: {num: 1, max: 7.17ms, proc_keys: 0, rpc_num: 1, rpc_time: 7.16ms, copr_cache_hit_ratio: 1.00, distsql_concurrency: 15}, tikv_task:{time:31ms, loops:1}, scan_detail: {get_snapshot_time: 1.39ms, rocksdb: {block: {}}} | range:["23-768-687-3665","23-768-687-3665"], keep order:false | N/A | N/A |
| │ └─TableRowIDScan_30(Probe) | 1.01 | 1 | cop[tikv] | table:a | time:15.9ms, loops:2, cop_task: {num: 1, max: 9.32ms, proc_keys: 1, rpc_num: 1, rpc_time: 9.3ms, copr_cache_hit_ratio: 0.00, distsql_concurrency: 15}, tikv_task:{time:0s, loops:1}, scan_detail: {total_process_keys: 1, total_process_keys_size: 200, total_keys: 1, get_snapshot_time: 949.9µs, rocksdb: {block: {cache_hit_count: 5}}} | keep order:false | N/A | N/A |
| └─HashAgg_38(Probe) | 1970688.00 | 1999956 | root | | time:45.7s, loops:1955 | group by:tpch.orders.o_custkey, funcs:sum(Column#21)->Column#18, funcs:firstrow(tpch.orders.o_custkey)->tpch.orders.o_custkey | 505.3 MB | 0 Bytes |
| └─TableReader_39 | 1970688.00 | 29566408 | root | | time:1.08s, loops:976, cop_task: {num: 975, max: 1.51s, min: 3.95ms, avg: 295.2ms, p95: 698.2ms, max_proc_keys: 51200, p95_proc_keys: 51200, tot_proc: 1m15.7s, tot_wait: 1.7s, rpc_num: 975, rpc_time: 4m47.8s, copr_cache_hit_ratio: 0.03, distsql_concurrency: 15} | data:HashAgg_32 | 34.7 MB | N/A |
| └─HashAgg_32 | 1970688.00 | 29566408 | cop[tikv] | | tikv_task:{proc max:392ms, min:0s, avg: 74.7ms, p80:116ms, p95:203ms, iters:29273, tasks:975}, scan_detail: {total_process_keys: 29861760, total_process_keys_size: 4535337951, total_keys: 29862710, get_snapshot_time: 318.5ms, rocksdb: {key_skipped_count: 29861760, block: {cache_hit_count: 56091, read_count: 24746, read_byte: 425.6 MB, read_time: 13.3s}}} | group by:tpch.orders.o_custkey, funcs:sum(tpch.orders.o_totalprice)->Column#21 | N/A | N/A |
| └─TableFullScan_37 | 29955968.00 | 29955968 | cop[tikv] | table:orders | tikv_task:{proc max:355ms, min:0s, avg: 65ms, p80:102ms, p95:178ms, iters:29273, tasks:975} | keep order:false | N/A | N/A |
+------------------------------------+-------------+----------+-----------+---------------------------------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+-------------------------------------------------------------------------------------------------------------------------------+----------+---------+
8 rows in set (45.81 sec)
语句来源于帖子:https://asktug.com/t/topic/994770
这个查询即使加了 INL_JOIN 也不生效是因为目前 IndexJoin 只支持 inner side 是一个 DataSource，不支持 inner side 是 Agg->DataSource，而 b 是 Agg->DataSource 的形式。
需要改写下面的
mysql> explain select a.C_NAME, ( select sum(O_TOTALPRICE) from tpch.orders b where b.O_CUSTKEY = a.C_CUSTKEY group by o_custkey ) as price from tpch.customer a use index(index_C_PHONE) where a.C_PHONE = '23-768-687-3665';
+------------------------------------------+---------+-----------+-------------------------------------------+-----------------------------------------------------------------------------------------+
| id | estRows | task | access object | operator info |
+------------------------------------------+---------+-----------+-------------------------------------------+-----------------------------------------------------------------------------------------+
| Projection_11 | 1.01 | root | | tpch.customer.c_name, Column#27 |
| └─Apply_13 | 1.01 | root | | CARTESIAN left outer join |
| ├─IndexLookUp_16(Build) | 1.01 | root | | |
| │ ├─IndexRangeScan_14(Build) | 1.01 | cop[tikv] | table:a, index:index_C_PHONE(C_PHONE) | range:["23-768-687-3665","23-768-687-3665"], keep order:false |
| │ └─TableRowIDScan_15(Probe) | 1.01 | cop[tikv] | table:a | keep order:false |
| └─MaxOneRow_17(Probe) | 1.01 | root | | |
| └─StreamAgg_22 | 1.01 | root | | group by:tpch.orders.o_custkey, funcs:sum(tpch.orders.o_totalprice)->Column#27 |
| └─Projection_40 | 19.21 | root | | tpch.orders.o_custkey, tpch.orders.o_totalprice |
| └─IndexLookUp_39 | 19.21 | root | | |
| ├─IndexRangeScan_37(Build) | 19.21 | cop[tikv] | table:b, index:index_o_custkey(O_CUSTKEY) | range: decided by [eq(tpch.orders.o_custkey, tpch.customer.c_custkey)], keep order:true |
| └─TableRowIDScan_38(Probe) | 19.21 | cop[tikv] | table:b | keep order:false |
+------------------------------------------+---------+-----------+-------------------------------------------+-----------------------------------------------------------------------------------------+
11 rows in set (0.00 sec)
五、index join 执行计划下，outer表选择不是最优的问题
语句
select a.O_ORDERKEY, a.O_CUSTKEY,a.O_ORDERDATE,b.C_NAME from orders a join customer b on a.O_CUSTKEY = b.C_CUSTKEY;
mysql> select count(*) from orders;
+----------+
| count(*) |
+----------+
| 29955968 |
+----------+
1 row in set (3.93 sec)
mysql> select count(*) from customer;
+----------+
| count(*) |
+----------+
| 3000000 |
+----------+
1 row in set (0.72 sec)
tidb 语句的执行计划
mysql> explain analyze select /*+ INL_JOIN(a,b) */ a.O_ORDERKEY, a.O_CUSTKEY,a.O_ORDERDATE,b.C_NAME from orders a join customer b on a.O_CUSTKEY = b.C_CUSTKEY;
+----------------------------------+-------------+----------+-----------+-------------------------------------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------+------+
| id | estRows | actRows | task | access object | execution info | operator info | memory | disk |
+----------------------------------+-------------+----------+-----------+-------------------------------------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------+------+
| IndexJoin_20 | 29955968.00 | 29955968 | root | | time:5m11.9s, loops:29255, inner:{total:25m27.8s, concurrency:5, task:126, construct:2.48s, fetch:25m20.4s, build:4.88s}, probe:10.4s | inner join, inner:IndexLookUp_19, outer key:tpch.customer.c_custkey, inner key:tpch.orders.o_custkey, equal cond:eq(tpch.customer.c_custkey, tpch.orders.o_custkey) | 69.1 MB | N/A |
| ├─TableReader_31(Build) | 3000000.00 | 3000000 | root | | time:168ms, loops:2942, cop_task: {num: 116, max: 6.03s, min: 2.46ms, avg: 312.7ms, p95: 1.23s, max_proc_keys: 50144, p95_proc_keys: 50144, tot_proc: 23.5s, tot_wait: 5.25s, rpc_num: 116, rpc_time: 36.3s, copr_cache: disabled, distsql_concurrency: 15} | data:TableFullScan_30 | 13.0 MB | N/A |
| │ └─TableFullScan_30 | 3000000.00 | 3000000 | cop[tikv] | table:b | tikv_task:{proc max:5.75s, min:0s, avg: 199.1ms, p80:235ms, p95:717ms, iters:3387, tasks:116}, scan_detail: {total_process_keys: 3000000, total_process_keys_size: 610451426, total_keys: 3000116, get_snapshot_time: 2.4s, rocksdb: {key_skipped_count: 3000000, block: {cache_hit_count: 7844, read_count: 3203, read_byte: 71.6 MB, read_time: 2.59s}}} | keep order:false | N/A | N/A |
| └─IndexLookUp_19(Probe) | 29955968.00 | 29955968 | root | | time:25m16.2s, loops:29445, index_task: {total_time: 21m0.6s, fetch_handle: 19m11.8s, build: 4.73ms, wait: 1m48.7s}, table_task: {total_time: 1h0m25.6s, num: 1902, concurrency: 5}, next: {wait_index: 3m21.6s, wait_table_lookup_build: 1.34s, wait_table_lookup_resp: 21m51.1s} | | 100.3 KB | N/A |
| ├─IndexRangeScan_17(Build) | 29955968.00 | 29955968 | cop[tikv] | table:a, index:index_o_custkey(O_CUSTKEY) | time:19m10.5s, loops:29913, cop_task: {num: 2639, max: 5.57s, min: 2.9ms, avg: 603ms, p95: 1.99s, max_proc_keys: 50144, p95_proc_keys: 50144, tot_proc: 18m52.7s, tot_wait: 4m20.6s, rpc_num: 2639, rpc_time: 26m31.3s, copr_cache: disabled, distsql_concurrency: 15}, tikv_task:{proc max:5.06s, min:0s, avg: 428.6ms, p80:767ms, p95:1.67s, iters:39563, tasks:2639}, scan_detail: {total_process_keys: 29955968, total_process_keys_size: 1377974528, total_keys: 32958335, get_snapshot_time: 5.7s, rocksdb: {key_skipped_count: 29955968, block: {cache_hit_count: 28190101, read_count: 4508, read_byte: 40.3 MB, read_time: 33.7s}}} | range: decided by [eq(tpch.orders.o_custkey, tpch.customer.c_custkey)], keep order:false | N/A | N/A |
| └─TableRowIDScan_18(Probe) | 29955968.00 | 29955968 | cop[tikv] | table:a | time:1h0m6.3s, loops:31425, cop_task: {num: 97002, max: 4.45s, min: 529.9µs, avg: 364.6ms, p95: 1.54s, max_proc_keys: 656, p95_proc_keys: 429, tot_proc: 6h49m25.4s, tot_wait: 2h22m11.3s, rpc_num: 97032, rpc_time: 9h49m33.1s, copr_cache: disabled, distsql_concurrency: 15}, backoff{regionMiss: 56ms}, tikv_task:{proc max:4.03s, min:0s, avg: 253.6ms, p80:452ms, p95:1.26s, iters:339294, tasks:97002}, scan_detail: {total_process_keys: 29955968, total_process_keys_size: 4549646548, total_keys: 29972627, get_snapshot_time: 8.46s, rocksdb: {key_skipped_count: 33331, block: {cache_hit_count: 176784267, read_count: 15276, read_byte: 272.8 MB, read_time: 32.4s}}} | keep order:false | N/A | N/A |
+----------------------------------+-------------+----------+-----------+-------------------------------------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------+------+
6 rows in set (5 min 11.96 sec)
从执行计划来看，a表是customer 表，当成了inner表，回表的代价相当的巨大，
如果我们强行指定下，让a表当outer表，会不会效果更好呢?
mysql> explain analyze select /*+ INL_JOIN(b) */ a.O_ORDERKEY, a.O_CUSTKEY,a.O_ORDERDATE,b.C_NAME from orders a join customer b on a.O_CUSTKEY = b.C_CUSTKEY;
+-----------------------------+-------------+----------+-----------+---------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------+---------+------+
| id | estRows | actRows | task | access object | execution info | operator info | memory | disk |
+-----------------------------+-------------+----------+-----------+---------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------+---------+------+
| IndexJoin_12 | 29955968.00 | 29955968 | root | | time:2m58.7s, loops:29255, inner:{total:14m50.6s, concurrency:5, task:1180, construct:46.3s, fetch:13m59.3s, build:4.91s}, probe:18.3s | inner join, inner:TableReader_9, outer key:tpch.orders.o_custkey, inner key:tpch.customer.c_custkey, equal cond:eq(tpch.orders.o_custkey, tpch.customer.c_custkey) | 21.9 MB | N/A |
| ├─TableReader_18(Build) | 29955968.00 | 29955968 | root | | time:1.59s, loops:29317, cop_task: {num: 1025, max: 608.4ms, min: 3.23ms, avg: 158.7ms, p95: 333.4ms, max_proc_keys: 50144, p95_proc_keys: 50144, tot_proc: 1m30.4s, tot_wait: 7.71s, rpc_num: 1025, rpc_time: 2m42.6s, copr_cache: disabled, distsql_concurrency: 15} | data:TableFullScan_17 | 18.4 MB | N/A |
| │ └─TableFullScan_17 | 29955968.00 | 29955968 | cop[tikv] | table:a | tikv_task:{proc max:515ms, min:0s, avg: 84.3ms, p80:154ms, p95:228ms, iters:33322, tasks:1025}, scan_detail: {total_process_keys: 29955968, total_process_keys_size: 4549646548, total_keys: 29956993, get_snapshot_time: 1.68s, rocksdb: {key_skipped_count: 29955968, block: {cache_hit_count: 63232, read_count: 16425, read_byte: 282.6 MB, read_time: 11.8s}}} | keep order:false | N/A | N/A |
| └─TableReader_9(Probe) | 29955968.00 | 29744299 | root | | time:13m43.2s, loops:34209, cop_task: {num: 39435, max: 595.3ms, min: 657.8µs, avg: 77.6ms, p95: 211.6ms, max_proc_keys: 2016, p95_proc_keys: 2016, tot_proc: 21m47s, tot_wait: 8m43s, rpc_num: 39435, rpc_time: 50m57.4s, copr_cache: disabled, distsql_concurrency: 15} | data:TableRangeScan_8 | N/A | N/A |
| └─TableRangeScan_8 | 29955968.00 | 29744299 | cop[tikv] | table:b | tikv_task:{proc max:556ms, min:0s, avg: 33.4ms, p80:60ms, p95:133ms, iters:162048, tasks:39435}, scan_detail: {total_process_keys: 29744299, total_process_keys_size: 6052480951, total_keys: 29943155, get_snapshot_time: 2.1s, rocksdb: {key_skipped_count: 335362, block: {cache_hit_count: 141357867, read_count: 6, read_byte: 616.2 KB, read_time: 72.8ms}}} | range: decided by [tpch.orders.o_custkey], keep order:false | N/A | N/A |
+-----------------------------+-------------+----------+-----------+---------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------+---------+------+
5 rows in set (2 min 58.70 sec)
可以看到让a表当outer表，执行效率还是会好一点的，所以优化器在评估这种连接的时候，目前还是存在不足的
主要是目前tidb的join reorder 的算法比较简单，还无法把回表等成本考虑进去
这条语句在MySQL上的执行计划如下
mysql> explain analyze select a.O_ORDERKEY, a.O_CUSTKEY,a.O_ORDERDATE,b.C_NAME from orders a join customer b on a.O_CUSTKEY = b.C_CUSTKEY;
+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| EXPLAIN |
+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| -> Nested loop inner join (cost=18038574.80 rows=29529477) (actual time=0.093..388788.022 rows=30000000 loops=1)
-> Table scan on a (cost=3488289.09 rows=29529477) (actual time=0.075..270060.577 rows=30000000 loops=1)
-> Single-row index lookup on b using PRIMARY (C_CUSTKEY=a.O_CUSTKEY) (cost=0.39 rows=1) (actual time=0.004..0.004 rows=1 loops=30000000)
|
+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (6 min 32.82 sec)
可以看到是，MySQL就自动选择了a表当成是1个outer表，可以看出MySQL的join reorder 还是稍微好一点
六、结论
从上述的例子可以看到，TiDB的优化器在SQL改写、表连接的成本评估都存在一些改进空间，有些语句可能出现的概率不是很大，但是一旦出现，TiDB优化器不能很好处理的话，会导致语句的处理效率比较低，在这方面，TiDB的优化器在未来需要进一步的完善。