慢 SQL 的致胜法宝 | 京东物流技术团队
大促备战，最大的隐患项之一就是慢 SQL，对于服务平稳运行带来的破坏性最大，也是日常工作中经常带来整个应用抖动的最大隐患，在日常开发中如何避免出现慢 SQL，出现了慢 SQL 应该按照什么思路去解决是我们必须要知道的。本文主要介绍对于慢 SQL 的排查、解决思路，通过一个个实际的例子深入分析总结，以便更快更准确的定位并解决问题。
解决步骤
step1、观察 SQL
出于一些历史原因有的 SQL 查询可能非常复杂，需要同时关联非常多的表，使用一些复杂的函数、子查询，这样的 SQL 在项目初期由于数据量比较少，不会对数据库造成较大的压力，但是随着时间的积累以及业务的发展，这些 SQL 慢慢就会转变为慢 SQL，对数据库的性能产生一定的影响。
对于这样的 SQL，建议先了解业务场景，梳理关联关系，尝试将 SQL 拆解为几个简单的小 SQL，在内存中关联组合。
step2、分析问题
大家在分析慢 SQL 时最常用的工具肯定是 explain 语句，如下是 explain 语句的执行输出。
一般情况下我们最需要关注的指标有 type、possible_keys、key、rows、extra 几项。
type 为连接类型，有如下几种取值，性能从好到坏排序如下：
system：该表只有一行（相当于系统表），system 是 const 类型的特例
const：针对主键或唯一索引的等值查询扫描, 最多只返回一行数据. const 查询速度非常快, 因为它仅仅读取一次即可
eq_ref：当使用了索引的全部组成部分，并且索引是 PRIMARY KEY 或 UNIQUE NOT NULL 才会使用该类型，性能仅次于 system 及 const。
ref：当满足索引的最左前缀规则，或者索引不是主键也不是唯一索引时才会发生。如果使用的索引只会匹配到少量的行，性能也是不错的。
TIPS
最左前缀原则，指的是索引按照最左优先的方式匹配索引。比如创建了一个组合索引(column1, column2, column3)，那么，如果查询条件是：
WHERE column1 = 1、WHERE column1= 1 AND column2 = 2、WHERE column1= 1 AND column2 = 2 AND column3 = 3 都可以使用该索引；
WHERE column1 = 2、WHERE column1 = 1 AND column3 = 3 就无法匹配该索引。
fulltext：全文索引
ref_or_null：该类型类似于 ref，但是 MySQL 会额外搜索哪些行包含了 NULL。这种类型常见于解析子查询
index_merge：此类型表示使用了索引合并优化，表示一个查询里面用到了多个索引
unique_subquery：该类型和 eq_ref 类似，但是使用了 IN 查询，且子查询是主键或者唯一索引。例如：
index_subquery：和 unique_subquery 类似，只是子查询使用的是非唯一索引
range：范围扫描，表示检索了指定范围的行，主要用于有限制的索引扫描。比较常见的范围扫描是带有 BETWEEN 子句或 WHERE 子句里有>、>=、<、<=、IS NULL、<=>、BETWEEN、LIKE、IN()等操作符。
index：全索引扫描，和 ALL 类似，只不过 index 是全盘扫描了索引的数据。当查询仅使用索引中的一部分列时，可使用此类型。有两种场景会触发：
如果索引是查询的覆盖索引，并且索引查询的数据就可以满足查询中所需的所有数据，则只扫描索引树。此时，explain 的 Extra 列的结果是 Using index。index 通常比 ALL 快，因为索引的大小通常小于表数据。
按索引的顺序来查找数据行，执行了全表扫描。此时，explain 的 Extra 列的结果不会出现 Uses index。
ALL：全表扫描，性能最差。
possible_keys
展示当前查询可以使用哪些索引，这一列的数据是在优化过程的早期创建的，因此有些索引可能对于后续优化过程是没用的。
key
表示 MySQL 实际选择的索引，重点需要注意 Using filesort 和 Using temporary，前者代表无法利用索引完成排序操作，数据较少时从内存排序，否则从磁盘排序，后者 MySQL 需要创建一个临时表来保存结果。
通过 EXPLAIN 可以初步定位出 SQL 是否使用索引，使用的索引是否正确，排序是否合理、索引列区分度等情况，通过这些基本就可以定位出绝大部分问题。
step3、指定方案
若无法从 SQL 本身解决可以根据业务场景和数据分布情况等因素合理制定修改方案。
案例展示
1、本 SQL 主要存在两个问题，一个是查询结果数据量较大，大约 2W 条数据，其次就是根据非索引字段 oil_gun_price 排序，造成 filesort。有两种修改选择，一种是改造为分页查询，根据 id 升序排序，根据 id 偏移避免深分页的问题，另外就是直接获取符合条件的全量数据，不指定排序方式，然后在内存中排序即可。像这样的场景尽量不要使用数据库进行排序，除非可以直接利用索引进行排序，不然尽量选择一次性或者分页的方式将所有数据加载到内存后在进行排序。
2、本 SQL 主要的问题在于在关联查询中使用了子查询进行拼接，子查询中条件较少，相当于先执行了一次全表扫描，将第一次查询的结果加载到内存中再去执行关联，查询时长 2.63 秒，是比较常见的导致慢 SQL 的原因，应该尽量避免使用，这里选择子查询改为关联查询，最后执行时长 0.71 秒
3、本 SQL 比较典型，是非常容易被忽视但又经常出现的慢 SQL。SQL 中 carrier_code 和 trader_code 都有索引，但是最后使用了 update_time 索引，这是由于 MYSQL 优化器优化后的结果，可能导致实际执行时使用的索引跟预想的不一样，这种 SQL 常见于在使用共用的查询 SQL，实际上很多情况下并不能完全适用，例如排序方式，查询字段，返回条数等等，因此还是建议不同的业务逻辑使用自己单独定义的 SQL。解决方式可以使用 force_index 根据情况指定索引或者修改排序方式
对于 limit N 带有 group by ，order by 的 SQL 语句 (order by 和 group by 的字段有索引可以使用)，MySQL 优化器会尽可能选择利用现有索引的有序性，减少排序--这看起来是 SQL 的执行计划的最优解，但是实际上效果可能会南辕北辙，相信大家都遇到过很多案例中 SQL 执行计划选择 order by id 的索引进而导致全表扫描，而不是利用 where 条件中的索引查找过滤数据，这样就可能导致查询很低效(当然查询也可能很高效，这个跟表中数据的具体分布有关)
order by limit 优化能起到正面作用的前提是，首先假设有序索引和无序索引是不相关的，其次假设数据是均匀分布的。
这两个假设是估算通过排序索引来访问 cost 的前提(但是现实生产环境中这两个假设在绝大多数场景中都是不成立的，所以就造成多数场景下索引选择错误)，有可能会遇到通过条件索引过滤执行时间为几十毫秒，但是通过索引排序扫描耗时 1 小时的情况，可以认为是 MySQL 的一个 bug。
4、SQL 中的 limit 也是经常导致慢 SQL 的原因之一，当对 SQL 使用了 limit 进行限制时，如果 SQL 使用的 limit 限制大于剩余的总条数，并且使用的索引条件不能很好的利用上有序的特性，那么 MYSQL 很可能会进行全表扫描。例如下面这个 SQL，SQL 在执行过程中使用了 create_time 索引，但是条件中没有 create_time 作为条件，而 SQL 结果总条数为 6，小于此时 limit 的结果 10，因此 MYSQL 进行了全表扫描，耗时 2.19 秒，而当将 limit 改为 6 时，SQL 执行时长为 0.01 秒，因为当 MYSQL 在查询到 6 条满足条件的结果时就直接返回了，不会再进行全表扫描。因此，当分页查询的数据已经不满一页的情况下，最好手动设置 limit 参数。
5、如下 SQL 表关联过多，导致数据库加载的数据量比较大，可以根据实际情况选择先查出来一张表的数据作为基础数据，再根据连表条件把剩下的字段填充上。数据量较大的表不建议关联过多表，可以通过适当冗余字段或者加工宽表代替。
6、本 SQL 使用 update_time 作为时间范围索引，需要注意是否存在热数据过于集中的问题，导致查询数据量非常大，排序条件比较复杂，无法直接通过 SQL 优化解决。一方面需要先解决热数据过于集中的问题，一方面需要根据业务场景优化，比如增加一些默认条件以缩减数据量。
实际开发过程中还有许多从 SQL 本身不好优化的场景，比如查询数据加载过多、表数据量过大、数据倾斜严重等等，尽量根据业务场景进行一些必要的保护措施限制，在不影响业务的情况下寻找替代方案，例如使用 ES 进行查询，不过还是需要根据实际的场景选择不同的方式解决。
7、对于一些较大数据量的表，在进行分页查询的时候其实很快就能返回结果，但是在进行分页 count 总条数时往往很慢，这是因为在分页查询时会有 pageSize 的限制，当 MYSQL 查询到满足条数的数据后就会直接返回，而在进行 count 时则会根据条件全表查询，当条件包含的数据量过大时就会限制 SQL 的性能。这种情况下建议一方面将分页逻辑重写，分离 count 和 selectList，可以考虑应用 ES 作为 count 数据来源，或在某些条件下如果已存在总条数则不再 count，减少分页 count 的次数；另一方面限制分页深度避免出现深分页。
总体优化原则
创建合适的索引
减少不必要访问的列
使用覆盖索引
语句改写
数据结转
选择合适的列进行排序
适当的列冗余
SQL 拆分
适当应用 ES
作者：京东物流 李文浩
来源：京东云开发者社区 自猿其说 Tech 转载请注明来源
版权声明: 本文为 InfoQ 作者【京东科技开发者】的原创文章。
原文链接:【http://xie.infoq.cn/article/d025c94cd89d131efede187c8】。文章转载请联系作者。
拥抱技术，与开发者携手创造未来！ 2018-11-20 加入
我们将持续为人工智能、大数据、云计算、物联网等相关领域的开发者，提供技术干货、行业技术内容、技术落地实践等文章内容。京东云开发者社区官方网站【https://developer.jdcloud.com/】，欢迎大家来玩
评论