面试题：SQLite 事务处理与语句性能调优

使用事务确保操作原子性

在 SQLite 中，可以使用 BEGIN TRANSACTION、COMMIT 和 ROLLBACK 语句来管理事务。以下是一个示例，展示如何在插入多条记录到不同表以及更新部分记录时使用事务：

BEGIN TRANSACTION;

-- 插入记录到表1
INSERT INTO table1 (column1, column2) VALUES ('value1', 'value2');
INSERT INTO table1 (column1, column2) VALUES ('value3', 'value4');

-- 插入记录到表2
INSERT INTO table2 (column3, column4) VALUES ('value5', 'value6');
INSERT INTO table2 (column3, column4) VALUES ('value7', 'value8');

-- 更新部分记录
UPDATE table1 SET column2 = 'new_value' WHERE column1 = 'value1';

COMMIT;

在这个示例中，BEGIN TRANSACTION 开始一个事务，所有在 BEGIN TRANSACTION 和 COMMIT 之间的 SQL 语句会被视为一个原子操作。如果在事务执行过程中发生错误，可以使用 ROLLBACK 回滚所有操作，确保数据库状态不会被部分修改。

性能调优

索引优化
- 单表查询：
  - 对于经常用于 WHERE 子句的列，创建索引可以显著提高查询性能。例如，如果经常执行 SELECT * FROM table1 WHERE column1 = 'value1';，可以创建索引 CREATE INDEX idx_table1_column1 ON table1 (column1);。
- 多表关联查询：
  - 在关联列上创建索引。例如，如果有查询 SELECT * FROM table1 JOIN table2 ON table1.id = table2.table1_id;，可以在 table1.id 和 table2.table1_id 上创建索引。
  - CREATE INDEX idx_table1_id ON table1 (id);
  - CREATE INDEX idx_table2_table1_id ON table2 (table1_id);
- 子查询：
  - 如果子查询中涉及到 WHERE 条件，可以在相关列上创建索引。例如，对于 SELECT * FROM table1 WHERE id IN (SELECT table1_id FROM table2 WHERE column3 = 'value5');，在 table2.table1_id 和 table2.column3 上创建索引。
  - CREATE INDEX idx_table2_table1_id ON table2 (table1_id);
  - CREATE INDEX idx_table2_column3 ON table2 (column3);
查询优化
- 避免全表扫描：尽量使用索引来缩小查询范围，避免全表扫描。通过合理的索引设计，让查询能够快速定位到所需的数据。
- 使用 EXPLAIN QUERY PLAN：这是一个非常有用的工具，可以查看 SQLite 如何执行查询，帮助发现查询性能瓶颈。例如，EXPLAIN QUERY PLAN SELECT * FROM table1 JOIN table2 ON table1.id = table2.table1_id;，根据查询计划调整索引或查询语句。
数据库设计优化
- 避免冗余数据：冗余数据会增加存储成本，并且在更新时可能导致数据不一致。保持数据库设计的规范化，减少不必要的数据重复。
- 合理分区：对于大数据量的表，可以考虑合理分区，将数据按照一定规则划分到不同的物理文件或存储区域，提高查询性能。不过 SQLite 在分区方面的支持相对有限，通常需要在应用层面进行一定的处理。

星途面试题库

面试题：SQLite 事务处理与语句性能调优

知识考点

面试题答案

使用事务确保操作原子性

性能调优