MySQL优化器与执行计划之：`MySQL`的`CTE`优化：`WITH …`语句的`物化`与`非物化`策略。 - 智猿学院-前后端，数据库，人工智能，云计算等领域前沿技术讲座

MySQL CTE优化：WITH语句的物化与非物化策略

大家好，今天我们来深入探讨MySQL中的Common Table Expressions (CTEs)，特别是WITH语句的物化（Materialization）与非物化（Inlining）策略。 CTE是现代SQL中非常重要的组成部分，能够提高代码的可读性和可维护性，但在性能方面，它也可能成为瓶颈。理解MySQL如何处理CTE，以及如何影响其性能，对于编写高效的SQL查询至关重要。

1. 什么是CTE？

CTE，即Common Table Expression，是一种临时的命名结果集，它定义在一个SQL查询的开始，并在该查询的范围内可用。你可以把CTE想象成一个临时的视图，但它只存在于当前查询的执行过程中。

CTE使用WITH关键字定义，语法如下：

WITH
  cte_name1 AS (
    SELECT column1, column2
    FROM table1
    WHERE condition1
  ),
  cte_name2 AS (
    SELECT column3, column4
    FROM cte_name1
    WHERE condition2
  )
SELECT *
FROM cte_name2
WHERE condition3;

在这个例子中，我们定义了两个CTE：cte_name1和cte_name2。 cte_name2甚至可以引用cte_name1的结果。

CTE的优点：

可读性： 将复杂的查询分解成更小的、逻辑上独立的块。
可维护性： 易于修改和调试。
递归查询： 支持递归查询，用于处理层次结构数据。
避免代码重复： 在单个查询中多次使用相同的子查询逻辑。

2. 物化 vs. 非物化：MySQL如何处理CTE

MySQL处理CTE有两种主要策略：

物化 (Materialization): 将CTE的结果集存储到一个临时表中（通常在内存中，但也可能落盘）。主查询再从这个临时表中读取数据。
非物化 (Inlining): 将CTE的定义合并到主查询中，优化器会像处理普通子查询一样优化整个查询。

选择哪种策略取决于MySQL优化器的判断，通常基于CTE的复杂性、大小以及主查询如何使用CTE的结果。

2.1 物化策略

当MySQL选择物化CTE时，它会执行以下步骤：

执行CTE的查询。
将结果存储在一个临时表中。
主查询从这个临时表中读取数据。

优点：

避免重复计算： 如果CTE在主查询中被多次引用，物化可以避免多次执行CTE的查询。
简化优化： 将CTE的结果物化后，主查询的优化变得更简单，因为优化器只需要考虑从临时表中读取数据。

缺点：

额外的I/O开销： 创建和读取临时表会产生额外的I/O开销，特别是当CTE的结果集很大时。
内存消耗： 将CTE的结果存储在临时表中会消耗内存资源。

2.2 非物化策略 (Inlining)

当MySQL选择非物化CTE时，它会将CTE的定义直接嵌入到主查询中。优化器会像处理普通子查询一样，对整个查询进行优化。

优点：

避免I/O开销： 不需要创建和读取临时表，避免了额外的I/O开销。
更好的优化机会： 优化器可以对整个查询进行全局优化，可能会找到更优的执行计划。

缺点：

重复计算： 如果CTE在主查询中被多次引用，可能会导致重复计算。
优化复杂性： 将CTE的定义嵌入到主查询中会增加查询的复杂性，可能会影响优化器的效率。
可能产生笛卡尔积： 非物化含有join的CTE时，如果优化器处理不当，可能会引入笛卡尔积，导致性能急剧下降。

3. 如何判断MySQL是否物化了CTE？

你可以使用EXPLAIN命令来查看MySQL的执行计划，从而判断CTE是否被物化。

例如，假设我们有以下查询：

WITH
  customer_orders AS (
    SELECT
      customer_id,
      COUNT(*) AS total_orders
    FROM
      orders
    GROUP BY
      customer_id
  )
SELECT
  c.customer_name,
  co.total_orders
FROM
  customers c
JOIN
  customer_orders co ON c.customer_id = co.customer_id
WHERE co.total_orders > 5;

运行 EXPLAIN 命令：

EXPLAIN
WITH
  customer_orders AS (
    SELECT
      customer_id,
      COUNT(*) AS total_orders
    FROM
      orders
    GROUP BY
      customer_id
  )
SELECT
  c.customer_name,
  co.total_orders
FROM
  customers c
JOIN
  customer_orders co ON c.customer_id = co.customer_id
WHERE co.total_orders > 5;

查看 EXPLAIN 的结果，如果看到类似以下的信息，则表示CTE被物化了：

id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	PRIMARY	c	NULL	ALL	PRIMARY	NULL	NULL	NULL	100	100.00	Using where
1	PRIMARY		NULL	ALL	NULL	NULL	NULL	NULL	10	100.00	Using where; Using join buffer (Block Nested Loop)
2	DERIVED	orders	NULL	ALL	NULL	NULL	NULL	NULL	1000	100.00	Using temporary; Using filesort

在上面的例子中，select_type为DERIVED表示该查询是派生表，通常意味着CTE被物化成了一个临时表。 <derived2>表示从第2个查询(id=2)派生的结果集。 Using temporary 和 Using filesort 进一步暗示了物化策略，因为创建临时表通常需要排序。

如果没有看到DERIVED，而是直接看到了CTE的查询计划，则表示CTE被内联了。

4. 影响CTE物化策略的因素

MySQL优化器会综合考虑以下因素来决定是否物化CTE：

CTE的复杂性： 复杂的CTE（例如，包含大量的join、group by、window function）更有可能被物化，以简化主查询的优化。
CTE的大小： 如果优化器认为CTE的结果集会很大，可能会选择不物化，以避免过多的I/O开销。
CTE在主查询中的使用方式： 如果CTE在主查询中被多次引用，优化器更有可能选择物化，以避免重复计算。
MySQL版本和配置： 不同版本的MySQL，以及不同的配置参数，都可能影响CTE的物化策略。例如，optimizer_switch 系统变量可以控制某些优化器的行为，包括CTE的物化。

5. 如何控制CTE的物化策略

从MySQL 8.0.19开始，MySQL提供了一些hint来控制CTE的物化策略。

MATERIALIZE hint: 强制物化CTE。
NO_MATERIALIZE hint: 强制不物化CTE。

5.1 使用 MATERIALIZE hint

WITH
  MATERIALIZE customer_orders AS (
    SELECT
      customer_id,
      COUNT(*) AS total_orders
    FROM
      orders
    GROUP BY
      customer_id
  )
SELECT
  c.customer_name,
  co.total_orders
FROM
  customers c
JOIN
  customer_orders co ON c.customer_id = co.customer_id
WHERE co.total_orders > 5;

使用 MATERIALIZE hint 可以确保CTE customer_orders 被物化。即使优化器原本会选择内联，也会强制执行物化。

5.2 使用 NO_MATERIALIZE hint

WITH
  NO_MATERIALIZE customer_orders AS (
    SELECT
      customer_id,
      COUNT(*) AS total_orders
    FROM
      orders
    GROUP BY
      customer_id
  )
SELECT
  c.customer_name,
  co.total_orders
FROM
  customers c
JOIN
  customer_orders co ON c.customer_id = co.customer_id
WHERE co.total_orders > 5;

使用 NO_MATERIALIZE hint 可以尝试强制不物化CTE customer_orders。但是，请注意，NO_MATERIALIZE 只是一个建议，优化器可能会忽略它。在某些情况下，例如CTE非常复杂或者包含不支持内联的结构，优化器仍然会选择物化。

6. 示例：物化与非物化的性能对比

为了更好地理解物化和非物化对性能的影响，我们来看一个例子。

假设我们有两张表：users 和 orders。 users 表包含用户信息，orders 表包含订单信息。

CREATE TABLE users (
  id INT PRIMARY KEY AUTO_INCREMENT,
  username VARCHAR(255) NOT NULL,
  email VARCHAR(255) NOT NULL,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE orders (
  id INT PRIMARY KEY AUTO_INCREMENT,
  user_id INT NOT NULL,
  product_name VARCHAR(255) NOT NULL,
  order_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (user_id) REFERENCES users(id)
);

-- 插入大量数据
INSERT INTO users (username, email)
SELECT
  CONCAT('user', seq),
  CONCAT('user', seq, '@example.com')
FROM
  seq_1_to_100000;

INSERT INTO orders (user_id, product_name)
SELECT
  FLOOR(RAND() * 100000) + 1,
  CONCAT('Product', FLOOR(RAND() * 100) + 1)
FROM
  seq_1_to_1000000;

现在，我们使用CTE来查询每个用户的订单总数，并筛选出订单总数超过100的用户：

WITH
  user_order_counts AS (
    SELECT
      user_id,
      COUNT(*) AS order_count
    FROM
      orders
    GROUP BY
      user_id
  )
SELECT
  u.username,
  uoc.order_count
FROM
  users u
JOIN
  user_order_counts uoc ON u.id = uoc.user_id
WHERE
  uoc.order_count > 100;

6.1 测试物化策略的性能

WITH
  MATERIALIZE user_order_counts AS (
    SELECT
      user_id,
      COUNT(*) AS order_count
    FROM
      orders
    GROUP BY
      user_id
  )
SELECT
  u.username,
  uoc.order_count
FROM
  users u
JOIN
  user_order_counts uoc ON u.id = uoc.user_id
WHERE
  uoc.order_count > 100;

运行这个查询，并记录执行时间。

6.2 测试非物化策略的性能

WITH
  NO_MATERIALIZE user_order_counts AS (
    SELECT
      user_id,
      COUNT(*) AS order_count
    FROM
      orders
    GROUP BY
      user_id
  )
SELECT
  u.username,
  uoc.order_count
FROM
  users u
JOIN
  user_order_counts uoc ON u.id = uoc.user_id
WHERE
  uoc.order_count > 100;

运行这个查询，并记录执行时间。

6.3 结果分析

在这个例子中，由于user_order_counts CTE的结果集相对较小，并且在主查询中只被引用了一次，所以非物化策略通常会更快。因为避免了创建和读取临时表的开销。

但是，如果user_order_counts CTE非常复杂，或者在主查询中被多次引用，那么物化策略可能会更优。

7. 最佳实践

使用 EXPLAIN 命令分析执行计划： 理解MySQL如何处理你的CTE。
尝试 MATERIALIZE 和 NO_MATERIALIZE hints： 根据 EXPLAIN 的结果，选择合适的物化策略。
避免在CTE中进行不必要的计算： 只选择需要的列，并尽可能地过滤数据。
考虑使用索引： 确保CTE和主查询中使用的表都有合适的索引。
避免在CTE中使用 DISTINCT： 在某些情况下，DISTINCT 会阻止优化器进行内联。
将复杂的查询分解成更小的CTE： 提高可读性和可维护性，并帮助优化器更好地优化查询。
定期更新MySQL版本： 新版本通常包含优化器的改进。

8. 无法内联CTE的情况

虽然NO_MATERIALIZE hint可以尝试强制不物化CTE，但在某些情况下，优化器仍然会选择物化，或者根本无法内联。以下是一些常见的例子：

递归CTE： 递归CTE必须被物化。
包含 LIMIT 子句的CTE： 如果CTE包含 LIMIT 子句，优化器通常会选择物化。
包含 UNION 或 UNION ALL 的CTE： 某些情况下，包含 UNION 或 UNION ALL 的CTE无法被内联。
在存储过程中使用的CTE： 在存储过程中，CTE可能会被物化，具体取决于MySQL版本和配置。
某些复杂的join场景： 优化器如果认为内联会导致性能下降，会选择物化。

CTE策略选择的影响

CTE的物化与非物化策略对查询性能有着直接的影响。正确理解和选择合适的策略，能够显著提高查询效率。使用EXPLAIN命令可以帮助我们了解MySQL如何处理CTE，而MATERIALIZE和NO_MATERIALIZE hints则提供了控制物化行为的手段。结合实际场景进行测试和分析，才能找到最适合的解决方案。

MySQL CTE优化：WITH语句的物化与非物化策略

发表回复 取消回复

发表回复取消回复