SQL 优化路在何方

上回说到 MySQL 索引的使用，是不是有了索引就一定能让查询效率变高呢？很显然不是的。有很多情况会影响 SQL 执行效率。先不说数据库服务的硬件配置，以及执行 SQL 时那一瞬间的系统负载情况，今天主要探讨如何优化 SQL 本身。

MySQL 的慢查询日志是 MySQL 提供的一种日志记录，它用来记录在 MySQL 中响应时间超过阀值的语句，具体指运行时间超过long_query_time 值的 SQL，则会被记录到慢查询日志中。long_query_time 的默认值为 10，意思是运行 10S 以上的语句。默认情况下，MySQL 数据库并不启动慢查询日志，需要我们手动来设置这个参数。如果不是调优需要的话，一般不建议启动该参数，因为开启慢查询日志会或多或少带来一定的性能影响。

即便有性能消耗，通常线上业务数据库都会打开慢查询日志，slow_query_log 可以设置是否开启慢查询日志，1 表示开启，0 表示关闭。long_query_time 根据业务需求设置不同阈值，OLTP 业务下 1S 以上的 SQL 可以认为是慢 SQL。

当我们通过慢查询日志获取到 SQL 后，通常会从以下几个方面分析：

1. SQL 是不是过于复杂

2. 数据量是不是过大

3. SQL 执行计划

OLTP 场景下，强烈建议单条 SQL 中关联的表不要超过两张，并且尽量使用主键 join ，至少也是通过索引做等值关联。即便是 OLAP 场景下关联的表也不宜过多。

如果 SQL 中用到的表数量巨大，比如单表数据量上亿。这时候 SQL 再怎么优化性能都难有质的提升，应该先通过分库分表来降低单表的大小，下一步才是 SQL 优化。

优化 SQL 通常会用到 explain 关键字来查看 SQL 的执行计划。例如如下 SQL:

explain
select
      c.number,
      d.mobile,
      d.name,
      f.created_time,
      f.biz_number,
      d.ip,
      d.app
from
  (
    select
      b.*
    from
      (
        select
          a.number,
          a.order_number,
          a.user_id,
          a.created_time
        from
          Bill a
        where
          a.loan_type not in ('P', 'M')
        group by
          a.order_number
        order by
          a.created_time
      ) b
    group by
      b.number
  ) c
  left join (
    SELECT
      *
    FROM
      ActivationLog
    where
      status in ('success')
    group by
      number
  ) d on c.number = d.number
  LEFT JOIN User m
    on d.mobile = m.mobile
  LEFT JOIN (
    select
      id,
      no,
      name,
      mobile,
      order_number,
      created_time,
      created_ip,
      biz_number
    from
      LoanOrder
  ) f on c.order_number = f.order_number
where
  f.created_time >= '2019-11-01'
  and f.created_time <= '2020-07-10';
复制

简单介绍一下结果表常用字段的含义：

• id: 表示查询中执行 select 子句或者操作表的顺序，id 的值越大，代表优先级越高，越先执行。相同情况下从上到下依次执行。

• select_type: 表示 select 查询的类型，主要是用于区分各种复杂的查询，例如：普通查询、联合查询、子查询等。

• UNION RESULT：代表从 union 的临时表中读取数据，而 table 列的<union1,4> 表示用第一个和第四个select的结果进行 union 操作。

• UNION：如果 union 后边又出现的 select 语句，则会被标记为 union；若 union 包含在 from 子句的子查询中，外层 select 将被标记为 derived。

• DERIVED：表示包含在 from 子句中的子查询的 select，在我们的 from 列表中包含的子查询会被标记为 derived 。

• SUBQUERY：当 select 或 where 列表中包含了子查询，该子查询被标记为：SUBQUERY 。

• PRIMARY：当查询语句中包含任何复杂的子部分，最外层查询则被标记为PRIMARY。

• SIMPLE：表示最简单的 select 查询语句，也就是在查询中不包含子查询或者 union 交并差集等操作。

• type: 查询使用了何种类型，它在 SQL优化中是一个非常重要的指标，以下性能从好到坏依次是：system  > const > eq_ref > ref  > ref_or_null > index_merge > unique_subquery > index_subquery > range > index > ALL。说一下常见的值：

• eq_ref：查询时命中主键 primary key 或者 unique key索引。

• ref：ref 表示使用非唯一性索引，会找到很多个符合条件的行。

• range：使用索引选择行，仅检索给定范围内的行。简单点说就是针对一个有索引的字段，给定范围检索数据。

• index：index 与 ALL 其实都是读全表，区别在于 index 是遍历索引树读取，而 ALL 是遍历所有叶子节点。

• ALL：将遍历全表以找到匹配的行，性能最差，尽量避免。

• key: 查询中实际使用到的索引，若没有使用索引，显示为NULL。

• key_len: 表示 where 条件中查询用到的索引长度（字节数），原则上长度越短越好 。

• possible_keys：表示在 MySQL 中通过哪些索引，能让我们在表中找到想要的记录，一旦查询涉及到的某个字段上存在索引，则索引将被列出，但这个索引并不一定会是最终查询数据时所被用到的索引。

• ref: 常见的有 const，func，null，字段名。

• 当使用常量等值查询，显示 const

• 当关联查询时，会显示相应关联表的关联字段

• 如果查询条件使用了表达式、函数，或者条件列发生内部隐式转换，可能显示为 func

• 其他情况 null

• rows: 以表的统计信息和索引使用情况，估算要找到我们所需的记录，需要读取的行数，一般情况下 rows 值越小越好。

• Extra: 额外的信息会在 Extra 字段显示。

• Using index：select 操作中使用了覆盖索引，通俗一点讲就是查询的列被索引覆盖，使用到覆盖索引查询速度会非常快，SQL 优化中理想的状态。

• Using where：查询时未找到可用的索引，进而通过 where 条件过滤获取所需数据，并不是所有带 where 语句的查询都会显示Using where。

• Using index condition：在 MySQL 5.6 版本后加入的新特性（Index Condition Pushdown）；会先通过索引条件过滤，随后用 WHERE 子句中的其他条件去过滤。

• Using temporary：表示查询后结果需要使用临时表来存储，一般在排序或者分组查询时用到。

• Using filesort：表示无法利用索引完成的排序操作，也就是 ORDER BY 的字段没有索引，通常这样的SQL都是需要优化的。

最后补充两点：

1. 当 possible_keys 有多个，但是执行计划中用到的 key 不是最优的情况下，可以通过 force_index 语句手动指定某张表某个索引。

2. join 算法决定 join 时尽量小表驱动大表，并且尽量所有被驱动表都直接与驱动连接，尽量不要被驱动与被驱动表连接。