MySQL的EXPLAIN FORMAT=JSON：如何深入解读其输出的内部细节与成本估算？

MySQL EXPLAIN FORMAT=JSON：内部细节与成本估算深度解读

大家好，今天我们来深入探讨MySQL的EXPLAIN FORMAT=JSON，它提供了一种结构化的方式来理解MySQL的查询执行计划。我们不仅要了解JSON输出的各个字段，还要深入研究其背后的逻辑和成本估算方法，以便更好地优化SQL查询。

1. EXPLAIN FORMAT=JSON 的基本使用

首先，我们来看一个简单的示例：

EXPLAIN FORMAT=JSON SELECT * FROM employees WHERE dept_id = 10 AND salary > 50000;

执行上述语句后，你会得到一个JSON格式的字符串。这个字符串包含了查询执行的所有信息，包括使用的表、索引、访问方法、成本估算等。

2. JSON 输出结构概览

JSON输出的顶层结构通常包含一个query_block对象，它描述了整个查询。query_block内部可能包含多个嵌套的块，对应于子查询或UNION操作。

一个典型的query_block结构如下：

{
  "query_block": {
    "select_id": 1,
    "cost_info": {
      "query_cost": "12.34",
      "rows_examined_per_scan": 1000,
      "rows_produced_per_join": 100
    },
    "table": {
      "table_name": "employees",
      "access_type": "ref",
      "possible_keys": ["dept_id_index"],
      "key": "dept_id_index",
      "key_length": "4",
      "used_key_parts": ["dept_id"],
      "ref": "const",
      "rows": 50,
      "filtered": "10.00",
      "cost_info": {
        "read_cost": "1.23",
        "eval_cost": "0.45",
        "prefix_cost": "1.68",
        "data_read_per_join": "5000"
      },
      "attached_condition": "(`employees`.`salary` > 50000)"
    }
  }
}

下面我们详细解释各个字段的含义。

3. 关键字段详解

• select_id: 查询块的ID，对于复杂的查询，如包含子查询或UNION，会有多个select_id。
• cost_info: 包含成本估算信息。
  • query_cost: 整个查询的成本。这是一个抽象的单位，用于比较不同执行计划的优劣。
  • rows_examined_per_scan: 每次扫描检查的行数。
  • rows_produced_per_join: 每次连接操作产生的行数。
• table: 描述查询中涉及的表。
  • table_name: 表名。
  • access_type: 访问类型，例如"ref"、"range"、"ALL"等。这是优化SQL的重要指标。
  • possible_keys: 可能使用的索引。
  • key: 实际使用的索引。
  • key_length: 使用的索引长度。
  • used_key_parts: 实际使用的索引字段。
  • ref: 用于索引查找的列或常量。
  • rows: 估计需要扫描的行数。
  • filtered: 经过条件过滤后，估计剩余的行数的百分比。
  • cost_info: 表级别的成本信息。
    • read_cost: 读取数据的成本。
    • eval_cost: 评估条件的成本。
    • prefix_cost: 访问该表之前的累计成本。
    • data_read_per_join: 从表中读取的数据量(字节).
  • attached_condition: 附加的过滤条件，即WHERE子句中没有用于索引查找的条件。

4. access_type 的重要性

access_type 是衡量查询性能的关键指标。以下列出常见的access_type，并按性能从优到劣排序：

access_type	含义	优化建议
system	表中只有一行数据，或使用SYSTEM_USER等系统变量。	通常不需要优化。
const	使用主键或唯一索引进行等值查询，最多返回一行数据。	通常不需要优化。
eq_ref	使用主键或唯一索引进行连接查询，对于连接表中的每一行，最多返回一行数据。	确保连接字段上有索引。
ref	使用非唯一索引进行等值查询，可能返回多行数据。	考虑是否可以使用覆盖索引，减少回表查询。
fulltext	使用全文索引。	确保全文索引配置正确，并合理使用全文搜索语法。
ref_or_null	类似于ref，但会额外搜索NULL值。	检查查询逻辑，看是否可以避免NULL值的搜索。
index_merge	使用多个索引进行查询，并将结果合并。	尽量避免使用index_merge，因为它通常不如使用单个组合索引高效。可以考虑创建一个包含多个查询字段的组合索引。
unique_subquery	IN子查询中使用主键或唯一索引。	尽量使用JOIN代替IN子查询，特别是当子查询返回大量数据时。
index_subquery	IN子查询中使用非唯一索引。	尽量使用JOIN代替IN子查询。
range	使用索引进行范围查询，例如BETWEEN、>、<等。	确保范围查询的条件合理，避免扫描过多数据。
index	扫描整个索引树。通常比ALL快，因为索引通常比数据小。	检查是否可以使用更精确的查询条件，避免全索引扫描。考虑使用覆盖索引。
ALL	扫描整个表。这是最慢的访问类型。	这是最需要优化的。检查是否缺少索引，或者查询条件无法使用索引。

5. 成本估算的内部逻辑

MySQL的成本估算是基于一系列的参数和公式。理解这些参数和公式有助于我们更好地理解查询优化器的工作原理。

• IO 成本： 读取磁盘页面的成本。这取决于存储引擎的实现，以及磁盘的性能。InnoDB存储引擎使用B+树索引，因此IO成本与B+树的高度有关。
• CPU 成本： 评估条件、比较数据、排序等操作的成本。这取决于CPU的性能，以及操作的复杂度。
• 内存成本： 使用内存进行排序、哈希连接等操作的成本。如果内存不足，可能会导致性能下降。
• 网络成本： 在分布式环境下，数据传输的成本。

MySQL使用这些成本参数来计算每个操作的成本，然后选择成本最低的执行计划。

InnoDB 存储引擎的成本模型

InnoDB存储引擎使用基于成本的优化器。成本估算涉及以下几个关键因素：

1. 页面读取成本 (io_block_read_cost): 读取一个磁盘页面的成本。默认值通常较小，例如 1.0。可以通过 optimizer_switch 系统变量控制。

2. 行评估成本 (row_evaluate_cost): 评估一行数据的成本，包括比较和过滤。默认值通常也很小，例如 0.2。

3. 内存临时表成本 (internal_tmp_disk_storage_engine): 如果查询需要创建内存临时表，会增加额外的成本，具体取决于使用的存储引擎。

4. 数据传输成本 (net_cost): 在分布式环境中，数据在网络上传输的成本。

成本计算示例

假设我们有一个查询：

SELECT * FROM orders WHERE customer_id = 123 AND order_date BETWEEN '2023-01-01' AND '2023-01-31';

假设 customer_id 上有索引，order_date 上没有索引。

1. 使用 customer_id 索引:

   • 读取成本: 假设通过索引找到 100 行数据，读取这些行的成本为 100 * io_block_read_cost。
   • 评估成本: 对于每一行数据，需要评估 order_date 是否在指定范围内，成本为 100 * row_evaluate_cost。
   • 总成本: 100 * io_block_read_cost + 100 * row_evaluate_cost。

2. 全表扫描:

   • 读取成本: 假设 orders 表有 10000 行数据，读取整个表的成本为 10000 * io_block_read_cost。
   • 评估成本: 对于每一行数据，需要评估 customer_id 和 order_date 是否满足条件，成本为 10000 * row_evaluate_cost。
   • 总成本: 10000 * io_block_read_cost + 10000 * row_evaluate_cost。

MySQL 优化器会比较这两种方案的成本，选择成本最低的方案。在这个例子中，如果 io_block_read_cost 和 row_evaluate_cost 较小，且通过 customer_id 索引找到的行数较少，那么使用索引的方案可能更优。

6. 如何利用 EXPLAIN FORMAT=JSON 进行优化

• 识别全表扫描: 如果 access_type 为 "ALL"，则需要立即优化，例如添加索引或修改查询条件。
• 检查索引使用情况: 如果 possible_keys 中有索引，但 key 为 NULL，则说明MySQL认为使用索引不如全表扫描。可以尝试强制使用索引（FORCE INDEX）。
• 减少扫描行数: rows 字段表示估计需要扫描的行数。优化目标是尽可能减少这个值。
• 优化连接查询: 对于连接查询，需要关注连接顺序和连接方式。MySQL会尝试不同的连接顺序，选择成本最低的。可以尝试使用STRAIGHT_JOIN强制指定连接顺序。
• 覆盖索引: 如果查询只需要访问索引中的列，而不需要回表查询，则可以使用覆盖索引。覆盖索引可以显著提高查询性能。
• 避免索引合并: 尽量避免使用index_merge，因为它通常不如使用单个组合索引高效。
• 分析 filtered 列: filtered 列表示经过条件过滤后，估计剩余的行数的百分比。如果这个值很小，说明过滤条件很有效。

7. 示例：优化一个慢查询

假设我们有一个查询：

SELECT * FROM products WHERE category_id = 1 AND price BETWEEN 100 AND 200 AND color = 'red';

执行 EXPLAIN FORMAT=JSON 后，发现 access_type 为 "ALL"，rows 很大。

1. 创建索引:

   CREATE INDEX idx_category_price_color ON products (category_id, price, color);

2. 再次执行 EXPLAIN FORMAT=JSON:

   如果 access_type 变为 "range"，rows 显著减少，则说明索引生效了。

3. 分析结果:

   • 检查 key_length，确保索引被充分利用。
   • 检查 filtered，看是否可以进一步优化查询条件。

8. 注意事项

• EXPLAIN 的结果是估计值，并非完全准确。实际执行情况可能有所不同。
• EXPLAIN 只能分析单个查询。对于复杂的存储过程或函数，需要分解成多个查询进行分析。
• MySQL 版本不同，EXPLAIN 的输出格式和字段可能有所差异。
• ANALYZE TABLE 可以更新表的统计信息，从而提高成本估算的准确性。
• optimizer_switch 系统变量可以控制优化器的行为。

9. 深入理解成本参数的调整

虽然通常不建议直接修改MySQL的成本参数，但在某些特殊情况下，调整这些参数可以帮助优化器选择更合适的执行计划。例如，如果你的服务器使用SSD硬盘，可以适当降低io_block_read_cost，以鼓励优化器更多地使用索引。

示例：修改成本参数

-- 查看当前的成本参数
SELECT @@optimizer_switch;

-- 修改io_block_read_cost (不推荐直接修改全局参数，这里仅作演示)
SET GLOBAL innodb_io_capacity = 2000; -- 先调整IO能力
SET GLOBAL innodb_read_io_threads = 16; -- 调整读取线程数
SET GLOBAL innodb_write_io_threads = 16; -- 调整写入线程数
-- 实际调整 io_block_read_cost 比较复杂，通常需要结合具体场景和压测结果

-- 恢复默认值 (如果需要)
-- SET GLOBAL innodb_io_capacity = 200;
-- SET GLOBAL innodb_read_io_threads = 4;
-- SET GLOBAL innodb_write_io_threads = 4;

10. 总结
理解EXPLAIN FORMAT=JSON的输出，能够帮助我们深入理解MySQL的查询执行计划，从而更好地优化SQL查询。关键在于理解各个字段的含义，特别是access_type和成本信息，并结合实际情况进行分析和调整。 优化是一个迭代的过程，需要不断地尝试和验证。

掌握EXPLAIN JSON：深入理解查询优化
通过EXPLAIN JSON，我们可以更深入地了解MySQL查询优化器的决策过程，进而编写出更高效的SQL语句。
成本估算是查询优化的核心，理解成本模型的参数和公式，有助于我们更好地控制优化器的行为。