MySQL性能分析工具的使用1. 数据库服务器的优化步骤当我们遇到数据库调优问题的时候，该如何思考呢？这里把思考的流程

1. 数据库服务器的优化步骤

当我们遇到数据库调优问题的时候，该如何思考呢？这里把思考的流程整理成下面这张图。

整个流程划分成了观察（Show status）和行动（Action）两个部分。字母 S 的部分代表观察（会使

用相应的分析工具），字母 A 代表的部分是行动（对应分析可以采取的行动）。

2. 查看系统性能参数

在MySQL中，可以使用 SHOW STATUS 语句查询一些MySQL数据库服务器的性能参数、执行频率。

SHOW STATUS语句语法如下：

SHOW [GLOBAL|SESSION] STATUS LIKE '参数';

3.(重要) 统计SQL的查询成本：last_query_cost

我们依然使用第8章的 student_info 表为例：

CREATE TABLE `student_info`
(
    `id`          INT(11) NOT NULL AUTO_INCREMENT,
    `student_id`  INT     NOT NULL,
    `name`        VARCHAR(20) DEFAULT NULL,
    `course_id`   INT     NOT NULL,
    `class_id`    INT(11)     DEFAULT NULL,
    `create_time` DATETIME    DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    PRIMARY KEY (`id`)
) ENGINE = INNODB
  AUTO_INCREMENT = 1
  DEFAULT CHARSET = utf8;

如果我们想要查询 id=900001 的记录，然后看下查询成本，我们可以直接在聚簇索引上进行查找：

SELECT student_id, class_id, NAME, create_time
FROM student_info
WHERE id = 900001;

运行结果（1 条记录，运行时间为 0.042s ）

然后再看下查询优化器的成本，实际上我们只需要检索一个页即可：

如果我们想要查询 id 在 900001 到 9000100 之间的学生记录呢？

SELECT student_id, class_id, NAME, create_time
FROM student_info
WHERE id BETWEEN 900001 AND 900100;

运行结果（100 条记录，运行时间为 0.046s ）：

然后再看下查询优化器的成本，这时我们大概需要进行 20 个页的查询。

你能看到页的数量是刚才的 20 倍，但是查询的效率并没有明显的变化，实际上这两个 SQL 查询的时间

基本上一样，就是因为采用了顺序读取的方式将页面一次性加载到缓冲池中，然后再进行查找。虽然页

数量（last_query_cost）增加了不少，但是通过缓冲池的机制，并没有增加多少查询时间。

使用场景： 它对于比较开销是非常有用的，特别是我们有好几种查询方式可选的时候。

4. 定位执行慢的 SQL：慢查询日志

开启慢查询日志参数

开启slow_query_log

mysql > set global slow_query_log='ON';

然后我们再来查看下慢查询日志是否开启，以及慢查询日志文件的位置：

你能看到这时慢查询分析已经开启，同时文件保存在 /var/lib/mysql/atguigu02-slow.log 文件

中。

修改long_query_time阈值

接下来我们来看下慢查询的时间阈值设置，使用如下命令：

mysql > show variables like '%long_query_time%';

这里如果我们想把时间缩短，比如设置为 1 秒，可以这样设置：

#测试发现：设置global的方式对当前session的long_query_time失效。对新连接的客户端有效。所以可以一并 执行下述语句 
mysql > set global long_query_time = 1; mysql> show global variables like '%long_query_time%'; 
mysql> set long_query_time=1; 
mysql> show variables like '%long_query_time%';

查看慢查询数目

查询当前系统中有多少条慢查询记录

SHOW GLOBAL STATUS LIKE '%Slow_queries%';

测试及分析

测试

从上面的结果可以看出来，查询学生编号为“3455655”的学生信息花费时间为2.09秒。查询学生姓名为

“oQmLUr”的学生信息花费时间为2.39秒。已经达到了秒的数量级，说明目前查询效率是比较低的，下面的小节我们分析一下原因。

分析

show status like 'slow_queries';

慢查询日志分析工具：mysqldumpslow

在生产环境中，如果要手工分析日志，查找、分析SQL，显然是个体力活，MySQL提供了日志分析工具

mysqldumpslow 。

查看mysqldumpslow的帮助信息

mysqldumpslow --help

mysqldumpslow 命令的具体参数如下：

举例：我们想要按照查询时间排序，查看前五条 SQL 语句，这样写即可：

mysqldumpslow -s t -t 5 /var/lib/mysql/atguigu01-slow.log

工作常用参考：

#得到返回记录集最多的10个SQL
mysqldumpslow -s r -t 10 /var/lib/mysql/atguigu-slow.log
#得到访问次数最多的10个SQL
mysqldumpslow -s c -t 10 /var/lib/mysql/atguigu-slow.log
#得到按照时间排序的前10条里面含有左连接的查询语句
mysqldumpslow -s t -t 10 -g "left join" /var/lib/mysql/atguigu-slow.log
#另外建议在使用这些命令时结合 | 和more 使用 ，否则有可能出现爆屏情况
mysqldumpslow -s r -t 10 /var/lib/mysql/atguigu-slow.log | more

5. 查看 SQL 执行成本：SHOW PROFILE

6. 分析查询语句：EXPLAIN

基本语法

EXPLAIN 或 DESCRIBE语句的语法形式如下：

EXPLAIN SELECT select_options 
或者
DESCRIBE SELECT select_options

如果我们想看看某个查询的执行计划的话，可以在具体的查询语句前边加一个 EXPLAIN ，就像这样：

mysql> EXPLAIN SELECT 1;

查看执行计划并不会真的查询，只是展示数据库怎么去查询而已。

EXPLAIN 语句输出的各个列的作用如下：

数据的准备

EXPLAIN各列作用

为了让大家有比较好的体验，我们调整了下 EXPLAIN 输出列的顺序。

1. table

不论我们的查询语句有多复杂，里边儿包含了多少个表，到最后也是需要对每个表进行单表访问的，所

以MySQL规定EXPLAIN语句输出的每条记录都对应着某个单表的访问方法，该条记录的table列代表着该

表的表名（有时不是真实的表名字，可能是简称）。

2. id

union all 就只有两个 id ，因为不需要去重操作

小结:

id如果相同，可以认为是一组，从上往下顺序执行（相当于并列，一起查询，只需要查一次）
在所有组中， id值越大，优先级越高，越先执行
关注点： id号每个号码，表示一趟独立的查询, 一个sql的查询趟数越少越好

3. select_type

SIMPLE

PRIMARY、UNION

SUBQUERY（普通子查询）

DEPENDENT SUBQUERY （相关子查询）

DERIVED

4. partitions (可略)

5. type ☆（重要）

完整的访问方法如下： system（Hash索引）， const （唯一索引+等值匹配）， eq_ref ， ref ， fulltext ， ref_or_null ，

index_merge ， unique_subquery ， index_subquery ， range ， index ， ALL 。

system （类似Hash索引的，基本上直接就能定位到的）

const（找到就马上返回，注意点：唯一索引、等值匹配、单表）

key2不是唯一索引，那么type就是ALL（全表扫描）

eq_ref （唯一索引、等值匹配、多表）

ref（索引 + 与数据类型一样的常量等值匹配）

ref_or_null

index_merge

range（范围级别）

index（索引覆盖）

ALL（没有索引）

小结：

结果值从最好到最坏依次是： system > const > eq_ref > ref > fulltext > ref_or_null > index_merge > unique_subquery > index_subquery > range > index > ALL 其中比较重要的几个提取出来（见上图中的蓝色）。

SQL 性能优化的目标：至少要达到 range 级别，要求是 ref 级别，最好是 consts级别。（阿里巴巴

开发手册要求）

6. possible_keys和key

mysql> EXPLAIN SELECT * FROM s1 WHERE key1 > 'z' AND key3 = 'a';

9. rows ☆

10. filtered

主要看多表连接

11. Extra ☆

Impossible WHERE

mysql> EXPLAIN SELECT * FROM s1 WHERE 1 != 1;

Using where

No matching min/max row

Using index

加上主键值也算是索引覆盖

MySQL性能分析工具的使用