面试题：MySQL复杂查询下GROUP BY优化

Question

假设有三个表：orders（订单表，包含订单ID、客户ID、订单日期等），order_items（订单项表，包含订单ID、产品ID、数量等），products（产品表，包含产品ID、产品名称、价格等）。现在要统计每个客户在不同月份购买的不同产品的总数量和总金额，并且按照客户ID和月份排序。写出SQL查询语句，并阐述如何对该涉及GROUP BY的复杂查询进行优化？

Answer 1

SQL查询语句

SELECT 
    o.customer_id,
    MONTH(o.order_date) AS order_month,
    p.product_id,
    SUM(oi.quantity) AS total_quantity,
    SUM(oi.quantity * p.price) AS total_amount
FROM 
    orders o
JOIN 
    order_items oi ON o.order_id = oi.order_id
JOIN 
    products p ON oi.product_id = p.product_id
GROUP BY 
    o.customer_id, MONTH(o.order_date), p.product_id
ORDER BY 
    o.customer_id, order_month;

优化涉及GROUP BY的复杂查询的方法

1. 索引优化：
   • 在orders表的customer_id和order_date列上创建复合索引，例如CREATE INDEX idx_customer_date ON orders(customer_id, order_date);。这可以加快连接和分组操作。
   • 在order_items表的order_id和product_id列上创建索引，CREATE INDEX idx_order_product ON order_items(order_id, product_id);。
   • 在products表的product_id列上创建索引，CREATE INDEX idx_product ON products(product_id);。这些索引能加速表之间的连接操作。
2. 查询重写：
   • 如果数据量巨大，可以考虑使用WITH子句（CTE）来分解复杂查询，使查询逻辑更清晰，并且某些数据库系统可能会对CTE进行更优化的处理。例如：

WITH joined_data AS (
    SELECT 
        o.customer_id,
        MONTH(o.order_date) AS order_month,
        oi.product_id,
        oi.quantity,
        p.price
    FROM 
        orders o
    JOIN 
        order_items oi ON o.order_id = oi.order_id
    JOIN 
        products p ON oi.product_id = p.product_id
)
SELECT 
    customer_id,
    order_month,
    product_id,
    SUM(quantity) AS total_quantity,
    SUM(quantity * price) AS total_amount
FROM 
    joined_data
GROUP BY 
    customer_id, order_month, product_id
ORDER BY 
    customer_id, order_month;

3. 避免使用函数在GROUP BY列：在GROUP BY子句中尽量避免使用函数，虽然在上述查询中MONTH函数看似不可避免，但如果可能，尽量在数据插入时就存储已经处理好的月份信息，这样在GROUP BY时可以直接使用列，效率更高。
4. 数据库配置优化：
   • 调整数据库服务器的内存分配，确保查询有足够的内存来处理中间数据，减少磁盘I/O。例如，对于MySQL，可以适当增加innodb_buffer_pool_size参数的值。
   • 合理设置并行查询参数，一些数据库支持并行执行查询，适当调整并行度可以提高查询性能，但要注意不能过度并行导致系统资源耗尽。