MySQL（优化）性能分析之explain

MySQL提供了一个非常有用的语法——EXPLAIN，它可以帮助我们深入了解 SQL 查询的执行计划。通过分析 EXPLAIN的输出，我们可以发现潜在的性能瓶颈，并采取措施优化查询。

本文将详细讲解 EXPLAIN 的用法、各字段的含义，并结合具体代码示例，帮助更好地理解如何利用 EXPLAIN 来优化 MySQL查询。

什么是 EXPLAIN？

EXPLAIN是一个用于分析SQL查询执行计划的命令。它可以告诉我们 MySQL如何执行SELECT语句，包括表的连接方式、索引的使用情况以及扫描的行数等信息。通过这些信息，我们可以判断查询是否高效，并找到优化的方向。

基本语法

EXPLAIN SELECT 字段列表 FROM 表名 WHERE 条件;

例如：

EXPLAIN SELECT * FROM employees WHERE department_id = 5;

运行上述命令后，MySQL会返回一个结果集，其中每一列都提供了关于查询执行计划的具体信息。

EXPLAIN输出字段详解

在分析EXPLAIN的输出时，我们需要关注以下几个关键字段：

1. id

• 含义：表示查询的序列号。
• 作用：
  • 如果多个SELECT子句存在（如子查询或 UNION），每个子句都会有一个唯一的 id。
  • 执行顺序：
    • 如果id相同，则按从上到下的顺序执行。
    • 如果id不同，值越大越先执行。

2. select_type

• 含义：表示 SELECT 查询的类型。
• 常见取值：
  • SIMPLE：简单查询，不涉及子查询或表连接。
  • PRIMARY：主查询，通常是外层查询。
  • SUBQUERY：子查询，出现在 SELECT 或 WHERE 子句中。
  • UNION：UNION 中的第二个或后续查询。
  • DERIVED：派生表（子查询出现在 FROM 子句中）。

3. type

• 含义：表示表的访问类型或连接类型。
• 性能由好到差的顺序：
  • NULL：无需访问表。
  • system：表中只有一行数据。
  • const：表中最多只有一行匹配（通常用于主键或唯一索引）。
  • eq_ref：每行都与另一张表的一行精确匹配（常用于主键或唯一索引连接）。
  • ref：使用非唯一索引查找多行。
  • range：使用索引范围扫描。
  • index：全索引扫描。
  • ALL：全表扫描（最差性能）。

4. possible_keys

• 含义：可能使用的索引。
• 作用：列出所有候选索引，但并不保证实际使用。

5. key

• 含义：实际使用的索引。
• 注意：如果为 NULL，说明没有使用索引。

6. key_len

• 含义：索引使用的字节数。
• 解读：值越小越好，表示索引更高效。

7. rows

• 含义：MySQL 预估需要扫描的行数。
• 注意：对于 InnoDB引擎，这是一个估计值，可能不准确。

8. filtered

• 含义：返回结果占扫描行数的百分比。
• 解读：值越高越好，表示过滤效率更高。

代码示例

示例 1：简单查询

假设我们有一个 employees 表，包含以下字段：id（主键）、name、department_id。

查询语句

EXPLAIN SELECT * FROM employees WHERE department_id = 5;

输出结果

分析

• id: 1，只有一个查询。
• select_type: SIMPLE，这是一个简单的查询。
• type: ref，使用了非唯一索引 department_id。
• rows: 10，MySQL 预估需要扫描 10 行。
• filtered: 100.00，所有扫描的行都符合条件。

示例 2：带子查询的复杂查询

假设我们还有一个departments 表，包含字段 id 和 name。

查询语句

EXPLAIN SELECT e.name FROM employees e WHERE e.department_id IN (SELECT d.id FROM departments d WHERE d.name = 'HR');

输出结果

分析

• id: 1 是主查询，2 是子查询。
• select_type: 主查询是PRIMARY，子查询是SUBQUERY。
• type: 主查询是ALL（全表扫描），子查询是ref（使用了索引）。
• rows: 主查询扫描了 100 行，子查询只扫描了 1 行。
• 建议：主查询未使用索引，考虑为department_id添加索引。

示例 3：多表连接查询

查询语句

EXPLAIN SELECT e.name, d.name FROM employees e JOIN departments d ON e.department_id = d.id WHERE d.name = 'HR';

输出结果

分析

• id: 1，两个表的连接属于同一个查询。
• type: d 表使用了ref，e表使用了eq_ref（高效连接）。
• rows: 每个表只扫描了1行。
• 结论：此查询性能较好，索引使用得当。

通过 EXPLAIN，我们可以深入了解 MySQL 查询的执行计划，从而发现潜在的性能问题并进行优化。总结以下几点：

1. 尽量避免全表扫描：确保查询能够使用索引（type 应避免 ALL）。
2. 减少扫描行数：优化条件和索引设计，降低rows的值。
3. 优先使用高效连接类型：如eq_ref 和ref。
4. 合理使用子查询：避免不必要的嵌套查询。