04 - SQL 与表结构优化

索引是最重要的一招，但不是全部。同样的数据、同样的索引，SQL 写得好不好、表结构设计得合不合理，性能可能差好几倍。这一章讲这些「不花钱、改改写法和设计就能提速」的手段。

一句话先记住：很多性能问题不是数据库不行，是 SQL 写得烂、表设计得糙。

4.1 SQL 写法优化

① 只查需要的列，别用 SELECT *

-- ❌ 取了一堆用不上的列，还可能破坏覆盖索引
SELECT * FROM users WHERE id = 1;

-- ✅ 只取需要的，网络传输少、可能用上覆盖索引
SELECT id, name FROM users WHERE id = 1;

② 用 LIMIT 限制返回行数

-- 只需要前 10 条就别把 10 万条全查出来
SELECT id, title FROM articles ORDER BY created_at DESC LIMIT 10;

③ 避免在 WHERE 里对列做运算（会导致索引失效）

见第 02 章。把函数/运算放到「值」那边，让列保持干净。

④ 深分页问题（大 OFFSET 很慢）

-- ❌ 第 10000 页：数据库要先扫描并丢弃前 100000 行，很慢
SELECT * FROM articles ORDER BY id LIMIT 10 OFFSET 100000;

-- ✅ 用「上一页最后一条的 id」做游标（键集分页），直接定位
SELECT * FROM articles WHERE id > 上次最后的id ORDER BY id LIMIT 10;

深分页是列表接口常见的坑。OFFSET 越大越慢，因为它要「数着跳过」前面所有行。改用「游标/键集分页」能保持恒定速度。

⑤ 用 EXISTS / JOIN 代替低效的子查询

-- ⚠️ 某些相关子查询会对每行都执行一次，很慢
SELECT * FROM users u
WHERE (SELECT COUNT(*) FROM orders o WHERE o.user_id = u.id) > 0;

-- ✅ 改用 EXISTS（找到一条就停）或 JOIN，通常更快
SELECT * FROM users u WHERE EXISTS (
  SELECT 1 FROM orders o WHERE o.user_id = u.id
);

⑥ 批量操作代替循环单条

❌ 在应用里循环，一条条 INSERT 1000 次 → 1000 次网络往返 + 1000 个小事务
✅ 一条 INSERT 插入 1000 行（批量），或用 COPY → 一次搞定，快几十倍

4.2 避免大事务与长事务

大事务（一次改超多行）/ 长事务（开着很久不提交）的危害：
  · 长时间持有锁 → 阻塞别人（见《数据库锁》系列）
  · 在 PostgreSQL 里，长事务会让 MVCC 的旧版本无法被 VACUUM 回收 → 表膨胀
  · 一旦回滚，代价巨大

建议：
  · 事务尽量短小、快进快出
  · 别在事务里做耗时操作（调外部 API、等用户输入、大量计算）
  · 超大批量操作，分批提交（比如每 1000 行提交一次）

4.3 表结构优化

① 选对字段类型（小而准）

· 能用 INT 就别用 BIGINT，能用小类型就别用大的 → 省空间、索引更小更快
· 时间用 timestamp/timestamptz，别用字符串存日期
· 金额用 NUMERIC（精确），别用 float（有精度误差）
· 状态/枚举值，用小整数或专门的枚举类型，别存长字符串

字段越小，一行数据越小，一个数据页能放的行越多，同样的查询要读的页就越少 → 越快。

② 范式 vs 反范式

范式化（拆表，减少冗余）：
  · 优点：数据不重复、更新方便、一致性好
  · 缺点：查询时要 JOIN 多张表，可能变慢

反范式化（故意冗余，减少 JOIN）：
  · 优点：查询快，不用 JOIN
  · 缺点：数据冗余、更新时要维护多处一致性

例子：订单表里冗余存一份「用户名」
  → 查订单列表时不用再 JOIN 用户表
  → 代价：用户改名时，要同步更新订单表里的冗余名字

权衡：一般先规范化设计；当某个查询因为 JOIN 太多而慢、且该数据读远多于写时，才考虑「适度反范式」用冗余换查询速度。

③ 大字段拆分

一张表里有个很大的字段（如文章正文 TEXT、大 JSON）：
  → 查列表时其实不需要正文，却因为它导致每行很大、扫描变慢
  → 把大字段拆到单独的表，主表保持"苗条"，列表查询更快

4.4 定期维护（PostgreSQL）

· ANALYZE：更新统计信息，让优化器估得准（选对执行计划）
· VACUUM：回收 MVCC 产生的死元组，防止表膨胀
  （autovacuum 一般会自动做，高频更新的表要留意够不够及时）
· REINDEX：索引用久了可能膨胀，必要时重建

这些是 DBA 层面的维护，了解「它们在解决什么问题」即可：让统计准、让表不膨胀、让索引保持高效。

本章小结

下一章预告：单库层面的优化（索引、SQL、表结构）做完还不够扛，就该借助「外部力量」了——读写分离和缓存，给数据库减负。

上一章 ← 03 - 用 EXPLAIN 读懂执行计划 ｜ 下一章 → 05 - 读写分离与缓存