子查询索引不工作,走了全表扫描

为提高效率,请提供以下信息,问题描述清晰能够更快得到解决:

【TiDB 版本】
v4.10
【问题描述】
子查询索引不工作,走了全表扫描
有两张表,
任务表:

create  table task(
    id bigint primary key not null ,
    name varchar(255)
);

INSERT INTO task (id, name) VALUES (1, '1');
INSERT INTO task (id, name) VALUES (2, '2');
INSERT INTO task (id, name) VALUES (3, '3');

任务参与者:

create  table task_participant(
    id bigint primary key not null ,
    task_id bigint not null,
    user_id bigint not null,
    name varchar(255) not null
);
INSERT INTO task_participant (id, task_id, user_id, name) VALUES (1, 2, 1, '张三');
INSERT INTO task_participant (id, task_id, user_id, name) VALUES (2, 2, 2, '李四');
INSERT INTO task_participant (id, task_id, user_id, name) VALUES (3, 1, 2, '李四');

其中task_id对应task表的id,在task_id上建立索引

create index IDX_TASK_ID_TASK_PARTICIPANT on task_participant(task_id);

原系统中有类似sql,执行时发现很慢

select name as taskName, (select group_concat(name) from task_participant where task_id = a.id) as participants from task a where a.id=1;

查看执行计划

explain  analyze  select name as taskName, (select group_concat(name) from task_participant where task_id = a.id) as participants from task a where a.id=1;
id	estRows	actRows	task	access object	execution info	operator info	memory	disk
HashJoin_13	1.00	1	root	""	time:1.13667ms, loops:2, build_hash_table:{total:892.342µs, fetch:885.217µs, build:7.125µs}, probe:{concurrency:5, total:5.099145ms, max:1.055399ms, probe:7.865µs, fetch:5.09128ms}	left outer join, equal:[eq(exam.task.id, exam.task_participant.task_id)]	8.26953125 KB	0 Bytes
├─Point_Get_21(Build)	1.00	1	root	table:task	time:789.893µs, loops:2, Get:{num_rpc:1, total_time:749.872µs}	handle:1	N/A	N/A
└─HashAgg_22(Probe)	2.40	2	root	""	time:1.034225ms, loops:3, PartialConcurrency:4, FinalConcurrency:4	"group by:exam.task_participant.task_id, funcs:group_concat(exam.task_participant.name separator "","")->Column#7, funcs:firstrow(exam.task_participant.task_id)->exam.task_participant.task_id"	6.78125 KB	N/A
  └─TableReader_28	3.00	3	root	""	time:866.684µs, loops:2, cop_task: {num: 1, max:982.896µs, proc_keys: 4, rpc_num: 1, rpc_time: 935.259µs, copr_cache_hit_ratio: 0.00}	data:TableFullScan_27	262 Bytes	N/A
    └─TableFullScan_27	3.00	3	cop[tikv]	table:task_participant	time:0ns, loops:0, tikv_task:{time:0s, loops:1}	keep order:false, stats:pseudo	N/A	N/A

发现表task_participant没有使用task_id上的索引,走了全表扫描

若提问为性能优化、故障排查类问题,请下载脚本运行。终端输出的打印结果,请务必全选并复制粘贴上传。

2 个赞


看执行计划中统计信息是使用的 pseudo 虚拟统计信息,那说明统计信息应该有点问题。
可以看下表的统计信息健康度情况:https://docs.pingcap.com/zh/tidb/stable/statistics#表的健康度信息

并且 analyze table 收集一下统计信息之后再看下执行计划。

1 个赞

前面健康度确实为0,执行了analyze table后,健康度为100了,但还是存在同样的问题.

mysql> SHOW STATS_HEALTHY where Table_name  like 'task%';
+---------+------------------+----------------+---------+
| Db_name | Table_name       | Partition_name | Healthy |
+---------+------------------+----------------+---------+
| exam    | task             |                |     100 |
| exam    | task_participant |                |     100 |
+---------+------------------+----------------+---------+
2 rows in set (0.00 sec)

现在的执行计划是这样的:

HashJoin_13	1.00	1	root	""	time:1.29164ms, loops:2, build_hash_table:{total:979.745µs, fetch:974.312µs, build:5.433µs}, probe:{concurrency:5, total:5.898458ms, max:1.196814ms, probe:9.751µs, fetch:5.888707ms}	left outer join, equal:[eq(exam.task.id, exam.task_participant.task_id)]	20.5 KB	0 Bytes
├─Point_Get_21(Build)	1.00	1	root	table:task	time:872.041µs, loops:2, Get:{num_rpc:1, total_time:826.149µs}	handle:1	N/A	N/A
└─HashAgg_22(Probe)	2.00	2	root	""	time:1.197775ms, loops:3, PartialConcurrency:4, FinalConcurrency:4	group by:exam.task_participant.task_id, funcs:group_concat(exam.task_participant.name separator ",")->Column#7, funcs:firstrow(exam.task_participant.task_id)->exam.task_participant.task_id	6.78125 KB	N/A
  └─TableReader_28	3.00	3	root	""	time:1.011186ms, loops:2, cop_task: {num: 1, max:1.013934ms, proc_keys: 3, rpc_num: 1, rpc_time: 971.636µs, copr_cache_hit_ratio: 0.00}	data:TableFullScan_27	264 Bytes	N/A
    └─TableFullScan_27	3.00	3	cop[tikv]	table:task_participant	time:0ns, loops:0, tikv_task:{time:0s, loops:1}	keep order:false	N/A	N/A
1 个赞

这个应该是数据量比较少,执行计划判断直接全表扫的代价表小所以选择了全表扫,三条记录如果走索引的话,还需要回表会比直接全表扫3条数据过滤条件代价大。

1 个赞

目前大数据量的表也是这样的情况,这里是对表结构和数据量作了简化

1 个赞

能提供一下大数据量的表环境下,收集统计信息之后,新的 explain analyze 结果是怎么样的么

1 个赞

这个是我们真实数据的执行结果,执行过analyze table了,STATS_HEALTHY如下

mysql> SHOW STATS_HEALTHY where Table_name  like 't_exam_paper%';
+---------+--------------+----------------+---------+
| Db_name | Table_name   | Partition_name | Healthy |
+---------+--------------+----------------+---------+
| exam    | t_exam_paper |                |      98 |
+---------+--------------+----------------+---------+
1 row in set (0.00 sec)

mysql> SHOW STATS_HEALTHY where Table_name  like 't_exam_worker%';
+---------+---------------+----------------+---------+
| Db_name | Table_name    | Partition_name | Healthy |
+---------+---------------+----------------+---------+
| exam    | t_exam_worker |                |      99 |
+---------+---------------+----------------+---------+
1 row in set (0.00 sec)

sql如下,表结构与上面的示例类似
explain analyze SELECT (select group_concat(tew.worker_name) from t_exam_worker tew where tew.exam_id = tep.exam_id) uploader FROM t_exam_paper tep WHERE tep.exam_id = 143950;
的执行计划如下

id	estRows	actRows	task	access object	execution info	operator info	memory	disk
HashJoin_13	2.01	1	root	""	time:434.337987ms, loops:2, build_hash_table:{total:534.708µs, fetch:529.029µs, build:5.679µs}, probe:{concurrency:5, total:2.171039928s, max:434.234318ms, probe:5.353005ms, fetch:2.165686923s}	left outer join, equal:[eq(exam.t_exam_paper.exam_id, exam.t_exam_worker.exam_id)]	8.26953125 KB	0 Bytes
├─IndexReader_29(Build)	2.01	1	root	""	time:451.548µs, loops:2, cop_task: {num: 1, max:632.096µs, proc_keys: 1, rpc_num: 1, rpc_time: 590.146µs, copr_cache_hit_ratio: 0.00}	index:IndexRangeScan_28	217 Bytes	N/A
│ └─IndexRangeScan_28	2.01	1	cop[tikv]	table:tep, index:logic(exam_id, paper_id)	time:0ns, loops:0, tikv_task:{time:0s, loops:1}	range:[143950,143950], keep order:false	N/A	N/A
└─HashAgg_30(Probe)	61688.36	60563	root	""	time:434.04929ms, loops:61, PartialConcurrency:4, FinalConcurrency:4	group by:exam.t_exam_worker.exam_id, funcs:group_concat(exam.t_exam_worker.worker_name separator ",")->Column#34, funcs:firstrow(exam.t_exam_worker.exam_id)->exam.t_exam_worker.exam_id	223.697265625 KB	N/A
  └─TableReader_38	370064.00	370122	root	""	time:298.284777ms, loops:363, cop_task: {num: 1, max:297.167162ms, proc_keys: 370149, rpc_num: 1, rpc_time: 297.151424ms, copr_cache_hit_ratio: 0.00}	data:TableFullScan_37	9.181286811828613 MB	N/A
    └─TableFullScan_37	370064.00	370122	cop[tikv]	table:tew	time:0ns, loops:0, tikv_task:{time:220ms, loops:366}	keep order:false	N/A	N/A
1 个赞

如果通过 force index 方式强制走索引的话,这个 SQL 执行效率会更高么?

1 个赞

加上force index,执行时间没什么变化
执行计划是IndexFullScan,期望的应该是IndexRangeScan,tidb生成子查询执行计划时,似乎没有将外面的条件纳入进来。
我用exam_id = 143950 代替tew.exam_id = tep.exam_id,将其放到子查询中后, SELECT (select group_concat(tew.worker_name) from t_exam_worker tew where tew.exam_id = 143950 uploader FROM t_exam_paper tep WHERE tep.exam_id = 143950;,会用到索引,执行时间有近百倍的提升,差不多是ms级和s级的差别。
而在mysql中,无论exam_id = 143950是否在子查询中,执行计划都会用到索引,sql也都是毫秒级。

1 个赞

可否改写下 sql
SELECT group_concat(tew.worker_name) uploader
FROM t_exam_paper tep
join t_exam_worker tew
on tew.exam_id = tep.exam_id
WHERE tep.exam_id = 143950

看下是否能解决此问题

如果方便希望可以提供
t_exam_paper
t_exam_worker
两张表的建表语句,以及他们的统计信息导出文件

1 个赞

https://github.com/pingcap/tidb/issues/15082

和github上的这个问题类似
这种查询在sql中应该还是比较常见. 因为是老项目,类似的sql还蛮多,所以想看看你们这边的安排. 如果已经修复或能比较快修复的话, 我们这边再等等;如果比较久的话, 我们产品内部增加一些工期作调整.
(t_exam_paper,t_exam_worker这两人张表因是线上真实的表,相关信息可能不是很方便提供出来,非常抱歉)

1 个赞

楼主这个问题我也遇到了 索引覆盖查询条件 但还是全表扫描 强制走索引也是IndexFullScan不是IndexRangeScan

这个优化对于目前 TiDB 的优化器框架来说并不好做,如果要做的话,可能需要调整整体设计,比较麻烦,短期内没有排期计划。

什么时间能安排修改这个问题那?我们很多地方用了这个子查询

官方目前好像没这方面的计划:joy:, 我们项目这边目前是将外面的条件移到了子查询里面.类似这样

select name as taskName, (select group_concat(name) from task_participant where task_id = 1) as participants from task a where a.id=1;


5.0.2测试可以走索引

这种case 4.12也可以,你可以把我上面的sql copy看下,我这边升级到最新的版本看,也是走的全表扫描.

测试了下,禁用子查询去关联化后,使用强制索引能走正确计划。


创建SPM绑定


SPM绑定后,在关闭 ‘子查询去关联’功能时原SQL能走正常执行计划,打开后执行计划又变了

测试有几个问题

  1. 禁用子查询去关联操作是全局的 ,不知道语句级能否设置?
  2. SPM绑定如何查看绑定后的执行计划? 为什么在绑定后还会出现执行计划变化情况?
    @GangShen @北京大爷
1 个赞

这个问题如果还没有解决,建议新建一个问题咨询~

此话题已在最后回复的 1 分钟后被自动关闭。不再允许新回复。