波仔
🐬 第29章 数据库维护
📄 一句话概括

维护就三件事：备份、体检、看日志。 平时都不显眼，出事那天全是必需品。 Demo 真造了个两万行的库文件：413,696 字节，删掉一半一个字节都没少， VACUUM 之后降到 212,992 字节（小了 48.5%）； integrity_check 回 ok，backup 出来的副本 217,088 字节、一万行一行不少。

📝 本章和前面几章的关系

前面二十八章都在「库是好的、数据是对的」这个前提下工作。 这一章处理另外三种情况：库变大了、库坏了、库起不来了。 它是全书唯一一章讲「运维」的，也是真实工作里最常被追问的一块。

一、备份：两种做法，各有各的用处

逻辑备份导出的是 SQL 语句（mysqldump），能在别的版本、别的机器上重放，文件是纯文本、 能改能查。 缺点是慢，大库要跑很久。 物理备份拷的是数据文件本身，快、能整库搬走， 但通常绑版本、绑平台。 Demo 里那句 con.backup() 就是物理级复制。 为什么不能直接拷文件：拷的瞬间如果有写入， 拷走的是一半旧一半新的页，恢复出来是坏的。 backup 接口会处理一致性，别用文件管理器代替它。

二、维护四件事：体检、分析、整理、看日志

一体检：PRAGMA integrity_check 回 ok 就说明页结构没坏。 库打不开时先跑它，比猜快得多。 MySQL 对应 CHECK TABLE。 二分析：ANALYZE 收集「每张表多少行、索引选择性如何」的统计信息， 查询优化器靠它决定走不走索引。 数据量大变之后要重跑，否则它还在按老数据做计划。 三整理：删了数据文件不变大，因为页被标成空闲、留着等复用， 这叫碎片。 VACUUM 重写整个文件把空洞挤掉，MySQL 对应 OPTIMIZE TABLE。 四看日志：错误日志（起不来先看它）、慢查询日志（找慢 SQL）、 二进制日志（时间点恢复、主从复制都靠它）。

三、VACUUM 的两个代价

一是要两倍空间：它是「另写一份新的，再换掉旧的」，磁盘不够就会失败。 二是要锁一段时间：整库重写，期间写操作基本做不了。 别在高峰期跑，大库要挑维护窗口。 另外它不是万能的： 碎片整理只回收空间，不会让索引变快。 查询慢是另一回事，得看执行计划。

四、诊断启动问题与备份三条铁律

起不来的时候顺序是固定的：先看错误日志，再看配置有没有被改， 再确认磁盘满没满、权限对不对。 九成的「起不来」是后两条。 铁律一：定期且自动。 靠人记得的备份，等于没有备份。 铁律二：异地存一份。 库和备份在同一块盘上，盘坏了一起没。 顺手加密，备份文件本身就是全量数据。 铁律三：定期演练恢复。 没恢复过的备份不算备份—— 很多事故都是在「以为有备份」的那天才发现备份是空的。

五、小结

教材小结：备份分逻辑备份与物理备份；日常维护包括检查表、分析表、整理碎片与查看日志； 启动出问题先看错误日志。 本课补三条：一是删数据不会让文件变小，要 VACUUM 才回收；二是 VACUUM 要两倍空间且会锁库； 三是备份不演练恢复就等于没有。

六、动手 Demo：两万行、删一半、再挤干

Demo 六段：造一个两万行的真库文件，量大小；删掉一半再看大小；VACUUM 挤干再量； 跑完整性检查与分析；最后做一次真备份。 盯住那三个字节数：413,696 → 413,696 → 212,992。 中间那个没变，就是「碎片」这件事的全部证据。

import sqlite3, os
con = sqlite3.connect("demo29.db")

# 先造两万行，量一下文件多大
con.execute("CREATE TABLE big (id INTEGER PRIMARY KEY, val INTEGER)")
con.executemany("INSERT INTO big (val) VALUES (?)",
                 [(i % 97,) for i in range(20000)])
con.commit()
print(os.path.getsize("demo29.db"))     # 413696

# 删掉一半，文件不会变小：页被标记成空闲，还留着
con.execute("DELETE FROM big WHERE id % 2 = 0")
con.commit()
print(os.path.getsize("demo29.db"))     # 还是 413696

# VACUUM 重写整个文件，只留有用页
con.execute("VACUUM")
print(os.path.getsize("demo29.db"))     # 212992

# 体检：页结构完整性 + 统计信息
con.execute("PRAGMA integrity_check")
con.execute("ANALYZE")
con.execute("SELECT COUNT(*) FROM sqlite_stat1")

# 备份：接口级复制，不会拷到「一半旧一半新」
bak = sqlite3.connect("demo29.bak")
con.backup(bak)
print(os.path.getsize("demo29.bak"))    # 217088

实跑输出：

--------------------------------------------------------------------
第二十九课：数据库维护
--------------------------------------------------------------------

  【一】先造一个真实的库文件来折腾
  SQL> SELECT COUNT(*) AS n FROM big
  n
  ------
  20000
  （1 行）
  **两万行，库文件的逻辑大小 413,696 字节（约 404 KB）。**

  【二】删了一半，文件一点没变小
  SQL> SELECT COUNT(*) AS n FROM big
  n
  ------
  10000
  （1 行）
  **删掉一万行，文件还是 413,696 字节，一个字节都没少。**
  **原因**：SQLite 删数据只是把页标记成「空闲」，
  **页还留着，等着下次插入复用。** **MySQL 的 InnoDB 也一样，这叫「碎片」。**

  【三】VACUUM：把空洞挤掉
  **VACUUM 之后 212,992 字节，比原来小了 48.5%。**
  **它就是「重写一遍整个文件、只留有用页」**，所以磁盘够、**而且过程中要两倍空间**。
  **别在高峰期跑，它会把库锁住一段时间。**
  **MySQL 的对应命令是 `OPTIMIZE TABLE 表名`**，思路一样。

  【四】体检：integrity_check 与 ANALYZE
  SQL> PRAGMA integrity_check
  integrity_check
  ----------------
  ok
  （1 行）
  **回 ok 就说明页结构没坏。** 这是排查「库打不开」时的第一条命令，**比猜快得多。**
  SQL> SELECT COUNT(*) AS n FROM sqlite_stat1
  n
  --
  1
  （1 行）
  **ANALYZE 收集统计信息**（每张表多少行、索引的选择性），
  **查询优化器靠它决定走不走索引。** 数据量大变之后跑一次，计划才会更新。

  【五】备份：真的复制一份出来
  SQL> SELECT COUNT(*) AS n FROM big
  n
  ------
  10000
  （1 行）
  **`con.backup(目标连接)` 就是把整个库复制过去**，**备份文件 217,088 字节，里面一万行一行不少。**
  **它比「复制文件」安全**：复制文件时如果正好有写入，
  **拷走的一半旧一半新，恢复出来是坏的。** backup 接口会处理一致性。

  【六】MySQL 侧的常用手段（原码，不执行）
  `mysqldump -u root -p crashcourse > backup.sql`  **逻辑备份，导出 SQL**
  `mysql -u root -p crashcourse < backup.sql`  **恢复**
  `CHECK TABLE orders;`  `ANALYZE TABLE orders;`  `OPTIMIZE TABLE orders;`
  **日志三件套**：错误日志（起不来先看它）、慢查询日志（找慢 SQL）、
  **二进制日志（能做时间点恢复，也能做主从复制）。**

  【七】备份的三条铁律
  **一，定期且自动。** 靠人记得的备份，等于没有备份。
  **二，异地存一份。** 库和备份在同一块盘上，盘坏了一起没。
  **三，定期演练恢复。** **没恢复过的备份不算备份**——很多事故是在「以为有备份」的那天才发现的。

第一段两万行，413,696 字节（约 404 KB）。这是基准。 第二段删掉一万行，还是 413,696，一个字节都没少——页被标成空闲但没归还。 第三段 VACUUM 之后 212,992，小了 48.5%。 代价是两倍空间加一段锁库时间。 第四段体检。 integrity_check 回 ok；ANALYZE 之后 sqlite_stat1 里有了一行统计。 第五段备份。 217,088 字节，里面一万行一行不少。 注意它比原库还大一点——备份文件不带空闲页，但页布局不同。 第六段 MySQL 手段。 mysqldump 导出 SQL、mysql < backup.sql 恢复， CHECK／ANALYZE／OPTIMIZE TABLE 三件套。

30 秒实验

  1. 删掉全部行再 VACUUM，文件会变成多大？会比 212,992 小吗？
  2. 不跑 ANALYZE 就建索引，统计信息表里有几行？
  3. （先想再跑）备份文件 217,088 字节，比 VACUUM 后的 212,992 大，为什么？

常见误区

  1. 以为删数据文件会变小。 只是标记空闲，要 VACUUM 或 OPTIMIZE 才回收。
  2. 用复制文件代替备份。 有写入时拷到的是一半旧一半新，恢复出来是坏库。
  3. 备份了但从不演练恢复。 没验证过的备份，出事那天才知道不能用。
自测三问（点开对答案） 1. 逻辑备份和物理备份各有什么优缺点？ —— 逻辑备份导出 SQL，跨版本、可读可改，但慢；物理备份拷数据文件，快，但通常绑版本与平台。 2. 为什么删了数据文件却不变小？ —— 删除只是把页标记成空闲留着复用，空间没还给操作系统，这就是碎片。 3. 备份的三条铁律是什么？ —— 定期且自动、异地存一份、定期演练恢复。

库修好了、备份也齐了， 最后一章回答那个总被追问的问题：为什么这条 SQL 慢？ 性能——全书收尾。

相关笔记