一条SQL查询,TiDB中性能很慢,想强制走索引也不生效,还是很慢,MySQL却很快。

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

**[TiDB 版本]**4.0.8 TiDB上的MySQL客户端是5.7

[MySQL MGR集群] 8.0.12



表结构:
all_tab.txt (43.9 KB)

测试的SQL:

查询的测试SQL.txt (3.6 KB)
[问题描述]
首先我贴下查询的对比图片:
MySQL8.0的MGR集群的SQL查询时间:
image

TiDB中直接从TiKV读取数据的查询时间:
image

TiDB中从TiFlash读取数据的查询时间是29秒,执行计划中可以看出来。

下面提供一下不同环境下执行SQL的执行计划:

TiDB直接查询不走TiFlash的执行计划:

explain analyze.txt (11.2 KB)
从MySQL的MGR集群读取数据执行计划 和 从TiFlash中读取数据的执行计划:
TiFlash和MGR集群的执行计划 (1).xlsx (16.2 KB)

尝试解决的办法:
一、在SQL端添加hint,使得TiDB能够走索引,但是发现TiDB的hint都是不生效的。
1、 /*+ use_index(xxx) */

2、use index

image


二、查看官网对于SQL性能优化都是将计算下推到TiKV节点,但是Join语句都是不能进行计算下推,猜想这可能是性能差异如此大的原因。但是我这边还没有确认,只能看到执行SQL时候TiKV cpu占用很低,TiDB占用却很高;

一个TiDB节点:
image-2021-01-20-16-33-48-680

三个TiKV节点

image-2021-01-20-16-19-47-444

1

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

  1. 可以看下几个表的统计信息健康度是怎么样的?可以通过 SHOW STATS_HEALTHY 命令看下,如果表的健康度在 90 以下,可以通过 analyze table 收集一下统计信息再执行 SQL 看下
  2. 可以提供一下 explain analyze SELECT COUNT( rh.review_his_id ) FROM t_cs_bu_review_his rh WHERE rh.review_source_code = hd.SERVER_ORDER ; 的执行计划是怎么样的吗?
  3. 方便的话,可以提供一下相关表的统计信息,那样我可以在我本地环境看下执行计划并尝试调整,导出统计信息的方法参考:https://docs.pingcap.com/zh/tidb/stable/statistics#导出统计信息

统计信息按照官网的方式在浏览器中无法访问,如果通过Wget或者Curl下载的文件不是json格式,而是乱码形式。我有点困惑。

你导出统计信息的地址是怎么样的?默认的 tidb_status_port 端口是 10080 不是 4000

bodir.zip (4.8 MB)

麻烦尝试加一下这个组合索引然后看下优化的效果

 alter table t_cs_bu_review_his add index ubd(review_source_code,review_his_id);

我这边加完索引之后,新的执行计划是这样的
explain_add.txt (44.1 KB)

添加索引udb.xlsx (12.9 KB)

这边添加了索引后性能还是不太好。
你这边的执行效率 不能够反应我这边的执行效率吧。

是的,所以需要在你们的环境上添加索引验证这个优化是否可行。
通过反馈的执行计划看到与添加索引之前没有变化


但是我本地添加索引之后根据统计信息执行计划应该是可以将 hashagg 消除掉的(从最初执行计划看到,SQL 耗时主要是在 HashAgg 算子上)

麻烦确认一下已经添加完成了,admin show ddl jobs 看到对应的 DDL 状态是 synced。


是这样么?
这边确认是加了索引了

useindex.sql (1.6 KB)
看下强制使用索引能否走到新的索引上

走不了索引。

上面的图片是走了TiFlash的

这个是走TiKV的

这个 SQL 有绑定执行计划吗?
可以 show bindings 看下,如果有的话可以删除一下 binding
https://docs.pingcap.com/zh/tidb/stable/sql-statement-drop-binding#drop-globalsession-binding

有 binding 的前提下,use index 或者 hint 等语法都会失效的。

image

image


这个是命令行登录到TiDB查询的结果集和查询时间

你好,我这边发现MySQL重启后,第一次执行也是49秒,我怀疑MySQL做了Cache。考虑MySQL8.0没要查询缓存,有可能会对热点数据缓存。目前只能这样下定论了。 TiDB每次都是重新计算。

感谢反馈,不好意思,没能帮上什么忙