参考文档

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不访问索引或者表，就能返回结果