explain|explain --查询分析学习 explain--查询分析学习

简介：
MySQL 提供了一个 explain 命令, 它可以对 SELECT 语句进行分析, 并输出 SELECT 执行的详细信息, 以供开发人员针对性优化.
用法
explain 命令用法十分简单, 在 SELECT 语句前加上 explain就可以了, 例如:
`explain select * from a where name='小王' AND a_tx='我爱中国';

文章图片

介绍各列含义
各列的含义如下:
id: SELECT 查询的标识符. 每个 SELECT 都会自动分配一个唯一的标识符.
select_type: SELECT 查询的类型.
table: 查询的是哪个表
partitions: 匹配的分区
type: join 类型
possible_keys: 此次查询中可能选用的索引
key: 此次查询中确切使用到的索引.
key_len:表示查询优化器使用了索引的字节数. 这个字段可以评估组合索引是否完全被使用, 或只有最左部分字段被使用到.
ref: 哪个字段或常数与 key 一起被使用
rows: 显示此查询一共扫描了多少行. 这个是一个估计值.
filtered: 表示此查询条件所过滤的数据的百分比
extra: 额外的信息
学习各列信息
select_type select_type表示了查询的类型, 它的常用取值有:

SIMPLE, 表示此查询不包含 UNION 查询或子查询PRIMARY, 表示此查询是最外层的查询UNION, 表示此查询是 UNION 的第二或随后的查询DEPENDENT UNION, UNION 中的第二个或后面的查询语句, 取决于外面的查询UNION RESULT, UNION 的结果SUBQUERY, 子查询中的第一个 SELECTDEPENDENT SUBQUERY: 子查询中的第一个 SELECT, 取决于外面的查询. 即子查询依赖于外层查询的结果.最常见的查询类别应该是 SIMPLE 了

type type字段比较重要, 它提供了判断查询是否高效的重要依据依据. 通过 type字段, 我们判断此次查询是全表扫描还是索引扫描等.
下面例子使用的俩张表

文章图片
【explain|explain --查询分析学习】
type常用的取值有:

1.'system': 表中只有一条数据. 这个类型是特殊的 const 类型.2.'const': 针对主键或唯一索引的等值查询扫描, 最多只返回一行数据. const 查询速度非常快, 因为它仅仅读取一次即可. 例如下面的这个查询, 它使用了主键索引, 因此 type 就是 const 类型的. mysql> explain select * from a where id = 1; +----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------+ | id | select_type | table | partitions | type| possible_keys | key| key_len | ref| rows | filtered | Extra | +----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------+ |1 | SIMPLE| a| NULL| const | PRIMARY| PRIMARY | 4| const |1 |100.00 | NULL| +----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------+3.'eq_ref':此类型通常出现在多表的 join 查询, 表示对于前表的每一个结果, 都只能匹配到后表的一行结果. 并且查询的比较操作通常是 =, 查询效率较高. 例如: mysql> explain select * from a,b where a.id=b.id; +----+-------------+-------+------------+--------+---------------+---------+---------+------------+------+----------+-------+ | id | select_type | table | partitions | type| possible_keys | key| key_len | ref| rows | filtered | Extra | +----+-------------+-------+------------+--------+---------------+---------+---------+------------+------+----------+-------+ |1 | SIMPLE| b| NULL| ALL| PRIMARY| NULL| NULL| NULL|1 |100.00 | NULL| |1 | SIMPLE| a| NULL| eq_ref | PRIMARY| PRIMARY | 4| wenda.b.id |1 |100.00 | NULL| +----+-------------+-------+------------+--------+---------------+---------+---------+------------+------+----------+-------+4.'ref': 此类型通常出现在多表的 join 查询, 针对于非唯一或非主键索引, 或者是使用了最左前缀规则索引的查询. 例如给a表的name增加普通索引 mysql> alter table a add index aname_index(name); 给b表的name增加普通索引 mysql> alter table b add index bname_index(name); 查询 mysql> explain select * from a,b where a.name=b.name; +----+-------------+-------+------------+------+---------------+-------------+---------+--------------+------+----------+-------------+ | id | select_type | table | partitions | type | possible_keys | key| key_len | ref| rows | filtered | Extra| +----+-------------+-------+------------+------+---------------+-------------+---------+--------------+------+----------+-------------+ |1 | SIMPLE| b| NULL| ALL| bname_index| NULL| NULL| NULL|3 |100.00 | Using where | |1 | SIMPLE| a| NULL| ref| aname_index| aname_index | 83| wenda.b.name |1 |100.00 | NULL| +----+-------------+-------+------------+------+---------------+-------------+---------+--------------+------+----------+-------------+5.'range': 表示使用索引范围查询, 通过索引字段范围获取表中部分数据记录. 这个类型通常出现在 =, <>, >, >=, <, <=, IS NULL, <=>, BETWEEN, IN() 操作中. 当 type 是 range 时, 那么explain 输出的 ref 字段为 NULL, 并且 key_len 字段是此次查询中使用到的索引的最长的那个例如: mysql> explain select * from a where id between 2 and 4; +----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+-------------+ | id | select_type | table | partitions | type| possible_keys | key| key_len | ref| rows | filtered | Extra| +----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+-------------+ |1 | SIMPLE| a| NULL| range | PRIMARY| PRIMARY | 4| NULL |3 |100.00 | Using where | +----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+-------------+6.'index': 表示全索引扫描(full index scan), 和 ALL 类型类似, 只不过 ALL 类型是全表扫描, 而 index 类型则仅仅扫描所有的索引, 而不扫描数据. index 类型通常出现在: 所要查询的数据直接在索引树中就可以获取到, 而不需要扫描数据. 当是这种情况时, Extra 字段会显示 Using index. 例如: mysql> explain select name from a; +----+-------------+-------+------------+-------+---------------+-------------+---------+------+------+----------+-------------+ | id | select_type | table | partitions | type| possible_keys | key| key_len | ref| rows | filtered | Extra| +----+-------------+-------+------------+-------+---------------+-------------+---------+------+------+----------+-------------+ |1 | SIMPLE| a| NULL| index | NULL| aname_index | 83| NULL |4 |100.00 | Using index | +----+-------------+-------+------------+-------+---------------+-------------+---------+------+------+----------+-------------+ 我们查询的 name 字段恰好是一个索引,7.'ALL': 表示全表扫描, 这个类型的查询是性能最差的查询之一. 通常来说, 我们的查询不应该出现 ALL 类型的查询, 因为这样的查询在数据量大的情况下, 对数据库的性能是巨大的灾难. 如一个查询是 ALL 类型查询, 那么一般来说可以对相应的字段添加索引来避免. mysql> explain select a_tx from a; +----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------+ | id | select_type | table | partitions | type | possible_keys | key| key_len | ref| rows | filtered | Extra | +----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------+ |1 | SIMPLE| a| NULL| ALL| NULL| NULL | NULL| NULL |4 |100.00 | NULL| +----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------+

通常来说, 不同的 type 类型的性能关系如下:
ALL < index < range ~ index_merge < ref < eq_ref < const < system
ALL 类型因为是全表扫描, 因此在相同的查询条件下, 它是速度最慢的.
而index类型的查询虽然不是全表扫描, 但是它扫描了所有的索引, 因此比ALL 类型的稍快.
后面的几种类型都是利用了索引来查询数据, 因此可以过滤部分或大部分数据, 因此查询效率就比较高了.
key_len
表示查询优化器使用了索引的字节数. 这个字段可以评估组合索引是否完全被使用, 或只有最左部分字段被使用到.
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 的, 则没有此属性.

例如

mysql> alter table a add index more_index(id,name(5));

建立一个组合索引使用id(int 类型)和name的前5个字符(varchar类型)

mysql> mysql> show index from a; +-------+------------+-------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+ | Table | Non_unique | Key_name| Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | Visible | Expression | +-------+------------+-------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+ | a|1 | aname_index |1 | name| A|3 |NULL |NULL | YES| BTREE||| YES| NULL| | a|1 | more_index|1 | id| A|4 |NULL |NULL || BTREE||| YES| NULL| | a|1 | more_index|2 | name| A|3 |5 |NULL | YES| BTREE||| YES| NULL| +-------+------------+-------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+ #有一个组合索引more_index(id,name); mysql> explain select * from a where id = 4; +----+-------------+-------+------------+------+---------------+------------+---------+-------+------+----------+-------+ | id | select_type | table | partitions | type | possible_keys | key| key_len | ref| rows | filtered | Extra | +----+-------------+-------+------------+------+---------------+------------+---------+-------+------+----------+-------+ |1 | SIMPLE| a| NULL| ref| more_index| more_index | 4| const |1 |100.00 | NULL| +----+-------------+-------+------------+------+---------------+------------+---------+-------+------+----------+-------+ #只有最左边id被使用到 id为id类型所以是4mysql> explain select * from a where id = 4 and name='小王'; +----+-------------+-------+------------+------+------------------------+------------+---------+-------------+------+----------+-------------+ | id | select_type | table | partitions | type | possible_keys| key| key_len | ref| rows | filtered | Extra| +----+-------------+-------+------------+------+------------------------+------------+---------+-------------+------+----------+-------------+ |1 | SIMPLE| a| NULL| ref| aname_index,more_index | more_index | 27| const,const |1 |100.00 | Using where | +----+-------------+-------+------------+------+------------------------+------------+---------+-------------+------+----------+-------------+ 组合索引使用到了name是 utf8mb4 编码，所以是 4 + 4*5+2 = 26；加上name字段允许为NULL所以＋1 = 27

rows rows 也是一个重要的字段. MySQL 查询优化器根据统计信息, 估算 SQL 要查找到结果集需要扫描读取的数据行数.
这个值非常直观显示 SQL 的效率好坏, 原则上 rows 越少越好.
Extra explain 中的很多额外的信息会在 Extra字段显示, 常见的有以下几种内容:

1.'Using index': 表示查询在索引树中就可查找所需数据, 不用扫描表数据文件, 往往说明性能不错2.'Using temporary':查询有使用临时表, 一般出现于排序, 分组和多表 join 的情况, 查询效率不高, 建议优化.3.'Using filesort':当 Extra 中有 Using filesort 时, 表示 MySQL 需额外的排序操作, 不能通过索引顺序达到排序效果. 一般有 Using filesort, 都建议优化去掉, 因为这样的查询 CPU 资源消耗大. 例如： mysql> explain select id, name from a order by name; +----+-------------+-------+------------+-------+---------------+------------+---------+------+------+----------+-----------------------------+ | id | select_type | table | partitions | type| possible_keys | key| key_len | ref| rows | filtered | Extra| +----+-------------+-------+------------+-------+---------------+------------+---------+------+------+----------+-----------------------------+ |1 | SIMPLE| a| NULL| index | NULL| more_index | 87| NULL |4 |100.00 | Using index; Using filesort | +----+-------------+-------+------------+-------+---------------+------------+---------+------+------+----------+-----------------------------+#我们的组合索引是more_index（id，name）但是上面的查询中根据 name 来排序, 因此不能使用索引进行优化, 进而会产生 Using filesort. 如果我们将排序依据改为 ORDER BY id, name, 那么就不会出现 Using filesort 了. 例如: mysql> explain select id, name from a order by id,name; +----+-------------+-------+------------+-------+---------------+------------+---------+------+------+----------+-------------+ | id | select_type | table | partitions | type| possible_keys | key| key_len | ref| rows | filtered | Extra| +----+-------------+-------+------------+-------+---------------+------------+---------+------+------+----------+-------------+ |1 | SIMPLE| a| NULL| index | NULL| more_index | 87| NULL |4 |100.00 | Using index | +----+-------------+-------+------------+-------+---------------+------------+---------+------+------+----------+-------------+