浅谈mysql explain详解

在我们进行SQL查询优化的过程中，经常会用到mysql的explain命令。该命令是mysql提供给我们查看查询执行计划的工具，可以帮助我们分析查询的执行效率，找出问题所在。本文将以mysql explain为中心进行详细的阐述，从多个方面进行解释说明。

一、查询优化器

在执行SQL语句之前，mysql会先对SQL语句进行解析，并生成一颗查询解析树。得到查询解析树后，mysql会根据一些条件进行查询优化，包括索引、表连接顺序、子查询转换等，然后生成执行计划。在执行计划生成后，mysql会使用查询执行器对其进行执行，并返回结果。

在解析SQL语句的时候，mysql会尝试把查询转化为更高效的内部执行模型。mysql内部的执行模型是基于迭代器模型的，每个执行单元都是一个迭代器，可以将查询分为多个迭代器组合而成。优化器的主要作用是根据SQL语句的特点与限制条件，选择执行计划。

下面是一个简单的查询语句：

EXPLAIN SELECT id, name, age
FROM users
WHERE age > 18
ORDER BY age DESC, id ASC
LIMIT 10;

当我们执行以上查询语句时，优化器会根据语句中的WHERE条件和ORDER BY子句，确定查询优化策略。可以通过explain命令查看执行计划：

+----+-------------+-------+------------+------+---------------+------+---------+------+--------+----------+------------------------------------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows   | filtered | Extra                              |
+----+-------------+-------+------------+------+---------------+------+---------+------+--------+----------+------------------------------------+
| 1  | SIMPLE      | users | NULL       | ALL  | NULL          | NULL | NULL    | NULL | 100000 | 10.00    | Using where; Using filesort; Range |
+----+-------------+-------+------------+------+---------------+------+---------+------+--------+----------+------------------------------------+

其中的每一行都代表一个访问单元。

这里的select_type有三种可能的取值：

• SIMPLE
• PRIMARY
• SUBQUERY

以上查询语句的select_type为SIMPLE，表示这是一个简单的SELECT查询。下面来详细解释一下each字段的含义：

• id：查询标识符，表示查询的唯一标识符，通常是一个数字。如果该命令在同一个session中执行多次，每个查询的id都会不一样。
• select_type：查询类型，有以下几种取值：SIMPLE、PRIMARY、SUBQUERY等。
• table：查询的主表
• type：访问类型，代表了mysql在表中找到所需行的方式，常见的类型有：ALL、index、range等。
• possible_keys：可用索引，表示mysql能够使用哪些索引来优化查询。
• key：实际使用的索引，如果为NULL，则没有使用索引。
• key_len：索引长度
• ref：关联条件
• rows：扫描行数
• Extra：说明。

从上面的执行计划结果中可以看出，mysql并没有使用任何索引，而是进行了全表扫描，这是效率低下的。

二、WHERE子句的优化

在使用mysql explain分析查询计划时，WHERE子句往往是需要关注的重点，因为这决定了查询的过滤条件。

以下是一个示例，用于测试一个关于年龄的查询：

EXPLAIN SELECT * FROM users WHERE age > 30;

执行后的执行计划：

+----+-------------+-------+------------+------+---------------+------+---------+------+------+---------+-------------+
| ID | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered| Extra       |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+---------+-------------+
| 1  | SIMPLE      | users | NULL       | ALL  | NULL          | NULL | NULL    | NULL | 1000 | 100.00  | Using where |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+---------+-------------+

结果中的type字段为ALL，意味着mysql需要扫描整张表来查找满足WHERE条件的数据。

为了提高效率，可以为某些字段创建索引，例如对于该用户表中的age字段来说，可能希望它拥有一个索引。以下是一个为age字段创建索引的示例SQL语句：

ALTER TABLE users ADD INDEX (age);

然后，重新执行查询命令：

EXPLAIN SELECT * FROM users WHERE age > 30;

执行后的查询计划如下：

+----+-------------+-------+------------+------+---------------+------+---------+------+------+---------+-------------+
| ID | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered| Extra       |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+---------+-------------+
| 1  | SIMPLE      | users | NULL       | ref  | age           | age  | 5       | NULL | 14   | 100.00  | Using index |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+---------+-------------+

可以看出，当我们为age字段创建了一个索引时，查询优化器改变了type字段的值，并将key和ordering列的值设置为age。由于该索引使用了age字段，因此不再需要扫描全表，而是使用索引进行搜索，大大提高了查询效率。

三、JOIN查询优化

在mysql中，join操作通常是最花费资源的操作。为了最大限度地减少瓶颈并提高性能，我们必须对SQL查询进行优化。

以下是一个关于join操作的示例，用于模拟teacher和student两张表的关联查询：

EXPLAIN SELECT *
FROM teacher
JOIN student ON teacher.id = student.teacher_id
WHERE student.gender = '男';

执行后的查询计划如下：

+----+-------------+--------+------------+------+---------------+--------+---------+--------------------------+------+----------+-------+
| ID | select_type | table  | partitions | type | possible_keys | key    | key_len | ref                      | rows | filtered | Extra |
+----+-------------+--------+------------+------+---------------+--------+---------+--------------------------+------+----------+-------+
| 1  | SIMPLE      | student| NULL       | ALL  | NULL          | NULL   | NULL    | NULL                     | 3    | 33.33    | Using where |
| 1  | SIMPLE      | teacher| NULL       | ALL  | PRIMARY       | NULL   | NULL    | NULL                     | 2    | 100.00   | Using join buffer |
+----+-------------+--------+------------+------+---------------+--------+---------+--------------------------+------+----------+-------+

在这个例子中，我们可以看到Extra列是Using join buffer，这意味着mysql需要对结果使用外部算法进行排序，并且JOIN缓冲区不足以处理查询的连接关系，因此需要使用外部排序。

为了避免这样的性能问题，我们可以创建适当的索引，例如对于student表中的teacher_id字段来说，可能会创建一个索引字符串类型。以下是一个为该字段创建索引的示例SQL语句：

ALTER TABLE student ADD INDEX (teacher_id);

然后，重新执行查询命令：

EXPLAIN SELECT *
FROM teacher
JOIN student ON teacher.id = student.teacher_id
WHERE student.gender = '男';

执行计划如下：

+----+-------------+--------+------------+------+---------------+--------------+---------+--------------------------+------+----------+-------------+
| ID | select_type | table  | partitions | type | possible_keys | key          | key_len | ref                      | rows | filtered | Extra       |
+----+-------------+--------+------------+------+---------------+--------------+---------+--------------------------+------+----------+-------------+
| 1  | SIMPLE      | student| NULL       | ref  | teacher_id    | teacher_id   | 5       | const                    | 1    | 100.00   | Using index |
| 1  | SIMPLE      | teacher| NULL       | ALL  | PRIMARY       | NULL         | NULL    | NULL                     | 2    | 100.00   | Using where |
+----+-------------+--------+------------+------+---------------+--------------+---------+--------------------------+------+----------+-------------+

该查询执行计划的性能得到了大幅提升，type列现在变成了ref，说明我们已经使用了索引来优化查询。

四、查询优化实践

了解了mysql的explain命令的基本知识之后，我们现在来看一个实际的查询优化案例，其中使用了一些上述提到的优化技巧。

用例总结：假设我们有一个users表，其中存储了用户的姓名，年龄和性别信息。我们需要查询年龄在18岁以上的男性用户，并按照年龄降序，姓名升序排序，返回前10条数据。以下是这个查询的SQL语句及其执行计划：

EXPLAIN SELECT name,age,gender FROM users WHERE age > 18 AND gender = '男' ORDER BY age DESC,name ASC LIMIT 10;

执行计划如下：

+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+--------------------------+
| ID | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra                    |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+--------------------------+
| 1  | SIMPLE      | users | NULL       | ALL  | NULL          | NULL | NULL    | NULL | 1000 | 10.00    | Using where; Using filesort |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+--------------------------+

根据上面的执行计划结果，我们可以看到，mysql并没有使用任何索引，而是使用了WHERE条件进行查询，并使用了SORT排序算法。这样效率低下且需要更多的磁盘空间。

为了优化查询性能，我们可以考虑为age和gender字段创建索引，以下是一个为这些字段创建索引的示例SQL语句：

ALTER TABLE users ADD INDEX age_gender_idx(age, gender);

然后，重新执行查询命令：

EXPLAIN SELECT name,age,gender FROM users WHERE age > 18 AND gender = '男' ORDER BY age DESC,name ASC LIMIT 10;

执行计划如下：

+----+-------------+-------+------------+------+---------------+--------------+---------+------+------+----------+--------------------------+
| ID | select_type | table | partitions | type | possible_keys | key          | key_len | ref  | rows | filtered | Extra                    |
+----+-------------+-------+------------+------+---------------+--------------+---------+------+------+----------+--------------------------+
| 1  | SIMPLE      | users | NULL       | ref  | age_gender_idx| age_gender_idx| 6       | const|  28  | 90.00    | Using where              |
+----+-------------+-------+------------+------+---------------+--------------+---------+------+------+----------+--------------------------+

这次查询的