MySQL编程进阶之：`EXPLAIN`的深度解析：从`type`、`rows`和`extra`等字段分析SQL性能。 - 智猿学院-前后端，数据库，人工智能，云计算等领域前沿技术讲座

各位听众，大家好！我是你们的老朋友，今天咱们来聊聊MySQL性能优化的秘密武器——EXPLAIN。别看它名字平平无奇，但只要你掌握了它，就能像福尔摩斯一样，轻松找出SQL语句中的性能瓶颈，让你的数据库跑得飞快！

咱们今天的主题是“EXPLAIN的深度解析：从type、rows和extra等字段分析SQL性能”。我会尽量用大白话，配合一些实战案例，让大家听得明白，学得会，用得上。

一、EXPLAIN是什么？为什么我们需要它？

简单来说，EXPLAIN就是MySQL提供的用于分析SQL查询语句执行计划的工具。它可以告诉你MySQL将如何执行你的SQL语句，包括使用哪些索引，扫描多少行数据，以及是否需要额外的操作等等。

想象一下，你要去一个陌生的地方，EXPLAIN就像是一张地图，告诉你应该走哪条路，避开哪些坑，最终才能最快到达目的地。对SQL语句来说，这个“目的地”就是查询结果，而EXPLAIN就是帮你找到最佳路径的地图。

如果没有EXPLAIN，你就像蒙着眼睛开车，不知道SQL语句到底做了什么，性能瓶颈在哪里，只能靠瞎猜和试错，效率低下不说，还容易把数据库搞崩溃。

二、EXPLAIN的用法：一句话搞定

使用EXPLAIN非常简单，只需要在你的SQL语句前面加上EXPLAIN关键字即可。例如：

EXPLAIN SELECT * FROM users WHERE age > 20;

执行这条语句后，MySQL会返回一个表格，里面包含了关于这条SQL语句执行计划的各种信息。

三、EXPLAIN结果的解读：核心字段详解

EXPLAIN返回的结果有很多列，但我们最需要关注的是type、possible_keys、key、key_len、rows和extra这几个字段。下面我们逐一讲解：

1. id：查询的标识符

id列表示查询中执行select子句或操作表的顺序。
id越大优先级越高，越先被执行。
如果id相同，可以认为是一组，从上往下顺序执行。
在所有组中，id值越大，优先级越高，越先执行。

2. select_type：查询类型

select_type表示对应行是简单查询还是复杂查询。常见的类型有：

`select_type`	含义
`SIMPLE`	简单查询，不包含子查询或UNION。
`PRIMARY`	最外层的SELECT，在拥有子查询的语句中，最外面的SELECT查询计划就是PRIMARY。
`SUBQUERY`	在SELECT或WHERE列表中包含了子查询。
`DERIVED`	在FROM列表中包含的子查询被标记为DERIVED(衍生)，MySQL会递归执行这些子查询，把结果放在临时表里。
`UNION`	若第二个SELECT出现在UNION之后，则被标记为UNION；若UNION包含在FROM子句的子查询中，外层SELECT将被标记为：DERIVED。
`UNION RESULT`	从UNION表获取结果的SELECT。
`DEPENDENT SUBQUERY`	子查询的结果依赖于外部查询。这意味着子查询需要针对外部查询的每一行执行一次。这通常会导致性能问题，尤其是当外部查询返回大量行时。例如：`SELECT * FROM t1 WHERE col1 IN (SELECT col2 FROM t2 WHERE t2.col3 = t1.col3);`
`DEPENDENT UNION`	和DEPENDENT SUBQUERY类似，UNION中的SELECT依赖于外部查询。
`MATERIALIZED`	MySQL需要将子查询的结果物化（Materialize）到一个临时表中。这通常发生在子查询的结果需要在多个地方使用，或者子查询的结果集太大，无法直接在内存中处理时。
`UNCACHEABLE SUBQUERY`	无法被缓存的子查询。这意味着MySQL每次执行外部查询时都需要重新执行子查询。这通常发生在子查询中使用了某些不确定性的函数（如RAND()），或者子查询的结果集变化频繁时。
`UNCACHEABLE UNION`	和UNCACHEABLE SUBQUERY类似，UNION中的SELECT无法被缓存。

3. table：查询涉及的表

这个字段很简单，就是SQL语句中涉及的表名。

4. partitions：匹配的分区

如果表是分区表，这个字段会显示查询匹配的分区。

5. type：最重要的字段！访问类型

type字段是EXPLAIN结果中最最重要的字段之一，它表示MySQL如何查找表中的行。它的值有很多种，从最佳到最差依次是：

system： 表中只有一行数据，或者表为空。这是最佳的访问类型，通常发生在系统表或临时表中。
const： 使用主键或唯一索引进行等值查询。MySQL可以直接通过索引找到唯一的一行数据，效率非常高。
eq_ref： 使用主键或唯一索引进行关联查询。MySQL可以使用索引找到关联表中唯一的一行数据。
ref： 使用非唯一索引进行等值查询。MySQL可以使用索引找到多行数据。
fulltext： 使用全文索引进行查询。
ref_or_null： 类似于ref，但是MySQL还需要额外搜索包含NULL值的行。
index_merge： 使用多个索引合并来查找数据。
unique_subquery： 在IN子查询中使用唯一索引。
index_subquery： 在IN子查询中使用非唯一索引。
range： 使用索引进行范围查询，例如BETWEEN、>、<、IN等。
index： 扫描整个索引树来查找数据。通常发生在查询只需要索引列的情况下。
ALL： 最差的访问类型！扫描整个表来查找数据。这意味着MySQL需要读取表中的每一行数据，效率非常低。

一般来说，我们应该尽量避免ALL和index这两种访问类型，尽量让type达到ref或以上。

示例：

-- 假设users表有一个主键id和一个索引name
EXPLAIN SELECT * FROM users WHERE id = 1; -- type: const
EXPLAIN SELECT * FROM users WHERE name = '张三'; -- type: ref
EXPLAIN SELECT * FROM users; -- type: ALL

6. possible_keys：可能用到的索引

这个字段表示MySQL在查询过程中可能使用到的索引。注意，只是“可能”，不代表一定会使用。

7. key：实际用到的索引

这个字段表示MySQL在查询过程中实际使用的索引。如果没有使用索引，则为NULL。

8. key_len：索引的长度

这个字段表示MySQL使用的索引的长度。通过key_len可以判断MySQL使用了复合索引的哪些列。

9. ref：索引的哪一列被使用了

显示了之前的表在key列记录的索引中查找值所用的列或常量。

10. rows：估计要检查的行数

这个字段表示MySQL为了找到结果，估计需要扫描的行数。rows越小，效率越高。

11. filtered：过滤比例

表示经过过滤后，剩余的行数占rows的百分比。

12. Extra：额外信息

Extra字段包含了MySQL在查询过程中执行的一些额外操作的信息。这个字段非常重要，可以帮助我们进一步了解查询的性能瓶颈。常见的Extra值有：

Using index： 查询只需要访问索引即可完成，不需要回表查询。这意味着查询的列都在索引中，效率非常高。
Using where： MySQL需要使用WHERE子句来过滤结果。这意味着查询的列不在索引中，MySQL需要先读取数据，然后再进行过滤。
Using temporary： MySQL需要使用临时表来存储中间结果。这通常发生在ORDER BY或GROUP BY子句中，效率较低。
Using filesort： MySQL需要使用文件排序来对结果进行排序。这通常发生在没有合适的索引可以用于排序的情况下，效率非常低。
Using join buffer (Block Nested Loop)： MySQL需要使用连接缓冲区来优化连接查询。这通常发生在连接的表没有合适的索引可以使用的情况下。
Impossible WHERE noticed after reading const tables： WHERE子句总是false，导致没有符合条件的行。
No tables used： 没有FROM子句或其他无法访问的表。
Distinct: MySQL 发现第一条匹配行后,停止为当前的行组合搜索更多的行。
Not exists: MySQL 可以对查询进行 LEFT JOIN 优化,在找到一条匹配 LEFT JOIN 标准的行后,不再为前面的行组合在该表内检查更多的行。

一般来说，我们应该尽量避免Using temporary和Using filesort这两种情况，尽量让查询使用索引来避免全表扫描。

四、实战案例：优化SQL性能

说了这么多理论，不如来点实际的。我们来看几个实战案例，演示如何使用EXPLAIN来优化SQL性能。

案例1：避免全表扫描

假设我们有一个orders表，包含了订单信息，其中有一个user_id字段表示用户ID。我们想要查询某个用户的订单数量：

SELECT COUNT(*) FROM orders WHERE user_id = 123;

如果user_id字段没有索引，那么这条SQL语句会进行全表扫描，效率非常低。我们可以使用EXPLAIN来验证这一点：

EXPLAIN SELECT COUNT(*) FROM orders WHERE user_id = 123;

如果type字段是ALL，说明MySQL进行了全表扫描。我们可以创建一个user_id索引来优化这条SQL语句：

CREATE INDEX idx_user_id ON orders (user_id);

然后再次执行EXPLAIN：

EXPLAIN SELECT COUNT(*) FROM orders WHERE user_id = 123;

如果type字段变成了ref，说明MySQL使用了索引，效率大大提高。

案例2：优化排序

假设我们想要查询所有订单，并按照订单时间进行排序：

SELECT * FROM orders ORDER BY order_time;

如果order_time字段没有索引，那么这条SQL语句会进行文件排序，效率非常低。我们可以使用EXPLAIN来验证这一点：

EXPLAIN SELECT * FROM orders ORDER BY order_time;

如果Extra字段包含了Using filesort，说明MySQL进行了文件排序。我们可以创建一个order_time索引来优化这条SQL语句：

CREATE INDEX idx_order_time ON orders (order_time);

然后再次执行EXPLAIN：

EXPLAIN SELECT * FROM orders ORDER BY order_time;

如果Extra字段不再包含Using filesort，并且type字段是index，说明MySQL使用了索引进行排序，效率大大提高。

案例3：优化连接查询

假设我们有两个表：users和orders，它们之间通过user_id字段进行关联。我们想要查询所有用户的订单信息：

SELECT * FROM users JOIN orders ON users.id = orders.user_id;

如果orders表的user_id字段没有索引，那么这条SQL语句可能会进行全表扫描，效率非常低。我们可以使用EXPLAIN来验证这一点：

EXPLAIN SELECT * FROM users JOIN orders ON users.id = orders.user_id;

如果type字段是ALL，说明MySQL进行了全表扫描。我们可以创建一个user_id索引来优化这条SQL语句：

CREATE INDEX idx_user_id ON orders (user_id);

然后再次执行EXPLAIN：

EXPLAIN SELECT * FROM users JOIN orders ON users.id = orders.user_id;

如果type字段变成了ref或eq_ref，说明MySQL使用了索引，效率大大提高。

案例4：复合索引的使用

假设我们有一个products表，包含了商品信息，其中有category_id和price字段。我们想要查询某个分类下，价格小于某个值的商品：

SELECT * FROM products WHERE category_id = 123 AND price < 100;

我们可以创建一个复合索引来优化这条SQL语句：

CREATE INDEX idx_category_id_price ON products (category_id, price);

这个复合索引会先按照category_id进行排序，然后再按照price进行排序。这样，MySQL就可以使用索引来快速找到符合条件的商品。

五、注意事项：EXPLAIN的局限性

虽然EXPLAIN是一个强大的工具，但它也有一些局限性：

EXPLAIN只能分析SELECT语句，不能分析INSERT、UPDATE、DELETE等语句。
EXPLAIN只能提供一个估计的执行计划，实际的执行情况可能会有所不同。
EXPLAIN不能告诉你SQL语句的执行时间。
EXPLAIN不能分析存储过程和触发器。

因此，在使用EXPLAIN时，我们需要结合实际情况进行分析，不能完全依赖EXPLAIN的结果。

六、总结：成为SQL性能优化大师

今天我们深入学习了EXPLAIN的使用方法，了解了各个字段的含义，并通过实战案例演示了如何使用EXPLAIN来优化SQL性能。

记住，EXPLAIN只是一个工具，关键在于理解SQL的执行原理，以及如何选择合适的索引。只要你勤加练习，善于思考，就能成为SQL性能优化大师！

希望今天的讲座对大家有所帮助，谢谢大家！下课！

发表回复 取消回复

发表回复取消回复