写点什么

用一个性能提升了 666 倍的小案例说明在 TiDB 中正确使用索引的重要性

  • 2022 年 5 月 10 日
  • 本文字数:9532 字

    阅读完需:约 31 分钟

背景

最近在给一个物流系统做 TiDB POC 测试,这个系统是基于 MySQL 开发的,本次投入测试的业务数据大概 10 个库约 900 张表,最大单表 6 千多万行。


这个规模不算大,测试数据以及库表结构是用 Dumpling 从 MySQL 导出,再用 Lightning 导入到 TiDB 中,整个过程非常顺利。


系统在 TiDB 上跑起来后,通过 Dashboard 观察到有一条 SQL 非常规律性地出现在慢查询页面中,打开 SQL 一看只是个单表查询并不复杂,感觉必有蹊跷。


问题现象

以下是从 Dashboard 中抓出来的原始 SQL 和执行计划,总共消耗了 1.2s,其中绝大部分时间都花在了 Coprocessor 扫描数据中:



SELECT {31个字段}FROM  job_cm_dataWHERE  (    group_id = 'GROUP_MATERIAL'    AND cur_thread = 1    AND pre_excutetime < '2022-04-27 11:55:00.018'    AND ynflag = 1    AND flag = 0  )ORDER BY  idLIMIT  200;
复制代码


  id                           task       estRows  operator info                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                  actRows  execution info                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                 memory   disk  Projection_7                 root       200      test_ba.job_cm_data.id, test_ba.job_cm_data.common_job_type, test_ba.job_cm_data.org_code, test_ba.job_cm_data.key_one, test_ba.job_cm_data.key_two, test_ba.job_cm_data.key_three, test_ba.job_cm_data.key_four, test_ba.job_cm_data.key_five, test_ba.job_cm_data.key_six, test_ba.job_cm_data.key_seven, test_ba.job_cm_data.key_eight, test_ba.job_cm_data.permission_one, test_ba.job_cm_data.permission_two, test_ba.job_cm_data.permission_three, test_ba.job_cm_data.cur_thread, test_ba.job_cm_data.group_id, test_ba.job_cm_data.max_execute_count, test_ba.job_cm_data.remain_execute_count, test_ba.job_cm_data.total_execute_count, test_ba.job_cm_data.pre_excutetime, test_ba.job_cm_data.related_data, test_ba.job_cm_data.delay_time, test_ba.job_cm_data.error_message, test_ba.job_cm_data.flag, test_ba.job_cm_data.ynflag, test_ba.job_cm_data.create_time, test_ba.job_cm_data.update_time, test_ba.job_cm_data.create_user, test_ba.job_cm_data.update_user, test_ba.job_cm_data.ip, test_ba.job_cm_data.version_num  0        time:1.17s, loops:1, Concurrency:OFF                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                           83.8 KB  N/A  └─Limit_14                   root       200      offset:0, count:200                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                            0        time:1.17s, loops:1                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                            N/A      N/A    └─Selection_31             root       200      eq(test_ba.job_cm_data.ynflag, 1)                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                0        time:1.17s, loops:1                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                            16.3 KB  N/A      └─IndexLookUp_41         root       200                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                     0        time:1.17s, loops:1, index_task: {total_time: 864.6ms, fetch_handle: 26.1ms, build: 53.3ms, wait: 785.2ms}, table_task: {total_time: 4.88s, num: 17, concurrency: 5}                                                                                                                                                                                                                                                                                                                                                                                           4.06 MB  N/A        ├─IndexRangeScan_38    cop[tikv]  7577.15  table:job_cm_data, index:idx_group_id(group_id), range:["GROUP_MATERIAL","GROUP_MATERIAL"], keep order:true                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                    258733   time:3.34ms, loops:255, cop_task: {num: 1, max: 2.45ms, proc_keys: 0, rpc_num: 1, rpc_time: 2.43ms, copr_cache_hit_ratio: 1.00}, tikv_task:{time:146ms, loops:257}                                                                                                                                                                                                                                                                                                                                                                                             N/A      N/A        └─Selection_40         cop[tikv]  200      eq(test_ba.job_cm_data.cur_thread, 1), eq(test_ba.job_cm_data.flag, 0), lt(test_ba.job_cm_data.pre_excutetime, 2022-04-27 11:55:00.018000)                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                           0        time:4.68s, loops:17, cop_task: {num: 18, max: 411.4ms, min: 15.1ms, avg: 263ms, p95: 411.4ms, max_proc_keys: 20480, p95_proc_keys: 20480, tot_proc: 4.41s, tot_wait: 6ms, rpc_num: 18, rpc_time: 4.73s, copr_cache_hit_ratio: 0.00}, tikv_task:{proc max:382ms, min:12ms, p80:376ms, p95:382ms, iters:341, tasks:18}, scan_detail: {total_process_keys: 258733, total_process_keys_size: 100627600, total_keys: 517466, rocksdb: {delete_skipped_count: 0, key_skipped_count: 258733, block: {cache_hit_count: 1296941, read_count: 0, read_byte: 0 Bytes}}}  N/A      N/A          └─TableRowIDScan_39  cop[tikv]  7577.15  table:job_cm_data, keep order:false                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                            258733   tikv_task:{proc max:381ms, min:12ms, p80:375ms, p95:381ms, iters:341, tasks:18}                                                                                                                                                                                                                                                                                                                                                                                                                                                                                N/A      N/A
复制代码


这个执行计划比较简单,稍微分析一下可以看出它的执行流程:


  • 先用IndexRangeScan算子扫描idx_group_id这个索引,得到了 258733 行符合条件的 rowid

  • 接着拿 rowid 去做TableRowIDScan扫描每一行数据并进行过滤,得到了 0 行数据

  • 以上两步组成了一个IndexLookUp回表操作,返回结果交给 TiDB 节点做 Limit,得到 0 行数据

  • 最后做一个字段投影Projection得到最终结果


execution info中看到主要的时间都花在Selection_40这一步,初步判断为大量回表导致性能问题。


小技巧:看到 IndexRangeScan 中 Loops 特别大的要引起重视了。

深入分析

根据经验推断,回表多说明索引效果不好,先看一下这个表的总行数是多少:


mysql> select count(1) from job_cm_data;+----------+| count(1) |+----------+|   311994 |+----------+1 row in set (0.05 sec)
复制代码


从回表数量来看,这个索引字段的区分度肯定不太行,进一步验证这个推断:


mysql> select group_id,count(1) from job_cm_data group by group_id;+------------------------------+----------+| group_id                     | count(1) |+------------------------------+----------+| GROUP_HOUSELINK              |       20 || GROUP_LMSMATER               |    37667 || GROUP_MATERIAL               |   258733 || GROUP_MATERISYNC             |    15555 || GROUP_WAREHOUSE_CONTRACT     |        7 || GROUP_WAREHOUSE_CONTRACT_ADD |       12 |+------------------------------+----------+6 rows in set (0.01 sec)
复制代码



从上面两个结果可以判断出idx_group_id这个索引有以下问题:


  • 区分度非常差,只有 6 个不同值

  • 数据分布非常不均匀,GROUP_MATERIAL 这个值占比超过了 80%


所以这是一个非常失败的索引。


对于本文中的 SQL 而言,首先要从索引中扫描出 258733 个 rowid,再拿这 258733 个 rowid 去查原始数据,不仅不能提高查询效率,反而让查询变的更慢了。


不信的话,我们把这个索引删掉再执行一遍 SQL。


mysql> alter table job_cm_data drop index idx_group_id;Query OK, 0 rows affected (0.52 sec)
复制代码



从这个执行计划看到现在已经变成了全表扫描,但是执行时间却比之前缩短了一倍多,而且当命中 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 个条件创建索引:


ALTER TABLE `test`.`job_cm_data` ADD INDEX `idx_muti`(`group_id`, `cur_thread`,`pre_excutetime`,`ynflag`,`flag`);
复制代码



确实,从这个执行计划可以看到性能有了大幅提升,比全表扫描快了 10 倍。那是不是可以收工了?还不行。


这个索引存在两个问题:


  • 5 个索引字段有点太多了,维护成本大

  • 5 万多个索引扫描结果也有点太多(因为只用到了 3 个字段)


基于前面贴出来的表统计信息和索引创建原则,索引字段的区分度一定要高,这 5 个查询字段里面pre_excutetime有 35068 个不同的值比较适合建索引,group_id从开始就已经排除了,cur_thread有 6 个不同值每个值数量都很均匀也不适合,ynflag列所有数据都是 1 可以直接放弃,最后剩下flag需要特别看一下。


mysql> select flag,count(1) from job_cm_data group by flag;+------+----------+| flag | count(1) |+------+----------+|    2 |   277832 ||    4 |       30 ||    1 |    34132 |+------+----------+3 rows in set (0.06 sec)
复制代码


从上面这个输出结果来看,它也算不上一个好的索引字段,但巧就巧在实际业务都是查询 flag=0 的数据,也就是说如果给它建了索引,在索引里就能排除掉 99%以上的数据。有点意思,那就建个索引试试。


ALTER TABLE `test`.`job_cm_data` ADD INDEX `idx_muti`(`pre_excutetime`,`flag`);
复制代码



这个结果好像和预期的不太对呀,怎么搞成扫描 31 万行索引了?


别忘了,复合索引有个最左匹配原则,而这个pre_excutetime刚好是范围查询,所以实际只用到了pre_excutetime这个索引,而偏偏整个表的数据都符合筛选的时间段,其实就相当于IndexFullScan了。那行,再把字段顺序换个位置:


ALTER TABLE `test`.`job_cm_data` ADD INDEX `idx_muti`(`flag``pre_excutetime`);
复制代码



看到执行时间这下满足了,在没有使用 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/ 。

评论

发布
暂无评论
用一个性能提升了666倍的小案例说明在TiDB中正确使用索引的重要性_TiDB 社区干货传送门_InfoQ写作社区