MySQL高级讲座篇之：MySQL 8.0的`CTE`优化：如何避免重复计算和提高查询性能？ - 智猿学院-前后端，数据库，人工智能，云计算等领域前沿技术讲座

各位观众老爷，早上好！我是今天的主讲人，咱们今天聊聊MySQL 8.0里面一个挺有意思的东西，就是Common Table Expression，也就是CTE，俗称“公用表表达式”。这玩意儿，说白了，就是给一段SQL查询起个名字，然后你可以在其他地方引用它。听起来好像没啥了不起，但用好了，它能帮你避免重复计算，还能让你的SQL看起来更漂亮，性能蹭蹭往上涨！

咱们今天就来好好扒一扒，看看这玩意儿到底怎么用，怎么优化，以及怎么避开那些坑。

第一部分：啥是CTE？为啥要用它？

首先，啥是CTE？简单来说，CTE就是一个临时结果集，你可以把它想象成一个临时表，只不过这个表只在当前的查询语句里有效。

语法长这样：

WITH cte_name AS (
    SELECT column1, column2
    FROM table1
    WHERE condition
)
SELECT column1, column2
FROM cte_name
WHERE another_condition;

WITH cte_name AS (...)：这部分定义了CTE，cte_name 是你给这个CTE起的名字。
(SELECT ...)：括号里就是你的查询语句，也就是CTE要返回的结果集。
SELECT ... FROM cte_name ...：这一部分就是使用CTE的地方，你可以像使用普通表一样使用 cte_name。

那为啥要用它呢？主要有这么几个好处：

代码可读性更高： 复杂的SQL拆分成小块，每个CTE负责一部分逻辑，更容易理解。
避免重复计算： 一个复杂的计算过程，如果需要多次使用，可以放在CTE里，避免重复执行。
简化递归查询： CTE支持递归，可以用来处理层级结构的数据，比如组织架构、商品分类等等。
某些情况下，提高性能： 虽然不是绝对的，但合理使用CTE，可以优化查询执行计划，提高性能。

第二部分：CTE的几种常见用法

咱们先来看看CTE的几种常见用法，通过例子来理解它。

1. 简化复杂查询

假设我们要查询每个部门工资最高的员工信息。不用CTE的话，可能要写成这样：

SELECT
    e.employee_id,
    e.employee_name,
    e.salary,
    d.department_name
FROM
    employees e
JOIN
    departments d ON e.department_id = d.department_id
WHERE
    (e.department_id, e.salary) IN (
        SELECT
            department_id,
            MAX(salary)
        FROM
            employees
        GROUP BY
            department_id
    );

看起来是不是有点绕？用CTE可以这样写：

WITH max_salaries AS (
    SELECT
        department_id,
        MAX(salary) AS max_salary
    FROM
        employees
    GROUP BY
        department_id
)
SELECT
    e.employee_id,
    e.employee_name,
    e.salary,
    d.department_name
FROM
    employees e
JOIN
    departments d ON e.department_id = d.department_id
JOIN
    max_salaries ms ON e.department_id = ms.department_id AND e.salary = ms.max_salary;

max_salaries 这个CTE负责计算每个部门的最高工资，然后在外面的查询里直接引用它。是不是清晰多了？

2. 避免重复计算

假设我们要查询订单总金额大于平均订单总金额的客户信息。不用CTE的话，可能要这样写：

SELECT
    c.customer_id,
    c.customer_name,
    SUM(o.order_total) AS total_order_amount
FROM
    customers c
JOIN
    orders o ON c.customer_id = o.customer_id
GROUP BY
    c.customer_id, c.customer_name
HAVING
    SUM(o.order_total) > (SELECT AVG(order_total) FROM orders); -- 这里计算了平均订单总金额

平均订单总金额在HAVING子句里被计算了一次。用CTE可以这样写：

WITH avg_order_amount AS (
    SELECT AVG(order_total) AS avg_amount FROM orders
)
SELECT
    c.customer_id,
    c.customer_name,
    SUM(o.order_total) AS total_order_amount
FROM
    customers c
JOIN
    orders o ON c.customer_id = o.customer_id
GROUP BY
    c.customer_id, c.customer_name
HAVING
    SUM(o.order_total) > (SELECT avg_amount FROM avg_order_amount); -- 直接引用CTE的结果

avg_order_amount 这个CTE只计算一次平均订单总金额，避免了重复计算。

3. 递归查询

这是CTE最强大的功能之一。假设我们有一个组织架构表 employees，包含 employee_id, employee_name, manager_id 三个字段，manager_id 指向员工的直接上级。我们要查询某个员工的所有下属。

WITH RECURSIVE subordinates AS (
    SELECT
        employee_id,
        employee_name,
        manager_id
    FROM
        employees
    WHERE
        employee_id = 1 -- 假设要查询 employee_id 为 1 的员工的所有下属

    UNION ALL

    SELECT
        e.employee_id,
        e.employee_name,
        e.manager_id
    FROM
        employees e
    JOIN
        subordinates s ON e.manager_id = s.employee_id
)
SELECT * FROM subordinates;

WITH RECURSIVE subordinates AS (...)： RECURSIVE 关键字表示这是一个递归CTE。
第一个 SELECT 语句是基础情况，它选择了 employee_id 为 1 的员工信息。
第二个 SELECT 语句是递归步骤，它连接了 employees 表和 subordinates CTE，找到了所有直接下属。
UNION ALL 将基础情况和递归步骤的结果合并在一起。

这个查询会一直递归下去，直到找不到更多的下属为止。

第三部分：CTE优化：避免重复计算和提高查询性能

虽然CTE有很多优点，但用不好也会踩坑。最常见的问题就是性能问题。

1. 物化（Materialization） vs. 内联（Inlining）

这是CTE性能优化的核心概念。

物化： MySQL 把 CTE 的结果集存储到一个临时表中，然后在后续的查询中读取这个临时表。
内联： MySQL 把 CTE 的查询语句直接嵌入到外层查询中，相当于把 CTE 展开。

物化会消耗额外的存储空间和IO，但可以避免重复计算。内联则没有这些开销，但如果CTE被多次引用，可能会导致重复计算。

MySQL 8.0 默认会尝试内联CTE，但如果CTE太复杂，或者被多次引用，它可能会选择物化。

2. 如何影响MySQL的选择？

MySQL的优化器会根据成本估算来决定是否物化CTE。我们可以通过一些技巧来影响它的选择：

MATERIALIZED 提示： 强制MySQL物化CTE。

WITH materialized cte_name AS (
    SELECT ...
)
SELECT ... FROM cte_name;

NO_MATERIALIZATION 提示： 阻止MySQL物化CTE。

WITH no_materialization cte_name AS (
    SELECT ...
)
SELECT ... FROM cte_name;

索引： 如果CTE的结果集很大，并且被多次引用，可以考虑在CTE的查询语句中添加索引，以提高查询效率。
重写SQL： 有时候，可以通过重写SQL语句来避免使用CTE，或者简化CTE的逻辑，从而提高性能。

3. 案例分析

咱们来看几个具体的例子，分析一下如何优化CTE的性能。

案例1：CTE被多次引用

假设我们要查询每个部门工资最高的员工信息，并且还要统计每个部门工资高于平均工资的人数。

WITH max_salaries AS (
    SELECT
        department_id,
        MAX(salary) AS max_salary
    FROM
        employees
    GROUP BY
        department_id
),
avg_salaries AS (
    SELECT
        department_id,
        AVG(salary) AS avg_salary
    FROM
        employees
    GROUP BY
        department_id
)
SELECT
    e.employee_id,
    e.employee_name,
    e.salary,
    d.department_name,
    (SELECT COUNT(*) FROM employees e2 WHERE e2.department_id = e.department_id AND e2.salary > (SELECT avg_salary FROM avg_salaries WHERE department_id = e.department_id)) AS above_avg_count
FROM
    employees e
JOIN
    departments d ON e.department_id = d.department_id
JOIN
    max_salaries ms ON e.department_id = ms.department_id AND e.salary = ms.max_salary;

在这个例子中，avg_salaries CTE在子查询中被多次引用。如果avg_salaries CTE没有被物化，那么每次执行子查询都会重新计算一次平均工资，效率会很低。

我们可以使用 MATERIALIZED 提示强制MySQL物化 avg_salaries CTE：

WITH max_salaries AS (
    SELECT
        department_id,
        MAX(salary) AS max_salary
    FROM
        employees
    GROUP BY
        department_id
),
MATERIALIZED avg_salaries AS ( -- 强制物化
    SELECT
        department_id,
        AVG(salary) AS avg_salary
    FROM
        employees
    GROUP BY
        department_id
)
SELECT
    e.employee_id,
    e.employee_name,
    e.salary,
    d.department_name,
    (SELECT COUNT(*) FROM employees e2 WHERE e2.department_id = e.department_id AND e2.salary > (SELECT avg_salary FROM avg_salaries WHERE department_id = e.department_id)) AS above_avg_count
FROM
    employees e
JOIN
    departments d ON e.department_id = d.department_id
JOIN
    max_salaries ms ON e.department_id = ms.department_id AND e.salary = ms.max_salary;

案例2：CTE包含复杂逻辑

假设我们要查询所有订单金额大于100的客户，并且还要筛选出那些至少下过3个订单的客户。

WITH large_orders AS (
    SELECT
        customer_id,
        COUNT(*) AS order_count,
        SUM(order_total) AS total_amount
    FROM
        orders
    WHERE
        order_total > 100
    GROUP BY
        customer_id
    HAVING
        COUNT(*) >= 3
)
SELECT
    c.customer_id,
    c.customer_name,
    lo.order_count,
    lo.total_amount
FROM
    customers c
JOIN
    large_orders lo ON c.customer_id = lo.customer_id;

在这个例子中，large_orders CTE包含 WHERE 子句、GROUP BY 子句和 HAVING 子句，逻辑比较复杂。如果数据量很大，MySQL可能会选择物化 large_orders CTE，这可能会导致性能问题。

我们可以尝试使用 NO_MATERIALIZATION 提示阻止MySQL物化 large_orders CTE：

WITH no_materialization large_orders AS ( -- 阻止物化
    SELECT
        customer_id,
        COUNT(*) AS order_count,
        SUM(order_total) AS total_amount
    FROM
        orders
    WHERE
        order_total > 100
    GROUP BY
        customer_id
    HAVING
        COUNT(*) >= 3
)
SELECT
    c.customer_id,
    c.customer_name,
    lo.order_count,
    lo.total_amount
FROM
    customers c
JOIN
    large_orders lo ON c.customer_id = lo.customer_id;

案例3：使用EXPLAIN分析执行计划

无论我们使用哪种优化技巧，最终都要通过 EXPLAIN 命令来分析查询执行计划，看看MySQL到底是怎么执行我们的SQL的。

例如，我们可以执行以下命令来查看案例1的查询执行计划：

EXPLAIN
WITH max_salaries AS (
    SELECT
        department_id,
        MAX(salary) AS max_salary
    FROM
        employees
    GROUP BY
        department_id
),
MATERIALIZED avg_salaries AS ( -- 强制物化
    SELECT
        department_id,
        AVG(salary) AS avg_salary
    FROM
        employees
    GROUP BY
        department_id
)
SELECT
    e.employee_id,
    e.employee_name,
    e.salary,
    d.department_name,
    (SELECT COUNT(*) FROM employees e2 WHERE e2.department_id = e.department_id AND e2.salary > (SELECT avg_salary FROM avg_salaries WHERE department_id = e.department_id)) AS above_avg_count
FROM
    employees e
JOIN
    departments d ON e.department_id = d.department_id
JOIN
    max_salaries ms ON e.department_id = ms.department_id AND e.salary = ms.max_salary;

EXPLAIN 命令会返回一个表格，包含查询执行的各个步骤的信息，比如使用了哪些索引，是否进行了全表扫描，是否物化了CTE等等。通过分析这些信息，我们可以判断我们的优化是否有效，并根据实际情况进行调整。

第四部分：CTE的局限性

CTE虽然强大，但也并非万能。它有一些局限性：

只在当前查询中有效： CTE只能在当前的查询语句中使用，不能跨查询使用。
不能被索引： CTE只是一个临时结果集，不能像普通表一样创建索引。
某些情况下性能可能更差： 如果CTE的逻辑过于复杂，或者被多次引用，可能会导致性能下降。

第五部分：总结

CTE是MySQL 8.0中一个非常有用的功能，它可以提高SQL代码的可读性，避免重复计算，简化递归查询，并且在某些情况下可以提高查询性能。

但是，CTE也并非万能。我们需要了解CTE的物化和内联机制，合理使用 MATERIALIZED 和 NO_MATERIALIZATION 提示，并通过 EXPLAIN 命令分析查询执行计划，才能充分发挥CTE的优势，避免踩坑。

总而言之，CTE就像一把双刃剑，用好了能让你飞起来，用不好可能会让你摔得很惨。所以，在使用CTE的时候，一定要慎重考虑，仔细测试，才能确保你的SQL代码既优雅又高效。

好了，今天的讲座就到这里。希望大家有所收获！谢谢大家！

发表回复 取消回复

发表回复取消回复