sqlite3作为轻量级嵌入式数据库，在很多小型应用、移动端场景中广泛使用，但不少开发者在实际使用中会遇到读写速度慢的问题，影响整体业务效率。下面先通过一张示意图了解sqlite3的基本运行逻辑，再逐步分析问题和解决方法。
sqlite3速度慢的常见原因
1. 频繁提交事务
sqlite3默认是自动提交事务模式，每执行一条写操作SQL，都会自动开启事务、提交事务。事务提交需要写磁盘、同步日志，频繁操作会带来大量IO开销，是导致速度慢的最常见原因。
2. 缺少合适的索引
如果查询语句的WHERE条件、JOIN关联字段没有创建索引，sqlite3会进行全表扫描，表数据量越大，扫描耗时越长，查询速度自然会变慢。
3. 配置参数不合理
sqlite3有多个PRAGMA配置参数，比如同步模式、日志模式等，默认配置偏向数据安全，没有针对性能做优化，也会拖慢操作速度。
4. SQL语句写法不规范
比如查询时使用SELECT * 返回所有字段、没有限制返回条数、在WHERE条件中对字段使用函数导致索引失效等，都会增加不必要的开销。
对应的解决方法
1. 批量操作使用手动事务
如果是批量插入、更新、删除操作，建议手动开启事务，所有操作完成后再统一提交，减少事务提交的次数。以下是Python中使用sqlite3手动事务的示例：
import sqlite3
# 连接数据库
conn = sqlite3.connect('test.db')
cursor = conn.cursor()
# 手动开启事务，关闭自动提交
conn.execute('BEGIN TRANSACTION')
try:
# 批量插入数据
data = [('user1', 20), ('user2', 25), ('user3', 30)]
cursor.executemany('INSERT INTO user (name, age) VALUES (?, ?)', data)
# 统一提交事务
conn.commit()
print('批量插入完成')
except Exception as e:
# 出错时回滚事务
conn.rollback()
print(f'操作失败: {e}')
finally:
conn.close()2. 合理创建和使用索引
针对经常用于查询条件、关联查询的字段创建索引，但不要过度创建，因为索引会增加写操作的开销。创建索引的SQL示例如下：
-- 创建普通索引，针对user表的age字段
CREATE INDEX idx_user_age ON user(age);
-- 创建联合索引，针对查询中同时使用的name和age字段
CREATE INDEX idx_user_name_age ON user(name, age);
-- 查看表的索引信息
PRAGMA index_list('user');注意不要在频繁更新的字段上创建过多索引，同时避免在索引字段上使用函数，比如WHERE strftime('%Y', create_time) = '2024'这种写法会让索引失效，应该改成范围查询。
3. 优化PRAGMA配置参数
可以根据场景调整sqlite3的配置参数，平衡数据安全性和性能：
- synchronous：默认是FULL，设置为NORMAL或者OFF可以减少磁盘同步次数，提升写速度，OFF模式下如果系统崩溃可能会丢失数据，适合对数据安全性要求不高的场景。
- journal_mode：默认是DELETE，设置为WAL（预写日志）模式可以提升并发读写性能，读操作不会阻塞写操作，写操作也不会阻塞读操作。
- cache_size：设置缓存大小，单位是页，默认是2000页，可以适当调大，比如设置为10000，让更多数据缓存在内存中，减少磁盘IO。
配置参数的示例代码如下：
import sqlite3
conn = sqlite3.connect('test.db')
cursor = conn.cursor()
# 设置WAL日志模式
cursor.execute('PRAGMA journal_mode=WAL')
# 设置同步模式为NORMAL
cursor.execute('PRAGMA synchronous=NORMAL')
# 设置缓存大小为10000页
cursor.execute('PRAGMA cache_size=10000')
conn.commit()
conn.close()4. 优化SQL语句写法
避免使用SELECT *，只查询需要的字段；查询时尽量加上LIMIT限制返回条数；避免子查询嵌套过深，可以用JOIN代替；如果查询需要排序，尽量让排序字段使用索引。以下是优化前后的SQL对比：
-- 优化前：查询所有字段，无限制 SELECT * FROM user WHERE age > 18; -- 优化后：只查询需要的字段，限制返回100条 SELECT id, name, age FROM user WHERE age > 18 LIMIT 100;
性能验证方法
调整优化后，可以通过sqlite3的内置工具验证性能变化，比如使用EXPLAIN QUERY PLAN查看查询语句的执行计划，确认是否使用了索引：
-- 查看查询语句的执行计划 EXPLAIN QUERY PLAN SELECT id, name, age FROM user WHERE age > 18 LIMIT 100;
如果执行计划中出现USING INDEX的字样，说明查询已经使用了索引，性能会有明显提升。如果是写操作慢，可以对比优化前后批量插入相同数据量的耗时，确认优化效果。