面试题集锦:MySQL / PostgreSQL 问答大全

Hello,我是小兔大白糖。 这篇整理面试口述与高频追问要点,方便对照站内同主题通识文。
Generated by kimi-k2.6 model
面向 Senior/Staff 级别后端开发岗位的数据库系统性面试准备文档。涵盖 MySQL 与 PostgreSQL 核心原理、索引、事务、锁、优化、高可用、选型对比,深度解析原理 + 实战 + 源码。
目录
- MySQL 索引原理
- InnoDB 存储引擎
- MySQL 事务与隔离级别
- MySQL 锁机制
- MySQL SQL 优化与执行计划
- MySQL 分库分表与读写分离
- MySQL 主从复制与高可用
- PostgreSQL 核心架构与特性
- PostgreSQL 索引体系
- PostgreSQL MVCC 与事务
- PostgreSQL 高级特性
- MySQL vs PostgreSQL 选型对比
1. MySQL 索引原理
TL;DR
索引是数据库的查询加速器——理解 B+ 树结构、聚簇索引与非聚簇索引的区别、覆盖索引和最左前缀原则,是 SQL 优化的基础。
1.1 B+ 树索引结构
Situation(场景)
面试官不会问"什么是索引",而是问"为什么 MySQL 用 B+ 树而不是红黑树/哈希表"——这是在考察你对磁盘 IO 和数据结构的深层理解。
Theory(理论)
B+ 树核心特性:
B+ 树(m 阶)
├── 非叶子节点只存索引键值 + 子节点指针
├── 叶子节点存完整数据(聚簇索引)或主键值(二级索引)
├── 叶子节点通过双向链表连接(支持范围查询)
└── 所有叶子节点在同一层(树高度低,IO 次数少)
为什么不用红黑树?
- 红黑树高度 ≈ log₂N,B+ 树高度 ≈ logₘN(m 通常 100+)
- 1000 万数据:红黑树高度 ≈ 24,B+ 树高度 ≈ 3
- 红黑树每次查找可能触发磁盘随机 IO,B+ 树一次加载一页(16KB),顺序 IO
为什么不用哈希表?
- 哈希只支持等值查询,不支持范围查询和排序
- 哈希冲突影响性能
- 哈希索引在 MySQL 中仅 Memory 引擎支持,InnoDB 的 Adaptive Hash Index 是辅助
页(Page)结构:
InnoDB 页(默认 16KB)
┌─────────────────┬──────────────────────┐
│ File Header │ 页号、上下页指针等 │ 38B
├─────────────────┼──────────────────────┤
│ Page Header │ 页内记录数、空闲空间 │ 56B
├─────────────────┼──────────────────────┤
│ Infimum │ 最小记录(虚拟) │
├─────────────────┼──────────────────────┤
│ User Records │ 实际数据记录 │
├─────────────────┼──────────────────────┤
│ Free Space │ 空闲空间 │
├─────────────────┼──────────────────────┤
│ Page Directory │ 页目录(槽位数组) │
├─────────────────┼──────────────────────┤
│ File Trailer │ 校验和 │ 8B
└─────────────────┴──────────────────────┘
Page Directory:将页内记录分组,每组一个槽位指向组内最大记录的偏移
查找时先用二分法定位槽位,再在组内线形查找
Practice(实践)
-- 查看索引页使用情况
SHOW TABLE STATUS LIKE 'user';
-- Rows / 每页记录数 ≈ 页数
-- 计算索引高度
-- 主键为 BIGINT(8B),指针 6B,每页可存 16KB/(8+6)B ≈ 1170 个键
-- 三层 B+ 树:1170 * 1170 * 16 ≈ 2190 万条记录// 业务场景:理解索引页后,优化批量插入
type UserBatchInsert struct {
db *sql.DB
}
// 按主键顺序批量插入,减少页分裂
func (s *UserBatchInsert) InsertOrdered(ctx context.Context, users []User) error {
// 先按主键排序
sort.Slice(users, func(i, j int) bool {
return users[i].ID < users[j].ID
})
tx, err := s.db.BeginTx(ctx, nil)
if err != nil {
return err
}
defer tx.Rollback()
stmt, err := tx.PrepareContext(ctx, "INSERT INTO user(id, name, email) VALUES (?, ?, ?)")
if err != nil {
return err
}
defer stmt.Close()
for _, user := range users {
if _, err := stmt.ExecContext(ctx, user.ID, user.Name, user.Email); err != nil {
return err
}
}
return tx.Commit()
}常见误区
| ❌ 误区 | ✅ 正确理解 |
|---|---|
| 索引越多越好 | 每个索引都增加写开销和存储空间,需要权衡 |
| B+ 树只适合等值查询 | B+ 树叶子节点链表天然支持高效范围查询 |
| 索引字段重复率低才有效 | 重复率低(区分度高)确实更好,但重复率高的字段组合索引可能有效 |
| 主键必须用自增 ID | 自增 ID 减少页分裂,但 UUID 也有场景(分布式 ID) |
Extension(延伸)
- 追问:为什么 InnoDB 一页是 16KB? → 平衡内存利用率和磁盘 IO,现代磁盘扇区 4KB,16KB 是 4 的倍数对齐
- 追问:B+ 树插入导致页分裂怎么办? → 页分裂产生碎片,可通过 OPTIMIZE TABLE 重建表;自增主键顺序插入可避免
- 追问:MySQL 8.0 的降序索引有什么用? →
INDEX idx(a DESC, b ASC),避免 filesort,优化ORDER BY a DESC, b ASC
1.2 聚簇索引 vs 非聚簇索引
TL;DR
聚簇索引决定数据物理存储顺序——表只能有一个聚簇索引(主键),查询时尽量利用覆盖索引避免回表。
Theory(理论)
聚簇索引(Clustered Index)
├── 叶子节点存完整行数据
├── 表数据本身就是按主键排序的 B+ 树
├── 没有主键时:选第一个非空唯一索引 → 否则生成 6B 的隐藏 row_id
└── 查询主键时直接命中数据页
非聚簇索引(Secondary Index)
├── 叶子节点存【索引列 + 主键值】
├── 查询非主键列时:先查二级索引得主键 → 再回表查聚簇索引
└── 覆盖索引:查询列全在二级索引中,无需回表
回表(Lookup) vs 覆盖索引(Covering Index):
-- 表结构
CREATE TABLE user (
id BIGINT PRIMARY KEY,
name VARCHAR(50),
email VARCHAR(100),
age INT,
INDEX idx_name_email (name, email)
);
-- ❌ 需要回表:查询了 age,不在 idx_name_email 中
SELECT name, email, age FROM user WHERE name = 'Alice';
-- 执行过程:
-- 1. 走 idx_name_email,找到 name='Alice' 的主键 id
-- 2. 拿 id 去聚簇索引查完整行,获取 age
-- 3. 两次索引查找
-- ✅ 覆盖索引:查询列都在索引中
SELECT name, email FROM user WHERE name = 'Alice';
-- 执行过程:
-- 1. 走 idx_name_email,直接拿到 name 和 email
-- 2. 无需回表,一次索引查找Practice(实践)
-- 判断查询是否使用覆盖索引
EXPLAIN SELECT name, email FROM user WHERE name = 'Alice';
-- Extra 列显示 "Using index" → 覆盖索引,无回表
EXPLAIN SELECT name, email, age FROM user WHERE name = 'Alice';
-- Extra 列显示 "NULL" 或 "Using where" → 需要回表// 真实业务:利用覆盖索引优化列表查询
@Service
public class UserService {
@Autowired
private JdbcTemplate jdbcTemplate;
// ❌ 低效:SELECT * 导致回表
public List<User> listUsersBad(int page, int size) {
return jdbcTemplate.query(
"SELECT * FROM user ORDER BY create_time DESC LIMIT ? OFFSET ?",
new BeanPropertyRowMapper<>(User.class), size, page * size);
}
// ✅ 高效:只查索引列,利用覆盖索引
public List<UserSummary> listUsersGood(int page, int size) {
// id 是主键(聚簇索引),create_time 有索引
// 联合索引 (create_time, id) 可实现覆盖索引
return jdbcTemplate.query(
"SELECT id, create_time FROM user ORDER BY create_time DESC LIMIT ? OFFSET ?",
(rs, rowNum) -> new UserSummary(rs.getLong("id"), rs.getTimestamp("create_time")),
size, page * size);
}
}业界案例:美团订单列表查询优化——原查询 SELECT * 导致大量回表,改为只查索引列(订单 ID + 时间),列表展示时异步加载详情,QPS 从 2000 提升到 15000。
1.3 最左前缀原则与索引下推
TL;DR
联合索引的使用必须遵循最左前缀——查询条件必须从索引的最左列开始匹配;MySQL 5.6+ 的索引下推(ICP)减少回表次数。
Theory(理论)
最左前缀原则:
-- 联合索引 (a, b, c) 的 B+ 树是先按 a 排序,a 相同再按 b 排序,b 相同再按 c 排序
✅ 能用上索引:
WHERE a = 1
WHERE a = 1 AND b = 2
WHERE a = 1 AND b = 2 AND c = 3
WHERE a = 1 AND c = 3 -- a 用上,c 用不上(但 8.0 有索引跳跃扫描)
❌ 用不上索引:
WHERE b = 2 -- 缺少最左列 a
WHERE b = 2 AND c = 3 -- 缺少最左列 a
WHERE a > 1 AND b = 2 -- a 范围查询后,b 用不上(范围查询右侧列失效)索引下推(Index Condition Pushdown, ICP):
-- 索引 (name, age)
SELECT * FROM user WHERE name LIKE 'A%' AND age = 20;
-- 没有 ICP(5.6 之前):
-- 1. 从索引找到 name LIKE 'A%' 的所有主键
-- 2. 回表查完整行
-- 3. 在 Server 层过滤 age = 20
-- 有 ICP(5.6+):
-- 1. 从索引找到 name LIKE 'A%' 的记录
-- 2. 在索引层就判断 age = 20,不满足的直接过滤
-- 3. 只回表满足条件的记录
-- Extra 显示 "Using index condition"常见误区
| ❌ 误区 | ✅ 正确理解 |
|---|---|
WHERE a=1 OR b=2 能用上 (a,b) 索引 | OR 条件通常导致索引失效,需分别评估或使用 UNION |
| 联合索引 (a,b,c) 和单独索引 (a) 效果一样 | 联合索引可以减少回表,但 (a) 单列查询更紧凑 |
ORDER BY b, a 能用上 (a,b) 索引 | ORDER BY 也要遵循最左前缀,ORDER BY a, b 才能用上 |
| 索引列参与计算仍能用索引 | WHERE age + 1 = 20 会导致索引失效,应写为 WHERE age = 19 |
2. InnoDB 存储引擎
TL;DR
InnoDB 是 MySQL 的默认存储引擎——理解 Buffer Pool、Redo Log、Undo Log、Double Write 的协作机制,是理解 MySQL 事务和崩溃恢复的基石。
2.1 Buffer Pool
Situation(场景)
为什么内存越大 MySQL 性能越好?Buffer Pool 就是 InnoDB 的内存缓存区,理解其 LRU 算法和脏页刷盘机制,是调优的关键。
Theory(理论)
Buffer Pool 结构:
Buffer Pool(默认 128MB,生产通常 50-75% 物理内存)
├── Free List:空闲页链表
├── LRU List:数据页链表(冷热分离)
│ ├── Young 区(5/8):热数据,最近访问
│ └── Old 区(3/8):冷数据,首次加载放这里
├── Flush List:脏页链表(已修改但未刷盘)
└── 每个页 16KB,通过哈希表快速定位
LRU 改进算法:
- 传统 LRU:新页放头部,淘汰尾部
- 问题:全表扫描会把热数据全部挤出
- 改进:新页先放 Old 区头部,访问后再移到 Young 区
- innodb_old_blocks_pct = 37(Old 区占比)
- innodb_old_blocks_time = 1000(毫秒,Old 区停留时间)
脏页刷盘:
1. 用户线程修改数据 → 只改 Buffer Pool 中的页(脏页)
2. 后台线程(Page Cleaner)定期将脏页刷到磁盘
3. 检查点(Checkpoint):标记哪些 LSN 之前的脏页已刷盘
为什么不是直接写磁盘?
- 内存写速度是磁盘写的 10 万倍
- 顺序聚合写比随机写效率高
- 但刷盘有延迟,崩溃时可能丢数据 → Redo Log 解决
Practice(实践)
-- Buffer Pool 状态监控
SHOW ENGINE INNODB STATUS\G
-- Buffer pool hit rate 应 > 99%
-- 查看 Buffer Pool 大小和状态
SHOW VARIABLES LIKE 'innodb_buffer_pool_size';
SHOW STATUS LIKE 'Innodb_buffer_pool_%';
-- 生产配置建议
[mysqld]
innodb_buffer_pool_size = 16G -- 物理内存的 50-75%
innodb_buffer_pool_instances = 8 -- 多实例减少锁竞争(每个至少 1GB)
innodb_old_blocks_pct = 37
innodb_old_blocks_time = 1000业界案例:阿里巴巴 MySQL 实例 Buffer Pool 配置为物理内存的 70%(如 64GB 内存配 44GB Buffer Pool),配合 SSD 磁盘,Buffer Pool 命中率稳定在 99.9% 以上。
2.2 Redo Log vs Undo Log vs Binlog
TL;DR
Redo Log 保证崩溃恢复(物理日志),Undo Log 保证回滚和 MVCC(逻辑日志),Binlog 保证主从复制(逻辑日志)——三者协作构成 InnoDB 的完整事务体系。
Theory(理论)
三种日志对比:
| 维度 | Redo Log | Undo Log | Binlog |
|---|---|---|---|
| 作用 | 崩溃恢复 | 事务回滚、MVCC | 主从复制、数据恢复 |
| 层级 | 存储引擎层(InnoDB) | 存储引擎层 | Server 层 |
| 内容 | 物理日志(页修改) | 逻辑日志(反向操作) | 逻辑日志(SQL 语句/行变更) |
| 写入时机 | 事务执行时持续写 | 事务执行时持续写 | 事务提交时写 |
| 刷盘策略 | 循环写、固定大小 | 自动清理(Purge) | 追加写、可保留 |
| 格式 | 固定大小文件(ib_logfile) | 回滚段(Rollback Segment) | Statement/Row/Mixed |
Redo Log 工作原理:
WAL(Write-Ahead Logging)原则:
- 先写日志,再写磁盘
- 修改数据页前,必须先写 Redo Log
- 即使数据页没刷盘,只要 Redo Log 已持久化,就能恢复
Redo Log Buffer → OS Buffer → 磁盘
刷盘时机:
1. 事务提交时(默认)
2. Log Buffer 空间不足时(> 1/2)
3. 后台线程每秒刷盘
4. 正常关闭时
innodb_flush_log_at_trx_commit:
- 0:每秒刷盘(可能丢 1 秒数据)
- 1:每次事务提交刷盘(默认,最安全)
- 2:每次提交写 OS Buffer(系统崩溃可能丢,MySQL 崩溃不丢)
Undo Log 与 MVCC:
Undo Log 存储在回滚段(Rollback Segment)中:
- INSERT:Undo 存主键(删除时用)
- UPDATE:Undo 存修改前的完整行
- DELETE:Undo 存完整行(插入时用)
MVCC 读取时:
- 根据 ReadView 判断记录可见性
- 不可见时通过 Undo Log 链找到历史版本
- 每行记录有 trx_id 和 roll_pointer 指向 Undo Log
两阶段提交(2PC):
为什么需要两阶段提交?保证 Redo Log 和 Binlog 一致性
事务提交过程:
1. InnoDB 准备阶段:写 Redo Log(Prepare 状态)
2. Server 层写 Binlog
3. InnoDB 提交阶段:Redo Log 改 Commit 状态
崩溃恢复判断:
- Redo Log Prepare + Binlog 完整 → 提交事务
- Redo Log Prepare + Binlog 不完整 → 回滚事务
- Redo Log Commit → 已提交,无需处理
Practice(实践)
-- Redo Log 配置
SHOW VARIABLES LIKE 'innodb_log_%';
-- innodb_log_file_size = 1G -- 单个 Redo 文件大小
-- innodb_log_files_in_group = 2 -- Redo 文件个数
-- innodb_log_buffer_size = 16M -- Log Buffer
-- Binlog 配置
SHOW VARIABLES LIKE 'log_bin%';
SHOW VARIABLES LIKE 'binlog_format';
-- binlog_format = ROW -- 推荐 ROW 格式,记录行变更
-- sync_binlog = 1 -- 每次提交刷盘
-- binlog_row_image = FULL -- 记录变更前后的完整行// 业务场景:理解日志后,正确配置事务刷盘策略
type OrderRepository struct {
db *sql.DB
}
// 金融级场景:保证数据不丢
func (r *OrderRepository) CreateOrderCritical(ctx context.Context, order Order) error {
// 依赖 MySQL 配置:
// innodb_flush_log_at_trx_commit = 1
// sync_binlog = 1
_, err := r.db.ExecContext(ctx,
"INSERT INTO orders (id, amount, status) VALUES (?, ?, ?)",
order.ID, order.Amount, order.Status)
return err
}
// 非关键场景(日志、统计):牺牲一点持久性换性能
func (r *OrderRepository) CreateLogNonCritical(ctx context.Context, log Log) error {
// 可配合 MySQL 配置:
// innodb_flush_log_at_trx_commit = 2
// sync_binlog = 0
_, err := r.db.ExecContext(ctx,
"INSERT INTO operation_log (content, created_at) VALUES (?, NOW())",
log.Content)
return err
}常见误区
| ❌ 误区 | ✅ 正确理解 |
|---|---|
| Redo Log 和 Binlog 内容一样 | 完全不同,Redo 是物理日志,Binlog 是逻辑日志 |
| Undo Log 只在回滚时用 | Undo Log 还是 MVCC 读取历史版本的关键 |
| sync_binlog=0 性能最好但不可用 | 非关键业务可用,关键业务必须 sync_binlog=1 |
| Redo Log 越大越好 | 太大恢复慢,太小频繁 checkpoint,通常 1-2GB/文件 |
3. MySQL 事务与隔离级别
TL;DR
事务的 ACID 由 InnoDB 的 MVCC + Undo Log + Redo Log + 锁共同实现——理解四种隔离级别下的一致性问题(脏读/不可重复读/幻读)和 MVCC 实现,是事务调优的基础。
3.1 事务 ACID 与隔离级别
Situation(场景)
转账操作扣了 A 的钱但没加到 B 的账上——这是典型的原子性问题;读到未提交的数据——这是隔离性问题。面试官通过这些问题考察你对事务本质的理解。
Theory(理论)
ACID 实现机制:
| 特性 | 实现机制 |
|---|---|
| Atomicity(原子性) | Undo Log(回滚) |
| Consistency(一致性) | 约束、触发器、存储过程(业务层 + 数据库层) |
| Isolation(隔离性) | MVCC + 锁 |
| Durability(持久性) | Redo Log + Binlog |
四种隔离级别与问题:
| 隔离级别 | 脏读 | 不可重复读 | 幻读 | 实现方式 |
|---|---|---|---|---|
| READ UNCOMMITTED | ✅ 可能 | ✅ 可能 | ✅ 可能 | 直接读最新版本 |
| READ COMMITTED | ❌ 不会 | ✅ 可能 | ✅ 可能 | 每次查询生成 ReadView |
| REPEATABLE READ | ❌ 不会 | ❌ 不会 | ❌ 不会* | 事务开始时生成 ReadView |
| SERIALIZABLE | ❌ 不会 | ❌ 不会 | ❌ 不会 | 所有操作加排他锁 |
*MySQL InnoDB 的 REPEATABLE READ 通过 Gap Lock 解决了幻读(标准定义下 RR 仍有幻读)
Practice(实践)
-- 查看和设置隔离级别
SELECT @@transaction_isolation;
SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ;
-- 生产环境建议
[mysqld]
transaction_isolation = REPEATABLE-READ// Spring 事务配置
@Service
public class TransferService {
@Autowired
private AccountRepository accountRepository;
// 默认 REPEATABLE_READ
@Transactional(rollbackFor = Exception.class)
public void transfer(Long fromId, Long toId, BigDecimal amount) {
Account from = accountRepository.findByIdForUpdate(fromId);
Account to = accountRepository.findByIdForUpdate(toId);
from.setBalance(from.getBalance().subtract(amount));
to.setBalance(to.getBalance().add(amount));
accountRepository.save(from);
accountRepository.save(to);
}
}3.2 MVCC 多版本并发控制
TL;DR
MVCC 通过保存数据的历史版本,让读写操作互不阻塞——理解 ReadView、trx_id、roll_pointer 的协作,是理解 InnoDB 并发性能的核心。
Theory(理论)
隐藏字段:
每行记录除了用户定义的字段,还有三个隐藏字段:
┌─────────────┬───────────┬─────────────┐
│ DB_ROW_ID │ DB_TRX_ID │ DB_ROLL_PTR │
│ 6B row_id │ 6B 事务ID │ 7B 回滚指针 │
└─────────────┴───────────┴─────────────┘
DB_TRX_ID:最后修改该行的事务 ID
DB_ROLL_PTR:指向 Undo Log 中该行的上一个版本
DB_ROW_ID:隐藏主键(没有显式主键时生成)
ReadView 结构:
ReadView(快照读时生成)
├── m_ids:生成 ReadView 时活跃的(未提交)事务 ID 列表
├── min_trx_id:m_ids 中最小的事务 ID
├── max_trx_id:生成 ReadView 时系统分配的下一个事务 ID
└── creator_trx_id:创建该 ReadView 的事务 ID
可见性判断规则:
1. trx_id == creator_trx_id → 可见(自己改的)
2. trx_id < min_trx_id → 可见(已提交)
3. trx_id >= max_trx_id → 不可见(未来事务改的)
4. min_trx_id <= trx_id < max_trx_id:
- trx_id 在 m_ids 中 → 不可见(活跃事务,未提交)
- trx_id 不在 m_ids 中 → 可见(已提交)
不可见时:通过 DB_ROLL_PTR 找 Undo Log 链上的历史版本
当前读 vs 快照读:
-- 快照读(普通 SELECT):读 ReadView 中的版本,不加锁
SELECT * FROM user WHERE id = 1;
-- 当前读(锁定读):读最新版本,加锁
SELECT * FROM user WHERE id = 1 FOR UPDATE; -- 排他锁
SELECT * FROM user WHERE id = 1 LOCK IN SHARE MODE; -- 共享锁
-- 为什么 UPDATE/DELETE 是当前读?
-- 因为要先读最新数据,确认是否满足 WHERE 条件,再加锁修改
UPDATE user SET age = age + 1 WHERE id = 1; -- 当前读 + 修改Practice(实践)
-- MVCC 解决不可重复读演示
-- Session A
BEGIN;
SELECT * FROM user WHERE id = 1; -- 假设结果是 age=20,生成 ReadView
-- Session B
BEGIN;
UPDATE user SET age = 30 WHERE id = 1;
COMMIT;
-- Session A(再次查询,RR 隔离级别)
SELECT * FROM user WHERE id = 1; -- 结果仍是 age=20(快照读,ReadView 未变)
SELECT * FROM user WHERE id = 1 FOR UPDATE; -- 结果是 age=30(当前读,最新版本)
COMMIT;常见误区
| ❌ 误区 | ✅ 正确理解 |
|---|---|
| MVCC 完全不加锁 | MVCC 只是让读不加锁,写操作仍然需要锁 |
| RR 隔离级别完全没幻读 | RR + 快照读没幻读,但当前读(FOR UPDATE)加 Gap Lock 防幻读 |
| ReadView 每次查询都生成 | RC 每次查询生成,RR 事务开始时生成一次 |
| Undo Log 可以无限增长 | Purge 线程会清理不再需要的 Undo Log |
4. MySQL 锁机制
TL;DR
InnoDB 的锁按粒度分为行锁和表锁,按类型分为共享锁和排他锁——理解 Gap Lock、Next-Key Lock、意向锁和死锁排查,是处理并发问题的关键。
4.1 锁的类型与粒度
Theory(理论)
按粒度分类:
| 锁类型 | 锁定范围 | 开销 | 并发度 | 适用场景 |
|---|---|---|---|---|
| 行锁 | 单行记录 | 大 | 高 | 高并发 OLTP |
| 间隙锁(Gap Lock) | 索引间隙 | 中 | 中 | RR 隔离级别防幻读 |
| 临键锁(Next-Key Lock) | 行 + 间隙 | 中 | 中 | InnoDB 默认行锁 |
| 表锁 | 整张表 | 小 | 低 | DDL 操作、MyISAM |
| 意向锁 | 表级别标记 | 很小 | 高 | 协调行锁与表锁 |
按类型分类:
| 锁类型 | 英文 | 获取方式 | 兼容性 |
|---|---|---|---|
| 共享锁 | S Lock / IS | SELECT ... LOCK IN SHARE MODE | 与 S 兼容,与 X 冲突 |
| 排他锁 | X Lock / IX | SELECT ... FOR UPDATE / DML | 与所有锁冲突 |
| 意向共享锁 | IS | 获取 S 锁前自动获取 | 表级,与 IX 兼容 |
| 意向排他锁 | IX | 获取 X 锁前自动获取 | 表级,与 IS/IX 兼容 |
Next-Key Lock 示例:
-- 表 user 有索引 on age,现有数据 age = 10, 20, 30
-- RR 隔离级别下:
SELECT * FROM user WHERE age = 20 FOR UPDATE;
-- 锁住:
-- 1. age = 20 的行(Record Lock)
-- 2. (10, 20] 的间隙(Gap Lock)
-- 3. (20, 30] 的间隙(Gap Lock)
-- 合起来就是 Next-Key Lock:(-∞, 10], (10, 20], (20, 30], (30, +∞)
-- 其他事务:
INSERT INTO user(age) VALUES(15); -- 阻塞(间隙 (10,20] 被锁)
INSERT INTO user(age) VALUES(25); -- 阻塞(间隙 (20,30] 被锁)
INSERT INTO user(age) VALUES(5); -- 可能阻塞(取决于最左间隙)Practice(实践)
-- 查看当前锁情况
SELECT * FROM information_schema.INNODB_TRX; -- 活跃事务
SELECT * FROM information_schema.INNODB_LOCKS; -- 锁(8.0 已废弃,用 data_locks)
SELECT * FROM performance_schema.data_locks; -- 8.0+ 锁信息
SELECT * FROM performance_schema.data_lock_waits; -- 锁等待
-- 查看死锁日志
SHOW ENGINE INNODB STATUS\G
-- LATEST DETECTED DEADLOCK 部分// 业务场景:避免死锁的批量更新策略
type OrderRepository struct {
db *sql.DB
}
// ❌ 容易导致死锁:多个事务按不同顺序更新相同记录
func (r *OrderRepository) UpdateStatusBad(ctx context.Context, ids []int64, status string) error {
tx, _ := r.db.BeginTx(ctx, nil)
defer tx.Rollback()
for _, id := range ids {
_, err := tx.ExecContext(ctx,
"UPDATE orders SET status = ? WHERE id = ?", status, id)
if err != nil {
return err
}
}
return tx.Commit()
}
// ✅ 避免死锁:按主键排序后更新,保证全局顺序一致
func (r *OrderRepository) UpdateStatusGood(ctx context.Context, ids []int64, status string) error {
sort.Slice(ids, func(i, j int) bool { return ids[i] < ids[j] })
tx, _ := r.db.BeginTx(ctx, nil)
defer tx.Rollback()
for _, id := range ids {
_, err := tx.ExecContext(ctx,
"UPDATE orders SET status = ? WHERE id = ?", status, id)
if err != nil {
return err
}
}
return tx.Commit()
}业界案例:阿里巴巴订单系统死锁排查——高并发下多个事务更新订单状态时产生死锁,通过按订单 ID 排序更新 + 减少事务持有锁的时间,死锁率从每天数百次降到个位数。
4.2 死锁排查与预防
TL;DR
死锁是并发事务相互等待对方释放锁——理解死锁产生的四个条件和排查方法,比记住"加锁顺序一致"更重要。
Theory(理论)
死锁产生的四个条件:
- 互斥条件:资源一次只能被一个事务占用
- 请求与保持:事务持有资源同时请求新资源
- 不可剥夺:资源只能由持有者主动释放
- 循环等待:事务之间形成等待环
InnoDB 死锁检测:
1. 事务等待锁时,检查是否形成等待环
2. 发现死锁后,选择 Undo Log 最少的事务回滚(代价最小)
3. 被回滚的事务会收到 ERROR 1213 (40001): Deadlock found
innodb_deadlock_detect = ON(默认开启,高并发下可能消耗大量 CPU)
innodb_lock_wait_timeout = 50(锁等待超时,秒)
死锁预防策略:
| 策略 | 说明 |
|---|---|
| 固定加锁顺序 | 所有事务按相同顺序访问资源 |
| 减少锁持有时间 | 把非必要的操作放到事务外 |
| 降低隔离级别 | RC 比 RR 产生死锁的概率低(没有 Gap Lock) |
| 使用乐观锁 | 版本号机制替代数据库锁 |
| 重试机制 | 捕获死锁异常后重试 |
5. MySQL SQL 优化与执行计划
TL;DR
SQL 优化的核心是减少扫描行数、避免排序和临时表、利用索引——理解 EXPLAIN 输出和优化器代价模型,是性能调优的基本功。
5.1 EXPLAIN 详解
Theory(理论)
EXPLAIN 关键列:
| 列 | 含义 | 关注点 |
|---|---|---|
| id | 执行顺序 | id 相同从上到下,id 越大越先执行 |
| select_type | 查询类型 | SIMPLE/PRIMARY/SUBQUERY/DERIVED/UNION |
| table | 访问的表 | |
| type | 访问类型 | system > const > eq_ref > ref > range > index > ALL |
| possible_keys | 可能使用的索引 | |
| key | 实际使用的索引 | NULL 表示没走索引 |
| key_len | 索引使用长度 | 越长表示使用索引列越多 |
| ref | 索引参照 | const/func/null |
| rows | 估计扫描行数 | 越小越好 |
| Extra | 额外信息 | Using index/Using where/Using filesort/Using temporary |
type 类型从优到劣:
system: 表只有一行(系统表)
const: 通过主键或唯一索引一次命中
eq_ref: JOIN 中,被驱动表通过主键或唯一索引访问
ref: 通过非唯一索引访问
range: 索引范围扫描(BETWEEN/IN/>/<)
index: 全索引扫描(遍历整个索引树)
ALL: 全表扫描(遍历所有数据页)
Extra 警告信号:
| Extra 值 | 含义 | 优化方向 |
|---|---|---|
| Using filesort | 需要额外排序 | 添加合适的索引避免排序 |
| Using temporary | 需要创建临时表 | 简化查询,优化 GROUP BY/DISTINCT |
| Using where | Server 层过滤 | 尝试让索引覆盖更多过滤条件 |
| Using index | 覆盖索引 | ✅ 好的信号 |
| Using index condition | 索引下推 | ✅ 好的信号(5.6+) |
Practice(实践)
-- 慢查询分析示例
EXPLAIN SELECT u.name, COUNT(o.id) as order_count
FROM user u
LEFT JOIN orders o ON u.id = o.user_id
WHERE u.created_at > '2024-01-01'
GROUP BY u.id
ORDER BY order_count DESC
LIMIT 10;
-- 可能的优化:
-- 1. user(created_at, id) 索引 → 减少 WHERE 扫描
-- 2. orders(user_id, id) 索引 → JOIN 优化,覆盖索引
-- 3. 如果 order_count 不需要精确,考虑冗余字段或物化视图// 业务场景:分页查询优化(深分页问题)
@Service
public class OrderQueryService {
@Autowired
private JdbcTemplate jdbcTemplate;
// ❌ 低效深分页:OFFSET 1000000 需要扫描前 100 万行
public List<Order> queryPageBad(int page, int size) {
return jdbcTemplate.query(
"SELECT * FROM orders ORDER BY id DESC LIMIT ? OFFSET ?",
new BeanPropertyRowMapper<>(Order.class),
size, page * size
);
}
// ✅ 高效深分页:利用覆盖索引 + 延迟关联
public List<Order> queryPageGood(int lastId, int size) {
// 传入上一页最后一条的 id
return jdbcTemplate.query(
"SELECT o.* FROM orders o " +
"INNER JOIN (SELECT id FROM orders WHERE id < ? ORDER BY id DESC LIMIT ?) tmp " +
"ON o.id = tmp.id",
new BeanPropertyRowMapper<>(Order.class),
lastId, size
);
}
// ✅ 更优方案:业务层面限制最大页码,或提供"加载更多"而非跳页
}业界案例:美团订单查询优化——深分页 LIMIT 100000, 10 导致查询时间从 5ms 增长到 5s,通过"上一页最后 ID"方式优化后,稳定在 10ms 以内。
5.2 SQL 优化十大原则
| 原则 | 说明 | 示例 |
|---|---|---|
| 1. 避免 SELECT * | 减少网络传输和回表 | SELECT id, name 替代 SELECT * |
| 2. 小表驱动大表 | JOIN 时小表放前面 | STRAIGHT_JOIN 强制驱动顺序 |
| 3. 用连接替代子查询 | 子查询可能产生临时表 | JOIN 替代 IN (SELECT ...) |
| 4. 批量操作替代循环 | 减少网络往返 | INSERT INTO ... VALUES (...), (...) |
| 5. 避免函数操作索引列 | 导致索引失效 | WHERE create_time > '2024-01-01' 替代 WHERE DATE(create_time) = '2024-01-01' |
| 6. 覆盖索引减少回表 | 查询列都在索引中 | 创建 (a, b) 索引查 SELECT a, b |
| 7. 分页优化 | 避免深分页 | 游标分页替代 OFFSET |
| 8. 避免隐式类型转换 | 导致索引失效 | 字符串字段传字符串 |
| 9. 排序利用索引 | 避免 filesort | ORDER BY 列建立索引 |
| 10. 分解复杂查询 | 减少锁竞争 | 大查询拆分成多个小查询 |
6. MySQL 分库分表与读写分离
TL;DR
数据量超过单机承载能力时,分库分表是必经之路——理解水平/垂直拆分策略、ShardingKey 选择和分布式 ID,是架构设计的核心能力。
6.1 分库分表策略
Theory(理论)
拆分维度:
垂直拆分:
├── 垂直分库:按业务拆分(用户库、订单库、商品库)
│ └── 解决:单机数据库连接数瓶颈、不同业务独立扩容
│
└── 垂直分表:按字段拆分(大字段/冷热字段分离)
└── 解决:单行过大、减少 Buffer Pool 占用
水平拆分:
├── 水平分库:同一表数据分散到多个库
│ └── 解决:单库数据量/写入量过大
│
└── 水平分表:同一表数据分散到多个表
└── 解决:单表数据量过大、索引效率下降
分片策略:
| 策略 | 原理 | 优点 | 缺点 |
|---|---|---|---|
| Hash 取模 | sharding_key % N | 数据分布均匀 | 扩容需数据迁移(一致性 Hash 可缓解) |
| 范围分片 | 按 ID 或时间范围 | 扩容简单、支持范围查询 | 可能存在热点(最新数据集中在最后一片) |
| 列表分片 | 按枚举值(如地区) | 按业务逻辑拆分 | 数据分布可能不均 |
| 复合分片 | 先范围再 Hash | 兼顾两者优点 | 实现复杂 |
分布式 ID 方案:
| 方案 | 原理 | 优点 | 缺点 |
|---|---|---|---|
| 数据库自增 | auto_increment | 简单 | 单机瓶颈、不适合分片 |
| Snowflake | 时间戳 + 机器ID + 序列号 | 趋势递增、高性能 | 依赖时钟、需要协调机器ID |
| 号段模式 | 批量申请 ID 段 | 高可用、高性能 | 需要维护发号器 |
| UUID | 随机生成 | 全球唯一、去中心化 | 无序、占用空间大 |
Practice(实践)
// 基于 ShardingSphere 的分库分表配置
@Configuration
public class ShardingConfig {
@Bean
public DataSource shardingDataSource() throws SQLException {
// 数据源配置
Map<String, DataSource> dataSourceMap = new HashMap<>();
dataSourceMap.put("ds0", createDataSource("jdbc:mysql://localhost:3306/db0"));
dataSourceMap.put("ds1", createDataSource("jdbc:mysql://localhost:3306/db1"));
// 分片规则
ShardingTableRuleConfiguration orderTableRule = new ShardingTableRuleConfiguration(
"orders", "ds${0..1}.orders_${0..15}");
// 分库策略:user_id % 2
orderTableRule.setDatabaseShardingStrategy(
new StandardShardingStrategyConfiguration("user_id", "dbShardingAlgorithm"));
// 分表策略:user_id % 16
orderTableRule.setTableShardingStrategy(
new StandardShardingStrategyConfiguration("user_id", "tableShardingAlgorithm"));
// 算法配置
Properties dbProps = new Properties();
dbProps.setProperty("algorithm-expression", "ds${user_id % 2}");
Properties tableProps = new Properties();
tableProps.setProperty("algorithm-expression", "orders_${user_id % 16}");
ShardingRuleConfiguration shardingRuleConfig = new ShardingRuleConfiguration();
shardingRuleConfig.getTables().add(orderTableRule);
shardingRuleConfig.getShardingAlgorithms().put("dbShardingAlgorithm",
new ShardingSphereAlgorithmConfiguration("INLINE", dbProps));
shardingRuleConfig.getShardingAlgorithms().put("tableShardingAlgorithm",
new ShardingSphereAlgorithmConfiguration("INLINE", tableProps));
return ShardingSphereDataSourceFactory.createDataSource(dataSourceMap,
Collections.singleton(shardingRuleConfig), new Properties());
}
}// 真实业务:Snowflake ID 生成器
type Snowflake struct {
mu sync.Mutex
epoch int64 // 起始时间戳
workerID int64 // 机器ID
sequence int64 // 序列号
lastTime int64 // 上次生成ID的时间戳
}
func NewSnowflake(workerID int64) *Snowflake {
return &Snowflake{
epoch: time.Date(2024, 1, 1, 0, 0, 0, 0, time.UTC).UnixMilli(),
workerID: workerID,
}
}
func (s *Snowflake) NextID() int64 {
s.mu.Lock()
defer s.mu.Unlock()
now := time.Now().UnixMilli()
if now < s.lastTime {
panic("clock moved backwards")
}
if now == s.lastTime {
s.sequence = (s.sequence + 1) & 4095 // 12位序列号
if s.sequence == 0 {
for now <= s.lastTime {
now = time.Now().UnixMilli()
}
}
} else {
s.sequence = 0
}
s.lastTime = now
return ((now - s.epoch) << 22) | (s.workerID << 12) | s.sequence
}业界案例:饿了么订单系统分 1024 张表——按 user_id % 1024 分片,配合 Snowflake ID,支撑日均千万级订单。通过 ShardingSphere 做中间件层,业务代码无感知。
6.2 读写分离
Theory(理论)
读写分离架构
┌─────────────┐
│ 应用层 │
└──────┬──────┘
│
┌──────▼──────┐
│ 代理层 │ ← MySQL Proxy / ShardingSphere-Proxy
│ (路由) │ 写请求 → 主库,读请求 → 从库
└──────┬──────┘
│
┌───┴───┐
▼ ▼
┌─────┐ ┌─────┐
│ 主库 │ │ 从库 │ ← 主从复制(异步/半同步)
│ 写 │ │ 读 │
└─────┘ └─────┘
主从延迟问题:
- 异步复制:主库写完即返回,从库异步同步,可能有延迟
- 半同步复制:至少一个从库收到 Binlog 才返回,减少延迟
- 解决方案:
1. 强制走主库(对一致性要求高的读)
2. 延迟敏感业务走主库
3. 使用缓存弥补延迟窗口
7. MySQL 主从复制与高可用
TL;DR
MySQL 主从复制基于 Binlog——理解复制原理、并行复制和高可用方案(MHA/MGR/ Orchestrator),是保障数据库可靠性的基础。
7.1 主从复制原理
Theory(理论)
复制流程:
1. 主库:
- 事务提交时写 Binlog( Dump Thread 读取 Binlog 发送给从库)
2. 从库:
- I/O Thread:接收主库 Binlog,写入 Relay Log
- SQL Thread:读取 Relay Log,重放 SQL
3. 复制模式:
- Statement:记录 SQL 语句(体积小,但有函数不确定性问题)
- Row:记录行变更(推荐,数据一致但体积大)
- Mixed:自动选择(MySQL 判断)
4. 复制类型:
- 异步复制:主库不等待从库(默认,可能丢数据)
- 半同步复制:主库等待至少一个从库收到 Binlog(after_sync/after_commit)
- 组复制(MGR):基于 Paxos 的强一致复制
并行复制(5.6+):
5.6:基于库并行(多线程分别处理不同库的 SQL)
5.7:基于组提交并行(同一个事务组内并行)
8.0:基于 WriteSet 并行(无冲突的事务并行执行)
slave_parallel_type = LOGICAL_CLOCK
slave_parallel_workers = 8
slave_preserve_commit_order = ON
Practice(实践)
-- 查看复制状态
SHOW SLAVE STATUS\G
-- 关注:
-- Slave_IO_Running / Slave_SQL_Running
-- Seconds_Behind_Master(延迟秒数,0 表示无延迟)
-- Relay_Master_Log_File / Exec_Master_Log_Pos
-- 半同步复制配置
[mysqld]
rpl_semi_sync_master_enabled = 1
rpl_semi_sync_slave_enabled = 1
rpl_semi_sync_master_timeout = 10000 -- 10秒超时降级为异步7.2 高可用方案
| 方案 | 原理 | 切换方式 | 数据一致性 | 适用场景 |
|---|---|---|---|---|
| MHA | 主从 + 故障检测 + VIP 漂移 | 手动/自动 | 异步,可能丢数据 | 传统方案,逐步淘汰 |
| MGR | 组复制 + Paxos | 自动选举 | 强一致 | MySQL 官方推荐(8.0) |
| Orchestrator | 可视化拓扑 + Raft | 自动/手动 | 依赖复制方式 | 大规模集群管理 |
| ProxySQL | 中间件 + 健康检查 | 自动 | 依赖后端 | 读写分离 + 故障转移 |
MGR(MySQL Group Replication):
- 基于 Paxos 协议的组复制
- 单主模式(Primary)或多主模式(Multi-Primary)
- 自动故障检测和选举
- 事务在组内多数节点确认后才提交
优点:强一致、自动故障转移、无需 VIP
缺点:写入性能略低(需要多数确认)、网络分区敏感
业界案例:GitHub 使用 Orchestrator + ProxySQL 管理数千个 MySQL 实例——Orchestrator 自动检测主库故障并提升从库,ProxySQL 做读写分离和连接池,故障切换时间从分钟级降到秒级。
8. PostgreSQL 核心架构与特性
TL;DR
PostgreSQL 是功能最强大的开源关系型数据库——理解其进程架构、MVCC 实现和扩展能力,是区分"只会 MySQL"和"懂数据库"的关键。
8.1 进程架构
Theory(理论)
PostgreSQL 进程架构(对比 MySQL 线程模型):
PostgreSQL(多进程)
├── Postmaster(主进程)
│ ├── 监听连接请求
│ ├── 管理共享内存
│ └── 启动/监控子进程
│
├── Backend Process(后端进程)
│ └── 每个客户端连接对应一个独立进程
│
├── Background Writer(后台写进程)
│ └── 将共享缓冲区脏页刷到磁盘
│
├── WAL Writer(预写日志进程)
│ └── 将 WAL Buffer 刷到磁盘
│
├── AutoVacuum(自动清理进程)
│ └── 清理死元组、更新统计信息
│
└── Checkpointer(检查点进程)
└── 定期执行检查点
MySQL(多线程)
├── 一个进程内多个线程处理连接
├── 线程池复用连接
└── 内存共享更简单
PostgreSQL 多进程优缺点:
✅ 崩溃隔离性好(单个后端崩溃不影响其他连接)
✅ 利用 OS 调度器
❌ 连接数多时有进程切换开销(解决:PgBouncer 连接池)
❌ 共享内存机制比线程共享更复杂
共享内存结构:
Shared Buffers(类似 MySQL Buffer Pool)
├── 默认 128MB,推荐 25% 内存
├── 使用 Clock Sweep 算法(类似 LRU)
└── 数据页 8KB(MySQL 是 16KB)
WAL Buffers
├── WAL 日志先写 WAL Buffer
├── 再刷到磁盘(pg_xlog/pg_wal)
└── 默认 16MB
Practice(实践)
-- 查看 PostgreSQL 进程
SELECT * FROM pg_stat_activity;
-- 查看共享内存配置
SHOW shared_buffers; -- 默认 128MB
SHOW effective_cache_size; -- OS 缓存 + PG 缓存,用于查询优化器估算
SHOW work_mem; -- 排序/哈希操作内存
SHOW maintenance_work_mem; -- VACUUM/CREATE INDEX 内存
-- 生产环境推荐配置
shared_buffers = 4GB -- 内存的 25%
effective_cache_size = 12GB -- 内存的 75%
work_mem = 256MB -- 每个操作(注意:并行时会乘以连接数)
maintenance_work_mem = 512MB
max_connections = 200 -- 配合 PgBouncer8.2 PostgreSQL 核心特性
| 特性 | 说明 | MySQL 对比 |
|---|---|---|
| 完整 ACID | 所有操作默认事务 | MySQL 5.5+ InnoDB 也支持 |
| 复杂查询 | CTE(WITH 递归)、窗口函数、LATERAL JOIN | MySQL 8.0 开始支持窗口函数 |
| 扩展类型 | 数组、JSONB、范围类型、地理空间(PostGIS) | MySQL 5.7+ 支持 JSON,8.0 支持数组 |
| 扩展系统 | PL/pgSQL、PL/Python、自定义类型/操作符/索引 | MySQL 插件系统相对封闭 |
| 并发控制 | MVCC 无读锁 | 两者都用 MVCC,但实现不同 |
| 表继承 | 表可以继承其他表 | MySQL 不支持 |
| 部分索引 | 只对满足条件的行建索引 | MySQL 8.0 支持函数索引 |
| 并行查询 | 单查询多进程并行 | MySQL 8.0 支持 |
9. PostgreSQL 索引体系
TL;DR
PostgreSQL 支持 6 种索引类型——理解 B-tree、Hash、GiST、GIN、SP-GiST、BRIN 的适用场景,是 PostgreSQL 性能优化的关键。
9.1 索引类型对比
| 索引类型 | 适用场景 | 特点 | MySQL 对应 |
|---|---|---|---|
| B-tree | 等值、范围、排序(默认) | 平衡树,通用性强 | InnoDB 主索引 |
| Hash | 等值查询 | 只支持 =,不支持范围 | Adaptive Hash Index(内部) |
| GiST | 地理空间、范围、模糊查询 | 通用搜索树框架 | 无直接对应 |
| GIN | 数组、全文检索、JSONB | 倒排索引,适合多值 | 无直接对应 |
| SP-GiST | 分区搜索(quadtrees、kd-trees) | 空间分区 | 无直接对应 |
| BRIN | 超大表顺序数据 | 块级范围索引,极小 | 无直接对应 |
Practice(实践)
-- B-tree(默认)
CREATE INDEX idx_user_email ON user(email);
-- Hash(等值查询优化)
CREATE INDEX idx_user_phone ON user USING HASH(phone);
-- GIN(JSONB 查询)
CREATE INDEX idx_user_profile ON user USING GIN(profile);
-- 查询:SELECT * FROM user WHERE profile @> '{"city": "Beijing"}';
-- GiST(地理空间)
CREATE EXTENSION postgis;
CREATE INDEX idx_location ON stores USING GIST(location);
-- 查询:SELECT * FROM stores WHERE location <-> point(116,39) < 1000;
-- BRIN(超大时序表)
CREATE INDEX idx_logs_time ON logs USING BRIN(created_at);
-- 适用:数十亿行的日志表,时间顺序写入// 真实业务:PostgreSQL JSONB 索引优化
type UserProfile struct {
ID int64
Profile map[string]interface{}
}
type UserRepository struct {
db *sql.DB
}
// 使用 GIN 索引加速 JSONB 查询
func (r *UserRepository) FindByCity(ctx context.Context, city string) ([]UserProfile, error) {
// 依赖 GIN 索引:CREATE INDEX idx_profile ON users USING GIN(profile);
rows, err := r.db.QueryContext(ctx,
"SELECT id, profile FROM users WHERE profile @> jsonb_build_object('city', $1)",
city)
if err != nil {
return nil, err
}
defer rows.Close()
var users []UserProfile
for rows.Next() {
var u UserProfile
var profileJSON []byte
if err := rows.Scan(&u.ID, &profileJSON); err != nil {
return nil, err
}
json.Unmarshal(profileJSON, &u.Profile)
users = append(users, u)
}
return users, nil
}业界案例:Instagram 使用 PostgreSQL + GIN 索引存储用户元数据(JSONB 格式)——数十亿用户的灵活属性存储,GIN 索引支持任意字段查询,避免了 MySQL 中宽表或 EAV 模式的弊端。
10. PostgreSQL MVCC 与事务
TL;DR
PostgreSQL 的 MVCC 通过多版本存储实现——理解 xmin/xmax 隐藏字段、Vacuum 机制和快照隔离,是 PostgreSQL 运维的核心。
10.1 PostgreSQL MVCC 实现
Theory(理论)
隐藏字段(每行都有):
PostgreSQL 每行记录的隐藏系统字段:
┌─────────┬─────────┬──────────┬──────────┐
│ xmin │ xmax │ cmin │ cmax │
│ 插入事务 │ 删除事务 │ 命令ID │ 命令ID │
│ 的 txid │ 的 txid │ │ │
└─────────┴─────────┴──────────┴──────────┘
xmin:插入该元组的事务 ID
xmax:删除/更新该元组的事务 ID(0 表示未被删除)
cmin/cmax:同一事务内的命令 ID(处理同一事务内的可见性)
t_infomask 标志位:
- HEAP_XMIN_COMMITTED:插入已提交
- HEAP_XMIN_INVALID:插入已回滚
- HEAP_XMAX_COMMITTED:删除已提交
- HEAP_XMAX_INVALID:删除已回滚
可见性判断:
1. 获取当前事务的快照(Snapshot)
- xmin:所有活跃事务中最小的事务 ID
- xmax:已分配的最大事务 ID + 1
- xip_list:活跃事务 ID 列表
2. 判断元组可见性:
- xmin 未提交(在 xip_list 中)且不是当前事务 → 不可见
- xmin 已回滚 → 不可见
- xmax 已提交且不是当前事务 → 不可见(已被删除)
- xmax 未提交或是当前事务 → 可见(删除未生效或自己删的)
- 否则 → 可见
与 MySQL MVCC 对比:
| 维度 | MySQL InnoDB | PostgreSQL |
|---|---|---|
| 历史版本存储 | Undo Log(回滚段) | 数据页中直接存多版本 |
| 清理机制 | Purge 线程异步清理 | Vacuum 进程清理死元组 |
| 回滚实现 | Undo Log | 直接利用数据页中的旧版本 |
| 表膨胀 | 无(Undo 独立) | 有(更新产生死元组,需 Vacuum) |
| 事务 ID 回卷 | 无(64 位) | 有(32 位,需 Vacuum 冻结) |
10.2 Vacuum 机制
TL;DR
PostgreSQL 的更新和删除不会立即释放空间,而是通过 Vacuum 清理死元组——理解 Vacuum、AutoVacuum 和事务 ID 回卷,是 PostgreSQL DBA 的必修课。
Theory(理论)
Vacuum 的作用:
1. 清理死元组(Dead Tuples):
- UPDATE/DELETE 产生的旧版本
- 对所有事务不可见的元组
2. 冻结事务 ID:
- txid 是 32 位,会回卷(约 21 亿后回到 3)
- 将旧元组的 xmin 标记为 FrozenTransactionId(2)
- 防止事务 ID 回卷导致可见性问题
3. 更新统计信息:
- ANALYZE 收集表统计信息,供查询优化器使用
4. 可见性映射(Visibility Map):
- 标记哪些页所有元组都可见
- Index-Only Scan 可以跳过不可见页的检查
AutoVacuum 配置:
-- 查看 Vacuum 状态
SELECT schemaname, relname, n_dead_tup, n_live_tup, last_vacuum, last_autovacuum
FROM pg_stat_user_tables
WHERE n_dead_tup > 10000;
-- AutoVacuum 配置
autovacuum = on
autovacuum_max_workers = 3
autovacuum_naptime = 1min
-- 触发阈值:
-- dead tuples > autovacuum_vacuum_threshold + autovacuum_vacuum_scale_factor * total tuples
autovacuum_vacuum_threshold = 50
autovacuum_vacuum_scale_factor = 0.2常见误区
| ❌ 误区 | ✅ 正确理解 |
|---|---|
| Vacuum 会阻塞读写 | VACUUM(非 FULL)不阻塞读写,VACUUM FULL 会锁表 |
| 表膨胀只能通过 Vacuum Full 解决 | 可以先尝试 pg_repack 或 pg_squeeze(在线重建) |
| AutoVacuum 可以关闭 | 绝对不要关闭,否则事务 ID 回卷会导致数据库停止 |
| Vacuum 越频繁越好 | Vacuum 也有开销,大表频繁 Vacuum 影响性能 |
Extension(延伸)
- 追问:pg_repack 和 VACUUM FULL 的区别? → pg_repack 在线重建(创建新表 + 触发器同步),VACUUM FULL 锁表重建
- 追问:事务 ID 回卷(XID wraparound)怎么预防? → 确保 AutoVacuum 正常运作,监控
age(datfrozenxid),超过 2 亿需紧急处理 - 追问:为什么 PostgreSQL 不用 Undo Log? → 多进程架构下,共享 Undo 段管理复杂;数据页内多版本实现更简单,但代价是表膨胀
11. PostgreSQL 高级特性
TL;DR
PostgreSQL 的 JSONB、分区表、FDW 和扩展系统使其不仅是关系型数据库——理解这些特性,是在架构选型时做出正确决策的前提。
11.1 JSONB 与文档存储
Theory(理论)
-- JSON vs JSONB
-- JSON:文本存储,解析时才验证,保留格式(空格、键顺序)
-- JSONB:二进制存储,解析后存储,去除冗余,支持索引
CREATE TABLE events (
id SERIAL PRIMARY KEY,
data JSONB
);
-- JSONB 操作符
SELECT data->>'name' FROM events; -- ->> 取文本
SELECT data->'tags' FROM events; -- -> 取 JSON
SELECT data @> '{"status": "active"}'; -- @> 包含
SELECT data ? 'tag1'; -- ? 键是否存在
SELECT jsonb_pretty(data) FROM events; -- 格式化输出
-- GIN 索引加速 JSONB 查询
CREATE INDEX idx_events_data ON events USING GIN(data);
CREATE INDEX idx_events_data_path ON events USING GIN(data jsonb_path_ops); -- 更紧凑JSONB 索引策略:
| 索引类型 | 适用查询 | 特点 |
|---|---|---|
| GIN (default) | @> ? ?& ?| | 支持所有操作符,索引较大 |
| GIN (jsonb_path_ops) | @> | 只支持包含查询,索引更小更快 |
| B-tree(特定键) | ->> 等值/范围 | 对特定键建立表达式索引 |
Practice(实践)
-- 为特定 JSONB 键创建表达式索引
CREATE INDEX idx_events_name ON events((data->>'name'));
-- 部分索引(只索引活跃事件)
CREATE INDEX idx_events_active ON events(data)
WHERE data @> '{"status": "active"}';11.2 分区表(Partitioning)
-- 声明式分区(10+)
CREATE TABLE logs (
id BIGINT,
created_at TIMESTAMPTZ,
message TEXT
) PARTITION BY RANGE (created_at);
-- 创建分区
CREATE TABLE logs_2024_01 PARTITION OF logs
FOR VALUES FROM ('2024-01-01') TO ('2024-02-01');
CREATE TABLE logs_2024_02 PARTITION OF logs
FOR VALUES FROM ('2024-02-01') TO ('2024-03-01');
-- 自动创建分区(使用 pg_partman 扩展)分区策略对比:
| 策略 | 适用场景 | 优点 | 缺点 |
|---|---|---|---|
| 范围分区 | 时序数据 | 按时间归档、删除旧分区快 | 可能导致热点 |
| 列表分区 | 按地区/状态 | 灵活 | 分区数可能很多 |
| 哈希分区 | 均匀分布 | 避免热点 | 不支持范围查询裁剪 |
11.3 FDW(外部数据包装器)
-- 访问远程 PostgreSQL 表
CREATE EXTENSION postgres_fdw;
CREATE SERVER remote_server
FOREIGN DATA WRAPPER postgres_fdw
OPTIONS (host 'remote.host', dbname 'remote_db');
CREATE USER MAPPING FOR current_user
SERVER remote_server
OPTIONS (user 'remote_user', password 'secret');
CREATE FOREIGN TABLE remote_users (
id INT,
name TEXT
) SERVER remote_server
OPTIONS (schema_name 'public', table_name 'users');
-- 像本地表一样查询
SELECT * FROM remote_users WHERE id = 1;业界案例:高盛使用 PostgreSQL + FDW 构建数据联邦——多个交易系统的数据分散在不同数据库,通过 FDW 统一查询,无需 ETL 即可生成跨系统报表。
12. MySQL vs PostgreSQL 选型对比
TL;DR
没有银弹——MySQL 更适合 OLTP 和高并发写入,PostgreSQL 更适合复杂查询和扩展性场景。选型取决于业务特性、团队技能和生态需求。
12.1 全维度对比
| 维度 | MySQL | PostgreSQL |
|---|---|---|
| 架构 | 多线程 | 多进程 |
| 默认引擎 | InnoDB(支持多引擎) | 单一存储引擎 |
| SQL 兼容性 | 较宽松 | ANSI SQL 高度兼容 |
| 复杂查询 | 8.0 大幅改进 | 原生支持 CTE、窗口函数、LATERAL |
| JSON 支持 | 5.7+ JSON,8.0 改进 | JSONB 更成熟,支持索引 |
| 全文检索 | 支持(InnoDB/MyISAM) | 内置 + 更灵活的搜索配置 |
| 地理空间 | 需 Spatial 扩展 | PostGIS(业界标准) |
| 扩展性 | 插件相对封闭 | 极强的扩展系统(类型/索引/语言) |
| 性能(OLTP) | 高并发写入更优 | 稍逊 |
| 性能(OLAP) | 需 TiDB/ClickHouse 补充 | 并行查询更强 |
| 复制 | 异步/半同步/MGR | 流复制/逻辑复制/物理复制 |
| 高可用 | MGR/MHA/Orchestrator | Patroni/repmgr |
| 连接数 | 更高(线程模型) | 较低(进程模型),需连接池 |
| 社区生态 | 更广泛的云厂商支持 | 更活跃的开源社区 |
| 典型用户 | 阿里、腾讯、美团、Facebook | Apple、Instagram、Spotify、Netflix |
12.2 选型决策树
选择 MySQL 如果:
✅ 高并发 OLTP,大量简单读写
✅ 团队熟悉 MySQL,有成熟的 DBA 体系
✅ 需要广泛的云厂商托管服务(RDS)
✅ 需要与大量 Java/PHP 生态工具集成
✅ 需要 Group Replication 强一致集群
选择 PostgreSQL 如果:
✅ 复杂查询、报表分析、数据仓库(中小规模)
✅ 需要灵活的 schema(JSONB、数组、自定义类型)
✅ 地理空间数据(PostGIS)
✅ 需要强 ACID + 复杂事务
✅ 需要扩展系统(自定义索引、操作符、FDW)
✅ 需要逻辑复制(跨版本、异构同步)
12.3 常见误区
| ❌ 误区 | ✅ 正确理解 |
|---|---|
| PostgreSQL 比 MySQL 慢 | PostgreSQL 14+ 在 OLTP 场景已大幅追赶,复杂查询更快 |
| MySQL 不适合复杂查询 | MySQL 8.0 的 CTE 和窗口函数已大幅改进 |
| 大厂都用 MySQL | Apple、Netflix、Instagram 等大量使用 PostgreSQL |
| PostgreSQL 不能处理大数据量 | 通过分区、并行查询、连接池,PB 级也有案例 |
| 从 MySQL 迁移到 PostgreSQL 很简单 | 语法差异、函数差异、生态工具都需要适配 |
Extension(延伸)
- 追问:什么场景应该用 TiDB 而不是 MySQL/PostgreSQL? → 海量数据(>10TB)、需要水平扩展、强一致分布式事务
- 追问:MySQL 8.0 vs PostgreSQL 14+ 还有多大差距? → 复杂查询和扩展性 PG 仍领先,OLTP 性能差距已很小
- 追问:云原生时代数据库选型有什么变化? → Aurora/RDS Proxy/Serverless 降低了运维门槛,但核心原理仍需理解
面试速查表
高频问题 TOP 20
| # | 问题 | 核心关键词 | 参考章节 |
|---|---|---|---|
| 1 | InnoDB 为什么用 B+ 树? | 磁盘 IO + 树高度 + 范围查询 | 1.1 |
| 2 | 聚簇索引和非聚簇索引区别 | 数据存储方式 + 回表 + 覆盖索引 | 1.2 |
| 3 | 最左前缀原则 | 联合索引排序方式 + 失效场景 | 1.3 |
| 4 | Buffer Pool 是什么 | LRU + 脏页 + 命中率 | 2.1 |
| 5 | Redo/Undo/Binlog 区别 | 物理日志/逻辑日志/层级/作用 | 2.2 |
| 6 | 两阶段提交 | Redo Prepare → Binlog → Redo Commit | 2.2 |
| 7 | 事务隔离级别 | RC/RR/幻读/MVCC | 3 |
| 8 | MVCC 实现原理 | ReadView + trx_id + Undo Log | 3.2 |
| 9 | 当前读 vs 快照读 | FOR UPDATE / 普通 SELECT | 3.2 |
| 10 | InnoDB 锁类型 | Record Lock / Gap Lock / Next-Key Lock | 4 |
| 11 | 死锁怎么排查 | information_schema / SHOW ENGINE | 4.2 |
| 12 | EXPLAIN type 类型 | const/ref/range/index/ALL | 5.1 |
| 13 | 深分页怎么优化 | 覆盖索引 + 延迟关联 + 游标 | 5.1 |
| 14 | 分库分表策略 | Hash/Range/分布式 ID | 6 |
| 15 | 主从延迟怎么解决 | 强制走主库 + 半同步 + 缓存 | 7 |
| 16 | MGR 是什么 | Paxos + 组复制 + 强一致 | 7.2 |
| 17 | PostgreSQL 多进程优缺点 | 崩溃隔离 vs 连接开销 | 8.1 |
| 18 | PostgreSQL Vacuum 作用 | 死元组清理 + 事务 ID 冻结 | 10.2 |
| 19 | PostgreSQL JSONB 索引 | GIN / jsonb_path_ops | 9, 11.1 |
| 20 | MySQL vs PostgreSQL 选型 | OLTP vs 复杂查询 / 生态 | 12 |
类比速记
| 概念 | 类比 |
|---|---|
| Buffer Pool | 图书馆的书架(热门书放前面,冷门书淘汰) |
| Redo Log | 收银台小票(先打小票再入库,丢了小票能重打) |
| Undo Log | 撤销按钮(做错事能回退,别人也能看历史版本) |
| Binlog | 账本副本(给会计对账用) |
| MVCC | 时光机(能看到过去某个时间点的世界) |
| Gap Lock | 占位符(这排座位我包了,别人不能坐) |
| 覆盖索引 | 自助取餐(不用去后厨,前台直接拿) |
| 回表 | 凭票取货(先拿号,再去仓库取) |
| Vacuum | 扫地机器人(定期清理地上的垃圾元组) |
| PostgreSQL 进程 | 独立办公室(每个客户一间,互不干扰) |
| MySQL 线程 | 开放式工位(共享空间,效率高但可能互相影响) |
本文档由 kimi-k2.6 模型生成,面向 Senior/Staff 级别后端开发面试准备。 核心方法论:原理 + 实战 + 源码,STAR 框架组织答案,误区对比加深理解。 涵盖 MySQL 与 PostgreSQL 双数据库体系,配合选型对比,适应不同技术栈面试需求。