left join 表连接未使用索引

[Kongdom]

【 TiDB 使用环境】生产环境
【 TiDB 版本】v7.5.1
【复现路径】无
【遇到的问题：问题现象及影响】

建表脚本：

CREATE TABLE IF NOT EXISTS `tmp_test_2024111901` (
  `c_no` varchar(20) COLLATE utf8mb4_general_ci NOT NULL,
  `c_tele` varchar(40) COLLATE utf8mb4_general_ci NOT NULL,
  `c_org_id` varchar(10) COLLATE utf8mb4_general_ci NOT NULL,
  PRIMARY KEY (`c_org_id`,`c_no`),
  KEY `idx_tele` (`c_org_id`,`c_tele`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

CREATE TABLE IF NOT EXISTS `tmp_test_2024111902` (
  `c_cardno` varchar(20) COLLATE utf8mb4_general_ci NOT NULL,
  `c_grade` varchar(10) COLLATE utf8mb4_general_ci NOT NULL,
  `c_status` varchar(20) COLLATE utf8mb4_general_ci NOT NULL,
  `c_customer` varchar(20) COLLATE utf8mb4_general_ci NOT NULL,
  `c_org_id` varchar(10) COLLATE utf8mb4_general_ci NOT NULL,
  PRIMARY KEY (`c_org_id`,`c_cardno`),
  KEY `idx_customer` (`c_org_id`,`c_customer`,`c_status`,`c_grade`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

查询语句：

EXPLAIN ANALYZE 
SELECT * FROM tmp_test_2024111901 t1
LEFT JOIN tmp_test_2024111902 t2 ON t2.c_org_id = t1.c_org_id 
                                AND t2.c_customer = t1.c_no 
                                AND t2.c_grade = '主' 
                                AND t2.c_status = '正常'
WHERE t1.c_org_id = '8' 
AND t1.c_tele  = '13666666666'  

执行计划：

id                      estRows actRows task        access object                                   execution info                                                                                                                                                                                                                  operator info                                                                                                                                                                                                                                                                                   memory      disk
IndexJoin_15            0.00    0       root                                                        time:160.8ms, loops:1, RU:0.553395                                                                                                                                                                                              left outer join, inner:TableReader_11, outer key:test.tmp_test_2024111901.c_org_id, inner key:test.tmp_test_2024111902.c_org_id, equal cond:eq(test.tmp_test_2024111901.c_no, test.tmp_test_2024111902.c_customer), eq(test.tmp_test_2024111901.c_org_id, test.tmp_test_2024111902.c_org_id)    0 Bytes     N/A
├─IndexReader_28(Build) 0.00    0       root                                                        time:160.7ms, loops:1, cop_task: {num: 1, max: 160.7ms, proc_keys: 0, tot_proc: 235.2µs, tot_wait: 3.36ms, rpc_num: 1, rpc_time: 160.6ms, copr_cache_hit_ratio: 0.00, build_task_duration: 19.3µs, max_distsql_concurrency: 1}  index:IndexRangeScan_27                                                                                                                                                                                                                                                                         284 Bytes   N/A
│ └─IndexRangeScan_27   0.00    0       cop[tikv]   table:t1, index:idx_tele(c_org_id, c_tele)  tikv_task:{time:1ms, loops:1}, scan_detail: {total_keys: 1, get_snapshot_time: 3.27ms, rocksdb: {block: {cache_hit_count: 6}}}                                                                                                  range:["\x008" "\x001\x003\x006\x006\x006\x006\x006\x006\x006\x006\x006","\x008" "\x001\x003\x006\x006\x006\x006\x006\x006\x006\x006\x006"], keep order:false                                                                                                                                   N/A         N/A
└─TableReader_11(Probe) 0.00    0       root                                                                                                                                                                                                                                                                                        data:Selection_10                                                                                                                                                                                                                                                                               N/A         N/A
  └─Selection_10        0.00    0       cop[tikv]                                                                                                                                                                                                                                                                                   eq(test.tmp_test_2024111902.c_grade, "主"), eq(test.tmp_test_2024111902.c_org_id, "8"), eq(test.tmp_test_2024111902.c_status, "正常")                                                                                                                                                            N/A         N/A
    └─TableRangeScan_9  0.00    0       cop[tikv]   table:t2                                                                                                                                                                                                                                                                        range: decided by [eq(test.tmp_test_2024111902.c_org_id, test.tmp_test_2024111901.c_org_id)], keep order:false                                                                                                                                                                                  N/A         N/A

问题，t2表没有走索引 inx_customer。如果把语句改成下面这样，将t2的c_org_id也给常量，就会走索引，两个写法不应该是等价的么？

EXPLAIN ANALYZE 
SELECT * FROM tmp_test_2024111901 t1
LEFT JOIN tmp_test_2024111902 t2 ON t2.c_org_id = '8'
                                AND t2.c_customer = t1.c_no 
                                AND t2.c_grade = '主' 
                                AND t2.c_status = '正常'
WHERE t1.c_org_id = '8' 
AND t1.c_tele  = '13666666666' 

[zhanggame1 (Ti D Ber G I13ecx U)]

改了条件只是逻辑上等价，sql上要重新生成执行计划。
另外对于聚簇表已经拿到主键值，TableRangeScan比走索引再回表显然快

[WalterWj (王军 - PingCAP)]

@Kongdom

t2.c_org_id = '8'

这个放 where 就是 inner join 了吧。
这两个 sql 也不是等价的。

[Kongdom]

但是实际数据执行下来，tablerangescan比索引慢了10倍。

[zhanggame1 (Ti D Ber G I13ecx U)]

手工加个hint吧

[Kongdom]

改了一下，放到on后面也是一样的。

[nobody (不定时出现)]

这里的 IndexReader_28 没有拿到 t1.c_no 这个条件，所以只用了一个关联列 t2.c_org_id = t1.c_org_id 去访问 T2 表，推测可能是 t2.c_org_id 过滤性不佳所以选择了 TableRangeScan_9。

这里的问题就是为啥 IndexReader_28 这步没有去回表拿 t1.c_no 这个条件，我本地测试这里是indexlookup 回表拿到 t1.c_no 然后再根据，t.c_org_id 和 t2. c_customer 去走索引 访问数据。（有点想不通为啥

可以尝试将 t1 表的 idx_tele 索引构成改造成 (c_org_id,c_tele,c_no) 试试

[nobody (不定时出现)]

@Kongdom

outer key:test.tmp_test_2024111901.c_org_id, inner key:test.tmp_test_2024111902.c_org_id,

outer key:test.tmp_test_2024111901.c_org_id, inner key:test.tmp_test_2024111902.c_org_id

[Kongdom]

@nobody

推测可能是 t2.c_org_id 过滤性不佳所以选择了 TableRangeScan_9。

确实，这个字段所有记录的值都是一样的。

[Kongdom]

@nobody

indexlookup 回表拿到 t1.c_no 然后再根据，t.c_org_id 和 t2. c_customer 去走索引 访问数据

这里我本地验证可能和数据量有关，用原表大数据量的表现就很稳定，一直不走索引。用上面复现脚本生成的就是另外一种表现。

[Brian]

对 表数据量，数据分布也很大程度上能影响执行计划

[zhanggame1 (Ti D Ber G I13ecx U)]

先analyze再看

[Kongdom]

analyze过了，都是100

[kevinsna (Ti D Ber P O Zcnp Ja)]

如果实在不行，就考虑加hint了

[Kongdom]

现在是想看看能不能在不改语句的情况下调优。如果改语句的话，可以按第二种写法，就能走到索引，速度也有提升了。

[zhanggame1 (Ti D Ber G I13ecx U)]

不改语句可以用global bindings固定执行计划

[kang]

改了条件只是逻辑上等价