Mysql优化基础之Explain工具

字段解释

---

• id：代表sql中查询语句的序列号，序列号越大则执行的优先级越高，序号一样谁在前谁先执行。id为null则最后执行
• select_type：查询类型，表示当前被分析的sql语句的查询的复杂度。这个字段有多个值。

1. SIMPLE：表示简单查询
2. PRIMARY：表示复杂查询中的最外层的select查询语句
3. SUBQUERY：表是子查询语句 跟在select 关键字后面的select查询语句；
4. derived： 派生查询，跟在一个select查询语句的from关键字后面的select查询语句

• table：表示当前访问的表的名称。
• partitions：返回的是数据分区的信息（不常用）
• type：这个字段决定mysql如何查找表中的数据，查找数据记录的大概范围。
• possible_keys：这个字段显示的是sql在查询时可能使用到的索引，但是不一定真的使用，只是一种可能。

如果这一列有值，但是key列为null，那是因为mysql觉得可能会使用索引，但是又因为表中的数据很少，使用索引反而没有全表扫描效率高，那么mysql就不会使用索引查找

该列如果是NULL，则没有相关的索引，考虑where条件语句的字段加索引

• key：SQL执行中真正用到的索引字段。
• key_len：用到的索引字段的长度，通过这个字段可以显示具体使用到了索引字段中的哪些列（主要针对组合索引）
• ref：表示那些列或常量被用于查找索引列上的值
• rows：表示在查询过程中检索了多少列 但是并不一定就是返回这么多列数据。
• Extra：展示一些额外信息，标识着表的索引使用情况。

详情参考 http://blog.wingflare.com/2019/10/y2x1d5wv3q6prl3m.html

https://blog.csdn.net/it_tifarmer/article/details/111566731

TYPE详解

---

有以下几张表的情况：

-- 演员表
 CREATE TABLE `actor` (
 `id` INT ( 11 ) NOT NULL,
 `name` VARCHAR ( 45 ) DEFAULT NULL,
 `update_time` datetime DEFAULT NULL,
 PRIMARY KEY ( `id` )  
) ENGINE = INNODB DEFAULT CHARSET = utf8;
 
INSERT INTO `actor` (`id`, `name`, `update_time`) VALUES (1,'a','2017‐12‐22 15:27:18'), (2,'b','20 17‐12‐22 15:27:18'), (3,'c','2017‐12‐22 15:27:18');
 
-- 电影表
CREATE TABLE `film` (
 `id` INT ( 11 ) NOT NULL AUTO_INCREMENT,
 `name` VARCHAR ( 10 ) DEFAULT NULL,
 PRIMARY KEY ( `id` ),
 KEY `idx_name` ( `name` )  
) ENGINE = INNODB DEFAULT CHARSET = utf8;
 
INSERT INTO `film` (`id`, `name`) VALUES (3,'film0'),(1,'film1'),(2,'film2');
 
-- 演员和电影中间表
CREATE TABLE `film_actor` (
 `id` INT ( 11 ) NOT NULL,
 `film_id` INT ( 11 ) NOT NULL,
 `actor_id` INT ( 11 ) NOT NULL,
 `remark` VARCHAR ( 255 ) DEFAULT NULL,
 PRIMARY KEY ( `id` ),
 KEY `idx_film_actor_id` ( `film_id`, `actor_id` )  
) ENGINE = INNODB DEFAULT CHARSET = utf8;
 
INSERT INTO `film_actor` (`id`, `film_id`, `actor_id`) VALUES (1,1,1),(2,1,2),(3,2,1);

从好到差的顺序为：system > const > eq_ref > ref > range > index > all; 一般来说我们优化到range就可以了 最好到ref

1. null：type字段的值如果为null，那么表示当前的查询语句不需要访问表，只需要从索引树中就可以获取我们需要的数据

   explain select id from actor where id =1;
   

2. system/const：用户主键索引或者唯一索引查询时，只能匹配1条数据 一般可以对sql查询语句优化成一个常量，那么type一般就是system或者const，system是const的一个特例。

   explain select * from (select * from film where id = 1) tmp;
   

3. eq_ref：在进行连接查询时，例如left join 时，如果是使用主键索引或者唯一索引连接查询 ，结果返回一条数据，则type的值为一般为eq_ref。

4. ref：相比较eq_ref,不使用主键索引或者唯一索引，使用的是普通索引或者唯一索引的部分前缀，索引与一个值进行比较后可能获取到多个符合条件的行，不在是唯一的行了。

   唯一索引的部分前缀：组合索引是唯一索引的一类，这里指组合索引的前缀部分入组合索引a_b_c，那么a和a_b是唯一索引a_b_c的前缀部分

5. range：通常使用范围查找，例如between，in，<,>,>=等使用索引进行范围检索。

    explain select * from film where id >2;
   

6. index：扫描索引树就能获取到的数据，一般是扫描二级索引，并且不会从根节点扫描，一般直接扫描二级索引的叶子节点，速度比较慢。因为二级索引叶子节点不保存表中其他字段数据 只保存主键，所以二级索引还是比较小的，扫描速度相比All还是很快的。这里用到了覆盖索引，什么是覆盖索引：可以直接遍历索引树就能获取数据叫做覆盖索引。这里遍历name索引树就可以获取到主键id的值就是覆盖索引。

   explain select id from film ;
   

7. ALL:这是一种效率最低的type，需要扫描主键索引树的叶子节点，获取数据是表中其他列的数据，即全表扫描（扫描主索引树的叶子节点）。

   explain select remark from film ;
   

key_len计算

---

字符串

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

数值类型

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

时间类型

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

注意：如果字段允许为 NULL，需要多1字节记录是否为 NULL

假如为某个表的一下三个字段建立组合索引（name_age_phone）

name varchar(50) DEFAULT NULL COMMENT '姓名',

age int(11) DEFAULT NULL COMMENT '年龄',

phone varchar(12) DEFAULT NULL,

则完全使用索引的key_len计算为：key_len=name(50*3+2+1=153)+age(4+1)+phone(12*3+2+1=39)