04-24 Explain

EXPLAIN SELECT * FROM actor

Explain中的列

id列

id列的编号是 select 的序列号，有几个 select 就有几个id，并且id的顺序是按 select 出现的顺序增长的。 id列越大执行优先级越高，id相同则从上往下执行，id为NULL最后执行。

select_type列

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

简单查询，simple

简单查询不包含子查询和union

EXPLAIN SELECT * FROM film WHERE id=2

复杂查询

/* #关闭mysql5.7新特性对衍生表的合并优化*/
set session optimizer_switch='derived_merge=off';

EXPLAIN SELECT (SELECT 1 FROM actor WHERE id = 1) from (SELECT * FROM film WHERE
id = 1) tmp;

• PRIMARY复杂查询最外层的select
• SUBQUERY，子查询。包含在 select 中的子查询（不在 from 子句中）
• 包含在 from 子句中的子查询。MySQL会将结果存放在一个临时表中，也称为派生表

table列

这一列表示 explain 的一行正在访问哪个表
当 from 子句中有子查询时，table列是格式，表示当前查询依赖 id=N 的查询，于是先执行 id=N 的查询.
当有 union 时，UNION RESULT 的 table 列的值为，1和2表示参与 union 的 select 行id

type列

这一列表示关联类型或访问类型，即MySQL决定如何查找表中的行，查找数据行记录的大概范围。 依次从最优到最差分别为：system > const > eq_ref > ref > range > index > ALL 一般来说，得保证查询达到range级别，最好达到ref

NULL

mysql能够在优化阶段分解查询语句，在执行阶段用不着再访问表或索引。例如：在索引列中选取最小值，可 以单独查找索引来完成，不需要在执行时访问表

EXPLAIN SELECT min(id) FROM actor

const, system

用于 primary key 或 unique key 的所有列与常数比较时，所以表最多有一个匹配行，读取1次，速度比较快。system是 const的特例，表里只有一条元组匹配时为system.

EXPLAIN SELECT * FROM (SELECT * FROM film WHERE id=1) tmp

eq_ref

primary key 或 unique key 索引的所有部分被连接使用 ，最多只会返回一条符合条件的记录。

EXPLAIN SELECT * FROM film_actor LEFT JOIN film ON film_actor.film_id = film.id;

ref

不使用唯一索引，而是使用普通索引或者唯一性索引的部分前缀，索引要和某个值相比较，可能会 找到多个符合条件的行.

• 简单 select 查询，name是普通索引

  EXPLAIN SELECT * FROM film WHERE name='a'
  

  

• 关联表查询，idx_film_actor_id是film_id和actor_id的联合索引，这里使用到了film_actor的左边前缀film_id部分

  EXPLAIN SELECT film_id FROM film LEFT JOIN  film_actor ON film.id = film_actor.film_id
  

  

range

范围扫描通常出现在 in(), between ,> ,<, >= 等操作中。使用一个索引来检索给定范围的行。

index

一般是扫描某个二级索引，这种扫描不会从索引树根节点开始快速查找，而是直接 对二级索引的叶子节点遍历和扫描.这种查询一般为使用覆盖索引，二级索引一般比较小，所以这 种通常比ALL快一些

ALL

即全表扫描，扫描你的聚簇索引的所有叶子节点。

possible_keys列

这一列显示查询可能使用哪些索引来查找。但是这个是说有可能有到而已，最后结果不一定用的。

key列

这一列显示mysql实际采用哪个索引来优化对该表的访问

key_len列

计算mysql在索引里使用的字节数，通过这个值可以算出具体使用了索引中的哪些列。

• 字符串
  • char(n)：如果存汉字长度就是 3n 字节
  • varchar(n)：如果存汉字则长度是 3n + 2 字节，
• 数值类型
  • tinyint：1字节
  • smallint：2字节
  • int：4字节
• 时间类型
  • date：3字节
  • timestamp：4字节
  • datetime：8字节
    • 
    • 这里的keylen=33，因为name的类型是varchar(10)，而且允许为null，所以是：3*10+2+1（允许为null）=33

ref列

这一列显示了在key列记录的索引中，表查找值所用到的列或常量

rows列

是mysql估计要读取并检测的行数，这个不是结果集里的行数

索引实践

id int(11),name varchar(24),position varchar(20)
KEY idx_name_age_position (name,age,position) USING BTREE

1.最左前缀法则

如果索引了多列，要遵守最左前缀法则。指的是查询从索引的最左前列开始并且不跳过索引中的列。

 EXPLAIN SELECT * FROM employees WHERE `name` = 'Bill' and age = 31; /*走索引*/
 EXPLAIN SELECT * FROM employees WHERE age = 30 AND position = 'dev'; /*不走索引*/
 EXPLAIN SELECT * FROM employees WHERE position = 'manager';/*不走索引*/

 /*在顺序乱的情况下，但是在覆盖了所有的索引情况下，还是会走索引，mysql的优化*/
 EXPLAIN SELECT * FROM employees WHERE position=1 AND age = 31 AND `name` = 'Bill';

2.不在索引列上做任何操作（计算、函数、（自动or手动）类型转换），会导致索引失效而转向全表扫描

EXPLAIN SELECT * FROM employees WHERE name='LiLei';

EXPLAIN SELECT * FROM employees WHERE LEFT(name,4)='LiLei';

3.存储引擎不能使用索引中范围条件右边的列,范围查询后面的字段索引会失效

这里的keylen是140，是把3个索引都用到了：4+324+2+320+2=140

EXPLAIN SELECT * FROM employees WHERE name= 'LiLei' AND age = 22 AND position ='manage
r';

下面的keyLen是78，是因为age是用了范围，导致后面的position失效，keLen=4+3*24+2=78

EXPLAIN SELECT * FROM employees WHERE name= 'LiLei' AND age > 22 AND position ='manage
r';

4.尽量使用覆盖索引（只访问索引的查询（索引列包含查询列）），减少 select * 语句

5.mysql在使用不等于（！=或者<>），not in ，not exists 的时候无法使用索引会导致全表扫描

EXPLAIN SELECT * FROM employees WHERE name != 'LiLei'

6.is null,is not null 一般情况下也无法使用索引

7.like以通配符开头（’$abc…’）mysql索引失效会变成全表扫描操作

EXPLAIN SELECT * FROM employees WHERE name like '%Li%' ;

解决like’%字符串%’索引不被使用的方法？

使用覆盖索引，查询字段必须是建立覆盖索引字段

EXPLAIN SELECT name,age,position FROM employees WHERE name like '%Li%' ;

8.字符串不加单引号索引失效

9.少用or或in，用它查询时，mysql不一定使用索引

10.like LiLei%相当于=常量，%LiLei和%LiLei% 相当于范围