在 TiDB 中正确使用索引,性能提升 666 倍 |
您所在的位置:网站首页 › tidb主键可以不唯一 › 在 TiDB 中正确使用索引,性能提升 666 倍 |
在 TiDB 中正确使用索引,性能提升 666 倍 作者:hey-hoho 背景最近在给一个物流系统做TiDB POC测试,这个系统是基于MySQL开发的,本次投入测试的业务数据大概10个库约900张表,最大单表6千多万行。 这个规模不算大,测试数据以及库表结构是用Dumpling从MySQL导出,再用Lightning导入到TiDB中,整个过程非常顺利。 系统在TiDB上跑起来后,通过Dashboard观察到有一条SQL非常规律性地出现在慢查询页面中,打开SQL一看只是个单表查询并不复杂,感觉必有蹊跷。 以下是从Dashboard中抓出来的原始SQL和执行计划,总共消耗了1.2s,其中绝大部分时间都花在了Coprocessor扫描数据中: 从回表数量来看,这个索引字段的区分度肯定不太行,进一步验证这个推断: 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的时候那速度就更快了: 其实这个也在预期内,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`);
建复合索引其实还有个原则,就是区分度高的字段要放在前面。因为复合索引是从左往右去对比,区分区高的字段放前面就能大幅减少后面字段对比的范围,从而让索引的效率最大化。 这就相当于层层过滤器,大家都希望每一层都尽可能多的过滤掉无效数据,而不希望10万行进来的时候到最后一层还是10万行,那前面的过滤就都没意义了。在这个例子中,flag就是一个最强的过滤器,放在前面再合适不过。 不过这也要看实际场景,当查询flag的值不为0时,会引起一定量的回表,我们以4(30行)和1(34132行)做下对比: 真实业务中,flag=0的数据不会超过50行,参考上面的结果,50次回表也就10ms以内,性能依然不错,完全符合要求。 我觉得应用层面允许调整SQL的话,再限制下pre_excutetime的最小时间,就可以算是个最好的解决方案了。 最后上一组图看看优化前后的对比。 nice~ 总结这个例子就是提示大家,索引是个好东西但并不是银弹,加的不好就难免适得其反。 本文涉及到的索引知识点: 索引字段的区分区要足够高,最佳示例就是唯一索引使用索引查询的效率不一定比全表扫描快充分利用索引特点减少回表次数复合索引的最左匹配原则复合索引区分度高的字段放在前面碰到问题要能够具体情况具体分析,索引的使用原则估计很多人都背过,怎么能融会贯通去使用还是需要多思考。 索引不规范,DBA两行泪,珍惜身边每一个帮你调SQL的DBA吧。 |
今日新闻 |
点击排行 |
|
推荐新闻 |
图片新闻 |
|
专题文章 |
CopyRight 2018-2019 实验室设备网 版权所有 win10的实时保护怎么永久关闭 |