关于SQL优化常用方法 - EXPLAIN详解

因为最近实习中要做一些项目组中的SQL优化工作，常用到EXPLAIN工具，用于显示 SQL 查询的执行计划。它提供了关于查询如何执行的详细信息，可以优化性能，在这里做一个总结。

1 用法

当我们在执行一条sql语句的时候，只需在最前面加上 EXPLAIN 关键字即可，结果如下：

 

2 详解

当你执行类似以下命令时：

EXPLAIN SELECT * FROM your_table WHERE column = 'value';

返回表中各个字段的含义：

        1. id

                • 查询中每个步骤的标识符。

                • 数值越大，优先级越高。

                • 如果有多个行，表示查询中有多个步骤或子查询。

        2. select_type

                查询的类型，表示当前查询的类别。

                        SIMPLE：简单查询，不包含子查询或 UNION。

                        PRIMARY：主查询，包含子查询时外层的查询。

                        SUBQUERY：子查询中的 SELECT。

                        DERIVED：派生表（即子查询用于 FROM 子句）。

                        UNION：UNION 的第二个及后续查询。

                        UNION RESULT：UNION 合并的结果集。

        3. table

                        • 当前步骤涉及的表名。

        4. partitions

                        • 使用的分区（如果有分区表）。

        5. type

                表示表访问的类型，影响查询效率。

                        ALL：全表扫描，效率最低。

                        index：扫描索引，通常比全表扫描更快。

                        range：范围扫描，例如使用 BETWEEN 或 >.

                        ref：基于索引的非唯一匹配。

                        eq_ref：基于唯一索引的匹配，效率较高。

                        const/system：表中只有一行数据或系统表，效率最高。

                        NULL：不访问表，直接返回结果。

        6. possible_keys

                • 查询可能使用的索引列表。

        7. key

                • 查询实际使用的索引。如果没有使用索引，显示 NULL。

        8. key_len

                • 使用的索引部分的长度（字节数）。越小越高效。

        9. ref

                • 显示查询中使用哪个列或常量与索引匹配。

        10. rows

                • 估计需要读取的行数。值越小越好。

        11. filtered

                • 表示查询条件过滤掉的比例（百分比）。

                • 越高，说明条件过滤效果越好。

        12. Extra

                • 关于查询的附加信息。

                • Using index：完全从索引中获取数据，而无需访问表。

                • Using where：使用 WHERE 过滤数据。

                • Using temporary：需要使用临时表。

                • Using filesort：需要额外的排序操作，效率较低。

3 示例

执行以下查询：

EXPLAIN SELECT * FROM users WHERE age > 30;

返回的结果可能类似：

---

id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	users	range	age_index	age_index	4	NULL	100	50.00	Using where

解释：

• id：这是一个简单的查询，没有子查询。

• select_type：这是一个简单查询。

• table：操作的表是 users。

• type：使用了 range 扫描，查询范围是 age > 30。

• possible_keys：查询可能使用 age_index 索引。

• key：实际使用了 age_index。

• key_len：索引长度为 4 字节。

• ref：没有引用其他表。

• rows：预估需要扫描 100 行。

• filtered：查询条件过滤掉了 50%的数据。

• Extra：使用了 WHERE 条件。

4 总结

使用 EXPLAIN，最主要用途就是优化查询，例如添加合适的索引，避免全表扫描等。