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

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

作者: Raymond 原文来源:https://tidb.net/blog/599959f2

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

一、引言

Index join 是表关联的常用连接方式,笔者在近期学习TiDB 的index join 过程中,发现TiDB的优化器在选择表连接策略的过程中,在某些场景下并不能选择index join 作为表的连接方式,这一点与MySQL的优化器的选择策略是不同的,下面笔者就以几个例子来展现下这种差异:

备注:笔者测试语句的表数据来源均来自tpch,可以由tiup bench tpch ...导入生成这些数据

测试的TiDB版本为6.5.3,mysql 版本为8.0.30

二、测试案例1关联条件有函数

该测试案例的信息来源于 https://asktug.com/t/topic/1013277?replies_to_post_number=9 该帖子

对应的表结构

mysql> show create table customer \G
*************************** 1. row ***************************
       Table: customer
Create Table: CREATE TABLE `customer` (
  `C_CUSTKEY` bigint(20) NOT NULL,
  `C_NAME` varchar(25) NOT NULL,
  `C_ADDRESS` varchar(40) NOT NULL,
  `C_NATIONKEY` bigint(20) NOT NULL,
  `C_PHONE` char(15) NOT NULL,
  `C_ACCTBAL` decimal(15,2) NOT NULL,
  `C_MKTSEGMENT` char(10) NOT NULL,
  `C_COMMENT` varchar(117) NOT NULL,
  PRIMARY KEY (`C_CUSTKEY`) /*T![clustered_index] CLUSTERED */
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin
1 row in set (0.00 sec)

mysql> show create table orders \G
*************************** 1. row ***************************
       Table: orders
Create Table: CREATE TABLE `orders` (
  `O_ORDERKEY` bigint(20) NOT NULL,
  `O_CUSTKEY` bigint(20) NOT NULL,
  `O_ORDERSTATUS` char(1) NOT NULL,
  `O_TOTALPRICE` decimal(15,2) NOT NULL,
  `O_ORDERDATE` date NOT NULL,
  `O_ORDERPRIORITY` char(15) NOT NULL,
  `O_CLERK` char(15) NOT NULL,
  `O_SHIPPRIORITY` bigint(20) NOT NULL,
  `O_COMMENT` varchar(79) NOT NULL,
  PRIMARY KEY (`O_ORDERKEY`) /*T![clustered_index] CLUSTERED */,
  KEY `index_O_TOTALPRICE` (`O_TOTALPRICE`),
  KEY `index_a` (`O_ORDERSTATUS`,`O_TOTALPRICE`),
  KEY `indexO_CUSTKEY` (`O_CUSTKEY`),
  KEY `index_date` (`O_ORDERDATE`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin
1 row in set (0.00 sec)

原始语句及其执行计划

mysql> explain analyze select /*+ INL_JOIN(a,b) */ count(*) from customer a,orders b where a.c_phone='25-989-741-2988' and a.c_custkey=b.o_custkey and a.C_COMMENT=b.O_COMMENT;
+------------------------------------+-----------+---------+-----------+------------------------------------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+-----------+------+
| id                                 | estRows   | actRows | task      | access object                            | execution info                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                       | operator info                                                                                                                                                                                                                 | memory    | disk |
+------------------------------------+-----------+---------+-----------+------------------------------------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+-----------+------+
| StreamAgg_9                        | 1.00      | 1       | root      |                                          | time:901.5ms, loops:2                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                | funcs:count(1)->Column#18                                                                                                                                                                                                     | 8 Bytes   | N/A  |
| └─IndexJoin_39                     | 1.05      | 0       | root      |                                          | time:901.5ms, loops:1, inner:{total:183.6ms, concurrency:5, task:1, construct:247.6µs, fetch:183.2ms, build:61µs}, probe:41.2µs                                                                                                                                                                                                                                                                                                                                                                                                      | inner join, inner:IndexLookUp_38, outer key:tpch2.customer.c_custkey, inner key:tpch2.orders.o_custkey, equal cond:eq(tpch2.customer.c_comment, tpch2.orders.o_comment), eq(tpch2.customer.c_custkey, tpch2.orders.o_custkey) | 65.6 KB   | N/A  |
|   ├─TableReader_33(Build)          | 1.04      | 1       | root      |                                          | time:717.2ms, loops:3, cop_task: {num: 1, max: 717.2ms, proc_keys: 150000, tot_proc: 714ms, rpc_num: 1, rpc_time: 717.1ms, copr_cache: disabled, distsql_concurrency: 15}                                                                                                                                                                                                                                                                                                                                                            | data:Selection_32                                                                                                                                                                                                             | 423 Bytes | N/A  |
|   │ └─Selection_32                 | 1.04      | 1       | cop[tikv] |                                          | tikv_task:{time:714ms, loops:151}, scan_detail: {total_process_keys: 150000, total_process_keys_size: 30533765, total_keys: 150001, get_snapshot_time: 647.8µs, rocksdb: {key_skipped_count: 150000, block: {cache_hit_count: 5, read_count: 495, read_byte: 11.1 MB, read_time: 345.8ms}}}                                                                                                                                                                                                                                          | eq(tpch2.customer.c_phone, "25-989-741-2988")                                                                                                                                                                                 | N/A       | N/A  |
|   │   └─TableFullScan_31           | 150000.00 | 150000  | cop[tikv] | table:a                                  | tikv_task:{time:694ms, loops:151}                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                    | keep order:false                                                                                                                                                                                                              | N/A       | N/A  |
|   └─IndexLookUp_38(Probe)          | 1.05      | 6       | root      |                                          | time:183ms, loops:2, index_task: {total_time: 1.84ms, fetch_handle: 1.83ms, build: 670ns, wait: 1.09µs}, table_task: {total_time: 181.1ms, num: 1, concurrency: 5}, next: {wait_index: 2.01ms, wait_table_lookup_build: 0s, wait_table_lookup_resp: 181ms}                                                                                                                                                                                                                                                                           |                                                                                                                                                                                                                               | 9.34 KB   | N/A  |
|     ├─IndexRangeScan_36(Build)     | 1.05      | 6       | cop[tikv] | table:b, index:indexO_CUSTKEY(O_CUSTKEY) | time:1.83ms, loops:3, cop_task: {num: 1, max: 1.79ms, proc_keys: 6, rpc_num: 1, rpc_time: 1.77ms, copr_cache: disabled, distsql_concurrency: 15}, tikv_task:{time:1ms, loops:1}, scan_detail: {total_process_keys: 6, total_process_keys_size: 276, total_keys: 7, get_snapshot_time: 888.5µs, rocksdb: {key_skipped_count: 6, block: {cache_hit_count: 2}}}                                                                                                                                                                         | range: decided by [eq(tpch2.orders.o_custkey, tpch2.customer.c_custkey)], keep order:false                                                                                                                                    | N/A       | N/A  |
|     └─TableRowIDScan_37(Probe)     | 1.05      | 6       | cop[tikv] | table:b                                  | time:181ms, loops:2, cop_task: {num: 3, max: 85.9ms, min: 35.3ms, avg: 60.3ms, p95: 85.9ms, max_proc_keys: 2, p95_proc_keys: 2, tot_proc: 174ms, rpc_num: 3, rpc_time: 180.8ms, copr_cache: disabled, distsql_concurrency: 15}, tikv_task:{proc max:83ms, min:34ms, avg: 58.7ms, p80:83ms, p95:83ms, iters:3, tasks:3}, scan_detail: {total_process_keys: 6, total_process_keys_size: 929, total_keys: 6, get_snapshot_time: 2.35ms, rocksdb: {block: {cache_hit_count: 9, read_count: 18, read_byte: 3.76 MB, read_time: 170.7ms}}} | keep order:false                                                                                                                                                                                                              | N/A       | N/A  |
+------------------------------------+-----------+---------+-----------+------------------------------------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+-----------+------+
8 rows in set (0.94 sec)

从该执行计划来看,还是可以走正常的index join执行计划,但是如果把关联条件加个函数呢?语句如下

select   count(*) from customer a,orders b where a.c_phone='25-989-741-2988' and a.c_custkey=b.o_custkey and substr(a.C_COMMENT,1,10)=substr(b.O_COMMENT,1,10);

再跑一遍执行计划

mysql>  explain analyze select   count(*) from customer a,orders b where a.c_phone='25-989-741-2988' and a.c_custkey=b.o_custkey and substr(a.C_COMMENT,1,10)=substr(b.O_COMMENT,1,10);
+----------------------------------+------------+---------+-----------+---------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+---------------------------------------------------------------------------------------------------+-----------+---------+
| id                               | estRows    | actRows | task      | access object | execution info                                                                                                                                                                                                                                                                                  | operator info                                                                                     | memory    | disk    |
+----------------------------------+------------+---------+-----------+---------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+---------------------------------------------------------------------------------------------------+-----------+---------+
| StreamAgg_11                     | 1.00       | 1       | root      |               | time:1.51s, loops:2                                                                                                                                                                                                                                                                             | funcs:count(1)->Column#18                                                                         | 8 Bytes   | N/A     |
| └─HashJoin_22                    | 1.05       | 0       | root      |               | time:1.51s, loops:1, build_hash_table:{total:154.5ms, fetch:154.5ms, build:5.21µs}, probe:{concurrency:5, total:7.54s, max:1.51s, probe:190.6ms, fetch:7.35s}                                                                                                                                   | inner join, equal:[eq(tpch2.customer.c_custkey, tpch2.orders.o_custkey) eq(Column#19, Column#20)] | 25.7 KB   | 0 Bytes |
|   ├─Projection_14(Build)         | 1.04       | 1       | root      |               | time:154.4ms, loops:2, Concurrency:OFF                                                                                                                                                                                                                                                          | tpch2.customer.c_custkey, substr(tpch2.customer.c_comment, 1, 10)->Column#19                      | 40.7 KB   | N/A     |
|   │ └─TableReader_17             | 1.04       | 1       | root      |               | time:154.4ms, loops:2, cop_task: {num: 1, max: 169.2ms, proc_keys: 150000, tot_proc: 167ms, rpc_num: 1, rpc_time: 169.2ms, copr_cache: disabled, distsql_concurrency: 15}                                                                                                                       | data:Selection_16                                                                                 | 407 Bytes | N/A     |
|   │   └─Selection_16             | 1.04       | 1       | cop[tikv] |               | tikv_task:{time:167ms, loops:151}, scan_detail: {total_process_keys: 150000, total_process_keys_size: 30533765, total_keys: 150001, get_snapshot_time: 914µs, rocksdb: {key_skipped_count: 150000, block: {cache_hit_count: 500}}}                                                              | eq(tpch2.customer.c_phone, "25-989-741-2988")                                                     | N/A       | N/A     |
|   │     └─TableFullScan_15       | 150000.00  | 150000  | cop[tikv] | table:a       | tikv_task:{time:139ms, loops:151}                                                                                                                                                                                                                                                               | keep order:false                                                                                  | N/A       | N/A     |
|   └─Projection_18(Probe)         | 1498900.00 | 1498900 | root      |               | time:1.36s, loops:1469, Concurrency:5                                                                                                                                                                                                                                                           | tpch2.orders.o_custkey, substr(tpch2.orders.o_comment, 1, 10)->Column#20                          | 486.3 KB  | N/A     |
|     └─TableReader_20             | 1498900.00 | 1498900 | root      |               | time:855.3ms, loops:1469, cop_task: {num: 55, max: 184.1ms, min: 1.64ms, avg: 61.8ms, p95: 171.1ms, max_proc_keys: 50144, p95_proc_keys: 50144, tot_proc: 1.38s, tot_wait: 16ms, rpc_num: 55, rpc_time: 3.4s, copr_cache: disabled, distsql_concurrency: 15}                                    | data:TableFullScan_19                                                                             | 6.18 MB   | N/A     |
|       └─TableFullScan_19         | 1498900.00 | 1498900 | cop[tikv] | table:b       | tikv_task:{proc max:62ms, min:0s, avg: 20.4ms, p80:38ms, p95:48ms, iters:1682, tasks:55}, scan_detail: {total_process_keys: 1498900, total_process_keys_size: 227047584, total_keys: 1498955, get_snapshot_time: 6.04ms, rocksdb: {key_skipped_count: 1498900, block: {cache_hit_count: 3882}}} | keep order:false                                                                                  | N/A       | N/A     |
+----------------------------------+------------+---------+-----------+---------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+---------------------------------------------------------------------------------------------------+-----------+---------+
9 rows in set (1.52 sec)

从上面的执行计划可以看到,执行计划已经发生了改变,变成了hash join,那么如果利用sql hint 来强行指定index join的使用是否可行呢?

mysql> explain analyze select /*+ INL_JOIN(a,b) */ count(*) from customer a,orders b where a.c_phone='25-989-741-2988' and a.c_custkey=b.o_custkey and substr(a.C_COMMENT,1,10)=substr(b.O_COMMENT,1,10);
+----------------------------------+------------+---------+-----------+---------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+---------------------------------------------------------------------------------------------------+-----------+---------+
| id                               | estRows    | actRows | task      | access object | execution info                                                                                                                                                                                                                                                                                | operator info                                                                                     | memory    | disk    |
+----------------------------------+------------+---------+-----------+---------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+---------------------------------------------------------------------------------------------------+-----------+---------+
| StreamAgg_11                     | 1.00       | 1       | root      |               | time:1.41s, loops:2                                                                                                                                                                                                                                                                           | funcs:count(1)->Column#18                                                                         | 8 Bytes   | N/A     |
| └─HashJoin_22                    | 1.05       | 0       | root      |               | time:1.41s, loops:1, build_hash_table:{total:84.2ms, fetch:84.2ms, build:6.54µs}, probe:{concurrency:5, total:7.03s, max:1.41s, probe:176.7ms, fetch:6.85s}                                                                                                                                   | inner join, equal:[eq(tpch2.customer.c_custkey, tpch2.orders.o_custkey) eq(Column#19, Column#20)] | 25.7 KB   | 0 Bytes |
|   ├─Projection_14(Build)         | 1.04       | 1       | root      |               | time:84ms, loops:2, Concurrency:OFF                                                                                                                                                                                                                                                           | tpch2.customer.c_custkey, substr(tpch2.customer.c_comment, 1, 10)->Column#19                      | 40.7 KB   | N/A     |
|   │ └─TableReader_17             | 1.04       | 1       | root      |               | time:84ms, loops:2, cop_task: {num: 1, max: 84.6ms, proc_keys: 150000, tot_proc: 81ms, rpc_num: 1, rpc_time: 84.5ms, copr_cache: disabled, distsql_concurrency: 15}                                                                                                                           | data:Selection_16                                                                                 | 403 Bytes | N/A     |
|   │   └─Selection_16             | 1.04       | 1       | cop[tikv] |               | tikv_task:{time:81ms, loops:151}, scan_detail: {total_process_keys: 150000, total_process_keys_size: 30533765, total_keys: 150001, get_snapshot_time: 755.9µs, rocksdb: {key_skipped_count: 150000, block: {cache_hit_count: 503}}}                                                           | eq(tpch2.customer.c_phone, "25-989-741-2988")                                                     | N/A       | N/A     |
|   │     └─TableFullScan_15       | 150000.00  | 150000  | cop[tikv] | table:a       | tikv_task:{time:71ms, loops:151}                                                                                                                                                                                                                                                              | keep order:false                                                                                  | N/A       | N/A     |
|   └─Projection_18(Probe)         | 1498900.00 | 1498900 | root      |               | time:1.32s, loops:1468, Concurrency:5                                                                                                                                                                                                                                                         | tpch2.orders.o_custkey, substr(tpch2.orders.o_comment, 1, 10)->Column#20                          | 481.3 KB  | N/A     |
|     └─TableReader_20             | 1498900.00 | 1498900 | root      |               | time:807.3ms, loops:1468, cop_task: {num: 55, max: 209.3ms, min: 1.55ms, avg: 64.1ms, p95: 171ms, max_proc_keys: 50144, p95_proc_keys: 50144, tot_proc: 1.41s, tot_wait: 7ms, rpc_num: 55, rpc_time: 3.52s, copr_cache: disabled, distsql_concurrency: 15}                                    | data:TableFullScan_19                                                                             | 9.26 MB   | N/A     |
|       └─TableFullScan_19         | 1498900.00 | 1498900 | cop[tikv] | table:b       | tikv_task:{proc max:59ms, min:0s, avg: 20ms, p80:44ms, p95:54ms, iters:1682, tasks:55}, scan_detail: {total_process_keys: 1498900, total_process_keys_size: 227047584, total_keys: 1498955, get_snapshot_time: 3.97ms, rocksdb: {key_skipped_count: 1498900, block: {cache_hit_count: 4047}}} | keep order:false                                                                                  | N/A       | N/A     |
+----------------------------------+------------+---------+-----------+---------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+---------------------------------------------------------------------------------------------------+-----------+---------+
9 rows in set, 2 warnings (1.41 sec)

从执行计划来看,还是没有走index join,还是选择了hash join,所以根据执行结果来看,tidb在对这种关联条件使用了函数的情况下是无法走index join的,这一点需要优化,如果遇到1个小表和1个大表做关联,且关联字段上有索引,那么这种情况下走index join 比hash join 的效率是要高的

那么MySQL的执行计划是怎么样的?

mysql> explain analyze select   count(*) from customer a,orders b where a.c_phone='25-989-741-2988' and a.c_custkey=b.o_custkey and substr(a.C_COMMENT,1,10)=substr(b.O_COMMENT,1,10);                             +--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| EXPLAIN                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                        |
+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| -> Aggregate: count(0)  (cost=116149.61 rows=1) (actual time=48.579..48.580 rows=1 loops=1)
    -> Nested loop inner join  (cost=93735.93 rows=224137) (actual time=48.575..48.575 rows=0 loops=1)
        -> Filter: (a.C_PHONE = '25-989-741-2988')  (cost=15275.30 rows=14802) (actual time=0.046..48.508 rows=1 loops=1)
            -> Table scan on a  (cost=15275.30 rows=148023) (actual time=0.044..39.239 rows=150000 loops=1)
        -> Filter: (substr(a.C_COMMENT,1,10) = substr(b.O_COMMENT,1,10))  (cost=3.79 rows=15) (actual time=0.064..0.064 rows=0 loops=1)
            -> Index lookup on b using indexO_CUSTKEY (O_CUSTKEY=a.C_CUSTKEY)  (cost=3.79 rows=15) (actual time=0.060..0.062 rows=6 loops=1)
 |
+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.05 sec)

由此可以看到 MySQL在遇到关联条件上有用到函数的情况下,照样是可以走Index join的

三、测试案例2关联条件是非等值关联条件

对应的表结构

*************************** 1. row ***************************
       Table: orders
Create Table: CREATE TABLE `orders` (
  `O_ORDERKEY` bigint(20) NOT NULL,
  `O_CUSTKEY` bigint(20) NOT NULL,
  `O_ORDERSTATUS` char(1) NOT NULL,
  `O_TOTALPRICE` decimal(15,2) NOT NULL,
  `O_ORDERDATE` date NOT NULL,
  `O_ORDERPRIORITY` char(15) NOT NULL,
  `O_CLERK` char(15) NOT NULL,
  `O_SHIPPRIORITY` bigint(20) NOT NULL,
  `O_COMMENT` varchar(79) NOT NULL,
  PRIMARY KEY (`O_ORDERKEY`) /*T![clustered_index] CLUSTERED */,
  KEY `index_O_TOTALPRICE` (`O_TOTALPRICE`),
  KEY `index_a` (`O_ORDERSTATUS`,`O_TOTALPRICE`),
  KEY `indexO_CUSTKEY` (`O_CUSTKEY`),
  KEY `index_date` (`O_ORDERDATE`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin
1 row in set (0.00 sec)

mysql> show create table lineitem\G
*************************** 1. row ***************************
       Table: lineitem
Create Table: CREATE TABLE `lineitem` (
  `L_ORDERKEY` bigint(20) NOT NULL,
  `L_PARTKEY` bigint(20) NOT NULL,
  `L_SUPPKEY` bigint(20) NOT NULL,
  `L_LINENUMBER` bigint(20) NOT NULL,
  `L_QUANTITY` decimal(15,2) NOT NULL,
  `L_EXTENDEDPRICE` decimal(15,2) NOT NULL,
  `L_DISCOUNT` decimal(15,2) NOT NULL,
  `L_TAX` decimal(15,2) NOT NULL,
  `L_RETURNFLAG` char(1) NOT NULL,
  `L_LINESTATUS` char(1) NOT NULL,
  `L_SHIPDATE` date NOT NULL,
  `L_COMMITDATE` date NOT NULL,
  `L_RECEIPTDATE` date NOT NULL,
  `L_SHIPINSTRUCT` char(25) NOT NULL,
  `L_SHIPMODE` char(10) NOT NULL,
  `L_COMMENT` varchar(44) NOT NULL,
  PRIMARY KEY (`L_ORDERKEY`,`L_LINENUMBER`) /*T![clustered_index] CLUSTERED */,
  KEY `indexL_SHIPDATE` (`L_SHIPDATE`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin
1 row in set (0.00 sec)

语句

select  count(*) from orders a join lineitem b on a.O_ORDERDATE>b.L_SHIPDATE  where a.O_TOTALPRICE > 500000;

从语句可以看出,该语句的关联条件是非等值的关联条件

使用sql hint 强制让tidb使用index join,看是否可行

mysql> explain analyze select /*+ INL_JOIN(a,b) */   count(*) from orders a join lineitem b on a.O_ORDERDATE>b.L_SHIPDATE  where a.O_TOTALPRICE > 500000;
+--------------------------------------+---------------+----------+-----------+-------------------------------------------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+------------------------------------------------------------------------------------------+---------+---------+
| id                                   | estRows       | actRows  | task      | access object                                   | execution info                                                                                                                                                                                                                                                                                                                                                                                                                                                              | operator info                                                                            | memory  | disk    |
+--------------------------------------+---------------+----------+-----------+-------------------------------------------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+------------------------------------------------------------------------------------------+---------+---------+
| StreamAgg_10                         | 1.00          | 1        | root      |                                                 | time:19s, loops:2                                                                                                                                                                                                                                                                                                                                                                                                                                                           | funcs:count(1)->Column#26                                                                | 8 Bytes | N/A     |
| └─HashJoin_24                        | 1110028510.72 | 40096990 | root      |                                                 | time:18.2s, loops:39161, build_hash_table:{total:9.85ms, fetch:9.84ms, build:9.64µs}, probe:{concurrency:5, total:1m35.2s, max:19s, probe:1m28.4s, fetch:6.81s}                                                                                                                                                                                                                                                                                                             | CARTESIAN inner join, other cond:gt(tpch2.orders.o_orderdate, tpch2.lineitem.l_shipdate) | 12.1 KB | 0 Bytes |
|   ├─IndexLookUp_18(Build)            | 184.97        | 16       | root      |                                                 | time:9.82ms, loops:2, index_task: {total_time: 3.09ms, fetch_handle: 3.09ms, build: 596ns, wait: 1.4µs}, table_task: {total_time: 6.26ms, num: 1, concurrency: 5}, next: {wait_index: 3.6ms, wait_table_lookup_build: 0s, wait_table_lookup_resp: 6.21ms}                                                                                                                                                                                                                   |                                                                                          | 10.5 KB | N/A     |
|   │ ├─IndexRangeScan_16(Build)       | 184.97        | 16       | cop[tikv] | table:a, index:index_O_TOTALPRICE(O_TOTALPRICE) | time:3.09ms, loops:3, cop_task: {num: 2, max: 2.98ms, min: 1.96ms, avg: 2.47ms, p95: 2.98ms, max_proc_keys: 16, p95_proc_keys: 16, rpc_num: 2, rpc_time: 4.92ms, copr_cache: disabled, distsql_concurrency: 15}, tikv_task:{proc max:0s, min:0s, avg: 0s, p80:0s, p95:0s, iters:2, tasks:2}, scan_detail: {total_process_keys: 16, total_process_keys_size: 736, total_keys: 18, get_snapshot_time: 11.8µs, rocksdb: {key_skipped_count: 16, block: {cache_hit_count: 4}}}  | range:(500000.00,+inf], keep order:false                                                 | N/A     | N/A     |
|   │ └─TableRowIDScan_17(Probe)       | 184.97        | 16       | cop[tikv] | table:a                                         | time:6.09ms, loops:2, cop_task: {num: 3, max: 4.08ms, min: 645.5µs, avg: 1.96ms, p95: 4.08ms, max_proc_keys: 8, p95_proc_keys: 8, rpc_num: 3, rpc_time: 5.85ms, copr_cache: disabled, distsql_concurrency: 15}, tikv_task:{proc max:2ms, min:0s, avg: 666.7µs, p80:2ms, p95:2ms, iters:3, tasks:3}, scan_detail: {total_process_keys: 16, total_process_keys_size: 2446, total_keys: 16, get_snapshot_time: 55.2µs, rocksdb: {block: {cache_hit_count: 35}}}                | keep order:false                                                                         | N/A     | N/A     |
|   └─IndexReader_22(Probe)            | 6001215.00    | 6001215  | root      |                                                 | time:266.1ms, loops:5872, cop_task: {num: 193, max: 952ms, min: 1.71ms, avg: 309ms, p95: 766.9ms, max_proc_keys: 50144, p95_proc_keys: 50144, tot_proc: 5.04s, tot_wait: 72ms, rpc_num: 194, rpc_time: 59.6s, copr_cache: disabled, distsql_concurrency: 15}, backoff{regionMiss: 2ms}                                                                                                                                                                                      | index:IndexFullScan_21                                                                   | 2.30 MB | N/A     |
|     └─IndexFullScan_21               | 6001215.00    | 6001215  | cop[tikv] | table:b, index:indexL_SHIPDATE(L_SHIPDATE)      | tikv_task:{proc max:102ms, min:0s, avg: 25.1ms, p80:45ms, p95:69ms, iters:6622, tasks:193}, scan_detail: {total_process_keys: 6001215, total_process_keys_size: 342069255, total_keys: 6001408, get_snapshot_time: 17.1ms, rocksdb: {key_skipped_count: 6001215, block: {cache_hit_count: 428, read_count: 4684, read_byte: 27.1 MB, read_time: 60.7ms}}}                                                                                                                   | keep order:false                                                                         | N/A     | N/A     |
+--------------------------------------+---------------+----------+-----------+-------------------------------------------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+------------------------------------------------------------------------------------------+---------+---------+
7 rows in set, 2 warnings (19.05 sec)

从上面的执行计划可以看出,即使强制让优化器走index join 也是不行的

但是如果我们把关联条件换成等值关联条件呢?

mysql> explain analyze select /*+ INL_JOIN(a,b) */   count(*) from orders a join lineitem b on a.O_ORDERDATE=b.L_SHIPDATE  where a.O_TOTALPRICE > 500000;
+--------------------------------------+-----------+---------+-----------+-------------------------------------------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------+------+
| id                                   | estRows   | actRows | task      | access object                                   | execution info                                                                                                                                                                                                                                                                                                                                                                                                                                                                    | operator info                                                                                                                                                                 | memory   | disk |
+--------------------------------------+-----------+---------+-----------+-------------------------------------------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------+------+
| StreamAgg_10                         | 1.00      | 1       | root      |                                                 | time:58.9ms, loops:2                                                                                                                                                                                                                                                                                                                                                                                                                                                              | funcs:count(1)->Column#26                                                                                                                                                     | 8 Bytes  | N/A  |
| └─IndexJoin_48                       | 439441.22 | 39751   | root      |                                                 | time:58.2ms, loops:40, inner:{total:50.1ms, concurrency:5, task:1, construct:19.4µs, fetch:44.6ms, build:5.47ms}, probe:1.17ms                                                                                                                                                                                                                                                                                                                                                    | inner join, inner:IndexReader_47, outer key:tpch2.orders.o_orderdate, inner key:tpch2.lineitem.l_shipdate, equal cond:eq(tpch2.orders.o_orderdate, tpch2.lineitem.l_shipdate) | 398.1 KB | N/A  |
|   ├─IndexLookUp_40(Build)            | 184.97    | 16      | root      |                                                 | time:6.77ms, loops:3, index_task: {total_time: 2.72ms, fetch_handle: 2.72ms, build: 578ns, wait: 1.24µs}, table_task: {total_time: 3.98ms, num: 1, concurrency: 5}, next: {wait_index: 2.85ms, wait_table_lookup_build: 0s, wait_table_lookup_resp: 3.91ms}                                                                                                                                                                                                                       |                                                                                                                                                                               | 2.52 KB  | N/A  |
|   │ ├─IndexRangeScan_38(Build)       | 184.97    | 16      | cop[tikv] | table:a, index:index_O_TOTALPRICE(O_TOTALPRICE) | time:2.72ms, loops:3, cop_task: {num: 2, max: 2.64ms, min: 2.63ms, avg: 2.64ms, p95: 2.64ms, max_proc_keys: 16, p95_proc_keys: 16, rpc_num: 2, rpc_time: 5.24ms, copr_cache: disabled, distsql_concurrency: 15}, tikv_task:{proc max:1ms, min:0s, avg: 500µs, p80:1ms, p95:1ms, iters:2, tasks:2}, scan_detail: {total_process_keys: 16, total_process_keys_size: 736, total_keys: 18, get_snapshot_time: 2.11ms, rocksdb: {key_skipped_count: 16, block: {cache_hit_count: 4}}}  | range:(500000.00,+inf], keep order:false                                                                                                                                      | N/A      | N/A  |
|   │ └─TableRowIDScan_39(Probe)       | 184.97    | 16      | cop[tikv] | table:a                                         | time:3.9ms, loops:2, cop_task: {num: 3, max: 1.47ms, min: 1.16ms, avg: 1.28ms, p95: 1.47ms, max_proc_keys: 8, p95_proc_keys: 8, rpc_num: 3, rpc_time: 3.8ms, copr_cache: disabled, distsql_concurrency: 15}, tikv_task:{proc max:0s, min:0s, avg: 0s, p80:0s, p95:0s, iters:3, tasks:3}, scan_detail: {total_process_keys: 16, total_process_keys_size: 2446, total_keys: 16, get_snapshot_time: 1.79ms, rocksdb: {block: {cache_hit_count: 35}}}                                 | keep order:false                                                                                                                                                              | N/A      | N/A  |
|   └─IndexReader_47(Probe)            | 439441.22 | 39751   | root      |                                                 | time:43.8ms, loops:43, cop_task: {num: 27, max: 13.6ms, min: 1.05ms, avg: 5.65ms, p95: 13.1ms, max_proc_keys: 5470, p95_proc_keys: 5088, tot_proc: 23ms, tot_wait: 14ms, rpc_num: 28, rpc_time: 153.1ms, copr_cache: disabled, distsql_concurrency: 15}, backoff{regionMiss: 2ms}                                                                                                                                                                                                 | index:IndexRangeScan_46                                                                                                                                                       | 83.0 KB  | N/A  |
|     └─IndexRangeScan_46              | 439441.22 | 39751   | cop[tikv] | table:b, index:indexL_SHIPDATE(L_SHIPDATE)      | tikv_task:{proc max:6ms, min:0s, avg: 1.44ms, p80:3ms, p95:4ms, iters:142, tasks:27}, scan_detail: {total_process_keys: 39751, total_process_keys_size: 2265807, total_keys: 39789, get_snapshot_time: 8.71ms, rocksdb: {key_skipped_count: 39751, block: {cache_hit_count: 60, read_count: 47, read_byte: 278.1 KB, read_time: 744.3µs}}}                                                                                                                                        | range: decided by [eq(tpch2.lineitem.l_shipdate, tpch2.orders.o_orderdate)], keep order:false                                                                                 | N/A      | N/A  |
+--------------------------------------+-----------+---------+-----------+-------------------------------------------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------+------+
7 rows in set (0.06 sec)

从上述执行计划可知,把关联条件换成等值的就可以走Index join了

那么MySQL的执行计划是怎么样的?

mysql> explain  analyze  select  count(*) from orders a join lineitem b on a.O_ORDERDATE>b.L_SHIPDATE  where a.O_TOTALPRICE > 500000;
+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| EXPLAIN                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                            |
+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| -> Aggregate: count(0)  (cost=12395385.30 rows=1) (actual time=16682.222..16682.222 rows=1 loops=1)
    -> Nested loop inner join  (cost=9310532.80 rows=30848525) (actual time=0.549..15124.670 rows=40096992 loops=1)
        -> Index range scan on a using O_TOTALPRICE over (500000.00 < O_TOTALPRICE), with index condition: (a.O_TOTALPRICE > 500000.00)  (cost=7.64 rows=16) (actual time=0.093..2.346 rows=16 loops=1)
        -> Filter: (a.O_ORDERDATE > b.L_SHIPDATE)  (cost=15490.33 rows=1928033) (actual time=0.120..840.187 rows=2506062 loops=16)
            -> Covering index range scan on b (re-planned for each iteration)  (cost=15490.33 rows=5784677) (actual time=0.113..664.007 rows=2506062 loops=16)
 |
+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (16.69 sec)

发现MySQL在遇到这种情况下走index join 依然是支持的

四、结论

根据一些测试案例我们可知,TiDB 在某些语句下,优化器目前的选择策略还不是很完善,这会导致SQL语句的运行效率稍许不足,希望产品在后续优化器方面能够继续加强。

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