【性能优化篇】可视化手把手教你Mysql优化

1、工具 EXPLAIN 介绍

1.1 简介

EXPLAIN语句提供有关 MySQL 如何执行语句的信息。 EXPLAIN适用于 SELECT, DELETE, INSERT, REPLACE, 和 UPDATE语句。
EXPLAIN为语句中使用的每个表返回一行信息 SELECT。它按照 MySQL 在处理语句时读取它们的顺序列出输出中的表。MySQL 使用嵌套循环连接方法解析所有连接。这意味着 MySQL 从第一个表中读取一行，然后在第二个表、第三个表等中找到匹配的行。当所有的表都被处理完后，MySQL 将选择的列输出，并在表列表中回溯，直到找到一个有更多匹配行的表。从此表中读取下一行，然后继续处理下一个表。
EXPLAIN输出包括分区信息。此外，对于SELECT 语句，EXPLAIN生成可以显示的扩展信息 SHOW WARNINGS。

在较旧的 MySQL 版本中，分区和扩展信息是使用 EXPLAIN PARTITIONS和 生成的EXPLAIN EXTENDED。这些语法仍然被认为是为了向后兼容，但分区和扩展输出现在默认启用，因此和PARTITIONS 关键字EXTENDED是多余的并且已弃用。它们的使用会导致警告；EXPLAIN希望在未来的 MySQL 版本中将 它们从语法中删除。

2 Explain 输出列

创建示例表

DROP TABLE IF EXISTS `actor`;
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;

DROP TABLE IF EXISTS `film`;
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;

DROP TABLE IF EXISTS `film_actor`;
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;

每个输出行都EXPLAIN 提供有关一个表的信息，列名显示在表的第一列中；FORMAT=JSON第二列提供了使用 时输出中显示的等效属性名称 。

explain select *  from logs;

2.1 id（JSON 名称 select_id）

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

2.2 select_type（JSON 名称：无）

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

(1) SIMPLE(简单SELECT,不使用UNION或子查询等)
(2) PRIMARY(查询中若包含任何复杂的子部分,最外层的select被标记为PRIMARY)
(3) UNION(UNION中的第二个或后面的SELECT语句)
(4) DEPENDENT UNION(UNION中的第二个或后面的SELECT语句，取决于外面的查询)
(5) UNION RESULT(UNION的结果)
(6) SUBQUERY(子查询中的第一个SELECT)
(7) DEPENDENT SUBQUERY(子查询中的第一个SELECT，取决于外面的查询)
(8) DERIVED(派生表的SELECT, FROM子句的子查询)
(9) UNCACHEABLE SUBQUERY(一个子查询的结果不能被缓存，必须重新评估外链接的第一行)

案列：

SET SESSION optimizer_switch = 'derived_merge=off'; #关闭mysql5.7新特性对衍生表的合 并优化
EXPLAIN SELECT
	( SELECT 1 FROM actor WHERE id = 1 ) 
FROM
	( SELECT * FROM film WHERE id = 1 ) act;

如图所示：箭头ID 的值即为 explain 分析的 Id 值

2.3 table列

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

2.4 type列

type输出列 描述EXPLAIN了表是如何连接的。在 JSON 格式的输出中，这些作为access_type属性的值被发现。下面的列表描述了连接类型，从最好的类型到最差的排序：

NULL > system > const > eq_ref > ref > ref_or_null > index_merge > range > index > ALL

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

explain select min(id) from film;

2.4.2 system 表只有一行记录（等于系统表），这是const类型的特列，平时不大会出现，可以忽略。

2.4.3 const 表示通过索引一次就找到了，const用于比较primary key或uique索引，因为只匹配一行数据，所以很快，如主键置于where列表中，MySQL就能将该查询转换为一个常量。

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

2.4.4 eq_ref 用于联表查询的情况，按联表的主键或唯一键联合查询。

多表join时，对于来自前面表的每一行，在当前表中只能找到一行。这可能是除了system和const之外最好的类型。当主键或唯一非NULL索引的所有字段都被用作join联接时会使用此类型。

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

2.4.5 ref 可以用于单表扫描或者连接

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

简单 select 查询，name是普通索引（非唯一索引）

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

关联表查询，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;

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

EXPLAIN SELECT
	* 
FROM
	actor 
WHERE
	id > 1;

2.4.7 index index只遍历索引树，通常比All快。因为，索引文件通常比数据文件小，也就是虽然all和index都是读全表，但index是从索引中读取的，而all是从硬盘读的。

EXPLAIN SELECT
	* 
FROM
	film;

2.4.8 all 即全表扫描，扫描你的聚簇索引的所有叶子节点。通常情况下这需要增加索引来进行优化了。

explain select * from actor;

2.5 possible_keys列

possible_keys 表示 MySQL 在查询时, 能够使用到的索引. 注意, 即使有些索引在 possible_keys 中出现, 但是并不表示此索引会真正地被 MySQL 使用到. MySQL 在查询时具体使用了哪些索引, 由 key 字段决定.

explain 时可能出现 possible_keys 有列，而 key 显示 NULL 的情况，这种情况是因为表中数据不多，mysql认为索引 对此查询帮助不大，选择了全表查询。 如果该列是NULL，则没有相关的索引。在这种情况下，可以通过检查 where 子句看是否可以创造一个适当的索引来提 高查询性能，然后用 explain 查看效果。

2.6 key列

此字段是 MySQL 在当前查询时所真正使用到的索引.

如果没有使用索引，则该列是 NULL。如果想强制mysql使用或忽视possible_keys列中的索引，在查询中使用 force index、ignore index。

2.7 key_len列

表示查询优化器使用了索引的字节数. 这个字段可以评估组合索引是否完全被使用, 或只有最左部分字段被使用到.

举例来说，film_actor的联合索引 idx_film_actor_id 由 film_id 和 actor_id 两个int列组成，并且每个int是4字节。通 过结果中的key_len=4可推断出查询使用了第一个列：film_id列来执行索引查找。

EXPLAIN SELECT
	* 
FROM
	film_actor 
WHERE
	film_id = 2;

key_len 的计算规则如下:

• 字符串
  • char(n): n 字节长度
  • varchar(n): 如果是 utf8 编码, 则是 3 _n + 2字节; 如果是 utf8mb4 编码, 则是 4 _n + 2 字节.
• 数值类型:
  • TINYINT: 1字节
  • SMALLINT: 2字节
  • MEDIUMINT: 3字节
  • INT: 4字节
  • BIGINT: 8字节
• 时间类型
  • DATE: 3字节
  • TIMESTAMP: 4字节
  • DATETIME: 8字节
• 字段属性: NULL 属性 占用一个字节. 如果一个字段是 NOT NULL 的, 则没有此属性.

2.8 ref列

这一列显示了在key列记录的索引中，表查找值所用到的列或常量，常见的有：const（常量），字段名（例：film.id）

2.9 rows列

rows 也是一个重要的字段. MySQL 查询优化器根据统计信息, 估算 SQL 要查找到结果集需要扫描读取的数据行数.
这个值非常直观显示 SQL 的效率好坏, 原则上 rows 越少越好.

2.10 Extra列

1）Using index：使用覆盖索引

覆盖索引定义：mysql执行计划explain结果里的key有使用索引，如果select后面查询的字段都可以从这个索引的树中 获取，这种情况一般可以说是用到了覆盖索引，extra里一般都有using index；覆盖索引一般针对的是辅助索引，整个 查询结果只通过辅助索引就能拿到结果，不需要通过辅助索引树找到主键，再通过主键去主键索引树里获取其它字段值

EXPLAIN SELECT
	film_id 
FROM
	film_actor 
WHERE
	film_id = 1;

2）Using where：使用 where 语句来处理结果，并且查询的列未被索引覆盖

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

3）Using index condition：查询的列不完全被索引覆盖，where条件中是一个前导列的范围；

EXPLAIN SELECT
	* 
FROM
	film_actor 
WHERE
	film_id > 1;

4）Using temporary：mysql需要创建一张临时表来处理查询。出现这种情况一般是要进行优化的，首先是想到用索 引来优化。

explain select distinct name from actor;

explain select distinct name from film;

1. actor.name没有索引，此时创建了张临时表来distinct
2. film.name建立了idx_name索引，此时查询时extra是using index,没有用临时表

5）Using filesort：将用外部排序而不是索引排序，数据较小时从内存排序，否则需要在磁盘完成排序。这种情况下一 般也是要考虑使用索引来优化的

1. actor.name未创建索引，会浏览actor整个表，保存排序关键字name和对应的id，然后排序name并检索行记录

explain select * from actor order by name;

2. film.name建立了idx_name索引,此时查询时extra是using index

explain select * from film order by name;

3、索引优化最佳实践

创建表结构

CREATE TABLE `employees` (
  `id` int NOT NULL AUTO_INCREMENT,
  `name` varchar(24) NOT NULL DEFAULT '' COMMENT '姓名',
  `age` int NOT NULL DEFAULT '0' COMMENT '年龄',
  `position` varchar(20) NOT NULL DEFAULT '' COMMENT '职位',
  `hire_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '入职时间',
  PRIMARY KEY (`id`),
  KEY `idx_name_age_position` (`name`,`age`,`position`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8mb3 COMMENT='员工记录表';

3.1.全值匹配

1）

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

2)

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

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

3.2 最左前缀法则 索引 (idx_name_age_position)

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

只有第一个走索引，其他都不走索引，因为其他都跳过了 name 则其他字段排列无序 无法走索引

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

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

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

EXPLAIN SELECT * FROM employees WHERE left(name,3) = 'LiLei';

3.4 存储引擎不能使用索引中范围条件右边的列

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

3.6 mysql在使用不等于（！=或者<>），not in ，not exists 的时候无法使用索引会导致全表扫描 < 小于、 > 大于、 <=、>= 这些，mysql内部优化器会根据检索比例、表大小等多个因素整体评估是否使用索引

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

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

EXPLAIN SELECT * FROM employees WHERE name like "Lei%";

EXPLAIN SELECT * FROM employees WHERE name like "%Lei";

3.9 字符串不加单引号索引失效

数据库该字段是什么类型就要传什么类型 否则 就会失效，如果是字符串类型，传了一个int 就会失效

3.10 少用or或in，用它查询时，mysql不一定使用索引，mysql内部优化器会根据检索比例、表大小等多个因素整体评 估是否使用索引，详见范围查询优化

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

4. 参考文档

• MySQL explain 应用详解链接
• MySQL 性能优化神器 Explain 使用分析
• Mysql 官方文档