1.概述
TiDB 日常运维过程中，很多时候集群抖动都是一些慢 SQL 导致的，本文抛砖引玉，分享几则线上 SQL 优化案例，为大家日常 SQL 优化提供一些方法和思路。
2.优化案例1
- 原始 SQL
SELECT
rDelete
FROM
table_xxx
WHERE
rTopicId = 90168191
AND replyId = (
SELECT
rTargetReplyid
FROM
table_xxx
WHERE
replyId = 1319646872
) \"G
- 问题
这个表有13亿多数据，表上有索引(rTopicId,replyId)和(replyId )，而且区分度还不错，本条SQL返回数据是0行。但是，实际上这个 SQL 执行时间长达800多秒，扫描keys高达13亿，几十个这种SQL请求可能就会将集群拖垮，部分慢日志内容如下
# Query_time: 831.631793825
# Process_time: 2980.99 Wait_time: 9430.584 Request_count: 15455 Total_keys: 1316893603 Process_keys: 1312707991
- 优化方法
方法 1
将上面一条SQL拆分成下面两条SQL，这两条SQL分别执行的效率非常高，毫秒级返回数据。
SELECT rTargetReplyid FROM table_xxx WHERE replyId = 1319646872;
SELECT rDelete FROM table_xxx WHERE rTopicId = 90168191 AND replyId = xxx;
方法 2
将上面子查询SQL改写成 join 查询，改写后也是毫秒级级返回数据。
select
t.rDelete
from
table_xxx t,
(
select
rTargetReplyid
from
table_xxx
where
replyId = 1319646872
) tmp_t
where
t.rTopicId = 90168191
and t.replyId = tmp_t.rTargetReplyid;
方法 3
继续用子查询，这里只是将rTopicId字段也加入子查询
SELECT
rDelete
FROM
table_xxx
WHERE
rTopicId = 90168191
AND replyId = (
SELECT
rTargetReplyid
FROM
table_xxx
WHERE
rTopicId = 90168191
AND replyId = 1319646872
);
从性能方面来讲：
第一种方式 > 第二种方式 > 第三种方式
建议使用第一种方式，SQL 简单高效，最终我们线上也是采用的第一种方式，效果非常好。
- 优化后的效果及总结
优化前SQL执行时间800多秒，优化后毫秒级返回数据。我们平时书写SQL时应当尽量避免子查询，防止子查询带来的一些问题。
3.优化案例2
- 原始 SQL
select xxx,xxxx,xxxx from tb1 join tb2 join tb3 xxx limit 30000; #多个上亿的大表关联查询的复杂SQL
- 问题
完全相同的SQL，limit 10000可以正常返回结果，limit 20000也可以正常返回结果，但是limit 30000时无法返回结果
- 优化方法
调大TiDB系统参数，增大并发，主要调整的参数如下
set global tidb_index_lookup_concurrency=8; #默认值4
set global tidb_index_lookup_join_concurrency=8; #默认值4
set global tidb_index_join_batch_size=50000; #默认值25000
下面是这几个参数的说明，更详细的信息请见官网
tidb_index_lookup_concurrency
作用域：SESSION | GLOBAL
默认值：4
这个变量用来设置 index lookup 操作的并发度，AP 类应用适合较大的值，TP 类应用适合较小的值。
tidb_index_join_batch_size
作用域：SESSION | GLOBAL
默认值：25000
这个变量用来设置 index lookup join 操作的 batch 大小，AP 类应用适合较大的值，TP 类应用适合较小的值。
tidb_index_lookup_join_concurrency
作用域：SESSION | GLOBAL
默认值：4
这个变量用来设置 index lookup join 算法的并发度。
- 优化后的效果及总结
优化TiDB参数后，SQL(limit 30000)可以正常返回结果，响应时间有小幅提升。以上几个参数对于OLAP的系统，可以根据实际情况进行调整优化。
4.优化案例3
- 原始SQL
SELECT
a.biz_id,
a.biz_type,
a.short_title,
a.img_url,
a.author,
a.graphic_img_list3,
a.jump_url,
a.cms_series_ids,
a.car_brand_ids,
a.cms_level_ids,
a.cms_spec_ids,
a.cms_tags,
a.nlp_tags_choose2,
a.city_ids,
a.cms_content_class,
a.recommend_time,
a.created_stime AS create_at,
a.modified_stime,
e.uniq_brands_id,
e.uniq_brands_name,
e.uniq_category_id,
e.uniq_category_name,
e.uniq_city_id,
e.uniq_city_name,
e.uniq_keywords_id,
e.uniq_keywords_name,
e.uniq_nlp_is_accident,
e.uniq_nlp_is_beauty,
e.uniq_series_id,
e.uniq_series_name
FROM
tb1 a
LEFT JOIN tb2 e ON CONCAT(a.biz_type, '-2014-', a.biz_id) = e.object_uid
where
a.state = 0
and a.biz_type = 600038
and a.biz_id in (1087200);
- 问题
a表上有一个联合索引(state,biz_type,biz_id)，通过这个索引a表只返回1条数据，效率很高，e表上有一个唯一索引object_uid，按理来说效率应该非常高才对。但是这个SQL响应时间250多秒，明显效率太低，不合常理。
- 优化方法
通过执行计划看到此SQL走了tiflash，执行时间是256秒，我们加一下hint，让其走tikv看下执行计划和效率
desc SELECT /*+ read_from_storage(tikv[a],tikv[e]) */
a.biz_id,
a.biz_type,
a.short_title,
a.img_url,
a.author,
a.graphic_img_list3,
a.jump_url,
a.cms_series_ids,
a.car_brand_ids,
a.cms_level_ids,
a.cms_spec_ids,
a.cms_tags,
a.nlp_tags_choose2,
a.city_ids,
a.cms_content_class,
a.recommend_time,
a.created_stime AS create_at,
a.modified_stime,
e.uniq_brands_id,
e.uniq_brands_name,
e.uniq_category_id,
e.uniq_category_name,
e.uniq_city_id,
e.uniq_city_name,
e.uniq_keywords_id,
e.uniq_keywords_name,
e.uniq_nlp_is_accident,
e.uniq_nlp_is_beauty,
e.uniq_series_id,
e.uniq_series_name
FROM
tb1 a
LEFT JOIN tb2 e ON CONCAT(a.biz_type, '-2014-', a.biz_id) = e.object_uid
where
a.state = 0
and a.biz_type = 600038
and a.biz_id in (1087200);
+-------------------------------+----------+-----------+-------------------------------------------------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| id | estRows | task | access object | operator info |
+-------------------------------+----------+-----------+-------------------------------------------------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Projection_6 | 8.00 | root | | test.tb1.biz_id, test.tb1.biz_type, test.tb1.short_title, test.tb1.img_url, test.tb1.author, test.tb1.graphic_img_list3, test.tb1.jump_url, test.tb1.cms_series_ids, test.tb1.car_brand_ids, test.tb1.cms_level_ids, test.tb1.cms_spec_ids, test.tb1.cms_tags, test.tb1.nlp_tags_choose2, test.tb1.city_ids, test.tb1.cms_content_class, test.tb1.recommend_time, test.tb1.created_stime, test.tb1.modified_stime, test.tb2.uniq_brands_id, test.tb2.uniq_brands_name, test.tb2.uniq_category_id, test.tb2.uniq_category_name, test.tb2.uniq_city_id, test.tb2.uniq_city_name, test.tb2.uniq_keywords_id, test.tb2.uniq_keywords_name, test.tb2.uniq_nlp_is_accident, test.tb2.uniq_nlp_is_beauty, test.tb2.uniq_series_id, test.tb2.uniq_series_name |
| └─HashJoin_8 | 8.00 | root | | CARTESIAN left outer join |
| ├─Selection_10(Build) | 0.00 | root | | eq(test.tb1.state, 0) |
| │ └─Point_Get_9 | 1.00 | root | table:tb1, index:uniq_index_id_type(biz_id, biz_type) | |
| └─TableReader_13(Probe) | 8000.00 | root | | data:Selection_12 |
| └─Selection_12 | 8000.00 | cop[tikv] | | eq(concat(cast(600038), "-2014-", cast(1087200)), test.tb2.object_uid) |
| └─TableFullScan_11 | 10000.00 | cop[tikv] | table:e | keep order:false, stats:pseudo |
+-------------------------------+----------+-----------+-------------------------------------------------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
7 rows in set (0.00 sec)
走TiKV的响应时间是21秒，相比走TiFlash的250多秒快了不少。通过执行计划看到，e表并没有走唯一索引，而是进行了全表扫描，这是为什么？PingCAP官方小伙伴振娇一起帮忙分析了这个SQL，原因是：因为 CONCAT(a.biz_type,’-2014-’,a.biz_id) 这个拼接，导致 tidb 没有生成 a 这个列的相应的虚拟列 ，所以 e 表即使有唯一索引，也没有办法用上(优化器这块有提升的空间)
下面我们尝试通过改写 SQL 的方法来看下，改写后的 SQL 如下
desc select a1.*,
e.uniq_brands_id,
e.uniq_brands_name,
e.uniq_category_id,
e.uniq_category_name,
e.uniq_city_id,
e.uniq_city_name,
e.uniq_keywords_id,
e.uniq_keywords_name,
e.uniq_nlp_is_accident,
e.uniq_nlp_is_beauty,
e.uniq_series_id,
e.uniq_series_name
from
(select
CONCAT(a.biz_type,'-2014-',a.biz_id) as c1,
a.biz_type,
a.short_title,
a.img_url,
a.author,
a.graphic_img_list3,
a.jump_url,
a.cms_series_ids,
a.car_brand_ids,
a.cms_level_ids,
a.cms_spec_ids,
a.cms_tags,
a.nlp_tags_choose2,
a.city_ids,
a.cms_content_class,
a.recommend_time,
a.created_stime AS create_at,
a.modified_stime
from tb1 a
where a.state = 0 and a.biz_type = 600038 and a.biz_id in (1087200)
) a1
left join tb2 e on a1.c1 = e.object_uid;
+-----------------------------------+---------+-----------+-------------------------------------------------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| id | estRows | task | access object | operator info |
+-----------------------------------+---------+-----------+-------------------------------------------------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Projection_7 | 0.00 | root | | Column#106, test.tb1.biz_type, test.tb1.short_title, test.tb1.img_url, test.tb1.author, test.tb1.graphic_img_list3, test.tb1.jump_url, test.tb1.cms_series_ids, test.tb1.car_brand_ids, test.tb1.cms_level_ids, test.tb1.cms_spec_ids, test.tb1.cms_tags, test.tb1.nlp_tags_choose2, test.tb1.city_ids, test.tb1.cms_content_class, test.tb1.recommend_time, test.tb1.created_stime, test.tb1.modified_stime, test.tb2.uniq_brands_id, test.tb2.uniq_brands_name, test.tb2.uniq_category_id, test.tb2.uniq_category_name, test.tb2.uniq_city_id, test.tb2.uniq_city_name, test.tb2.uniq_keywords_id, test.tb2.uniq_keywords_name, test.tb2.uniq_nlp_is_accident, test.tb2.uniq_nlp_is_beauty, test.tb2.uniq_series_id, test.tb2.uniq_series_name |
| └─IndexJoin_11 | 0.00 | root | | left outer join, inner:IndexLookUp_10, outer key:Column#106, inner key:test.tb2.object_uid, equal cond:eq(Column#106, test.tb2.object_uid) |
| ├─Projection_16(Build) | 0.00 | root | | concat(cast(test.tb1.biz_type, var_string(20)), -2014-, cast(test.tb1.biz_id, var_string(20)))->Column#106, test.tb1.biz_type, test.tb1.short_title, test.tb1.img_url, test.tb1.author, test.tb1.graphic_img_list3, test.tb1.jump_url, test.tb1.cms_series_ids, test.tb1.car_brand_ids, test.tb1.cms_level_ids, test.tb1.cms_spec_ids, test.tb1.cms_tags, test.tb1.nlp_tags_choose2, test.tb1.city_ids, test.tb1.cms_content_class, test.tb1.recommend_time, test.tb1.created_stime, test.tb1.modified_stime |
| │ └─Selection_18 | 0.00 | root | | eq(test.tb1.state, 0) |
| │ └─Point_Get_17 | 1.00 | root | table:tb1, index:uniq_index_id_type(biz_id, biz_type) | |
| └─IndexLookUp_10(Probe) | 1.00 | root | | |
| ├─IndexRangeScan_8(Build) | 1.00 | cop[tikv] | table:e, index:uniq_object_uid(object_uid) | range: decided by [eq(test.tb2.object_uid, Column#106)], keep order:false, stats:pseudo |
| └─TableRowIDScan_9(Probe) | 1.00 | cop[tikv] | table:e | keep order:false, stats:pseudo |
+-----------------------------------+---------+-----------+-------------------------------------------------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
8 rows in set (0.01 sec)
- 优化后的效果及总结
优化前SQL执行时间250多秒，改写后的SQL运行时间0.14秒。同时，官方也在积极改进这个问题，详情请见https://github.com/pingcap/tidb/issues/22739
5.优化案例4
- 问题SQL
test > desc select * from tb1_bit where iscompleted=0 order by retrytimefornext ;
+---------------------------+------------+-----------+---------------+-----------------------------------+
| id | estRows | task | access object | operator info |
+---------------------------+------------+-----------+---------------+-----------------------------------+
| Sort_5 | 2617494.40 | root | | test.tb1_bit.retrytimefornext:asc |
| └─Selection_8 | 2617494.40 | root | | eq(test.tb1_bit.iscompleted, 0) |
| └─TableReader_10 | 3271868.00 | root | | data:TableFullScan_9 |
| └─TableFullScan_9 | 3271868.00 | cop[tikv] | table:tb1_bit | keep order:false |
+---------------------------+------------+-----------+---------------+-----------------------------------+
- 问题
表中有一个联合索引(iscompleted,retrytimefornext)，而且针对这个SQL，索引过滤性非常好，只有1条数据，但是却走了全表扫描，导致效率低。
- 优化方法
因为这个表是从mysql迁移过来的，在mysql上是可以走索引的，而且效率很高，因此猜测可能是 iscompleted 的类型导致的问题。iscompleted 是 bit 类型，尝试在测试环境改为 tinyint 类型，看下执行计划和效果
test > desc select * from tb1_tinyint where iscompleted=0 order by retrytimefornext ;
+--------------------------------+---------+-----------+------------------------------------------------------------------------------------------+------------------------------+
| id | estRows | task | access object | operator info |
+--------------------------------+---------+-----------+------------------------------------------------------------------------------------------+------------------------------+
| IndexLookUp_20 | 32.80 | root | | |
| ├─IndexRangeScan_18(Build) | 32.80 | cop[tikv] | table:tb1_tinyint, index:idx_iscompleted_retrytimefornext(iscompleted, retrytimefornext) | range:[0,0], keep order:true |
| └─TableRowIDScan_19(Probe) | 32.80 | cop[tikv] | table:tb1_tinyint | keep order:false |
+--------------------------------+---------+-----------+------------------------------------------------------------------------------------------+------------------------------+
从上面执行计划可以看到，修改类型后，SQL走了联合索引(iscompleted,retrytimefornext)
- 优化后的效果及总结
优化前执行时间2.46秒，优化后执行时间0.01秒。tidb对bit类型支持不是太友好，建议在tidb里面使用tinyint代替bit类型。
6.总结
上面分享的几则优化案例，原因即有SQL写法问题，也有TiDB优化器的Bug。对于每一个SQL，上线前建议业务方尽量审核，查看执行效率，尤其大表，否则上线后可能会造成比较严重的集群抖动。同时了解到，TiDB官方会在不久的将来提供改写SQL的特性(query rewrite)，这样在遇到慢SQL导致的集群抖动时，在业务方允许的情况下可以将慢SQL及时改写SQL返回，比如改写为select ‘’。
在这里为官方响应问题的速度和PingCAP小伙伴的大力支持点赞。TiDB很优秀也很强大，在迅速发展的过程中难免有一些小的瑕疵，不过瑕不掩瑜，掩盖不了TiDB的优秀。