面试题：SQLite查询计划在高并发场景下的优化

SQLite自带分析工具：在SQLite中，可以使用 EXPLAIN QUERY PLAN 语句。在要执行的SQL查询语句前加上此命令，如 EXPLAIN QUERY PLAN SELECT * FROM your_table WHERE some_condition;，它会返回查询计划信息，包括如何访问表、是否使用索引等。
可视化工具：如SQLiteStudio等可视化工具，这些工具通常能以图形化方式展示查询计划，更直观地理解查询执行过程。

表访问方式：查看查询计划中表是全表扫描（SCAN TABLE）还是通过索引访问（SEARCH TABLE...USING INDEX）。全表扫描在大数据量下性能较差，应尽量优化为索引访问。
索引使用情况：确认是否正确使用了索引。若查询计划显示未使用预期索引，可能索引设计不合理，比如索引列与查询条件不匹配，或者复合索引顺序有误。
连接顺序：对于多表连接查询，连接顺序会影响性能。查询计划会展示表连接的顺序，不合理的顺序可能导致中间结果集过大。

索引优化：
- 创建合适索引：根据频繁查询的条件，创建索引。例如，若经常按某个列进行过滤查询，为该列创建单列索引；若涉及多列的联合查询条件，创建复合索引，但要注意复合索引的列顺序应与查询条件中列的使用顺序相匹配。
- 避免冗余索引：冗余索引会增加插入、更新操作的开销，定期检查并删除不必要的索引。
查询语句优化：
- 简化查询：尽量避免复杂的子查询和嵌套查询，可通过连接操作替代。例如，将子查询改写为 JOIN 操作，减少中间结果集的生成。
- 使用覆盖索引：若查询仅涉及索引列，可创建覆盖索引，这样查询时可直接从索引获取数据，无需回表操作，提高查询性能。
数据库结构优化：
- 范式与反范式：在适当情况下，对数据库进行反范式设计。虽然范式化设计可减少数据冗余，但在高并发查询场景下，过多的关联操作可能影响性能。反范式设计通过增加少量冗余数据，减少表连接，提高查询效率，但要注意数据一致性维护。
- 分区表：对于大数据量表，可采用分区表技术。根据某个列（如时间）将数据划分到不同的分区，查询时可只访问相关分区，减少数据扫描范围。

影响：事务中的查询操作会受到事务隔离级别的影响。不同隔离级别下，查询可能看到不同的数据版本，这可能导致查询计划的变化。例如，在 READ COMMITTED 隔离级别下，查询只能看到已提交的数据，可能在事务执行过程中因其他事务提交新数据而使查询计划有所不同；而在 SERIALIZABLE 隔离级别下，为保证事务串行化执行，可能会有更多的锁机制介入，影响查询性能。
优化思路：
- 合理设置隔离级别：根据业务需求选择合适的事务隔离级别。若业务对数据一致性要求不是极高，可选择较低隔离级别（如 READ COMMITTED）以减少锁争用，提高并发性能；若数据一致性要求严格，则选择较高隔离级别（如 SERIALIZABLE），但要注意可能带来的性能问题。
- 控制事务粒度：尽量减小事务的粒度，将大事务拆分为多个小事务。小事务执行时间短，锁持有时间也短，可降低锁争用的可能性，提高并发性能。

影响：SQLite采用共享锁（读锁）和排他锁（写锁）。在高并发查询场景下，若有写操作（如插入、更新、删除），会获取排他锁，此时其他读写操作都需等待，影响查询性能。同时，查询语句本身也可能获取共享锁，多个查询同时获取共享锁可能导致死锁或性能瓶颈。
优化思路：
- 读写分离：将读操作和写操作分离到不同的数据库连接或线程。对于只读查询，可使用共享锁并发执行，提高查询并发度；对于写操作，采用排队或批量处理的方式，减少写锁对读操作的影响。
- 优化锁粒度：尽量降低锁的粒度，如使用行级锁而非表级锁。SQLite默认采用表级锁，但可通过一些操作（如使用 BEGIN IMMEDIATE 开启事务，在一定程度上可实现行级锁效果）来优化锁粒度，减少锁争用范围。
- 死锁检测与处理：定期检查数据库是否存在死锁情况，若发现死锁，及时采取措施，如回滚其中一个事务来打破死锁。可通过设置合适的死锁超时时间，避免长时间等待。

星途面试题库