面试题：MySQL查询优化之复杂场景优化

Question

在一个电商数据库中，有订单表orders（包含订单id、用户id、订单金额、下单时间等字段），商品表products（包含商品id、商品名称、价格等字段），订单详情表order_items（包含订单id、商品id、购买数量等字段）。现在需要统计每个用户购买的不同商品的总金额，且只统计下单时间在最近一个月内的订单。请写出实现该查询的SQL语句，并详细说明在大数据量下如何进行查询优化，包括但不限于索引策略、分区策略以及查询重写等方法。

Answer 1

SQL语句

SELECT
    o.user_id,
    SUM(pi.price * oi.quantity) AS total_amount
FROM
    orders o
JOIN
    order_items oi ON o.order_id = oi.order_id
JOIN
    products pi ON oi.product_id = pi.product_id
WHERE
    o.order_time >= DATE_SUB(CURRENT_DATE, INTERVAL 1 MONTH)
GROUP BY
    o.user_id;

大数据量下的查询优化

1. 索引策略
   • 在 orders 表的 order_time 字段上创建索引，加速 WHERE 子句中的时间过滤条件。

   CREATE INDEX idx_order_time ON orders(order_time);
   

   • 在 orders 表的 user_id 字段上创建索引，加速 GROUP BY 操作。

   CREATE INDEX idx_user_id ON orders(user_id);
   

   • 在 order_items 表的 order_id 字段上创建索引，用于连接 orders 表。

   CREATE INDEX idx_order_id ON order_items(order_id);
   

   • 在 order_items 表的 product_id 字段上创建索引，用于连接 products 表。

   CREATE INDEX idx_product_id ON order_items(product_id);
   

   • 在 products 表的 product_id 字段上创建索引，用于连接 order_items 表。

   CREATE INDEX idx_product_id_products ON products(product_id);
   

2. 分区策略
   • 可以根据 orders 表的 order_time 进行分区，比如按月份分区。这样在查询最近一个月的数据时，只需要扫描对应月份的分区，减少扫描的数据量。以MySQL为例，假设 order_time 是 DATE 类型：

   ALTER TABLE orders PARTITION BY RANGE (YEAR(order_time) * 100 + MONTH(order_time)) (
       PARTITION p0 VALUES LESS THAN (202301),
       PARTITION p1 VALUES LESS THAN (202302),
       -- 依此类推添加更多分区
       PARTITION p11 VALUES LESS THAN (202401)
   );
   

3. 查询重写
   • 可以先在 orders 表中查询出最近一个月的订单，然后将结果作为临时表与其他表进行连接。

   WITH recent_orders AS (
       SELECT * FROM orders WHERE order_time >= DATE_SUB(CURRENT_DATE, INTERVAL 1 MONTH)
   )
   SELECT
       ro.user_id,
       SUM(pi.price * oi.quantity) AS total_amount
   FROM
       recent_orders ro
   JOIN
       order_items oi ON ro.order_id = oi.order_id
   JOIN
       products pi ON oi.product_id = pi.product_id
   GROUP BY
       ro.user_id;
   

   • 这种方式可以减少中间结果集的大小，提高查询效率。同时，在大数据量下还可以考虑使用分布式计算框架，如Hadoop、Spark等，将数据分布到多个节点进行并行处理，进一步提升查询性能。