第24章_mysql性能分析工具的使用

文章目录

1. 数据库服务器的优化步骤
2.查看系统性能参数
3. 统计SQL的查询成本：last_query_cost
4. 定位执行慢的 SQL：慢查询日志
- 4.1 开启慢查询日志参数
- 4.2 查看慢查询数目
- 4.3 测试慢sql语句，查看慢日志
- 4.4 系统变量 log_output， log-queries-not-using-indexes， log_slow_admin_statements的作用
- 4.5 慢查询日志分析工具：mysqldumpslow
- 4.6 关闭慢查询日志
- 4.7 删除慢查询日志
5. 分析查询语句：EXPLAIN
- 5.1 简介
- 5.2 执行计划中各字段含义
- - 5.2.1 id
  - 5.2.2 select_type
  - 5.2.3 table
  - 5.2.4 type
  - 5.2.5 possible_keys 和 key
  - 5.2.6 key_len
  - 5.2.7 ref
  - 5.2.8 rows
  - 5.2.9 Extra
- 5.3 实例分析

在数据库调优中，我们的目标就是响应时间更快，吞吐量更大。利用宏观的监控工具和微观的日志分析可以帮我们快速找到调优的思路和方式

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

当我们遇到数据库调优问题的时候，该如何思考呢？这里把思考的流程整理成下面这张图。
整个流程划分成了观察（Show status）和行动（Action）两个部分。字母 S 的部分代表观察（会使用相应的分析工具），字母 A 代表的部分是行动（对应分析可以采取的行动）
在这里插入图片描述

可以通过观察了解数据库整体的运行状态，通过性能分析工具可以让我们了解执行慢的SQL都有哪些，查看具体的SQL 执行计划，甚至是SQL执行中的每一步的成本代价，这样才能定位问题所在，找到了问题，再采取相应的行动

详细解释一下这张图:
首先在S1部分，需要观察服务器的状态是否存在周期性的波动。如果存在周期性波动，有可能是周期性节点的原因，比如双十一、促销活动等。这样的话，可以通过A1这一步骤解决，也就是加缓存，或者更改缓存失效策略。

如果缓存策略没有解决，或者不是周期性波动的原因，就需要进一步分析查询延迟和卡顿的原因。接下来进入S2这一步，需要开启慢查询。慢查询可以帮我们定位执行慢的SQL语句。可以通过设置long_query_time参数定义“慢""的阈值，如果SQL执行时间超过了long query_time，则会认为是慢查询。当收集上来这些慢查询之后，就可以通过分析工具对慢查询日志进行分析。

在S3这一步骤中，我们就知道了执行慢的SQL，这样就可以针对性地用EXPLAIN查看对应SQL语句的执行计划，或者使用show profile查看SQL中每一个步骤的时间成本。这样就可以了解SQL查询慢是因为执行时间长，还是等待时间长。

如果是SQL等待时间长，就进入A2步骤。在这一步骤中，可以调优服务器的参数，比如适当增加数据库缓冲池等。如果是SQL执行时间长，就进入A3步骤，这一步中需要考虑是索引设计的问题?还是查询关联的数据表过多?还是因为数据表的字段设计问题导致了这一现象。然后在这些维度上进行对应的调整。
如果A2和A3都不能解决问题，需要考虑数据库自身的SQL查询性能是否已经达到了瓶颈，如果确认没有达到性能瓶颈，就需要重新检查，重复以上的步骤。如果已经达到了性能瓶颈，进入A4阶段，需要考虑增加服务器，采用读写分离的架构，或者考虑对数据库进行分库分表，比如垂直分库、垂直分表和水平分表等。

以上就是数据库调忧的流程思路。如果发现执行SQL时存在不规则延迟或卡顿的时候，就可以采用分析工具帮我们定位有问题的SQL，这三种分析工具可以理解是SQL调优的三个步骤:慢查询、EXPLAIN和SHOEW　PROFILING

小结：
在这里插入图片描述

2.查看系统性能参数

在MySQL中，可以使用 SHOW STATUS 语句查询一些MySQL数据库服务器的性能参数、执行频率。
SHOW STATUS语句语法如下：
SHOW [GLOBAL|SESSION] STATUS LIKE '参数';

一些常用的性能参数如下：
Connections：连接MySQL服务器的次数。
Uptime：MySQL服务器的上线时间。 Slow_queries：慢查询的次数。 Innodb_rows_read：Select查询返回的行数
Innodb_rows_inserted：执行INSERT操作插入的行数
Innodb_rows_updated：执行UPDATE操作更新的行数
Innodb_rows_deleted：执行DELETE操作删除的行数
Com_select：查询操作的次数。
Com_insert：插入操作的次数。对于批量插入的 INSERT 操作，只累加一次。 Com_update：更新操作的次数。
Com_delete：删除操作的次数。

演示：

 show status like 'Connections';
/*
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| Connections   | 20    |
+---------------+-------+
*/
#查询服务器工怍时间
show status like 'uptime';
/*
+---------------+--------+
| Variable_name | Value  |
+---------------+--------+
| Uptime        | 382062 |
+---------------+--------+
*/

若查询MySQL服务器的慢查询次数，则可以执行如下语句:

SHOW STATUS LIKE 'Slow_queries' ;
/*
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| Slow_queries  | 0     |
+---------------+-------+
*/

慢查询次数参数可以结合慢查询日志找出慢查询语句，然后针对慢查询语句进行表结构优化或者查询语句优化。再比如，如下的指令可以查看相关的指令情况:

SHOW STATUS LIKE 'Innodb_rows_%';
/*
+----------------------+-------+
| Variable_name        | Value |
+----------------------+-------+
| Innodb_rows_deleted  | 0     |
| Innodb_rows_inserted | 224   |
| Innodb_rows_read     | 214   |
| Innodb_rows_updated  | 0     |
+----------------------+-------+
*/

3. 统计SQL的查询成本：last_query_cost

一条SQL查询语句在执行前需要确定查询执行计划，如果存在多种执行计划的话，MySQL会计算每个执行计划所需要的成本，从中选择成本最小的一个作为最终执行的执行计划。
如果想要查看某条SQL语句的查询成本，可以在执行完这条SQL语句之后，通过查看当前会话中的last_query_cost变量值来得到当前查询的成本。它通常也是评价一个查询的执行效率的一个常用指标。这个查询成本对应的是SQL语句所需要读取的页的数量

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 ）

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

SHOW STATUS LIKE 'last_query_cost';
/*
+-----------------+----------+
| Variable_name | Value |
+-----------------+----------+
| Last_query_cost | 1.000000 |
+-----------------+----------+
*/

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

SELECT student_id, class_id, NAME, create_time FROM student_info
WHERE id BETWEEN 900001 AND 900100;
#运行结果（100 条记录，运行时间为 0.046s ）

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

 SHOW STATUS LIKE 'last_query_cost';/*
+-----------------+-----------+
| Variable_name | Value |
+-----------------+-----------+
| Last_query_cost | 21.134453 |
+-----------------+-----------+
*/

能看到页的数量是刚才的 20 倍，但是查询的效率并没有明显的变化，实际上这两个 SQL 查询的时间基本上一样，就是因为采用了顺序读取的方式将页面一次性加载到缓冲池中，然后再进行查找。虽然页数量（last_query_cost）增加了不少，但是通过缓冲池的机制，并没有增加多少查询时间。
使用场景：它对于比较开销是非常有用的，特别是有好几种查询方式可选的时候

SQL查询是一个动态的过程，从页加载的角度来看，可以得到以下两点结论:
1.位置决定效率。如果页就在数据库缓冲池中，那么效率是最高的，否则还需要从内存或者磁盘中进行读取，当然针对单个页的读取来说，如果页存在于内存中，会比在磁盘中读取效率高很多。
2.批量决定效率。如果从磁盘中对单一页进行随机读，那么效率是很低的（差不多10ms)，而采用顺序读取的方式，批主对页进行读取，平均一页的读取效率就会提升很多，甚至要快于单个页面在内存中的随机读取。
所以说，遇到I/O并不用担心，方法找对了，效率还是很高的。首先要考虑数据存放的位置，如果是经常使用的数据就要尽量放到缓冲池中，其次可以充分利用磁盘的吞吐能力，一次性批量读取数据，这样单个页的读取效率也就得到了提升。

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

MySQL的慢查询日志，用来记录在MySQL中响应时间超过阈值的语句，具体指运行时间超过long_query_time值的SQL，则会被记录到慢查询日志中。long_query_time的默认值为10，意思是运行10秒以上(不含10秒)的语句，认为是超出了最大忍耐时间值。
它的主要作用是，帮助我们发现那些执行时间特别长的SQL查询，并且有针对性地进行优化，从而提高系统的整体效率。当数据库服务器发生阻塞、运行变慢的时候，检查一下慢查询日志，找到那些慢查询，对解决问题很有帮助。比如一条sql执行超过5秒钟，就算慢SQL，希望能收集超过5秒的sql，结合explain进行全面分析。
默认情况下，MySQL数据库没有开启慢查询日志，需要手动来设置这个参数。如果不是调优需要的话，一般不建议启动该参数，因为开启慢查询日志会或多或少带来一定的性能影响。
慢查询日志支持将日志记录写入文件

4.1 开启慢查询日志参数

开启slow_query_log
在使用前，需要先看下慢查询是否已经开启，使用下面这条命令即可:

show variables like 'slow_query_log';
/*
+----------------+-------+
| Variable_name  | Value |
+----------------+-------+
| slow_query_log | OFF   |
+----------------+-------+
*/

能看到slow_query_log=OFF，可以把慢查询日志打开，注意设置变量值的时候需要使用global，否则会报错:
set global slow_query_log='ON';

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

show variables like '%slow_query_log%';
/*
+---------------------+--------------------------------+
| Variable_name       | Value                          |
+---------------------+--------------------------------+
| slow_query_log      | OFF                            |
| slow_query_log_file | /var/lib/mysql/LSLNO1-slow.log |
+---------------------+--------------------------------+
*/

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

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

show variables like '%long_query_time%';
/*
+-----------------+-----------+
| Variable_name   | Value     |
+-----------------+-----------+
| long_query_time | 10.000000 |
+-----------------+-----------+
*/

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

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

补充:配置文件中一并设置参数
如下的方式相较于前面的命令行方式，可以看作是永久设置的方式
修改my.cnf文件，[mysqld]下增加或修改参数long_query_time、slow_query_log 和slow_query_log_file后，然后重启MySQL服务器

[mysqld]
slow_query_log=ON#开启慢查询日志的开关
slow_query_log_file=/var/lib/mysql/atguigu-slow.log#慢查询日志的目录和文件名信息
long_query_time=1 #设置慢查询的阈值为1秒。超出此设定值的SQL即被记录到慢查询日志
log_output=FTLE

如果不指定存储路径，慢查询日志将默认存储到MySQL数据库的数据文件夹下。如果不指定文件名，默认文件名为hostname-slow.log

4.2 查看慢查询数目

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

SHOW GLOBAL STATUS LIKE '%Slow_queries%';
/*
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| Slow_queries  | 0     |
+---------------+-------+
*/

4.3 测试慢sql语句，查看慢日志

试慢sql语句，查看慢日志

mysql> select sleep(2);
+----------+
| sleep(2) |
+----------+
|        0 |
+----------+
1 row in set (2.01 sec)

select sleep(2);表执行暂停2秒的sql语句

然后我们打开慢日志文件，也就是slow_query_log_file的值，即/var/lib/mysql/LSLNO1-slow.log文件，跳转到最后可以看到如下内容

# Time: 2021-02-23T11:38:25.222093Z
# User@Host: root[root] @ localhost [127.0.0.1]  Id:    19
# Query_time: 2.000681  Lock_time: 0.000000 Rows_sent: 1  Rows_examined: 1
SET timestamp=1614080303;
select sleep(2);

Time:Time: 2021-02-23T11:38:25.222093Z 表该条慢sql执行的时间点

User@Host: root[root] @ localhost [127.0.0.1] Id: 19 表该条慢sql的连接账号为root，IP地址为127.0.0.1，Id表连接ID，每次连接该ID加1

Query_time: 2.000681 表执行该sql的总时间为2.000681秒

Lock_time: 0.000000 表锁表时间

Rows_sent: 1 表发送sql结果的行数，只有1行

Rows_examined: 1 表执行该sql语句检查数据库的行数

4.4 系统变量 log_output， log-queries-not-using-indexes， log_slow_admin_statements的作用

log_output=‘FILE’表示将日志存入文件，默认值是’FILE’。log_output='TABLE’表示将日志存入数据库，这样日志信息就会被写入到mysql.slow_log表中。MySQL数据库支持同时两种日志存储方式，配置的时候以逗号隔开即可，如：log_output=‘FILE,TABLE’。日志记录到系统的专用日志表中，要比记录到文件耗费更多的系统资源，因此对于需要启用慢查询日志，又需要能够获得更高的系统性能，那么建议优先记录到文件

mysql> show variables like '%log_output%';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| log_output    | FILE  |
+---------------+-------+
1 row in set (0.01 sec)

log-queries-not-using-indexes：未使用索引的查询也被记录到慢查询日志中（可选项）。如果调优的话，建议开启这个选项。另外，开启了这个参数，其实使用full index scan的sql也会被记录到慢查询日志

mysql> show variables like 'log_queries_not_using_indexes';
+-------------------------------+-------+
| Variable_name                 | Value |
+-------------------------------+-------+
| log_queries_not_using_indexes | OFF   |
+-------------------------------+-------+
1 row in set (0.01 sec)

log_slow_admin_statements表示是否将慢管理语句例如ANALYZE TABLE和ALTER TABLE等记入慢查询日志

mysql> show variables like 'log_slow_admin_statements';
+---------------------------+-------+
| Variable_name             | Value |
+---------------------------+-------+
| log_slow_admin_statements | OFF   |
+---------------------------+-------+
1 row in set (0.01 sec)

4.5 慢查询日志分析工具：mysqldumpslow

日志分析工具mysqldumpslow，mysql官方自带的，只要安装了mysql就可以使用它，可以用来帮助我们分析慢日志文件

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

root@kickseed:/home# mysqldumpslow --help
Usage: mysqldumpslow [ OPTS... ] [ LOGS... ]Parse and summarize the MySQL slow query log. Options are--verbose    verbose--debug      debug--help       write this text to standard output-v           verbose-d           debug-s ORDER     what to sort by (al, at, ar, c, l, r, t), 'at' is defaultal: average lock timear: average rows sentat: average query timec: countl: lock timer: rows sentt: query time  -r           reverse the sort order (largest last instead of first)-t NUM       just show the top n queries-a           don't abstract all numbers to N and strings to 'S'-n NUM       abstract numbers with at least n digits within names-g PATTERN   grep: only consider stmts that include this string-h HOSTNAME  hostname of db server for *-slow.log filename (can be wildcard),default is '*', i.e. match all-i NAME      name of server instance (if using mysql.server startup script)-l           don't subtract lock time from total time

-s 是表示按照何种方式排序，后面可以带如下排序方式

c: 访问次数
l: 锁定时间
r: 返回记录的行数
t: 查询时间
al:平均锁定时间
ar:平均返回记录的行数
at:平均查询时间

-t 是top n的意思，即为返回前面多少条的数据

-g 后边可以写一个正则匹配模式，大小写不敏感的

例子

得到返回记录行数最多的10条SQL语句（行数最多排序需要 -s r ；限制10条需要 -t 10）

mysqldumpslow -s r -t 10 /database/mysql/mysql06_slow.log

得到访问次数最多的10个SQL（访问次数最多排序需要 -s c ；限制10条需要 -t 10）

mysqldumpslow -s c -t 10 /database/mysql/mysql06_slow.log

得到按照执行时间排序的前10条，且sql语句里面含有左连接的查询语句（执行时间排序需要 -s t；限制10条需要 -t 10）

mysqldumpslow -s t -t 10 -g “left join” /database/mysql/mysql06_slow.log

另外建议在使用这些命令时结合 | 和more 使用，否则有可能出现刷屏的情况。

mysqldumpslow -s r -t 20 /mysqldata/mysql/mysql06-slow.log | more

笔者以自己的慢日志为例，做一次查询

mysqldumpslow -s t -t 5 LSLNO1-slow.log 表我要分析LSLNO1-slow.log文件，该文件是mysql的慢日志文件，得到按照执行时间，从大到小排序，最多5条sql语句

root@kickseed:/var/lib/mysql# mysqldumpslow -s t -t 5 LSLNO1-slow.log Reading mysql slow query log from LSLNO1-slow.log
Count: 2  Time=2.00s (4s)  Lock=0.00s (0s)  Rows=1.0 (2), root[root]@localhostselect sleep(N)Count: 3  Time=0.21s (0s)  Lock=0.00s (0s)  Rows=25.0 (75), root[root]@[192.168.54.28]SHOW TABLE STATUS FROM `hyj_player`Count: 1  Time=0.21s (0s)  Lock=0.00s (0s)  Rows=23211.0 (23211), root[root]@[192.168.54.28]SELECT * FROM `hyj_player`.`t_player_basic_data` LIMIT N, NCount: 1  Time=0.20s (0s)  Lock=0.00s (0s)  Rows=24211.0 (24211), root[root]@[192.168.54.28]SELECT * FROM t_player_basic_dataCount: 4  Time=0.02s (0s)  Lock=0.00s (0s)  Rows=1.0 (4), root[root]@[192.168.54.28]SELECT COUNT(*) FROM t_player_basic_data WHERE playerID < N

从返回的结果中可以看到，从最大耗时的2秒，到最小耗时的0.02秒，一共5条记录

4.6 关闭慢查询日志

MySQL服务器停止慢查询日志功能有两种方法：
方式1：永久性方式

[mysqld]
slow_query_log=OFF

或者，把slow_query_log一项注释掉或删除

[mysqld]
#slow_query_log =OFF

重启MySQL服务，执行如下语句查询慢日志功能。

SHOW VARIABLES LIKE ‘%slow%’; #查询慢查询日志所在目录
SHOW VARIABLES LIKE ‘%long_query_time%’; #查询超时时长

方式2：临时性方式
使用SET语句来设置。
（1）停止MySQL慢查询日志功能，具体SQL语句如下

SET GLOBAL slow_query_log=off;SHOW VARIABLES LIKE '%slow%';
/*
+---------------------------+--------------------------------+
| Variable_name             | Value                          |
+---------------------------+--------------------------------+
| log_slow_admin_statements | OFF                            |
| log_slow_extra            | OFF                            |
| log_slow_slave_statements | OFF                            |
| slow_launch_time          | 2                              |
| slow_query_log            | OFF                            |
| slow_query_log_file       | /var/lib/mysql/LSLNO1-slow.log |
+---------------------------+--------------------------------+
*/
#以及
SHOW VARIABLES LIKE '%long_query_time%';
/*
+-----------------+----------+
| Variable_name   | Value    |
+-----------------+----------+
| long_query_time | 1.000000 |
+-----------------+----------+
*/

（2）重启MySQL服务，使用SHOW语句查询慢查询日志功能信息，具体SQL语句如下

#在根目录下重启服务
[root@LSLNO1 mysql]# systemctl restart mysqld
mysql> SHOW VARIABLES LIKE '%long_query_time%';
/*重启后阈值恢复默认值10
+-----------------+-----------+
| Variable_name   | Value     |
+-----------------+-----------+
| long_query_time | 10.000000 |
+-----------------+-----------+
*/

4.7 删除慢查询日志

查询并调优完后该日志没有用了，可以将其删除
使用SHOW语句显示慢查询日志信息，具体SQL语句如下

show variables like 'slow_query_log%';
/*
+---------------------+--------------------------------+
| Variable_name       | Value                          |
+---------------------+--------------------------------+
| slow_query_log      | OFF                            |
| slow_query_log_file | /var/lib/mysql/LSLNO1-slow.log |
+---------------------+--------------------------------+
*/

从执行结果可以看出，慢查询日志的目录默认为MySQL的数据目录，在该目录下手动删除慢查询日志文件即可

[root@LSLNO1 mysql]# rm LSLNO1-slow.log
rm：是否删除普通文件 "LSLNO1-slow.log"？y

使用命令mysqladmin flush-logs 来重新生成查询日志文件，具体命令如下，执行完毕会在数据目录下重新生成慢查询日志文件
mysqladmin -uroot -p flush-logs slow

提示
慢查询日志都是使用mysqladmin flush-logs命令来删除重建的。使用时一定要注意，一旦执行了这个命令，慢查询日志都只存在新的日志文件中，如果需要旧的查询日志，就必须享先备份。

5. 分析查询语句：EXPLAIN

5.1 简介

使用EXPLAIN关键字可以模拟优化器执行SQL查询语句，从而知道MySQL是如何处理你的SQL语句的。分析你的查询语句或是表结构的性能瓶颈。

通过EXPLAIN，我们可以分析出以下结果：

表的读取顺序
数据读取操作的操作类型
哪些索引可能使用
哪些索引被实际使用
表之间的引用
每张表有多少行被优化器查询

使用方式如下：
EXPLAIN +SQL语句
EXPLAIN SELECT * FROM t1

执行计划包含的信息
在这里插入图片描述

5.2 执行计划中各字段含义

5.2.1 id

select查询的序列号，包含一组数字，表示查询中执行select子句或操作表的顺序
id的结果共有3中情况

id相同，执行顺序由上至下
id不同，如果是子查询，id的序号会递增，id值越大优先级越高，越先被执行
id相同不同，同时存在

如上图所示，在id为1时，table显示的是 ,这里指的是指向id为2的表，即t3表的衍生表。

说明：

关注点: id号每个号码。表示一趟独立的查询,一个sql的查询趟数越少越好

5.2.2 select_type

常见和常用的值有如下几种：
在这里插入图片描述
分别用来表示查询的类型，主要是用于区别普通查询、联合查询、子查询等的复杂查询。

SIMPLE 简单的select查询，查询中不包含子查询或者UNION
PRIMARY 查询中若包含任何复杂的子部分，最外层查询则被标记为PRIMARY
SUBQUERY 在SELECT或WHERE列表中包含了子查询
DERIVED 在FROM列表中包含的子查询被标记为DERIVED（衍生），MySQL会递归执行这些子查询，把结果放在临时表中
UNION 若第二个SELECT出现在UNION之后，则被标记为UNION：若UNION包含在FROM子句的子查询中，外层SELECT将被标记为：DERIVED
UNION RESULT 从UNION表获取结果的SELECT

5.2.3 table

指的就是当前执行的表

5.2.4 type

type所显示的是查询使用了哪种类型，type包含的类型包括如下图所示的几种：
在这里插入图片描述
从最好到最差依次是：

system > const > eq_ref > ref > range > index > all

一般来说，得保证查询至少达到range级别，最好能达到ref。

system 表只有一行记录（等于系统表），这是const类型的特列，平时不会出现，这个也可以忽略不计
const 表示通过索引一次就找到了，const用于比较primary key 或者unique索引。因为只匹配一行数据，所以很快。如将主键置于where列表中，MySQL就能将该查询转换为一个常量。

首先进行子查询得到一个结果的d1临时表，子查询条件为id = 1 是常量，所以type是const，id为1的相当于只查询一条记录，所以type为system。
eq_ref 唯一性索引扫描，对于每个索引键，表中只有一条记录与之匹配。常见于主键或唯一索引扫描
ref 非唯一性索引扫描，返回匹配某个单独值的所有行，本质上也是一种索引访问，它返回所有匹配某个单独值的行，然而，它可能会找到多个符合条件的行，所以他应该属于查找和扫描的混合体。
range 只检索给定范围的行，使用一个索引来选择行，key列显示使用了哪个索引，一般就是在你的where语句中出现between、< 、>、in等的查询，这种范围扫描索引比全表扫描要好，因为它只需要开始于索引的某一点，而结束于另一点，不用扫描全部索引。
index Full Index Scan，Index与All区别为index类型只遍历索引树。这通常比ALL快，因为索引文件通常比数据文件小。（也就是说虽然all和Index都是读全表，但index是从索引中读取的，而all是从硬盘读取的）

id是主键，所以存在主键索引
all Full Table Scan 将遍历全表以找到匹配的行

5.2.5 possible_keys 和 key

possible_keys 显示可能应用在这张表中的索引，一个或多个。查询涉及到的字段上若存在索引，则该索引将被列出，但不一定被查询实际使用。

key

实际使用的索引，如果为NULL，则没有使用索引。（可能原因包括没有建立索引或索引失效）
查询中若使用了覆盖索引（select 后要查询的字段刚好和创建的索引字段完全相同），则该索引仅出现在key列表中

5.2.6 key_len

表示索引中使用的字节数，可通过该列计算查询中使用的索引的长度，在不损失精确性的情况下，长度越短越好。key_len显示的值为索引字段的最大可能长度，并非实际使用长度，即key_len是根据表定义计算而得，不是通过表内检索出的。
在这里插入图片描述

5.2.7 ref

显示索引的那一列被使用了，如果可能的话，最好是一个常数。哪些列或常量被用于查找索引列上的值。
在这里插入图片描述

5.2.8 rows

根据表统计信息及索引选用情况，大致估算出找到所需的记录所需要读取的行数，也就是说，用的越少越好
在这里插入图片描述

5.2.9 Extra

包含不适合在其他列中显式但十分重要的额外信息

Using filesort（九死一生）
说明mysql会对数据使用一个外部的索引排序，而不是按照表内的索引顺序进行读取。MySQL中无法利用索引完成的排序操作称为“文件排序”。
在这里插入图片描述
Using temporary（十死无生）
使用了用临时表保存中间结果，MySQL在对查询结果排序时使用临时表。常见于排序order by和分组查询group by。

Using index（发财了）
表示相应的select操作中使用了覆盖索引（Covering Index），避免访问了表的数据行，效率不错。如果同时出现using where，表明索引被用来执行索引键值的查找；如果没有同时出现using where，表明索引用来读取数据而非执行查找动作。
在这里插入图片描述
Using where
表明使用了where过滤

Using join buffer
表明使用了连接缓存,比如说在查询的时候，多表join的次数非常多，那么将配置文件中的缓冲区的join buffer调大一些。

impossible where
where子句的值总是false，不能用来获取任何元组

select tables optimized away
在没有GROUPBY子句的情况下，基于索引优化MIN/MAX操作或者对于MyISAM存储引擎优化COUNT(*)操作，不必等到执行阶段再进行计算，查询执行计划生成的阶段即完成优化。

distinct
优化distinct操作，在找到第一匹配的元组后即停止找同样值的动作

5.3 实例分析

在这里插入图片描述

执行顺序1：select_type为UNION，说明第四个select是UNION里的第二个select，最先执行【select name,id from t2】
执行顺序2：id为3，是整个查询中第三个select的一部分。因查询包含在from中，所以为DERIVED【select id,name from t1 where other_column=’’】
执行顺序3：select列表中的子查询select_type为subquery,为整个查询中的第二个select【select id from t3】
执行顺序4：id列为1，表示是UNION里的第一个select，select_type列的primary表示该查询为外层查询，table列被标记为,表示查询结果来自一个衍生表，其中derived3中的3代表该查询衍生自第三个select查询，即id为3的select。【select d1.name …】
执行顺序5：代表从UNION的临时表中读取行的阶段，table列的< union1,4 >表示用第一个和第四个select的结果进行UNION操作。【两个结果union操作】