MySQL 进阶讲解（二）：快速生成测试数据以及 EXPLAIN 详解

前言

索引类似大学图书馆建书目索引，可以提高数据检索的效率，降低数据库的 IO 成本。MySQL 在 300w 条记录左右性能开始逐渐下降，虽然官方文档说 500~800w 记录，所以大数据量建立索引是非常有必要的。MySQL 提供了 EXPLAIN，用于显示 SQL 执行的详细信息，可以进行索引的优化。使用 EXPLAIN 关键字可以模拟优化器执行 SQL 查询语句，从而知道 MySQL 是如何处理你的 SQL 语句的，分析你的查询语句或是表结构的性能瓶颈。可以帮助选择更好的索引和写出更优化的查询语句。

本章首先介绍如何通过存储过程随机生成大量随机数据作为 EXPLIAN 的测试数据，然后通过例子详解 EXPLIAN 用法以及各字段含义，最后对 EXPLIAN 用途进行总结。

EXPLAIN 概述

EXPLAIN 命令是查看查询优化器如何决定执行查询的主要方法，使用 EXPLAIN，只需要在查询中的 SELECT 关键字之前增加 EXPLAIN 这个词即可，MYSQL 会在查询上设置一个标记，当执行查询时，这个标记会使其返回关于在执行计划中每一步的信息，而不是执行它，它会返回一行或多行信息，显示出执行计划中的每一部分和执行的次序，从而可以从分析结果中找到查询语句或是表结构的性能瓶颈。

通过 EXPLAIN，我们可以分析出以下结果：

表的读取顺序
数据读取操作的操作类型
哪些索引可以使用
哪些索引被实际使用
表之间的引用
每张表有多少行被优化器查询

随机生成大量测试数据

利用 MySQL 内存表插入速度快的特点，先利用函数和存储过程在内存表中生成数据，然后再从内存表插入普通表中。

（1）登录 MySQL

# 1. 连接到远程主机上的 MySQL
$ mysql -h [host] -u [username] -p [password]

# 2. 查看所有的数据库
mysql> show databases;

# 3. 选择数据库
mysql> use [table_name];

# 4. 查看数据库中的表 
mysql> show tables;

（2）创建内存表

如果一条一条插入普通表的话，效率太低下，但内存表插入速度是很快的，可以先建立一张内存表，插入数据后，在导入到普通表中。

DROP TABLE IF EXISTS `big_data_user_memory`;
CREATE TABLE `big_data_user_memory` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT COMMENT '主键ID',
  `user_id` varchar(20) DEFAULT NULL COMMENT '用户ID',
  `user_name` varchar(20) DEFAULT NULL COMMENT '用户名称',
  `age` tinyint(3) DEFAULT NULL COMMENT '年龄',
  `gender` tinyint(1) DEFAULT NULL COMMENT '性别 [0: 男性; 1: 女性]',
  `phone` varchar(20) DEFAULT NULL COMMENT '手机',
  `group_id` int(11) DEFAULT NULL COMMENT '分组ID',
  `join_time` datetime DEFAULT NULL COMMENT '加入时间',
  `gmt_create` datetime DEFAULT NULL COMMENT '创建时间',
  `gmt_modified` datetime DEFAULT NULL COMMENT '更新时间',
  PRIMARY KEY (`id`),
  KEY `index_user_id` (`user_id`) USING HASH
) ENGINE=MEMORY DEFAULT CHARSET=utf8mb4;

（3）创建普通表

创建普通表，参数设置和内存表相同，否则从内存表往普通标导入数据会报错。

DROP TABLE IF EXISTS `big_data_user`;
CREATE TABLE `big_data_user` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT COMMENT '主键ID',
  `user_id` varchar(20) DEFAULT NULL COMMENT '用户ID',
  `user_name` varchar(20) DEFAULT NULL COMMENT '用户名称',
  `age` tinyint(3) DEFAULT NULL COMMENT '年龄',
  `gender` tinyint(1) DEFAULT NULL COMMENT '性别 [0: 男性; 1: 女性]',
  `phone` varchar(20) DEFAULT NULL COMMENT '手机',
  `group_id` int(11) DEFAULT NULL COMMENT '分组ID',
  `join_time` datetime DEFAULT NULL COMMENT '加入时间',
  `gmt_create` datetime DEFAULT NULL COMMENT '创建时间',
  `gmt_modified` datetime DEFAULT NULL COMMENT '更新时间',
  PRIMARY KEY (`id`),
  KEY `index_user_id` (`user_id`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

（4）创建存储函数

-- 生成随机 UserId
CREATE DEFINER=`root`@`localhost` FUNCTION `generateCode`( n int ) RETURNS varchar(20) CHARSET utf8
    DETERMINISTIC
BEGIN
	DECLARE chars_str VARCHAR ( 100 ) DEFAULT 'abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789';
	DECLARE return_str VARCHAR ( 255 ) DEFAULT '';
	DECLARE i INT DEFAULT 0;
	WHILE
		i < n DO
		SET return_str = concat(return_str, SUBSTRING(chars_str, FLOOR( 1 + RAND() * 62 ), 1 ));
		SET i = i + 1;
	END WHILE;
	RETURN return_str;

END

# 生成随机中文名
CREATE DEFINER=`root`@`localhost` FUNCTION `generateUserName`() RETURNS varchar(255) CHARSET utf8
    DETERMINISTIC
BEGIN
	#Routine body goes here...
	DECLARE last_name varchar(2056) DEFAULT '赵钱孙李周郑王冯陈楮卫蒋沈韩杨朱秦尤许何吕施张孔曹严华金魏陶姜戚谢喻柏水窦章云苏潘葛奚范彭郎鲁韦昌马苗凤花方俞任袁柳酆鲍史唐费廉岑薛雷贺倪汤滕殷罗毕郝邬安常乐于时傅皮齐康伍余元卜顾孟平黄和穆萧尹姚邵湛汪祁毛禹狄米贝明臧计伏成戴谈宋茅庞熊纪舒屈项祝董梁杜阮蓝闽席季麻强贾路娄危江童颜郭梅盛林刁锺徐丘骆高夏蔡田樊胡凌霍虞万支柯昝管卢莫经裘缪干解应宗丁宣贲邓郁单杭洪包诸左石崔吉钮龚程嵇邢滑裴陆荣翁';
	DECLARE first_name varchar(2056) DEFAULT '嘉懿煜城懿轩烨伟苑博伟泽熠彤鸿煊博涛烨霖烨华煜祺智宸正豪昊然明杰诚立轩立辉峻熙弘文熠彤鸿煊烨霖哲瀚鑫鹏致远俊驰雨泽烨磊晟睿天佑文昊修洁黎昕远航旭尧鸿涛伟祺轩越泽浩宇瑾瑜皓轩擎苍擎宇志泽睿渊楷瑞轩弘文哲瀚雨泽鑫磊梦琪忆之桃慕青问兰尔岚元香初夏沛菡傲珊曼文乐菱痴珊恨玉惜文香寒新柔语蓉海安夜蓉涵柏水桃醉蓝春儿语琴从彤傲晴语兰又菱碧彤元霜怜梦紫寒妙彤曼易南莲紫翠雨寒易烟如萱若南寻真晓亦向珊慕灵以蕊寻雁映易雪柳孤岚笑霜海云凝天沛珊寒云冰旋宛儿绿真盼儿晓霜碧凡夏菡曼香若烟半梦雅绿冰蓝灵槐平安书翠翠风香巧代云梦曼幼翠友巧听寒梦柏醉易访旋亦玉凌萱访卉怀亦笑蓝春翠靖柏夜蕾冰夏梦松书雪乐枫念薇靖雁寻春恨山从寒忆香觅波静曼凡旋以亦念露芷蕾千兰新波代真新蕾雁玉冷卉紫山千琴恨天傲芙盼山怀蝶冰兰山柏翠萱乐丹翠柔谷山之瑶冰露尔珍谷雪乐萱涵菡海莲傲蕾青槐冬儿易梦惜雪宛海之柔夏青亦瑶妙菡春竹修杰伟诚建辉晋鹏天磊绍辉泽洋明轩健柏煊昊强伟宸博超君浩子骞明辉鹏涛炎彬鹤轩越彬风华靖琪明诚高格光华国源宇晗昱涵润翰飞翰海昊乾浩博和安弘博鸿朗华奥华灿嘉慕坚秉建明金鑫锦程瑾瑜鹏经赋景同靖琪君昊俊明季同开济凯安康成乐语力勤良哲理群茂彦敏博明达朋义彭泽鹏举濮存溥心璞瑜浦泽奇邃祥荣轩';
	DECLARE return_str varchar(2056) DEFAULT '';
	# 一个中文的长度是 3 位
	DECLARE first_name_length int DEFAULT LENGTH(first_name) / 3;
	DECLARE last_name_length int DEFAULT LENGTH(last_name) / 3;
	
	SET return_str = CONCAT(return_str, SUBSTRING(last_name, FLOOR(1 + RAND() * last_name_length), 1));
	SET return_str = CONCAT(return_str, SUBSTRING(first_name, FLOOR(1 + RAND() * first_name_length), 1));
	
	IF RAND() > 0.400 THEN
			SET return_str = CONCAT(return_str, SUBSTRING(first_name, FLOOR(1 + RAND() * first_name_length), 1));
	END IF;

	RETURN return_str;
END

# 生成随机手机号
CREATE DEFINER=`root`@`localhost` FUNCTION `generatePhone`() RETURNS char(11) CHARSET utf8
    DETERMINISTIC
BEGIN
	#Routine body goes here...
	DECLARE head VARCHAR(256) DEFAULT '133、153、180、181、189、177、173、149、130、131、132、155、156、145、185、186、176、175、134、135、136、137、138、139、150、151、152、157、158、159、182、183、184、187、188、147、178';
	DECLARE content CHAR(10) DEFAULT '0123456789';
	DECLARE phone CHAR(11) DEFAULT substring( head, 1 + ( FLOOR(( RAND() * 37 ))* 4 ), 3 );
	DECLARE i int DEFAULT 1;
  DECLARE len int DEFAULT LENGTH(content);
	
	WHILE i <= 8 DO
		SET i = i + 1;
		SET phone = CONCAT(phone, substring( content, floor( 1 + RAND() * len ), 1 ));
	END WHILE;
	
	RETURN phone;
END

# 生成随机'yyyy-MM-dd'至'yyyy-MM-dd'时间
CREATE DEFINER=`root`@`localhost` FUNCTION `generateDateTime`(begin_time char(10), end_time char(10)) RETURNS datetime
BEGIN
	#Routine body goes here...
	DECLARE date_time VARCHAR ( 255 ) DEFAULT '';
	DECLARE local_date VARCHAR ( 255 ) DEFAULT '';
	DECLARE local_time VARCHAR ( 255 ) DEFAULT '';
	
	SET local_date = DATE(FROM_UNIXTIME( UNIX_TIMESTAMP( begin_time ) + FLOOR( RAND() * ( UNIX_TIMESTAMP( end_time ) - UNIX_TIMESTAMP( begin_time ) + 1 ) )));
	SET local_time = CONCAT(local_time, FLOOR(RAND() * 24), ':', FLOOR(RAND() * 60), ':', FLOOR(RAND() * 60));
	SET date_time = CONCAT(local_date, ' ', local_time);
	
	RETURN date_time;
END

（5）创建存储过程

CREATE DEFINER=`root`@`localhost` PROCEDURE `generateBigDataUser`(IN num INT)
BEGIN
	#Routine body goes here...
	
	#申明变量i,默认为1
	DECLARE i INT DEFAULT 1;
	
	#当i小于传入的参数时执行循环插入
	WHILE  i <= num  DO
		INSERT INTO `big_data_user_memory`(`user_id`, `user_name`, `age`, `gender`, `phone`, `group_id`, `join_time`, `gmt_create`, `gmt_modified`) VALUES (generateCode(20), generateUserName(), 18 + FLOOR(RAND() * 50), FLOOR(RAND() * 2), generatePhone(), FLOOR(RAND() * 100), generateDateTime('1990-01-01', '2019-08-15'), DATE(NOW() - INTERVAL (FLOOR(rand() * 1000 )) DAY), NOW());
		SET i = i + 1;
	END WHILE;

END

（6）调用存储过程

1	CALL generateBigDataUser(1000000);

在调用存储过程的过程中内存表大小的问题抛出 “The table ‘big_data_memory’ is full”，这是就需要我们修改一下 MySQL 的配置信息。

# 1. 查看 tmp_table_size 大小, tmp_table_size: 控制内存临时表的最大值, 超过限值后就往硬盘写, 写的位置由变量 tmpdir 决定 
mysql> SHOW variables like '%tmp_table_size%';

# 2. 查看 max_heap_table_size 大小, max_heap_table_size: 用户可以创建的内存表 (memory table) 的大小. 这个值用来计算内存表的最大行数值
mysql> SHOW VARIABLES LIKE '%max_heap_table_size%';

# 3. 修改 tmp_table_size 大小
mysql> SET SESSION tmp_table_size = 1024 * 1024 * 1024;

# 4. 修改 max_heap_table_size 大小
mysql> SET SESSION max_heap_table_size = 1024 * 1024 * 1024;

（7）将内存表中的数据导入普通表

1	mysql> INSERT INTO big_data_user SELECT * FROM big_data_user_memory;

以上，我们通过存储过程快速产生百万条随机测试数据的工作就大功告成了。接下来，我们将用我们产生的数据为基础详解 EXPLIAN 用法以及各字段含义。

（8）准备关联查询数据

CREATE TABLE `big_data_group` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT COMMENT '主键ID',
  `group_code` varchar(20) DEFAULT NULL COMMENT '分组编码',
  `number_of_people` int(11) DEFAULT NULL COMMENT '人数',
  `gmt_create` datetime DEFAULT NULL COMMENT '创建时间',
  `gmt_modified` datetime DEFAULT NULL COMMENT '更新时间',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE DEFINER=`root`@`localhost` PROCEDURE `generateBigDataGroup`(IN num INT)
BEGIN
	#Routine body goes here...
	DECLARE i INT DEFAULT 1;
	
	#当i小于传入的参数时执行循环插入
	WHILE  i <= num  DO
		INSERT INTO `big_data_group`(`group_code`, `number_of_people`, `gmt_create`, `gmt_modified`) VALUES (generateCode(15), FLOOR(RAND() * 10000), DATE(NOW() - INTERVAL (FLOOR(rand() * 1000 )) DAY), NOW());
		SET i = i + 1;
	END WHILE;

END

1	mysql> CALL generateBigDataGroup(100)

EXPLIAN 用法以及各字段含义

EXPLIAN 模拟优化器执行 SQL 语句，在 5.6 以及以后的版本中，除过 SELECT，其他比如 INSERT，UPDATE 和 DELETE 均可以使用 EXPLIAN 查看执行计划，从而知道 MySQL 是如何处理 SQL 语句，分析查询语句或者表结构的性能瓶颈。

本次 EXPLIAN 以根据手机号码过滤测试数据中手机号码重复的、保留 ID 最小数据的滤重 SQL 语句为例子。

EXPLAIN
DELETE FROM big_data_user WHERE phone IN (
SELECT phone FROM (
SELECT phone FROM big_data_user bdu1 GROUP BY bdu1.phone HAVING count(*)> 1) p) AND id NOT IN (
SELECT id FROM (
SELECT min(bdu2.id) FROM big_data_user bdu2 GROUP BY bdu2.phone HAVING count(*)> 1) b);

20190815153622

EXPLIAN 出来的信息有 12 列，分别是 id、select_type、table、partitions、type、possible_keys、key、key_len、ref、rows、filtered、Extra

id - 查询标识

查询标识，表示 SQL 语句中执行 SELECT 子句或者是操作的顺序。

id 相同时执行顺序从上至下。
id 不同时，如果是子查询，id 的序号会递增，序号越大的越先执行。
id 相同，不同都存在时，id 相同的可以认为是一组查询按从上至下的顺序执行，id 值越大越优先执行。
id 为 NULL，如果行引用其他行的联合结果，则值可以为 NULL。在这种情况下，表列显示像 <unionM,N> 这样的值，以指示该行引用 id 值为 M 和 N 的行的并。

select_type - 查询类型

查询类型，主要是用于区分普通查询、联合查询、子查询等复杂的查询。

SIMPLE：简单的 select 查询，查询中不包含子查询或者 UNION

1	EXPLAIN SELECT * FROM big_data_user WHERE user_id='Jt2BHyxQqsPBoZAO9adp';

PRIMARY：查询中若包含任何复杂的子部分，最外层查询则被标记为 PRIMARY

1	EXPLAIN SELECT *, (SELECT group_code FROM big_data_group WHERE id=group_id) AS group_code FROM big_data_user WHERE user_id='Jt2BHyxQqsPBoZAO9adp';

SUBQUERY：在 SELECT 或 WHERE 列表中包含了子查询

1	EXPLAIN SELECT * FROM big_data_user WHERE group_id = (SELECT id FROM big_data_group WHERE group_code='cqlhc1nBKNAlOTQ');

DEPENDENT SUBQUERY：在 SELECT 或 WHERE 列表中包含了子查询，该子查询依赖外层查询。

1	EXPLAIN SELECT *, (SELECT group_code FROM big_data_group WHERE id=group_id) AS group_code FROM big_data_user WHERE user_id='Jt2BHyxQqsPBoZAO9adp';

DERIVED：在 FROM 列表中包含的子查询被标记为 DERIVED（衍生），MySQL 会递归执行这些子查询，把结果放在临时表中

1	EXPLAIN SELECT * FROM (SELECT * FROM big_data_user LIMIT 5) AS bdu

UNION：若第二个 SELECT 出现在 UNION 之后，则被标记为 UNION；若 UNION 包含在 FROM 子句的子查询中，外层 SELECT 将被标记为DERIVED

1	EXPLAIN SELECT * FROM big_data_user WHERE user_id = 'Jt2BHyxQqsPBoZAO9adp' UNION SELECT * FROM big_data_user WHERE phone = '13982711661';

UNION RESULT：从 UNION 表获取结果的 SELECT

1	EXPLAIN SELECT * FROM big_data_user WHERE user_id = 'Jt2BHyxQqsPBoZAO9adp' UNION SELECT * FROM big_data_user WHERE phone = '13982711661';

table - 查询涉及表

查询涉及表，显示这一行的数据是关于哪张表的。这也可以是下列值之一：

<unionM,N>：输出行引用了 id 值为 M 和 N 的行的 UNION 结果。
< derivedN >：该行引用了一个 id 值为 n 的行的派生表结果。
< subqueryN >：输出行引用了 id 值为 N 的行的物化子查询的结果。

partitions - 匹配到的分区信息

匹配到的分区信息，由查询匹配记录的分区。对于非分区表，值为 NULL。

type - 连接类型

连接类型，对表访问方式，表示 MySQL 在表中找到所需行的方式，又称 “访问类型”。常用的类型有： ALL、index、range、 ref、eq_ref、const、system、NULL（从左到右，性能从差到好）。SQL 性能优化的目标：至少要达到 range 级别，要求是 ref 级别，如果可以是 consts
最好。

system: 表中只有一条数据，这个类型是特殊的 const 类型。
const: 针对主键或唯一索引的等值查询扫描，最多只返回一行数据。 const 查询速度非常快，因为它仅仅读取一次即可。

1	EXPLAIN SELECT * FROM big_data_user WHERE id = 1;

eq_ref: 此类型通常出现在多表的 join 查询，表示对于前表的每一个结果，都只能匹配到后表的一行结果。并且查询的比较操作通常是 =，查询效率较高。

1	EXPLAIN SELECT * FROM big_data_user bdu LEFT JOIN big_data_group bdg ON bdu.group_id = bdg.id;

ref: 此类型通常出现在多表的 join 查询，针对于非唯一或非主键索引，或者是使用了最左前缀规则索引的查询。

1	EXPLAIN SELECT * FROM big_data_user WHERE user_id='Jt2BHyxQqsPBoZAO9adp';

range: 表示使用索引范围查询，通过索引字段范围获取表中部分数据记录。这个类型通常出现在 =, <>, >, >=, <, <=, IS NULL, <=>, BETWEEN, IN() 操作中。

1	EXPLAIN SELECT * FROM big_data_user WHERE id BETWEEN 2 AND 8;

index: 表示全索引扫描 (full index scan)，和 ALL 类型类似，只不过 ALL 类型是全表扫描，而 index 类型则仅仅扫描所有的索引，而不扫描数据。index 类型通常出现在：所要查询的数据直接在索引树中就可以获取到, 而不需要扫描数据。当是这种情况时，Extra 字段会显示 Using index。例如下面这个例子：EXPLAIN SELECT id FROM big_data_user;

1	EXPLAIN SELECT id FROM big_data_user;

ALL: 表示全表扫描，这个类型的查询是性能最差的查询之一。通常来说，我们的查询不应该出现 ALL 类型的查询，因为这样的查询在数据量大的情况下，对数据库的性能是巨大的灾难。如一个查询是 ALL 类型查询，那么一般来说可以对相应的字段添加索引来避免。

1	EXPLAIN SELECT * FROM big_data_user;

possible_keys - 可能选择的索引

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

key - 实际使用的索引

实际使用的索引，实际使用的索引，如果为null，则没有使用索引，因此会出现possible_keys列有可能被用到的索引，但是key列为null，表示实际没用索引。

key_len - 实际使用的索引的长度

实际使用的索引的长度，表示索引中使用的字节数，而通过该列计算查询中使用的索引长度，在不损失精确性的情况下，长度越短越好，key_len显示的值为索引字段的最大可能长度，并非实际使用长度，即key_len是根据表定义计算而得而不是通过表内检索出的。

ref - 和索引进行比较的列

和索引进行比较的列，表示哪些列或常量与键列中命名的索引相比较，以从表中选择行。

1	EXPLAIN SELECT * FROM big_data_user bdu LEFT JOIN big_data_group bdg ON bdu.group_id = bdg.id;

rows - 需要被检索的大致行数

需要被检索的大致行数，根据表统计信息及索引选用情况，大致估算出找到所需的记录所需要读取的行数。

filtered - 按表条件过滤的行百分比

按表条件过滤的行百分比，该列表示将被表条件过滤的表行的估计百分比。最大值为100，这意味着没有发生行过滤。值从100下降表明过滤量增加。

Extra - 额外信息

额外信息，不适合在其他字段中显示，但是十分重要的额外信息。

EXPLAIN
DELETE FROM big_data_user WHERE phone IN (
SELECT phone FROM (
SELECT phone FROM big_data_user bdu1 GROUP BY bdu1.phone HAVING count(*)> 1) p) AND id NOT IN (
SELECT id FROM (
SELECT min(bdu2.id) FROM big_data_user bdu2 GROUP BY bdu2.phone HAVING count(*)> 1) b);

Using filesort : 表示 MySQL 需额外的排序操作，不能通过索引顺序达到排序效果，MySQL Query Optimizer 不得不选择相应的排序算法来实现。一般有 using filesort 都建议优化去掉，因为这样的查询 cpu 资源消耗大。
Using temporary : 使用了临时表保存中间结果，MySQL 在对查询结果排序时使用了临时表。常见于 order by， group by， join 操作，查询效率不高，建议优化。
Using index : 发生了索引覆盖，查询时在索引树上取到了需要查询的数据，不需要再进行回行操作。
Using join buffer : 使用了连接缓存，Block Nested Loop，连接算法是块嵌套循环连接；Batched Key Access，连接算法是批量索引连接。
Using where : 表示 MySQL 服务器从存储引擎收到查询数据，再进行 “后过滤”（Post-filter）。所谓 “后过滤”，就是先读取整行数据，再检查此行是否符合 where 句的条件，符合就留下，不符合便丢弃。因为检查是在读取行后才进行的，所以称为 “后过滤”。注意：Extra 列出现 Using where 表示 MySQL 服务器将存储引擎返回服务层以后再应用 WHERE 条件过滤。
Impossible WHERE : where 子句的值总是 false，不能用来获取任何数据。
distinct : 查找 distinct 值，当 MySQL 找到了第一条匹配的结果时，将停止该值的查询，转为后面其他值查询。

SQL 执行顺序

想要优化 SQL，必须清楚知道 SQL 的执行顺序，这样再配合 explain 才能事半功倍！

完整 SQL 语句：

select distinct 
    <select_list>
from
    <left_table> <join_type>
join <right_table> on <join_condition>
where
    <where_condition>
group by
    <group_by_list>
having
    <having_condition>
order by
    <order_by_condition>
limit <limit number>

SQL 执行顺序：

1、from <left_table><join_type>
2、on <join_condition>
3、<join_type> join <right_table>
4、where <where_condition>
5、group by <group_by_list>
6、having <having_condition>
7、select
8、distinct <select_list>
9、order by <order_by_condition>
10、limit <limit_number>

总结

我们使用 EXPLAIN 解析 SQL 执行计划时，如果有下面几种情况，就需要特别关注下了：

首先看下 type 这列的结果，如果有类型是 ALL 时，表示预计会进行全表扫描（full table scan）。通常全表扫描的代价是比较大的，建议创建适当的索引，通过索引检索避免全表扫描。
再来看下 Extra 列的结果，如果有出现 Using temporary 或者 Using filesort 则要多加关注：Using temporary，表示需要创建临时表以满足需求，通常是因为 GROUP BY 的列没有索引，或者 GROUP BY 和 ORDER BY 的列不一样，也需要创建临时表，建议添加适当的索引；Using filesort，表示无法利用索引完成排序，也有可能是因为多表连接时，排序字段不是驱动表中的字段，因此也没办法利用索引完成排序，建议添加适当的索引；Using where，通常是因为全表扫描或全索引扫描时（type 列显示为 ALL 或 index），又加上了 WHERE 条件，建议添加适当的索引；其他状态例如：Using index、Using index condition、Using index for group-by 则都还好，不用紧张。

参考博文

[1]. MySQL 的索引是什么？怎么优化？
[2]. EXPLAIN Output Format
[3]. MySQL 快速生成 100W 条测试数据
[4]. MySQL EXPLAIN 详解
[5]. MySQL 高级之 explain 执行计划详解

前言