什么时EXPLAIN 关键字
使用 explain
关键字可以模拟优化器执行SQL查询语句,从而知道MySQL 时如何处理你得SQL语句的,分析你得查询语句或者是表的性能瓶颈
怎么使用
Explain + SQL 语句
Explain SELECT * FROM TABLE_A;
作用
查看:
- 表的读取顺序
- 数据读取操作的操作类型
- 哪些索引可以使用
- 那些索引被实际使用
- 表之间的引用
- 每张表有多少行被优化器查询
执行计划包含的信息
** 名词解释**
id: select 查询的序列号,包含一组数字,表示查询中执行select子句或操作表的顺序
3种情况:
- id 相同, 执行顺序由上至下
- id 不同,如果是子查询,id的序号会递增,id 值越大的优先级越高,越先被执行
- id 如果相同,可以认为是一组,从上往下顺序执行。在所有组中,id值越大,优先级越高,越先被执行。
衍生 = DERIVED
derived2 可以认为是 t3 的id 2 执行完之后,衍生出一张虚拟表 derived2。
select_type:
- SIMPLE: 简单的select 查询, 查询种不包括子查询或者 UNION
- PRIMARY: 查询中若包含任何复杂的子部分,最外层查询则被标记为
- SUBQUERY: 在 SELECT 或者 WHERE 列表中 包含了 子查询
- DERIVED : 在 FROM 表中包含的子查询被标记为DERIVED(衍生)
- UNION:若第二个SELECT出现在UNION之后,贼被标记为UNION;若UNION包含在FROM子句的子查询中,外层SELECT将被标记为:DERIVED
- 从UNION表获取结果的SELECT
TYPE
type: 显示的是访问类型,是较为重要的一个指标。 结果值从最好到最坏一次是
system > const > eq_ref > ref > fulltext > ref_or_null > index_mrge > unique_subquery > index_subquery >range > index > all
显示查询使用了何种类型
常见的 八种: 从最好到最差依次是:
system > const > eq_ref > ref > range > index > all
- system : 表只有一行记录(等于系统表),这是 const 类型的特例,平时不会出现,这个也可以忽略不计
- const: 表示通过索引一次就找到了,const 用于比较 primary key 或者 unqiue 索引,因为只匹配一行数据,所以很快。如将主键 置于 where 列表中, MySQL 就能将该查询转换为一个常量
- eq_ref : 唯一性索引扫描,对于每一个索引键,表中只有一条记录与之匹配,常见主键或唯一索引扫描
- ref : 非唯一性索引扫描,返回匹配某个独自值得所有行,本质上也是一种索引访问,他返回所有匹配某个单独值的行,然而它可能会找到多个符合条件的行, 所以他应该属于查找和扫描的混合体
- range : 质检所给定范围, 使用一个索引来选择行, key 列显示了使用哪个索引。一般就是在你的where 语句中出现了 between, < , > , in 等的查询,这种范围扫描索引扫描,比权标扫描要好,因为他只需要开始于索引的某一点,结束与另一点,不需要扫描全部索引
- index: FULL INDEX , index 与 ALL 区别为index 类型只便利所引述,这通常比ALL 快,因为索引文件通常比数据文件小,也就是说 虽然all 和 index 都是读全表,但是index 是从索引中读取的, 而 all 是从硬盘中读取的
- all : Full Table Scan : 将遍历全表以找到匹配的行
**possible_keys 和 key **
EXPLAIN select * from staffs where name='July' or age = 23;
+----+-------------+--------+------------+------+-----------------------+------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+--------+------------+------+-----------------------+------+---------+------+------+----------+-------------+
| 1 | SIMPLE | staffs | NULL | ALL | idx_staffs_nameAgePos | NULL | NULL | NULL | 3 | 55.56 | Using where |
+----+-------------+--------+------------+------+-----------------------+------+---------+------+------+----------+-------------+
预计应该使用到的索引,比如上面的sql分析中。预计应该使用到的possible_keys
索引为 idx_staffs_nameAgePos
, 但是 key
才为真正使用到的索引。 这里的 key为 null. 说明该查询中并没有实际使用到索引。
key_len
key_len 表示索引使用的字节数, 根据这个值可以判断索引的使用情况。
** ref **
显示索引的哪一列被使用了,如果可能的话,是一个常数。
** row **
MYSQL 认为必须检查的用来返回请求数据的行数。
** Extra **
- Using filesort 说明 mysql 会对数据使用一个外部的索引排序,而不是按照表内的索引顺序进行读取, Mysql 中无法利用索引完成排序的操作成为文件排序。
# 该排序直接使用 age 并没有使用 name ,所以产生了文件内排序
EXPLAIN select id,name,age,pos from staffs ORDER BY age \G;
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: staffs
partitions: NULL
type: index
possible_keys: NULL
key: idx_staffs_nameAgePos
key_len: 128
ref: NULL
rows: 3
filtered: 100.00
Extra: Using index; Using filesort
1 row in set, 1 warning (0.00 sec)
ERROR:
No query specified
# Extra 使用了 where 和 index
EXPLAIN select id,name,age,pos from staffs WHERE name='July' ORDER BY age \G;
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: staffs
partitions: NULL
type: ref
possible_keys: idx_staffs_nameAgePos
key: idx_staffs_nameAgePos
key_len: 62
ref: const
rows: 1
filtered: 100.00
Extra: Using where; Using index
1 row in set, 1 warning (0.00 sec)
ERROR:
No query specified
- Using temporary: 使用了临时表保存中间结果,mysql 在查询结果排序的时候使用了临时表。 常见于排序 order by 和 分组查询 group by
# 排序的时候使用了临时表,又使用了文件内排序。
EXPLAIN select id,name,age,pos from staffs GROUP BY age \G;
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: staffs
partitions: NULL
type: index
possible_keys: idx_staffs_nameAgePos
key: idx_staffs_nameAgePos
key_len: 128
ref: NULL
rows: 3
filtered: 100.00
Extra: Using index; Using temporary; Using filesort
1 row in set, 1 warning (0.00 sec)
ERROR:
No query specified
# 要注意索引的使用顺序。这里就是用了 where, 和 index
EXPLAIN select id,name,age,pos from staffs where name = 'gss' GROUP BY age \G;
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: staffs
partitions: NULL
type: ref
possible_keys: idx_staffs_nameAgePos
key: idx_staffs_nameAgePos
key_len: 62
ref: const
rows: 1
filtered: 100.00
Extra: Using where; Using index
1 row in set, 1 warning (0.00 sec)
ERROR:
No query specified
- Using index: 表示相应的 select 操作中使用了覆盖索引,Covering Index 。 避免访问了表的数据行,如果同时出现 using where,表明索引被用来执行索引键值的查找,如果没有同时出现 using where , 表明索引用来读取数据而非执行查找动作。
- Using Where 表明查找使用了 where 过滤
- using join buffer 表示使用了连接缓存
- impossible where where 子句的值总是 false ,不能用来获取任何元素
- select tables optimized away 在设置 group by 子句的情况下,基于 索引 优化 MIN / MAX 操作或者 对于 MyISAM 存储引擎优化(COUNT(*)) , 不必等到执行阶段再进行计算,查询执行计划生成的阶段即完成优化
- distinct , 优化 distinct 操作,再找到第一匹配的元素后即停止找同样值得动作。