`EXPLAIN FORMAT=JSON`的`详细`解析：`成本`、`过滤器`和`索引`信息的`解读`。

EXPLAIN FORMAT=JSON 的深入解析：成本、过滤器与索引

大家好，今天我们来深入探讨 MySQL 中 EXPLAIN FORMAT=JSON 的输出，重点关注其中的 cost、filter 和 index 相关信息。理解这些信息对于优化 SQL 查询至关重要，能够帮助我们识别性能瓶颈并选择合适的索引策略。

1. EXPLAIN 基础回顾

EXPLAIN 语句是 MySQL 提供的一个强大的工具，用于分析 SQL 查询的执行计划。它可以帮助我们了解 MySQL 优化器是如何处理我们的查询，以及查询的执行顺序、使用的索引等信息。EXPLAIN FORMAT=JSON 提供了一种结构化的、更详细的输出格式，相比于传统的 EXPLAIN 输出，它包含更多的信息，更方便程序解析。

2. EXPLAIN FORMAT=JSON 的结构

EXPLAIN FORMAT=JSON 的输出是一个 JSON 对象，包含了查询执行计划的详细信息。 它的主要结构如下：

{
  "query_block": {
    "select_id": 1,
    "cost_info": {
      "query_cost": "4.22",
      "sort_cost": "0.00",
      "rows_cost": "4.22"
    },
    "table": {
      "table_name": "employees",
      "access_type": "ALL",
      "possible_keys": null,
      "key": null,
      "used_key_parts": null,
      "key_length": "0",
      "ref": null,
      "rows": 10,
      "filtered": "10.00",
      "cost_info": {
        "read_cost": "1.00",
        "eval_cost": "3.22",
        "prefix_cost": "4.22",
        "data_read_per_join": "1K"
      },
      "attached_condition": "(`employees`.`salary` > 50000)"
    }
  }
}

这个 JSON 结构可以嵌套，例如，一个复杂的查询可能包含多个 query_block，每个 query_block 代表查询中的一个部分，例如子查询或 UNION。

3. cost 详解

cost 是衡量查询执行复杂度的重要指标。MySQL 优化器使用 cost 来评估不同的执行计划，并选择 cost 最低的计划。 在 EXPLAIN FORMAT=JSON 的输出中，cost 信息出现在多个地方，包括 query_block 和 table 对象中。

• query_cost: 表示整个查询块的总成本。它包括读取数据、评估条件、排序等操作的成本。
• sort_cost: 表示排序操作的成本。如果查询需要排序，sort_cost 会反映排序的复杂程度。
• rows_cost: 表示扫描行数的成本。这个成本与扫描的行数和每行的读取成本有关。
• read_cost: 表示读取数据的成本。它反映了从磁盘或内存读取数据的开销。
• eval_cost: 表示评估条件的成本。它反映了评估 WHERE 子句中条件的开销。
• prefix_cost: 表示访问到当前表之前的总成本。在多表连接查询中，它可以帮助我们了解连接顺序对成本的影响。
• data_read_per_join: 表示每次连接读取的数据量。它可以帮助我们评估连接操作的效率。

示例：

假设我们有以下的 EXPLAIN FORMAT=JSON 输出片段：

"cost_info": {
  "query_cost": "10.50",
  "sort_cost": "0.00",
  "rows_cost": "10.50"
},
"table": {
  "table_name": "orders",
  "cost_info": {
    "read_cost": "2.00",
    "eval_cost": "8.50",
    "prefix_cost": "10.50",
    "data_read_per_join": "2K"
  }
}

在这个例子中，query_cost 为 10.50，表示整个查询块的总成本。read_cost 为 2.00，表示读取 orders 表的成本。eval_cost 为 8.50，表示评估 WHERE 子句中条件的成本。prefix_cost 为 10.50，表示访问到 orders 表之前的总成本（如果这是第一个访问的表，则等于该表的总成本）。

如何利用 cost 信息进行优化：

• 减少 read_cost: 通过使用索引来减少需要扫描的行数，从而降低 read_cost。
• 减少 eval_cost: 优化 WHERE 子句中的条件，避免复杂的表达式或函数调用，从而降低 eval_cost。
• 优化连接顺序: 在多表连接查询中，调整连接顺序可以显著影响 prefix_cost，从而影响整个查询的性能。通常，将数据量较小的表放在连接顺序的前面可以提高性能。

4. filter 详解

filtered 属性表示经过 WHERE 子句过滤后，表中剩余的行数的百分比。它是一个介于 0 和 100 之间的值。filtered 值越高，表示 WHERE 子句的过滤效果越好，需要扫描的行数越少，查询效率越高。

示例：

假设我们有以下的 EXPLAIN FORMAT=JSON 输出片段：

"table": {
  "table_name": "customers",
  "rows": 1000,
  "filtered": "10.00",
  "attached_condition": "(`customers`.`city` = 'New York')"
}

在这个例子中，rows 为 1000，表示 customers 表总共有 1000 行。filtered 为 10.00，表示经过 WHERE 子句 (customers.city= 'New York') 过滤后，表中剩余的行数约为 1000 * 10% = 100 行。

如何利用 filtered 信息进行优化：

• 提高 filtered 值: 优化 WHERE 子句，使其能够更有效地过滤数据。例如，可以使用更精确的条件，或者添加更多的条件。
• 使用索引: 如果 WHERE 子句中使用了索引，可以提高 filtered 值，因为索引可以帮助 MySQL 快速定位到符合条件的行，而无需扫描整个表。
• 考虑复合索引: 如果有多个条件需要过滤，可以考虑使用复合索引，将多个列包含在同一个索引中，以提高过滤效率。

attached_condition: 这个属性显示了附加到表上的条件。它可以帮助我们理解 MySQL 优化器是如何使用索引和过滤数据的。

5. index 详解

索引是提高查询性能的关键。EXPLAIN FORMAT=JSON 的输出中包含了大量的索引相关信息，可以帮助我们了解 MySQL 优化器是如何使用索引的。

• possible_keys: 表示 MySQL 优化器在执行查询时可能使用的索引列表。如果 possible_keys 为 null，表示没有可用的索引。
• key: 表示 MySQL 优化器实际选择使用的索引。如果 key 为 null，表示没有使用索引。
• used_key_parts: 表示实际使用的索引列。对于复合索引，它会显示使用了哪些列。
• key_length: 表示使用的索引的长度。它可以帮助我们了解 MySQL 优化器使用了索引的多少个前缀列。
• ref: 表示与索引列进行比较的列或常量。它可以帮助我们了解 MySQL 优化器是如何使用索引进行查找的。
• access_type: 表示 MySQL 优化器访问表的方式。常见的 access_type 包括：
  • system: 表只有一行记录，通常是系统表。
  • const: 使用唯一索引或主键查找，只返回一行记录。
  • eq_ref: 在连接查询中使用唯一索引或主键查找，只返回一行记录。
  • ref: 使用非唯一索引查找，返回多行记录。
  • range: 使用索引范围查找，例如 BETWEEN 或 >。
  • index: 扫描整个索引树。
  • ALL: 扫描整个表。

示例：

假设我们有以下的 EXPLAIN FORMAT=JSON 输出片段：

"table": {
  "table_name": "products",
  "access_type": "ref",
  "possible_keys": [
    "idx_category_id",
    "idx_price"
  ],
  "key": "idx_category_id",
  "used_key_parts": [
    "category_id"
  ],
  "key_length": "4",
  "ref": "const",
  "rows": 100,
  "filtered": "50.00",
  "attached_condition": "(`products`.`category_id` = 1)"
}

在这个例子中，possible_keys 为 ["idx_category_id", "idx_price"]，表示 MySQL 优化器可以选择使用 idx_category_id 或 idx_price 索引。key 为 idx_category_id，表示 MySQL 优化器实际选择了使用 idx_category_id 索引。used_key_parts 为 ["category_id"]，表示使用了 idx_category_id 索引的 category_id 列。key_length 为 4，表示索引的长度为 4 个字节。ref 为 const，表示与索引列进行比较的是一个常量。access_type 为 ref，表示使用非唯一索引查找。

如何利用 index 信息进行优化：

• 确保使用索引: 如果 key 为 null，表示没有使用索引，需要考虑添加索引或优化查询条件，使其能够使用索引。
• 选择合适的索引: 如果 possible_keys 中有多个索引，MySQL 优化器可能会选择错误的索引。可以使用 FORCE INDEX 提示来强制 MySQL 使用特定的索引。
• 优化索引列的顺序: 对于复合索引，索引列的顺序非常重要。应该将最常用的列放在索引的前面。
• 避免索引失效: 避免在 WHERE 子句中使用函数或表达式，这些操作可能会导致索引失效。
• 减少 key_length: 如果只需要使用索引的一部分列，可以考虑使用前缀索引，以减少索引的长度，提高查询效率。

6. 案例分析

假设我们有以下表结构：

CREATE TABLE `employees` (
  `id` INT PRIMARY KEY AUTO_INCREMENT,
  `name` VARCHAR(255) NOT NULL,
  `age` INT NOT NULL,
  `salary` DECIMAL(10, 2) NOT NULL,
  `department_id` INT NOT NULL,
  INDEX `idx_department_id` (`department_id`),
  INDEX `idx_age_salary` (`age`, `salary`)
);

CREATE TABLE `departments` (
  `id` INT PRIMARY KEY AUTO_INCREMENT,
  `name` VARCHAR(255) NOT NULL
);

现在我们执行以下查询：

SELECT e.name, d.name
FROM employees e
JOIN departments d ON e.department_id = d.id
WHERE e.age > 30 AND e.salary > 50000;

使用 EXPLAIN FORMAT=JSON 分析该查询：

{
  "query_block": {
    "select_id": 1,
    "cost_info": {
      "query_cost": "2.70",
      "sort_cost": "0.00",
      "rows_cost": "2.70"
    },
    "nested_loop": [
      {
        "table": {
          "table_name": "e",
          "access_type": "range",
          "possible_keys": [
            "idx_age_salary"
          ],
          "key": "idx_age_salary",
          "used_key_parts": [
            "age"
          ],
          "key_length": "4",
          "ref": null,
          "rows": 5,
          "filtered": "100.00",
          "cost_info": {
            "read_cost": "1.00",
            "eval_cost": "1.00",
            "prefix_cost": "2.00",
            "data_read_per_join": "2K"
          },
          "attached_condition": "(`e`.`age` > 30)"
        }
      },
      {
        "table": {
          "table_name": "d",
          "access_type": "eq_ref",
          "possible_keys": [
            "PRIMARY"
          ],
          "key": "PRIMARY",
          "used_key_parts": [
            "id"
          ],
          "key_length": "4",
          "ref": [
            "test.e.department_id"
          ],
          "rows": 1,
          "filtered": "100.00",
          "cost_info": {
            "read_cost": "0.25",
            "eval_cost": "0.45",
            "prefix_cost": "2.70",
            "data_read_per_join": "1K"
          },
          "attached_condition": "(`e`.`department_id` = `d`.`id`)"
        }
      }
    ]
  }
}

分析：

• employees 表使用了 idx_age_salary 索引，access_type 为 range，表示使用了范围查找。但是，只使用了索引的第一列 age，因为 attached_condition 只包含 e.age > 30。
• departments 表使用了主键索引，access_type 为 eq_ref，表示在连接查询中使用唯一索引查找。

优化：

为了充分利用 idx_age_salary 索引，可以将查询条件修改为：

SELECT e.name, d.name
FROM employees e
JOIN departments d ON e.department_id = d.id
WHERE e.age > 30 AND e.salary > 50000 AND e.age < 60; -- 添加 age 的上限

或者，创建一个新的索引，包含 age 和 salary 列：

ALTER TABLE `employees` ADD INDEX `idx_age_salary_combined` (`age`, `salary`);

这将允许 MySQL 优化器使用索引来过滤 age 和 salary，从而提高查询性能。

7. 总结

通过深入了解 EXPLAIN FORMAT=JSON 的输出，特别是 cost、filter 和 index 相关的信息，我们可以更好地理解 MySQL 优化器是如何执行我们的查询，并识别性能瓶颈。 合理地利用索引、优化查询条件和调整连接顺序，可以显著提高 SQL 查询的性能。 持续地学习和实践才能不断提升SQL优化的技能。