面试题：MySQL查询执行计划之高级难度题

SQL查询语句：

SELECT 
    c.customer_name,
    SUM(oi.quantity * p.price) AS total_amount
FROM 
    orders o
JOIN 
    customers c ON o.customer_id = c.customer_id
JOIN 
    order_items oi ON o.order_id = oi.order_id
JOIN 
    products p ON oi.product_id = p.product_id
WHERE 
    YEAR(o.order_date) = 2023
GROUP BY 
    c.customer_id, c.customer_name
HAVING 
    SUM(oi.quantity * p.price) > 1000;

执行计划分析：
- 表连接顺序：
  - 数据库优化器会根据统计信息决定表的连接顺序。理想情况下，应该从数据量较小且能快速过滤数据的表开始连接。例如，如果orders表中有日期索引，那么先从orders表过滤出2023年的订单，能减少后续连接的数据量。
- 索引使用：
  - 在orders表的order_date列上，如果没有索引，全表扫描会非常耗时。添加CREATE INDEX idx_order_date ON orders(order_date);可以加速日期过滤。
  - orders表的customer_id列、order_items表的order_id列、products表的product_id列也都应该建立索引，以加速连接操作。例如，CREATE INDEX idx_customer_id ON orders(customer_id); ，CREATE INDEX idx_order_id ON order_items(order_id); ，CREATE INDEX idx_product_id ON products(product_id);
- 聚合操作：
  - GROUP BY操作在没有合适索引时也会消耗大量资源。如果对c.customer_id和c.customer_name进行GROUP BY，在customers表上对这两列建立联合索引CREATE INDEX idx_customer ON customers(customer_id, customer_name); 有助于提升聚合性能。
提升性能的调整：
- 索引调整：
  - 按照上述分析，在相关列上建立索引，特别是连接列和过滤列。
- 表结构调整：
  - 如果某些表数据量过大，可以考虑分区表。例如，orders表可以按日期进行分区，将不同年份的数据分在不同的分区，这样在查询特定年份数据时，只需扫描对应分区，减少扫描的数据量。
- 查询语句调整：
  - 可以使用EXISTS子查询替代部分连接来优化查询逻辑。例如：

SELECT 
    c.customer_name,
    total_amount
FROM 
    customers c
JOIN 
    (
        SELECT 
            o.customer_id,
            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
        WHERE 
            YEAR(o.order_date) = 2023
        GROUP BY 
            o.customer_id
        HAVING 
            SUM(oi.quantity * p.price) > 1000
    ) sub ON c.customer_id = sub.customer_id;

这种方式有时能让优化器生成更高效的执行计划。

星途面试题库

面试题：MySQL查询执行计划之高级难度题

知识考点

面试题答案