MySQL高阶讲座之：`MySQL`的`CTE`：其在递归查询和复杂`SQL`中的性能考量。 - 智猿学院-前后端，数据库，人工智能，云计算等领域前沿技术讲座

各位观众老爷，大家好！今天咱们来聊聊MySQL里的一个好东西，叫做CTE（Common Table Expression），也就是“公共表表达式”。这玩意儿，说白了，就是个临时表，但它威力可大了，能帮你搞定递归查询，简化复杂SQL，甚至还能提升性能，当然，也可能适得其反。今天咱们就来好好剖析一下。

第一部分：CTE是什么？能吃吗？

首先，咱们得明白CTE是啥。简单来说，CTE就是一个临时的结果集，你可以在一个SQL语句中定义它，然后在同一个语句中多次使用它。它只在当前查询中有效，查询结束后就消失了，就像灰姑娘的魔法一样。

语法长这样：

WITH CTE_Name AS (
    -- 定义CTE的SQL语句
    SELECT column1, column2
    FROM table1
    WHERE condition
)
-- 在主查询中使用CTE
SELECT column1, column2
FROM CTE_Name
WHERE condition2;

WITH关键字是CTE的标志，CTE_Name是你给这个临时表起的名字，后面括号里的SELECT语句就是定义这个临时表的。然后，在后面的SELECT语句中，你就可以像使用普通表一样使用CTE_Name了。

举个例子，假设我们有个employees表，包含id, name, manager_id字段，表示员工的ID，姓名和上级ID。现在想找出所有直接或间接向ID为1的员工汇报的员工的名字。

不用CTE的话，可能得写一堆复杂的JOIN，看得人眼花缭乱。但是有了CTE，我们可以这样：

WITH RECURSIVE subordinate AS (
    SELECT id, name, manager_id
    FROM employees
    WHERE manager_id = 1
    UNION ALL
    SELECT e.id, e.name, e.manager_id
    FROM employees e
    INNER JOIN subordinate s ON e.manager_id = s.id
)
SELECT name
FROM subordinate;

这段代码里，我们定义了一个递归CTE，名字叫subordinate。

第一部分（SELECT id, name, manager_id FROM employees WHERE manager_id = 1）： 这部分是递归的起点，它找出所有直接向ID为1的员工汇报的员工。
UNION ALL： 这个关键字将两个SELECT语句的结果合并起来。
第二部分（SELECT e.id, e.name, e.manager_id FROM employees e INNER JOIN subordinate s ON e.manager_id = s.id）： 这部分是递归的部分，它找出所有直接向subordinate表里的员工汇报的员工。也就是说，它会一层一层地往下找，直到找到所有间接汇报的员工。
RECURSIVE关键字: 这个关键字是MySQL识别这个CTE为递归CTE的关键.

最后，我们从subordinate表里选出所有员工的名字。

第二部分：递归查询：CTE的拿手好戏

刚才那个例子已经展示了CTE在递归查询方面的威力。递归查询是指查询需要重复执行，直到满足某个条件为止。在处理树形结构或者层级结构的数据时，递归查询非常有用。

再举个例子，假设我们有个category表，包含id, name, parent_id字段，表示分类的ID，名称和父分类ID。现在想找出ID为3的分类的所有子分类。

WITH RECURSIVE subcategories AS (
    SELECT id, name, parent_id
    FROM category
    WHERE id = 3
    UNION ALL
    SELECT c.id, c.name, c.parent_id
    FROM category c
    INNER JOIN subcategories s ON c.parent_id = s.id
)
SELECT id, name
FROM subcategories;

这段代码和之前的例子非常相似。第一部分找出ID为3的分类，第二部分递归地找出它的所有子分类。

注意： 在编写递归CTE时，一定要小心，确保递归能够终止，否则可能会陷入无限循环，导致数据库崩溃。MySQL 8.0 默认限制了递归深度为 100。你可以通过设置cte_max_recursion_depth变量来修改这个限制，但是要谨慎，不要设置得太大。

第三部分：复杂SQL？CTE来帮你解围

除了递归查询，CTE还可以用来简化复杂的SQL语句。通过将复杂的查询分解成多个小的CTE，可以提高SQL语句的可读性和可维护性。

假设我们要查询每个部门工资最高的员工的信息。没有CTE的话，可能得写一个嵌套的SELECT语句，像俄罗斯套娃一样，让人头晕眼花。但是有了CTE，我们可以这样：

WITH dept_max_salary AS (
    SELECT department_id, MAX(salary) AS max_salary
    FROM employees
    GROUP BY department_id
)
SELECT e.id, e.name, e.salary, e.department_id
FROM employees e
INNER JOIN dept_max_salary dms ON e.department_id = dms.department_id AND e.salary = dms.max_salary;

这段代码里，我们先定义了一个CTE dept_max_salary，用于找出每个部门的最高工资。然后，在主查询中，我们将employees表和dept_max_salary表连接起来，找出所有工资等于所在部门最高工资的员工。

第四部分：性能考量：双刃剑

CTE虽然好用，但是也要注意它的性能。CTE的性能取决于MySQL如何执行它。在某些情况下，MySQL会将CTE物化（Materialize），也就是将CTE的结果集存储在一个临时表中。这可能会导致性能下降，特别是当CTE的结果集很大时。

但是，在某些情况下，MySQL也可以优化CTE，避免物化。例如，如果CTE只被使用一次，并且可以合并到主查询中，MySQL可能会选择不物化CTE。

为了更好地理解CTE的性能，我们可以使用EXPLAIN命令来查看MySQL的执行计划。EXPLAIN命令会告诉我们MySQL如何执行查询，包括是否使用了临时表，是否使用了索引等等。

举个例子：

EXPLAIN
WITH CTE_Name AS (
    SELECT column1, column2
    FROM table1
    WHERE condition
)
SELECT column1, column2
FROM CTE_Name
WHERE condition2;

执行EXPLAIN命令后，MySQL会返回一个表格，包含很多信息，比如id, select_type, table, type, possible_keys, key, key_len, ref, rows, Extra等等。

select_type: 这一列可以告诉我们查询的类型。如果CTE被物化，select_type可能会显示DERIVED或MATERIALIZED。
Extra: 这一列包含一些额外的信息，比如是否使用了临时表，是否使用了索引等等。

通过分析EXPLAIN的结果，我们可以判断CTE是否影响了性能，并采取相应的措施。

一些优化CTE性能的建议：

*尽量避免在CTE中使用`SELECT `：** 只选择需要的列，可以减少CTE的结果集大小，提高性能。
在CTE中使用索引： 如果CTE的结果集很大，并且需要进行过滤或排序，可以考虑在CTE中使用索引。
考虑使用临时表： 如果CTE被多次使用，并且结果集很大，可以考虑将其存储在一个真正的临时表中，而不是使用CTE。当然，使用临时表会增加代码的复杂性，需要权衡。
确保递归 CTE 有终止条件: 无限递归会耗尽资源并导致查询失败。
利用物化提示: 从 MySQL 8.0.19 开始，你可以使用 MATERIALIZED 和 NO_MATERIALIZED 提示来强制或阻止 CTE 物化。例如：
```
WITH MATERIALIZED my_cte AS (
    SELECT * FROM my_table WHERE some_condition
)
SELECT * FROM my_cte;
```
或者：
```
WITH NO_MATERIALIZED my_cte AS (
    SELECT * FROM my_table WHERE some_condition
)
SELECT * FROM my_cte;
```
这些提示可以帮助你根据具体情况微调性能。

第五部分：CTE vs 临时表：选哪个？

既然CTE和临时表都可以存储临时结果集，那我们应该选择哪个呢？

特性	CTE	临时表
作用域	当前查询	当前会话
持久性	临时，查询结束后消失	临时，会话结束后消失
语法	`WITH ...`	`CREATE TEMPORARY TABLE ...`
使用场景	简单，只在当前查询中使用	复杂，需要在多个查询中使用，或者需要存储大量数据
性能	可能被优化，也可能被物化，取决于MySQL	可以手动控制索引，优化性能

总的来说，如果只是需要在当前查询中使用一个临时的结果集，并且结果集不大，那么CTE是一个不错的选择。如果需要在多个查询中使用临时的结果集，或者结果集很大，需要进行复杂的索引和优化，那么临时表可能更适合。

第六部分：一些高级用法和注意事项

多重CTE： 你可以在一个查询中定义多个CTE，用逗号分隔：

WITH
    cte1 AS (SELECT ...),
    cte2 AS (SELECT ... FROM cte1 WHERE ...)
SELECT ... FROM cte2;

更新CTE： 虽然CTE主要用于查询，但在某些情况下，你也可以使用CTE来更新数据。 MySQL 8.0 允许使用 CTE 进行 UPDATE、DELETE 和 INSERT 操作，但有一些限制。例如，你不能使用递归 CTE 来更新数据。
注意命名冲突： CTE的名字不能和表名或者其他CTE的名字冲突。
可读性至上： 虽然CTE可以简化复杂的SQL语句，但是也要注意代码的可读性。不要为了使用CTE而滥用CTE，否则可能会适得其反。

第七部分：总结

今天我们聊了MySQL的CTE，包括它的定义、语法、用途、性能和一些高级用法。希望大家能够掌握CTE这个强大的工具，在实际工作中灵活运用，写出更高效、更优雅的SQL代码。

记住，CTE就像一把瑞士军刀，功能强大，但是也要谨慎使用，才能发挥它的最大威力。

好了，今天的讲座就到这里。谢谢大家！

发表回复 取消回复

发表回复取消回复