个人站从 MySQL 换成 SQLite 的实测:内存账、四类 SQL 方言改写与备份假成功

最近一段时间,「把站点从 MySQL 迁出来」这件事在小站长圈子里讨论得很热。原因也很实在:单核两 G 的机器上,MySQL 一个进程就要吃掉 300 到 600MB,剩下的内存还要留给 PHP-FPM;而很多个人站的查询其实简单得可怜——按时间列文章、按栏目过滤、全文搜一下关键词。这种负载,用 MySQL 属于「开卡车送快递」。

这篇文章不复述别人的测评,只讲两件事:什么规模的站点适合换、换了以后哪些东西会变难。后半段是我自己在一台展示型站点上做的实测数据,以及现在会重做的那几个决定。

先算清楚:换引擎到底能省下多少

判断值不值得换,别从「哪个数据库更现代」出发,从内存账本出发。我的实测环境是 2 核 2G 的云服务器,站点约 1200 篇内容、每天 3000 到 5000 PV、有搜索和标签页。

迁之前,MySQL 的常驻内存在 380MB 左右(缓冲池 256MB 加上连接与临时表),PHP-FPM 的 8 个工作进程共占约 400MB,系统与其他服务约 200MB,余量紧张,一到晚上抓取高峰就开始换页。迁之后,SQLite 的内存占用几乎可以忽略——它随进程走,没有独立服务,峰值出现在一次大查询里,几百 KB 到几 MB。同一台机器上,整机可用内存从「经常低于 150MB」变成「稳定在 600MB 以上」。

注意这里有个容易自我欺骗的地方:SQLite 省的是「常驻内存」,不是「CPU」。如果你的瓶颈是 MySQL 的慢查询和排序,换 SQLite 大概率不会变快,甚至更慢,因为单写入者模型下并发的写请求会排队。只有当你确认「瓶颈是内存不够导致的换页与 OOM」时,这次迁移才有明确收益。

判断自己是不是适合的那一类站

我把适合与不适合写成两组互斥的问题,对照自己答一遍就很清楚了。

适合的画像:读远多于写(比如发布是几个人手动操作,访问是每天几千次);单机部署,没有读库从库;数据量在几 GB 以内;查询以主键和少数几个索引列为主;可以接受「某个大查询偶尔慢一点,但不影响别人」;备份策略是「整库一份文件拷走」。

不适合的画像:有并发写入(评论、点赞、计数器高频更新);需要从库或多实例;数据量十几 GB 以上;有复杂的联表统计;必须在线热备不能停机;团队里有人只会 MySQL 的管理方式。

还有一类是看起来适合、实际不该换:站点跑在成熟 CMS 上,且有多个依赖 MySQL 特性的插件。换引擎的代价不在数据库,而在「把所有 SQL 方言和驱动层改一遍」,一旦插件用了 ON DUPLICATE KEY UPDATE、FULLTEXT 或存储过程,你就得逐个改写。这种情况下,钱应该花在升级配置上,而不是花在迁移上。

迁移第一步:把「SQL 方言」列成清单,而不是边改边发现

最容易翻车的方式是先导出数据、跑起来看哪里报错。正确做法是先做静态清点:把代码里所有 SQL 语句按类型抓出来,标出哪些是 SQLite 不直接支持的。

# 把项目里所有 SQL 出现处捞出来,人工过一遍
grep -rniE '(select|insert|update|delete|replace)[ (]' --include='*.php' . \
  | grep -viE '^\s*(//|\*)' | cut -c1-160 > /tmp/sql_inventory.txt
wc -l /tmp/sql_inventory.txt

重点标记四类写法:一是 INSERT ... ON DUPLICATE KEY UPDATE,SQLite 要换成 INSERT ... ON CONFLICT(...) DO UPDATE SET;二是 FULLTEXT 全文索引,SQLite 要用 FTS5 虚拟表,且中文分词需要额外方案;三是 AUTO_INCREMENT 与 UNSIGNED,前者对应 AUTOINCREMENT,后者 SQLite 不支持;四是日期函数,NOW()、DATE_FORMAT()、FROM_UNIXTIME() 全都不存在,要换成 datetime('now','localtime') 与 strftime()。

另外还有一个不显眼但会咬人的差异:字符串比较的默认行为。MySQL 的普通比较通常不区分大小写(取决于 collation),而 SQLite 的 = 是区分大小写的。这意味着登录时「用户名大小写不一致也能进」的行为会改变。这类问题不会报错,只会表现为「某几个老用户突然登不上」。

第二步:数据类型与时间戳的搬迁规则

数据迁移本身不难,难在保持一致。我用的原则是:结构手写,数据程序搬。结构不要靠自动转换工具生成,因为工具往往把 varchar(255) 一律变成 TEXT、把 INT 变成 INTEGER,丢掉了长度约束和 unsigned 语义,后期想加约束又要改一次。

时间字段是最容易出错的。MySQL 里 DATETIME 是「不带时区的一串字面量」,而 Unix 时间戳是绝对时刻。个人站常见的做法是两者混用——创建时间存 int 时间戳、更新时间存 datetime。迁移时可以统一成 INTEGER 存时间戳,也能统一成 TEXT 存 ISO8601 字符串,但必须二选一并且全站一致,绝不能一个表一种。混用的直接后果是排序错乱,而这种错乱往往要过很久才有人发现。

迁移脚本我建议写成「读一批、写一批、对账一批」的形式,不要一把梭:

import sqlite3, pymysql
src = pymysql.connect(host='127.0.0.1', user='app', password='***', database='site',
                      charset='utf8mb4', cursorclass=pymysql.cursors.DictCursor)
dst = sqlite3.connect('/var/www/site/data/site.db')
dst.execute('PRAGMA journal_mode=WAL')
dst.execute('PRAGMA foreign_keys=ON')
src_c = src.cursor(); src_c.execute('SELECT * FROM posts ORDER BY id')
rows = src_c.fetchall()
dst.executemany(
    'INSERT OR REPLACE INTO posts (id,title,slug,body,created,updated,status) '
    'VALUES (?,?,?,?,?,?,?)',
    [(r['id'], r['title'], r['slug'], r['body'],
      int(r['created'].timestamp()), int(r['updated'].timestamp()), r['status'])
     for r in rows])
dst.commit()
print('rows', len(rows), 'dst_count', dst.execute('SELECT COUNT(*) FROM posts').fetchone()[0])

脚本里有三个细节:PRAGMA journal_mode=WAL 是为了让后续的读不被写阻塞,这是 SQLite 用在网站上最重要的一条设置;charset='utf8mb4' 必须显式写,否则 emoji 会在读取时就变成问号,而不是在写入时——这个区别很重要,因为前者不可逆;最后那行对账必须打印出来,迁移是否完整只认这个数字。

第三步:迁移之后才暴露的三类问题

数据搬完、页面能打开,不算迁移完成。下面三类问题通常在运行一到两周后才浮现,我按遇到的顺序写。

第一类,写入并发下的 busy 超时。SQLite 同一时刻只允许一个写入者。站点上「访问计数」「最后访问时间」「会话表」这类高频写入会在高峰期互相等待,报出 database is locked。解决办法不是提高重试次数,而是把高频写入去掉或合并:访问计数改成「内存里累加、定时批量落库」,会话表换成文件或 Redis,更新时间只在该用户实际改动内容时写。这一步做完,写冲突基本消失。

第二类,搜索变弱。这是替换 MySQL 最明显的代价。原来的 LIKE '%关键词%' 在几千篇文章上勉强可用,但会全表扫描;上 FTS5 以后速度很快,代价是中文分词。FTS5 自带的 unicode61 对中文是按字切,召回偏高、相关性差;想要接近期望的效果,需要引入分词器或者自己预处理成二元组。我的选择是只对标题和标签建索引,正文搜索降级为「按标题命中 + 站内搜索跳转搜索引擎」——这个决定省掉了大量调优时间,但也确实降低了站内搜索的体验。

第三类,备份与恢复的心智模型变了。MySQL 时代可以用 mysqldump 做逻辑备份、可以单表恢复;SQLite 是单文件,备份简单到「拷一个文件」,但前提是文件在拷的时候是一致的。开 WAL 之后,直接 cp 数据库文件会得到一个缺了 -wal 文件的不完整副本——这种备份「看起来能打开」,一旦恢复就丢最近的数据,是最危险的一类假成功。正确做法是用 SQLite 自带的在线备份接口,或者用 VACUUM INTO 生成一个一致性快照:

# 一致性快照,不需要停服务
sqlite3 /var/www/site/data/site.db "VACUUM INTO '/mnt/offsite/site-$(date +%F).db'"
# 校验快照可用且行数对得上
sqlite3 /mnt/offsite/site-$(date +%F).db "PRAGMA integrity_check; SELECT COUNT(*) FROM posts;"

PRAGMA integrity_check 返回 ok 只是「文件结构没坏」,不等于「数据是全的」。所以还要加一条行数校验,与线上对比。备份这件事的标准从来不是「命令跑完没报错」,而是「恢复出来的东西对得上」。

第四步:该调的参数其实只有四个

SQLite 的配置看起来有一大堆,实际上对网站场景有决定性影响的只有四行,其余多是微调:

PRAGMA journal_mode = WAL;        -- 读不阻塞写,必开
PRAGMA synchronous = NORMAL;      -- WAL 下推荐,掉电最多丢最后一次事务
PRAGMA busy_timeout = 5000;       -- 遇锁等 5 秒而不是立刻报错
PRAGMA cache_size = -32000;       -- 负数表示 KB,即 32MB 页缓存
PRAGMA foreign_keys = ON;         -- 每个连接都要设,不是全局的

前四个是性能与稳定性的核心取舍:synchronous=FULL 更安全但写入慢数倍,对内容站完全没必要;busy_timeout 如果设得太小,高峰期会成片报错,设得太大又会让请求堆积,5000 毫秒是个经验上的平衡点。最后一行最容易漏:foreign_keys 是连接级设置,每次新建连接都要重新开,写在初始化 SQL 里但没在新连接里执行,就会表现为「外键约束时有时无」。

还有一条不写在 SQL 里的注意事项:数据库文件所在的目录必须让 PHP 进程可写,而 SQLite 在写的时候会在同目录下创建 -wal 和 -shm 文件。只给数据库文件本身 chmod 666 是不行的,必须给目录写权限。这个问题在容器化部署里特别常见——挂载了单个文件而不是目录,于是 WAL 文件根本创建不出来,日志里报的是「unable to open database file」,看起来像路径错了。

什么情况下我会劝人换回 MySQL

这段是给已经心动的人泼的冷水。三种情况下,SQLite 会成为负担而不是优势。

要上多台机器的时候。SQLite 是单机嵌入式数据库,没有网络协议。一旦你需要第二台 Web 服务器分担流量,数据库就必须挪出去,这时要么上 MySQL,要么上 PostgreSQL,之前的工作白做。如果站点有明显的增长预期,不如一次到位。

团队里有人要直连改数据的时候。MySQL 可以开个只读账号给别人查数据、可以用客户端工具连、可以按表授权。SQLite 只是一个文件,任何能登服务器的人都能直接改,无法做细粒度权限。这在只有自己的时候是优点,在有协作的时候是风险。

需要在线扩字段或加索引的时候。SQLite 对 ALTER TABLE 的支持很有限,加索引在数据量大时会锁住写;MySQL 的在线 DDL 虽然也不完美,但工具链成熟得多。

回看这次迁移,我会改的三个决定

最后把结论落成三条可复用的判断,而不是一句「SQLite 真好」。

第一,先量化再迁。我当时凭感觉认为「内存不够」,实际应该先跑一周的内存采样,确认峰值出现在 MySQL 还是 PHP-FPM。事后看结论是对的,但过程不够扎实——如果峰值其实在 FPM 的进程数上,省下的内存会被立刻吃掉,迁移就白做了。

第二,搜索不要硬扛。我应该一开始就把站内搜索降级,而不是先花两天折腾中文分词再放弃。个人站的核心价值在内容本身,搜索是一个「有就行」的功能,不值得投入超过半天的调优。

第三,备份先改再迁。数据库换引擎的那一天,备份脚本必须同步换掉。我当时的备份脚本里还留着 mysqldump,连续三天跑失败但没有告警,等于三天没有备份。迁移类工作的验收清单里,应该把「备份链路是否同步更新并有成功告警」列在第一行,而不是最后一行。

Last modification:November 19th, 2026 at 07:33 pm

Leave a Comment