MySQL EXPLAIN FORMAT=JSON：深入解读查询执行计划的奥秘

各位同学，大家好！今天我们来深入探讨MySQL的EXPLAIN FORMAT=JSON输出，它提供了比传统EXPLAIN更详细的查询执行计划信息，帮助我们理解MySQL如何执行SQL查询，并进行性能优化。我们将重点关注其内部细节和成本估算，帮助大家更好地理解和利用这个强大的工具。

1. 为什么要使用 EXPLAIN FORMAT=JSON？

传统的 EXPLAIN 命令虽然能提供一些关于查询执行计划的信息，例如使用的索引、访问类型等，但信息相对有限，难以深入了解查询优化的细节。EXPLAIN FORMAT=JSON 提供了更结构化、更详细的输出，包含更多关于成本估算、连接顺序、优化器决策等信息。这些信息对于识别性能瓶颈，优化索引设计，以及理解MySQL的查询优化器的工作方式至关重要。

2. EXPLAIN FORMAT=JSON 的基本结构

EXPLAIN FORMAT=JSON 的输出是一个JSON文档，描述了查询的执行计划。这个JSON文档主要包含以下几个部分：

• query_block: 代表查询中的一个查询块，例如一个SELECT语句、一个子查询或一个UNION操作。
• select_id: 查询块的唯一标识符。
• nested_loop: 代表嵌套循环连接操作，这是最常见的连接类型。
• table: 包含关于访问表的详细信息，例如表名、使用的索引、访问类型等。
• cost_info: 包含关于查询成本的估算信息，例如读取的行数、CPU成本、IO成本等。
• attached_condition: 当某些条件被下推到存储引擎时，会在这里显示。
• materialized_from_subquery: 如果查询使用了物化子查询，会在这里显示子查询的执行计划。

3. 深入解读关键字段

接下来，我们通过一些示例来深入解读 EXPLAIN FORMAT=JSON 输出中的关键字段。

3.1 示例查询

首先，我们创建一个简单的表 users：

CREATE TABLE `users` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `username` varchar(255) NOT NULL,
  `email` varchar(255) NOT NULL,
  `age` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `idx_username` (`username`),
  KEY `idx_email` (`email`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

INSERT INTO `users` (`username`, `email`, `age`) VALUES
('Alice', 'alice@example.com', 25),
('Bob', 'bob@example.com', 30),
('Charlie', 'charlie@example.com', 35),
('David', 'david@example.com', 40),
('Eve', 'eve@example.com', 28);

我们执行以下查询：

EXPLAIN FORMAT=JSON SELECT * FROM users WHERE username = 'Alice' AND age > 20;

3.2 EXPLAIN FORMAT=JSON 输出示例

以下是 EXPLAIN FORMAT=JSON 的输出示例：

{
  "query_block": {
    "select_id": 1,
    "cost_info": {
      "query_cost": "1.15",
      "sort_cost": "0.00",
      "rows_produced": "1",
      "tmp_table_cost": "0.00"
    },
    "table": {
      "table_name": "users",
      "access_type": "ref",
      "possible_keys": [
        "idx_username"
      ],
      "key": "idx_username",
      "used_key_parts": [
        "username"
      ],
      "key_length": "767",
      "ref": [
        "const"
      ],
      "rows": "1",
      "filtered": "100.00",
      "cost_info": {
        "read_cost": "0.15",
        "eval_cost": "1.00",
        "prefix_cost": "1.15",
        "data_read_per_join": "376"
      },
      "attached_condition": "(`users`.`age` > 20)"
    }
  }
}

3.3 关键字段详解

• select_id: 1 表示这是查询中的第一个SELECT块。如果查询包含子查询或UNION，则会有多个 select_id。
• cost_info (query_block级别):
  • query_cost: 1.15 表示整个查询块的估算成本。这是一个相对值，用于比较不同执行计划的优劣。
  • sort_cost: 0.00 表示排序的估算成本。如果查询需要排序（例如使用 ORDER BY），这个值会增加。
  • rows_produced: 1 表示查询块预计产生的行数。这个值会影响后续连接操作的成本估算。
  • tmp_table_cost: 0.00 表示创建临时表的估算成本。如果查询需要创建临时表（例如使用 GROUP BY 或 DISTINCT），这个值会增加。
• table: 描述了对 users 表的访问。
  • table_name: users 表示正在访问的表名。
  • access_type: ref 表示使用索引进行查找。ref 表示使用非唯一索引或唯一索引的前缀进行查找。 其他常见的访问类型包括：
    • system: 表只有一行数据，通常是系统表。
    • const: 表只有一行匹配，且该行数据在查询优化阶段已知。
    • eq_ref: 对于来自之前表的每一行，在该表中只有一行满足条件（使用唯一索引或主键）。
    • range: 使用索引范围扫描。
    • index: 全索引扫描。
    • all: 全表扫描。
  • possible_keys: ["idx_username"] 表示可能使用的索引。
  • key: idx_username 表示实际使用的索引。
  • used_key_parts: ["username"] 表示实际使用的索引列。
  • key_length: 767 表示使用的索引长度（字节）。
  • ref: ["const"] 表示索引列与常量进行比较。
  • rows: 1 表示MySQL预计需要扫描的行数。 这个值只是一个估计值，并不一定准确。
  • filtered: 100.00 表示经过索引过滤后，满足其他条件的行数百分比。在本例中，所有通过索引 idx_username 找到的行都满足 age > 20 的条件。
  • cost_info (table级别):
    • read_cost: 0.15 表示读取数据的估算成本。
    • eval_cost: 1.00 表示评估条件的估算成本。
    • prefix_cost: 1.15 表示访问该表的总估算成本。
    • data_read_per_join: 376 表示在此join操作中读取的数据量（字节）。
  • attached_condition: (users.age> 20) 表示被下推到存储引擎的条件。这意味着MySQL尝试在存储引擎级别过滤数据，而不是在MySQL服务器级别。

3.4 成本估算的意义

cost_info 提供了关于查询成本的估算信息。这些成本是相对的，用于比较不同执行计划的优劣。一般来说，query_cost 越低，执行计划越好。需要注意的是，成本估算依赖于统计信息，如果统计信息不准确，成本估算也可能不准确。因此，定期更新表的统计信息非常重要：

ANALYZE TABLE users;

4. 复杂的查询示例

让我们看一个更复杂的例子，涉及多个表和一个子查询。

4.1 创建 orders 表

CREATE TABLE `orders` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `user_id` int(11) NOT NULL,
  `order_date` datetime NOT NULL,
  `amount` decimal(10,2) NOT NULL,
  PRIMARY KEY (`id`),
  KEY `idx_user_id` (`user_id`),
  CONSTRAINT `fk_user_id` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

INSERT INTO `orders` (`user_id`, `order_date`, `amount`) VALUES
(1, '2023-01-01', 100.00),
(1, '2023-01-02', 200.00),
(2, '2023-01-03', 300.00),
(3, '2023-01-04', 400.00),
(4, '2023-01-05', 500.00);

4.2 查询语句

EXPLAIN FORMAT=JSON SELECT u.username, (SELECT SUM(o.amount) FROM orders o WHERE o.user_id = u.id) AS total_amount
FROM users u
WHERE u.age > (SELECT AVG(age) FROM users);

4.3 EXPLAIN FORMAT=JSON 输出

{
  "query_block": {
    "select_id": 1,
    "cost_info": {
      "query_cost": "11.30",
      "sort_cost": "0.00",
      "rows_produced": "5",
      "tmp_table_cost": "0.00"
    },
    "nested_loop": [
      {
        "table": {
          "table_name": "u",
          "access_type": "ALL",
          "possible_keys": null,
          "key": null,
          "rows": "5",
          "filtered": "20.00",
          "cost_info": {
            "read_cost": "1.00",
            "eval_cost": "1.00",
            "prefix_cost": "2.00",
            "data_read_per_join": "1K"
          },
          "attached_condition": "(`u`.`age` > (select #2))"
        }
      },
      {
        "table": {
          "table_name": "<subquery2>",
          "access_type": "eq_ref",
          "possible_keys": null,
          "key": "auto_key0",
          "used_key_parts": [
            "user_id"
          ],
          "key_length": "4",
          "ref": [
            "u.id"
          ],
          "rows": "1",
          "filtered": "100.00",
          "cost_info": {
            "read_cost": "2.20",
            "eval_cost": "0.00",
            "prefix_cost": "2.20",
            "data_read_per_join": "376"
          }
        }
      }
    ]
  },
  "query_block": {
    "select_id": 2,
    "cost_info": {
      "query_cost": "1.15",
      "sort_cost": "0.00",
      "rows_produced": "1",
      "tmp_table_cost": "0.00"
    },
    "table": {
      "table_name": "users",
      "access_type": "ALL",
      "possible_keys": null,
      "key": null,
      "rows": "5",
      "filtered": "100.00",
      "cost_info": {
        "read_cost": "1.00",
        "eval_cost": "0.15",
        "prefix_cost": "1.15",
        "data_read_per_join": "1K"
      }
    }
  }
}

4.4 分析

• select_id: 1: 主查询块。
  • nested_loop: 表示使用了嵌套循环连接。
  • 第一个 table (别名 u): 对 users 表进行全表扫描 (access_type: ALL)。这是因为没有合适的索引可以用于过滤 u.age > (SELECT AVG(age) FROM users) 条件。 filtered: "20.00" 表示只有20%的行满足这个条件。
  • 第二个 table (别名 <subquery2>): 这是一个子查询，用于计算每个用户的总订单金额。 access_type: eq_ref 表示对于 users 表中的每一行，都使用 orders 表的索引 idx_user_id 进行查找。
• select_id: 2: 子查询 (SELECT AVG(age) FROM users)。
  • 对 users 表进行全表扫描 (access_type: ALL) 来计算平均年龄。

4.5 优化建议

这个查询的性能瓶颈在于对 users 表进行全表扫描。为了优化这个查询，我们可以创建一个索引来加速 u.age > (SELECT AVG(age) FROM users) 条件的过滤：

ALTER TABLE users ADD INDEX idx_age (age);

重新执行 EXPLAIN FORMAT=JSON 后，可以看到执行计划发生了变化，users 表使用了索引 idx_age，提高了查询效率。

5. 理解连接类型

理解 access_type 对于优化查询至关重要。以下是一些常见的 access_type 及其含义：

access_type	含义	优化建议
system	表只有一行数据，通常是系统表。	无需优化。
const	表只有一行匹配，且该行数据在查询优化阶段已知。	无需优化。
eq_ref	对于来自之前表的每一行，在该表中只有一行满足条件（使用唯一索引或主键）。	确保使用唯一索引或主键。
ref	使用索引进行查找。	检查索引是否被充分利用，考虑增加索引列或调整查询条件。
range	使用索引范围扫描。	检查范围条件是否合理，考虑使用更精确的条件或优化索引。
index	全索引扫描。	表中的数据量较小，或者查询需要访问索引中的所有列。 如果查询只需要访问表中的少量数据，则应该考虑使用覆盖索引或优化查询条件。
all	全表扫描。	这是最慢的访问类型。 应该尽量避免全表扫描，通过添加索引或优化查询条件来提高查询效率。

6. 如何利用 EXPLAIN FORMAT=JSON 进行性能调优

• 识别性能瓶颈: 通过分析 EXPLAIN FORMAT=JSON 的输出，找到查询中成本最高的部分，例如全表扫描、排序操作等。
• 优化索引: 根据查询条件和访问类型，创建合适的索引来加速数据访问。
• 调整查询语句: 优化查询语句，例如避免使用 SELECT *，减少子查询的使用，改写复杂的JOIN操作等。
• 更新统计信息: 定期更新表的统计信息，确保成本估算的准确性。

7. 成本估算的局限性

虽然 EXPLAIN FORMAT=JSON 提供了详细的成本估算信息，但需要注意的是，这些估算并不是完全准确的。成本估算依赖于统计信息，如果统计信息不准确，成本估算也可能不准确。此外，成本估算没有考虑硬件资源、并发情况等因素。因此，在进行性能优化时，不能仅仅依赖于成本估算，还需要结合实际情况进行测试和评估。

8. 一些实用技巧

• 使用 ANALYZE TABLE 更新统计信息: 定期运行 ANALYZE TABLE 命令来更新表的统计信息，确保成本估算的准确性。
• 关注 filtered 列: filtered 列表示经过索引过滤后，满足其他条件的行数百分比。如果 filtered 值较低，说明索引过滤效果不佳，可能需要考虑优化索引或查询条件。
• 比较不同执行计划的成本: 可以通过修改查询语句或索引来生成不同的执行计划，然后比较它们的成本，选择最优的执行计划。

9. 更多使用EXPLAIN FORMAT=JSON的实例

9.1 优化 OR 条件

假设有如下查询：

SELECT * FROM users WHERE username = 'Alice' OR email = 'bob@example.com';

如果 username 和 email 都有独立的索引，MySQL可能不会同时使用这两个索引。通过 EXPLAIN FORMAT=JSON 可以看到是否使用了索引合并 (index_merge)。 如果没有使用，可以尝试使用 UNION 来改写查询：

SELECT * FROM users WHERE username = 'Alice'
UNION ALL
SELECT * FROM users WHERE email = 'bob@example.com' AND username != 'Alice';

然后比较两种查询的执行计划和性能。

9.2 避免隐式类型转换

如果查询中使用了隐式类型转换，可能会导致索引失效。例如：

SELECT * FROM users WHERE id = '1'; -- id 是 INT 类型

虽然 '1' 可以隐式转换为整数，但MySQL可能不会使用 id 列上的索引。 通过EXPLAIN FORMAT=JSON可以发现是否使用了索引，如果没有，应该将查询改为：

SELECT * FROM users WHERE id = 1;

9.3 理解连接顺序

在多表连接查询中，表的连接顺序会影响查询性能。MySQL的查询优化器会尝试找到最优的连接顺序。通过 EXPLAIN FORMAT=JSON 可以看到实际的连接顺序。 如果发现连接顺序不合理，可以使用 STRAIGHT_JOIN 强制指定连接顺序 (慎用)。

10. 总结和建议

今天我们深入探讨了 EXPLAIN FORMAT=JSON 的输出，学习了如何解读其内部细节和成本估算。 掌握 EXPLAIN FORMAT=JSON 的使用方法，可以帮助我们更好地理解MySQL的查询优化器，识别性能瓶颈，优化索引设计，以及调整查询语句。希望大家在实际工作中多多练习，不断提高SQL优化能力。

掌握成本评估，优化查询性能，提升数据库效率。