面试题：SQLite中select与操作管道在多表复杂场景下的优化应用

现有三个表，'products' 表包含 'product_id'（主键，整数类型）、'product_name'（文本类型）、'price'（数值类型）；'orders' 表包含 'order_id'（主键，整数类型）、'customer_id'（整数类型）、'order_date'（日期类型）；'order_items' 表包含 'order_item_id'（主键，整数类型）、'order_id'（整数类型，外键关联 'orders' 表的 'order_id'）、'product_id'（整数类型，外键关联 'products' 表的 'product_id'）、'quantity'（整数类型）。请通过SELECT命令和操作管道，计算每个客户在2023年购买产品的总花费，并按总花费降序排列。要求考虑性能优化，例如合理使用索引等，并阐述优化思路。

46.4万热度

难度

数据库SQLite

SQL 查询语句：

-- 为了性能优化，在orders表的order_date列、order_items表的order_id和product_id列、products表的product_id列上添加索引
CREATE INDEX idx_order_date ON orders(order_date);
CREATE INDEX idx_order_items_order_id ON order_items(order_id);
CREATE INDEX idx_order_items_product_id ON order_items(product_id);
CREATE INDEX idx_products_product_id ON products(product_id);

SELECT 
    o.customer_id,
    SUM(p.price * oi.quantity) AS total_spent
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
ORDER BY 
    total_spent DESC;

优化思路：
- 索引优化：
  - 在 orders 表的 order_date 列上创建索引，这样在执行 WHERE YEAR(o.order_date) = 2023 条件筛选时，可以快速定位到2023年的订单，避免全表扫描。
  - 在 order_items 表的 order_id 和 product_id 列上创建索引，因为 order_items 表分别通过这两列与 orders 表和 products 表进行连接，索引可以加速连接操作。
  - 在 products 表的 product_id 列上创建索引，用于加速与 order_items 表的连接，因为连接条件是基于 product_id。
- 查询结构优化：
  - 使用 JOIN 操作来关联三个表，这种方式比子查询等方式在处理多表连接时通常更高效。JOIN 操作使得数据库查询优化器可以更好地规划执行计划。
  - 在 GROUP BY 子句中只对 customer_id 进行分组，因为我们只需要按客户来统计总花费，减少不必要的分组字段可以提高查询效率。
  - 使用 SUM(p.price * oi.quantity) 直接在查询中计算每个客户的总花费，这样可以避免在应用层进行额外的计算，减少数据传输和处理开销。

星途面试题库

面试题：SQLite中select与操作管道在多表复杂场景下的优化应用

知识考点

面试题答案