TiDB 与 MySQL 优化器在特定语句下执行效果对比 (一)
作者：TiDB 社区干货传送门
- 2024-02-09 北京
本文字数：26322 字
阅读完需：约 86 分钟
作者： Raymond 原文来源：https://tidb.net/blog/4c9de20f
一、引言
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 的优化器在未来需要进一步的完善。
划线
评论
复制
发布于: 刚刚阅读数: 2
版权声明: 本文为 InfoQ 作者【TiDB 社区干货传送门】的原创文章。
原文链接:【http://xie.infoq.cn/article/c5a2c928d5f8d23605cfb00cd】。文章转载请联系作者。
TiDB 社区干货传送门
关注
TiDB 社区官网:https://tidb.net/ 2021-12-15 加入
TiDB 社区干货传送门是由 TiDB 社区中布道师组委会自发组织的 TiDB 社区优质内容对外宣布的栏目，旨在加深 TiDBer 之间的交流和学习。一起构建有爱、互助、共创共建的 TiDB 社区 https://tidb.net/
评论