面试题：SQLite复杂聚合操作与性能优化

假设有一个非常大的SQLite表'orders'，包含字段'order_id'（订单ID，整数类型），'customer_id'（客户ID，整数类型），'order_date'（订单日期，日期类型），'order_amount'（订单金额，浮点数类型）。现在要统计每个客户在不同月份的订单总金额，并且只显示订单总金额大于1000的记录。请写出SQL语句，并说明在大数据量下，如何对该查询进行性能优化。

13.9万热度

难度

数据库SQLite

SQL语句

SELECT
    customer_id,
    strftime('%Y-%m', order_date) AS month,
    SUM(order_amount) AS total_amount
FROM
    orders
GROUP BY
    customer_id,
    strftime('%Y-%m', order_date)
HAVING
    SUM(order_amount) > 1000;

性能优化

索引优化：
- 在 orders 表的 customer_id 和 order_date 字段上创建复合索引。因为查询中按 customer_id 和 order_date 进行分组，复合索引可以加快数据的检索。
```
CREATE INDEX idx_customer_date ON orders (customer_id, order_date);
```
查询优化：
- 如果数据量极大，可以考虑对数据进行分区，比如按日期分区。这样在查询时，只需要检索特定分区的数据，减少扫描的数据量。
- 在SQLite中，可以通过定期清理历史数据来减少表的大小，提高查询性能。例如，如果只需要统计近一年的数据，定期删除一年前的数据。
配置优化：
- 适当增加SQLite的缓存大小，让更多的数据可以在内存中处理，减少磁盘I/O。可以通过 PRAGMA cache_size 来设置缓存大小（单位是页面数，通常一个页面大小为1024字节）。例如，设置缓存大小为10000个页面：
```
PRAGMA cache_size = 10000;
```

星途面试题库

面试题：SQLite复杂聚合操作与性能优化

知识考点

面试题答案

SQL语句

性能优化