not in 不支持走index join嘛

【 TiDB 使用环境】测试
版本:v6.5.3
请问下各位老师，not in 这种anti join 语句是不支持走index join嘛

mysql> show create table t3;
+-------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table                                                                                                                                                                                                                     |
+-------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| t3    | CREATE TABLE `t3` (
  `id` int(11) NOT NULL,
  `name` varchar(10) DEFAULT NULL,
  PRIMARY KEY (`id`) /*T![clustered_index] CLUSTERED */,
  KEY `index_name` (`name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin |
+-------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

mysql> show create table t4;
+-------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table                                                                                                                                                                                                                     |
+-------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| t4    | CREATE TABLE `t4` (
  `id` int(11) NOT NULL,
  `name` varchar(10) DEFAULT NULL,
  PRIMARY KEY (`id`) /*T![clustered_index] CLUSTERED */,
  KEY `index_name` (`name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin |
+-------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

mysql> explain select /*+ INL_JOIN(t3) */  * from t3 where t3.name  not  in (select t4.name from t4);
+-----------------------------+---------+-----------+----------------------------------+-----------------------------------------------------------------------------+
| id                          | estRows | task      | access object                    | operator info                                                               |
+-----------------------------+---------+-----------+----------------------------------+-----------------------------------------------------------------------------+
| HashJoin_8                  | 1.60    | root      |                                  | CARTESIAN anti semi join, other cond:eq(nulljoin.t3.name, nulljoin.t4.name) |
| ├─IndexReader_16(Build)     | 1.00    | root      |                                  | index:IndexFullScan_15                                                      |
| │ └─IndexFullScan_15        | 1.00    | cop[tikv] | table:t4, index:index_name(name) | keep order:false, stats:pseudo                                              |
| └─TableReader_10(Probe)     | 2.00    | root      |                                  | data:TableFullScan_9                                                        |
|   └─TableFullScan_9         | 2.00    | cop[tikv] | table:t3                         | keep order:false, stats:pseudo                                              |
+-----------------------------+---------+-----------+----------------------------------+-----------------------------------------------------------------------------+
5 rows in set, 1 warning (0.00 sec)

mysql> explain select /*+ INL_JOIN(t3) */  * from t3 where t3.name    in (select t4.name from t4);
+--------------------------------+---------+-----------+----------------------------------+---------------------------------------------------------------------------------------------------------------------------------------------+
| id                             | estRows | task      | access object                    | operator info                                                                                                                               |
+--------------------------------+---------+-----------+----------------------------------+---------------------------------------------------------------------------------------------------------------------------------------------+
| IndexJoin_13                   | 1.25    | root      |                                  | inner join, inner:IndexReader_12, outer key:nulljoin.t4.name, inner key:nulljoin.t3.name, equal cond:eq(nulljoin.t4.name, nulljoin.t3.name) |
| ├─StreamAgg_32(Build)          | 1.00    | root      |                                  | group by:nulljoin.t4.name, funcs:firstrow(nulljoin.t4.name)->nulljoin.t4.name                                                               |
| │ └─IndexReader_33             | 1.00    | root      |                                  | index:StreamAgg_24                                                                                                                          |
| │   └─StreamAgg_24             | 1.00    | cop[tikv] |                                  | group by:nulljoin.t4.name,                                                                                                                  |
| │     └─IndexFullScan_31       | 1.00    | cop[tikv] | table:t4, index:index_name(name) | keep order:true, stats:pseudo                                                                                                               |
| └─IndexReader_12(Probe)        | 1.25    | root      |                                  | index:Selection_11                                                                                                                          |
|   └─Selection_11               | 1.25    | cop[tikv] |                                  | not(isnull(nulljoin.t3.name))                                                                                                               |
|     └─IndexRangeScan_10        | 1.25    | cop[tikv] | table:t3, index:index_name(name) | range: decided by [eq(nulljoin.t3.name, nulljoin.t4.name)], keep order:false, stats:pseudo                                                  |
+--------------------------------+---------+-----------+----------------------------------+---------------------------------------------------------------------------------------------------------------------------------------------+
8 rows in set (0.00 sec)

image2092×817 36.3 KB

感觉查询计划没有问题

join应该属于连接算法了

多了一个agg ，index join 好像不支持

改写成table join试一下。from T3 left join T4 on T3.NAME=T4.NAME and T3.NAME is null

可以考虑用except差集运算来改写，TiDB不支持minus。

一般not in不走的，换成not exists / left join看看

换成 left join 或者 right join 试试

反半连接只支持hashjoin，猜测左外反半连接支持indexjoin。
可以参考这篇文章中的左外反半连接做下hint走索引的测试：

不支持，换成别的吧

这就是 TiDB 社区的帖子，为啥不贴社区链接呢

换成left join试试

是的毛老师的文章，但是我之前是在知乎上看到了就直接在知乎上搜索的。

子查询不如join

left join 试试

TiDB不支持minus。