TiDB optimizer didn't choose the best index
- Dominant language
- Go
- Stars
- 40.5k
- Forks
- 6.2k
- PR merge metrics
- PR metrics pending
Description
## Bug Report
Please answer these questions before submitting your issue. Thanks!
### 1. Minimal reproduce step (Required)
CREATE TABLE tt (
org_id_path varchar(256) NOT NULL,
res_id int(4) NOT NULL,
res_name varchar(128) NOT NULL,
status int(4) DEFAULT NULL,
KEY idx_org_status (org_id_path,status),
KEY idx_org_res_id (org_id_path,res_id)
)
sql1 := `explain select org_id_path,res_id,res_name,status from tt where org_id_path like '11%' order by res_id limit 0, 10;`

最优索引应该是 idx_org_res_id
sql2 := `explain select org_id_path,res_id,res_name,status from tt **use index (idx_org_res_id)** where org_id_path like '11%' order by res_id limit 0, 10;`

原因分析:
baseLogicalPlan.findBestTask() => child.findBestTask()
Datasource 贪心找到的 best plan,但是并不是整体最优的:
第一步:【TableScan,TaskTp: copDoubleReadTask】DataSource.findBestTask() 有两个索引:idx_org_status,idx_org_res_id,他们的代价是一样的,选到了第一个索引 idx_org_status (cost相等时,先被估算的索引胜出)
第二步:【core.PhysicalTopN】baseLogicalPlan.findBestTask() 把子计划与自身结合起来curTask := pp.attach2Task(childTasks...),这时,两个索引 topN是不同的:pushedDownTopN 使用 indexPlan或tablePlan。但是,第一步只返回了一个索引,这里没得选,只能使用 idx_org_status。
如果建表时,把索引 idx_org_status,idx_org_res_id顺序调换一下,则 idx_org_res_id 胜出。
### 2. What did you expect to see? (Required)
总是能使用最优的索引 idx_org_res_id
### 3. What did you see instead (Required)
使用了错误的索引,而且与建表时索引的排列顺序有关
### 4. What is your TiDB version? (Required)
所有版本
Contributor guide
Assessment
This issue has not been assessed yet.