Explain实践优化详解

• 1、Explain结果集解析
• • 1.1 id列
  • 1.2 select_type列
  • 1.3 table列
  • 1.4 type列
  • 1.5 possible_keys列
  • 1.6 key列
  • 1.7 key_len列
  • 1.8 ref列
  • 1.9 rows列
  • 1.10 filtered列
  • 1.1 Extra列
• 2、索引实践

Explain是mysql提供的针对查询语句模拟优化的工具，可以针对输出的结果进行有效分析。
mysql8.0Explian官网地址
https://dev.mysql.com/doc/refman/8.0/en/explain-output.html

建几张表备用

CREATE TABLE `actor` (
  `id` int NOT NULL,
  `name` varchar(45) DEFAULT NULL,
  `update_time` datetime DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3;

CREATE TABLE `film` (
  `id` int NOT NULL AUTO_INCREMENT,
  `name` varchar(10) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `idx_name` (`name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3;

CREATE TABLE `film_actor` (
  `id` int NOT NULL,
  `film_id` int NOT NULL,
  `actor_id` int NOT NULL,
  `remark` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `idx_film_actor_id` (`film_id`,`actor_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3;

1、Explain结果集解析

随便执行一行查询语句，发现结果集中出现多列语义。id、select_type、table、type列等等。那其中各列的含义是什么？

1.1 id列

id列的编号是select序列号，id顺序是根据select出现的顺序排序的。

1.2 select_type列

• 1）simple：简单查询。不包含子查询或union情况
• 2）primary：复杂查询中最外层的select

select (select * from actor where id = 1) from (select * from xxx where x) der;

• 3）subquery：子查询
• 4）derived：from语句中的子查询。

1.3 table列

表示当前explain行正在查询的表。

1.4 type列

type列是Explain中最为关键的列。

查询性能排序依次是：
system > const > eq_ref > ref > range > index > ALL

• Null：优化分解查询语句后，不需要访问表或索引，直接能取到
  
• system：经过mysql优化后，表中只有1行数据与之匹配，相当于常量
• const：使用主键或者unique key查询表单时
• eq_ref：使用唯一索引或主键索引进行关联查询
• ref：通过非主键索引或唯一索引的部分前缀进行查询

EXPLAIN SELECT * FROM film WHERE NAME='a';

• range：使用了索引或主键，但指定了索引的查找范围 —该场景最好要分页

EXPLAIN SELECT * FROM actor WHERE id>1;

-----------------------------------------------------------------------------以下为不及格的使用----------------------------------------------------------------------------
index：全表扫描整个二级索引的叶子节点（下述场景中，film表中所有字段均被索引或主键覆盖，此时会出现覆盖索引查询）

EXPLAIN SELECT * FROM film;

All：全表扫描聚集索引的叶子节点（actor表中的所有数据都要查到，包括非索引字段，此时最优的查询方案就是聚簇索引的全表扫描）

EXPLAIN SELECT * FROM actor;

1.5 possible_keys列

1.6 key列

1.7 key_len列

1.8 ref列

1.9 rows列

1.10 filtered列

1.1 Extra列

2、索引实践