MySQL优化【壹】——EXPLAIN-优快云博客

本文链接：https://blog.youkuaiyun.com/yilian_develope/article/details/52671851

本文详细介绍MySQL中的EXPLAIN命令，解释其各个字段的意义及如何通过EXPLAIN优化SQL查询。包括使用EXPLAIN EXTENDED命令查看SQL改写情况，以及通过具体实例展示不同SQL语句的执行计划差异。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

一、前言

1、数据库版本为MySQL 5.5.24-CDB-2.0.0-log

2、工具为navicat for mysql

二、优化SQL语句的一般步骤

1、show status命令了解各种SQL的执行频率

#查询插入和查询次数等
SHOW GLOBAL STATUS LIKE 'Com_%';

2、通过慢查询或者show processlist定位效率低的sql语句

#log_slow_queries为慢查询开关，long_query_time为慢查询时间限制，单位秒，查询时间超过该限制将记录到慢查询日志中
SHOW VARIABLES LIKE '%quer%';

3、通过EXPLAIN分析低效SQL的执行计划

4、通过show profile 分析SQL（需要MySQL5.0.37以上）

#是否支持show profile和show profiles
SELECT @@have_profiling;
#是否开启profiling,1开启，0关闭
SELECT @@profiling;

5、通过trace分析优化器如何选择执行计划（需要MySQL5.6以上）

6、确定问题并采取相应的优化措施

本文主要介绍其中的EXPLAIN

三、EXPLAIN介绍

在定位到了低效的SQL语句之后，可以通过EXPLAIN获取MySQL是如何执行SELECT语句的信息，包括所用索引，扫描行数等

mysql> EXPLAIN SELECT * FROM `user`;
+----+-------------+-------+------+---------------+------+---------+------+-------+-------+
| id | select_type | table | type | possible_keys | key  | key_len | ref  | rows  | Extra |
+----+-------------+-------+------+---------------+------+---------+------+-------+-------+
|  1 | SIMPLE      | user  | ALL  | NULL          | NULL | NULL    | NULL | 10000 |       |
+----+-------------+-------+------+---------------+------+---------+------+-------+-------+

（1） id

查询的select序列号。只是MySQL的解析顺序，并不一定是执行顺序。但在分析时按id顺序也足够。

（2） select_type

（3） table

显示这一行的数据是关于哪张表的

①<unionM,N>：表示id值为M和N的结果集UNION的结果。

②<derivedN>：表示查询结果来自于id值为N的临时表。

③<subqueryN>：表示查询结果来自于id值为N的子查询。

（4） type

重要的列之一，显示连接类型，从好到坏如下：

①system：表示表中只有一行数据的情况，属于const的特例。

②const：当mysql对某查询某部分进行优化，并转为一个常量时，使用这些访问类型。

③eq_ref：唯一性索引扫描，对于每个索引键，表中只有一条记录与之匹配，常用于主键或者唯一索引的关联查询。

④ref：非唯一性索引扫描，返回匹配某个单独值的所有行，常见于使用非唯一索引或者唯一索引的前缀进行查找

⑤fulltext：关联字段是全文索引。

⑥ref_or_null：同ref类似，还要加上索引为null的情况。

⑦index_merge：多个索引扫描结果合并到一个结果集的时候，例如or的两边条件都是index

⑧unique_subquery：对于某些IN子查询时，取代req_ref的一种类型。其中子查询结果为唯一索引

⑨index_subquery：跟unique_subquery类似，只不过子查询结果为普通索引

⑩range：索引范围扫描，对索引的扫描开始于某一点，返回匹配值的行，常见与between ，< ,>等查询

⑾index：只遍历索引需要匹配行，如查询所有主键id

⑿ALL：遍历全表寻找匹配行

（5） possible_keys

显示可能应用到这次查询的索引。如果为空，没有可能的索引。

（6） key

实际使用的索引。如果为NULL，则没有使用索引。很少的情况下，MYSQL会选择优化不足的索引（即不是最佳的索引选择）。

这种情况下，可以在SELECT语句中使用USE INDEX（indexname）来强制使用一个索引或者用IGNORE INDEX（indexname）来强制MYSQL忽略索引。

（7） key_len

使用的索引的长度。在不损失精确性的情况下，长度越短越好。联合索引的key_len为使用到的索引部分的长度，所以可以根据key_len判断使用了联合索引的哪部分。

测试结果（InnoDB表，utf8编码（每个字符3字节））：

①其中int占1(是否为空)+4位，设定为NOT NULL时，长度-1

②varchar为1(是否为空)+2(存储长度)+字符所占字节数*长度，设定为NOT NULL时，长度-1

③char为1(是否为空)+字符所占字节数*长度，设定为NOT NULL时，长度-1

（8） ref

显示哪一列或者常量被用于索引的比较，如果是func，跟索引比较的是通过函数计算的结果。

（9） rows

表示MySQL认为必须检查的数据行数；对于InnoDB，这个值是个估计值，不一定总是精确的，但在用于分析已经足够。

（10） extra

关于MYSQL如何解析查询的额外信息。主要包括：

①Using index 使用索引

②Using temporary 使用临时表

③Using where 使用到where条件

等等，有很多类型的说明，用于开发者分析Sql语句的效率或者错误。

四、EXPLAIN EXTENDED

在MySQL4.1之后出现了EXPLAIN EXTENDED命令，直接使用EXPLAIN EXTENDED会得到和EXPLAIN相同的数据，只是多个filtered字段。

filtered估计返回结果行占读取行（rows）的百分比。看起来很有用，但它要求where的列上建有索引，并且不走索引的range和ref扫描，而是走全表扫描或者覆盖索引扫描。

在EXPLAIN EXTENDED命令之后，我们可以紧跟SHOW WARNINGS查看SQL在真正被执行前做了哪些SQL改写（或者说优化）

#EXPLAIN EXTENDED实例，所用表见实例的所建表。
EXPLAIN EXTENDED
SELECT * FROM user_info WHERE user_id IN (
	SELECT id FROM `user` WHERE account LIKE 'user00%'
);
SHOW WARNINGS;
#EXPLAIN返回结果
+----+--------------------+-----------+-----------------+-----------------+---------+---------+------+------+----------+-------------+
| id | select_type        | table     | type            | possible_keys   | key     | key_len | ref  | rows | filtered | Extra       |
+----+--------------------+-----------+-----------------+-----------------+---------+---------+------+------+----------+-------------+
|  1 | PRIMARY            | user_info | ALL             | NULL            | NULL    | NULL    | NULL |    1 |      100 | Using where |
|  2 | DEPENDENT SUBQUERY | user      | unique_subquery | PRIMARY,account | PRIMARY | 4       | func |    1 |      100 | Using where |
+----+--------------------+-----------+-----------------+-----------------+---------+---------+------+------+----------+-------------+
#SHOW WARNINGS优化结果
SELECT
	`optimize-test`.`user_info`.`id` AS `id`,
	`optimize-test`.`user_info`.`user_id` AS `user_id`,
	`optimize-test`.`user_info`.`name` AS `name`,
	`optimize-test`.`user_info`.`title` AS `title`
FROM
	`optimize-test`.`user_info`
WHERE
	< in_optimizer >(
		`optimize-test`.`user_info`.`user_id` ,< EXISTS >(
			< primary_index_lookup >(
				< CACHE >(
					`optimize-test`.`user_info`.`user_id`
				)IN USER ON PRIMARY
				WHERE
					(
						(
							`optimize-test`.`user`.`account` LIKE 'user00%'
						)
						AND(
							< CACHE >(
								`optimize-test`.`user_info`.`user_id`
							)= `optimize-test`.`user`.`id`
						)
					)
			)
		)
	)

上述是一个很典型的MySQL自动优化，MySQL会将IN优化为EXISTS，除此之外还包括去掉必定成立的WHERE条件，去除不必要的括号等等。

在SHOW WARNINGS中出现的特殊标记

（1）<cache>(expr)

表达式被执行一次之后，会将结果保存缓存中供以后直接使用。

（2）<exists>(query fragment)

经优化器优化后，子查询语句被转换成EXISTS语句（如上述的EXPLAIN EXTENDED结果）

（3）<in_optimizer>(query fragment)

MySQL优化器自行优化的语句

（4）<index_lookup>(query fragment)

查询语句被处理成通过使用索引查找符合条件的行

（5）<is_not_null_test>(expr)

一个test用于校验表达式不会有NULL的结果

（6）<primary_index_lookup>(query fragment)

查询语句被处理成通过使用主键查找符合条件的行

（7）<ref_null_helper>(expr)
同样也是MySQL优化器自行优化的语句

四、实例

测试表：

#创建用户表
CREATE TABLE `user` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `account` varchar(32) NOT NULL COMMENT '账号',
  `password` varchar(64) DEFAULT NULL COMMENT '密码',
  `user_nick` varchar(32) DEFAULT NULL COMMENT '昵称',
  `desc` varchar(1024) DEFAULT NULL COMMENT '描述',
  `phone` varchar(16) DEFAULT NULL COMMENT '手机号',
  `age` int(11) DEFAULT NULL,
  `is_delete` char(1) NOT NULL DEFAULT '1' COMMENT '删除标识，1未删除，0删除',
  `delete_time` varchar(32) NOT NULL DEFAULT '',
  PRIMARY KEY (`id`),
  UNIQUE KEY `account` (`account`,`is_delete`,`delete_time`)
) ENGINE=InnoDB AUTO_INCREMENT=21000010 DEFAULT CHARSET=utf8
#创建用户信息表
CREATE TABLE `user_info` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `user_id` int(11) DEFAULT NULL COMMENT '用户id',
  `name` varchar(64) DEFAULT NULL,
  `title` char(16) DEFAULT NULL COMMENT '称号',
  PRIMARY KEY (`id`),
  KEY `user_id` (`user_id`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8

实例1

mysql> EXPLAIN SELECT * FROM `user` WHERE account LIKE '%user000%';
+----+-------------+-------+------+---------------+------+---------+------+----------+-------------+
| id | select_type | table | type | possible_keys | key  | key_len | ref  | rows     | Extra       |
+----+-------------+-------+------+---------------+------+---------+------+----------+-------------+
|  1 | SIMPLE      | user  | ALL  | NULL          | NULL | NULL    | NULL | 21001262 | Using where |
+----+-------------+-------+------+---------------+------+---------+------+----------+-------------+

mysql> EXPLAIN SELECT * FROM `user` WHERE account LIKE 'user000%';
+----+-------------+-------+-------+---------------+---------+---------+------+------+-------------+
| id | select_type | table | type  | possible_keys | key     | key_len | ref  | rows | Extra       |
+----+-------------+-------+-------+---------------+---------+---------+------+------+-------------+
|  1 | SIMPLE      | user  | range | account       | account | 98      | NULL |    5 | Using where |
+----+-------------+-------+-------+---------------+---------+---------+------+------+-------------+

从上面两条查询中，我们看到只有一个%的差别，但两者的分析结果就有很大区别：

（1）差距的主要原因还是索引，LIKE查询中只有左匹配才能应用索引，如user%，而%user和%user%都是不能利用索引的

（2）前面的查询由于不能应用索引，所以type为ALL，即要全表扫描；rows值特别大时，也以为着这样的从查询耗时很长（14s左右）。

（3）后面的查询由于应用索引（key为account），而LIKE也是一种范围查找，所以type为range；rows值只有5，表示只用扫描5行记录就能得到结果（实际结果也是5条），所以耗时很短

（4）后面的查询中key_len为98，而联合索引account中，account字段为varchar（32）NOT NULL，key长正好为98（32 * 3 + 2）；说明这条查询只应用了account索引的account部分。

实例2

EXPLAIN SELECT * FROM `user` WHERE account LIKE 'user%' OR id < 10000;
+----+-------------+-------+------+-----------------+------+---------+------+----------+-------------+
| id | select_type | table | type | possible_keys   | key  | key_len | ref  | rows     | Extra       |
+----+-------------+-------+------+-----------------+------+---------+------+----------+-------------+
|  1 | SIMPLE      | user  | ALL  | PRIMARY,account | NULL | NULL    | NULL | 21001262 | Using where |
+----+-------------+-------+------+-----------------+------+---------+------+----------+-------------+

mysql> EXPLAIN SELECT * FROM `user` WHERE account LIKE 'user111%' OR id < 10000;
+----+-------------+-------+-------------+-----------------+-----------------+---------+------+--------+------------------------------------------------+
| id | select_type | table | type        | possible_keys   | key             | key_len | ref  | rows   | Extra                                          |
+----+-------------+-------+-------------+-----------------+-----------------+---------+------+--------+------------------------------------------------+
|  1 | SIMPLE      | user  | index_merge | PRIMARY,account | account,PRIMARY | 98,4    | NULL | 243646 | Using sort_union(account,PRIMARY); Using where |
+----+-------------+-------+-------------+-----------------+-----------------+---------+------+--------+------------------------------------------------+

这个实例主要说明MySQL对最优方案的选择上面（其中account的格式为userX，其中X为唯一的数字）：

（1）前面的查询中，我们发现两个WHERE条件都应用了索引(possible_key中可以看出），但实际情况还是type=ALL，why？因为account的格式都是userX，所以最终的结果其实就是整张表，所以MySQL认为全表扫描更加有利。（实际情况下，只要结果集占比较大，MySQL都会认为整表扫描更加有利）

（2）后面的查询中，type=index_merge，说明实际执行过程是两个WHERE条件都应用索引查询，并进行并集操作（通过Extra的Using sort_union可以看出）

五、链接

1、官网EXPLAIN说明