mysql使用explain xxx
可以分析sql
语句的性能,本文详细总结一下每个字段的意义
MYSQL 5.6.3
以前只能EXPLAIN SELECT
; MYSQL5.6.3
以后就可以EXPLAIN SELECT,UPDATE,DELETE
概述
首先看一个简单的示例
mysql> explain select * from staff;
+----+-------------+-------+------+---------------+------+---------+------+------+-------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+------+---------------+------+---------+------+------+-------+
| 1 | SIMPLE | staff | ALL | NULL | NULL | NULL | NULL | 2 | NULL |
+----+-------------+-------+------+---------------+------+---------+------+------+-------+
1 row in set
先总结下所有字段的意义
column | 含义 |
---|---|
id | 查询序号 |
select_type | 查询类型 |
table | 表名 |
type | join 类型 |
prossible_keys | 可能会选择的索引 |
key | 实际选择的索引 |
key_len | 索引的长度 |
ref | 与索引作比较的列 |
rows | 要检索的行数(估算值) |
Extra | 额外信息 |
再详细介绍每个字段的意义
1. id
SQL查询中的序列号
id列数字越大越先执行,如果说数字一样大,那么就从上往下依次执行。
例如:
mysql> explain select * from (select * from ( select * from t3 where id=3952602) a) b;
+----+-------------+------------+--------+-------------------+---------+---------+------+------+-------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+------------+--------+-------------------+---------+---------+------+------+-------+
| 1 | PRIMARY | <derived2> | system | NULL | NULL | NULL | NULL | 1 | |
| 2 | DERIVED | <derived3> | system | NULL | NULL | NULL | NULL | 1 | |
| 3 | DERIVED | t3 | const | PRIMARY,idx_t3_id | PRIMARY | 4 | | 1 | |
+----+-------------+------------+--------+-------------------+---------+---------+------+------+-------+
sql
语句从内到外按照id
的顺序3 => 2 => 1
依次执行
2. select_type
查询的类型,可以是下表的任何一种类型
select_type | 类型说明 |
---|---|
SIMPLE | 简单SELECT (不使用UNION 或子查询) |
PRIMARY | 最外层的SELECT |
UNION | UNION 中第二个或之后的SELECT 语句 |
DEPENDENT UNION | UNION 中第二个或之后的SELECT 语句取决于外面的查询 |
UNION RESULT | UNION 的结果 |
SUBQUERY | 子查询中的第一个SELECT |
DEPENDENT SUBQUERY | 子查询中的第一个SELECT , 取决于外面的查询 |
DERIVED | 衍生表(FROM 子句中的子查询) |
MATERIALIZED | 物化子查询 |
UNCACHEABLE SUBQUERY | 结果集无法缓存的子查询,必须重新评估外部查询的每一行 |
UNCACHEABLE UNION | UNION 中第二个或之后的SELECT ,属于无法缓存的子查询 |
SIMPLE
示例:
mysql> explain select * from t3 where id=3952602;
+----+-------------+-------+-------+-------------------+---------+---------+-------+------+-------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+-------+-------------------+---------+---------+-------+------+-------+
| 1 | SIMPLE | t3 | const | PRIMARY,idx_t3_id | PRIMARY | 4 | const | 1 | |
+----+-------------+-------+-------+-------------------+---------+---------+-------+------+-------+
PRIMARY
示例:
mysql> explain select * from (select * from t3 where id=3952602) a ;
+----+-------------+------------+--------+-------------------+---------+---------+------+------+-------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+------------+--------+-------------------+---------+---------+------+------+-------+
| 1 | PRIMARY | <derived2> | system | NULL | NULL | NULL | NULL | 1 | |
| 2 | DERIVED | t3 | const | PRIMARY,idx_t3_id | PRIMARY | 4 | | 1 | |
+----+-------------+------------+--------+-------------------+---------+---------+------+------+-------+
UNION、UNION RESULT
示例:
mysql> explain select * from t3 where id=3952602 union all select * from t3 ;
+----+--------------+------------+-------+-------------------+---------+---------+-------+------+-------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+--------------+------------+-------+-------------------+---------+---------+-------+------+-------+
| 1 | PRIMARY | t3 | const | PRIMARY,idx_t3_id | PRIMARY | 4 | const | 1 | |
| 2 | UNION | t3 | ALL | NULL | NULL | NULL | NULL | 1000 | |
|NULL | UNION RESULT | <union1,2> | ALL | NULL | NULL | NULL | NULL | NULL | |
+----+--------------+------------+-------+-------------------+---------+---------+-------+------+-------+
3. table
查询的表名。不一定是实际存在的表。
可以为如下的值:
-
<unionM,N>
: 引用id
为M
和N
UNION
后的结果。 -
<derivedN>
: 引用id
为N
的结果派生出的表。派生表可以是一个结果集,例如派生自FROM
中子查询的结果。 -
<subqueryN>
: 引用id
为N
的子查询结果物化得到的表。即生成一个临时表保存子查询的结果。
例如:
mysql> explain select * from (select * from ( select * from t3 where id=3952602) a) b;
+----+-------------+------------+--------+-------------------+---------+---------+------+------+-------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+------------+--------+-------------------+---------+---------+------+------+-------+
| 1 | PRIMARY | <derived2> | system | NULL | NULL | NULL | NULL | 1 | |
| 2 | DERIVED | <derived3> | system | NULL | NULL | NULL | NULL | 1 | |
| 3 | DERIVED | t3 | const | PRIMARY,idx_t3_id | PRIMARY | 4 | | 1 | |
+----+-------------+------------+--------+-------------------+---------+---------+------+------+-------+
4. type(重要)
这是最重要的字段之一,显示查询使用了何种类型。
下表从上到下性能依次递减
type | 说明 |
---|---|
system | 表中只有一行数据或者是空表,这是const 类型的一个特例。且只能用于myisam 和memory 表。如果是Innodb 引擎表,type 列在这个情况通常都是all 或者index |
const | 最多只有一行记录匹配。当联合主键或唯一索引的所有字段跟常量值比较时,join 类型为const 。其他数据库也叫做唯一索引扫描 |
eq_ref | 多表join 时,对于来自前面表的每一行,在当前表中只能找到一行。它用在一个索引的所有部分被联接使用并且索引是UNIQUE 或PRIMARY KEY |
ref | 对于来自前面表的每一行,在此表的索引中可以匹配到多行。 |
fulltext | 使用全文索引的时候是这个类型。要注意,全文索引的优先级很高,若全文索引和普通索引同时存在时,mysql 不管代价,优先选择使用全文索引 |
ref_or_null | 该联接类型如同ref ,但是添加了MySQL 可以专门搜索包含NULL 值的行 |
index_merge | 表示查询使用了两个以上的索引,最后取交集或者并集,常见and ,or 的条件使用了不同的索引 |
unique_subquery | 用于where 中的in 形式子查询,子查询返回不重复值唯一值,可以完全替换子查询,效率更高。 该类型替换了下面形式的IN 子查询的ref : value IN (SELECT primary_key FROM single_table WHERE some_expr) |
index_subquery | 该联接类型类似于unique_subquery 。适用于非唯一索引,可以返回重复值。 |
range | 索引范围查询,常见于使用 =, <>, >, >=, <, <=, IS NULL, <=>, BETWEEN, IN()或者like等运算符的查询中。比如:SELECT * FROM tbl_name WHERE key_column BETWEEN 10 and 20; |
index | 索引全表扫描,把索引从头到尾扫一遍。这里包含两种情况: 一种是查询使用了覆盖索引,那么它只需要扫描索引就可以获得数据,这个效率要比全表扫描要快,因为索引通常比数据表小,而且还能避免二次查询。在extra 中显示Using index ,反之,如果在索引上进行全表扫描,没有Using index 的提示。 |
ALL | 全表扫描,性能最差 |
const
示例:
mysql> explain select * from t3 where id=3952602;
+----+-------------+-------+-------+-------------------+---------+---------+-------+------+-------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+-------+-------------------+---------+---------+-------+------+-------+
| 1 | SIMPLE | t3 | const | PRIMARY,idx_t3_id | PRIMARY | 4 | const | 1 | |
+----+-------------+-------+-------+-------------------+---------+---------+-------+------+-------+
eq_ref
示例:
mysql> create unique index idx_t3_id on t3(id) ;
Query OK, 1000 rows affected (0.03 sec)
Records: 1000 Duplicates: 0 Warnings: 0
mysql> explain select * from t3,t4 where t3.id=t4.accountid;
+----+-------------+-------+--------+-------------------+-----------+---------+----------------------+------+-------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+--------+-------------------+-----------+---------+----------------------+------+-------+
| 1 | SIMPLE | t4 | ALL | NULL | NULL | NULL | NULL | 1000 | |
| 1 | SIMPLE | t3 | eq_ref | PRIMARY,idx_t3_id | idx_t3_id | 4 | dbatest.t4.accountid | 1 | |
+----+-------------+-------+--------+-------------------+-----------+---------+----------------------+------+-------+
index_merge
示例:
mysql> explain select * from t4 where id=3952602 or accountid=31754306 ;
+----+-------------+-------+-------------+----------------------------+----------------------------+---------+------+------+------------------------------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+-------------+----------------------------+----------------------------+---------+------+------+------------------------------------------------------+
| 1 | SIMPLE | t4 | index_merge | idx_t4_id,idx_t4_accountid | idx_t4_id,idx_t4_accountid | 4,4 | NULL | 2 | Using union(idx_t4_id,idx_t4_accountid); Using where |
+----+-------------+-------+-------------+----------------------------+----------------------------+---------+------+------+------------------------------------------------------+
range
示例:
mysql> explain SELECT * FROM t3 WHERE id BETWEEN 10 and 1000;
+----+-------------+-------+-------+-------------------+-----------+---------+------+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+-------+-------------------+-----------+---------+------+------+-------------+
| 1 | SIMPLE | t3 | range | PRIMARY,idx_t3_id | idx_t3_id | 4 | NULL | 2 | Using where |
+----+-------------+-------+-------+-------------------+-----------+---------+------+------+-------------+
5. possible_keys
查询可能使用到的索引都会在这里列出来
6. key
查询真正使用到的索引。
select_type
为index_merge
时,这里可能出现两个以上的索引,其他的select_type
这里只会出现一个。
7. key_len
查询用到的索引长度(字节数)
如果是单列索引,那就整个索引长度算进去,如果是多列索引,那么查询不一定都能使用到所有的列,用多少算多少。
在不损失精确性的情况下,长度越短越好
key_len只计算where
条件用到的索引长度,而排序和分组就算用到了索引,也不会计算到key_len
中
8. ref
和前方表连接的字段
如果是使用的常数等值查询,这里会显示const
,如果是连接查询,被驱动表的执行计划这里会显示驱动表的关联字段,如果是条件使用了表达式或者函数,或者条件列发生了内部隐式转换,这里可能显示为func
9. rows(重要)
这是mysql
估算的需要扫描的行数(不是精确值)。
这个值非常直观显示 SQL
的效率好坏, 原则上 rows
越少越好.
10. Extra(重要)
EXplain
中的很多额外的信息会在 Extra
字段显示, 常见的有以下几种内容:
Extra | 说明 |
---|---|
Distinct | 一旦找到了与行相联合匹配的行,就不再搜索了。在select 部分使用了distinc 关键字会出现 |
Using filesort | 表示需额外的排序操作, 不能通过索引顺序达到排序效果。看到这个的时候,查询就需要优化了。 |
Using index | “覆盖索引扫描”, 表示查询在索引树中就可查找所需数据, 不用扫描表数据文件, 往往说明性能不错 |
Using temporary | 查询有使用临时表, 一般出现于排序, 分组和多表 join 的情况, 查询效率不高, 建议优化. |
以上就是explain
各个字段的意义,这里一般做个进阶了解
在实际开发中,为了提升开发效率,我们可以选择搭配一些可视化SQL优化工具使用,可以参考我的这篇文章《小白也会用的SQL优化工具推荐》