用一个性能提升了 666 倍的小案例说明在 TiDB 中正确使用索引的重要性
背景
最近在给一个物流系统做 TiDB POC 测试，这个系统是基于 MySQL 开发的，本次投入测试的业务数据大概 10 个库约 900 张表，最大单表 6 千多万行。
这个规模不算大，测试数据以及库表结构是用 Dumpling 从 MySQL 导出，再用 Lightning 导入到 TiDB 中，整个过程非常顺利。
系统在 TiDB 上跑起来后，通过 Dashboard 观察到有一条 SQL 非常规律性地出现在慢查询页面中，打开 SQL 一看只是个单表查询并不复杂，感觉必有蹊跷。
问题现象
以下是从 Dashboard 中抓出来的原始 SQL 和执行计划，总共消耗了 1.2s，其中绝大部分时间都花在了 Coprocessor 扫描数据中：
这个执行计划比较简单，稍微分析一下可以看出它的执行流程：
先用
IndexRangeScan算子扫描idx_group_id这个索引，得到了 258733 行符合条件的 rowid接着拿 rowid 去做
TableRowIDScan扫描每一行数据并进行过滤，得到了 0 行数据以上两步组成了一个
IndexLookUp回表操作，返回结果交给 TiDB 节点做 Limit，得到 0 行数据最后做一个字段投影
Projection得到最终结果
从execution info中看到主要的时间都花在Selection_40这一步，初步判断为大量回表导致性能问题。
小技巧：看到 IndexRangeScan 中 Loops 特别大的要引起重视了。
深入分析
根据经验推断，回表多说明索引效果不好，先看一下这个表的总行数是多少：
从回表数量来看，这个索引字段的区分度肯定不太行，进一步验证这个推断：
从上面两个结果可以判断出idx_group_id这个索引有以下问题：
区分度非常差，只有 6 个不同值
数据分布非常不均匀，GROUP_MATERIAL 这个值占比超过了 80%
所以这是一个非常失败的索引。
对于本文中的 SQL 而言，首先要从索引中扫描出 258733 个 rowid，再拿这 258733 个 rowid 去查原始数据，不仅不能提高查询效率，反而让查询变的更慢了。
不信的话，我们把这个索引删掉再执行一遍 SQL。
从这个执行计划看到现在已经变成了全表扫描，但是执行时间却比之前缩短了一倍多，而且当命中 Coprocessor Cache 的时候那速度就更快了：
正当我觉得删掉索引就万事大吉的时候，监控里的 Duration 99 线突然升高到了 200 多 ms，满脸问号赶紧查一下慢日志是什么情况。发现这条 SQL 执行时间虽然变短了，但是慢 SQL 突然就变多了：
仔细对比 SQL 后发现，这些 SQL 是分别查询了group_id的 6 个值，而且频率还很高。也就是说除了前面贴出来的那条 SQL 变快，其他group_id的查询都变慢了。
其实这个也在预期内，group_id比较少的数据就算走了索引它的回表次数也很少，这个时间仍然比全表扫描要快的多。
因此要解决这个问题仅仅删掉索引是不行的，不仅慢查询变多 duration 变高，全表扫描带来的后果导致 TiKV 节点的读请求压力特别大。
初始情况下这个表只有 2 个 region，而且 leader 都在同一个 store 上，导致该节点 CPU 使用量暴增，读热点问题非常明显。
经过手动切分 region 后把请求分摊到 3 个 TiKV 节点中，但 Unified Readpool CPU 还是都达到了 80%左右，热力图最高每分钟流量 6G。
继续盘它。
解决思路
既然全表扫描行不通，那解决思路还是想办法让它用上索引。
经过和业务方沟通，得知这是一个存储定时任务元数据的表，虽然查询很频繁但是每次返回的结果集很少，真实业务中没有那多需要处理的任务。
基于这个背景，我联想到可以通过查索引得出最终符合条件的 rowid，再拿这个小结果集去回表就可以大幅提升性能了。
那么很显然，我们需要一个复合索引，也称为联合索引、组合索引，即把多个字段放在一个索引中。对于本文中的案例，可以考虑把 where 查询字段组成一个复合索引。
但怎么去组合字段其实是大有讲究的，很多人可能会一股脑把 5 个条件创建索引：
确实，从这个执行计划可以看到性能有了大幅提升，比全表扫描快了 10 倍。那是不是可以收工了？还不行。
这个索引存在两个问题：
5 个索引字段有点太多了，维护成本大
5 万多个索引扫描结果也有点太多（因为只用到了 3 个字段）
基于前面贴出来的表统计信息和索引创建原则，索引字段的区分度一定要高，这 5 个查询字段里面pre_excutetime有 35068 个不同的值比较适合建索引，group_id从开始就已经排除了，cur_thread有 6 个不同值每个值数量都很均匀也不适合，ynflag列所有数据都是 1 可以直接放弃，最后剩下flag需要特别看一下。
从上面这个输出结果来看，它也算不上一个好的索引字段，但巧就巧在实际业务都是查询 flag=0 的数据，也就是说如果给它建了索引，在索引里就能排除掉 99%以上的数据。有点意思，那就建个索引试试。
这个结果好像和预期的不太对呀，怎么搞成扫描 31 万行索引了？
别忘了，复合索引有个最左匹配原则，而这个pre_excutetime刚好是范围查询，所以实际只用到了pre_excutetime这个索引，而偏偏整个表的数据都符合筛选的时间段，其实就相当于IndexFullScan了。那行，再把字段顺序换个位置：
看到执行时间这下满足了，在没有使用 Coprocessor Cache 的情况下执行时间也只需要 1.8ms。一个小小的索引调整，性能提升 666 倍。
建复合索引其实还有个原则，就是区分度高的字段要放在前面。因为复合索引是从左往右去对比，区分区高的字段放前面就能大幅减少后面字段对比的范围，从而让索引的效率最大化。
这就相当于层层过滤器，大家都希望每一层都尽可能多的过滤掉无效数据，而不希望 10 万行进来的时候到最后一层还是 10 万行，那前面的过滤就都没意义了。在这个例子中，flag就是一个最强的过滤器，放在前面再合适不过。
不过这也要看实际场景，当查询flag的值不为 0 时，会引起一定量的回表，我们以 4（30 行）和 1（34132 行）做下对比：
真实业务中，flag=0的数据不会超过 50 行，参考上面的结果，50 次回表也就 10ms 以内，性能依然不错，完全符合要求。我觉得应用层面允许调整 SQL 的话，再限制下pre_excutetime的最小时间，就可以算是个最好的解决方案了。
最后上一组图看看优化前后的对比。
nice~
总结
这个例子就是提示大家，索引是个好东西但并不是银弹，加的不好就难免适得其反。
本文涉及到的索引知识点：
索引字段的区分区要足够高，最佳示例就是唯一索引
使用索引查询的效率不一定比全表扫描快
充分利用索引特点减少回表次数
复合索引的最左匹配原则
复合索引区分度高的字段放在前面
碰到问题要能够具体情况具体分析，索引的使用原则估计很多人都背过，怎么能融会贯通去使用还是需要多思考。
索引不规范，DBA 两行泪，珍惜身边每一个帮你调 SQL 的 DBA 吧。
原作者：@hey-hoho 原文链接：https://tidb.net/blog/d20a3fe4
TiDB 社区官网:https://tidb.io 2021.12.15 加入
TiDB 社区干货传送门是由 TiDB 社区中布道师组委会自发组织的 TiDB 社区优质内容对外宣布的栏目，旨在加深 TiDBer 之间的交流和学习。一起构建有爱、互助、共创共建的 TiDB 社区 https://tidb.io/ 。
评论