1, 何为 explain 执行计划?
使用 explain 关键字可以模拟优化器执行 SQL 语句, 从而知道 MySQL 是如何使用索引来处理你的 SQL 查询语句以及连接表, 可以分析查询语句或是结构的性能瓶颈, 帮助我们选择更好的索引和写出更优化的查询语句.(说白了, 就是优化 SQL 的工具)
2, 如何使用 explain?
在你的 SQL 查询语句前加上 explain 即可, 如 explain select * from table,MySQL 会在查询上设置一个标记, 执行查询时, 会返回执行计划的信息, 而不是执行这条 SQL(如果 from 中包含子查询, 仍会执行该子查询, 将结果放入临时表).
3, 使用 explain 的例子
需要使用三张表, 分别为 actor 演员表, film 电影表, film_actor 电影 - 演员关联表.
- CREATE TABLE `actor` (
- `id` int(11) NOT NULL COMMENT '主键 id',
- `name` varchar(45) DEFAULT NULL COMMENT '演员名称',
- `update_time` datetime DEFAULT NULL COMMENT '修改时间',
- PRIMARY KEY (`id`)
- ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
- insert into `actor` (`id`, `name`, `update_time`) values('1','a','2020-02-11 22:56:00');
- insert into `actor` (`id`, `name`, `update_time`) values('2','b','2020-02-11 22:56:00');
- insert into `actor` (`id`, `name`, `update_time`) values('3','c','2020-02-11 22:56:00');
- CREATE TABLE `film` (
- `id` int(11) NOT NULL AUTO_INCREMENT COMMENT '主键 id',
- `name` varchar(10) DEFAULT NULL COMMENT '电影名称',
- PRIMARY KEY (`id`),
- KEY `idx_name` (`name`)
- ) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8;
- insert into `film` (`id`, `name`) values('3','film0');
- insert into `film` (`id`, `name`) values('1','film1');
- insert into `film` (`id`, `name`) values('2','film2');
- CREATE TABLE `film_actor` (
- `id` int(11) NOT NULL COMMENT '主键 id',
- `film_id` int(11) NOT NULL COMMENT '电影 id',
- `actor_id` int(11) NOT NULL COMMENT '演员 id',
- `remark` varchar(255) DEFAULT NULL COMMENT '备注',
- 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`, `remark`) values('1','1','1',NULL);
- insert into `film_actor` (`id`, `film_id`, `actor_id`, `remark`) values('2','1','2',NULL);
- insert into `film_actor` (`id`, `film_id`, `actor_id`, `remark`) values('3','2','1',NULL);
执行完以上 SQL 后, 三张表数据对应如下:
下面展示 explain 中每个列的信息:
(1)id 列
id 列的编号是 select 语句的序列号, 有几个 select 就有几个 id, 并且 id 的序号是按 select 出现的顺序而增长的(id 越大, 对应的 select 语句越先执行, 如果 id 相等, 则从上往下执行, id 为 NULL 最后执行).
MySQL 将 select 查询分为简单查询 (SIMPLE) 和复杂查询(PRIMARY).
复杂查询分为三类: 简单子查询, 派生表(from 语句中的子查询),union 查询.
1)简单子查询
执行 SQL 语句: EXPLAIN SELECT (SELECT 1 FROM actor LIMIT 1) FROM film
2)from 子句中的子查询
执行 SQL 语句: EXPLAIN SELECT id FROM (SELECT id FROM film) AS der
分析: 这个查询执行时有个临时表别名为 der, 外部 select 查询引用了这个临时表.
3)union 查询
执行 SQL 语句: EXPLAIN SELECT 1 UNION ALL SELECT 1
分析: union 结果总是放在一个匿名临时表中, 临时表不在 SQL 中出现, 因此它的 id 为 NULL.(不推荐使用 union, 性能不高)
(2)select_type 列
这一列表示对应行是简单还是复杂查询, 如果是复杂查询, 又是上述三种复杂查询中的哪一种.
1)SIMPLE: 简单查询. 查询不包含子查询和 union.
执行 SQL 语句: EXPLAIN SELECT * FROM film WHERE id=2
2)PRIMARY: 复杂查询中最外层的 select.
3)SUBQUERY: 包含在 select 中的子查询(不在 from 子句中).
4)DERIVED: 包含在 from 子句中的子查询. MySQL 会将结果存放在一个临时表中, 也称为派生表(DERIVED 的英文含义).
执行 SQL 语句: EXPLAIN SELECT (SELECT 1 FROM actor WHERE id=1) FROM (SELECT * FROM film WHERE id=1) der
5)UNION: 在 union 中的第二个和随后的 select.
6)UNION RESULT: 从 union 临时表检索结果的 select.
执行 SQL 语句: EXPLAIN SELECT 1 UNION ALL SELECT 1
(3)table 列
这一列表示 explain 的一行正在访问哪个表.
当 from 子句中有子查询时, table 列是 < DERIVED N > 格式, 表示当前查询依赖 id=N 的查询, 于是先执行 id=N 的查询.
当有 union 时, UNION RESULT 的 table 列的值为 < union 1,2>,1 和 2 表示参与 union 的 select 行 id.
(4)type 列
(温馨提示: 以下部分理论有可能解释完还是懵逼, 没关系, 继续往下看, 有实践例子)
这一列表示关联类型或访问类型, 即 MySQL 决定如何查找表中的行, 查找数据记录的大概范围.
SQL 语句查询效率从最优到最差依次为: system> const> eq_ref> ref> range> index> ALL.
一般来说, 得保证查询达到 range 级别, 最好达到 ref.
NULL:MySQL 能够在 SQL 语句执行之前 (即优化阶段) 分析分解查询语句, 在执行阶段用不着再访问表或索引. 例如: 在索引列中选取最小值, 可以单独查找索引来完成, 不需要在执行时访问表, 出现的频率不高.
const,system:MySQL 能够对查询的某部分进行优化并将其转化成一个常量(可以看 show warnings 的结果). 用于主键索引或唯一索引的所有列与常数比较时, 表最多有一个匹配行, 读取 1 次, 速度比较快. system 是 const 的特例, 表里只有一条记录匹配时为 system.
执行 SQL 语句: EXPLAIN EXTENDED SELECT * FROM (SELECT * FROM film WHERE id=1) tmp
分析: 上面的子查询 SELECT * FROM film WHERE id = 1 语句 where 后面 id 使用的是主键索引查询, 主键是唯一的, 所以查询结果一定是只有一条记录, 对于明确知道结果集只有一条记录的查询, 它的 type 为 const 类型, 性能已经非常高了; 而第一个 select 复杂查询的表只有一条记录, 所以结果也肯定只有一条记录(第二个 select 子查询之前表中可能是多条记录), 这种特例它的 type 为 system 类型, 性能最高.
执行 SQL 语句: EXPLAIN EXTENDED SELECT * FROM (SELECT * FROM film WHERE id=1) tmp; SHOW WARNINGS;
分析: 用 explain extended 查看执行计划会比 explain 多一列 filtered, 该列给出一个百分比的值, 这个值和 rows 列一起使用, 可以估计出那些将要和 explain 中的前一个表进行连接的行的数目, 前一个表就是指 explain 的 id 列的值比当前表的 id 小的表. explain extended 还可以搭配 show warnings 一起使用, 它可以给出一个优化建议, 真正执行时是执行优化建议的那条 SQL, 但是如果是很复杂的 SQL, 它优化出来的结果可能都没你原先的 SQL 性能高.
eq_ref: 主键索引或唯一索引的所有部分被连接使用, 最多只会返回一条符合条件的记录. 这可能是在 const 之外最好的连接类型了, 简单的 select 查询不会出现这种 type.
执行 SQL 语句: EXPLAIN SELECT * FROM film_actor LEFT JOIN film ON film_actor.film_id=film.id
分析: 有两条记录, 说明有 2 次查询, id 相等, 则从上往下执行, 说明第 1 条先执行查询 film_actor 表, 第 2 条左连接查询 film 表. 左连接 film 表并关联 film.id, 由于 film.id 是唯一索引, film 表只能关联一行记录, 所以第 2 条 select 的 type 为 eq_ref.
ref: 相比 eq_ref, 不使用唯一索引, 而是使用普通索引或者唯一索引的前缀部分, 索引要和某个值相比较, 可能会找到多条符合条件的记录.
1 简单 select 查询, name 是普通索引(非唯一索引)
执行 SQL 语句: EXPLAIN SELECT * FROM film WHERE NAME="film1"
2 关联表查询, idx_film_actor_id 是 film_id 和 actor_id 的联合索引, 这里使用了 film_actor 的索引左边前缀部分 film_id.
执行 SQL 语句: EXPLAIN SELECT * FROM film LEFT JOIN film_actor ON film.id=film_actor.film_id
range: 范围扫描通常出现在 in(),between,>,<,>= 等操作中. 使用一个索引来检索给定范围的行.
执行 SQL 语句: EXPLAIN SELECT * FROM actor WHERE id>1
index: 扫描全表索引, 这通常会比 ALL 快一些.(index 是从索引中读取的, 而 ALL 是从硬盘中读取)
执行 SQL 语句: EXPLAIN SELECT * FROM film;(film 表所有字段都加了索引)
ALL: 即全表扫描, 意味着 MySQL 需要从头到尾去查找所需要的行(不走索引). 通常情况下这需要增加索引来优化了.
执行 SQL 语句: EXPLAIN SELECT * FROM actor;(actor 表有一个字段没加索引)
(5)possible_keys 列
这一列显示查询可能使用哪些索引来查找.
explain 时可能出现 possible_key 有列, 而 key 显示 NULL 的情况, 这种情况是因为表中数据不多, MySQL 认为索引对此查询帮助不大, 选择了全表查询.
如果该列是 NULL, 则没有相关的索引. 在这种情况下, 可以通过检查 where 子句是否可以创造一个适当的索引来提高查询性能, 然后用 explain 查看效果.
(6)key 列
这一列显示 MySQL 实际采用哪个索引来优化对该表的访问.
如果没有使用索引, 则该列是 NULL. 如果想强制 MySQL 使用或忽视 possible_keys 列中的索引, 在查询中使用 force index,ignore index.
(7)key_len 列
这一列显示了 MySQL 在索引里使用的字节数, 通过这个值可以算出具体使用了索引中的哪些列.
举例来说, film_actor 表的联合索引 idx_film_actor_id 由 film_id 和 actor_id 两个 int 列组成, 并且每个 int 是 4 字节. 通过下面结果中的 key_len=4 可推断出只使用了第一个列 flim_id 来执行索引查找.
执行 SQL 语句: EXPLAIN SELECT * FROM film_actor WHERE film_id=2
key_len 计算规则如下:
1 字符串
char(n):n 字节长度
varchar(n):2 字节存储字符串长度, 如果是 UTF-8, 则长度为 3n+2
2 数值类型
tinyint:1 字节
smallint:2 字节
int:4 字节
bigint:8 字节
3 时间类型
date:3 字节
timestamp:4 字节
datetime:8 字节
4 如果字段允许为 NULL, 需要 1 字节记录是否为 NULL
(8)ref 列
这一列显示了在 key 列记录的索引中, 表查找值所用到的列或常量, 常见的有: const(常量), 字段名(例: film.id).
(9)rows 列
这一列是 MySQL 估计要读取并检测的行数, 注意这个不是结果集里的行数.
(10)Extra 列
这一列展示的是额外信息. 常见的重要值如下:
Using index: 查询的列被索引覆盖, 并且 where 筛选条件是索引的前导列(类似联合索引的最左前缀原则), 是性能高的表现. 一般是使用了覆盖索引(即索引包含了所有查询的字段). 对于 InnoDB 来说, 如果是普通索引性能会有不少提高.
执行 SQL 语句: EXPLAIN SELECT film_id FROM film_actor WHERE film_id=1
Using where: 查询的列不完全被索引覆盖, where 筛选条件非索引的前导列.(不走索引, 性能较低)
执行 SQL 语句: EXPLAIN SELECT * FROM actor WHERE name='a'
Using where; Using index: 查询的列被索引覆盖, 并且 where 筛选条件是索引列之一但不是索引的前导列, 意味着无法直接通过索引来查找符合条件的数据.
执行 SQL 语句: EXPLAIN SELECT film_id FROM film_actor WHERE actor_id=1
NULL: 查询的列未被索引覆盖, 并且 where 筛选条件是索引的前导列, 意味着用到了索引, 但是部分字段未被索引覆盖, 必须通过 "回表" 来实现, 不是纯粹地用到了索引, 也不是完全没用到索引.
执行 SQL 语句: EXPLAIN SELECT * FROM film_actor WHERE film_id=1
Using index condition:MySQL 5.6 版本开始加入的新特性, 与 Using where 类似, 查询的列不完全被索引覆盖, where 条件中是一个前导列的范围.
执行 SQL 语句: EXPLAIN SELECT * FROM film_actor WHERE film_id>1
Using temporary:MySQL 需要创建一张临时表来处理查询. 出现这种情况一般是要进行优化的, 首先要想到用索引来优化.
1 actor.name 没有索引, 此时创建了一张临时表来 distinct.(distinct: 去除查询结果中的重复记录)
执行 SQL 语句: EXPLAIN SELECT DISTINCT NAME FROM actor
2 film.name 建立了 idx_name 索引, 此时查询时 extra 是 Using index, 没有用临时表.
执行 SQL 语句: EXPLAIN SELECT DISTINCT NAME FROM film
Using filesort:MySQL 会对结果使用一个外部索引排序, 而不是按照索引次序从表里读取行. 此时 MySQL 会根据连接类型浏览所有符合条件的记录, 并保存排序关键字和行指针, 然后排序关键字并按顺序检索行信息. 这种情况下一般也是要考虑使用索引来优化.
1 actor.name 未创建索引, 会浏览 actor 整个表, 保存排序关键字 name 和对应的 id, 然后排序 name 并检索行记录.
执行 SQL 语句: EXPLAIN SELECT * FROM actor ORDER BY name
2 film.name 建立了 idx_name 索引, 此时查询时 extra 是 Using index, 因为索引底层数据结构已经是排好序的.
执行 SQL 语句: EXPLAIN SELECT * FROM film ORDER BY name
4, 索引优化最佳实践
使用了 employees 员工表:
- CREATE TABLE `employees` (
- `id` int(11) NOT NULL AUTO_INCREMENT COMMENT '主键 id',
- `name` varchar(24) NOT NULL COMMENT '员工姓名',
- `age` int(11) NOT NULL DEFAULT '0' COMMENT '员工年龄',
- `position` varchar(20) NOT NULL COMMENT '员工职位',
- `hire_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '入职时间',
- PRIMARY KEY (`id`),
- KEY `idx_name_age_position` (`name`,`age`,`position`)
- ) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8;
- insert into `employees` (`id`, `name`, `age`, `position`, `hire_time`) values('1','LiLei','22','manager','2020-02-13 14:22:55');
- insert into `employees` (`id`, `name`, `age`, `position`, `hire_time`) values('2','HanMeimei','23','dev','2020-02-13 14:22:57');
- insert into `employees` (`id`, `name`, `age`, `position`, `hire_time`) values('3','Lucy','23','dev','2020-02-13 14:22:59');
(1)全值匹配
执行 SQL 语句: EXPLAIN SELECT * FROM employees WHERE name='LiLei'
执行 SQL 语句: EXPLAIN SELECT * FROM employees WHERE name='LiLei' AND age=22
执行 SQL 语句: EXPLAIN SELECT * FROM employees WHERE name='LiLei' AND age=22 AND position='manager'
(2)索引最左前缀原则
如果索引了多列, 要遵循最左前缀原则. 指的是查询从索引的最左前列开始并且不跳过索引中的列.
提问: 为什么联合索引要想命中索引必须采用最左前缀原则?(命中索引: 即是否用到了索引)
以下索引优化规则很多都可以结合下面这张图思考, 联合索引底层的索引数据结构图(B + 树), 索引的排序首先按 10002 排序, 接着是 Staff, 最后才是 1996-08-03, 如果不先拿第一个字段 10002 去比较, 根本没法比较, 导致无法命中索引.
提问: 以下 SQL 命中索引?
- EXPLAIN SELECT * FROM employees WHERE age = 22 AND position = 'manager';
- EXPLAIN SELECT * FROM employees WHERE position = 'manager';
- EXPLAIN SELECT * FROM employees WHERE name = 'LiLei';
- EXPLAIN SELECT * FROM employees WHERE name = 'LiLei' AND position = 'manager';
分析:
1中的 where 条件后面 age=22 不是索引的最左前列, 后面就不用看了, 没有命中索引,2也是如此.
3中的 name 是索引 idx_name_age_position 的最左前列, 命中索引.
4中的 name 命中索引, position 没有命中索引, 因为跳过索引中的 age 列, 中间断了, age 列还是需要全表扫描.
(3)不要在索引列上做任何操作(如计算, 函数, 自动或手动类型转换), 否则会导致索引失效而转向全表扫描
执行 SQL 语句: EXPLAIN SELECT * FROM employees WHERE LEFT(name, 3)='LiLei'
(4)存储引擎不能使用索引中范围条件右边的列
执行 SQL 语句: EXPLAIN SELECT * FROM employees WHERE name='LiLei' AND age>22 AND position='manager'
分析: 长度为 78,name 为 74,age 是 int 类型, 所以为 4, 即只有 name 和 age 命中索引, position 没有命中索引, 因为它属于 age 范围条件右边的索引列.
(5)尽量使用覆盖索引(只访问索引的查询, 索引列包含查询列), 减少 select * 语句
执行 SQL 语句: EXPLAIN SELECT name,age FROM employees WHERE name='LiLei'
执行 SQL 语句: EXPLAIN SELECT * FROM employees WHERE name='LiLei'
(6)MySQL 在使用不等于 (!= 或者 <>) 的时候无法使用索引, 会导致全表扫描
执行 SQL 语句: EXPLAIN SELECT * FROM employees WHERE name != 'LiLei'
(7)is null,is not null 也无法使用索引
执行 SQL 语句:
EXPLAIN SELECT * FROM employees WHERE name IS NULL
(8)like 以通配符开头('$abc'),MySQL 索引会失效导致全表扫描
执行 SQL 语句: EXPLAIN SELECT * FROM employees WHERE name LIKE '%Lei'
执行 SQL 语句: EXPLAIN SELECT * FROM employees WHERE name LIKE 'Lei%'
提问: 如何解决 like '% 字符串 %' 索引没有命中?
1 使用覆盖索引, 查询字段必须是建立覆盖索引字段
执行 SQL 语句: EXPLAIN SELECT name,age,position FROM employees WHERE name LIKE '%Lei%'
2 当覆盖索引指向的字段是 varchar(380)及以上的字段时, 覆盖索引会失效!
(9)字符串不加单引号, 索引失效(内部会做一个字符串转换函数)
执行 SQL 语句: EXPLAIN SELECT * FROM employees WHERE name=1000
(10)少用 or 或 in, 用它查询时, 非主键字段的索引会失效, 主键索引有时生效, 有时不生效, 跟数据量有关, 具体还得看 MySQL 的查询优化结果
执行 SQL 语句: EXPLAIN SELECT * FROM employees WHERE name='LiLei' OR name='Hanmeimei'
总结:
like KK% 相当于等于常量,%KK 和 %KK% 相当于范围.
来源: http://www.linuxidc.com/Linux/2020-02/162356.htm