执行计划是分析SQL性能的利器，目前互联网最常用的关系型数据库是MySQL，接下来结合自己对MySQL的理解和实践经验谈谈MySQL执行计划的使用。
简介
MySQL提供了explain语法，使用非常简单，例：EXPLAIN SELECT * from user_info WHERE id =2;
输出格式如下：
执行结果
各列的含义：
- id: SELECT 查询的标识符. 每个 SELECT 都会自动分配一个唯一的标识符.
- select_type: SELECT 查询的类型.
- table: 查询的是哪个表
- type: join 类型
- possible_keys: 此次查询中可能选用的索引
- key: 此次查询中确切使用到了索引.
- ref: 哪个字段或常数与 key 一起被使用
- rows: 显示此查询一共扫描了多少行. 这个是一个估计值.
- filtered: 表示此查询条件所过滤的数据的百分比
- extra: 额外的信息
针对几个重要属性，通过几个例子详细说明一下
id:select 的查询序号，包括一组数字，表示查询中执行了子句或操作的顺序。
id的查询结果有3种情况
- id相同，执行顺序自上而下
执行结果
- id不同，如果是子查询，id序号会递增，id值越大，越先被执行
执行结果
- id相同和不同同时存在，id值越大，越先被执行，id相同认为是同一组，自上而下执行。另外衍生=derived，下图中在id=1时，table显示的是derived2，指向id=2的表，即T3表的衍生表。
执行结果
select_type:表示查询的类型，取值如下：
- SIMPLE：表示此查询不包含 UNION 查询或子查询
- PRIMARY：表示此查询是最外层的查询
- UNION：表示此查询是 UNION 的第二或随后的查询
- DEPENDENT UNION：UNION 中的第二个或后面的查询语句, 取决于外面的查询
- UNION RESULT：UNION 的结果
- SUBQUERY：子查询中的第一个 SELECT
- DEPENDENT SUBQUERY：子查询中的第一个 SELECT, 取决于外面的查询. 即子查询依赖于外层查询的结果.
最常见的就是SIMPLE类型了，比如上图的展示。
如果使用了union查询，EXPLAIN (SELECT * FROM user_info WHERE id IN (1, 2, 3)) UNION (SELECT * FROM user_info WHERE id IN (3, 4, 5));结果如下：
执行结果
type：此属性比较重要，它提供了判断查询效率是否高效的依据。
常用取值：
- system ，表中只有一条数据。
- const，针对主键或者唯一索引的等值查询，最多返回一行数据，const查询效率高。
- eq_ref，多表join查询，使用主键或者唯一索引进行关联，通常使用“=”进行关联，查询效率高。explain SELECT * FROM user_info, order_info WHERE user_info.id = order_info.user_id;
执行结果
- ref，多表join查询，使用非唯一索引和非主键索引，或者使用最左前缀规则的查询。EXPLAIN SELECT * FROM user_info,order_info WHERE user_info.id = order_info.user_id AND order_info.user_id = 3;
执行结果
- range，使用范围查询，通过索引字段范围获取表中部分数据。通常使用=,>,
执行结果
- index，全索引扫描，和全表扫描类似，只不过扫描的是索引全部，性能稍好于全表扫描。EXPLAIN SELECT name from user_info
执行结果
- ALL，全表扫描，性能最低的查询方式。EXPLAIN SELECT * from user_info
查询结果
type的性能比较，通常的关系如下：
ALL < index < range < ref < eq_ref < const < system
我们内部要求SQL的查询要在range以上。
possible_keys：MySQL在查询的时候，能够使用到的索引。只是可能使用到的，真正使用的索引在key中体现。
key：MySQL在当前查询中使用的索引。
key_len：表示查询优化器使用了索引的字节数. 这个字段可以评估联合索引是否完全被使用, 或只有最左部分字段被使用到.
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 的, 则没有此属性.
举两个例子：
EXPLAIN SELECT * FROM order_info WHERE user_id < 3 AND product_name = 'p1' AND productor = 'WHH' ;
执行结果
上面的例子是从表 order_info 中查询指定的内容, 而我们从此表的建表语句中可以知道, 表 order_info 有一个联合索引： KEY `user_product_detail_index` (`user_id`, `product_name`, `productor`)
不过此查询语句 WHERE user_id < 3 AND product_name = 'p1' AND productor = 'WHH' 中, 因为先进行 user_id 的范围查询, 而根据 最左前缀匹配 原则, 当遇到范围查询时, 就停止索引的匹配, 因此实际上我们使用到的索引的字段只有 user_id,
因此在 EXPLAIN 中, 显示的 key_len 为 9. 因为 user_id 字段是 BIGINT, 占用 8 字节, 而 NULL 属性占用一个字节, 因此总共是 9 个字节. 若我们将user_id 字段改为 BIGINT(20) NOT NULL DEFAULT '0', 则 key_length 应该是8.
接下来看另一个例子：
EXPLAIN SELECT * FROM order_info WHERE user_id = 1 AND product_name = 'p1'
执行结果
这次的查询中, 我们没有使用到范围查询, key_len 的值为 161. 为什么呢? 因为我们的查询条件 WHERE user_id = 1 AND product_name = 'p1' 中, 仅仅使用到了联合索引中的前两个字段, 因此 keyLen(user_id) + keyLen(product_name) = 9 + 50 * 3 + 2 = 161。
ref：显示索引的查询条件，常量的效果最好。
例1：EXPLAIN SELECT * FROM order_info WHERE user_id = 1 AND product_name = 'p1'
执行结果
ref一列显示两个常量，代表查询条件中两个 常量参数。
例2：explain SELECT * FROM user_info, order_info WHERE user_info.id = order_info.user_id;
执行结果
上图中执行order_info表时，先进行了索引扫描(tpye类型是index)，然后在其结果集中，用order_info.user_id(ref 列中的值)去user_info表中做匹配。
rows：是一个重要的字段. MySQL 查询优化器根据统计信息, 估算 SQL 要查找到结果集需要扫描读取的数据行数，这个值非常直观显示 SQL 的效率好坏, 原则上 rows 越少越好。
Extra：EXPLAIN中很多额外信息会显示在Extra上，常见有如下几种内容：
- Using filesort
当 Extra 中有 Using filesort 时, 表示 MySQL 需额外的排序操作, 不能通过索引顺序达到排序效果. 一般有 Using filesort, 都建议优化去掉, 因为这样的查询 CPU 资源消耗大。
例如下面的例子：explain SELECT * FROM order_info ORDER BY product_name
执行结果
我们的索引是 KEY `user_product_detail_index` (`user_id`, `product_name`, `productor`)
但是上面的查询中根据 product_name 来排序, 因此不能使用索引进行优化, 进而会产生 Using filesort。
如果我们将排序依据改为 ORDER BY user_id, product_name, 那么就不会出现 Using filesort 了。
例如：explain SELECT * FROM order_info ORDER BY user_id,product_name;
执行结果
- Using index：“索引覆盖”，表示查询在索引中可以满足，不需要回表。
- Using temporary：查询有使用临时表，一般出现于排序, 分组和多表 join 的情况, 查询效率不高, 建议优化。
以上就是我对MySQL执行计划的理解和使用经验，希望给各位带来一些帮助。
附录：演示脚本
CREATE TABLE `user_info` (
`id` BIGINT(20) NOT NULL AUTO_INCREMENT,
`name` VARCHAR(50) NOT NULL DEFAULT '',
`age` INT(11) DEFAULT NULL, PRIMARY KEY (`id`),
KEY `name_index` (`name`)
)
ENGINE = InnoDB
DEFAULT CHARSET = utf8;
INSERT INTO user_info (name, age) VALUES ('xys', 20);
INSERT INTO user_info (name, age) VALUES ('a', 21);
INSERT INTO user_info (name, age) VALUES ('b', 23);
INSERT INTO user_info (name, age) VALUES ('c', 50);
INSERT INTO user_info (name, age) VALUES ('d', 15);
INSERT INTO user_info (name, age) VALUES ('e', 20);
INSERT INTO user_info (name, age) VALUES ('f', 21);
INSERT INTO user_info (name, age) VALUES ('g', 23);
INSERT INTO user_info (name, age) VALUES ('h', 50);
INSERT INTO user_info (name, age) VALUES ('i', 15);
CREATE TABLE `order_info` (
`id` BIGINT(20) NOT NULL AUTO_INCREMENT,
`user_id` BIGINT(20) DEFAULT NULL,
`product_name` VARCHAR(50) NOT NULL DEFAULT '',
`productor` VARCHAR(30) DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `user_product_detail_index` (`user_id`, `product_name`, `productor`)
)
ENGINE = InnoDB
DEFAULT CHARSET = utf8;
INSERT INTO order_info (user_id, product_name, productor) VALUES (1, 'p1', 'WHH');
INSERT INTO order_info (user_id, product_name, productor) VALUES (1, 'p2', 'WL');
INSERT INTO order_info (user_id, product_name, productor) VALUES (1, 'p1', 'DX');
INSERT INTO order_info (user_id, product_name, productor) VALUES (2, 'p1', 'WHH');
INSERT INTO order_info (user_id, product_name, productor) VALUES (2, 'p5', 'WL');
INSERT INTO order_info (user_id, product_name, productor) VALUES (3, 'p3', 'MA');
INSERT INTO order_info (user_id, product_name, productor) VALUES (4, 'p1', 'WHH');
INSERT INTO order_info (user_id, product_name, productor) VALUES (6, 'p1', 'WHH');
INSERT INTO order_info (user_id, product_name, productor) VALUES (9, 'p8', 'TE');