【 TiDB 使用环境】Poc
【 TiDB 版本】6.3.0
【遇到的问题】不走指定的INL_JOIN，执行效率太慢
【问题现象及影响】
表结构如下：
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 */,
KEY `customer_idx1` (`C_PHONE`),
KEY `customer_idx2` (`C_NATIONKEY`)
) 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 `orders_idx1` (`O_ORDERDATE`),
KEY `o_custkey` (`O_CUSTKEY`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin
1 row in set (0.00 sec)
如下SQL语句的执行计划：
mysql> explain select a.C_NAME,b.price from tpch.customer a left join (select o_custkey,sum(O_TOTALPRICE) as price from tpch.orders group by o_custkey) b on a.C_CUSTKEY=b.O_CUSTKEY where a.C_PHONE='23-768-687-3665';
+------------------------------------+-------------+-----------+---------------------------------------+-------------------------------------------------------------------------------------------------------------------------------+
| id | estRows | task | access object | operator info |
+------------------------------------+-------------+-----------+---------------------------------------+-------------------------------------------------------------------------------------------------------------------------------+
| HashJoin_13 | 1.02 | root | | left outer join, equal:[eq(tpch.customer.c_custkey, tpch.orders.o_custkey)] |
| ├─IndexLookUp_31(Build) | 1.02 | root | | |
| │ ├─IndexRangeScan_29(Build) | 1.02 | cop[tikv] | table:a, index:customer_idx1(C_PHONE) | range:["23-768-687-3665","23-768-687-3665"], keep order:false |
| │ └─TableRowIDScan_30(Probe) | 1.02 | cop[tikv] | table:a | keep order:false |
| └─HashAgg_38(Probe) | 1009664.00 | root | | group by:tpch.orders.o_custkey, funcs:sum(Column#21)->Column#18, funcs:firstrow(tpch.orders.o_custkey)->tpch.orders.o_custkey |
| └─TableReader_39 | 1009664.00 | root | | data:HashAgg_32 |
| └─HashAgg_32 | 1009664.00 | cop[tikv] | | group by:tpch.orders.o_custkey, funcs:sum(tpch.orders.o_totalprice)->Column#21 |
| └─TableFullScan_37 | 15000000.00 | cop[tikv] | table:orders | keep order:false |
+------------------------------------+-------------+-----------+---------------------------------------+-------------------------------------------------------------------------------------------------------------------------------+
8 rows in set (0.01 sec)
我理解最佳的执行计划路径为a表先通过customer_idx1索引过滤，然后通过INL_JOIN b表（b表的o_custkey索引），通过B表o_custkey索引，利用索引回表做sum(O_TOTALPRICE)，这样子是最佳的执行路径，但是实际情况并没有这么走，那么我手工加上hint，强制让其走：
mysql> explain select /*+ INL_JOIN(a,b) */ a.C_NAME,b.price from tpch.customer a left join (select o_custkey,sum(O_TOTALPRICE) as price from tpch.orders group by o_custkey) b on a.C_CUSTKEY=b.O_CUSTKEY where a.C_PHONE='23-768-687-3665';
+------------------------------------+-------------+-----------+---------------------------------------+-------------------------------------------------------------------------------------------------------------------------------+
| id | estRows | task | access object | operator info |
+------------------------------------+-------------+-----------+---------------------------------------+-------------------------------------------------------------------------------------------------------------------------------+
| HashJoin_12 | 1.02 | root | | left outer join, equal:[eq(tpch.customer.c_custkey, tpch.orders.o_custkey)] |
| ├─IndexLookUp_30(Build) | 1.02 | root | | |
| │ ├─IndexRangeScan_28(Build) | 1.02 | cop[tikv] | table:a, index:customer_idx1(C_PHONE) | range:["23-768-687-3665","23-768-687-3665"], keep order:false |
| │ └─TableRowIDScan_29(Probe) | 1.02 | cop[tikv] | table:a | keep order:false |
| └─HashAgg_37(Probe) | 1009664.00 | root | | group by:tpch.orders.o_custkey, funcs:sum(Column#21)->Column#18, funcs:firstrow(tpch.orders.o_custkey)->tpch.orders.o_custkey |
| └─TableReader_38 | 1009664.00 | root | | data:HashAgg_31 |
| └─HashAgg_31 | 1009664.00 | cop[tikv] | | group by:tpch.orders.o_custkey, funcs:sum(tpch.orders.o_totalprice)->Column#21 |
| └─TableFullScan_36 | 15000000.00 | cop[tikv] | table:orders | keep order:false |
+------------------------------------+-------------+-----------+---------------------------------------+-------------------------------------------------------------------------------------------------------------------------------+
8 rows in set, 2 warnings (0.00 sec)
mysql> show warnings;
+---------+------+-----------------------------------------------------------------------------------------------------------------------------------------------------+
| Level | Code | Message |
+---------+------+-----------------------------------------------------------------------------------------------------------------------------------------------------+
| Warning | 1815 | There are no matching table names for (b) in optimizer hint /*+ INL_JOIN(a, b) */ or /*+ TIDB_INLJ(a, b) */. Maybe you can use the table alias name |
| Warning | 1815 | Optimizer Hint /*+ INL_JOIN(a, b) */ or /*+ TIDB_INLJ(a, b) */ is inapplicable |
+---------+------+-----------------------------------------------------------------------------------------------------------------------------------------------------+
2 rows in set (0.00 sec)
可以看到并没有和预想的结果一样，看warnings是不能这样指定执行计划。
接下来我们执行查看耗时情况（为避免hash_agg发生oom，这里采用非并行模式做hash_agg）：
mysql> set tidb_hashagg_partial_concurrency=1;
Query OK, 0 rows affected, 1 warning (0.00 sec)
mysql> select a.C_NAME,b.price from tpch.customer a left join (select o_custkey,sum(O_TOTALPRICE) as price from tpch.orders group by o_custkey) b on a.C_CUSTKEY=b.O_CUSTKEY where a.C_PHONE='23-768-687-3665';
+--------------------+-----------+
| C_NAME | price |
+--------------------+-----------+
| Customer#000000002 | 481044.54 |
+--------------------+-----------+
1 row in set (10.86 sec)
可以看到执行了10多秒。
同样的数据量我导入到DB2中进行执行观察其执行计划和执行耗时（因执行计划输出较多，这里只贴局部关键信息）：
Optimizer Plan:
Rows
Operator
(ID)
Cost
1
RETURN
( 1)
63.7538
|
1
GRPBY
( 2)
63.7531
|
9.95056
NLJOIN
( 3)
63.7507
/--/ \---\
0.995056 10
TBSCAN FETCH
( 4) (--)
20.4589 43.2918
| / \
0.995056 10 1.5e+07
SORT RIDSCN Table:
( 5) ( 9) TPCH
20.4575 13.6895 ORDERS
| |
0.995056 10
FETCH SORT
( 6) (10)
20.455 13.6884
/ \ |
0.995056 1.5e+06 10
IXSCAN Table: IXSCAN
( 7) TPCH (11)
13.6718 CUSTOMER 13.685
| |
1.49996e+06 999982
Index: Index:
TPCH TPCH
CUSTOMER_IDX1 ORDERS_IDX2
[db2inst1@host1 ~]$ time db2 "select a.C_NAME,b.price from tpch.customer a left join (select o_custkey,sum(O_TOTALPRICE) as price from tpch.orders group by o_custkey) b on a.C_CUSTKEY=b.O_CUSTKEY where a.C_PHONE='23-768-687-3665'"
C_NAME PRICE
------------------------- ---------------------------------
Customer#000000002 481044.54
1 record(s) selected.
real 0m0.034s
user 0m0.008s
sys 0m0.018s
可以看到DB2是自动使用了正确的执行计划，并且效率很高。
所以这里的疑问是在存在索引的情况下，为何手工指定hint走INL_JOIN关联，但是实际并不走并显示这种关联不可用呢？