共计 16802 个字符，预计需要花费 43 分钟才能阅读完成。
本篇文章为 TiDB 查问优化及调优系列的最终篇，次要会集了一些用户常见的 SQL 优化案例，从背景、剖析、影响、倡议、实操几个角度进行解析。对于 SQL 调优原理的介绍见后面章节。
相干浏览：
TiDB 查问优化及调优系列（一）TiDB 优化器简介
TiDB 查问优化及调优系列（二）TiDB 查问打算简介
TiDB 查问优化及调优系列（三）慢查问诊断监控及排查
TiDB 查问优化及调优系列（四）查问执行打算的调整及优化原理
注：以下语句及后果根本为过后理论环境所记录的状况，因为版本更新起因，可能和现有格局略有差异，如 count 等价于当初的 estRows.
案例 1: Delete 波及数据量过大导致 OOM
MySQL [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 in set (0.00 sec)
MySQL [db_stat]> select count(*) from t_stat where imp_date<='20200202';
+-----------+
| count(*) |
+-----------+
| 184340473 |
+-----------+
1 row in set (17.88 sec)背景
大批量清理数据时系统资源耗费高，在 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 in set (0.00 sec)背景
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 in set (0.00 sec)背景
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 in set (0.00 sec)案例 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 > result剖析慢日志解析进去的 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，剖析具体的执行打算。
SQL1
select 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;
SQL2
select 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 join host 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 <>'';
SQL3
select 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 !='');
剖析执行打算未发现异常，查看相干表的统计信息也都没有过期，持续剖析 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 –n找到拜访频率最大的 3 个 region：
73 29452
140 33324
757 66625这些 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/]"' |grep 411837294180565013解决
对这些 region 做 split 操作，以 region 66625 为例，命令如下（须要将 x.x.x.x 替换为理论的 pd 地址）。
pd-ctl –u http://x.x.x.x:2379 operator add split-region 66625操作后查看 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\""]日志显示 region 曾经决裂实现，之后查看该 region 相干的 slow-query：
more tikv.log.2019-10-16-06\:28\:13 |grep slow-query | grep 66625察看一段时间后确认 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 1 2 // 把 region1 的 leader 调度到 store2leader 迁走之后，原 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) ;执行后果，须要 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 in set (1 min 3.77 sec)- 索引信息
- 查看执行打算
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：进一步统计所有行数，将数据返回给客户端；能够看到语句中 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 in set (16.86 sec)
mysql> SELECT COUNT(*) FROM tods.bus_jijin_trade_record WHERE updated_at > date_sub(now(), interval 48 hour) ;
+-----------+
| COUNT(*) |
+-----------+
| 713682 |
+-----------+能够看到，表 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 in set (6.27 sec)强制 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(now(), interval 48 hour) ;
+------------------------------+--------------+------+----------------------------------------------------------------------------------------------------------------------------+
| id | count | task | operator info |
+------------------------------+--------------+------+----------------------------------------------------------------------------------------------------------------------------+
| StreamAgg_13 | 1.00 | root | funcs:count(1) |
| └─HashRightJoin_24 | 421.12 | root | inner join, inner:IndexLookUp_20, equal:[eq(a.fund_code, b.fund_code)] |
| ├─IndexLookUp_20 | 421.12 | root | |
| │ ├─Selection_18 | 146027634.83 | cop | ge(a.id, 177045000) |
| │ │ └─IndexScan_16 | 176388219.00 | cop | table:a, index:UPDATED_AT, range:(2020-03-03 23:05:30,+inf], keep order:false |
| │ └─Selection_19 | 421.12 | cop | eq(a.pay_confirm_status, 1), eq(a.status, "CANCEL_SUCCESS"), in(a.type, "PURCHASE", "APPLY"), not(isnull(a.cancel_app_no)) |
| │ └─TableScan_17 | 146027634.83 | cop | table:bus_jijin_trade_record, keep order:false |
| └─TableReader_31 | 6442.00 | root | data:TableScan_30 |
| └─TableScan_30 | 6442.00 | cop | table:b, range:[-inf,+inf], keep order:false |
+------------------------------+--------------+------+----------------------------------------------------------------------------------------------------------------------------+应用 hint 后的执行打算，预估 updated_at 上的索引会扫描 176388219，没有抉择索引而抉择了全表扫描，能够断定是因为谬误的统计信息导致执行打算有问题。
查看表 bus_jijin_trade_record 上的统计信息。
mysql> show stats_meta where table_name like 'bus_jijin_trade_record' and db_name like 'tods';
+---------+------------------------+---------------------+--------------+-----------+
| Db_name | Table_name | Update_time | Modify_count | Row_count |
+---------+------------------------+---------------------+--------------+-----------+
| tods | bus_jijin_trade_record | 2020-03-05 22:04:21 | 10652939 | 176381997 |
+---------+------------------------+---------------------+--------------+-----------+
mysql> show stats_healthy where table_name like 'bus_jijin_trade_record' and db_name like 'tods';
+---------+------------------------+---------+
| Db_name | Table_name | Healthy |
+---------+------------------------+---------+
| tods | bus_jijin_trade_record | 93 |
+---------+------------------------+---------+依据统计信息，表 bus_jijin_trade_record 有 176381997，批改的行数有 10652939，该表的衰弱度为：(176381997-10652939)/176381997 *100=93。
解决
从新收集统计信息
mysql> set tidb_build_stats_concurrency=10;
Query OK, 0 rows affected (0.00 sec)
#调整收集统计信息的并发度，以便疾速对统计信息进行收集
mysql> analyze table tods.bus_jijin_trade_record;
Query OK, 0 rows affected (3 min 48.74 sec)查看没有应用 hint 语句的执行打算
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 | 1.99 | root | inner join, inner:IndexLookUp_23, equal:[eq(a.fund_code, b.fund_code)] |
| ├─IndexLookUp_23 | 1.99 | root | |
| │ ├─Selection_21 | 626859.65 | cop | ge(a.id, 177045000) |
| │ │ └─IndexScan_19 | 757743.08 | cop | table:a, index:UPDATED_AT, range:(2020-03-03 23:28:14,+inf], keep order:false |
| │ └─Selection_22 | 1.99 | cop | eq(a.pay_confirm_status, 1), eq(a.status, "CANCEL_SUCCESS"), in(a.type, "PURCHASE", "APPLY"), not(isnull(a.cancel_app_no)) |
| │ └─TableScan_20 | 626859.65 | cop | table:bus_jijin_trade_record, keep order:false |
| └─TableReader_37 | 6442.00 | root | data:TableScan_36 |
| └─TableScan_36 | 6442.00 | cop | table:b, range:[-inf,+inf], keep order:false |
+------------------------------+-----------+------+----------------------------------------------------------------------------------------------------------------------------+
9 rows in set (0.00 sec)能够看到，收集完统计信息后，当初的执行打算走了索引扫描，与手动增加 hint 的行为统一，且扫描的行数 757743 合乎预期。
此时执行工夫变为 1.69s，在执行打算没变的状况下，应该是因为缓存命中率回升带来的晋升。
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(*) |
+----------+
| 712 |
+----------+
1 row in set (1.69 sec)案例总结
能够看出该 SQL 执行效率变差是因为统计信息不精确造成的，在通过收集统计信息之后失去了正确的执行打算。
从最终后果 712 行记录来看，创立联结索引能够更大的升高扫描数据的量，更进一步晋升性能。在性能曾经满足业务要求状况下，联结索引会有额定的老本，留待当前尝试。
本文为「TiDB 查问优化及调优」系列文章的第五篇，也是最终篇。通过这个系列文章，咱们具体介绍了 TiDB 优化器、查问打算、慢查问以及调优的理论知识，并在本章节中进行了实战的分享。心愿通过这个系列文章，大家可能更加深刻地了解 TiDB 优化器，并通过这些调优技巧更好地晋升零碎性能。
如果您对 TiDB 的产品有任何倡议，欢送来到 internals.tidb.io 与咱们交换。
点击查看更多 TiDB 查问优化及调优文章
正文完
