Mysql中Explain用法详解

简介

MySQL中的Explain是一个非常有用的工具，它可以帮助我们定位查询语句的性能问题。使用Explain能够分析查询语句的执行计划，帮助开发者发现潜在的性能瓶颈和优化方案。本文将详细讲解MySQL中Explain的用法。

Explain语法

Explain语法类似于SQL语句，常见的如下所示：

EXPLAIN SELECT * FROM table_name WHERE condition;

Explain语句后面跟着的是需要分析的查询SQL语句。

各字段详解

Explain的输出结果包括很多字段，下面我们逐一讲解每个字段的含义。

字段名称	含义
id	查询序号，每个select都会有一个唯一的序号
select_type	查询的类型
table	表名，根据存储引擎不同可能是实际表名或者别名
partitions	分区信息
type	查询使用的索引类型，范围从好到坏依次是const、eq_ref、ref、fulltext、ref_or_null、index_merge、union、null
possible_keys	当前查询可以使用的索引
key	当前实际使用的索引
key_len	当前使用的索引的长度
ref	当前使用索引的参照列
rows	根据表统计出的应该扫描的行数
filtered	指示此查询条件所选的数据行的百分比
Extra	其他信息，包括使用什么语句建立临时表以及如何连接表

优化案例示例

下面我们将排除一些SQL语句的性能问题。假设我们有一个orders表，如下所示：

CREATE TABLE `orders` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `user_id` int(11) NOT NULL,
  `order_no` varchar(64) NOT NULL,
  `price` decimal(10,2) DEFAULT NULL,
  `created_time` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=10001 DEFAULT CHARSET=utf8;

示例1：简单查询

EXPLAIN SELECT * FROM orders WHERE user_id=100;

下面是该查询语句的执行计划：

+----+-------------+--------+------------+-------+---------------+---------+---------+------+------+----------+-------------+
| id | select_type | table  | partitions | type  | possible_keys | key     | key_len | ref  | rows | filtered | Extra       |
+----+-------------+--------+------------+-------+---------------+---------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | orders | NULL       | const | PRIMARY       | PRIMARY | 4       | NULL |    1 |   100.00 | Using index |
+----+-------------+--------+------------+-------+---------------+---------+---------+------+------+----------+-------------+

从上面结果可以看出：

查询条件中使用了索引字段；
type为const，表示使用的是常量索引；
只扫描了一行数据。

因此，该查询语句并没有性能问题。

示例2：分组查询

EXPLAIN SELECT user_id, COUNT(*) FROM orders GROUP BY user_id;

下面是该查询语句的执行计划：

+----+-------------+--------+------------+-------+---------------+---------+---------+------+------+----------+---------------------------------+
| id | select_type | table  | partitions | type  | possible_keys | key     | key_len | ref  | rows | filtered | Extra                           |
+----+-------------+--------+------------+-------+---------------+---------+---------+------+------+----------+---------------------------------+
|  1 | SIMPLE      | orders | NULL       | index | NULL          | PRIMARY | 4       | NULL |   10 |   100.00 | Using index; Using temporary    |
+----+-------------+--------+------------+-------+---------------+---------+---------+------+------+----------+---------------------------------+

从上面结果可以看出：

使用了索引字段；
type为index；
对于所有行都需要进行扫描，rows为表中数据行数；
使用了临时表。

针对该查询语句，可以优化如下：

EXPLAIN SELECT user_id, COUNT(*) FROM orders GROUP BY user_id WITH ROLLUP;

下面是优化后的执行计划：

+----+-------------+--------+------------+--------+---------------+---------+---------+------+------+----------+--------------------------+
| id | select_type | table  | partitions | type   | possible_keys | key     | key_len | ref  | rows | filtered | Extra                    |
+----+-------------+--------+------------+--------+---------------+---------+---------+------+------+----------+--------------------------+
|  1 | SIMPLE      | orders | NULL       | index  | NULL          | PRIMARY | 4       | NULL |   10 |   100.00 | NULL                     |
|  1 | SIMPLE      | NULL   | NULL       | index  | NULL          | NULL    | 0       | NULL |   11 |   100.00 | Using index; Using where |
+----+-------------+--------+------------+--------+---------------+---------+---------+------+------+----------+--------------------------+

优化后的查询语句使用了WITH ROLLUP子句，可以对查询结果进行汇总，同时避免了使用临时表，性能得到了提升。