面试题：SQLite数据完整性机制与性能优化的平衡策略

合理创建索引：
- 针对经常用于 WHERE 子句、JOIN 子句中的列创建索引。例如，在 SELECT * FROM users WHERE age > 30; 这样的查询中，如果 age 列没有索引，SQLite 可能需要全表扫描。对 age 列创建索引 CREATE INDEX idx_users_age ON users(age); 可以显著提升查询性能。
- 避免过度创建索引，因为每个索引都会占用额外的存储空间，并且在数据插入、更新和删除时，SQLite 不仅要更新表数据，还要更新相关索引，这会增加操作的开销。
复合索引：
- 当多个列经常一起出现在 WHERE 子句中时，使用复合索引。例如，对于查询 SELECT * FROM orders WHERE customer_id = 123 AND order_date > '2023 - 01 - 01';，可以创建复合索引 CREATE INDEX idx_orders_customer_date ON orders(customer_id, order_date);。
- 注意复合索引的列顺序，一般将选择性高（即列中不同值的数量多）的列放在前面，这样能更好地利用索引进行查询优化。

批量操作与事务：
- 将多个相关的数据库操作放在一个事务中。例如，在插入多条记录时，不要逐条插入，而是将所有插入语句放在一个事务块内。
```
BEGIN;
INSERT INTO products (product_name, price) VALUES ('Product1', 10.0);
INSERT INTO products (product_name, price) VALUES ('Product2', 15.0);
COMMIT;
```
- 这样可以减少磁盘 I/O 操作，因为 SQLite 在事务提交时才会将数据持久化到磁盘，而不是每次操作都进行 I/O。
事务隔离级别：
- SQLite 默认的隔离级别是 DEFERRED，这意味着在事务开始时，不会立即锁定数据库文件。在高并发场景下，如果多个事务同时进行读写操作，可能会出现数据不一致问题。可以根据实际需求设置隔离级别，例如 IMMEDIATE 隔离级别会在事务开始时就获取共享锁，防止其他写事务并发执行，从而保证数据一致性，但可能会影响并发性能。

应用层缓存：
- 在应用程序中实现缓存机制，例如使用内存缓存（如 Memcached 或 Redis）来缓存经常查询的结果。对于一些不经常变化的数据，如网站的配置信息、静态数据等，可以从缓存中直接获取，避免频繁查询 SQLite 数据库。
SQLite 内置缓存：
- SQLite 本身有一定的缓存机制，其页缓存会缓存最近访问的数据页。可以通过设置 PRAGMA cache_size 来调整缓存大小，单位是页面数。一般来说，增加缓存大小可以减少磁盘 I/O，提高性能，但要根据系统内存情况合理设置，避免占用过多内存导致系统性能下降。

性能与数据完整性的权衡：
- 在优化性能时，不能以牺牲数据完整性为代价。例如，虽然删除索引可能会提高插入性能，但会降低查询性能，并且可能破坏数据完整性（如唯一性约束依赖索引实现）。因此，需要在确保数据完整性的基础上，根据实际的读写操作频率和数据量来调整索引策略。
系统资源的权衡：
- 增加缓存大小可以提高性能，但会占用更多内存。在配置 PRAGMA cache_size 时，要考虑系统的整体内存使用情况，避免因内存不足导致系统交换空间频繁使用，反而降低性能。
- 对于事务管理，虽然批量操作和合理的隔离级别可以提高性能，但在高并发场景下，可能会导致部分事务等待锁，需要根据业务需求和并发量来平衡事务的并发度和数据一致性。

知识考点