MySQL的`explain format=json`：如何分析其`cost`、`rows_examined`与`filtered`指标以精确定位性能瓶颈？

MySQL explain format=json：精确定位性能瓶颈的深入分析

各位同学，大家好！今天我们来深入探讨MySQL的explain format=json，重点关注cost、rows_examined和filtered这三个关键指标，并学习如何利用它们来精确定位SQL查询的性能瓶颈。传统的explain输出信息相对简洁，但当查询变得复杂时，往往难以提供足够的信息来诊断问题。explain format=json提供了更结构化、更详细的查询执行计划，使我们能够更精确地了解MySQL的执行过程，从而更有效地进行优化。

1. explain format=json的基本用法

首先，我们来看一下explain format=json的基本用法。 假设我们有两张表，users和orders：

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

CREATE TABLE `orders` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `user_id` int(11) NOT NULL,
  `order_date` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `total_amount` decimal(10,2) NOT NULL,
  PRIMARY KEY (`id`),
  KEY `idx_user_id` (`user_id`),
  KEY `idx_order_date` (`order_date`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- 插入一些示例数据
INSERT INTO `users` (`username`, `email`) VALUES
('john_doe', '[email protected]'),
('jane_smith', '[email protected]'),
('peter_jones', '[email protected]');

INSERT INTO `orders` (`user_id`, `total_amount`) VALUES
(1, 100.00),
(1, 200.00),
(2, 150.00),
(3, 250.00);

现在，我们执行一个简单的JOIN查询，并使用explain format=json来查看其执行计划：

EXPLAIN FORMAT=JSON
SELECT u.username, o.total_amount
FROM users u
JOIN orders o ON u.id = o.user_id
WHERE u.email = '[email protected]';

执行结果会返回一个JSON格式的字符串。 为了方便查看，我们可以将其格式化。 一些MySQL客户端或在线JSON格式化工具可以帮助我们做到这一点。 这里我提取一些关键字段进行分析：

{
  "query_block": {
    "select_id": 1,
    "cost_info": {
      "query_cost": "1.45",
      "sort_cost": "0.00",
      "rows_examined_per_scan": 1,
      "rows_examined_per_lookup": 3,
      "rows_produced_per_join": 1,
      "read_cost": "0.45",
      "eval_cost": "1.00",
      "prefix_cost": "1.45",
      "data_read_per_join": "16"
    },
    "table": {
      "table_name": "u",
      "access_type": "ref",
      "possible_keys": [
        "PRIMARY",
        "idx_email"
      ],
      "key": "idx_email",
      "used_key_parts": [
        "email"
      ],
      "key_length": "767",
      "ref": [
        "const"
      ],
      "rows": 1,
      "filtered": "100.00",
      "cost_info": {
        "read_cost": "0.25",
        "eval_cost": "0.20",
        "prefix_cost": "0.45",
        "data_read_per_join": "16"
      },
      "attached_condition": "(`test`.`u`.`email` = '[email protected]')"
    },
    "inner_table": {
      "table_name": "o",
      "access_type": "ref",
      "possible_keys": [
        "idx_user_id"
      ],
      "key": "idx_user_id",
      "used_key_parts": [
        "user_id"
      ],
      "key_length": "4",
      "ref": [
        "test.u.id"
      ],
      "rows": 2,
      "filtered": "100.00",
      "cost_info": {
        "read_cost": "0.20",
        "eval_cost": "0.40",
        "prefix_cost": "0.60"
      },
      "attached_condition": "(`test`.`o`.`user_id` = `test`.`u`.`id`)"
    }
  }
}

2. cost：查询成本分析

cost 指标表示 MySQL 估计的查询执行成本。它是一个抽象的单位，用于比较不同执行计划的优劣。 成本越低，通常意味着查询执行速度越快。 在JSON输出中，你会看到多个与成本相关的指标，例如 query_cost、 read_cost、 eval_cost 和 prefix_cost。

• query_cost: 表示整个查询的总成本。
• read_cost: 表示读取数据的成本，例如从磁盘或内存读取数据页。
• eval_cost: 表示评估条件的成本，例如WHERE子句中的条件判断。
• prefix_cost: 表示到目前为止的查询部分的成本。

如何利用 cost 定位瓶颈？

1. 关注 query_cost: 如果 query_cost 很高，则表明整个查询的开销很大。 这时候，我们需要进一步分析，找出造成高成本的具体原因。

2. 比较不同执行计划: 可以尝试不同的查询写法、索引策略，然后使用 explain format=json 比较它们的 query_cost。 选择 query_cost 最低的方案。

3. 分析 read_cost 和 eval_cost:

   • 如果 read_cost 很高，说明查询花费了大量时间在读取数据上。 这通常意味着需要优化索引，减少全表扫描。
   • 如果 eval_cost 很高，说明查询花费了大量时间在评估条件上。 这可能是因为WHERE子句中的条件过于复杂，或者没有合适的索引来支持条件过滤。

示例：优化read_cost

假设我们有一个查询，需要根据 order_date 范围查询订单：

EXPLAIN FORMAT=JSON
SELECT * FROM orders WHERE order_date BETWEEN '2023-01-01' AND '2023-01-31';

如果 explain format=json 的结果显示 read_cost 很高，且 access_type 为 ALL (全表扫描)， 那么我们可以考虑在 order_date 列上创建索引：

CREATE INDEX idx_order_date ON orders (order_date);

创建索引后，再次执行 explain format=json， 观察 read_cost 是否降低， access_type 是否变成了 range 或其他更优的类型。

示例：优化eval_cost

假设我们有一个查询，WHERE条件包含多个OR操作：

EXPLAIN FORMAT=JSON
SELECT * FROM users WHERE username = 'john_doe' OR email = '[email protected]';

如果 explain format=json 的结果显示 eval_cost 很高， 可以尝试将OR条件拆分成多个UNION ALL 查询：

SELECT * FROM users WHERE username = 'john_doe'
UNION ALL
SELECT * FROM users WHERE email = '[email protected]';

然后分别 explain format=json 这两个查询，并比较总的成本。 在某些情况下，UNION ALL 可能会比使用OR更有效率。

3. rows_examined：扫描行数分析

rows_examined 指标表示 MySQL 为了执行查询而扫描的行数。这是一个非常重要的指标， 因为扫描的行数越多，查询的性能通常越差。 在JSON输出中，你会看到 rows (估计的返回行数) 和 rows_examined_per_scan (每次扫描检查的行数) 等相关指标。

如何利用 rows_examined 定位瓶颈？

1. 理想情况：rows_examined 接近 rows: 理想情况下，MySQL 只需要扫描很少的行就能找到满足条件的记录。 这通常意味着查询使用了合适的索引。

2. rows_examined 远大于 rows: 如果 rows_examined 远大于 rows， 说明查询扫描了大量的行，但只返回了很少的行。 这通常意味着查询没有使用合适的索引，或者索引的选择性不高。

3. 全表扫描 (access_type 为 ALL): 如果 explain format=json 的结果显示 access_type 为 ALL， 且 rows_examined 等于表中的总行数， 说明查询进行了全表扫描。 这通常是性能问题的一个重要信号，需要立即优化。

示例：优化 rows_examined

假设我们有一个查询，需要根据 username 查询用户：

EXPLAIN FORMAT=JSON
SELECT * FROM users WHERE username = 'john_doe';

如果 explain format=json 的结果显示 access_type 为 ALL， 且 rows_examined 等于 users 表的总行数， 那么我们可以考虑在 username 列上创建索引。 但我们已经创建了唯一索引username，为什么还会全表扫描呢？这是因为查询优化器可能认为全表扫描比使用索引更快（例如，表很小）。 我们可以尝试强制使用索引：

EXPLAIN FORMAT=JSON
SELECT * FROM users USE INDEX (username) WHERE username = 'john_doe';

或者

ANALYZE TABLE users; -- 更新表统计信息，帮助优化器做出更明智的选择
EXPLAIN FORMAT=JSON
SELECT * FROM users WHERE username = 'john_doe';

更新统计信息后，MySQL 优化器可能会选择使用索引，从而降低 rows_examined。

4. filtered：过滤比例分析

filtered 指标表示 MySQL 估计的经过条件过滤后， 剩余的行数比例。 它是一个百分比值，表示有多少行满足WHERE子句中的条件。

如何利用 filtered 定位瓶颈？

1. filtered 接近 100%: 如果 filtered 接近 100%， 说明 WHERE 子句中的条件非常有效，几乎所有的扫描行都满足条件。

2. filtered 很低: 如果 filtered 很低， 说明 WHERE 子句中的条件过滤性不强， 大部分扫描行都被过滤掉了。 这通常意味着 WHERE 子句中的条件不够具体，或者没有合适的索引来支持条件过滤。

3. 结合 rows_examined 和 filtered: filtered 指标需要结合 rows_examined 一起分析。 如果 rows_examined 很高，但 filtered 很低， 说明查询扫描了大量的行，但大部分都被过滤掉了。 这通常意味着需要优化 WHERE 子句中的条件，或者创建更合适的索引。

示例：优化 filtered

假设我们有一个查询，需要根据 created_at 范围查询用户：

EXPLAIN FORMAT=JSON
SELECT * FROM users WHERE created_at BETWEEN '2023-01-01' AND '2023-01-31';

如果 explain format=json 的结果显示 filtered 很低，且 rows_examined 很高， 那么我们可以考虑在 created_at 列上创建索引：

CREATE INDEX idx_created_at ON users (created_at);

创建索引后，再次执行 explain format=json，观察 filtered 是否提高，rows_examined 是否降低。 如果 created_at 列的选择性不高（例如，大部分用户的 created_at 都在这个范围内）， 即使创建了索引，filtered 也可能不会有显著的提高。 这时候，我们需要考虑优化 WHERE 子句中的条件，或者结合其他条件进行过滤。

示例：JOIN操作中的 filtered

在JOIN操作中，filtered 指标可以帮助我们了解连接条件的过滤效果。 例如，在前面的 users 和 orders 的JOIN查询中， orders 表的 filtered 表示有多少 orders 行匹配 users 表的 id。 如果 orders 表的 filtered 很低，说明连接条件 u.id = o.user_id 的过滤效果不佳， 大部分 orders 行都无法找到匹配的 users 行。 这可能是由于数据不一致导致的，例如 orders 表中存在 user_id 在 users 表中不存在的记录。

5. 实战案例：复杂查询优化

现在，我们来看一个更复杂的案例，演示如何综合利用 cost、rows_examined 和 filtered 来优化查询。

假设我们有一个需求：查询所有在2023年1月下过订单的用户名和订单总金额，并且用户邮箱包含 "example.com"。

EXPLAIN FORMAT=JSON
SELECT u.username, SUM(o.total_amount) AS total_spent
FROM users u
JOIN orders o ON u.id = o.user_id
WHERE o.order_date BETWEEN '2023-01-01' AND '2023-01-31'
AND u.email LIKE '%example.com%'
GROUP BY u.username;

通过 explain format=json 分析执行计划，我们可能会发现以下问题：

• users 表使用了全表扫描，rows_examined 很高。
• orders 表的 filtered 很低。
• query_cost 很高。

针对这些问题，我们可以采取以下优化措施：

1. 优化 users 表:

   • 在 email 列上创建索引，以支持 LIKE 查询。 注意，前缀匹配 (email LIKE 'example.com%') 可以有效利用索引， 但后缀匹配 (email LIKE '%example.com') 或中缀匹配 (email LIKE '%example.com%') 通常无法有效利用索引。 因此，我们需要考虑使用全文索引，或者优化查询逻辑。
   • 如果 email 列的选择性不高，可以考虑结合其他条件进行过滤，或者调整索引策略。

2. 优化 orders 表:

   • 确保 order_date 列上有索引，并且 MySQL 优化器选择了使用该索引。
   • 检查 orders 表中是否存在大量不符合 user_id 的数据，如果存在，需要清理或修复数据。

3. 优化 JOIN 操作:

   • 确保 users 和 orders 表的连接条件 u.id = o.user_id 使用了索引。
   • 考虑调整 JOIN 的顺序，例如，先过滤 orders 表，再连接 users 表。

优化后的查询可能如下所示：

CREATE INDEX idx_email ON users (email(20)); -- 创建 email 列的前缀索引
CREATE INDEX idx_order_date ON orders (order_date);

EXPLAIN FORMAT=JSON
SELECT u.username, SUM(o.total_amount) AS total_spent
FROM (SELECT * FROM orders WHERE order_date BETWEEN '2023-01-01' AND '2023-01-31') AS o -- 子查询先过滤 orders 表
JOIN users u ON u.id = o.user_id
WHERE u.email LIKE '%example.com%'
GROUP BY u.username;

通过不断地分析 explain format=json 的结果，并采取相应的优化措施，我们可以逐步提高查询的性能。

6. 总结：理解指标，持续优化

今天，我们深入学习了 MySQL explain format=json 的用法，并重点关注了 cost、rows_examined 和 filtered 这三个关键指标。理解这些指标的含义，并结合实际案例进行分析，可以帮助我们精确定位 SQL 查询的性能瓶颈，从而更有效地进行优化。记住，优化是一个持续的过程，需要不断地学习和实践。

掌握 cost、rows_examined和 filtered指标，可以帮助开发者理解MySQL查询执行计划，并根据这些信息对SQL语句和索引进行优化。持续学习和实践是提高数据库性能的关键。