MySQL-SQL优化Explain命令以及参数详解

前言

在MySQL优化的众多手段中，EXPLAIN命令扮演着至关重要的角色。它是数据库管理员和开发者手中的利器，用于分析SQL查询的执行计划。通过执行EXPLAIN，MySQL会提供一份详细的查询执行计划报告，这份报告揭示了查询将如何执行，包括数据访问路径、表的连接顺序、使用的索引、预期扫描的行数等关键信息。这些信息对于识别和解决性能瓶颈至关重要。
基于EXPLAIN提供的洞察，我们可以采取措施，如优化查询逻辑、调整索引设计、改变数据表结构等，以消除不必要的全表扫描、减少数据扫描量、优化表连接顺序等，从而达到提升查询效率的目的。因此，熟练掌握并运用EXPLAIN不仅是MySQL性能优化的起点，也是贯穿整个优化过程的核心技能之一。在数据库设计初期、代码开发阶段以及后续的性能调优工作中，适时地使用EXPLAIN进行查询分析，能够有效地指导我们做出正确的决策，确保数据库系统的高效稳定运行。
下面我们就来解析一下EXPLAIN命令以及参数的详细解释：

1.id
含义：查询中每个表别名的标识符，或者是查询中每个SELECT子句的标识符。
解析：如果id相同，执行顺序由上至下；id不同且为子查询时，id序号会递增，id值越大优先级越高，越先被执行；id既有相同的，又有不同的，id相同的一组内部按顺序执行，不同组之间id值大的优先执行。
2. select_type
含义：查询中每个SELECT子句的类型。
常见类型：
SIMPLE：简单的SELECT查询，不包含子查询或者UNION。
PRIMARY：最外面的SELECT查询。
UNION：UNION中的第二个或之后的SELECT查询。
DEPENDENT UNION：UNION中的第二个或之后的SELECT查询，其结果依赖于之前的查询。
UNION RESULT：UNION的结果。
SUBQUERY：子查询中的第一个SELECT。
DEPENDENT SUBQUERY：子查询中的第一个SELECT，其结果依赖于外部查询。
3. table
含义：显示这一行的数据是关于哪张表的。
4. type
含义：表示MySQL如何访问表来获取数据。从最优到最差的顺序大致为：const, eq_ref, ref, range, index, ALL。
常见类型：
const：最多只会有一条匹配记录，通常是因为主键或唯一索引被使用。
eq_ref：对于每个来自于前面表的行组合，从该表中读取一行。常见于使用唯一索引的JOIN操作。
ref：索引扫描，返回匹配某个单独值的所有行。
range：索引范围扫描，对索引的范围进行扫描，常见于BETWEEN, IN()等查询。
index：全索引扫描，遍历整个索引。
ALL：全表扫描，性能最差。
5. possible_keys
含义：指出MySQL能使用哪个索引在该表中查找行。
6. key
含义：实际使用的索引。如果为NULL，则没有使用索引。
7. key_len
含义：使用的索引的长度。在不损失精确性的情况下，长度越短越好。
8. ref
含义：显示索引的哪一列被使用了，如果可能的话，是一个常数。
9. rows
含义：MySQL认为必须检查的行数来执行查询。
10. Extra
含义：提供了额外的信息，如Using index（使用了覆盖索引，避免了访问表的数据行）、Using where（需要使用WHERE子句进行过滤）、Using temporary（使用了临时表）、Using filesort（需要进行排序操作）等。

示例：

EXPLAIN SELECT students.name, courses.name 
FROM students 
INNER JOIN courses ON students.course_id = courses.id 
WHERE students.age > 18;

+----+-------------+----------+------------+------+---------------+---------+---------+------------------+------+----------+
| id | select_type | table    | partitions | type | possible_keys | key     | key_len | ref              | rows | filtered | Extra       |
+----+-------------+----------+------------+------+---------------+---------+---------+------------------+------+----------+
|  1 | SIMPLE      | students | NULL       | ref  | idx_age       | idx_age | 5       | const            | 100  |   100.00 | Using where |
|  1 | SIMPLE      | courses  | NULL       | eq_ref| PRIMARY       | PRIMARY | 4       | test.students.cid|    1 |   100.00 | NULL        |
+----+-------------+----------+------------+------+---------------+---------+---------+------------------+------+----------+

在这个例子中，可以看到：
第一个表students通过索引idx_age进行了索引范围扫描（ref），并且因为WHERE子句过滤了年龄大于18岁的学生。
第二个表courses通过主键PRIMARY进行了等值匹配（eq_ref），这得益于在JOIN条件中使用了索引。

通过这样的输出，我们可以判断查询是否高效，以及是否有优化空间，比如是否需要添加或修改索引。