Explain详解

Explain extended + show warnings 查看优化器优化了什么

explain partitions 显示查询将访问的分区

id：有几个select就有几个id， id列数值越大执行优先级越高，id相同则从上往下执行，id为NULL最后执行。

select_type：表示对应行是简单还是复杂的查询

1. simple简单查询，不包含子查询和union
2. primary：复杂查询中最外层的select
3. subquery：包含在select中的子查询（不在from子句中）
4. derived：包含在from子句中的子查询。MySQL会将结果存放在一个临时表中，也成为派生表（derived的英文含义）

1. 1. 举例：
   2. set session optimizer_switch='derived_merge=off'; #关闭mysql5.7新特性对衍生表的合并优化
   3. explain select (select 1 from actor where id = 1) from (select * from film where id = 1) der;
   4. 
   5. set session optimizer_switch='derived_merge=on'; #还原默认配置

1. union：在 union 中的第二个和随后的 select

mysql> explain select 1 union all select 1;

table：

表示explain的一行正在访问哪个表

当from子句有子查询是，table列是<derivenN>格式，表示当前查询依赖id=N的查询，于是先执行id=N的查询

当有union时，union result 的table列的值为<union1,2>,1和2表示参与union的select行id

type:

依次从最优到最差分别为：system > const > eq_ref > ref > range > index > ALL

• 一般来说，得保证查询达到range级别，最好达到ref
• system

• • system 是 const的一个特列
  • 表中只有一个元素
  • 仅限于主键索引和唯一索引

• const

• • 数据表中只有一行能匹配到
  • 仅限于主键索引和唯一索引

• eq_ref

• • 连接使用, 最多返回一条符合条件的记录
  • 仅限于主键索引和唯一索引
  • 两个表必须有一个使用主键

• ref

• • 相比 eq_ref，不使用唯一索引，而是使用普通索引或者唯一性索引的部分前缀，索引要和某个值相比较，可能会找到多个符合条件的行。
  • 关联查询, 两个表都使用唯一索引

• range

• • 范围查找
  • in() between < > >=

• index

• • 全索引扫描
  • 为什么比ALL快, index 也是遍历全表, all 也是遍历全表, 单单这样看index是比all慢, 因为index是全索引扫描是有序的, all的磁盘扫描是无序的, 如果按照某个顺序排序, index的要优于all

• all

• • 全磁盘扫描
  • 性能最差

possible keys:

显示可能使用到的索引

如果这里有索引，但是key里显示null，可能时因为表中数据不多，MySQL认为索引对此查询帮助不大，选择了全表查询

该列为null，则没有相关索引

key

• 用到了哪个索引

key_len

• 用到索引的长度, 单位是字节
• 如果是联合索引, 只用到其中一个列, 可以通过key_len长度来判断
• 如果字段允许为 NULL，需要1字节记录是否为 NULL
• 索引最大长度是768字节，当字符串过长时，mysql会做一个类似左前缀索引的处理，将前半部分的字符提取出来做索引
• 字符串

• • char(n)：n字节长度
  • varchar(n)：2字节存储字符串长度，如果是utf-8，则长度 3n+ 2

• 数值类型

• • tinyint：1字节
  • smallint：2字节
  • int：4字节
  • bigint：8字节

• 时间类型

• • date：3字节
  • timestamp：4字节
  • datetime：8字节

ref

• 使用的那个索引的字段名

rows

• 扫描了表里多少行记录

extra

• using index

• • 使用覆盖索引

• using where

• • 使用where 语句

• using index condition

• • 索引下推

• using temporary

• • 生成临时表

• using filesort

• • 将用外部排序而不是索引排序, 也就是排序的那个字段不是索引

• select tables optimized away

• • 使用了聚合函数来访问索引字段
  • 比如 max min

索引最佳实践

1. 全值匹配
2. 最左前缀法则
3. 不能在索引列上做任何操作，会导致索引失效从而转向全表扫描
4. 存储引擎不能使用索引中范围条件右边的列
5. 尽量使用覆盖索引（只访问索引的查询（索引列包含查询列）），减少select*语句
6. MySQL在使用不等于的时候无法使用索引会导致全表扫描
7. is null ,is not null也无法使用索引
8. like 以通配符开头MySQL索引会失效
   1. 解决like %字符串% 索引不被使用的办法
   2. 使用覆盖索引,查询字段必须是建立覆盖索引字段
      1. 如果不能使用覆盖索引则可能需要借助搜索引擎(例：es)
9. 字符串不加单引号索引失效，因为name=1000；他会将name转化