数据库面试指南

数据库面试指南

一、MySQL 索引

1.1 B+ 树原理

                    [30|60]
                   /   |   \
            [10|20] [40|50] [70|80]
               |       |       |
            叶子节点通过链表连接

1.2 聚簇索引与非聚簇索引

1.3 覆盖索引

-- 索引 idx(name, age)
SELECT name, age FROM user WHERE name = 'Tom'; -- 覆盖索引
SELECT * FROM user WHERE name = 'Tom';          -- 需要回表

1.4 最左前缀原则

1.5 索引失效场景

-- 1. 对索引列使用函数
WHERE YEAR(create_time) = 2024  -- ❌
WHERE create_time >= '2024-01-01' -- ✅

-- 2. 隐式类型转换
WHERE phone = 13800138000  -- ❌ phone 是 varchar
WHERE phone = '13800138000' -- ✅

-- 3. LIKE 左模糊
WHERE name LIKE '%Tom'  -- ❌
WHERE name LIKE 'Tom%'  -- ✅

-- 4. OR 条件（部分列无索引）
WHERE a = 1 OR b = 2  -- b 无索引则全表扫描

-- 5. != 或 NOT IN
WHERE status != 1  -- 可能全表扫描

1.6 索引下推（ICP）

-- 索引 idx(name, age)
SELECT * FROM user WHERE name LIKE 'Tom%' AND age = 25;

1.7 联合索引设计原则

1. 高选择性列在前：区分度高的列放前面

2. 等值查询列在前：= 条件的列优先于范围条件

3. 覆盖常用查询：避免回表

4. 避免冗余索引：(a, b) 已包含 (a) 的功能

-- 查询：WHERE status = 1 AND create_time > '2024-01-01' ORDER BY id
-- 推荐索引：(status, create_time) 或 (status, id)

1.8 前缀索引

ALTER TABLE user ADD INDEX idx_email(email(10));

SELECT COUNT(DISTINCT LEFT(email, 10)) / COUNT(*) FROM user;
-- 选择性越接近 1 越好

二、MySQL 事务

2.1 ACID 特性

2.2 隔离级别

2.3 MVCC 原理

trx_id < min_trx_id → 可见（已提交）
trx_id >= max_trx_id → 不可见（未来事务）
trx_id in m_ids → 不可见（活跃事务）

2.4 当前读与快照读

-- 快照读（MVCC）
SELECT * FROM user WHERE id = 1;

-- 当前读（加锁）
SELECT * FROM user WHERE id = 1 FOR UPDATE;
INSERT/UPDATE/DELETE

三、MySQL 锁

3.1 锁分类

3.2 行锁类型

索引值: 10, 20, 30
Next-Key Lock 锁定 (10, 20]

3.3 死锁

3.4 乐观锁与悲观锁

-- 悲观锁
SELECT * FROM stock WHERE id = 1 FOR UPDATE;
UPDATE stock SET count = count - 1 WHERE id = 1;

-- 乐观锁（版本号）
UPDATE stock SET count = count - 1, version = version + 1
WHERE id = 1 AND version = #{version};

四、MySQL 日志

4.1 三大日志

4.2 redo log

事务提交流程：
1. 写 redo log（prepare）
2. 写 binlog
3. 写 redo log（commit）

4.3 Buffer Pool

Buffer Pool 结构：
- Free List：空闲页链表
- LRU List：已使用页链表（冷热分离）
- Flush List：脏页链表

4.4 InnoDB vs MyISAM

4.5 一条 SQL 执行流程

客户端 → 连接器 → 查询缓存(8.0废弃) → 分析器 → 优化器 → 执行器 → 存储引擎

五、SQL 优化

5.1 EXPLAIN 关键字段

5.2 慢查询优化

1. 开启慢查询日志

SET GLOBAL slow_query_log = ON;
SET GLOBAL long_query_time = 1;

1. 分析执行计划

EXPLAIN SELECT ...;

1. 常见优化：

• 添加合适索引

• 避免 SELECT *

• 分页优化

• 避免索引失效

5.3 分页优化

-- 深分页问题
SELECT * FROM user LIMIT 1000000, 10; -- 慢

-- 优化：延迟关联
SELECT * FROM user
WHERE id >= (SELECT id FROM user LIMIT 1000000, 1)
LIMIT 10;

-- 优化：记录上次 ID
SELECT * FROM user WHERE id > #{lastId} LIMIT 10;

5.4 ORDER BY 优化

-- 利用索引排序（索引 idx(status, create_time)）
SELECT * FROM orders WHERE status = 1 ORDER BY create_time;  -- ✅ 使用索引

-- 无法使用索引排序（需要 filesort）
SELECT * FROM orders WHERE status > 0 ORDER BY create_time;  -- ❌ 范围查询后无法用索引排序

5.5 JOIN 优化

-- 小表驱动大表
SELECT * FROM small_table s
JOIN large_table l ON s.id = l.foreign_id;

-- 确保被驱动表的关联字段有索引
ALTER TABLE large_table ADD INDEX idx_foreign_id(foreign_id);

5.6 子查询 vs JOIN

-- 子查询（可能效率低）
SELECT * FROM orders WHERE user_id IN (SELECT id FROM users WHERE status = 1);

-- 改写为 JOIN
SELECT o.* FROM orders o
JOIN users u ON o.user_id = u.id
WHERE u.status = 1;

5.7 COUNT 优化

COUNT(*) ≈ COUNT(1) > COUNT(主键) > COUNT(字段)

• COUNT(*) 和 COUNT(1) 会被优化器优化

• COUNT(字段) 不统计 NULL

六、分库分表

6.1 拆分方式

6.2 分片策略

6.3 分布式 ID

0 | 41位时间戳 | 10位机器ID | 12位序列号

• 64 位 long 型

• 趋势递增

• 每毫秒可生成 4096 个 ID

6.4 跨库 JOIN 解决方案

1. 全局表：将小表同步到所有分库

2. 字段冗余：将常用字段冗余到主表

3. 应用层组装：分别查询后在应用层合并

4. 数据同步到数仓：通过 ETL 同步后查询

6.5 数据迁移方案

1. 新库创建表结构
2. 开启双写（同时写新旧库）
3. 历史数据迁移
4. 数据校验
5. 切换读流量到新库
6. 停止旧库写入

1. 使用 Canal/DTS 同步 binlog
2. 全量同步 + 增量追平
3. 数据校验
4. 切换流量

6.6 分布式事务

七、主从复制

7.1 复制原理

主库 → binlog → IO线程 → 从库relay log → SQL线程 → 从库数据

7.2 主从延迟

7.3 读写分离

// 强制读主库注解
@Master
public User getUser(Long id) {
    return userMapper.selectById(id);
}

7.4 半同步复制

-- 开启半同步复制
INSTALL PLUGIN rpl_semi_sync_master SONAME 'semisync_master.so';
SET GLOBAL rpl_semi_sync_master_enabled = 1;

7.5 主从切换

1. 停止主库写入
2. 等待从库同步完成
3. 从库提升为主库
4. 修改应用配置

1. 检测主库故障
2. 选择最新的从库
3. 补齐差异日志
4. 提升为新主库
5. 其他从库指向新主库
6. 通知应用层

八、Redis 基础

8.1 数据类型与底层结构

8.2 List 底层结构

quicklist:
  head -> [ziplist] <-> [ziplist] <-> [ziplist] <- tail

8.3 Hash 底层结构

8.4 SDS（Simple Dynamic String）

struct sdshdr {
    int len;      // 已使用长度
    int free;     // 剩余空间
    char buf[];   // 数据
};

8.5 跳表（Skip List）

Level 3:  1 -----------------> 9
Level 2:  1 ------> 5 -------> 9
Level 1:  1 -> 3 -> 5 -> 7 -> 9

• 查找时间复杂度 O(log n)

• 实现简单，易于范围查询

8.6 Redis 为什么快

1. 内存操作

2. 单线程避免锁竞争（6.0 后 IO 多线程）

3. IO 多路复用（epoll）

4. 高效数据结构

九、Redis 持久化

9.1 RDB

触发方式：
- save：阻塞
- bgsave：fork 子进程
- 配置自动触发

9.2 AOF

写入策略：
- always：每条命令都写，最安全
- everysec：每秒写一次（默认）
- no：由操作系统决定

9.3 混合持久化

十、Redis 过期与淘汰

10.1 过期删除策略

10.2 内存淘汰策略

10.3 LRU vs LFU 实现

LFU 计数器结构：
高 16 位：上次访问时间（分钟级）
低 8 位：对数计数器（0-255）

十一、Redis 高可用

11.1 主从复制

全量同步：RDB + 缓冲区命令
增量同步：offset 偏移量

11.2 哨兵模式

11.3 Cluster 集群

HASH_SLOT = CRC16(key) mod 16384

11.4 故障转移流程

1. 节点间定期 PING/PONG 心跳检测
2. 超时未响应标记为 PFAIL（疑似下线）
3. 超过半数主节点认为 PFAIL → FAIL（确认下线）
4. 从节点发起选举（Raft 协议）
5. 获得多数主节点投票的从节点晋升为主节点
6. 新主节点广播 PONG，通知集群配置变更
7. 其他从节点指向新主节点

十二、Redis 缓存问题

12.1 缓存穿透

# 布隆过滤器
if not bloom_filter.exists(key):
    return None  # 一定不存在

12.2 缓存击穿

# 互斥锁
def get_data(key):
    data = redis.get(key)
    if data is None:
        if redis.setnx(lock_key, 1, ex=10):
            data = db.query(key)
            redis.set(key, data, ex=3600)
            redis.delete(lock_key)
        else:
            time.sleep(0.1)
            return get_data(key)
    return data

12.3 缓存雪崩

12.4 热点 Key 问题

# Key 分片示例
def get_hot_key(key):
    shard = random.randint(0, 9)
    return redis.get(f"{key}_{shard}")

12.5 大 Key 问题

redis-cli --bigkeys
redis-cli memory usage <key>

12.6 缓存与数据库一致性

1. 更新数据库
2. 删除缓存
3. 延迟双删（可选）

db.update(data)
redis.delete(key)
time.sleep(0.5)  # 等待主从同步
redis.delete(key)

十三、Redis 应用

13.1 分布式锁

-- 加锁
SET lock_key unique_value NX EX 30

-- 解锁（Lua 脚本保证原子性）
if redis.call("get", KEYS[1]) == ARGV[1] then
    return redis.call("del", KEYS[1])
else
    return 0
end

13.2 延迟队列

# 添加任务
redis.zadd("delay_queue", {task_id: execute_time})

# 消费任务
while True:
    tasks = redis.zrangebyscore("delay_queue", 0, now, limit=10)
    for task in tasks:
        if redis.zrem("delay_queue", task):
            process(task)

13.3 限流

def is_allowed(user_id, limit, window):
    key = f"rate:{user_id}"
    now = time.time()

    # 移除窗口外的记录
    redis.zremrangebyscore(key, 0, now - window)

    # 检查数量
    count = redis.zcard(key)
    if count < limit:
        redis.zadd(key, {now: now})
        redis.expire(key, window)
        return True
    return False

13.4 排行榜

# 增加分数
redis.zincrby("leaderboard", 10, "user:1001")

# 获取排名
redis.zrevrank("leaderboard", "user:1001")

# 获取 Top 10
redis.zrevrange("leaderboard", 0, 9, withscores=True)

13.5 布隆过滤器

# Redis 布隆过滤器（需要 RedisBloom 模块）
# 创建
BF.RESERVE user_filter 0.01 1000000  # 错误率 1%，容量 100 万

# 添加
BF.ADD user_filter user:1001

# 查询
BF.EXISTS user_filter user:1001  # 返回 1 表示可能存在，0 表示一定不存在

13.6 HyperLogLog

# 添加元素
redis.pfadd("uv:page1", "user1", "user2", "user3")

# 获取基数
redis.pfcount("uv:page1")  # 返回 3

# 合并多个 HyperLogLog
redis.pfmerge("uv:total", "uv:page1", "uv:page2")

13.7 GEO 地理位置

# 添加位置
redis.geoadd("locations", 116.405285, 39.904989, "beijing")
redis.geoadd("locations", 121.472644, 31.231706, "shanghai")

# 计算距离
redis.geodist("locations", "beijing", "shanghai", "km")  # 返回 1067.5

# 附近的人（半径 100km）
redis.georadius("locations", 116.405285, 39.904989, 100, "km")

# 获取 geohash
redis.geohash("locations", "beijing")

十四、高频面试题与答案

Q1: B+ 树为什么适合做索引？

1. 磁盘 IO 优化：
• 非叶子节点只存键值，不存数据，单节点可存更多键
• 树高度低（通常 3-4 层），减少磁盘 IO 次数

2. 范围查询高效：
• 叶子节点通过双向链表连接
• 范围查询只需遍历链表，无需回溯

3. 查询稳定：
• 所有数据都在叶子节点，查询路径长度一致
• 时间复杂度稳定 O(log n)

Q2: 聚簇索引和非聚簇索引区别？

Q3: 索引失效场景有哪些？

1. 对索引列使用函数：WHERE YEAR(date) = 2024

2. 隐式类型转换：WHERE phone = 13800138000（phone 是 varchar）

3. LIKE 左模糊：WHERE name LIKE '%Tom'

4. OR 条件部分列无索引

5. 使用 != 或 NOT IN

6. 联合索引不满足最左前缀

7. 范围查询后的列失效

Q4: MVCC 原理是什么？

Q5: 事务隔离级别及解决的问题？

• 脏读：读到未提交的数据

• 不可重复读：同一事务内两次读取结果不同

• 幻读：同一事务内两次查询行数不同

Q6: MySQL 锁类型有哪些？

Q7: redo log 和 binlog 区别？

Q8: 慢查询如何优化？

1. 开启慢查询日志：slow_query_log = ON

2. 使用 EXPLAIN 分析：关注 type、key、rows、Extra

3. 优化索引：
• 添加缺失索引
• 优化联合索引顺序
• 使用覆盖索引

4. SQL 改写：
• 避免 SELECT *
• 分页优化
• 子查询改 JOIN

5. 表结构优化：分库分表、归档历史数据

Q9: Redis 持久化方式？

Q10: 缓存穿透/击穿/雪崩的区别和解决方案？

Q11: 分布式锁如何实现？

# 加锁
SET lock_key unique_value NX PX 30000

# 解锁（Lua 脚本保证原子性）
if redis.call("get", KEYS[1]) == ARGV[1] then
    return redis.call("del", KEYS[1])
end

Q12: 缓存一致性如何保证？

1. 更新数据库
2. 删除缓存
3. 读请求时重建缓存

1. 删除缓存
2. 更新数据库
3. 延迟一段时间再删除缓存

Q13: 主从复制原理？

1. 从库执行 CHANGE MASTER TO，配置主库信息
2. 从库启动 IO 线程，连接主库
3. 主库启动 binlog dump 线程，发送 binlog
4. 从库 IO 线程接收，写入 relay log
5. 从库 SQL 线程读取 relay log，重放 SQL

最后更新：2026-01-09