Sql语句优化-explanin

Id

相同

1. 表示一趟独立的查询。一个sql 的查询趟数越少越好
2. 一组，从上往下顺序执行

EXPLAIN SELECT * from t1,t2,t3 where t1.id=t2.id and t2.id=t3.id

不同

id值越大，优先级越高，越先执行

explain
select content,(select id from t1 b where b.id=a.id) from t2 a

select_type

SIMPLE

简单的 select 查询,查询中不包含子查询或者UNION

explain select * from t2

PRIMARY

查询中若包含任何复杂的子部分，最外层查询则被标记为Primary 且只有一个

explain
select content,(select id from t1 b where b.id=a.id) from t2 a

SUBQUERY

子查询中的第一个SELECT

DEPENDENT SUBQUERY

子查询中的第一个SELECT,依赖了外面的查询

explain
select content,(select id from t1 b where b.id=a.id) from t2 a

DERIVED

用来表示包含在FROM子句的子查询中的SELECT,MySQL会递归执行并将结果放到一个临时表中。MySQL内部将其称为Derived table(派生表)，因为该刻临时表是从子查询派生出来的

UNCACHEABLE SUBQUERY

子查询，结果无法缓存，必须针对外部查询的每一行重新评估

EXPLAIN SELECT *  FROM t3 WHERE id = (
	SELECT id FROM t2 WHERE t2.id=@@sort_buffer_size
)

UNION

explain  select  * from t1
	union
select  * from t2

UNION包含在FROM子句的子查询中

explain
select *
from t2
where id in (select t1.id
             from t1
             union all
             select t3.id
             from t3)

table

type

优劣排序

system > const > eq_ref > ref > fulltext > ref_or_null > index_merge > unique_subquery > index_subquery > range > index > ALL

system>const>eq_ref>ref>range>index>ALL

一般来说，得保证查询至少达到range级别，最好能达到ref。

system

该表只有一行（相当于系统表），systemz是const类型的特例

const

针对主键或唯一索引的等值查询扫描，最多只返回一行数据。查询速度非常快，因为它仅仅读取一次即可

explain
select (select content from t1 where id = 1)
from t2

eq_ref

当使用了索引的全部组成部分，并且索引是PRIMARY KEY或UNIQUE NOT NULL才会使用该类型，性能仅次于system 及const。

多表关联查询，单行匹配

explain  select * from t1,t2 where t1.id=t2.id

多表关联查询，联合索引，多行匹配

ref

当满足索引的最左前缀规则，或者索引不是主键也不是唯一索引时才会发生。如果使用的索引只会匹配到少量的行，性能也是不错的

最左前缀原则

指的是索引按照最左优先的方式匹配索引。比如创建了一个组合索引(column1,column2,column3),

使用该索引

WHERE column1 =1 
WHERE column1=1 AND column2 =2
WHERE column1=1 AND column2=2 AND column3=3

无法匹配该索引

WHERE column2 
WHERE column1 =1 AND column3=3

根据索引（非主键，非唯一索引），匹配到多行

多表关联查询，单个索引，多行匹配

多表关联查询，联合索引，多行匹配

fulltext

全文索引

ref_or_null

该类型类似于ref，但是MySQL会额外搜索哪些行包含了NULL。这种类型常见于解析子查询

explain  select * from  t1 where content is null  or content='a'

index_merge

此类型表示使用了索引合并优化，表示一个查询里面用到了多个索引

unique_subquery

该类型和eq ref类似，但是使用了IN查询,且子查询是主键或者唯一索引。

index_subquery

和unique_subquery类似，只是子询使用的是非唯一索引

range

只检索给定范围的行,一般就是在你的where语句中出现了between、<、>、in等的查询

这种范围扫描索引扫描比全表扫描要好，因为它只需要开始于索引的某一点，而结束语另一点，不用扫描全部索引。

explain  select * from actor  where actor_id <10

index

全索引扫描，和ALL类似，只不过index是全盘扫描了索引的数据。当查询仅使用索引中的一部分列时，可使用此类型

如果索引是查询的覆盖索引，并且索引查询的数据就可以满足查询中所需的所有数据，则只扫描索引树。此时，explain的Extra列的结果是Using index。index通常比ALL快，因为索引的大小通常小于表数据

explain select actor_id from actor

ALL

将遍历全表以找到匹配的行

explain  select * from t1, t2 where t1.content=t2.content 无索引

possible_keys

显示可能应用在这张表中的索引，一个或多个。

查询涉及到的字段上若存在索引，则该索引将被列出，但不一定被查询实际使用

Key

实际使用的索引。如果为NULL，则没有使用索引

查询中若使用了覆盖索引(索引和查询的字段一致)，则该索引和查询的select字段重叠

条件越多,索引的字段越长

key_len

表示索引中使用的字节数，可通过该列计算查询中使用的索引的长度。 在不损失精确性的情况下，长度越短越好

字段能够帮你检查是否充分的利用上了索引key_len显示的值为索引字段的最大可能长度，并非实际使用长度，

Ref

显示索引的哪一列被使用了，如果可能的话，是一个常数。哪些列或常量被用于查找索引列上的值

Rows

估算会扫描的行数，数值越小越好。

filtered

表示符合查询条件的数据百分比，最大100。用rows×filtered可获得和下一张表连接的行数。例如rows=1000,filtered=50%,则和下一张表连接的行数是500。

在MySQL 5.7之前，想要显示此字段需使用explain extended命令；

MySOL.5.7及更高版本，explain默人就会展示filtered

Extra

Using filesort

使用一个外部的索引排序，而不是按照表内的索引顺序进行读取。

explain select * from salaries order by from_date

查询中排序的字段，排序字段若通过索引去访问将大大提高排序速度

Using temporary

使了用临时表保存中间结果,MySQL在对查询结果排序时使用临时表。常见于排序和分组查询

USING index

表示相应的select操作中使用了覆盖索引(Covering Index)，避免访问了表的数据行，效率不错！

如果要使用覆盖索引，一定要注意select列表中只取出需要的列，不可select *，

因为如果将所有字段一起做索引会导致索引文件过大，查询性能下降。

Using where

表明使用了where过滤

using join buffer

使用了连接缓存

impossible where

where子句的值总是false，不能用来获取任何元组