慢sql优化总结

项目中由于前人写的上古SQL,加上业务数据量逐渐增加，导致SQL效率急剧下降，故结合阿里云的PolarDB产品分析相关线上慢SQL日志，进而优化解决

经整理分析，此次sql性能问题主要凸显以下几点：

     1.数据量巨大，存在单表数据量超过1亿的数据，且很多单表体积在几千万的数据互相之间关联查询

     2.历史索引创建问题，新业务产生时未及时结合全场景进行索引更新，频繁创建新的索引，各个索引的排列未使得效率最大化且浪费磁盘空间，且多索引情况下判断索引命中索引需要更多的时间，极有可能锁表

     3.sql问题，未传参，表连接不规范，查询数据冗余等

     4.代码问题，从代码逻辑层面可以进一步优化

 

优化后，平均执行效率由动不动好几秒，十几秒甚至更长时间减少到平均0.5s内，总结相关经验后记录如下：

慢SQL优化总结：
   1.相关小表直接join或者left join 在explain后会发现出现file sort using index/where等情况  file sort主要通过（语句）提前或者破坏file sort  后几种主要看索引
   2.相关查询慢大多是数据量大且索引没有建好  建议联合索引命中规则建好  （误区：最左原则和mapper.xml中的where条件顺序毫无关联 mysql会通过索引自动查找 但是要小心索引失效）
   3.相关查询可以提前传入某些条件 这样对于一些大表数据可以直接筛选 减少表与表之间的连接数
   4.相关查询可以采用分页限制大小，针对某些count 可以视情况去除相关表连接 且相关表连接的on条件需要斟酌 正确表连接会提升很高效率
   5.针对大表的索引建立要谨慎，特别是联合索引 相关表数据越多越消耗相关资源
   6.mysql会出现索引使用出错的情况 可以force index使用相关其他索引 但是要做测试以免自己出错
   7.mysql关键字使用 例如in和exist 前者适合小表 后者适合大表 不同关联场景使用需要注意区分 like中的% 前后使用不同会导致索引失效 列（左列）上的一些运算亦是如此
   8.和程序相关，程序可以提前避免或者从逻辑上限制
   9.不要使用*，尽量查询出使用的参数，可以避免回表次数提升效率
   10.针对复杂，冗长的sql 首先理清业务逻辑 是否可以拆解 关联表是否可以删减 层层分析sql表中的问题，定位原因 建好索引

其实还是有一些sql优化起来很困难

     1.首先表数据量已经超级多了（1亿+） 在未考虑分库分表情况下 新建或者删除相关索引都会导致效率下降，且此时新建索引搞不好会有意外情况发生，这就是积重难返的上古SQL

     2.有些sql慢是因为表中存在重复数据，这个时候要考虑唯一索引的限制，我碰到的是一张表筛选后存在1000+组以上的数据重复，由于业务关系，删除重复行需要大量时间，导致问题一直存在（唯一索引需保证数据库数据在该条件下无重复数据才会创建成功）

最后说一下PolarDB吧

     1.还是得结合实际情况，像阿里云上的慢SQL规则是执行时间大于等于1s则会记录，但是当你拿到本地执行后会发现很快，这就要排查数据库的锁等待时间了（上图中的平均锁等待时间和最大所等待时间我没见过有值）

     2.SQL洞察主要起到排查执行失败日志的作用，优化前我这边动不动几十上百秒的（真不是危言耸听），结果数据拿下来一跑，哎嘿，嗖嗖的，我这边主要是排查一个月内的慢SQL日志

     3.目前只支持查询一个月内的慢SQL日志，所以我无法截图那些很吓人的执行时间的SQL

越努力越幸运 冲呀