a站|慢sql治理经典案例分享二次元|新浪微博

文章图片

文章图片

文章图片

菜鸟供应链金融慢sql治理已经有一段时间，自己负责的应用持续很长时间没有慢sql告警，现阶段在推进组内其他成员治理应用慢sql 。这里把治理过程中的一些实践拿出来分享下。
一全表扫描 1 案例
SELECT count(*) AS tmp_count FROM ( SELECT * FROM `XXX_rules` WHERE 1 = 1 ORDER BY gmt_create DESC ) a 2 溯源
在分页查询治理的文章里已经介绍过我们系统旧的分页查询逻辑，上面的查询sql明显就是分页查询获取总记录数，通过XXX_rules表的分页查询接口溯源，找到发起调用的页面是我们小二后台的一个操作商家准入的页面，页面打开后直接调用分页查询接口，除了分页参数，不传入其他任何查询参数，导致扫描全表。
3 分析
灵魂拷问：为什么要扫描全表？全表数据展示到页面，花里胡哨的数据有用吗？
调研：和经常使用这个页面的运营聊后了解到，打开页面查询出的全表数据对运营是没有用的，他们根本不看这些数据。运营的操作习惯是拿到商家id ，在页面查询框中输入商家id ，查到商家数据后进行操作。
4 解决方案
由此优化方案就很明朗了：打开页面时不直接查询全量数据，等运营输入商家id后，将商家id作为参数进行查询。 XXX_rules表中，商家id这一常用查询条件设置为索引，再结合分页查询优化，全表扫描慢sql得以解决。
优化后的小二后台页面如下：
【a站|慢sql治理经典案例分享】
打开页面时未查询任何数据，查询条件商家账户为必填项。
优化后的sql为:
SELECT count(*) AS tmp_count FROM ( SELECT * FROM `xxx_rules` WHERE 1 = 1 AND `rule_value` = '2928597xxx' ) a 执行EXPLAIN得到结果如下：

可以看到命中了索引，扫描行数为3 ，查询速度明显提高。
5 思考
扫描全表治理简单来说就是加入查询条件，命中索引，去除全表扫描查询，虽然有些粗暴，但并不是没有道理。实际业务场景中，很少有要扫描全表获取全部数据的情况，限制调用上游必须传入查询条件，且该查询条件能命中索引，能很大程度上避免慢sql 。
另外，再引申下， XXX_rules初始的用意是准入表，记录金融货主维度的准入情况，最多也就几千条数据，但是很多同事将这张表理解为规则表，写入很多业务相关规则，导致这个表膨胀到一百多万条数据，表不clean了。这就涉及到数据表的设计使用，明确表的使用规范，不乱写入数据，能给后期维护带来很大的便利。
二索引混乱 1 示例

2 分析
除了时间、操作人字段， XXX_rules表就rule_name、rule_value、status、product_code四个字段，表的索引对这四个字段做各种排列组合。存在如下问题：
1、rule_name离散度不高，放在索引首位不合适；
2、前三个索引重合度很高；
显然是对索引的命中规则不够了解。 XXX_rules表很多业务有定时任务对其写入删除，索引多、混乱，对性能有很大的影响。
高性能的索引有哪些，再来回顾下：
1、独立的列：索引列不能是表达式的一部分；
2、选择区分度高的列作为索引；
3、选择合适的索引列顺序：将选择性高的索引列放在最前列；
4、覆盖索引：查询的列均在索引中，不需要回查聚簇索引；
5、使用索引扫描来做排序;
6、在遵守最左前缀的原则下，尽量扩展索引，而不是创建索引。
但凡记得第3和6规则，也不至于把索引建成这样。
3 治理
对索引进行整合如下：

系统中有很多任务拉取整个产品下的准入记录，然后进行处理，所以将区分度较高的product_code放在索引首位，然后添加rule_name、status字段到索引里，进一步过滤数据，减少扫描行数，避免慢sql 。针对常用的rule_value查询条件，可以命中UK ，因此不用单独建立索引。
三非必要排序 1 问题描述
很多业务逻辑中，需要拉取满足某个条件的记录列表，查询的sql语句带有order by ，记录比较多的情况，排序代价往往很大，但是查询出来的记录是否有序对业务逻辑没有影响，比如分页治理里讨论的count语句，只需要统计条数， order by对条数没有影响，再比如查出记录列表后，不依赖记录的顺序遍历列表处理数据，这时候order by多此一举。