mysql Explain详解

mysql数据库逻辑分层结构：

InnoDB: 事务优先（适合高并发操作，行锁）
MyISAM: 性能优先 （表锁）

编写过程：
select distinct … from … join … on … where … group by … having … order by … limit …
解析过程：
from … join … on … where … group by … having …select distinct … order by … limit …

索引：index相当于书的目录，是帮组数据库高效获取数据的数据结构。

索引的弊端：

1. 索引本身很大，可以存放在内存、硬盘中；
2. 索引不是所有情况均使用：a、 少量数据；b、频繁更新的字段；c、很少使用的字段
3. 索引会降低增删改的效率

索引的优势：

1. 提高查询效率（降低IO使用率）
2. 降低CPU使用率

索引分类：

1. 单值索引：单列：age ,一个表可以多个单值索引；
2. 唯一索引：列值不能重复，例如ID，可以是null
3. 复合索引：多个列构成的索引
4. 主键索引：不能重复，不能是null

添加索引：
alter table employee add index idx_name_managerId(name, managerId);
alter table employee add unique index idx_name(name);

删除索引：
drop index idx_name on employee;

查看索引：
show index from employee;

SQL性能问题：

• 分析SQL的执行计划： explain, 可以模拟SQL优化器执行SQL语句；
• SQL查询优化会干扰我们的优化；

1. id: 编号
   id字段相同：从上往下顺序执行；
   Id值不同：Id值越大越优先查询（本质：在嵌套子查询时，先查内存，再查外层）；

2. select_type: 查询类型
   PRIMARY: 包含子查询SQL中的主查询（最外层）
   SUBQUERY: 包含子查询SQL中的子查询（非最外层）
   SIMPLE: 简单查询(不包含子查询，union的查询）
   DERIVED: 衍生查询（使用到了临时表）
   a、在from子查询中只有一张表
   b、在from子查询中，如果有table1 union table2 ,则table1就是derived，table2就是union
   UNION: 上例；
   UNION RESULT: 告知开发人员，哪些表之间存在union查询
   

3. table: 表

4. type: 索引类型
   system > const > eq_ref > ref > range > index > all；越往左，性能越高；
   a、system : 只有一条数据的系统表或者衍生表只有一条数据的主查询；
   b、const: 仅仅能查到一条数据的SQL，用于Primary key或unique 索引（类型与索引类型有关）
   
   c、eq_ref: 唯一性索引，对于每个索引键的查询，返回匹配唯一行数据（有且只有1个，不能有多个，也不能有0个）；
   
   d、ref: 非唯一性索引，对于每个索引键的查询，返回匹配的所有行
   
   e、range: 检索指定范围的行，where后面是一个范围查询（between，in，> < >= ）
   
   f、index : 查询全部索引中的数据：tname是索引列，只需扫描索引表，不需要扫描表中的所有数据；
   
   g、all : 查询全部表中的数据: tcdesc不是索引，需要全表扫描，即需要扫描表中的所有数据
   

5. possible_keys: 预测可能用到的索引

6. key: 实际使用的索引

7. key_len ：实际使用索引的长度

8. ref: 表之间的引用；指明当前表所参照的字段
   select … where a.c = b.x ; (其中b.x可以是常量，const)
   

9. rows : 通过索引优化查询到的数据个数

10. filtered：通过表条件过滤出的行数的百分比估计值。

11. Extra： 额外的信息
    a、using filesort: 性能消耗大，需要额外的一次排序（查询），一般用于order by语句中

    explain select * from courses where class = ‘Math’ order by class; – 不会有 Using filesort
    explain select * from courses where class = ‘Math’ order by age; – 有 Using filesort
    排序：排序的前提是先查询，第二个语句在对age进行排序前由于没有查询过，必须先查询一次，所以性能消耗大，需要额外一次排序。
    
    对于单索引，如果排序和查找是同一个字段，则不会出现using filesort；如果排序和查找不是同一个字段，那么就会出现using filesort。
    复合索引：不能跨列（最左匹配原因）
    建立一个联合索引：
    alter table courses add index idx_class_student_age(class,student,age);

    explain select * from courses where class = ‘Math’ order by student; – 不会有 Using filesort
    explain select * from courses where class = ‘Math’ order by age; – 有 Using filesort
    explain select * from courses where student = ‘B’ order by age; – 有 Using filesort
    where和order by按照联合索引的顺序使用。

    b、using temporary: 性能损耗大，用到了临时表，一般用于group by语句中
    
    explain select class, age from courses where class in (‘Java’,‘Math’) and age = 24 group by class, age; – 没有Using temporary

    explain select student from courses where class in (‘Java’,‘Math’) and age = 24 group by student; – 有Using temporary

    如何避免：查询哪些列，就根据哪些列进行group by。

    c、using index: 性能提升，索引覆盖

    原因：不读取原文件，只从索引文件中获取数据，只要查询的列全部都在索引中，就是索引覆盖using index。
    alter table courses add index idx_class_student_age(class,student,age);
    下面3个查询语句都用到了索引：
    explain select class,age from courses ;
    explain select student ,age from courses ;
    explain select age from courses ;

    如果用到了索引覆盖（using index）时，会对possible_keys和key造成影响；
    1、如果没有where，则索引只出现在key中；
    2、如果有where，则索引出现在key和possible_keys中。

    c、using where: 需要回表查询
    假设age是索引列，但查询语句select age,name from … where age = …;

    explain select class, age from courses where age = 24; – age需要会原表查询

    d、Impossible WHERE: where 字段永远为false
    

小结：

1. Using index不读数据文件，只从索引文件获取数据
2. Using where过滤元组和是否读取数据文件或索引文件没有关系;
3. where 和order by 拼起来不要跨列使用

早前的MySQL版本in会导致索引失效，之后的版本in不会导致索引失效！！！！

参考：MySQL Explain详解