MySQL使用EXPLAIN分析sql语句
关于EXPLAIN
EXPLAIN简介
EXPLAIN关键字用于提供MySQL是怎样执行sql语句的，只要在SELECT、DELETE、INSERT、REPLACE、UPDATE这些语句前面加上一个EXPLAIN关键字再运行，就可以输出相关的执行信息。最常见的是用它来分析优化select语句。
DESCRIBE和EXPLAIN的区别
首先说答案：这两个关键字的功能是完全一样的，可以相互替换使用。
打开13.8.2 EXPLAIN Statement，往下滚动一下，能找到以下句子
The DESCRIBE and EXPLAIN statements are synonyms. In practice, the DESCRIBE keyword is more often used to obtain information about table structure, whereas EXPLAIN is used to obtain a query execution plan (that is, an explanation of how MySQL would execute a query).
翻译：DESCRIBE和EXPLAIN语句是同义词。在实际使用中，DESCRIBE更常用于获取有关表结构的信息，而 EXPLAIN用于获取查询执行计划（即MySQL如何执行查询的说明）。
可以看到，DESCRIBE(平时使用可简写成DESC)和EXPLAIN是同义词，使用的时候功能是一样的，虽然功能完全一样，虽然可以相互替换使用，但在实际使用中，DESCRIBE更常用于获取有关表结构的信息(格式：desc <表名>)，而EXPLAIN更常在sql优化和索引优化中使用(用来分析select语句的执行情况，以便知识如何优化)。
构建测试数据库
导入数据库
要分析sql语句，首先要有一个数据库，MySQL官方有很多个Example Databases，在这里搜索“Example Databases”即可搜索到，我们就用它的第一个，叫“employee data”，这个数据库最大，有30多M，并且是在github提供下载：test_db，虽然官网的可能不至于删除，但我还是自己fork了一份到自己的github：test_db。另外这里也有一个在线的可以构建测试数据库的网站，有需要可以去看看。
以下是从下载到导入数据库的所有
# 下载(也可以在浏览器里下载，如果以后有新版本，请自己把以下链接替换为新版本链接即可)
wget https://github.com/datacharmer/test_db/releases/download/v1.0.7/test_db-1.0.7.tar.gz
# 解压(当然你也可以双击或右击解压)
tar -zxf test_db-1.0.7.tar.gz
# 进入test_db文件夹(一定要进入该文件夹后再执行以下语句，否则导入会报错，原因看后面的解释)
cd test_db
# 导入方式一：直接使用mysql命令导入(如果默认数据库)
mysql -u<用户名> -p < employees.sql
# 导入方式二：先登录进数据库
mysql -u<用户名> -p
# 登录后设置一下默认数据库引擎为InnoDB(当然这一步在大多数情况下可以不做，因为8.0以上版本默认引擎就是InnoDB)
set storage_engine = InnoDB;
# 然后再使用source导入
source employees.sql
- 1、虽然这个包叫test_db，但是它创建的数据库名是“employees”(意思是“员工数据库”)；
- 2、虽然test_db文件夹内有很多sql文件，但我们只需要导入employees.sql即可，因为在employees.sql文件底部有很多source语句，它会自动source其它sql文件，这也是为什么你需要先
cd进test_db文件夹的原因。
数据库表结构分析
mysql> show tables;
+----------------------+
| Tables_in_employees |
+----------------------+
| current_dept_emp |
| departments |
| dept_emp |
| dept_emp_latest_date |
| dept_manager |
| employees |
| salaries |
| titles |
+----------------------+
8 rows in set (0.00 sec)
可以看到，前面导入的employees库有8张表，不过实际上current_dept_emp和dept_emp_latest_date是视图，所以实际的表有6张：
- 1、employees: 员工表
- 2、titles: 员工职称表(与员工表关联)
- 3、salaries: 员工薪资表(与员工关联)
- 4、departments: 部门表
- 5、dept_emp: 员工-部门关联表
- 6、dept_manager: 部门经理表(与部门关联)
EXPLAIN输出结果详解
官方文档：8.8.2 EXPLAIN Output Format。
输出结果
执行以下sql语句，得到一个表格，这个表格就是explain后面那个select语句的执行信息
mysql> explain select * from employees;
+----+-------------+-----------+------------+------+---------------+------+---------+------+--------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-----------+------------+------+---------------+------+---------+------+--------+----------+-------+
| 1 | SIMPLE | employees | NULL | ALL | NULL | NULL | NULL | NULL | 299379 | 100.00 | NULL |
+----+-------------+-----------+------------+------+---------------+------+---------+------+--------+----------+-------+
1 row in set, 1 warning (0.00 sec)
如果explain后面加个format=json，可以以json格式输出sql执行信息
explain format=json select * from employees;
json格式输出explain信息
{
"query_block": {
"select_id": 1,
"cost_info": {
"query_cost": "30833.08"
},
"table": {
"table_name": "employees",
"access_type": "ALL",
"rows_examined_per_scan": 299379,
"rows_produced_per_join": 299379,
"filtered": "100.00",
"cost_info": {
"read_cost": "895.18",
"eval_cost": "29937.90",
"prefix_cost": "30833.08",
"data_read_per_join": "38M"
},
"used_columns": [
"emp_no",
"birth_date",
"first_name",
"last_name",
"gender",
"hire_date"
]
}
}
}
注意：json格式并不是单纯的把表格格式转换为json格式，而是显示了更多的信息，但也有些表格中的信息没有在json中显示(比如select_type和Extra就不在json信息中显示)。
输出结果详解
json格式信息我们暂时不看，我们先看表格信息，从以上表格信息中我们可以看到，explain共输出12个字段
| 序号 | 项 | 说明 | |
|---|---|---|---|
| 1 | id | MySQL查询优化器选定的执行计划中查询的序列号。表示查询中执行select子句或操作表的顺序，id值越大优先级越高，越先被执行。id相同，执行顺序由上至下。 | |
| 2 | 子序号 | select_type | select类型，如下所示，从最好到最差依次排列 |
| 1 | SIMPLE | 简单的select查询，不使用union及子查询 | |
| 2 | PRIMARY | 当有子查询时，最外层的select有可能是PRIMARY类型，但也未必，因为PRIMARY类型比较少见 | |
| 3 | UNION | UNION中的第二个或随后的select查询，不依赖外部查询的结果集。 | |
| 4 | DEPENDENT UNION | UNION中的第二个或随后的select查询，依赖于外部查询的结果集。 | |
| 5 | UNION RESULT | UNION 查询的结果集 | |
| 6 | SUBQUERY | 子查询中的第一个select查询，不依赖于外部查询的结果集。 | |
| 7 | DEPENDENT SUBQUERY | 子查询中的第一个select查询，依赖于外部查询的结果集。 | |
| 8 | DERIVED | 用于from子句里有子查询的情况。MySQL会递归执行这些子查询，把结果放在临时表里，不依赖外部结果集。 | |
| 9 | DEPENDENT DERIVED | 用于from子句里有子查询的情况。MySQL会递归执行这些子查询，把结果放在临时表里，依赖于外部结果集。 | |
| 10 | MATERIALIZED | 物化，其实就是创建临时表 | |
| 11 | UNCACHEABLE SUBQUERY | 结果集不能被缓存的子查询，必须重新为外层查询的每一行进行评估。 | |
| 12 | UNCACHEABLE UNION | UNION中的第二个或随后的select查询，属于不可缓存的子查询。 | |
| 3 | table | 输出表所引用的行 | |
| 4 | partitions | 查询将会从哪个分区匹配记录。 | |
| 5 | 子序号 | type | 以下类型从好到坏依次排列，全文索引一般可以忽略，因为全文索引一般都是用专用的全文索引，而不用mysql本身的。 |
| 1 | system | 表仅有一行(=系统表)。这是const连接类型的一个特例。 | |
| 2 | const | 该表最多有一个匹配行，在查询开始时被读取。 因为只有一行，所以这一行中字段的值可以被优化器的其余部分视为常量。 const表非常快，因为它们只被读取一次。这种类型一般出现在“where 字段=常量”，且字段是主键或有唯一索引时。 | |
| 3 | eq_ref | 对于先前表中的每个行组合，从该表中读取一行。 除了system和const类型之外，这是最好的连接类型。当连接使用索引的所有部分并且索引是PRIMARY KEY或UNIQUE NOT NULL索引时使用它。 | |
| 4 | ref | 连接不能基于关键字选择单个行，可能查找到多个符合条件的行。叫做ref是因为索引要跟某个参考值相比较。这个参考值或者是一个常数，或者是来自一个表里的多表查询的结果值。 | |
| 5 | fulltext | 使用全文索引执行连接，这种一般用的比较少，因为对中文支持不好。 | |
| 6 | ref_or_null | 与ref类似，但是MySQL必须在初次查找的结果里找出null条目，然后进行二次查找。 | |
| 7 | index_merge | 说明索引合并优化被使用了。例如：SELECT * FROM ref_table WHERE key_column=expr OR key_column IS NULL; | |
| 8 | unique_subquery | 在某些IN查询中使用此种类型，而不是常规的ref，如：value IN (SELECT primary_key FROM single_table WHERE some_expr) | |
| 9 | index_subquery | 在某些IN查询中使用此种类型，与unique_subquery类似，但是查询的是非唯一性索引：value IN (SELECT key_column FROM single_table WHERE some_expr) | |
| 10 | range | 只检索给定范围的行，使用一个索引来选择行。key列显示使用了哪个索引。当关键字列使用=、<>、>、>=、<、<=、IS NULL、<=>、BETWEEN、IN这些操作符与常量比较时，将会使用range类型。 | |
| 11 | index | 全表扫描，只是扫描表的时候按照索引次序进行而不是行。此时Extra会显示：using index，主要优点就是避免了排序，但是开销仍然非常大。 | |
| 12 | all | 最坏的情况，从头到尾全表扫描。一般可通过给适当的列添加索引以避免该类型。 | |
| 6 | possible_keys | 可能使用到的索引，用于指出MySQL能在该表中使用哪些索引有助于查询。如果为空，说明没有可用的索引。 | |
| 7 | key | MySQL实际从possible_key选择使用的索引。如果为NULL，则没有使用索引。很少的情况下，MYSQL会选择优化不足的索引。这种情况下，可以在SELECT语句中使用USE INDEX（indexname）来强制使用一个索引或者用IGNORE INDEX（indexname）来强制MYSQL忽略索引。 | |
| 8 | key_len | 使用的索引的长度。在不损失精确性的情况下，长度越短越好 | |
| 9 | ref | 显示索引的哪一列被使用了 | |
| 10 | rows | MYSQL认为必须检查的用来返回请求数据的行数 | |
| 11 | filtered | 输出表所引用的行 | |
| 10 | 子序号 | extra | 额外的信息。这列可能显示的信息非常多，这里我只列几个常见的，其它的请参见： EXPLAIN Extra Information |
| 1 | Using where | WHERE子句用于限制与下一个表匹配或发送到客户端的行。 | |
| 2 | Using filesort | 表示MySQL会对结果使用一个外部索排序，而不是从表里按索引次序读到相关内容。可能在内存或者磁盘上进行序。MySQL中无法利用索引完成的排序操作称为“文件排序”。 | |
| 3 | Using temporary | 表示MySQL在对查询结果排序时使用临时表。常见于排序order by和分组查询group by。 | |
| 4 | Using index | 当type=index时，Extra会显示Using index，因为type=index代表全表扫描，虽然情况比type=all好一点，但仍需尽量避免。 | |
以上为explain分析select语句的输出结果详解，对于select_type和type，都是从好到坏排列，所以尽量优化为好的类型就可以。
