`mysql`命令

`show processlist`命令

SHOW PROCESSLIST Statement

如果没有 FULL 关键字，SHOW PROCESSLIST 只会在Info字段中显示每个语句的前 100 个字符。

sql

show processlist;

SHOW PROCESSLIST输出包含以下列：

Id
连接标识符。此值与 INFORMATION_SCHEMA PROCESSLIST 表的 ID 列中显示的值、性能模式线程表的 PROCESSLIST_ID 列中显示的值以及线程内的 CONNECTION_ID() 函数返回的值相同。
User
发出语句的 MySQL 用户。系统用户值是指服务器生成的非客户端线程，用于内部处理任务，例如，延迟行处理程序线程或副本主机上使用的 I/O（接收器）或 SQL（应用程序）线程。对于系统用户，Host 列中未指定主机。未经身份验证的用户是指已与客户端连接关联但尚未对客户端用户进行身份验证的线程。event_scheduler 是指监视计划事件的线程
Host
发出语句的客户端的主机名（系统用户除外，因为系统用户没有主机）。TCP/IP 连接的主机名以 host_name:client_port 格式报告，以便于确定哪个客户端正在执行什么操作。
db
线程的默认数据库，如果未选择则为 NULL。
Command
线程代表客户端执行的命令类型，如果会话处于空闲状态，则为 Sleep。此列的值对应于客户端/服务器协议的 COM_xxx 命令和 Com_xxx 状态变量。
Time
线程处于当前状态的时间（以秒为单位）。对于副本 SQL 线程，该值是上次复制事件的时间戳与副本主机的实际时间之间的秒数。
State
指示线程正在执行的操作、事件或状态。
大多数状态对应于非常快速的操作。如果线程在给定状态下停留数秒，则可能存在需要调查的问题。
Info
线程正在执行的语句，如果未执行任何语句，则为 NULL。该语句可能是发送到服务器的语句，或者如果该语句执行其他语句，则为最内层的语句。例如，如果 CALL 语句执行正在执行 SELECT 语句的存储过程，则 Info 值显示 SELECT 语句。

`show profiles`和`show profile`命令

SHOW PROFILE Statement

Show Profiles并不是针对全局的，而是针对当前会话（session）的。这意味着，当你在MySQL中启用并使用了Show Profiles功能时，它只会记录并分析当前会话中执行的SQL语句的资源消耗情况，而不会影响到其他会话或整个MySQL实例。

具体来说，Show Profiles是MySQL提供的一个工具，可以用来分析SQL语句执行的资源消耗情况，如IO、上下文切换、CPU、内存等，从而帮助进行SQL调优。然而，这个功能默认是关闭的，并且它只会在当前会话中记录最近执行的SQL语句的资源消耗情况，数量通常是有限的（如最近15次）。

查看当前数据库是否支持profiling

sql

select @@have_profiling;

判断profiling开关是否开启

sql

select @@profiling;

开启profiling功能

sql

set profiling=1;

查看是否成功开启profiling

sql

select @@profiling;

SHOW PROFILES 显示发送到服务器的最新语句列表。列表的大小由 profiling_history_size 会话变量控制，其默认值为 15。最大值为 100。将该值设置为 0 可实际禁用分析

sql

# 显示profiling_history_size会话变量
select @@profiling_history_size;

# 设置profiling_history_size会话变量
set @@profiling_history_size=100;

查看每一条SQL的耗时情况

bash

mysql> show profiles;
+----------+------------+-----------------------+
| Query_ID | Duration   | Query                 |
+----------+------------+-----------------------+
|        1 | 0.00043900 | select @@profiling    |
|        2 | 0.00028625 | select * from tb_user |
|        3 | 0.00050750 | SELECT DATABASE()     |
|        4 | 0.00485225 | show databases        |
|        5 | 0.00383850 | show tables           |
|        6 | 0.00097625 | select * from tb_user |
+----------+------------+-----------------------+
6 rows in set, 1 warning (0.00 sec)

查看指定query id的SQL语句各个阶段的耗时情况

shell

mysql> show profile for query 6;
+--------------------------------+----------+
| Status                         | Duration |
+--------------------------------+----------+
| starting                       | 0.000345 |
| Executing hook on transaction  | 0.000012 |
| starting                       | 0.000024 |
| checking permissions           | 0.000014 |
| Opening tables                 | 0.000076 |
| init                           | 0.000017 |
| System lock                    | 0.000029 |
| optimizing                     | 0.000011 |
| statistics                     | 0.000036 |
| preparing                      | 0.000038 |
| executing                      | 0.000176 |
| end                            | 0.000011 |
| query end                      | 0.000008 |
| waiting for handler commit     | 0.000018 |
| closing tables                 | 0.000018 |
| freeing items                  | 0.000039 |
| cleaning up                    | 0.000105 |
+--------------------------------+----------+
17 rows in set, 1 warning (0.00 sec)

查看指定query id的SQL cpu的使用情况

bash

mysql> show profile cpu for query 6;
+--------------------------------+----------+----------+------------+
| Status                         | Duration | CPU_user | CPU_system |
+--------------------------------+----------+----------+------------+
| starting                       | 0.000345 | 0.000083 |   0.000108 |
| Executing hook on transaction  | 0.000012 | 0.000004 |   0.000006 |
| starting                       | 0.000024 | 0.000011 |   0.000014 |
| checking permissions           | 0.000014 | 0.000006 |   0.000008 |
| Opening tables                 | 0.000076 | 0.000045 |   0.000059 |
| init                           | 0.000017 | 0.000008 |   0.000010 |
| System lock                    | 0.000029 | 0.000012 |   0.000016 |
| optimizing                     | 0.000011 | 0.000005 |   0.000006 |
| statistics                     | 0.000036 | 0.000016 |   0.000021 |
| preparing                      | 0.000038 | 0.000016 |   0.000021 |
| executing                      | 0.000176 | 0.000089 |   0.000118 |
| end                            | 0.000011 | 0.000005 |   0.000005 |
| query end                      | 0.000008 | 0.000003 |   0.000005 |
| waiting for handler commit     | 0.000018 | 0.000008 |   0.000010 |
| closing tables                 | 0.000018 | 0.000008 |   0.000010 |
| freeing items                  | 0.000039 | 0.000016 |   0.000022 |
| cleaning up                    | 0.000105 | 0.000046 |   0.000060 |
+--------------------------------+----------+----------+------------+
17 rows in set, 1 warning (0.01 sec)

显示最近一句sql所有列，all表示显示所有列

sql

show profile all;

BLOCK IO显示块输入和输出操作的计数

sql

show profile block io;

CONTEXT SWITCHES 显示自愿和非自愿上下文切换的计数

sql

show profile context switches;

CPU 显示用户和系统 CPU 使用时间

sql

show profile cpu;

IPC 显示发送和接收的消息数量

sql

show profile ipc;

MEMORY 目前尚未实施

sql

show profile memory;

PAGE FAULTS 显示主要和次要页面错误的计数

sql

show profile page faults;

SOURCE 显示源代码中的函数名称，以及该函数所在的文件的名称和行号

sql

show profile source;

SWAPS 显示交换计数

sql

show profile swaps;

还可以从INFORMATION_SCHEMA PROFILING表获取分析信息。例如，以下查询是等效的：

sql

SHOW PROFILE FOR QUERY 2;

SELECT STATE, FORMAT(DURATION, 6) AS DURATION
FROM INFORMATION_SCHEMA.PROFILING
WHERE QUERY_ID = 2 ORDER BY SEQ;

`explain`命令

MySQL explain 应用详解
MySQL EXPLAIN 详解，可用EXPLAIN来分析优化数据库sql语句

创建测试数据库

sql

CREATE DATABASE `mydb`;

USE `mydb`;

DROP TABLE IF EXISTS `course`;

CREATE TABLE `course` (
  `id` int(10) NOT NULL AUTO_INCREMENT,
  `name` varchar(20) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=21 DEFAULT CHARSET=utf8;

insert  into `course`(`id`,`name`) values 
(1,'语文'),(2,'高等数学'),(3,'视听说'),(4,'体育'),(5,'马克思概况'),(6,'民族理论'),(7,'毛中特'),(8,'计算机基础'),(9,'深度学习'),(10,'Java程序设计'),(11,'c语言程序设计'),(12,'操作系统'),(13,'计算机网络'),(14,'计算机组成原理'),(15,'数据结构'),(16,'数据分析'),(17,'大学物理'),(18,'数字逻辑'),(19,'嵌入式开发'),(20,'需求工程');

DROP TABLE IF EXISTS `stu_course`;

CREATE TABLE `stu_course` (
  `sid` int(10) NOT NULL,
  `cid` int(10) NOT NULL,
  PRIMARY KEY (`sid`,`cid`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

insert  into `stu_course`(`sid`,`cid`) values 
(1,2),(1,4),(1,14),(1,16),(1,19),(2,4),(2,8),(2,9),(2,14),(3,13),(3,14),(3,20),(4,5),(4,8),(4,9),(4,11),(4,16),(5,4),(5,8),(5,9),(5,11),(5,12),(5,16),(6,2),(6,14),(6,17),(7,1),(7,8),(7,15),(8,2),(8,3),(8,7),(8,17),(9,1),(9,7),(9,16),(9,20),(10,4),(10,12),(10,14),(10,20),(11,3),(11,9),(11,16),(12,3),(12,7),(12,9),(12,12),(13,1),(13,5),(13,13),(14,1),(14,3),(14,18),(15,1),
(15,9),(15,15),(16,2),(16,7);

DROP TABLE IF EXISTS `student`;

CREATE TABLE `student` (
  `id` int(10) NOT NULL AUTO_INCREMENT,
  `name` varchar(20) DEFAULT NULL,
  `age` int(2) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `name` (`name`),
  KEY `name_age` (`name`,`age`),
  KEY `id_name_age` (`id`,`name`,`age`)
) ENGINE=InnoDB AUTO_INCREMENT=31 DEFAULT CHARSET=utf8;

insert  into `student`(`id`,`name`,`age`) values 
(25,'乾隆',17),(14,'关羽',43),(13,'刘备',12),(28,'刘永',12),(21,'后裔',12),(30,'吕子乔',28),(18,'嬴政',76),(22,'孙悟空',21),(4,'安其拉',24),(6,'宋江',22),(26,'康熙',51),(29,'张伟',26),(20,'张郃',12),(12,'张飞',32),(27,'朱元璋',19),(11,'李世民',54),(9,'李逵',12),(8,'林冲',43),(5,'橘右京',43),(24,'沙和尚',25),(23,'猪八戒',22),(15,'王与',21),(19,'王建',23),(10,'王莽',43),(16,'秦叔宝',43),(17,'程咬金',65),(3,'荆轲',21),(2,'诸葛亮',71),(7,'钟馗',23),(1,'鲁班',21);

id与table字段

为什么要将id和table放在一起讲呢？因为通过这两个字段可以完全判断出你的每一条SQL语句的执行顺序和表的查询顺序。

先看id后看table，id和table在SQL执行判断过程中的关系就像是足球联赛的积分榜，首先一个联赛的球队排名应该先看积分，积分越高的球队排名越靠前，当两支或多只球队的积分一样高怎么办呢？那我们就看净胜球，净胜球越多的球队，排在前面。而在explain中你可以把id看作是球队积分，table当作是净胜球。

比如说我们explain一下这一条SQL：

sql

EXPLAIN
SELECT 
    S.id,S.name,S.age,C.id,C.name
FROM course C JOIN stu_course SC ON C.id = SC.cid
JOIN student S ON S.id = SC.sid;

结果是这样：

sql

+------+-------------+-------+--------+---------------------+----------+---------+-------------+------+-------------+
| id   | select_type | table | type   | possible_keys       | key      | key_len | ref         | rows | Extra       |
+------+-------------+-------+--------+---------------------+----------+---------+-------------+------+-------------+
|    1 | SIMPLE      | S     | index  | PRIMARY,id_name_age | name_age | 68      | NULL        | 30   | Using index |
|    1 | SIMPLE      | SC    | ref    | PRIMARY             | PRIMARY  | 4       | mydb.S.id   | 1    | Using index |
|    1 | SIMPLE      | C     | eq_ref | PRIMARY             | PRIMARY  | 4       | mydb.SC.cid | 1    |             |
+------+-------------+-------+--------+---------------------+----------+---------+-------------+------+-------------+

我们看到id全是1，那就说明光看id这个值是看不出来每个表的读取顺序的，那我们就来看table这一行，它的读取顺序是自上向下的，所以，这三个表的读取顺序应当是：S - SC - C。

再来看一条SQL

sql

EXPLAIN
SELECT * 
FROM course AS C 
WHERE C.`id` = (
    SELECT SC.`cid` 
    FROM stu_course AS SC 
    WHERE SC.`sid` = 
    (
        SELECT 
            S.`id` 
        FROM student AS S
        WHERE  S.`name` = "安其拉"
    ) ORDER BY SC.`cid` LIMIT 1
);

这条语句是查询结果是：一个叫安其拉的学生选的课里面，课程id最小的一门课的信息，然后来看一下explain的结果吧！

sql

+------+-------------+-------+-------+---------------+---------+---------+-------+------+--------------------------+
| id   | select_type | table | type  | possible_keys | key     | key_len | ref   | rows | Extra                    |
+------+-------------+-------+-------+---------------+---------+---------+-------+------+--------------------------+
|    1 | PRIMARY     | C     | const | PRIMARY       | PRIMARY | 4       | const | 1    | Using where              |
|    2 | SUBQUERY    | SC    | ref   | PRIMARY       | PRIMARY | 4       | const | 5    | Using where; Using index |
|    3 | SUBQUERY    | S     | ref   | name,name_age | name    | 63      | const | 1    | Using where; Using index |
+------+-------------+-------+-------+---------------+---------+---------+-------+------+--------------------------+

此时我们发现id是不相同的，所以我们很容易就看出表读取的顺序了是吧！S - SC - C

注意！！！！！！你仔细看一下最里面的子查询是查询的哪个表，是S这张表，然后外面一层呢？是SC这张表，最外面这一层呢？是C这张表，所以执行顺序应该是啥呢？是....是.....难道是S - SC - C吗？是id越大的table读取越在前面吗？是的！这就像刚才说的足球联赛积分，分数越高的球队的排序越靠前。

当然还有下面这种情况

sql

EXPLAIN
SELECT * 
FROM course AS C 
WHERE C.`id` IN (
    SELECT SC.`cid` 
    FROM stu_course AS SC 
    WHERE SC.`sid` = 
    (
        SELECT 
            S.`id` 
        FROM student AS S
        WHERE  S.`name` = "安其拉"
    )
);

这个查询是：查询安其拉选课的课程信息

sql

+------+-------------+-------+--------+---------------+---------+---------+-------------+------+--------------------------+
| id   | select_type | table | type   | possible_keys | key     | key_len | ref         | rows | Extra                    |
+------+-------------+-------+--------+---------------+---------+---------+-------------+------+--------------------------+
|    1 | PRIMARY     | SC    | ref    | PRIMARY       | PRIMARY | 4       | const       | 5    | Using where; Using index |
|    1 | PRIMARY     | C     | eq_ref | PRIMARY       | PRIMARY | 4       | mydb.SC.cid | 1    |                          |
|    3 | SUBQUERY    | S     | ref    | name,name_age | name    | 63      | const       | 1    | Using where; Using index |
+------+-------------+-------+--------+---------------+---------+---------+-------------+------+--------------------------+

结果很明确：先看id应该是S表最先被读取，SC和C表id相同，然后table中SC更靠上，所以第二张读取的表应当是SC，最后读取C。

select_type字段

SIMPLE 简单查询，不包括子查询和union查询

sql

EXPLAIN SELECT * FROM student JOIN stu_course ON student.`id` = sid;

sql

+------+-------------+------------+-------+---------------------+----------+---------+-----------------+------+-------------+
| id   | select_type | table      | type  | possible_keys       | key      | key_len | ref             | rows | Extra       |
+------+-------------+------------+-------+---------------------+----------+---------+-----------------+------+-------------+
|    1 | SIMPLE      | student    | index | PRIMARY,id_name_age | name_age | 68      | NULL            | 30   | Using index |
|    1 | SIMPLE      | stu_course | ref   | PRIMARY             | PRIMARY  | 4       | mydb.student.id | 1    | Using index |
+------+-------------+------------+-------+---------------------+----------+---------+-----------------+------+-------------+

PRIMARY 当存在子查询时，最外面的查询被标记为主查询

SUBQUERY 子查询

sql

EXPLAIN
SELECT SC.`cid` 
FROM stu_course AS SC 
WHERE SC.`sid` = 
(
    SELECT 
        S.`id` 
    FROM student AS S
    WHERE  S.`name` = "安其拉"
);

sql

+------+-------------+-------+------+---------------+---------+---------+-------+------+--------------------------+
| id   | select_type | table | type | possible_keys | key     | key_len | ref   | rows | Extra                    |
+------+-------------+-------+------+---------------+---------+---------+-------+------+--------------------------+
|    1 | PRIMARY     | SC    | ref  | PRIMARY       | PRIMARY | 4       | const | 5    | Using where; Using index |
|    2 | SUBQUERY    | S     | ref  | name,name_age | name    | 63      | const | 1    | Using where; Using index |
+------+-------------+-------+------+---------------+---------+---------+-------+------+--------------------------+

DEPENDENT SUBQUERY
子查询的结果受到外层的影响：
sql
```
select id,name,(select name from student s where s.id=c.id) from course c;
```
不要在意SQL，以上SQL没有实际查询的意义只是用于表达用例
UNION

UNION RESULT

union连接的多表查询，第一个查询是primary，后面的是union, 结果集是union result

sql

EXPLAIN
SELECT * FROM student WHERE id = 1
UNION
SELECT * FROM student WHERE id = 2;

sql

+------+--------------+------------+-------+---------------------+---------+---------+-------+------+-------+
| id   | select_type  | table      | type  | possible_keys       | key     | key_len | ref   | rows | Extra |
+------+--------------+------------+-------+---------------------+---------+---------+-------+------+-------+
|    1 | PRIMARY      | student    | const | PRIMARY,id_name_age | PRIMARY | 4       | const | 1    |       |
|    2 | UNION        | student    | const | PRIMARY,id_name_age | PRIMARY | 4       | const | 1    |       |
| NULL | UNION RESULT | <union1,2> | ALL   | NULL                | NULL    | NULL    | NULL  | NULL |       |
+------+--------------+------------+-------+---------------------+---------+---------+-------+------+-------+

上面可以看到第三行table的值是<union1,2>

DEPENDENT UNION
和union一样，出现在union或者union all中，但是这个查询要受到外部查询的影响
注意：似乎https://www.kancloud.cn/xuwenyang/php_standard/2707424中描述的示例是错误的，导致没有继续研究这个类型。

DERIVED 在FROM列表中包含的子查询被标记为DERIVED（衍生），MySQL会递归执行这些子查询，把结果放在临时表中

MySQL5.7+ 进行优化了，增加了derived_merge（派生合并），默认开启，可加快查询效率

当你的MySQL是5.7及以上版本时你要将derived_merge关闭后才能看到DERIVED 状态

sql

# 关闭session optimizer_switch
set session optimizer_switch='derived_merge=off';
# 关闭global optimizer_switch
set global optimizer_switch='derived_merge=off';

# 显示session optimizer_switch
show session variables like 'optimizer_switch';

# 显示global optimizer_switch
show variables like 'optimizer_switch';

sql

EXPLAIN
SELECT * FROM 
(
    SELECT * 
    FROM student AS S JOIN stu_course AS SC 
    ON S.`id` = SC.`sid`
) AS SSC;

sql

+------+-------------+------------+-------+---------------------+----------+---------+-----------+------+-------------+
| id   | select_type | table      | type  | possible_keys       | key      | key_len | ref       | rows | Extra       |
+------+-------------+------------+-------+---------------------+----------+---------+-----------+------+-------------+
|    1 | PRIMARY     | <derived2> | ALL   | NULL                | NULL     | NULL    | NULL      | 30   |             |
|    2 | DERIVED     | S          | index | PRIMARY,id_name_age | name_age | 68      | NULL      | 30   | Using index |
|    2 | DERIVED     | SC         | ref   | PRIMARY             | PRIMARY  | 4       | mydb.S.id | 1    | Using index |
+------+-------------+------------+-------+---------------------+----------+---------+-----------+------+-------------+

上面我们观察，最外层的主查询的表是<derived2>,而S和SC表的select_type都是DERIVED,这说明S和SC都被用来做衍生查询，而这两张表查询的结果组成了名为<derived2>的衍生表，而衍生表的命名就是<select_type + id>。

partitions字段
该列显示的为分区表命中的分区情况。非分区表该字段为空（null）。

type字段

注意！！！注意！！！重点来了！

首先说一下这个字段，要记住以下10个状态，（从左往右，越靠左边的越优秀）

NULL > system > const > eq_ref > ref > ref_or_null > index_merge > range > index > ALL

NULL MySQL能够在优化阶段分解查询语句，在执行阶段用不着再访问表或索引

有没有这样一种疑惑，不查询索引也不查询表那你的数据是从哪里来的啊？谁说SELECT语句必须查询某样东西了？

sql

EXPLAIN SELECT 5*7;

sql

+------+-------------+-------+------+---------------+------+---------+------+------+----------------+
| id   | select_type | table | type | possible_keys | key  | key_len | ref  | rows | Extra          |
+------+-------------+-------+------+---------------+------+---------+------+------+----------------+
|    1 | SIMPLE      | NULL  | NULL | NULL          | NULL | NULL    | NULL | NULL | No tables used |
+------+-------------+-------+------+---------------+------+---------+------+------+----------------+

我就简简单单算个数不好吗？好啊😊。。。

但是！！如果只是这样的话我们还explain个毛线啊！我很闲吗？

存在这样一种情况，大家都知道索引是将数据在B+Tree中进行排序了，所以你的查询速率才这么高，那么B+树的最边上的叶子节点是不是要么是最大值要么是最小值啊？既然你都知道了，那MySQL比你更知道啊！当你要查询最大值或者最小值时，MySQL会直接到你的索引得分叶子节点上直接拿，所以不用访问表或者索引。

sql

EXPLAIN SELECT MAX(id) FROM student;

sql

+------+-------------+-------+------+---------------+------+---------+------+------+------------------------------+
| id   | select_type | table | type | possible_keys | key  | key_len | ref  | rows | Extra                        |
+------+-------------+-------+------+---------------+------+---------+------+------+------------------------------+
|    1 | SIMPLE      | NULL  | NULL | NULL          | NULL | NULL    | NULL | NULL | Select tables optimized away |
+------+-------------+-------+------+---------------+------+---------+------+------+------------------------------+

但是！你要记住，NULL的前提是你已经建立了索引。

SYSTEM 表只有一行记录（等于系统表），这是const类型的特列。

sql

explain select * from (select * from course where id=1 limit 1) a;

sql

+------+-------------+------------+--------+---------------+---------+---------+-------+------+-------+
| id   | select_type | table      | type   | possible_keys | key     | key_len | ref   | rows | Extra |
+------+-------------+------------+--------+---------------+---------+---------+-------+------+-------+
|    1 | PRIMARY     | <derived2> | system | NULL          | NULL    | NULL    | NULL  | 1    |       |
|    2 | DERIVED     | course     | const  | PRIMARY       | PRIMARY | 4       | const | 1    |       |
+------+-------------+------------+--------+---------------+---------+---------+-------+------+-------+

const 表示通过索引一次就找到了，const用于比较primary key或uique索引，因为只匹配一行数据，所以很快，如主键置于where列表中，MySQL就能将该查询转换为一个常量。

简单来说，const是直接按主键或唯一键读取。

sql

EXPLAIN SELECT * FROM student AS S WHERE id = 10;

sql

+------+-------------+-------+-------+---------------------+---------+---------+-------+------+-------+
| id   | select_type | table | type  | possible_keys       | key     | key_len | ref   | rows | Extra |
+------+-------------+-------+-------+---------------------+---------+---------+-------+------+-------+
|    1 | SIMPLE      | S     | const | PRIMARY,id_name_age | PRIMARY | 4       | const | 1    |       |
+------+-------------+-------+-------+---------------------+---------+---------+-------+------+-------+

eq_ref 用于联表查询的情况，按联表的主键或唯一键联合查询。

多表join时，对于来自前面表的每一行，在当前表中只能找到一行。这可能是除了system和const之外最好的类型。当主键或唯一非NULL索引的所有字段都被用作join联接时会使用此类型。

sql

EXPLAIN SELECT * FROM student AS S JOIN stu_course AS SC ON  S.`id` = SC.`cid`;

sql

+------+-------------+-------+--------+---------------------+---------+---------+-------------+------+-------------+
| id   | select_type | table | type   | possible_keys       | key     | key_len | ref         | rows | Extra       |
+------+-------------+-------+--------+---------------------+---------+---------+-------------+------+-------------+
|    1 | SIMPLE      | SC    | index  | NULL                | PRIMARY | 8       | NULL        | 59   | Using index |
|    1 | SIMPLE      | S     | eq_ref | PRIMARY,id_name_age | PRIMARY | 4       | mydb.SC.cid | 1    |             |
+------+-------------+-------+--------+---------------------+---------+---------+-------------+------+-------------+

以上面查询为例，我们观察id和table会知道，先是从SC表中取出一行数据，然后再S表查找匹配的数据，我们观察，SC中取出cid和S表中的id比较，毫无疑问因为id是S表中的主键（不重复），所以只能出现一个id与cid的值相同。所以！满足条件 S 表的 type为eq_ref。

ref 可以用于单表扫描或者连接。如果是连接的话，驱动表的一条记录能够在被驱动表中通过非唯一（主键）属性所在索引中匹配多行数据，或者是在单表查询的时候通过非唯一（主键）属性所在索引中查到一行数据。

sql

EXPLAIN SELECT * FROM student AS S JOIN stu_course AS SC ON S.id = SC.`sid`;

不要在意SQL，以上SQL没有实际查询的意义只是用于表达用例

sql

+------+-------------+-------+-------+---------------------+----------+---------+-----------+------+-------------+
| id   | select_type | table | type  | possible_keys       | key      | key_len | ref       | rows | Extra       |
+------+-------------+-------+-------+---------------------+----------+---------+-----------+------+-------------+
|    1 | SIMPLE      | S     | index | PRIMARY,id_name_age | name_age | 68      | NULL      | 30   | Using index |
|    1 | SIMPLE      | SC    | ref   | PRIMARY             | PRIMARY  | 4       | mydb.S.id | 1    | Using index |
+------+-------------+-------+-------+---------------------+----------+---------+-----------+------+-------------+

SC的主键索引是（cid,sid）所以sid列中肯定是重复的数据，虽然在后面的key中显示使用了主键索引。然后，就很明确了S.id一行能在SC表中通过索引查询到多行数据。

下面是单表了，写一个例子，但是不细讲了

sql

EXPLAIN SELECT * FROM student AS S WHERE S.`name` = "张飞";

sql

+------+-------------+-------+------+---------------+----------+---------+-------+------+--------------------------+
| id   | select_type | table | type | possible_keys | key      | key_len | ref   | rows | Extra                    |
+------+-------------+-------+------+---------------+----------+---------+-------+------+--------------------------+
|    1 | SIMPLE      | S     | ref  | name,name_age | name_age | 63      | const | 1    | Using where; Using index |
+------+-------------+-------+------+---------------+----------+---------+-------+------+--------------------------+

注意name字段是有索引的哈！！！

ref_or_null 类似ref，但是可以搜索值为NULL的行

sql

EXPLAIN SELECT * FROM student AS S WHERE S.`name` = "张飞" OR S.`name` IS NULL;

sql

+------+-------------+-------+-------------+---------------+----------+---------+-------+------+--------------------------+
| id   | select_type | table | type        | possible_keys | key      | key_len | ref   | rows | Extra                    |
+------+-------------+-------+-------------+---------------+----------+---------+-------+------+--------------------------+
|    1 | SIMPLE      | S     | ref_or_null | name,name_age | name_age | 63      | const | 2    | Using where; Using index |
+------+-------------+-------+-------------+---------------+----------+---------+-------+------+--------------------------+

index_merge 表示查询使用了两个以上的索引，最后取交集或者并集，常见and ，or的条件使用了不同的索引，官方排序这个在ref_or_null之后，但是实际上由于要读取多个索引，性能可能大部分时间都不如range。

sql

EXPLAIN SELECT * FROM student AS S WHERE S.`name` LIKE "张%" OR S.`age` = 30;

sql

+------+-------------+-------+-------+---------------+----------+---------+------+------+--------------------------+
| id   | select_type | table | type  | possible_keys | key      | key_len | ref  | rows | Extra                    |
+------+-------------+-------+-------+---------------+----------+---------+------+------+--------------------------+
|    1 | SIMPLE      | S     | index | name,name_age | name_age | 68      | NULL | 30   | Using where; Using index |
+------+-------------+-------+-------+---------------+----------+---------+------+------+--------------------------+

range 索引范围查询，常见于使用 =, <>, >, >=, <, <=, IS NULL, <=>, BETWEEN, IN()或者like等运算符的查询中。

sql

EXPLAIN SELECT S.`age` FROM student  AS S WHERE S.`age` > 30;

sql

+------+-------------+-------+-------+---------------+----------+---------+------+------+--------------------------+
| id   | select_type | table | type  | possible_keys | key      | key_len | ref  | rows | Extra                    |
+------+-------------+-------+-------+---------------+----------+---------+------+------+--------------------------+
|    1 | SIMPLE      | S     | index | NULL          | name_age | 68      | NULL | 30   | Using where; Using index |
+------+-------------+-------+-------+---------------+----------+---------+------+------+--------------------------+

index index只遍历索引树，通常比All快。因为，索引文件通常比数据文件小，也就是虽然all和index都是读全表，但index是从索引中读取的，而all是从硬盘读的。

sql

EXPLAIN SELECT S.`name` FROM student AS S;

sql

+------+-------------+-------+-------+---------------+------+---------+------+------+-------------+
| id   | select_type | table | type  | possible_keys | key  | key_len | ref  | rows | Extra       |
+------+-------------+-------+-------+---------------+------+---------+------+------+-------------+
|    1 | SIMPLE      | S     | index | NULL          | name | 63      | NULL | 30   | Using index |
+------+-------------+-------+-------+---------------+------+---------+------+------+-------------+

ALL 如果一个查询的type是All,并且表的数据量很大，那么请解决它！！！

possible_keys字段
这个表里面存在且可能会被使用的索引，可能会在这个字段下面出现，但是一般都以key为准。
key字段
实际使用的索引，如果为null,则没有使用索引，否则会显示你使用了哪些索引，查询中若使用了覆盖索引（查询的列刚好是索引），则该索引仅出现在key列表。
ref字段
显示哪些列被使用了，如果可能的话，最好是一个常数。哪些列或常量被用于查找索引列上的值。
rows字段
执行计划估算的扫描行数，不是精确值（innodb不是精确值，myisam是精确值，主要是因为innodb使用了mvcc）。
filtered字段
filtered列显示的是，对于表中的每一行，满足WHERE子句（以及其他可能影响行选择的条件，如JOIN条件）的百分比。这个百分比是基于MySQL的统计信息和优化器的估算，而不是实际执行的行数。因此，它只是一个近似值，但在大多数情况下，它对于理解查询的效率和可能的性能瓶颈是非常有用的。是查询的行数与总行数的比值。其实作用与rows差不多，都是数值越小，效率越高。
高百分比：如果filtered列的值很高（比如接近100%），这通常意味着WHERE子句非常宽松，或者该表的大部分行都符合WHERE子句的条件。这并不一定意味着查询就是高效的，但如果索引使用得当，它可能表明MySQL可以非常有效地找到这些行。
低百分比：相反，如果filtered列的值很低（比如只有几%），这表示WHERE子句非常严格，只有表中的一小部分行满足条件。这通常是好事，因为它减少了MySQL需要处理的数据量，但前提是MySQL能够有效地利用索引来找到这些行。
假设你有一个名为employees的表，你执行了以下查询，并使用了EXPLAIN来分析它：
sql
```
EXPLAIN SELECT * FROM employees WHERE department_id = 5;
```
如果filtered列显示的是10.00，这意味着MySQL估计大约只有10%的employees表中的行满足department_id = 5的条件。如果employees表有一个针对department_id的索引，并且MySQL能够使用这个索引来过滤行，那么即使只有10%的行满足条件，查询也应该能够高效地执行。

Extra字段

这一字段包含不适合在其他列显示，但是也非常重要的额外信息。

Using filesort 表示当SQL中有一个地方需要对一些数据进行排序的时候，优化器找不到能够使用的索引，所以只能使用外部的索引排序，外部排序就不断的在磁盘和内存中交换数据，这样就摆脱不了很多次磁盘IO，以至于SQL执行的效率很低。反之呢？由于索引的底层是B+Tree实现的，他的叶子节点本来就是有序的，这样的查询能不爽吗？

sql

EXPLAIN SELECT * FROM course AS C ORDER BY C.`name`;

sql

+------+-------------+-------+------+---------------+------+---------+------+------+----------------+
| id   | select_type | table | type | possible_keys | key  | key_len | ref  | rows | Extra          |
+------+-------------+-------+------+---------------+------+---------+------+------+----------------+
|    1 | SIMPLE      | C     | ALL  | NULL          | NULL | NULL    | NULL | 20   | Using filesort |
+------+-------------+-------+------+---------------+------+---------+------+------+----------------+

没有给C.name建立索引，所以在根据C.name排序的时候，他就使用了外部排序

Using tempporary 表示在对MySQL查询结果进行排序时，使用了临时表,,这样的查询效率是比外部排序更低的，常见于order by和group by。

sql

EXPLAIN SELECT C.`name` FROM course AS C GROUP BY C.`name`;

sql

+------+-------------+-------+------+---------------+------+---------+------+------+---------------------------------+
| id   | select_type | table | type | possible_keys | key  | key_len | ref  | rows | Extra                           |
+------+-------------+-------+------+---------------+------+---------+------+------+---------------------------------+
|    1 | SIMPLE      | C     | ALL  | NULL          | NULL | NULL    | NULL | 20   | Using temporary; Using filesort |
+------+-------------+-------+------+---------------+------+---------+------+------+---------------------------------+

上面这个查询就是同时触发了Using temporary和Using filesort，可谓是雪上加霜。

临时表可以是内存临时表和磁盘临时表，执行计划中看不出来，需要查看status变量：used_tmp_table、used_tmp_disk_table才可以看出来

Using index
表示使用了索引，很优秀👍。
查询时候不需要回表，表示相应的select查询中使用到了覆盖索引(Covering index)，避免访问表的数据行
如果同时出现了using where，说明索引被用来执行查询键值
如果没有using where，表示读取数据而不是执行查找操作

Using where

表示存储引擎返回的记录并不都是符合条件的，需要在server层进行筛选过滤，性能很低

sql

explain select * from course where name='高等数学';

sql

+------+-------------+--------+------+---------------+------+---------+------+------+-------------+
| id   | select_type | table  | type | possible_keys | key  | key_len | ref  | rows | Extra       |
+------+-------------+--------+------+---------------+------+---------+------+------+-------------+
|    1 | SIMPLE      | course | ALL  | NULL          | NULL | NULL    | NULL | 20   | Using where |
+------+-------------+--------+------+---------------+------+---------+------+------+-------------+

Using join buffer 表明使用了连接缓存,比如说在查询的时候，多表join的次数非常多，那么将配置文件中的缓冲区的join buffer调大一些。
impossible where 筛选条件没能筛选出任何东西
distinct 优化distinct操作，在找到第一匹配的元组后即停止找同样值的动作

`explain extended`命令

explain extended

explain extended在mysql不存在，但能够在mariadb中使用。

explain extended向输出添加了另一列，即filtered列。这是将按条件过滤的表行的百分比估计值。

`explain analyze`命令

在MySQL中，EXPLAIN ANALYZE是一个功能强大的工具，用于对SQL查询执行详细的分析。然而，需要注意的是，虽然EXPLAIN语句在MySQL中广泛使用以查看查询的执行计划，但直到MySQL 8.0.18版本之前，MySQL并没有直接提供名为EXPLAIN ANALYZE的语句。

从MySQL 8.0.18开始，MySQL引入了一种新的方式来分析查询的执行情况，这通常是通过EXPLAIN ANALYZE命令（或者更具体地说，是在EXPLAIN语句中使用ANALYZE选项）来实现的。然而，MySQL官方文档可能并没有直接使用EXPLAIN ANALYZE这样的命令名称，而是将其作为EXPLAIN语句的一个扩展或选项来介绍。

在MySQL 8.0.18及更高版本中，你可以通过EXPLAIN ANALYZE（尽管可能需要以EXPLAIN ... ANALYZE的形式）来执行查询，并获取比传统EXPLAIN更详细的执行信息，包括实际的运行时间、迭代次数、行数等。这有助于你更深入地了解查询的性能特性，并据此进行优化。

但是，请注意，由于MySQL版本的更新和文档的变化，最准确的信息应该来自你正在使用的MySQL版本的官方文档。

例如，在MySQL 8.0.x版本中，你可能会这样使用它：

sql

EXPLAIN ANALYZE SELECT * FROM your_table WHERE some_column = 'some_value';

mysql命令 ​

show processlist命令 ​

show profiles和show profile命令 ​

explain命令 ​