MySQL 性能优化：深入理解 EXPLAIN 输出

Explan关键字的作用

MySQL中的EXPLAIN关键字可以用来分析查询语句的执行计划。执行计划是MySQL优化器根据查询语句所做的选择和计算出来的一份查询执行计划表，它显示了MySQL在执行查询时所采取的具体步骤，包括表的读取顺序、使用的索引、扫描的行数等信息。通过分析执行计划表，我们可以了解查询语句的执行效率，并进行优化，从而提高MySQL的查询效率，提升系统的性能。

Explan分析

CREATE TABLE `users` (
  `id` bigint(10) NOT NULL AUTO_INCREMENT COMMENT '用户ID',
  `username` varchar(50) NOT NULL COMMENT '用户名',
  `password` varchar(255) NOT NULL COMMENT '密码',
  `email` varchar(100) NOT NULL COMMENT '电子邮件地址',
  `gender` tinyint(4) NOT NULL COMMENT '性别',
  `phone` varchar(20) DEFAULT NULL COMMENT '电话号码',
  `birthday` date DEFAULT NULL COMMENT '生日',
  `address` varchar(255) DEFAULT NULL COMMENT '地址',
  `avatar` varchar(255) DEFAULT NULL COMMENT '头像',
  `status` tinyint(4) NOT NULL COMMENT '状态',
  `created_time` datetime DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  `updated_time` datetime DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
  PRIMARY KEY (`id`),
  KEY `idx_username` (`username`) USING BTREE,
  KEY `idx_password_phone` (`password`,`phone`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=1004191 DEFAULT CHARSET=utf8 COMMENT='用户表';

DELIMITER //
DROP PROCEDURE IF EXISTS insert_random_users;

CREATE PROCEDURE insert_random_users ( IN num INT ) BEGIN
	DECLARE
		i INT DEFAULT 0;
	WHILE
			i < num DO
			INSERT INTO users ( username, PASSWORD, email, gender, phone, birthday, address, avatar, STATUS )
		VALUES
			(
				FLOOR( RAND() * 100000 ),
				FLOOR( RAND() * 100000 ),
				CONCAT( 'user', i, '@example.com' ),
			IF
				( RAND() > 0.5, 0, 1 ),
				CONCAT(
					'+',
				FLOOR( RAND() * 10000000000 )),
				DATE_ADD( '1970-01-01', INTERVAL FLOOR( RAND() * 36525 ) DAY ),
				CONCAT(
					'Address ',
				FLOOR( RAND() * 1000 )),
				CONCAT( '/avatars/user', FLOOR( RAND() * 1000 ), '.png' ),
			IF
			( RAND() > 0.5, 0, 1 ));
	
	SET i = i + 1;
	
END WHILE;

END // 
DELIMITER;
CALL insert_random_users ( 1000000 );

EXPLAIN SELECT * FROM users WHERE id = 1;

1. id列

id的sql语句的编号，id列越大执行优先级越高，id相同则从上往下执行，id为NULL最后执行。

2. select_type
   • SIMPLE：简单查询
   • PRIMARY：复杂查询中最外层的 select
   • SUBQUERY：包含在 select 中的子查询（不在 from 子句中）
   • DERIVED：会将查询结果存放在一个临时表中，也称为派生表

-- 关闭衍生表优化
set session optimizer_switch='derived_merge=off'; 
EXPLAIN select (select 1 from users where id = 1) from (select * from users where id = 1) der;

• UNION：在 union 中的第二个和随后的 select

EXPLAIN select 1 union all select 1 union all select 1;

3. table列

正在访问的表名

• ：表示当前查询依赖 id = N的查询，先执行 id =N 的查询

-- 关闭衍生表优化
set session optimizer_switch='derived_merge=off'; 
EXPLAIN select (select 1 from users where id = 1) from (select * from users where id = 1) der;

• <union1,2>：1和2表示参与union的select行id

EXPLAIN SELECT id FROM users UNION SELECT id FROM users;

4. partitions列

partitions 列会显示查询所访问的数据分区的名称。在使用分区表时，查询语句只需要访问与查询条件匹配的分区，而不是访问整个表。因此，在执行计划中，partitions 列可以帮助我们了解查询语句所涉及到的分区情况，以及查询语句在每个分区上的执行情况。如果查询涉及到多个分区，partitions 列会显示这些分区的名称，并用逗号分隔。如果查询语句涉及到的分区数超过了 MySQL 限制的最大值，partitions 列将显示 ALL。

5. type列

type 列表示 MySQL 在执行查询时所使用的访问类型 依次从最优到最差分别为：system > const > eq_ref > ref > range > index > all

• null：表示查询的表没有使用索引

EXPLAIN SELECT MIN(id) FROM users;

• system：查询仅返回一行结果，此行结果为系统表中的一行（如数据字典表）。
• const：查询通过索引从表中找到了唯一的匹配行，只有在查询中使用主键或唯一索引时才会出现这种类型。

EXPLAIN SELECT * FROM (SELECT * FROM users WHERE id = 1)tmp; 

• eq_ref：查询使用了连接（join）类型的语句，而且查询只返回匹配的唯一一行数据。

EXPLAIN SELECT * FROM users RIGHT JOIN users_two ON users.id = users_two.id;

• ref：查询使用了非唯一索引或者连接查询中的不同部分使用了索引，返回匹配的多行数据。

EXPLAIN SELECT * FROM users WHERE username = '张三';

• range：查询通过索引查找一定范围内的行，范围由使用索引的查询条件确定。

EXPLAIN SELECT * FROM users WHERE id > 100000;

• index：查询需要扫描整个索引，而不是数据表，常见于使用索引覆盖查询（covering index）时。

EXPLAIN SELECT id,`password`,phone FROM users;

• all：查询需要扫描全表进行匹配，通常出现在没有使用索引、使用了太多的 LIKE 语句或者范围太大的查询中。

EXPLAIN SELECT * FROM users;

6. possible_keys列

possible_keys 列显示了 MySQL 查询优化器可能使用的索引列表。如果查询中的某个列包含一个索引，则该索引将出现在 possible_keys 列中。需要注意的是，possible_keys 列只是一个建议，MySQL 查询优化器仍然可以选择不使用这些索引或选择其他索引来执行查询。因此，仅仅拥有一个或多个 possible_keys 列中的索引并不意味着查询一定会使用这些索引。如果查询使用的是联合索引，则 possible_keys 列中将显示该联合索引的所有列的组合，以及它们的顺序。在优化查询性能时，可以使用 possible_keys 列来确定哪些索引可能对查询有帮助。如果查询没有使用 possible_keys 中的任何一个索引，则说明查询可能需要进行优化，比如添加一个合适的索引来提高查询性能。

7. key列

key 列显示了 MySQL 实际使用的索引。如果查询使用了索引，则该索引将出现在 key 列中。如果查询未使用索引，则该列的值为 NULL。

8. key_len列

key_len 列的值是一个整数，表示索引的字节数，通过显示的字节数可以算出具体使用了索引中的那些列 key_len计算规则如下：

• 字符串，char(n)和varchar(n)，5.0.3以后版本中，n均代表字符数，而不是字节数，如果是utf-8，一个数字或字母占1个字节，一个汉字占3个字节
  • char(n)：如果存汉字长度就是 3n 字节
  • varchar(n)：如果存汉字则长度是 3n + 2 字节，加的2字节用来存储字符串长度，因为varchar是变长字符串
• 数值类型
  • tinyint：1字节
  • smallint：2字节
  • int：4字节
  • bigint：8字节
• 时间类型
  • date：3字节
  • timestamp：4字节
  • datetime：8字节
• 如果字段允许为 NULL，需要1字节记录是否为 NULL

对于 MyISAM 存储引擎，索引的最大长度为 1000 字节。 对于 InnoDB 存储引擎，索引的最大长度取决于 innodb_large_prefix 和 innodb_file_format 参数的设置。如果 innodb_large_prefix 参数设置为 ON，则最大长度为 3072 字节，否则为 767 字节。同时，如果innodb_file_format 参数设置为 Barracuda，则最大长度为 3072 字节，否则为 1000 字节。

9. ref列

ref 列将包含查询中使用的常量或列：常见的有 const（常量），字段名。

10. filtered列

该列是一个百分比的值，rows * filtered/100 可以估算出将要和 explain 中前一个表进行连接的行数（前一个表指 explain 中的id值比当前表id值小的表）。

11. Extra列

Extra 列是一列用来显示额外信息的列。Extra 列的值可以是空字符串，也可以是以下几种取值

• Using index：表示使用了覆盖索引（Covering Index），也就是查询结果可以从辅助索引中直接获取，而无需访问主键索引树。

EXPLAIN SELECT `password`,`phone`,`id` FROM users;

• Using where：表示查询时应用了 WHERE 条件过滤，并且查询条件中的列为被索引覆盖。

EXPLAIN SELECT * FROM users WHERE `address` = '123' ;

• Using temporary：表示查询需要使用临时表来保存中间结果。

EXPLAIN SELECT DISTINCT(address) FROM users;

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

EXPLAIN SELECT * FROM users WHERE `password` ='123' AND phone >'123';

• Using filesort：表示查询需要进行文件排序操作，即对查询结果进行排序时需要使用临时文件。

EXPLAIN SELECT * FROM users ORDER BY address;

• Using join buffer：表示查询需要使用 join buffer 来缓存连接过程中的数据，以提高查询效率

EXPLAIN SELECT * FROM users LEFT JOIN users_two ON users.id = users_two.id;

• Select tables optimized away：表示查询中的所有表都可以通过索引直接获取结果，而无需访问表的数据行。

EXPLAIN SELECT MAX(id) FROM users;

总结

上面列举了一些常见的，如果出现上面没有的可以查询官方文档：mysql Explan官方文档 EXPLAIN 是 MySQL 数据库中的一个关键字，用于显示查询语句的执行计划，帮助分析查询性能和优化查询语句。EXPLAIN 输出的结果通常包含以下列：

• id: 每个查询的唯一标识符，可以用来识别和跟踪查询执行过程中的不同步骤。
• select_type: 查询类型，例如 SIMPLE、PRIMARY、SUBQUERY、UNION 等。
• table: 表示该行所关联的表。
• partitions: 表示该查询的分区。
• type: 表示查询时使用的访问方法，例如 ALL、index、range、ref 等。
• possible_keys: 表示 MySQL 可能使用的索引，用逗号分隔。
• key: 表示 MySQL 最终选择使用的索引。
• key_len: 表示索引的长度，以字节为单位。
• ref: 表示索引的引用列，用于与索引列进行比较的列。
• rows: 表示 MySQL 估计需要扫描的行数。
• filtered: 表示从扫描行中筛选出符合条件的行的百分比。
• Extra: 一些额外的信息，例如查询使用的索引类型、查询使用了覆盖索引、查询需要使用临时表等。

通过分析 EXPLAIN 输出的结果，我们可以确定查询语句的性能瓶颈，并通过优化查询语句、索引等来提高查询性能。同时，了解 EXPLAIN 输出的各列含义，可以帮助我们更好地理解查询执行过程和优化查询语句的方式。