面试题：SQLite紧凑性设计下的性能优化

Question

在SQLite紧凑性设计的基础上，当面对大量数据时，如何通过优化查询、索引策略来保证性能不受紧凑性带来的潜在影响？请举例说明。

Answer 1

优化查询

1. 减少选择列：只选择需要的列，避免使用 SELECT *。例如，在有一个 employees 表，包含 id、name、age、department 等列，若只需要 name 和 department，则查询应写成 SELECT name, department FROM employees;。这样减少了数据传输量，提升性能。
2. 合理使用 WHERE 子句：精准过滤数据。比如在 orders 表中，要查询2023年的订单，使用 SELECT * FROM orders WHERE order_date >= '2023-01-01' AND order_date < '2024-01-01';，可减少扫描的数据量。
3. 避免在 WHERE 子句中对列进行函数操作：例如 SELECT * FROM users WHERE UPPER(name) = 'JOHN'; 应改写为 SELECT * FROM users WHERE name = 'JOHN';，因为对列进行函数操作会阻止索引的使用。

索引策略

1. 创建合适的索引：
   • 单列索引：若经常根据 customers 表的 city 列查询客户，可创建 CREATE INDEX idx_customers_city ON customers(city);，之后查询 SELECT * FROM customers WHERE city = 'New York'; 时，索引会加速查询。
   • 复合索引：对于需要同时根据 orders 表的 order_date 和 customer_id 进行查询的场景，如 SELECT * FROM orders WHERE order_date >= '2023-01-01' AND customer_id = 123;，可创建复合索引 CREATE INDEX idx_orders_date_customer ON orders(order_date, customer_id);。需注意复合索引中列的顺序，一般把选择性高（唯一值多）的列放在前面。
2. 定期分析和重建索引：随着数据的插入、更新和删除，索引可能会碎片化，影响性能。SQLite 虽没有像其他数据库那样专门的重建索引语句，但可通过备份和恢复数据库来重建索引。例如先 sqlite3 your_database.db ".backup /path/to/backup.db"，然后删除原数据库并将备份恢复为新数据库 sqlite3 your_database.db ".restore /path/to/backup.db"，这样可重建索引，提升性能。