MySQL索引. ref_Mysql索引篇（三） 善于explain分析sql语句

explain命令的使用方式：

explain+sql语句

explain返回结果的字段解释:

Id 用于表示查询中执行select子句或操作表的顺序

3种情况：

A. id相同执行顺序由上到下

比如：

看第一列和第三列：Id都是1，所以执行的顺序是先加载t1 , 然后 t3，最后t2

B.Id不同：如果是子查询，id的序号会递增，id值也大优先级越高，越先被执行

比如：

看第一列和第三列：先执行查t3表的子查询，再执行t1子查询，最后查t2查询

C.Id相同不同都存在

比如：

看第一列和第三列：首先会执行id大的sql，然后id相同的则从上到下顺序执行。

derived是衍生表的意思，也就是mysql把select t3.id from t3 where xxx这句的执行结果处理成了一个虚拟的表，这种把一个查询的结果临时存为一个表就是衍生表。

derived2中的2是id，表示这张衍生表是id为2的语句生成的衍生表，也就是表s1。

所以上面的执行顺序是：

Select_type 查询类型

有6类：

Simple 简单查询，即不包含子查询和union的查询

Primary 查询中如果包含子查询或union查询，最外层查询会标记为primary

Subquery 表示select或where中包含子查询

Derived 在from中包含的子查询会被标记为derived。Mysql会递归执行这些子查询，把子查询的结果放在临时表中

Union 若第二个select出现在union之后，则被标记为union；若union包含在from中的子查询，则外层select会标记为derived

Union result 从union表获取结果的select

Table 显示这一行的数据是关于哪张表的

Type 访问类型

有8类，性能从好到坏依次是

System > const > eq_ref > ref > range > index >all

要保证查询至少达到range级别，最好能达到ref级别。

System 表只有一条记录(实际中基本不存在这个情况)

Const 当条件查询是对主键或者唯一键进行精确查询(=);例如select * from  t1 where id=1，mysql会将这条记录作为常量。

Eq_ref 唯一性索引扫描。当使用唯一键为条件查询时就是eq_ref。

例如select * from user where userName =‘abc’

Ref 相对应eq_ref，当使用普通索引作为条件进行精确查询时就是ref,普通索引可以有重复的字段值。

例如： select * from employee where name =‘zbp’这条语句不是eq_ref而是ref，因为name不是唯一索引而是个普通索引，名字允许重名。

Eq_ref 只会返回一条记录，而ref可以返回多条记录

Range 对添加了索引的字段进行范围查找

如 in/bettween/>

Index index表示使用了覆盖索引。Index和all相同点是都会对全部的叶子节点进行遍历。区别是index遍历的是不含行数据的叶子节点(只遍历索引值)，all是遍历含有行数据的叶子节点，也就是说index遍历的次数和all一样，但是每次遍历(读取)的数据量比all小很多。所以index会比All快很多很多很多很多。

例如 select id from t1;

All 全表扫描

例如 select * from t1;

Possible_key 显示可能用到的索引

Key 是实际用到的索引；只要key字段有值，那肯定就用到了索引

如果key这一列为null，则没有使用索引；

Key_len 索引中使用的字节数

通过该字段可以判断我的联合索引中的字段是全部字段都使用到了索引还是只有部分使用了。

Ref 显示哪些列或者常量被作为被检索值去进行查找

例如：

只看ref字段这里表示，t2的col1以及常量‘ac’被作为要查找的值进行检索。

Rows 大致估算出查询的时候要扫描多少行记录

Extra 重要的额外信息

Using filesort  使用了文件排序

mysql中无法利用索引完成的排序，又叫做“文件排序”。出现这种情况很危险，性能不好。

(PS 文件排序不是在文件中进行排序而是在内存中进行排序，你用手指头想想怎么可能在文件中进行排序嘛)

比如：我建了联合索引 index(col1, col2, col3)

这条语句的where使用到了索引，但是order by排序的时候没有用到索引的排序，所以用了文件排序。意味着mysql要重新对结果集在内存中(sort_buffer 排序缓冲区)排序。

Using temporary: 使用了临时表保存中间结果

当mysql对查询的结果排序或分组时由于内存不足以存放要排序的内容，可能会内部创建临时表。出现了这个比using filesort还惨，因为内部创建临时表意味着会产生额外的IO。

比如：

从Using temporary 和Using filesort这个例子说明，即使where用到了索引，但如果排序没用到索引，性能也不好。

Using index 使用了覆盖索引

出现这个，性能会很好

如果同时出现using where ，表明索引用来执行where条件查找

如果没有出现using where，表明select查的是索引列，但是没有执行where查找。

例如：

这是同时出现 using index 和 using where

只出现了 using index

Using where 使用了条件查询

还有其他的但是工作中很少见。

最重要的还是 using filesort、using index 和 using temporary

Using join buffer 使用连接缓存

当进行3个及以上的表的联查时会出现Using join buffer。出现这个是正常现象，联查的表多了之后mysql肯定要对每一次联立的表做一个缓存再联立下一个表。只要使用3个表以上的多表联查就不可避免出现这个。

跟面试官聊的时候，其实最重要的是 id/type/key/rows/extra 这5个最重要。

=============================================

接下来举几个sql优化案例：

1.有一个文章表，只有id主键，其他字段没有建立索引

现要求查询 category_id 为1 且 comments 大于1 的情况下，按views排序的数据

Select * article where category_id = 1 and comments > 1 order by views

Explain 分析如下：

没有使用索引，而且排序是一个文件排序。

优化1： 创建联合索引 index idx_article_ccv (category_id, comments, views)

此时再查，结果如下：

使用了key字段表示使用了联合索引idx_article_ccv，type表示访问类型是一个range按范围查询。但是还是使用了文件排序(using filesort)

原因是使用了范围条件(comments>1)之后的条件或排序无法使用索引，也就是说 views 字段排序没用到索引。

优化2：删掉刚刚的索引，重新创建联合索引 index idx_article_ccv (category_id, views)

这个索引只包含两个字段，不含comments字段。

Type的类型是ref，使用到了索引，而且没有用到文件排序，而是使用了索引B+树中的排序。

mysql会找到B+树中满足category_id=1的叶子节点(包含其他列数据)，从磁盘读取到内存，然后再在内存中过滤找到comments>1的数据。由于在B+树中已经排好了序所以不会在内存中对views排序。

2.现在有一个博客表(也是一个文章表)blogs，还有一个分类表 category 。我想做一个关联查询，关联字段是category.id = blogs.tid。假设现在category.id是主键，但是blogs.tid没有建立索引。

explain select * from blogs b join category c on b.tid=c.id;

结果如下：

发现blogs表全表扫描(type为All)，没有用到索引(key为null)，扫描条数为185(我的博客表一共只有185条记录)；而category是唯一索引扫描(type为eq_ref)，使用了主键(key为primary)，只扫描了1条(category表共10个分类，有10条记录)

博客表是一个全表扫描，效率很低。

优化1：对blogs的tid建立单列索引后再查

发现现在变成 category没有使用索引，而blogs使用了索引tid，访问类型是ref。扫描的条数变为 10+23 = 33。效率明显提高。

这个例子想说明的是：

A.inner  join联查，mysql默认小表作为驱动表，大表作为被驱动表，让小表驱动大表。

Left  join联查，mysql会让左边的表作为驱动表，右边的表作为被驱动表。Right join同理。

B.多表联查会全表扫描驱动表的(例子中的表category)，所以在使用left join的时候尽量把小表放在左边，让小表成为驱动表，这样会对小表进行全表扫描，好过去对大表全表扫描。

C.多表联查中要保证关联字段建立了索引