本文深入解析了Explain命令在数据库性能优化中的应用，通过具体示例介绍了如何利用Explain分析SQL语句的执行效果，选择更优索引及优化查询语句，提升数据库性能。
Explain命令在解决数据库性能上是第一推荐使用命令，大部分的性能问题可以通过此命令来简单的解决，Explain可以用来查看SQL语句的执行效 果，可以帮助选择更好的索引和优化查询语句，写出更好的优化语句。
Explain语法：explain select … from … [where …]
例如：explain select * from news;
输出：+----+-------------+-------+-------+-------------------+---------+---------+-------+------
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+-------+-------------------+---------+---------+-------+------
下面对各个属性进行了解：
1、id：这是SELECT的查询序列号
2、select_type：select_type就是select的类型，可以有以下几种：
SIMPLE：简单SELECT(不使用UNION或子查询等)
PRIMARY：最外面的SELECT
UNION：UNION中的第二个或后面的SELECT语句
DEPENDENT UNION：UNION中的第二个或后面的SELECT语句，取决于外面的查询
UNION RESULT：UNION的结果。
SUBQUERY：子查询中的第一个SELECT
DEPENDENT SUBQUERY：子查询中的第一个SELECT，取决于外面的查询
DERIVED：导出表的SELECT(FROM子句的子查询)
3、table：显示这一行的数据是关于哪张表的
4、type：这列最重要，显示了连接使用了哪种类别,有无使用索引，是使用Explain命令分析性能瓶颈的关键项之一。
结果值从好到坏依次是：
system > const > eq_ref > ref > fulltext > ref_or_null > index_merge > unique_subquery > index_subquery > range > index > ALL
一般来说，得保证查询至少达到range级别，最好能达到ref，否则就可能会出现性能问题。
NULL：mysql能够在优化阶段分解查询语句，在执行阶段用不着再访问表或索引。例如：在索引列中选取最小值，可以单独查找索引来完成，不需要在执行时访问表
mysql> explain select min(id) from film; +----+-------------+-------+------+---------------+------+---------+------+------+------------------------------+ | 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 | +----+-------------+-------+------+---------------+------+---------+------+------+------------------------------+
const, system：mysql能对查询的某部分进行优化并将其转化成一个常量（可以看show warnings 的结果）。用于 primary key 或 unique key 的所有列与常数比较时，所以表最多有一个匹配行，读取1次，速度比较快。mysql> explain extended select * from (select * from film where id = 1) tmp; +----+-------------+------------+--------+---------------+---------+---------+-------+------+----------+-------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+------------+--------+---------------+---------+---------+-------+------+----------+-------+ | 1 | PRIMARY | <derived2> | system | NULL | NULL | NULL | NULL | 1 | 100.00 | NULL | | 2 | DERIVED | film | const | PRIMARY | PRIMARY | 4 | const | 1 | 100.00 | NULL | +----+-------------+------------+--------+---------------+---------+---------+-------+------+----------+-------+ mysql> show warnings; +-------+------+---------------------------------------------------------------+ | Level | Code | Message | +-------+------+---------------------------------------------------------------+ | Note | 1003 | /* select#1 */ select '1' AS `id`,'film1' AS `name` from dual | +-------+------+---------------------------------------------------------------+eq_ref：primary key 或 unique key 索引的所有部分被连接使用 ，最多只会返回一条符合条件的记录。这可能是在 const 之外最好的联接类型了，简单的 select 查询不会出现这种 type。
mysql> explain select * from film_actor left join film on film_actor.film_id = film.id; +----+-------------+------------+--------+---------------+-------------------+---------+-------------------------+------+-------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+------------+--------+---------------+-------------------+---------+-------------------------+------+-------------+ | 1 | SIMPLE | film_actor | index | NULL | idx_film_actor_id | 8 | NULL | 3 | Using index | | 1 | SIMPLE | film | eq_ref | PRIMARY | PRIMARY | 4 | test.film_actor.film_id | 1 | NULL | +----+-------------+------------+--------+---------------+-------------------+---------+-------------------------+------+-------------+
ref：相比eq_ref，不使用唯一索引，而是使用普通索引或者唯一性索引的部分前缀，索引要和某个值相比较，可能会找到多个符合条件的行。1. 简单 select 查询，name是普通索引（非唯一索引） mysql> explain select * from film where name = "film1"; +----+-------------+-------+------+---------------+----------+---------+-------+------+--------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-------+------+---------------+----------+---------+-------+------+--------------------------+ | 1 | SIMPLE | film | ref | idx_name | idx_name | 33 | const | 1 | Using where; Using index | +----+-------------+-------+------+---------------+----------+---------+-------+------+--------------------------+ 2.关联表查询，idx_film_actor_id是film_id和actor_id的联合索引，这里使用到了film_actor的左边前缀film_id部分。 mysql> explain select * from film left join film_actor on film.id = film_actor.film_id; +----+-------------+------------+-------+-------------------+-------------------+---------+--------------+------+-------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+------------+-------+-------------------+-------------------+---------+--------------+------+-------------+ | 1 | SIMPLE | film | index | NULL | idx_name | 33 | NULL | 3 | Using index | | 1 | SIMPLE | film_actor | ref | idx_film_actor_id | idx_film_actor_id | 4 | test.film.id | 1 | Using index | +----+-------------+------------+-------+-------------------+-------------------+---------+--------------+------+-------------+
ref_or_null：类似ref，但是可以搜索值为NULL的行。mysql> explain select * from film where name = "film1" or name is null; +----+-------------+-------+-------------+---------------+----------+---------+-------+------+--------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-------+-------------+---------------+----------+---------+-------+------+--------------------------+ | 1 | SIMPLE | film | ref_or_null | idx_name | idx_name | 33 | const | 2 | Using where; Using index | +----+-------------+-------+-------------+---------------+----------+---------+-------+------+--------------------------+
index_merge：表示使用了索引合并的优化方法。 例如下表：id是主键，tenant_id是普通索引。or 的时候没有用 primary key，而是使用了 primary key(id) 和 tenant_id 索引mysql> explain select * from role where id = 11011 or tenant_id = 8888; +----+-------------+-------+-------------+-----------------------+-----------------------+---------+------+------+-------------------------------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-------+-------------+-----------------------+-----------------------+---------+------+------+-------------------------------------------------+ | 1 | SIMPLE | role | index_merge | PRIMARY,idx_tenant_id | PRIMARY,idx_tenant_id | 4,4 | NULL | 134 | Using union(PRIMARY,idx_tenant_id); Using where | +----+-------------+-------+-------------+-----------------------+-----------------------+---------+------+------+-------------------------------------------------+
range：范围扫描通常出现在 in(), between ,> ,<, >= 等操作中。使用一个索引来检索给定范围的行。mysql> explain select * from actor where id > 1; +----+-------------+-------+-------+---------------+---------+---------+------+------+-------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-------+-------+---------------+---------+---------+------+------+-------------+ | 1 | SIMPLE | actor | range | PRIMARY | PRIMARY | 4 | NULL | 2 | Using where | +----+-------------+-------+-------+---------------+---------+---------+------+------+-------------+
index：和ALL一样，不同就是mysql只需扫描索引树，这通常比ALL快一些。mysql> explain select count(*) from film; +----+-------------+-------+-------+---------------+----------+---------+------+------+-------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-------+-------+---------------+----------+---------+------+------+-------------+ | 1 | SIMPLE | film | index | NULL | idx_name | 33 | NULL | 3 | Using index | +----+-------------+-------+-------+---------------+----------+---------+------+------+-------------+
ALL：即全表扫描，意味着mysql需要从头到尾去查找所需要的行。通常情况下这需要增加索引来进行优化了mysql> explain select * from actor; +----+-------------+-------+------+---------------+------+---------+------+------+-------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-------+------+---------------+------+---------+------+------+-------+ | 1 | SIMPLE | actor | ALL | NULL | NULL | NULL | NULL | 2 | NULL | +----+-------------+-------+------+---------------+------+---------+------+------+-------+
5、possible_keys：列指出MySQL能使用哪个索引在该表中找到行
6、key：显示MySQL实际决定使用的键（索引）。如果没有选择索引，键是NULL
7、key_len：显示MySQL决定使用的键长度。如果键是NULL，则长度为NULL。使用的索引的长度。在不损失精确性的情况下，长度越短越好
key_len 的计算规则如下:
字符串
char(n): n 字节长度
varchar(n): 如果是 utf8 编码, 则是 3 n + 2字节; 如果是 utf8mb4 编码, 则是 4 n + 2 字节.
数值类型:
TINYINT: 1字节
SMALLINT: 2字节
MEDIUMINT: 3字节
INT: 4字节
BIGINT: 8字节
时间类型
DATE: 3字节
TIMESTAMP: 4字节
DATETIME: 8字节
字段属性: NULL 属性 占用一个字节. 如果一个字段是 NOT NULL 的, 则没有此属性.
8、ref：显示使用哪个列或常数与key一起从表中选择行。
9、rows：显示MySQL认为它执行查询时必须检查的行数。
10、Extra：包含MySQL解决查询的详细信息，也是关键参考项之一。
Distinct
一旦MYSQL找到了与行相联合匹配的行，就不再搜索了Not exists
MYSQL 优化了LEFT JOIN，一旦它找到了匹配LEFT JOIN标准的行，就不再搜索了
Range checked for each
Record（index map:#）
没有找到理想的索引，因此对于从前面表中来的每一 个行组合，MYSQL检查使用哪个索引，并用它来从表中返回行。这是使用索引的最慢的连接之一Using filesort
看 到这个的时候，查询就需要优化了。MYSQL需要进行额外的步骤来发现如何对返回的行排序。它根据连接类型以及存储排序键值和匹配条件的全部行的行指针来 排序全部行Using index
列数据是从仅仅使用了索引中的信息而没有读取实际的行动的表返回的，这发生在对表 的全部的请求列都是同一个索引的部分的时候Using temporary
看到这个的时候，查询需要优化了。这 里，MYSQL需要创建一个临时表来存储结果，这通常发生在对不同的列集进行ORDER BY上，而不是GROUP BY上Using where
使用了WHERE从句来限制哪些行将与下一张表匹配或者是返回给用户。如果不想返回表中的全部行，并且连接类型ALL或index， 这就会发生，或者是查询有问题
其他一些Tip：
- 当type 显示为 “index” 时，并且Extra显示为“Using Index”， 表明使用了覆盖索引。
扫一扫
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
