参考文档
https://dev.mysql.com/doc/refman/5.7/en/explain-output.html
https://dev.mysql.com/doc/refman/5.7/en/explain.html
https://blog.csdn.net/baiducheng/article/details/79725535
语法
{EXPLAIN | DESCRIBE | DESC}
tbl_name [col_name | wild]
{EXPLAIN | DESCRIBE | DESC}
[explain_type]
{explainable_stmt | FOR CONNECTION connection_id}
explain_type: {
EXTENDED
| PARTITIONS
| FORMAT = format_name
}
format_name: {
TRADITIONAL
| JSON
}
explainable_stmt: {
SELECT statement
| DELETE statement
| INSERT statement
| REPLACE statement
| UPDATE statement
}
EXPLAIN 、DESCRIBE 、DESC效果是一样的,DESCRIBE、DESC多用于查看表结构,类似于SHOW COLUMNS,例如,
mysql> desc select * from purchase_order;
+----+-------------+----------------+------+---------------+------+---------+------+------+-------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+----------------+------+---------------+------+---------+------+------+-------+
| 1 | SIMPLE | purchase_order | ALL | NULL | NULL | NULL | NULL | 158 | NULL |
+----+-------------+----------------+------+---------------+------+---------+------+------+-------+
1 row in set (0.00 sec)
mysql> explain select * from purchase_order;
+----+-------------+----------------+------+---------------+------+---------+------+------+-------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+----------------+------+---------------+------+---------+------+------+-------+
| 1 | SIMPLE | purchase_order | ALL | NULL | NULL | NULL | NULL | 158 | NULL |
+----+-------------+----------------+------+---------------+------+---------+------+------+-------+
1 row in set (0.01 sec)
mysql> show columns in purchase_order;
+-------------------+---------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------------------+---------------+------+-----+---------+----------------+
| id | bigint(20) | NO | PRI | NULL | auto_increment |
| serial_number | char(19) | YES | | NULL | |
+-------------------+---------------+------+-----+---------+----------------+
20 rows in set (0.00 sec)
mysql> desc purchase_order;
+-------------------+---------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------------------+---------------+------+-----+---------+----------------+
| id | bigint(20) | NO | PRI | NULL | auto_increment |
| serial_number | char(19) | YES | | NULL | |
+-------------------+---------------+------+-----+---------+----------------+
20 rows in set (0.00 sec)
SHOW COLUMNS Syntax
一个简单的执行计划
在sql前面加EXPLAIN查看执行假话
mysql> EXPLAIN SELECT * from purchase_order;
+----+-------------+----------------+------+---------------+------+---------+------+------+-------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+----------------+------+---------------+------+---------+------+------+-------+
| 1 | SIMPLE | purchase_order | ALL | NULL | NULL | NULL | NULL | 11 | NULL |
+----+-------------+----------------+------+---------------+------+---------+------+------+-------+
1 row in set (0.00 sec)
先概括的看一下执行计划返回列的含义
列 | 说明 |
---|---|
select_type | select类型 |
table | 输出结果集的表 |
type | 访问类型 |
下面详细介绍以下每个列的含义
- select_type
这个参数表示select类型,每个取值代表的含义如下
取值 | 说明 |
---|---|
SIMPLE | 简单表,即不使用关联查询、自查询 |
PRIMARY | 主查询,即最外层的查询 |
SUBQUERY | 子查询的第一个select |
UNION | UNION中的第二个或者后面的查询语句 |
- type
从上之下,性能由差变强
取值 | 说明 |
---|---|
ALL | 全表扫描,mysql遍历全表来找到匹配的行 |
index | 索引全扫描,mysql遍历全部索引来查询匹配的行 |
range | 索引范围扫描,常见的操作符,<,<=,>,>=,between |
ref | 使用非唯一索引扫描、唯一索引前缀扫描 |
eq_ref | 唯一索引 |
const,system | |
NULL | mysql不访问索引或者表,就能返回结果 |