黄东旭解析 TiDB 的核心优势
505
2024-03-10
最近在给一个物流系统做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倍。
版权声明:本文内容由网络用户投稿,版权归原作者所有,本站不拥有其著作权,亦不承担相应法律责任。如果您发现本站中有涉嫌抄袭或描述失实的内容,请联系我们jiasou666@gmail.com 处理,核实后本网站将在24小时内删除侵权内容。