【 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'
但是实际数据执行下来，tablerangescan比索引慢了10倍。
确实，这个字段所有记录的值都是一样的。