慢查询优化步骤

通过慢查日志等定位那些执行效率较低的 SQL 语句

explain 分析SQL的执行计划

  • 需要重点关注 type、rows、filtered、extra

  • type

    • ALL 全表扫描
    • index 索引全扫描

    • index_merge

      • 索引合并
      • Extra中：Using intersect(index1,index2)
      • 如果在WHERE语句中，存在多层嵌套的AND/OR，MySQL可能不会选择最优的方案，可以尝试通过拆分WHERE子句的条件来进行转换：
        1. (x AND y) OR z => (x OR z) AND (y OR z)
        2. (x OR y) AND z => (x AND z) OR (y AND z)

    • range 索引范围扫描，常用语<,<=,>=,between,in 等操作

    • ref 使用非唯一索引扫描或唯一索引前缀扫描，返回单条记录，常出现在关联查询中
    • eq_ref 类似 ref，区别在于使用的是唯一索引，使用主键的关联查询
    • const/system 单条记录，系统会把匹配行中的其他列作为常数处理，如主键或唯一索引查询
    • null MySQL 不访问任何表或索引，直接返回结果
    • 虽然上至下，效率越来越高，但是根据 cost 模型，假设有两个索引 idx1(a, b, c),idx2(a, c)，SQL 为”select * from t where a = 1 and b in (1, 2) order by c”;如果走 idx1，那么是 type 为 range，如果走 idx2，那么 type 是 ref；当需要扫描的行数，使用 idx2 大约是 idx1 的 5 倍以上时，会用 idx1，否则会用 idx2
  • Extra：
    • Using filesort：MySQL 需要额外的一次传递，以找出如何按排序顺序检索行。通过根据联接类型浏览所有行并为所有匹配 WHERE 子句的行保存排序关键字和行的指针来完成排序。然后关键字被排序，并按排序顺序检索行；
    • Using temporary：使用了临时表保存中间结果，性能特别差，需要重点优化；
    • Using index：表示相应的 select 操作中使用了覆盖索引（Coveing Index）,避免访问了表的数据行，效率不错！如果同时出现 using where，意味着无法直接通过索引查找来查询到符合条件的数据；
    • Using index condition：MySQL5.6 之后新增的 ICP，using index condtion 就是使用了 ICP（索引下推），在存储引擎层进行数据过滤，而不是在服务层过滤，利用索引现有的数据减少回表的数据。