面试题：SQLite深度条件结果处理及Null值处理的优化策略

Question

假设有一张销售记录表'sales_records'，字段包括'sale_id'(整数类型，主键)，'product_name'(文本类型)，'quantity'(整数类型)，'price'(实数类型)，'discount'(实数类型，可为Null)。需求是计算每种产品的总销售额（总销售额 = 数量 * 价格 * （1 - 折扣），如果折扣为Null，视为无折扣即折扣为0），并且按总销售额从高到低排序，同时只显示总销售额大于10000的产品信息。在此基础上，假设表中有大量数据，如何优化这条SQLite查询语句以提高查询效率？请给出SQL语句及优化思路。

Answer 1

SQL语句

SELECT product_name, 
       SUM(quantity * price * (1 - COALESCE(discount, 0))) AS total_sales
FROM sales_records
GROUP BY product_name
HAVING total_sales > 10000
ORDER BY total_sales DESC;

优化思路

1. 索引优化：
   • 可以在 product_name 字段上创建索引，因为在 GROUP BY 操作时，索引能够加快分组的速度。
   • 执行语句 CREATE INDEX idx_product_name ON sales_records(product_name);
2. 避免函数操作：尽量不在查询的 WHERE 子句中对列进行函数操作，因为这样会阻止索引的使用。在上述查询中，COALESCE 函数在 SUM 内部，而不是 WHERE 子句中，减少了对索引使用的影响。
3. 分区表：如果数据量极大，可以考虑对 sales_records 表进行分区。例如按时间或其他逻辑进行分区，这样在查询时可以减少需要扫描的数据范围，提高查询效率。但SQLite本身对分区表支持有限，可能需要结合应用层逻辑来模拟分区。