共计 6502 个字符，预计需要花费 17 分钟才能阅读完成。
背景
最近在给一个物流零碎做 TiDB POC 测试，这个零碎是基于 MySQL 开发的，本次投入测试的业务数据大略 10 个库约 900 张表，最大单表 6 千多万行。
这个规模不算大，测试数据以及库表构造是用 Dumpling 从 MySQL 导出，再用 Lightning 导入到 TiDB 中，整个过程十分顺利。
零碎在 TiDB 上跑起来后，通过 Dashboard 察看到有一条 SQL 十分规律性地呈现在慢查问页面中，关上 SQL 一看只是个单表查问并不简单，感觉必有蹊跷。
问题景象
以下是从 Dashboard 中抓进去的原始 SQL 和执行打算，总共耗费了 1.2s，其中绝大部分工夫都花在了 Coprocessor 扫描数据中：
SELECT {31 个字段}
FROM
job_cm_data
WHERE
(
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
id
LIMIT
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
