菜鸟供应链金融慢SQL治理已经进行了一段时间，我负责的应用已经很久没有出现慢SQL告警了。现阶段，小组中的其他成员正在推动对慢SQL应用程序的治理。下面分享治理过程中的一些实践。1.全表扫描1.CaseSELECTcount(*)AStmp_countFROM(SELECT*FROM`XXX_rules`WHERE1=1ORDERBYgmt_createDESC)a2.溯源我们系统的老分页在分页查询管理一文中已经介绍过查询逻辑，上面的查询sql很明显是分页查询获取记录总数，通过XXX_rules表的分页查询接口追本溯源，发现发起调用的页面是我们中学后台商家运营的页面。页面打开后，直接调用分页查询接口，除了分页参数外，没有传入其他查询参数，导致扫描全表。3.分析灵魂拷问：为什么要全表扫描？页面显示全表数据，花哨的数据有用吗？研究：和经常用这个页面的运营聊天后了解到，打开页面查询的全表数据对运营是有好处的，他们根本不看数据。操作习惯是获取商户id，在页面的查询框中输入商户id，找到商户数据后进行操作。4、解决方案从这个优化方案中可以很明确：打开页面时不要直接查询全量数据，而是在运营商输入商户id后，以商户id为参数进行查询。在XXX_rules表中，将商户id的常用查询条件设置为索引，结合分页查询优化，解决全表扫描慢SQL。优化后的二级后台页面如下：打开页面时未查询任何数据，需要查询条件商户账号。优化后的SQL为：SELECTcount(*)AStmp_countFROM(SELECT*FROM`xxx_rules`WHERE1=1AND`rule_value`='2928597xxx')a执行EXPLAIN得到结果如下：可以看到索引是hitandrowisscanned当数量为3时，查询速度明显提升。5.扫描全表治理的思考简单来说，就是加查询条件，打索引，去掉全表扫描查询，虽然有点粗糙，但也不无道理。在实际业务场景中，很少会扫描整张表来获取所有数据。限制对上游的调用必须传入查询条件，查询条件可以命中索引，很大程度上可以避免慢SQL。另外，推而广之，XXX_rules的初衷是一个访问表，记录金融货主维度的访问情况，最多几千条数据，但是很多同事把这个表理解为规则表，并且写了很多业务相关的规则，导致表膨胀到百万条以上的数据，表也不干净了。这就涉及到数据表的设计和使用，明确表的使用规范，不能乱写数据，这样可以给后期的维护带来很大的方便。2.索引混乱1.示例2.分析XXX_rules表除了time和operator字段外，还有四个字段：rule_name，rule_value，status，product_code。表的索引对这四个字段进行各种排列组合。存在以下问题：rule_name的离散度不高，不宜放在索引的顶部；前三个指标重合度高；显然，索引的命中规则没有很好理解。XXX_rules表中很多业务都有定时任务写入和删除，索引又多又乱，对性能影响很大。什么是高性能索引，我们再回顾一下：独立列：索引列不能是表达式的一部分；选择区分度高的列作为索引；选择合适的索引列顺序：将选择性高的索引列放在末尾Frontcolumn；覆盖索引：查询的列都在索引中，不需要查聚集索引；使用索引扫描进行排序；在遵守最左前缀的原则下，尽量扩展索引，而不是创建索引。只要记住规则3和规则6，就不会这样建立索引。3.治理整合索引如下：系统中有很多任务拉取整个产品下的访问记录，然后进行处理，所以把区分度高的product_code放在索引的最前面，然后加上rule_name和status字段加入索引，进一步过滤数据，减少扫描行数，避免慢SQL。对于普通的rule_value查询条件，可以命中UK，所以不需要单独创建索引。三、非必要排序1、问题描述在很多业务逻辑中，需要拉取满足一定条件的记录列表。查询sql语句有orderby。当记录较多时，排序成本往往很高，但查询结果记录是否有序对业务逻辑没有影响。比如分页管理中讨论的count语句只需要统计记录条数，orderby对记录条数没有影响。比如找到记录列表后，不依赖记录的顺序遍历列表来处理数据，此时orderby就多余了。2、解决方案如果查询sql中没有limit语句，业务处理逻辑不依赖于orderby后列表记录的顺序，则去掉查询sql中的orderby语句。四、粗粒度查询1、问题描述业务中有很多定时任务，扫描某个表中某个产品下的所有数据，并对数据进行处理，例如：SELECT*FROMXXX_rulesWHERErule_name='apf_distributors'ANDstatus='00'ANDproduct_code='ADVANCE'三个查询条件都是低区分度的列。查到27万条数据，加索引意义不大。2.分析实际业务量没有那么大，最多几千条数据，表中的数据是从上游同步过来的，最好的办法是让上游精简数据，但是因为业务太长，很难找到上游的人来维护太大了，只能想其他办法了。本次定时任务的目的是拉出XXX_rules表中某些产品下的数据，与另一个表中的数据进行比较，对有差异的数据进行更新。每天早上处理，对时效要求不高。那么，是否可以转移处理任务的地方，而不是在应用机器上实时处理这么多条数据呢？3.解法数据是从离线任务odps同步过来的。它是dataWork数据处理平台。创建数据比对任务，将定时任务的数据比对逻辑放在dataWork上，用SQL实现。每天最多有数百条差异数据，结果集中包含高度差异化的列。将差异数据写入odps表，然后数据流回idb。新建一个定时任务，以返回差异数据中差异化程度高的列为查询条件，查询XXX_rules和更新XXX_rules，解决SQL慢的问题。该方法的前提是对数据有效性要求不高，离线产生的结果集较小。5.OR导致索引失败1.CaseSELECTcount(*)FROMXXX_level_reportWHERE1=1ANDEXISTS(SELECT1FROMXXX_white_listtWHERE(t.biz_id=customer_idORcustomer_idLIKECONCAT(t.biz_id,'@%'))ANDt.status=1AND(t.start_time<=CURRENT_TIMEORt.start_timeISNULL)AND(t.end_time>=CURRENT_TIMEORt.end_timeISNULL)ANDt.biz_type='GOODS_CONTROL_BLACKLIST')2.分析上面的查询语句，结果如下：XXX_white_list表以biz_id为索引，这里查询XXX_white_list表以biz_id为查询条件，为什么explain结果中的type是ALL，即扫描全表？索引无效？索引失效的原因有哪些？索引失效场景：OR查询左右缺失索引；复合索引不满足最左匹配原则；喜欢以%开头；需要类型转换；where中的索引列有操作；where中的索引列使用函数；当表扫描较快时（数据较少时），在上述查询语句的第8行，customer_id为XXX_level_report表的字段，漏掉了XXX_white_list表的索引，导致索引失败。3.解决方案这个语句是用condition、enumeration、join花哨的代码拼接在一起的。改起来很麻烦，好像不能直接删除“ORcustomer_idLIKECONCAT(t.biz_id,'@%')”这句话。最后重构这部分的查询语句，去掉or查询，解决慢SQL。
