Fork me on GitHub

Mysql Explain使用分析

发表于 | 分类于 | 浏览 次

简介

MySQL提供了一个explain命令, 它可以对select语句进行分析, 并输出select执行的详细信息, 以供开发人员针对查询性能进行优化.explain命令用法十分简单, 在select语句前加上explain就可以了, 例如:

explain SELECT * FROM bank where id = 1;

explain命令的输出内容大致如下:
explain-style

explain各列详解

一般12个字段(其中partitions和filtered 5.5版本普通模式下没有），会对重要的字段内容进行分析说明:

id

表示SELECT查询标识符，用于标识执行顺序，基本是数字；执行顺序原则：id数据值大的优先执行,id值相同的从上往下顺序执行

select_type

select_type 表示了查询的类型, 它的常用取值有:

类型 描述 样例
SIMPLE 简单查询，标识查询不包含任何子查询或者UNION语句 explain select * from tabname
PRIMARY 复杂查询的外层查询，一般都在第一行，代表这是一个复杂查询的最外层查询
UNION 复杂查询中，UNION子句第二个或之后的子查询（第一个被标为PRIMARY） explain select from tabname union select from tabname
SUBQUERY 复杂查询的子查询，指不在FROM子句中的那些 explain select * from tabname where id=(select max(id) from tabname)
DEPENDENT SUBQUERY 复杂查询中，依赖外部查询的子查询 explain select * from tabname where id not in(select id from tabname)
DERIVED 在FROM子句中的子查询
DEPENDENT UNION 复杂查询中，依赖外部查询的UNION子句查询 explain select * from tabname where id in (select id from tabname union select id from tabname)
UNION RESULT 复杂查询中，UNION的结果，这是一个从匿名临时表检索最终结果的查询
MATERIALIZED 复杂查询中，物化视图子句
UNCACHEABLE SUBQUERY 不可缓存的子查询
UNCACHEABLE UNION 不可缓存的UNION子查询  

table

表示查询涉及的表或衍生表,如果指定了别名就显示的别名。<derivedN>N就是id值，指该id值对应的那一步操作的结果。还有<unionM,N>这种类型，出现在UNION语句中
<derivedN>类型, eg:
explain select * from (select 1) b;

<unionM,N>类型, eg:
explain select * from bank where id in (select id from bank where id = 1 union select id from bank where id = 2);

partitions

针对MySQL内置分区表，表示当前使用了哪些子分区；用于确认查询对分区的过滤效率

type [重要]

常见以下几种类型，查询效率理论上由好到差
system > const > eq_ref > ref > fulltext > ref_or_null > index_merge > unique_subquery > index_subquery > range > index > ALL
详细说明每种常见type表示的含义：

类型 描述
system 被查询表中有且只有一条数据且为系统表.这是const连接类型的特例
const 针对主键或唯一索引的等值查询扫描，最多只返回一行数据，eg：SELECT * FROM tbl_name WHERE primary_key = 1
eq_ref 当使用了主键索引，唯一索引等只会唯一地找出一条记录的情况，这种类型就会出现，这种类型的性能相当好。
ref 当使用除了主键索引、唯一索引以外的索引来匹配值时，或者使用了最左前缀索引（包括唯一索引和主键的），就会出现这个类型
ref_or_null 连接类型类似ref，只是搜索的行中包含NULL值MySQL做了额外的查找。eg: SELECT * FROM ref_table WHERE key_column = ‘1’ or key_column IS NULL
fulltext 使用全文索引时出现。
index_merge 多个索引同时优化，本质上是每个索引单独使用，再通过某种算法来合并结果
index_subquery 索引替换子查询，如果有这样的语句SELECT * FROM table WHERE value IN(SELECT key_column FROM table where xxx)，IN后面的语句会被索引直接代替，来提高效率
unique_subquery 唯一索引替换子查询，和index_subquery类似，只不过是使用唯一索引来替换IN后面的子句
range 范围扫描，比index强一些，因为它是从索引的某个点开始的，用不着遍历全部索引。一些带有BETWEEN，各种比较符号的语句容易出现这种类型，但是要特别注意IN和OR，这也会显示成range，但是其性能跟index差不多
index 按索引顺序全表扫描，通常性能和全表扫描没什么区别，除非Extra列中有”Using index”字样，那说明使用了覆盖索引，这种情况下要快于ALL，因为直接扫描索引就能获取数据，而索引通常比表小的多
ALL 全表扫描。最差的一种类型，从数据表中逐行查找数据，应考虑查询优化了！

possible_keys

表示MySQL查询优化器发现当前查询可能被使用地索引，但不一定能会利用，如果possible_key的列举的索引越多，往往说明索引创建不合理，查询效率不是最高效；

key

显示MySQL实际决定使用的键（索引）。如果没有选择索引，键是NULL。注意：对于组合索引，查询可能只使用其部分字段

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 的, 则没有此属性.

key_len的长度计算方式例如：

varchr(10)变长字段且允许NULL      : 10*(Char Set：utf8=3,gbk=2,latin1=1)+1(NULL)+2(变长字段)
varchr(10)变长字段且不允许NULL    : 10*(Char Set：utf8=3,gbk=2,latin1=1)+2(变长字段)
char(10)固定字段且允许NULL        : 10*(Char Set：utf8=3,gbk=2,latin1=1)+1(NULL)
char(10)固定字段且允许NULL        : 10*(Char Set：utf8=3,gbk=2,latin1=1)

实例如下:

数据库bank表结构：

CREATE TABLE IF NOT EXISTS `bank` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `bank_name` varchar(100) NOT NULL DEFAULT '',
  `bank_no` varchar(30) DEFAULT '',
  `parent_bank_id` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `foreignkey` (`parent_bank_id`),
  KEY `unionkey` (`bank_name`,`bank_no`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

explain执行结果：

explain-keylen

计算的key_len即 100 ＊ 3 ＋ 2(如果bank_name允许null，则为100 ＊ 3 ＋ 2 ＋ 1)

rows

MySQL查询优化器根据统计信息，估算SQL要查找到结果集需要扫描读取的数据行数; 这个值非常直观显示SQL的效率好坏，原则rows越少越好

Extra

EXplain 中的很多额外的信息会在 Extra 字段显示, 常见的有以下几种:

Extra 描述
using where 使用过滤条件
using index 查询是覆盖了索引的
using temporary 使用临时表存储结果集，常见于排序和分组查询
using filesort 有排序
not exists mysql优化了left join，一旦它找到了匹配left join标准的行， 就不再搜索了
distinct 查找到第一个匹配的行之后，mysql则会停止对当前行的搜索
impossible where where子句总数失败的查询
impossible having having子句总数失败的查询
using join buffer 使用连接缓存
select tables optimized away 没有groupby情况下使用min(),max(),或者count(*)
using index for group-by 与using index类似，在使用group-by时可从索引中找到字段

参考资料
EXPLAIN Output Format
MySQL EXPLAIN 实践汇总
细说MySQL Explain和Optimizer Trace简介
MySQL性能优化神器 Explain使用分析
MySQL总结PPT

轻轻的我走了，正如我轻轻的来