一、概念及作用
使用explain关键字,可以模拟优化器执行的SQL语句，从而知道MYSQL是如何处理sql语句的。通过Explain可以分析查询语句或表结构的性能瓶颈
具体作用：
-
查看表的读取顺序
-
数据读取操作的操作类型
-
查看哪些索引可以使用
-
查看哪些索引被实际使用
-
查看表之间的引用
-
查看每张表有多少行被优化器执行
二、使用方法
使用Explain关键字 放到sql语句前
explain select cus_id from testemployee where cus_id > 10
三、 参数详解
3.1 id（重要）
select查询的序列号，包含一组数字,表示查询中执行select子句或操作表的顺序 值分为三种情况
id值相同
执行顺序由上到下
id不同
如果是子查询,id的序号会递增,id值越大优先级越高,优先被执行
id相同、不同,同时存在
可以认为是一组,从上往下顺序执行
在所有组中,id值越大,优先级越高,越先执行
-- id值相同
EXPLAIN SELECT * from employee e,department d,customer c where e.dep_id = d.id and e.cus_id = c.id;
-- id值不同
EXPLAIN SELECT * from department WHERE id = (SELECT id from employee WHERE id=(SELECT id from customer WHERE id = 1))
-- id值相同 不同都存在 deriverd 衍生出来的虚表
EXPLAIN select * from department d, (select * from employee group by dep_id) t where d.id = t.dep_id;
3.2 select_type
查询类型,主要用于区别普通查询,联合查询,子查询等复杂查询结果值
SIMPLE
简单select查询,查询中不包含子查询或者UNION
PRIMARY
查询中若包含任何复杂的子查询,最外层查询则被标记为primary
SUBQUERY
在select或where中包含了子查询
DERIVED
在from列表中包含的子查询被标记为derived(衍生)把结果放在临时表当
UNION
若第二个select出现的union之后,则被标记为union
若union包含在from子句的子查询中,外层select将被标记为deriver
UNION RESULT
从union表获取结果select,两个UNION合并的结果集在最后
-- union 和 union result 示例
EXPLAIN select * from employee e LEFT JOIN department d on e.dep_id = d.id
UNION
select * from employee e RIGHT JOIN department D ON e.dep_id = D.id
3.3 table
显示这一行的数据是关于哪张表的
3.4 partitions
如果查询是基于分区表的话, 会显示查询访问的分区
3.5 type (重要)
访问类型排列
结果值:(最好到最差) system > const > eq_ref > ref > range > index > ALL
3.5.1 system
-- system 表中有一行记录(系统表) 这是const类型的特例,平时不会出现
explain select HOST from mysql.db where HOST='localhost'
3.5.2 const 表示通过索引一次就找到了
-- const 表示通过索引一次就找到了，const用于比较primary 或者 unique索引. 直接查询主键或者唯一索引，因为只匹配一行数据,所以很快
EXPLAIN select id from employee where id=1
3.5.3 eq_ref 唯一性索引扫描
eq_ref 唯一性索引扫描 对于每个索引键,表中只有一条记录与之匹配, 常见于主键或唯一索引扫描
EXPLAIN select * from employee e,department d where e.id=d.id
3.5.4 ref 非唯一性索引扫描
出现在一对多关系中，例如部门和员工
ref 非唯一性索引扫描,返回匹配某个单独值的所有行,本质上也是一种索引访问,它返回所有匹配某个单独值的行可能会找到多个符合条件的行,所以它应该属于查找和扫描的混合体
EXPLAIN select e.id,e.dep_id,d.id from employee e,department d where e.dep_id = d.id
添加dep_id索引
CREATE index emp_dep_id on employee(dep_id)
执行结果
3.5.5 range
range 只检索给定范围的行,使用一个索引来选择行 一般就是在你的where语句中出现between<>\ in等查询,这种范围扫描索引比全表扫描要好,因为它只需要开始于索引的某一点.而结束语另一点,不用扫描全部索引
explain select * from employee where id>2
3.5.6 index
index与All区别为index类型只遍历索引树,通常比All要快,
因为索引文件通常比数据文件要小all和index都是读全表,
但index是从索引中读取,all是从硬盘当中读取
explain select id from employee
3.5.7 ALL
ALL 将全表进行扫描,从硬盘当中读取数据,如果出现了All 切数据量非常大, 一定要去做优化
explain select * from employee
要求:
一般来说,保证查询至少达到range级别
最好能达到ref
3.6 possible_keys
显示可能应用在这张表中的索引,一个或者多个
查询涉及到的字段上若存在索引,则该索引将被列出,但不一定被查询实际使用
可能自己创建了4个索引,在执行的时候,可能根据内部的自动判断,只使用了3个
-- 可能不会用到索引，实际用到索引
explain select dep_id from employee
-- 可能会使用索引，实际没用到索引
EXPLAIN select * from employee e,department d where e.dep_id = d.id
3.7 key （重要）
实际使用的索引,如果为NULL,则没有使用索引,查询中若使用了覆盖索引 ,
则该索引仅出现在key列表possible_keys与key关系,理论应该用到
哪些索引实际用到了哪些索引覆盖索引 查询的字段和建立的字段刚好吻合,
这种我们称为覆盖索引
3.8 key_len
-- 表示索引中使用的字节数,可通过该列计算查询中使用的索引长度 .
explain select * from employee where dep_id=1 and name='鲁班' and age=10
3.9 ref
索引是否被引入到, 到底引用到了哪几个索引
Explain select * from employee e,department d where e.dep_id = d.id and e.cus_id = 1
Explain select e.dep_id from employee e,department d,customer c where e.dep_id = d.id and e.cus_id = c.id and e.name='鲁班'
3.10 rows
根据表统计信息及索引选用情况,大致估算出找到所需的记录所需要读取的行数,每长表有多少行被优化器查询过
3.11 filtered
满足查询的记录数量的比例，注意是百分比，不是具体记录数 . 值越大越好，filtered列的值依赖统计信息，并不十分准确
-- 满足查询的记录数量的比例，注意是百分比，不是具体记录数 . 值越大越好，filtered列的值依赖统计信息，并不十分准确
Explain select e.dep_id from employee e,department d where e.dep_id = d.id
3.12 Extra （重要）
注意：
语句中出现了Using Filesort 和 Using Temporary说明没有使用到索引
出现 impossible where说明条件永远不成立
产生的值:
/*
Using filesort (需要优化)
说明mysql会对数据使用一个外部的索引排序,
而不是按照表内的索引顺序进行
Mysql中无法利用索引完成排序操作称为"文件排序"
*/
explain select * from employee where dep_id =1 ORDER BY cus_id
/*
Using temporary (需要优化)
使用了临时表保存中间结果,Mysql在对查询结果排序时, 使用了临时表,
常见于排序orderby 和分组查询group by
*/
explain select name from employee where dep_id in (1,2,3) GROUP BY cus_id
/*
impossible where (需要优化)
where 子句的值总是false 不能用来获取任何元组
*/
explain select name from employee where name='鲁班' and name='zs'
-
use index
表示相应的select中使用了覆盖索引,避免访问了表的数据行, 效率很好 如果同时出现using where 表明索引被用来执行索引键值的查找 如果没有同时出现using where 表明索引 用来读取数据而非执行查找动作 示例 -
using where
表明使用了where过滤 -
using join buffer
使用了连接缓存
