TiDB 与 MySQL 优化器在特定语句下执行效果对比 (二)
作者：TiDB 社区干货传送门
- 2024-02-09 北京
本文字数：26858 字
阅读完需：约 88 分钟
作者： 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: customerCreate 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_bin1 row in set (0.00 sec)
mysql> show create table orders \G*************************** 1. row *************************** Table: ordersCreate 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_bin1 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: ordersCreate 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_bin1 row in set (0.00 sec)
mysql> show create table lineitem\G*************************** 1. row *************************** Table: lineitemCreate 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_bin1 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 语句的运行效率稍许不足，希望产品在后续优化器方面能够继续加强。
划线
评论
复制
发布于: 刚刚阅读数: 5
版权声明: 本文为 InfoQ 作者【TiDB 社区干货传送门】的原创文章。
原文链接:【http://xie.infoq.cn/article/a3b7bfa8ab5f5f74061bee84f】。文章转载请联系作者。
TiDB 社区干货传送门
关注
TiDB 社区官网:https://tidb.net/ 2021-12-15 加入
TiDB 社区干货传送门是由 TiDB 社区中布道师组委会自发组织的 TiDB 社区优质内容对外宣布的栏目，旨在加深 TiDBer 之间的交流和学习。一起构建有爱、互助、共创共建的 TiDB 社区 https://tidb.net/
评论