SQL 性能分析

大家好，我是程序员啊粥，这段时间一直在分享 MySQL 索引系列的文章，我们学会了B+ 树索引模型，以及索引长度的计算、明明使用 Delete 把数据删除了，但是为什么磁盘上的数据文件大小没变？等内容，今天开始我们学习 SQL 的优化。

说起 SQL 优化，我们需要知道一个 SQL 的执行频率，假如说你有一条慢 SQL，好几个月才执行一次，那我觉得你其实也没啥花费精力优化它的必要，毕竟执行频率太低，投入产出比不足。

SQL 执行频率

关于查询 SQL 执行频率，我们可以使用 show global status like 'Com___'，（这后边是 7 个下划线）,这条命令可以显示当前数据库中增删改查等各个语句的使用次数，可以看我，我这个库中，大量的执行语句都是 select 语句，其他语句非常少。

那说明这个库中的查询时比较多的，所以我们需要额外关注查询的效率。

关于具体的查询效率，我们可以通过查询数据库的慢 SQL 日志来查询。

慢查询日志

• 慢查询日志是否开启：show variables like 'slow_query_log'
• 开启慢日志：set global slow_query_log = 1; （只对当前会话生效，全局生效需要修改 my.conf 配置文件）
• 设置慢查询阈值：set global long_query_time = 4
• 之后就可以在 slow.log 文件中查询到执行的慢 SQL。

这部分基本掌握这几个命令就可以了，我们可以在临时会话中开启慢 SQL 日志，然后执行对应的 SQL 语句来记录日志。

慢查询日志可以帮我们记录具体的慢查询语句，但是为什么慢它是没发告诉我们的，因此，我们还需要借助其他的一些命令来帮助我们具体慢的原因。

使用 MySQL profiling 功能剖析单条查询

• show profiles 能在做 SQL 优化时帮助我们了解耗时具体耗在了哪里。
• show prifile for query id 查看具体各个阶段的耗时

这两条命令结合在一起，可以明确告诉我们这条 SQL 在执行中，到底耗时在那一步，比如是某个子查询或者 Server 层数据传输等具体原因。

得出时间消耗在那个环节之后，我们便可以使用具体的执行计划来进行针对性的优化，下边着重介绍一下关于 SQL 执行计划的使用。

explain 执行计划

结果输出展示：

id

该语句的唯一标识。如果 explain 的结果包括多个 id 值，则数字越大越先执行；而对于相同 id 的行，则表示从上往下依次执行。

select_type

查询类型，有如下几种取值：

table

表示当前这一行正在访问哪张表，如果 SQL 定义了别名，则展示表的别名

partitions

当前查询匹配记录的分区。对于未分区的表，返回 null

type

连接类型，有如下几种取值，性能从好到坏排序 如下：

• system：该表只有一行（相当于系统表），system是const类型的特例
• const：针对主键或唯一索引的等值查询扫描, 最多只返回一行数据. const 查询速度非常快, 因为它仅仅读取一次即可
• eq_ref：当使用了索引的全部组成部分，并且索引是 PRIMARY KEY 或 UNIQUE NOT NULL 才会使用该类型，性能仅次于 system 及 const。
• ref：当满足索引的最左前缀规则，或者索引不是主键也不是唯一索引时才会发生。如果使用的索引只会匹配到少量的行，性能也是不错的。
• 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 实际选择的索引

key_len

索引使用的字节数。由于存储格式，当字段允许为 NULL 时，key_len 比不允许为空时大 1 字节。

ref

表示将哪个字段或常量和 key 列所使用的字段进行比较。

如果 ref 是一个函数，则使用的值是函数的结果。要想查看是哪个函数，可在 EXPLAIN 语句之后紧跟一个 SHOW WARNING 语句。

rows

MySQL 估算会扫描的行数，数值越小越好。

filtered

表示符合查询条件的数据百分比，最大 100。用 rows × filtered 可获得和下一张表连接的行数。例如rows = 1000，filtered = 50%，则和下一张表连接的行数是 500。

以上就是关于 explain 执行计划结果中的字段说明，具体的含义很多都是我从官网直接拿过来的，比较枯燥，当然也不需要你死记硬背，只要你在 SQL 优化过程中，有意识的使用 explain 分析它的执行计划，然后来查阅一下每个字段是什么含义，最后针对具体的问题进行能进行优化，这就可以了。

关于 explain 的具体使用，我会在下一篇文章中进行实战演练，今天就单纯介绍一下这些字段的含义，你有个大概印象就可以。