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

通识对照:MySQL 索引选型:B+ 树、最左前缀与回表代价

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

Hello,我是小兔大白糖。 这篇整理面试口述与高频追问要点,方便对照站内同主题通识文。


Generated by kimi-k2.6 model

面向 Senior/Staff 级别后端开发岗位的数据库系统性面试准备文档。涵盖 MySQL 与 PostgreSQL 核心原理、索引、事务、锁、优化、高可用、选型对比,深度解析原理 + 实战 + 源码。


目录

  1. MySQL 索引原理
  2. InnoDB 存储引擎
  3. MySQL 事务与隔离级别
  4. MySQL 锁机制
  5. MySQL SQL 优化与执行计划
  6. MySQL 分库分表与读写分离
  7. MySQL 主从复制与高可用
  8. PostgreSQL 核心架构与特性
  9. PostgreSQL 索引体系
  10. PostgreSQL MVCC 与事务
  11. PostgreSQL 高级特性
  12. 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 LogUndo LogBinlog
作用崩溃恢复事务回滚、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 / ISSELECT ... LOCK IN SHARE MODE与 S 兼容,与 X 冲突
排他锁X Lock / IXSELECT ... 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(理论)

死锁产生的四个条件:

  1. 互斥条件:资源一次只能被一个事务占用
  2. 请求与保持:事务持有资源同时请求新资源
  3. 不可剥夺:资源只能由持有者主动释放
  4. 循环等待:事务之间形成等待环

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 whereServer 层过滤尝试让索引覆盖更多过滤条件
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. 排序利用索引避免 filesortORDER 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                 -- 配合 PgBouncer

8.2 PostgreSQL 核心特性

特性说明MySQL 对比
完整 ACID所有操作默认事务MySQL 5.5+ InnoDB 也支持
复杂查询CTE(WITH 递归)、窗口函数、LATERAL JOINMySQL 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 InnoDBPostgreSQL
历史版本存储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 全维度对比

维度MySQLPostgreSQL
架构多线程多进程
默认引擎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/OrchestratorPatroni/repmgr
连接数更高(线程模型)较低(进程模型),需连接池
社区生态更广泛的云厂商支持更活跃的开源社区
典型用户阿里、腾讯、美团、FacebookApple、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 和窗口函数已大幅改进
大厂都用 MySQLApple、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

#问题核心关键词参考章节
1InnoDB 为什么用 B+ 树?磁盘 IO + 树高度 + 范围查询1.1
2聚簇索引和非聚簇索引区别数据存储方式 + 回表 + 覆盖索引1.2
3最左前缀原则联合索引排序方式 + 失效场景1.3
4Buffer Pool 是什么LRU + 脏页 + 命中率2.1
5Redo/Undo/Binlog 区别物理日志/逻辑日志/层级/作用2.2
6两阶段提交Redo Prepare → Binlog → Redo Commit2.2
7事务隔离级别RC/RR/幻读/MVCC3
8MVCC 实现原理ReadView + trx_id + Undo Log3.2
9当前读 vs 快照读FOR UPDATE / 普通 SELECT3.2
10InnoDB 锁类型Record Lock / Gap Lock / Next-Key Lock4
11死锁怎么排查information_schema / SHOW ENGINE4.2
12EXPLAIN type 类型const/ref/range/index/ALL5.1
13深分页怎么优化覆盖索引 + 延迟关联 + 游标5.1
14分库分表策略Hash/Range/分布式 ID6
15主从延迟怎么解决强制走主库 + 半同步 + 缓存7
16MGR 是什么Paxos + 组复制 + 强一致7.2
17PostgreSQL 多进程优缺点崩溃隔离 vs 连接开销8.1
18PostgreSQL Vacuum 作用死元组清理 + 事务 ID 冻结10.2
19PostgreSQL JSONB 索引GIN / jsonb_path_ops9, 11.1
20MySQL 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 双数据库体系,配合选型对比,适应不同技术栈面试需求。