MySQL执行计划于索引使用笔记

MySQL执行计划于索引使用笔记

一、Explain使用与详解：分析sql语句(mysql默认提供)

1.explain用法列句：explain + sql语句
2.案例：

1. Explain有两个变种
   （1）Explain extended ：会在Explain的基础上额外提供一些查询优化的信息
   explain extended select * from ~; show warnings; #显示执行的sql语句结构

（2）Explain

2. select_type列：表示对应行是简单还是复杂的查询（实际上是sql语句的一种类型）
   （1）simple：简单查询，查询中不包含子查询和union。
   例句：explain select * from 表名 where id =2
   （2）primary：复杂查询中最外层的select
   （3）subquery：包含在select中的子查询（不在from字句中）
   （4）derived：包含在from字句中的子查询。MySQL会将结果存放在一个临时表中， 也称为派生表（derived的英文含义）。(from后面的是子查询，也相当于衍生查询)例句：
   mysql> set session optimizer_switch='derived_merge=off'; #关闭MySQL5.7新特性对衍生表的合并优化
mysql> explain select (select 1 from actor where id =1) from (select * from film where id =1)

mysql> set session optimizer_switch='derived_merge=on'

3. ID：显示的ID越大越优先查询，执行时越在前面的越先执行。

4. type列：表示关联类型或者访问类型，即MySQL决定如何查找表中的行，查找数据行记录的大概范围
   （1）执行效率程度：system>const>eq_ref>ref>range>index>ALL，从左到右依次递减，一般需要保证查询达到range，最好是达到ref；
   （2）system：相当于const的一种特例，表里只有一条元组匹配（一条记录）
   （3）const：类似于查询一个常量，效率特别高(mysql能对查询的某部分进行优化并将其转化成一个常量，可以看show warning的结果)，用于primary或unique key 的所有列与常数比较时，表最多有一个匹配行，读取一次，速度较快
   
   （4）NULL：MySQL能够在优化夹断分解查询语句，在执行阶段用不着在访问表或索引。（一般在索引列中选取最小值，可以单独查找索引来完成，不需要在执行时访问表）
   mysql> explain select min(id) from film ;
   （5）eq_ref：使用唯一索引，最多只会返回一条符合条件的记录
   Explain select * from 表名 left join 表名 on 两表的关联字段；（在这个sql语句中中涉及到的两个表的执行效率是一样的，其ID是相等的）
   （6）ALL（全表扫描）：扫描聚簇索引的所有叶子节点（一般不推荐使用，可以通过增加索引进行优化）
   （7）ref：查询的结果可能不是一条，可能会出现多条（相比eq_ref，不使用唯一索引，而是使用普通索引或者唯一性索引的部分前缀，索引要和某个值相比较，可能会找到多个符合条件的行）。
   （8）range(范围查找)：范围扫描通常只出现在in()、between、<、>、>= 等操作中。
   （9）index（全索引扫描）：没有查询条件，但是扫描全索引的时候扫描到了某个二级索引，这种扫描不会从索引的根节点开始查找，只会直接对二级索引的叶子节点遍历和扫描，速度比较慢，一般为使用覆盖索引，二级索引一般较小，通常比ALL快一下。（一般效率不高）

5. possible_keys列：显示查询可能使用的索引（有时possible_keys有列，key显示NULL，表示索引对该查询帮助不打，选择全表查询）

6. key列：显示MySQL实际采用的索引（如果没有使用索引，该列为NULL）

7. key_len列：显示MySQL在索引里使用的字节数，可以计算具体使用了索引的哪些列（索引最大长度是768字节，字符串过长时，MySQL会做处理，提取字符串的前半部分做索引）
   key_len计算规则：
   （1）：字符串
   a、char(n)：n字节长度
   b、varchar(n)：如果是utf-8,则长度是3n+2字节，加的2字节用来存储字符串长度
   （2）：数值类型
   a、tinyint：1字节
   b、smallint：2字节
   c、int：4字节
   d、bigint：8字节
   （3）时间类型
   a、date：3字节
   b、timestamp：4字节
   c、datetime：8字节
   （2）字段允许为NULL时：1字节

8. ref列：显示了在key列记录的索引中，表查找值所用到的列或者常量（索引关联查询的字段）

9. rows：MySQL估计要读取并检测的行数，并不是结果集的行数（一个扫描行的预估值）

10. Extra列：额外的信息（情况较多）
    （1）Using index：使用覆盖索引（一种查询的方式）
    （2）Using where：直接用where查询（一般需要优化）
    （3）Using index condition：查询的列不完全被索引覆盖（结果集不一定很准确）
    （4）Using temporary：需要创建一个临时表（加一个distinct去重就可以达到优化的效果）
    （5）Using filesort：排序

二、从B+ 树底层分析常见索引优化原则

三、mysql索引最佳实践

1. 全值匹配：所有的值都会被匹配到
2. 最左前缀法则
3. 不在索引列上做任何操作（计算、函数、类型转换），会导致索引失效而转向全表扫描（原因：在索引树种会找不到值）
4. 存储引擎不能使用索引中范围条件右边的列(一旦值不是有序的就不会走索引)
5. 尽量使用覆盖索引，减少select*语句（）
6. MySQL在使用不等于（！=或者<>）的时候无法使用索引会导致全表扫描（不同场景结果集不同）
7. is null,is not null 一般情况下也无法使用索引
8. like 以通配符开头（’$abc…’）mysql索引失效会变成全变扫描操作（’**%‘会走索引，相当于等值查询，’%~'可以通过覆盖索引去优化）
9. 字符串不加单引号索引失效
10. 少用or或in，用他查询时，MySQL不一定使用索引，MySQL内部优化器会根据检索比例、表大小等多个因素整体评估是否使用索引
11. 范围查询优化：可以将大范围拆分为多个小范围