TiDB查询优化调优系列五，调优案例实践分享

本篇文章为 TiDB 查询优化及调优系列的最终篇，主要汇集了一些用户常见的 SQL 优化案例，从背景、分析、影响、建议、实操几个角度进行解析关于 SQL 调优原理的介绍见前面章节相关阅读：TiDB 查询优化及调优系列（一）TiDB 优化器简介。

TiDB 查询优化及调优系列（二）TiDB 查询计划简介TiDB 查询优化及调优系列（三）慢查询诊断监控及排查TiDB 查询优化及调优系列（四）查询执行计划的调整及优化原理注：以下语句及结果基本为当时实际环境所记录的情况，因为版本更新原因，可能和现有格式略有差别，如 count 等价于现在的 estRows.

案例1: Delete 涉及数据量过大导致 OOMMySQL [db_stat]> explain delete from t_stat where imp_date<=20200202; +---------------------+--------------+------+------------------------------------------------------+

|id| count | task | operator info | +---------------------+--------------+------+------------------------------------------------------+

| TableReader_6 |220895815.00| root | data:Selection_5 || └─Selection_5 |220895815.00| cop | le(db_stat.t_stat.imp_date,

"20200202")|| └─TableScan_4 |220895815.00| cop | table:t_stat, range:[-inf,+inf], keep order:false | +---------------------+--------------+------+------------------------------------------------------+

3 rows inset(0.00 sec) MySQL [db_stat]>select count(*) from t_stat where imp_date<=20200202; +-----------+

| count(*)| +-----------+ |184340473| +-----------+ 1 row inset(17.88 sec)Copy背景大批量清理数据时系统资源消耗高，在 TiDB 节点内存不足时可能导致 OOM

分析imp_date 字段上虽然有索引，但是扫描的时间范围过大，无论优化器选择 IndexScan 还是 Table Scan，TiDB 都要向 TiKV Coprocessor 请求读取大量的数据影响

TiKV 节点 Coprocessor CPU 使用率快速上涨执行 Delete 操作的 TiDB 节点内存占用快速上涨，因为要将大批量数据加载到 TiDB 内存建议删除数据时，缩小数据筛选范围，或者加上 limit N 每次删除一批数据

建议使用 Range 分区表，按照分区快速删除案例2 执行计划不稳定导致查询延迟增加MySQL [db_stat]> explain SELECT * FROM tbl_article_check_result t WHERE

(articleid =20190925A0PYT800) ORDER BY checkTime desc LIMIT 100; +--------------------------+----------+------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+

|id| count | task | operator info | +--------------------------+----------+------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+

| Projection_7 |100.00| root | db_stat.t.type, db_stat.t.articleid, db_stat.t.docid, db_stat.t.version, db_stat.t.checkid, db_stat.t.checkstatus, db_stat.t.seclevel, db_stat.t.t1checkstatus, db_stat.t.t2checkstatus, db_stat.t.mdaichannel, db_stat.t.mdaisubchannel, db_stat.t.checkuser, db_stat.t.checktime, db_stat.t.addtime, db_stat.t.havegot, db_stat.t.checkcode

|| └─Limit_12 |100.00| root | offset:0, count:100 || └─IndexLookUp_34 |100.00| root ||| ├─IndexScan_31

|30755.49| cop | table:t, index:checkTime, range:[NULL,+inf], keep order:true, desc || └─Selection_33

|100.00| cop | eq(db_dayu_1.t.articleid, "20190925A0PYT800")|| └─TableScan_32 |30755.49| cop | table:tbl_article_check_result, keep order:false

| +--------------------------+----------+------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+

6 rows inset(0.00 sec)Copy背景articleid 和 checkTime 字段上分别建有单列索引，正常情况下走 articleid 上的索引比较快，偶尔执行计划不稳定时走 checkTime

上的索引，导致查询延迟达到分钟级别分析LIMIT 100 限定了获取 100 条记录，如果 checkTime 和 articleid 列之间的相关度不高，在独立性假设失效时，优化器估算走 checkTime

上的索引并满足 articleid 条件时扫描的行数，可能比走 articleid 上的索引扫描的行数更少影响业务响应延迟不稳定，监控 Duration 偶尔出现抖动建议手动 analyze table，配合 crontab 定期 analyze，维持统计信息准确度

自动 auto analyze，调低 analyze ratio 阈值，提高收集频次，并设置运行时间窗 set global tidb_auto_analyze_ratio=0.2;set global tidb_auto_analyze_start_time=00:00 +0800;

set global tidb_auto_analyze_end_time=06:00 +0800;业务修改 SQL ，使用 force index 固定使用 articleid 列上的索引业务可以不用修改 SQL，使用 SPM （见上述章节）的 create binding 创建 force index 的绑定 SQL，可以避免执行计划不稳定导致的性能下降

案例3 查询字段与值的数据类型不匹配MySQL [db_stat]> explain select * from t_like_list where person_id=1535538061143263

; +---------------------+------------+------+-----------------------------------------------------------------------------------+

|id| count | task | operator info | +---------------------+------------+------+-----------------------------------------------------------------------------------+

| Selection_5 |1430690.40| root | eq(cast(db_stat.t_like_list.person_id), 1.535538061143263e+15)|| └─TableReader_7

|1788363.00| root | data:TableScan_6 || └─TableScan_6 |1788363.00| cop | table:t_like_list, range:[-inf,+inf

], keep order:false | +---------------------+------------+------+-----------------------------------------------------------------------------------+

3 rows inset(0.00 sec)Copy背景person_id 列上建有索引且选择性较好，但执行计划没有按预期走 IndexScan分析person_id 是字符串类型，但是存储的值都是数字，业务认为可以直接赋值；而优化器需要在字段上做

cast 类型转换，导致无法使用索引建议where 条件的值加上引号，之后执行计划使用了索引：MySQL [db_stat]> explain select * from table:t_like_list where

person_id=1535538061143263; +-------------------+-------+------+----------------------------------------------------------------------------------------------------------+

|id| count | task | operator info | +-------------------+-------+------+----------------------------------------------------------------------------------------------------------+

| IndexLookUp_10 |0.00| root ||| ├─IndexScan_8 |0.00| cop | table:t_like_list, index:person_id, range:

["1535538061143263","1535538061143263"], keep order:false || └─TableScan_9 |0.00| cop | table:t_like_list, keep order:false

| +-------------------+-------+------+----------------------------------------------------------------------------------------------------------+

3 rows inset(0.00 sec)Copy案例4 读热点导致 SQL 延迟增加背景某个数据量 600G 左右、读多写少的 TiDB 集群，某段时间发现 TiDB 监控的 Query Summary - Duration 指标显著增加，p99 如下图。

查看 TiDB 监控下的 KV Duration 明显升高，其中 KV Request Duration 999 by store 监控看到多个 TiKV 节点 Duration 均有上涨。

查看 TiKV 监控 Coprocessor Overview：

查看监控 Coprocessor CPU：

发现 Coprocessor CPU 线程池几乎打满下面开始分析日志，调查 Duration 和 Coprocessor CPU 升高的原因慢查询日志分析使用 pt-query-digest 工具分析 TiDB 慢查询日志：。

./pt-query-digest tidb_slow_query.log > resultCopy分析慢日志解析出来的 TopSQL 发现 Process keys 和 Process time 并不是线性相关，

Process keys 数量多的 SQL 的 Process time 处理时间不一定更长，如下面 SQL 的 Process keys 为 22.09M，Process time 为 51s。

下面 SQL 的 Process keys 为 12.68M，但是 Process time 高达 142353s。

过滤 Process time 较多的 SQL，发现 3 个典型的 slow query，分析具体的执行计划SQL1select a.a_id, a.b_id,uqm.p_id from a join hsq on a.b_id

=hsq.id join uqm on a.a_id=uqm.id;Copy

SQL2select distinct g.abc, g.def, g.ghi, h.abcd, hi.jq from ggg g left join ggg_host gh on g.id = gh.ggg_id left

joinhost h on gh.a_id = h.id left join a_jq hi on h.id = hi.hid where h.abcd is not null and h.abcd <>

and hi.jq is not null and hi.jq <>;Copy

SQL3select tb1.mt, tb2.name from tb2 left join tb1 on tb2.mtId=tb1.id where tb2.type=0 and (tb1.mt is not null and tb1.mt

!=) and (tb2.name is not null or tb2.name !=);Copy

分析执行计划未发现异常，查看相关表的统计信息也都没有过期，继续分析 TiDB 和 TiKV 日志常规日志分析查看 TiKV 日志中标记为 [slow-query] 的日志行中的 region 分布情况more

tikv.log.2019-10-16-06\:28\:13 |grep slow-query |awk -F ]{print $1}|awk{print $6}|sort|uniq -c |sort

–nCopy找到访问频率最大的 3 个 region：73294521403332475766625Copy这些 region 的访问次数远远高于其它 region，之后定位这些 region 所属的表名。

首先查看 [slow-query] 所在行记录的 table_id 和 start_ts，然后查询 TiDB 日志获取表名，比如 table_id 为 1318，start_ts 为 411837294180565013，使用如下命令过滤，发现是上述慢查询 SQL 涉及的表。

more tidb-2019-10-14T16-40-51.728.log |grep"/[1318/]"|grep411837294180565013Copy解决对这些 region 做 split 操作，以 region 66625 为例，命令如下（需要将 x.x.x.x 替换为实际的 pd 地址）。

pd-ctl –u http://x.x.x.x:2379 operator add split-region 66625Copy操作后查看 PD 日志[2019/10/16 18:22:56.223 +08:00

][INFO][operator_controller.go:99]["operator finish"][region-id=30796][operator="\"admin-split-region (kind:admin, region:66625(1668,3), createAt:2019-10-16 18:22:55.888064898 +0800 CST m=+110918.823762963, startAt:2019-10-16 18:22:55.888223469 +0800 CST m=+110918.823921524, currentStep:1, steps:[split region with policy SCAN]) finished

\""]Copy日志显示 region 已经分裂完成，之后查看该 region 相关的 slow-query：more tikv.log.2019-10-16-06\:28\:13 |grep slow-query

|grep66625Copy观察一段时间后确认 66625 不再是热点 region，继续处理其它热点 region所有热点 region 处理完成后，监控 Query Summary - Duration 显著降低。

Duration 稳定了保持一段时间，18:55 之后仍然有较高的 Duration 出现：

观察压力较重的 tikv，移走热点 region 的 leader：pd-ctl –u http://x.x.x.x:2379 operator add transfer-leader 12 //把 region1 的 leader 调度到 store2

Copyleader 迁走之后，原 TiKV 节点的 Duration 立刻下降，但是迁移到新 TiKV 节点的 Duration 随之上升。

之后多次对热点 region 进行 split 操作，最终 Duration 明显下降并恢复稳定。

案例总结对于分布式数据库的读热点问题，有时难以通过优化 SQL 的方式解决，需要分析整个 TiDB 集群的监控和日志来定位原因严重的读热点可能导致部分 TiKV 达到资源瓶颈，这种短板效应限制了整个集群性能的充分发挥，通过分裂 region 的方式可以将热点 region 分散到更多的 TiKV 节点上，让每个 TiKV 的负载尽可能达到均衡，缓解读热点对 SQL 查询性能的影响。

更多热点问题的处理思路可以参考 TiDB 查询优化及调优系列（四）查询执行计划的调整及优化原理案例5 SQL 执行计划不准背景SQL 执行时间突然变长分析SQL 语句select count(*) from tods.bus_jijin_trade_record a, tods.bus_jijin_info b where a.fund_code。

=b.fund_code and a.type in(PURCHASE,APPLY) and a.status=CANCEL_SUCCESS and a.pay_confirm_status =1 and a.cancel_app_no is not null and a.id

>=177045000 and a.updated_at > date_sub(now(), interval 48 hour);Copy执行结果，需要 1 分 3.7s：mysql>select count

(*) -> from tods.bus_jijin_trade_record a, tods.bus_jijin_info b -> where a.fund_code=b.fund_code and a.type

in(PURCHASE,APPLY) -> and a.status=CANCEL_SUCCESS and a.pay_confirm_status =1 -> and a.cancel_app_no is not null and a.id

>=177045000 -> and a.updated_at > date_sub(now(), interval 48 hour); +----------+ | count(*)| +----------+

|708| +----------+ 1 row inset(1 min 3.77 sec)Copy索引信息

查看执行计划mysql> explain ->select count(*) -> from tods.bus_jijin_trade_record a, tods.bus_jijin_info b -

> where a.fund_code=b.fund_code and a.type in(PURCHASE,APPLY) -> and a.status=CANCEL_SUCCESS and a.pay_confirm_status

=1 -> and a.cancel_app_no is not null and a.id >=177045000 -> and a.updated_at > date_sub(now(), interval

48 hour); +----------------------------+--------------+------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------+

|id| count | task | operator info | +----------------------------+--------------+------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------+

| StreamAgg_13 |1.00| root | funcs:count(1)|| └─HashRightJoin_27 |421.12| root | inner join, inner:TableReader_18, equal:

[eq(a.fund_code, b.fund_code)]|| ├─TableReader_18 |421.12| root | data:Selection_17 || │ └─Selection_17

|421.12| cop | eq(a.pay_confirm_status, 1), eq(a.status, "CANCEL_SUCCESS"), gt(a.updated_at, 2020-03-03

22:31:08), in(a.type, "PURCHASE", "APPLY"), not(isnull(a.cancel_app_no))|| │ └─TableScan_16 |145920790.55

| cop | table:a, range:[177045000,+inf], keep order:false || └─TableReader_37 |6442.00| root | data:TableScan_36

|| └─TableScan_36 |6442.00| cop | table:b, range:[-inf,+inf], keep order:false | +----------------------------+--------------+------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------+ TableScan_16，TableScan_36：表示在 TiKV 端分别对表 a 和 b 的数据进行扫描，其中 TableScan_16 扫描了

1.46 亿的行数； Selection_17：表示满足表 a 后面 where 条件的数据； TableReader_37： 由于表 b 没有独立的附加条件，所以直接将这部分数据返回给 TiDB； TableReader_18：将各个 coprocessor 满足 a 表条件的结果返回给 TiDB； HashRightJoin_27：将 TableReader_37 和 TableReader_18 上的结果进行

hash join； StreamAgg_13：进一步统计所有行数，将数据返回给客户端；Copy可以看到语句中 a 表(bus_jijin_trade_record)的条件 id >= 177045000，和 updated_at > date_sub(now(), interval 48 hour)上，这两个列分别都有索引，但是 TiDB 还是选择了全表扫描。

按照上面两个条件分别查询数据分区情况mysql> SELECT COUNT(*) FROM tods.bus_jijin_trade_record WHERE id>=177045000; +-----------+

| COUNT(*)| +-----------+ |145917327| +-----------+ 1 row inset(16.86 sec) mysql> SELECT COUNT(*) FROM tods.bus_jijin_trade_record WHERE updated_at

> date_sub(now(), interval 48 hour); +-----------+ | COUNT(*)| +-----------+ |713682| +-----------+Copy

可以看到，表 bus_jijin_trade_record 有 1.7 亿的数据量，应该走 updated_at 字段上的索引 使用强制 hint 进行执行，6.27 秒就执行完成了，速度从之前 63s 到现在的 6.3s，提升了 10 倍。

mysql>select count(*) -> from tods.bus_jijin_trade_record a use index(idx_bus_jijin_trade_record_upt)

, tods.bus_jijin_info b -> where a.fund_code=b.fund_code and a.type in(PURCHASE,APPLY) -> and a.status

=CANCEL_SUCCESS and a.pay_confirm_status =1 -> and a.cancel_app_no is not null and a.id >=177045000 -

> and a.updated_at > date_sub(now(), interval 48 hour); +----------+ | count(*)| +----------+ |709| +----------+

1 row inset(6.27 sec)Copy强制 hint 后的执行计划：mysql> explain ->select count(*) -> from tods.bus_jijin_trade_record a use index

(idx_bus_jijin_trade_record_upt), tods.bus_jijin_info b -> where a.fund_code=b.fund_code and a.type

in(PURCHASE,APPLY) -> and a.status=CANCEL_SUCCESS and a.pay_confirm_status =1 -> and a.cancel_app_no is not null and a.id

>=177045000 -> and a.updated_at > date_sub(

版权声明：本文内容由网络用户投稿，版权归原作者所有，本站不拥有其著作权，亦不承担相应法律责任。如果您发现本站中有涉嫌抄袭或描述失实的内容，请联系我们jiasou666@gmail.com 处理，核实后本网站将在24小时内删除侵权内容。