MySql索引之explain介绍

    explain描述MySQL如何执行查询操作、执行顺序，使用到的索引，以及MySQL成功返回结果集需要执行的行数。

    explain 可以帮助我们分析 select 语句,让我们知道查询效率低下的原因,从而改进我们的查询，让查询优化器能够更好的工作。

    explain关键字放在select查询语句的前面。

    语法：explain select  *  from t1;

  

    执行计划包含的信息：

        

    id: 标识符，表示执行顺序
    select_type: 查询类型
    table: 使用的表
    partitions: 使用的哪个分区，表分区的情况下才有值
    type:联接的类型，例如index索引
    possible_keys:可能用到的索引，保存的是索引名称，如果是多个索引的话，用逗号隔开
    key:实际用到的索引，保存的是索引名称，如果是多个索引的话，用逗号隔开
    key_len: 使用到是索引的长度
    ref: 引用索引对应的表中哪些行
    rows:显示mysql认为执行查询时必须要返回的行数
    filtered:通过过滤条件之后对比总数的百分比
    extra: 额外的信息, using file sort(需要优化，文件排序性能差), using where

 

 

 

explain字段详解

select_type: 查询类型

1.  simple 简单的查询

2.  primary 主查询，或者说是最外层查询

3.  subquery 子查询

4.  union union中的第二个或者后面的那一个select

5.  union result:  union之后的结果

6. dependent union : union中第二个或者后面的select,取决我们查询

7. dependent subquery 子查询中的第一个select
8. derived 衍生表

 

Type

表示按照某种类型来查询，例如按照索引类型查找，按照范围查找

1 const

表示 表中最多有一个匹配行

2 eq_ref

对于每个来自于前面的表的记录，从该表中读取唯一一行

 

3 ref

对于每个来自于前面的表的记录，所有匹配的行从这张表中取出

 

4 ref_or_null

类似于ref，但是可以搜索包含null值的行

5. index_merge

出现在使用一张表中的多个索引时，mysql会讲这多个索引合并到一起

6 range

按指定的范围来检索，很常见

例如> < between … and 等等

7 index

从索取树中查找

8 ALL

全表扫描

 

possible_keys  可能用到的索引，没什么用。

Key  实际用到的索引，重要。

key_len 越小越好，当使用联合索引时，其值可以指明真正使用了多少个字段作为索引，不能为null。

Ref 数据表中的哪列或哪个const会被用于与key字段指定的索引做比

Rows 值越小越好，说明检索的数据越少

 

Extra

1 usingwhere 使用了where查询

2 using index 使用了覆盖索引(select的数据列只用从索引中就能够取得,不必读取数据行)

3 usingwhere 和using index结合：表明索引被用来条件查找

4 usingjoin buffer表示使用了连接缓存

例：explain select * from student whereclassid in(select id from classes);

5 usingfilesort 使用了文件内排序，必须要优化，严重影响性能。

6 usingtemporary 使用了中间表或者是临时表，要先把数据放到临时表中，然后从临时表中获取需要的数据，需要优化，特别是数据量大的情况。

 

总结：和索引相关观察key、key_length、Extra，分组排序后字段要加索引。

 

Explain 案例

案例1：

业务需求

         查询类目为1（java）并且浏览次数(browse_times)大于1000，

         购买人数（buy_times）最多的课程 的信息

 

1.      写出符合业务的SQL:

select * from course where category_id=1and browse_times>1000 order by buy_times desc limit 1;

2.初步分析执行计划

2.      根据之前讲到的复合索引的概念， 以及在where条件和order by 排序中，需要建立复合索引

create index idx_cate_browse_buy oncourse(category_id,browse_times,buy_times);

3.      再次执行explain

此时：type=range(范围查找)，使用上了索引idx_cate_browse_buy

rows=2

但是：using filesort 还是存在？

4.      因为 索引字段 browse_times 使用了范围查找，会使索引后面的字段失效

那我们把范围改成=，来查看结果：

此时，using filesort 没有了，同时出现了type=ref,ref=const, 这是好的现象

 

当然，此时问题也出现了？查询的数据不符合需求了

5.      此时，我们需要跳过browse_times索引字段，重新来建立只有category_id,buy_id的复合索引：

drop indexidx_cate_browse_buy on course;

create index idx_cate_buy on course(category_id,buy_id);

此时就是比较适合的优化方法

 

案例2：

业务需求

         查询所有手机颜色，并把与之相同颜色的电脑也查询出来

1.写出sql:

select * from phone p left join computer c on p. color = c. color;

2.首先来分析一把

发现出现了全表扫描

3.分析需要建立索引，那到底是建在左表还是右表

4.首先在左表建立索引：

create index idx_color on phone(color);

结果：

5.在右表再来建立索引

drop index idx_color on phone;

create index idx_color on computer(color);

6,在左表和右表同时建立索引

create index idx_color on phone(color);

 

案例3：

业务需求

         查询手机，电脑，小车，属于同一颜色的记录

1.      写sql:

        select * from phone p inner join computer cp on p.color=cp.color inner join car c on c.color=cp.color;

2.      执行explain结果：

        先删除全部索引

        

3.      在phone中color字段上面建立索引

        

4.      在computer中color字段建立索引：

        

 

5.      在car中建立索引

        create index idx_color on car(color);

        

 

以下情况会索引失效

1.选择列尽量少用*号

 

2.      尽量遵循复合索引的字段顺序(最左前缀)

刚刚我们建了索引 idx(name,age,phone)

2.2  name和age上面加条件

2.3  name和age,phone上面加条件

 

2.4去掉最左边的列name

2.5 where中使用name,phone作为查找条件

3.复合索引字段不要使用> <查找，会是索引后的字段失效

    通过 key_lenth 的长度观察

4. 自动类型转换操作，及函数计算

4.1在索引列上做计算

4.2自动类型转换

 

4.3  函数计算

5.不要使用不等于!=  <> ，会使索引失效

 6.尽量不要出现 is null， is not null, 可能也会导致索引失效

 

7. like中字符串左边值不固定，会使索引失效

 

 

8.where中要尽量少用or