面试题：MySQL子查询优化：复杂关联场景

SQL 实现

WITH LatestOrders AS (
    -- 找到每个客户最近的订单
    SELECT 
        o.customer_id,
        o.order_id,
        o.order_date,
        ROW_NUMBER() OVER (PARTITION BY o.customer_id ORDER BY o.order_date DESC) AS rn
    FROM 
        orders o
),
LatestOrderItems AS (
    -- 找到每个客户最近订单中的商品明细
    SELECT 
        li.customer_id,
        li.order_id,
        oi.product_id,
        oi.quantity,
        oi.price,
        -- 计算商品总价
        oi.quantity * oi.price AS total_price
    FROM 
        LatestOrders li
    JOIN 
        order_items oi ON li.order_id = oi.order_id
),
MaxPriceProduct AS (
    -- 找到每个客户最近订单中总价最高的商品
    SELECT 
        lo.customer_id,
        lo.product_id,
        lo.total_price,
        ROW_NUMBER() OVER (PARTITION BY lo.customer_id ORDER BY lo.total_price DESC) AS rn
    FROM 
        LatestOrderItems lo
)
-- 最终查询出商品名称
SELECT 
    c.customer_name,
    p.product_name
FROM 
    MaxPriceProduct mp
JOIN 
    customers c ON mp.customer_id = c.customer_id
JOIN 
    products p ON mp.product_id = p.product_id
WHERE 
    mp.rn = 1;

假设存在 products 表（product_id、product_name）来存储商品名称。

性能问题分析

子查询嵌套较深：多层 CTE（公共表表达式）嵌套可能导致查询优化器难以生成高效的执行计划，特别是在数据量较大时。
排序操作频繁：在每个 CTE 中都使用了 ROW_NUMBER() 窗口函数进行排序，这在大数据量下性能开销较大。
连接操作较多：多次 JOIN 操作，如果表数据量较大，会增加查询的执行时间和资源消耗。

优化方案

减少子查询嵌套：尝试将部分逻辑合并到一个查询中，减少 CTE 的层数，这样查询优化器可以更好地进行优化。
优化排序操作：可以考虑先在子查询中获取必要的行，然后再进行排序。例如，先筛选出最近的订单，再在这些订单中计算总价并排序。
索引优化：在 orders 表的 customer_id 和 order_date 列、order_items 表的 order_id 列、products 表的 product_id 列上创建索引，这样可以加快 JOIN 和排序操作。

-- 在orders表上创建索引
CREATE INDEX idx_orders_customer_date ON orders (customer_id, order_date);
-- 在order_items表上创建索引
CREATE INDEX idx_order_items_order_id ON order_items (order_id);
-- 在products表上创建索引
CREATE INDEX idx_products_product_id ON products (product_id);

使用临时表：对于中间结果较大的子查询，可以考虑使用临时表来存储中间结果，避免重复计算。例如，先将每个客户最近的订单存储在临时表中，再基于临时表进行后续操作。

-- 创建临时表存储每个客户最近的订单
CREATE TEMPORARY TABLE temp_latest_orders AS
SELECT 
    o.customer_id,
    o.order_id,
    o.order_date,
    ROW_NUMBER() OVER (PARTITION BY o.customer_id ORDER BY o.order_date DESC) AS rn
FROM 
    orders o
WHERE 
    rn = 1;

-- 基于临时表继续完成查询逻辑
-- ...

这样可以减少查询的复杂度，提高查询性能。

星途面试题库

面试题：MySQL子查询优化：复杂关联场景

知识考点

面试题答案

SQL 实现

性能问题分析

优化方案