MySQL优化大杀器：使用 EXPLAIN , 快速定位性能瓶颈！

EXPLAIN 命令的使用非常简单，只需要在查询语句前加上 EXPLAIN 关键字即可。执行命令后，MySQL会输出一份查询执行计划的详细信息，包括表的连接顺序、使用的索引、扫描的行数等等。我们可以根据这些信息来判断查询语句的性能是否达到我们的要求，是否需要进行进一步的优化。文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/36240.html

准备

我们首先要创建两个表。一个是名为 orders 的订单表，其中包含订单信息，并且 customer_id（客户id）有索引，而 logistics_id（物流id） 则没有索引。文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/36240.html

1. +--------------+---------------+------+-----+---------+----------------+
2. | Field | Type | Null | Key | Default | Extra |
3. +--------------+---------------+------+-----+---------+----------------+
4. | id | varchar(11) | NO | PRI | NULL | |
5. | customer_id | varchar(32) | NO | MUL | NULL | |
6. | logistics_id | varchar(32) | NO | | NULL | |
7. | order_total | decimal(10,2) | NO | | NULL | |
8. +--------------+---------------+------+-----+---------+----------------+

另一个是名为 customer 的客户表文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/36240.html

1. +--------------+--------------+------+-----+---------+----------------+
2. | Field | Type | Null | Key | Default | Extra |
3. +--------------+--------------+------+-----+---------+----------------+
4. | id | varchar(32) | NO | PRI | NULL | |
5. | name | varchar(50) | NO | MUL | NULL | |
6. | email | varchar(100) | NO | | NULL | |
7. | phone | varchar(20) | YES | MUL | NULL | |
8. +--------------+--------------+------+-----+---------+----------------+

接下来我们使用 EXPLAIN 解释一条 SQL 语句，用于查询 id 为 A101、A102 客户的订单信息文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/36240.html

1. EXPLAIN SELECT
2. c.*,o.customer_id
3. FROM
4. customer c
5. LEFT JOIN orders o ON o.customer_id = c.id
6. WHERE c.id in ('A101','A102')

EXPLAIN 输出解析

解释后，我们可以得到下面的输出：文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/36240.html

1. +----+-------------+-------+------------+-------+---------------+-------------+---------+-----------+------+----------+-------------+
2. | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
3. +----+-------------+-------+------------+-------+---------------+-------------+---------+-----------+------+----------+-------------+
4. | 1 | SIMPLE | c | NULL | range | PRIMARY | PRIMARY | 130 | NULL | 2 | 100.00 | Using where |
5. | 1 | SIMPLE | o | NULL | ref | customer_id | customer_id | 130 | test.c.id | 1 | 100.00 | Using index |
6. +----+-------------+-------+------------+-------+---------------+-------------+---------+-----------+------+----------+-------------+

从输出结果中，我们可以看到以下信息：文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/36240.html

• select_type：查询类型
• table：查询涉及的表
• type：访问类型
• possible_keys：可能使用的索引
• key：实际使用的索引
• rows：扫描的行数
• filtered：过滤的行数比例
• Extra：其他信息

接下来我将详细说明这几种指标的类型和其含义。文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/36240.html

1. select_type 操作类型

• simple：简单的select查询，不包含子查询或union操作。
• primary：表示最外层查询。
• union：union操作中的第二个或后续查询。
• dependent union：union操作中的第二个或后续查询，依赖于外部查询。
• union result：union的结果集。
• subquery：子查询中的第一个select查询。
• dependent subquery：子查询中的第一个select查询，依赖于外部查询。
• derived：导出表的select查询，例如嵌套的子查询。

2. table 查询涉及的表

此次查询所涉及的表，如果有别名，会使用别名

3. partitions 匹配的分区数

如果查询操作未使用分区表，则该属性值为空。

4. type 访问类型

• all：全表扫描，逐行检查表中的每一行。
• index：索引全扫描，逐行检查索引树中的每一行。
• range：根据索引范围扫描一部分索引。
• ref：使用非唯一性索引查找匹配行。
• eq_ref：使用唯一性索引查找匹配行。
• const：使用常量值查找匹配行。
• system：类似const，但只有一行匹配，用于从系统表中读取一行数据。
• null：没有访问表或索引

type 的性能比较

all < index < range < ref < eq_ref < const < system文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/36240.html

all 是最慢的，所以我们一般避免 type 为 all文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/36240.html

5. possible_keys：可能使用的索引

possible_keys 列中列出的索引不一定会实际使用。MySQL 会根据表的统计信息和查询的复杂度等因素来选择使用哪些索引，在优化查询时，可以通过查看 possible_keys 列来确定是否需要创建新的索引来优化查询性能。如果查询中列出的所有索引都没有在 possible_keys 列中列出，那么可能需要创建一个新的索引来提高查询性能。

6. key：实际使用的索引

如果没有使用索引，则该属性值为NULL。

7. key_len：使用的索引长度

通常情况下，该值与索引列的字符数和列类型相关。

8. ref：参考列

通常与索引列相关联。

9. rows：预估扫描行数

不是精确值，而是MySQL的估计值。

10. filtered：过滤比

表示MySQL执行查询操作扫描的行数与实际返回的行数之比，即在查询操作中返回了多少行，与扫描的行数有多少是符合查询条件的。该值越接近1，则表示查询的效率越高。

11. Extra：额外信息

• Using filesort：表示需要进行排序操作。
• Using temporary：表示需要创建临时表。
• Using index：表示使用覆盖索引优化查询。
• Using where：表示使用 WHERE 子句进行过滤。
• Using join buffer：表示需要使用 join buffer来处理连接操作。
• Impossible where：表示使用的 WHERE 子句始终返回 false，结果集为空。

优化思路

所以我们优化的主要思路就是使 type 不为 all，并可以结合 Extra 修改SQL语句。一般有如下几种方法：文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/36240.html

1. 创建适当的索引：确保查询中涉及的列上有索引可以提高查询效率，例如使用 ALTER TABLE 语句添加索引。
2. 优化查询语句：尝试重写查询语句，使用更优化的 SQL 语法。例如，避免使用子查询和在 WHERE 子句中使用函数等操作，这些操作会使查询效率降低。
3. 增加缓存：如果经常运行相同的查询，可以考虑增加 MySQL 缓存的大小，这样可以避免对表进行频繁的扫描。