写密集型场景 SQLite 对比 MySQL 性能差异有多大

在写密集型场景下，MySQL 通常比 SQLite 具有更高的并发写入上限，核心差异在于锁粒度和架构设计。虽然 SQLite 在单线程或低并发下表现优异，但在高并发写入时，架构限制会导致性能显著下降，高并发写入场景建议优先选择 MySQL。

先说结论：SQLite 适合单用户、低并发写入或读多写少场景；MySQL 适合多用户、高并发写入场景。架构差异决定了写性能上限。

适用场景：低并发、单文件部署、边缘计算用 SQLite；Web 服务、多用户系统用 MySQL。
关键指标：关注写入并发数是否超过单线程处理能力，以及锁等待频率。
配置注意：SQLite 开启 WAL 模式可改善读并发，但无法突破单写者限制。

架构与锁机制差异

性能差异的根源在于底层架构设计目标不同。SQLite 是嵌入式数据库，与应用进程同进程运行，默认采用数据库级锁。这意味着同一时间只能有一个写操作在进行。

虽然 SQLite 支持 WAL（Write-Ahead Logging）模式可以实现读写分离，允许读者和写者并发，但写操作之间依然是串行化的。相比之下，MySQL（InnoDB 引擎）是客户端/服务端架构，支持行级锁。在高并发写入场景下，MySQL 允许多个事务同时修改不同行的数据，而 SQLite 必须排队等待锁释放。

在单行插入场景下，SQLite 的基础延迟可能低于 MySQL（无网络开销），但在并发写入场景下，SQLite 会因为锁竞争导致性能急剧下降，而 MySQL 能保持相对稳定。

基准测试实操

为了量化差异，建议使用标准压测工具进行验证。以下以 sysbench 为例，展示如何对比两者的写入性能。

1. 准备环境

确保已安装 sysbench 及对应的数据库驱动。

2. SQLite 压测命令

SQLite 通常需配合特定脚本测试，或使用通用 OLTP 脚本模拟。注意开启 WAL 模式：

sqlite3 test.db "PRAGMA journal_mode=WAL;"
sysbench oltp_write_only `--threads`=4 `--tables`=1 `--table-size`=10000 `--sqlite-db`=test.db run

3. MySQL 压测命令

sysbench oltp_write_only `--mysql-host`=127.0.0.1 `--mysql-user`=root `--mysql-password`=your_password `--mysql-db`=testdb `--threads`=4 `--tables`=1 `--table-size`=10000 run

4. 预期结果分析

低并发（1 线程）：SQLite 可能因无网络开销而略快或持平。
高并发（4 线程+）：SQLite 吞吐量不再增长甚至下降，出现 database is locked 错误；MySQL 吞吐量随线程数增加而提升。

迁移方案与脚本

如果评估后决定从 SQLite 迁移到 MySQL，除了使用 .dump 命令外，建议使用脚本处理数据类型兼容性（如 BOOLEAN 转 TINYINT）。

Python 迁移脚本示例：

import sqlite3
import mysql.connector

# 连接源数据库
src = sqlite3.connect('source.db')
# 连接目标数据库
dst = mysql.connector.connect(host='localhost', user='root', password='pwd', database='target_db')
cursor = dst.cursor()

# 读取 SQLite 数据
src_cursor = src.cursor()
src_cursor.execute("SELECT * FROM users")
rows = src_cursor.fetchall()

# 写入 MySQL (示例表结构需提前创建)
for row in rows:
    # 注意处理类型转换，如 SQLite 的 0/1 转 MySQL BOOLEAN/TINYINT
    cursor.execute("INSERT INTO users (id, name, active) VALUES (%s, %s, %s)", (row[0], row[1], row[2]))

dst.commit()
src.close()
dst.close()

注意：迁移前需在 MySQL 中预先创建好表结构，确保字段类型兼容。

验证与监控

迁移或优化后，通过以下方式验证性能及稳定性：

1. 监控锁等待

在 MySQL 中查看锁等待情况：

SHOW ENGINE INNODB STATUS;
-- 或查询 performance_schema
SELECT * FROM performance_schema.data_locks WHERE LOCK_STATUS='WAITING';

在 SQLite 中观察应用日志是否有 database is locked 或 SQLITE_BUSY 错误。

2. 资源占用观察

使用 top 或 htop 观察服务器 CPU 和 IO 等待。SQLite 写密集时可能导致文件锁竞争，增加 IO 等待时间；MySQL 则更多体现为 CPU 和内存消耗。

常见陷阱

1. 误以为 WAL 能解决写并发：WAL 模式主要优化读并发，写操作在 SQLite 中始终是互斥的，无法实现多写者并发。

2. 忽视网络开销：MySQL 有网络通信成本，在极低并发单行写入场景下，SQLite 可能反而更快，不要盲目迁移。

3. 文件锁权限问题：SQLite 依赖文件系统锁，多进程访问同一文件时，文件权限或 NFS 挂载可能导致锁失效或性能极差，建议本地磁盘存储。

4. 事务包裹不足：在 SQLite 中，多条插入操作如果不包裹在一个事务中（BEGIN TRANSACTION...COMMIT），每条语句都会单独提交，性能会下降显著。

笙亿网络策划