扫码阅读

手机扫码阅读

TiDB | 如何用索引将性能提升666倍？

331 2023-09-07

索引字段查询 SQL 回表

TiDB

神州数码云基地

在 TiDB 上的尝试、调研与分享

本期作者

何傲

高级后端开发工程师

一个来自神码钛合金战队的

TiDB爱好者

最近在做一个基于MySQL开发的TiDB POC测试，本次投入的测试数据大概900张表，最大单表6千多万行。

这个规模不算大，整个过程应该是非常顺利的。但当系统在TiDB上跑起来后，通过Dashboard观察到有一条SQL非常规律性地出现在慢查询页面中。感觉必有蹊跷。

基于这个问题，我进行了一个小小的索引调整，性能居然升了666倍！本期我们便深入分析，如何发现问题、正确使用索引来提升性能。

# 问题现象

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

```sqlSELECT {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;```

```sqlid                           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```

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

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

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

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

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

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

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

#深入分析

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

```sqlmysql> select count(1) from job_cm_data;+----------+| count(1) |+----------+| 311994 |+----------+1 row in set (0.05 sec)```

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

```sqlmysql> 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这个索引有以下问题：

1. 区分度非常差，只有6个不同值

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

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

# 尝试直接删掉索引

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

我们尝试把这个索引删掉再执行一遍SQL。

```sqlmysql> 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的查询都变慢了。

因此要解决这个问题仅仅删掉索引是不行的。不仅慢查询变多，duration变高，全表扫描带来的后果会导致TiKV节点的读请求压力特别大。

# 失败后的问题溯源

初始情况下这个表只有2个region，而且leader都在同一个store上，导致该节点CPU使用量暴增，读热点问题非常明显。

经过手动切分region后把请求分摊到3个TiKV节点中，但Unified Readpool CPU还是都达到了80%左右，热力图最高每分钟流量6G。

让我们看看应该如何来解决。

#解决思路

确定任务目标

既然全表扫描行不通，那解决思路还是想办法让它用上索引。

经过沟通，得知这是一个存储定时任务元数据的表。虽然查询很频繁但是每次返回的结果集很少，真实业务中没有那多需要处理的任务。

基于这个背景，我联想到可以通过查索引得出最终符合条件的rowid，再拿这个小结果集去回表就可以大幅提升性能了。

组成复合索引

很显然，通过上述原理，我们需要一个复合索引。（也称为联合索引、组合索引，即把多个字段放在一个索引中。）

对于本文中的案例，可以考虑把where查询字段组成一个复合索引。

但怎么去组合字段其实是大有讲究的，很多人可能会一股脑把5个条件创建索引：

```sqlALTER TABLE `test`.`job_cm_data` ADD INDEX `idx_muti`(`group_id`, `cur_thread`,`pre_excutetime`,`ynflag`,`flag`);```

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

这个索引存在两个问题：

1. 5个索引字段有点太多了，维护成本大.

2. 5万多个索引扫描结果也有点太多（因为只用到了3个字段）。

优化再尝试

基于前面贴出来的表统计信息和索引创建原则，索引字段的区分度一定要高。

这5个查询字段里面pre_excutetime有35068个不同的值比较适合建索引，group_id从开始就已经排除了。

cur_thread有6个不同值，每个值数量都很均匀也不适合。ynflag列所有数据都是1可以直接放弃，最后剩下flag需要特别看一下。

```sqlmysql> 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%以上的数据。

有点意思，那就建个索引试试。

```sqlALTER TABLE `test`.`job_cm_data` ADD INDEX `idx_muti`(`pre_excutetime`,`flag`);```

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

别忘了，复合索引有个最左匹配原则，而这个pre_excutetime刚好是范围查询，所以实际只用到了pre_excutetime这个索引。

而偏偏整个表的数据都符合筛选的时间段，其实就相当于IndexFullScan了。那我们需要再把字段顺序换个位置：

```sqlALTER 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的最小时间，就可以算是个最好的解决方案了。你觉得呢？

看看成果

最后上一组图看看优化前后的对比。

#索引知识点总结

本次的这个例子就是想告诉大家，索引是个好东西，但并不是银弹，加的不好就难免适得其反。

本文涉及到的索引知识点：

1. 索引字段的区分区要足够高，最佳示例就是唯一索引。

2. 使用索引查询的效率不一定比全表扫描快。

3. 充分利用索引特点减少回表次数。

4. 复合索引的最左匹配原则。

5. 复合索引区分度高的字段放在前面。

碰到问题要能够具体情况具体分析，索引的使用原则估计很多人都背过，怎么能融会贯通去使用还是需要多思考！

索引不规范，DBA两行泪！

如果你身边有一个帮你调SQL的DBA，

且行且珍惜啊~

希望本期的案例能够切实帮助到大家！

原文链接： http://mp.weixin.qq.com/s?__biz=Mzg5MzUyOTgwMQ==&mid=2247502456&idx=1&sn=b6dae51a0072cb2ea62b914b972816d4&chksm=c02ff1def75878c83784dd3db8c7092da37f3ab9fe02511c37279e1ae5217baf9078fcb96a6c#rd

TiDB | 如何用索引将性能提升666倍？

联系我们

融实践库

微信公众号

微信视频号

社区微信群