TiDB 与MySQL优化器在特定语句下执行效果对比(一)

原创
02/04 00:00
阅读数 11

作者: 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的优化器在未来需要进一步的完善。

展开阅读全文
加载中
点击引领话题📣 发布并加入讨论🔥
打赏
0 评论
0 收藏
0
分享
返回顶部
顶部