面试题：MySQL关联子查询优化策略实践

Question

假设存在两张表，`orders`表（包含`order_id`, `customer_id`, `order_date`等字段）和`order_items`表（包含`item_id`, `order_id`, `product_name`, `quantity`等字段），现在要查询每个客户最近一笔订单中购买数量最多的商品名称。请写出实现该查询的SQL语句，并说明如何对这条关联子查询进行性能优化。

Answer 1

SQL语句

WITH RankedOrders 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
),
RankedOrderItems AS (
    SELECT 
        oi.order_id,
        oi.product_name,
        oi.quantity,
        ROW_NUMBER() OVER (PARTITION BY oi.order_id ORDER BY oi.quantity DESC) AS rn
    FROM 
        order_items oi
)
SELECT 
    ro.customer_id,
    roi.product_name
FROM 
    RankedOrders ro
JOIN 
    RankedOrderItems roi ON ro.order_id = roi.order_id
WHERE 
    ro.rn = 1 AND roi.rn = 1;

性能优化

1. 索引优化
   • 在orders表的customer_id和order_date字段上创建联合索引：

CREATE INDEX idx_orders_customer_date ON orders (customer_id, order_date);

- 在`order_items`表的`order_id`和`quantity`字段上创建联合索引：

CREATE INDEX idx_order_items_order_quantity ON order_items (order_id, quantity);

2. 查询改写
   • 可以尝试使用窗口函数直接在主查询中实现相同逻辑，减少CTE（公共表表达式）的使用，有时能提升性能。

SELECT 
    o.customer_id,
    MAX(CASE WHEN ro.rn = 1 AND roi.rn = 1 THEN oi.product_name END) AS product_name
FROM 
    orders o
JOIN (
    SELECT 
        customer_id,
        order_id,
        ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY order_date DESC) AS rn
    FROM 
        orders
) ro ON o.customer_id = ro.customer_id AND o.order_id = ro.order_id
JOIN order_items oi ON o.order_id = oi.order_id
JOIN (
    SELECT 
        order_id,
        ROW_NUMBER() OVER (PARTITION BY order_id ORDER BY quantity DESC) AS rn
    FROM 
        order_items
) roi ON oi.order_id = roi.order_id
GROUP BY 
    o.customer_id;

3. 数据库配置
   • 调整数据库的缓存参数，确保查询过程中经常访问的数据能被缓存，减少磁盘I/O操作。例如，在MySQL中，适当增大innodb_buffer_pool_size参数的值。
   • 合理配置并行查询参数，对于支持并行查询的数据库，设置合适的并行度，充分利用多核CPU的性能。