搞定面试官 - 可以讲一下你平时是如何进行 SQL 性能分析的嘛？

本文链接：https://blog.csdn.net/Zbylant/article/details/126334113

SQL 性能分析

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

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

SQL 执行频率

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

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

file

关于具体的查询效率，我们可以通过查询数据库的慢 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 查看具体各个阶段的耗时

file

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

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

explain 执行计划

结果输出展示：

file

id

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

select_type

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

file

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：全表扫描，性能最差。