MySQL执行计划于索引使用笔记
一、Explain使用与详解:分析sql语句(mysql默认提供)
1.explain用法列句:explain + sql语句
2.案例:
- Explain有两个变种
(1)Explain extended :会在Explain的基础上额外提供一些查询优化的信息
explain extended select * from ~; show warnings; #显示执行的sql语句结构
(2)Explain
-
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'
-
ID:显示的ID越大越优先查询,执行时越在前面的越先执行。
-
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快一下。(一般效率不高) -
possible_keys列:显示查询可能使用的索引(有时possible_keys有列,key显示NULL,表示索引对该查询帮助不打,选择全表查询)
-
key列:显示MySQL实际采用的索引(如果没有使用索引,该列为NULL)
-
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字节 -
ref列:显示了在key列记录的索引中,表查找值所用到的列或者常量(索引关联查询的字段)
-
rows:MySQL估计要读取并检测的行数,并不是结果集的行数(一个扫描行的预估值)
-
Extra列:额外的信息(情况较多)
(1)Using index:使用覆盖索引(一种查询的方式)
(2)Using where:直接用where查询(一般需要优化)
(3)Using index condition:查询的列不完全被索引覆盖(结果集不一定很准确)
(4)Using temporary:需要创建一个临时表(加一个distinct去重就可以达到优化的效果)
(5)Using filesort:排序
二、从B+ 树底层分析常见索引优化原则
三、mysql索引最佳实践
- 全值匹配:所有的值都会被匹配到
- 最左前缀法则
- 不在索引列上做任何操作(计算、函数、类型转换),会导致索引失效而转向全表扫描(原因:在索引树种会找不到值)
- 存储引擎不能使用索引中范围条件右边的列(一旦值不是有序的就不会走索引)
- 尽量使用覆盖索引,减少select*语句()
- MySQL在使用不等于(!=或者<>)的时候无法使用索引会导致全表扫描(不同场景结果集不同)
- is null,is not null 一般情况下也无法使用索引
- like 以通配符开头(’$abc…’)mysql索引失效会变成全变扫描操作(’**%‘会走索引,相当于等值查询,’%~'可以通过覆盖索引去优化)
- 字符串不加单引号索引失效
- 少用or或in,用他查询时,MySQL不一定使用索引,MySQL内部优化器会根据检索比例、表大小等多个因素整体评估是否使用索引
- 范围查询优化:可以将大范围拆分为多个小范围