数据库

多数后端性能问题的终点都在数据库：一条没走索引的查询能把服务打垮，一次没想清楚的分片键能让扩容变成重写。本文按"索引 → 事务 → 优化 → 拆分"展开，所有结论尽量配上可复现的实测（SQLite 3.32.2 / Python 3.7.9，10 万行数据），并标注 MySQL/PostgreSQL/MongoDB 的差异。

一、数据库怎么选

类型	代表	强项	典型场景
关系型	MySQL、PostgreSQL	事务、约束、SQL 表达力、生态成熟	业务主库（订单、用户、账务）
文档型	MongoDB	灵活 schema、嵌套结构天然	内容/商品详情、快速迭代的业务对象
KV	Redis、RocksDB	极低延迟	缓存、会话、计数（见缓存）
列存	ClickHouse、Doris	海量数据聚合分析	报表、日志分析、用户行为
搜索	Elasticsearch	倒排索引、全文检索、聚合	搜索、模糊匹配、日志检索
图	Neo4j	关系遍历	社交关系、风控关联、知识图谱
时序	InfluxDB、TDengine	时间序列压缩与降采样	监控指标、IoT
向量	pgvector、Milvus	相似性检索	RAG 检索（见 RAG 与 Agent）

默认建议：业务主库用 PostgreSQL 或 MySQL（事务与生态最重要），需要全文/聚合/时序时再引入专用库，而不是让一个数据库包打天下。引入第二个存储前先问：能不能用索引、物化视图或只读副本解决。

MySQL 与 PostgreSQL 的关键差异：

维度	MySQL	PostgreSQL
索引类型	B+ 树为主（InnoDB），8.0 支持函数索引、隐藏索引	B 树 + GIN/GiST/BRIN/Hash，支持部分索引与表达式索引
JSON	JSON 类型，支持部分函数索引	`jsonb`（二进制、可索引、支持 GIN），表达力更强
复制	基于 binlog 的逻辑复制成熟	物理流复制 + 逻辑复制，支持同步/半同步与级联
DDL	8.0 支持部分 instant DDL	事务性 DDL，多数 ALTER 可回滚、在线加索引能力强
生态	云厂商与中间件兼容性最广	扩展丰富（PostGIS、Timescale、pgvector）

二、索引

2.1 为什么主流是 B+ 树

结构	查询	范围查询	写入	适用
哈希	O(1)	不支持	O(1)	等值查询（Redis、Memory 引擎）
二叉搜索树	O(log n)	支持	可能退化成链表	内存中
B 树	O(log n)	中序遍历	分裂合并	老版本索引
B+ 树	O(log n)	叶子节点链表，范围查询极快	稳定	关系型数据库默认
LSM 树	读放大	支持	追加写，写吞吐高	写多读少（RocksDB、ClickHouse、HBase）

B+ 树的关键设计：非叶子节点只存键值不存数据，因此一个节点能容纳更多键，树高更低（千万行数据树高通常 3～4 层，即 3～4 次磁盘 IO）；叶子节点用链表相连，范围扫描不需要回到根节点。

2.2 索引效果实测

10 万行 users 表，按 name 等值查询（SQLite 3.32.2，取 5 次均值）：

text

无索引  执行计划: SCAN TABLE users                        平均 8.947 ms
建索引后 执行计划: SEARCH TABLE users USING INDEX idx_name  平均 0.043 ms
提速倍数: 210x

索引的本质是用空间与写入成本换查询速度：一次全表扫描 8.9ms，走索引 0.043ms。数据量越大差距越夸张（全表扫描是 O(n)，索引是 O(log n)）。

2.3 最左前缀原则（复合索引）

复合索引 (city, age) 相当于先按 city 排序、city 相同再按 age 排序，因此必须从最左列开始连续匹配：

text

WHERE city = 'city7'                  → SEARCH USING INDEX idx_city_age (city=?)           ✅ 用上
WHERE city = 'city7' AND age = 30     → SEARCH USING INDEX idx_city_age (city=? AND age=?) ✅ 全用上
WHERE age = 30                        → SCAN TABLE users                                   ❌ 跳过最左列
WHERE city = 'city7' AND name='user7' → SEARCH USING INDEX idx_city_age (city=?)           ⚠️ 只用到 city

2.4 覆盖索引：不回表

text

SELECT city, age FROM users WHERE city='city7' → SEARCH USING COVERING INDEX idx_city_age  ✅ 索引内取完
SELECT *         FROM users WHERE city='city7' → SEARCH USING INDEX idx_city_age           ⚠️ 需回表取整行

覆盖索引省掉了"按主键回表取整行"的随机 IO，是成本最低的优化之一——把查询列收窄到业务真正需要的字段，比加索引更有效。

2.5 索引什么时候会失效

SQLite 3.32.2 实测（10 万行，name 列已有普通索引）：

写法	执行计划	说明
`name = 'user99999'`	SEARCH USING INDEX	等值可用
`name LIKE 'user9999%'`	SCAN TABLE	⚠️ SQLite 的 LIKE 默认大小写不敏感，普通 BINARY 索引用不上
`PRAGMA case_sensitive_like=ON` 后同上	SEARCH USING COVERING INDEX	修复方式一
建 `name COLLATE NOCASE` 索引后	SEARCH USING COVERING INDEX	修复方式二
`name LIKE '%99999'`	SCAN TABLE	后缀/中间模糊匹配必然失效
`length(name) = 9`	SCAN TABLE	对列做函数运算导致失效
`name >= 'a' AND name < 'b'`	SEARCH USING INDEX	用范围条件替代 LIKE 前缀，通用做法

MySQL/PostgreSQL 的 LIKE '前缀%' 在标准 collation 下通常能用上索引（与上面 SQLite 的表现不同），但规律一致：对列做了函数运算、隐式类型转换、或不是最左前缀，索引就废了。不确定的时候看执行计划，别靠背结论。

其他常见失效与低效写法：

隐式转换：字符串列用数字比较（WHERE phone = 13800138000）；
字符集/排序规则不一致的表做 JOIN；
OR 连接不同列的条件（可改 UNION ALL 或分别建索引）；
!=、<>、NOT IN 通常走不上索引（区分度低时全表更快是被优化器故意选的）；
优化器判断"回表成本高于全表扫描"时会主动放弃索引（小表、低区分度列常见）。

2.6 索引不是越多越好

每个索引都是一棵需要维护的 B+ 树：写放大（INSERT/UPDATE/DELETE 要同步更新所有索引）；
占空间：索引体积常与数据同量级（本次 10 万行 2.6 MB 数据，额外索引同样占空间）；
低区分度列（性别、状态位）单独建索引收益极低，通常作为复合索引的后缀列；
定期清理冗余索引：被更左前缀覆盖的索引（idx(a) 与 idx(a,b) 中的前者）可下线。

三、事务与隔离级别

3.1 ACID 与四类隔离级别

隔离级别	脏读	不可重复读	幻读	说明
读未提交 Read Uncommitted	可能	可能	可能	几乎不用
读已提交 Read Committed	不会	可能	可能	Oracle/PostgreSQL/SQL Server 默认
可重复读 Repeatable Read	不会	不会	可能（MySQL InnoDB 靠 MVCC + 间隙锁基本避免）	MySQL InnoDB 默认
串行化 Serializable	不会	不会	不会	全部加锁，吞吐最低

脏读：读到别人未提交的数据；
不可重复读：同一事务内两次读同一行，值被别人改了（针对 UPDATE）；
幻读：同一事务内两次范围查询，行数变了（针对 INSERT/DELETE）。

3.2 MVCC：读不加锁的秘密

MVCC（多版本并发控制）让"读"和"写"互不阻塞：每行数据保留多个版本，读事务看到的是启动时的一致性快照。

text

InnoDB 实现要点：
- 每行有隐藏列：DB_TRX_ID（最后修改事务 ID）、DB_ROLL_PTR（回滚指针）
- 修改时不覆盖旧值，而是写入新版本并链接到 undo log 形成版本链
- 读时按 ReadView（活跃事务列表）沿版本链找到"自己该看到的那个版本"
- 因此：快照读（普通 SELECT）不加锁；当前读（SELECT ... FOR UPDATE / UPDATE / DELETE）加锁

3.3 事务实测（SQLite WAL 模式）

text

journal_mode: wal

① 原子性：UPDATE age=100 提交后再开事务 UPDATE age=999，中途异常
   → rollback 后 age 仍为 100（一致）

② 隔离性：连接 A 开启事务 UPDATE age=777（未提交）
   → 连接 B 仍能读到 age=100（读不被写阻塞，看到的是已提交值）

③ 写写冲突：连接 A 未提交时，连接 B 尝试改同一行
   → OperationalError: database is locked

④ 连接 A 回滚后，连接 B 读到 age=100

要点：

读不阻塞写、写不阻塞读是 MVCC/WAL 的价值，但写写仍然互斥（同一行）；
长事务是 MVCC 的敌人：版本链无法清理，undo log 膨胀，还可能撞上连接数/锁等待；
明确事务边界：ORM 的隐式事务容易让"一个循环里 N 次提交"变成性能灾难（可批量提交）。

四、SQL 优化实战

4.1 排查流程

text

1. 定位慢 SQL：慢查询日志（MySQL slow log / pg_stat_statements）
2. 看执行计划：EXPLAIN（MySQL 8.0 用 EXPLAIN ANALYZE 看真实耗时）
3. 关注三件事：是否走索引（type/key）、扫描行数（rows）、是否有 filesort / 临时表
4. 改写法或加索引 → 再测 → 用真实数据量回归

4.2 深分页：OFFSET 越大越慢

10 万行表实测（取 20 次均值）：

text

OFFSET 99000         平均 2.979 ms   执行计划: SCAN TABLE users
游标 id > 99000      平均 0.051 ms   执行计划: SEARCH ... USING INTEGER PRIMARY KEY (rowid>?)
差距: 58.0x

LIMIT 10 OFFSET 99000 需要先扫描并丢弃前 99000 行——偏移量越大越慢。解法：

sql

-- 游标分页（keyset pagination）：记住上一页最后一条的排序键
SELECT * FROM users WHERE id > :last_id ORDER BY id LIMIT 10;

-- 或延迟关联：先用覆盖索引拿到主键，再回表取数据
SELECT * FROM users u JOIN (SELECT id FROM users ORDER BY id LIMIT 10 OFFSET 99000) t ON u.id = t.id;

4.3 N+1 查询

实测（2 万条订单，查 500 个用户的订单数）：

text

循环 500 次单查：566.3 ms
1 次批量分组查询：7.6 ms
差距: 74.7x

sql

-- 反例：每查一个用户就发一条 SQL
SELECT COUNT(*) FROM orders WHERE user_id = 1;   -- ×500 次

-- 正解：一次批量取回
SELECT user_id, COUNT(*) FROM orders WHERE user_id IN (1,2,...,500) GROUP BY user_id;

N+1 是 ORM 最常见的性能陷阱（懒加载触发）。解法：预加载/JOIN/批量查询，并在开发期开启 SQL 日志与 N+1 检测。

4.4 其他高频优化点

问题	做法
`SELECT *`	只取需要的列（顺带提升覆盖索引命中率）
大批量 INSERT	批量提交（每批 500～1000 条），避免逐条自动提交
JOIN 慢	小表驱动大表、确保关联列有索引且类型/字符集一致、避免多层嵌套子查询
大表 DDL	用在线 DDL 工具（gh-ost / pt-online-schema-change / pg_repack），避免长时间锁表
统计不准	定期 `ANALYZE`，让优化器拿到准确的行数分布
大事务	拆分成小批次，避免长锁与 undo log 膨胀
计数	`COUNT(*)` 在大表上仍要扫索引，高频计数用缓存或近似值

五、分库分表与迁移

拆分方式	做法	适用
垂直拆分（分库）	按业务域拆到不同库	先做这个：解耦与隔离故障
垂直拆分（分表）	大字段/低频字段拆到副表	热点行太宽
水平拆分（分表）	同一张表按分片键拆成 N 张	单表数据量过大（经验值：千万～亿级需评估）
水平拆分（分库）	分表后再分布到不同实例	单实例写入/容量瓶颈

分片键决定一切：

选查询最常带的维度（如 user_id、tenant_id），让绝大多数查询落在单分片；
避免用单调递增的时间戳做分片键（写入热点全压在一个分片，可用"时间戳 + 业务 ID 哈希"复合）；
分片后要放弃的能力：跨片 JOIN、跨片事务、全局唯一约束（用分布式 ID，见系统设计）、跨片排序分页。

迁移的稳妥路径（不停机）：

text

1. 双写：应用同时写旧库与新库（新库写入失败要告警但不影响主流程）
2. 全量同步：导数工具把历史数据搬到新库
3. 增量追平：消费 binlog 补齐双写期间遗漏/失败的数据
4. 数据校验：抽样 + 全量比对（行数、 checksum）
5. 读流量灰度切到新库 → 观察 → 停旧库写入 → 下线旧库

六、检查清单

每张表的索引都对应明确的查询，无冗余与重复索引；
复合索引符合最左前缀，字段顺序按"等值列 → 排序列 → 范围列"排列；
核心查询走覆盖索引或至少避免大范围回表；
慢查询有监控与告警，定期（每周）复盘 Top SQL；
事务边界明确，无长事务、无循环里逐条提交；
隔离级别与业务一致（默认别乱改，改了要说明原因）；
分页用游标或延迟关联，避免深 OFFSET；
无 N+1 查询（ORM 开启检测，Code Review 重点看）；
大表 DDL 用在线工具，且先在预发验证耗时与锁；
有备份与恢复演练（备份没验证过等于没有）；
分片键经过查询模式验证，扩容路径可执行。

七、常见坑速查

在索引列上用函数：WHERE DATE(created_at) = '2026-09-02' 让索引失效——改成范围条件；
隐式类型转换：字符串列传数字，索引失效且结果可能不符合预期；
模糊查询以 % 开头：必然全表扫描，需要全文检索就用 ES 或倒排索引；
OR 连接不同列：索引多半失效，改 UNION ALL 或调整索引设计；
索引越多越好：写入变慢、空间翻倍，低区分度列建索引纯属浪费；
深分页 OFFSET：页码越大越慢（实测 58x 差距）；
N+1 查询：ORM 懒加载悄悄放大 SQL 数量（实测 74.7x 差距）；
长事务：持锁时间长、undo log 膨胀、主从延迟加剧；
大事务一次性批量更新：锁住大量行、产生大量 undo，应分批（如每批 1000 条 + sleep）；
在业务高峰做 DDL：即使支持在线 DDL，也要评估对主从延迟与磁盘 IO 的影响；
用 SELECT *：浪费带宽与内存，还挡住了覆盖索引；
分片键拍脑袋：选了不常查询的维度，导致所有查询都要跨片聚合；
不做备份恢复演练：真正需要恢复时才发现备份不完整/耗时超预期。

状态与参考

状态：已收录（2026-09-02，由后端领域规划清单「数据库｜索引原理、事务与隔离级别、SQL 优化、分库分表」由占位页转为正式内容）。
版本：索引效果（210x）、最左前缀、覆盖索引、LIKE 失效与修复、深分页（58x）、N+1（74.7x）、事务原子性与 WAL 并发均为 SQLite 3.32.2（Python 3.7.9 sqlite3 模块）+ 10 万行数据实测，输出可直接复现；MySQL/PostgreSQL/MongoDB 的差异为通用结论，未在本机实测（本机无 MySQL/PostgreSQL 实例）。
参考：MySQL 8.0 参考手册 - 优化、PostgreSQL 文档 - 索引与执行计划、MongoDB 数据建模、Use The Index, Luke!、DDIA 第 3 章存储与检索。
阅读联动：缓存与数据库的一致性策略见缓存；分片键与分布式 ID 见系统设计；异步解耦与最终一致见消息队列；连接池与超时配置见微服务。

下一步

[ ] 安装 MySQL/PostgreSQL 实例，把本文的索引与事务实测在真实主库上复跑并补充执行计划截图
[ ] 补一篇 MongoDB 文档建模（嵌入 vs 引用）与关系型迁移对照
[ ] 用 sysbench 做一次压测，量化连接池大小与索引对吞吐的影响

写作规范请参阅领域概览。

数据库 ​

一、数据库怎么选 ​

二、索引 ​

2.1 为什么主流是 B+ 树 ​

2.2 索引效果实测 ​

2.3 最左前缀原则（复合索引） ​

2.4 覆盖索引：不回表 ​

2.5 索引什么时候会失效 ​

2.6 索引不是越多越好 ​

三、事务与隔离级别 ​

3.1 ACID 与四类隔离级别 ​

3.2 MVCC：读不加锁的秘密 ​

3.3 事务实测（SQLite WAL 模式） ​

四、SQL 优化实战 ​

4.1 排查流程 ​

4.2 深分页：OFFSET 越大越慢 ​

4.3 N+1 查询 ​

4.4 其他高频优化点 ​

五、分库分表与迁移 ​

六、检查清单 ​

七、常见坑速查 ​

状态与参考 ​

下一步 ​

数据库

一、数据库怎么选

二、索引

2.1 为什么主流是 B+ 树

2.2 索引效果实测

2.3 最左前缀原则（复合索引）

2.4 覆盖索引：不回表

2.5 索引什么时候会失效

2.6 索引不是越多越好

三、事务与隔离级别

3.1 ACID 与四类隔离级别

3.2 MVCC：读不加锁的秘密

3.3 事务实测（SQLite WAL 模式）

四、SQL 优化实战

4.1 排查流程

4.2 深分页：OFFSET 越大越慢

4.3 N+1 查询

4.4 其他高频优化点

五、分库分表与迁移

六、检查清单

七、常见坑速查

状态与参考

下一步