面试题：SQLite复杂子查询与复合查询优化及性能分析

Question

现有 'sales' 表，字段包含'sale_id', 'product_id', 'customer_id', 'quantity', 'price'，'sale_date'；'products' 表，字段有 'product_id', 'product_name'；'customers' 表，字段有 'customer_id', 'customer_name'。需求是找出在过去一个月内，购买了特定产品（假设产品名称为 'Product X'）且购买数量大于该产品平均购买数量的客户姓名和购买数量。请写出实现的SQL语句，并分析该查询在大数据量下的性能瓶颈以及可能的优化方法。

Answer 1

SQL语句

WITH ProductXSales AS (
    SELECT 
        s.customer_id, 
        s.quantity, 
        p.product_id
    FROM 
        sales s
    JOIN 
        products p ON s.product_id = p.product_id
    WHERE 
        p.product_name = 'Product X'
        AND s.sale_date >= DATE_SUB(CURDATE(), INTERVAL 1 MONTH)
),
AvgQuantity AS (
    SELECT 
        AVG(quantity) AS avg_qty, 
        product_id
    FROM 
        sales
    JOIN 
        products p ON sales.product_id = p.product_id
    WHERE 
        p.product_name = 'Product X'
    GROUP BY 
        product_id
)
SELECT 
    c.customer_name, 
    px.quantity
FROM 
    ProductXSales px
JOIN 
    customers c ON px.customer_id = c.customer_id
JOIN 
    AvgQuantity aq ON px.product_id = aq.product_id
WHERE 
    px.quantity > aq.avg_qty;

性能瓶颈分析

1. 全表扫描：在 sales 表、products 表和 customers 表数据量很大时，关联操作可能会导致全表扫描，特别是在没有合适索引的情况下。
2. 子查询性能：在 AvgQuantity 子查询中计算平均购买数量，如果 sales 表数据量巨大，计算平均值操作可能耗时。
3. 日期过滤：s.sale_date >= DATE_SUB(CURDATE(), INTERVAL 1 MONTH) 这种基于函数的条件可能阻止索引使用，从而导致全表扫描。

优化方法

1. 索引优化：
   • 在 sales 表的 product_id、sale_date 字段上创建联合索引：CREATE INDEX idx_product_date ON sales(product_id, sale_date);
   • 在 products 表的 product_name 字段上创建索引：CREATE INDEX idx_product_name ON products(product_name);
   • 在 customers 表的 customer_id 字段上创建索引：CREATE INDEX idx_customer_id ON customers(customer_id);
2. 避免函数操作：可以预先计算好一个月前的日期并存储在变量中，然后使用变量进行过滤，如：

SET @one_month_ago = DATE_SUB(CURDATE(), INTERVAL 1 MONTH);
WITH ProductXSales AS (
    SELECT 
        s.customer_id, 
        s.quantity, 
        p.product_id
    FROM 
        sales s
    JOIN 
        products p ON s.product_id = p.product_id
    WHERE 
        p.product_name = 'Product X'
        AND s.sale_date >= @one_month_ago
),
...

3. 物化视图：如果数据量巨大且查询频繁，可以考虑使用物化视图预先计算和存储结果，以减少实时查询的计算量。