MySQL 数据库 #

关系型数据库核心知识，覆盖索引优化、事务、锁、主从复制等

---

📋 目录 #

• 索引原理与优化
• 事务与隔离级别
• 锁机制
• 主从复制
• 分库分表

---

索引原理与优化 #

面试必考 ⭐⭐⭐⭐⭐

索引数据结构 #

结构	适用场景	时间复杂度	MySQL支持
B+树	范围查询、排序、全值查询	O(log n)	✅ InnoDB/MyISAM
哈希	等值查询	O(1)	✅ Memory引擎
全文索引	文本搜索	-	✅ MyISAM/InnoDB

B+树结构 #

B+树特点:

特性	说明
非叶子节点只存键	节点大，树矮（3-4层）
叶子节点存数据	支持范围查询
叶子节点双向链表	范围查询高效

索引类型 #

-- 1. 主键索引 (聚簇索引)
PRIMARY KEY (id)

-- 2. 唯一索引
UNIQUE KEY uk_name (name)

-- 3. 普通索引
KEY idx_age (age)

-- 4. 联合索引
KEY idx_name_age (name, age)

-- 5. 全文索引
FULLTEXT KEY idx_content (content)

聚簇索引 vs 非聚簇索引 #

特性	聚簇索引	非聚簇索引
数量	每个表1个	可以有多个
存储	数据行	键值+主键
二级索引查找	二次查找（回表）	也是回表
MySQL	InnoDB主键	所有辅助索引

回表示例:

-- 二级索引查找流程
SELECT * FROM user WHERE age = 25;

-- 1. 在age索引中找到 age=25 的行，得到主键id
-- 2. 通过主键id到聚簇索引中找到完整数据

最左前缀原则 #

-- 联合索引: (name, age, gender)

-- ✅ 命中索引
WHERE name = 'Tom'                    -- 命中索引第一列
WHERE name = 'Tom' AND age = 25       -- 命中索引前两列
WHERE name = 'Tom' AND gender = 'male'-- 命中索引第一列+跳查

-- ❌ 未命中索引
WHERE age = 25                        -- 未找到索引开头
WHERE gender = 'male'                 -- 未找到索引开头
WHERE age = 25 AND gender = 'male'    -- 未找到索引开头

-- ✅ 范围查询
WHERE name = 'Tom' AND age > 20 AND age < 30  -- 前两列精确匹配，第三列范围

索引失效场景 #

场景	说明	示例
函数运算	对索引列使用函数	WHERE YEAR(create_time) = 2024
隐式转换	类型不匹配	WHERE phone = 123（phone是字符串）
LIKE左模糊	%开头	WHERE name LIKE '%Tom%'
OR条件	使用OR且未都建索引	WHERE id = 1 OR name = 'Tom'
负向查询	!=、<>、NOT IN	WHERE status != 1w
类型转换	索引列有计算	WHERE id + 1 = 2

索引设计建议 #

-- 1. 区分度高的字段建索引（selectivity越高越好）
-- 区分度 = COUNT(DISTINCT(column)) / COUNT(*)

-- 2. 覆盖索引避免回表
-- 设计联合索引包含查询字段
KEY idx_user_id_status (user_id, status, create_time)

-- 3. 避免重复索引
-- × 冗余: (a) 和 (a, b)
-- ✓ 推荐: 只有 (a, b)

-- 4. 字符串前缀索引（减少索引大小）
KEY idx_name (name(10))  -- 只索引前10个字符

索引优化实战 #

-- 分析SQL执行计划
EXPLAIN SELECT * FROM user WHERE age = 25;

-- 关键指标
-- type: ALL（全表扫描）→ index → range → ref → eq_ref → const（最好）
-- rows: 预扫描行数
-- key: 实际使用的索引
-- Extra: Using index（覆盖索引）、Using filesort（额外排序）、Using temporary（临时表）

-- 强制使用/忽略索引
SELECT * FROM user USE INDEX(idx_age) WHERE age = 25;
SELECT * FROM user IGNORE INDEX(idx_age) WHERE age = 25;

索引查询执行流程 #

面试必考 ⭐⭐⭐⭐⭐

问题：执行 SELECT * FROM user WHERE id = 10000，索引查询的具体过程是什么？

1. 查询流程图 #

2. 详细执行步骤 #

假设 id 是主键（聚簇索引），执行 WHERE id = 10000 的完整过程：

步骤1: 确定使用哪个索引

优化器分析 → 主键索引是唯一且最快的 → 选择 PRIMARY 索引

步骤2: 从根节点开始查找

步骤3: 查找中间节点

步骤4: 定位到叶子节点

步骤5: 返回结果给执行器

3. 页加载与Buffer Pool #

4. 聚簇索引 vs 二级索引查询对比 #

查询类型	聚簇索引查询 (WHERE id=10000)	二级索引查询 (WHERE age=25)
步骤1	从根节点找id=10000	从age索引根节点找age=25
步骤2	到叶子节点直接取数据	到叶子节点取主键id
步骤3	返回数据	回表: 用id去聚簇索引找数据
IO次数	树的高度次	树的高度 × 2

二级索引回表示例:

5. 关键知识点总结 #

知识点	说明
页(Page)	InnoDB存储的基本单位，默认16KB
Buffer Pool	缓存热点数据，减少磁盘IO
B+树高度	3-4层可存千万级数据，每次查询3-4次IO
聚簇索引	叶子节点存完整数据，一次查找完成
二级索引	叶子节点存主键，需要回表

---

事务与隔离级别 #

事务四大特性 ACID #

特性	说明	实现方式
原子性	要么全成功，要么全失败	Undo Log
一致性	事务前后数据状态一致	原子性+隔离性+持久性保证
隔离性	并发事务互不干扰	MVCC + 锁
持久性	提交后永久生效	Redo Log

四种隔离级别 #

隔离级别	脏读	不可重复读	幻读	MySQL默认
读未提交	✅	✅	✅	-
读已提交	❌	✅	✅	PostgreSQL
可重复读	❌	❌	✅	✅ MySQL
串行化	❌	❌	❌	-

SQL设置隔离级别:

-- 查看当前隔离级别
SELECT @@transaction_isolation;

-- 设置隔离级别
SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;
SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ;
SET SESSION TRANSACTION ISOLATION LEVEL SERIALIZABLE;

-- 启动事务
START TRANSACTION;

-- 提交/回滚
COMMIT;
ROLLBACK;

MVCC 多版本并发控制 #

Redo Log 重做日志 #

面试必考 ⭐⭐⭐⭐⭐

问题：Redo Log是什么？它的作用和工作原理？

1. Redo Log 的作用 #

特性	说明
作用	保证事务的持久性 (Durability)
内容	物理日志，记录"哪个页做了什么修改"
大小	固定大小，循环写入（默认48MB）
文件	ib_logfile0, ib_logfile1

2. Write Ahead Log (WAL) 机制 #

核心思想: 先写日志，再写数据

3. Redo Log 写入流程 #

4. 两阶段提交 (2PC) #

为什么需要两阶段提交？ → 保证 Redo Log 和 Binlog 的一致性！

宕机恢复场景:

Redo Log状态	Binlog状态	恢复策略
Prepare	无	回滚事务
Prepare	有	提交事务
Commit	有	无需处理

5. Redo Log vs Binlog 区别 #

对比项	Redo Log	Binlog
作用	崩溃恢复、保证持久性	主从复制、归档、时间点恢复
记录内容	物理日志（页修改）	逻辑日志（SQL或行变更）
写入时机	事务执行过程中持续写	事务提交时写
文件大小	固定大小，循环覆盖	追加写入，可配置大小切换
存储引擎	InnoDB特有	Server层，所有引擎共用
恢复方式	重放Redo Log，恢复数据页	用Binlog恢复（主从或误操作）

6. 关键参数配置 #

-- 查看Redo Log相关参数
SHOW VARIABLES LIKE 'innodb_log%';

7. Redo Log 的循环写入 #

• write pos: 当前写入位置
• checkpoint: 可以覆盖的位置（脏页已刷盘）
• write pos 到 checkpoint 之间是可恢复的日志

Undo Log 回滚日志 #

面试必考 ⭐⭐⭐⭐⭐

问题：Undo Log是什么？它的作用和工作原理？

1. Undo Log 的作用 #

特性	说明
作用	回滚事务、MVCC快照读
内容	逻辑日志，记录"如何撤销"
存储	系统表空间(ibdata1)或独立Undo表空间
复用	Undo Log可以被复用，循环使用

2. Undo Log 记录格式 #

3. 回滚流程示例 #

4. Undo Log 与 MVCC #

5. Undo Log 的清理与复用 #

Undo Log的生命周期:

6. 关键参数配置 #

-- 查看Undo Log相关参数
SHOW VARIABLES LIKE 'innodb_undo%';

---

锁机制 #

锁类型 #

行锁示例 #

-- 显式加锁
SELECT * FROM user WHERE id = 1 FOR UPDATE;  -- 排他锁（写锁）
SELECT * FROM user WHERE id = 1 LOCK IN SHARE MODE;  -- 共享锁（读锁，MySQL8.0用FOR SHARE）

-- 事务中的行锁
START TRANSACTION;
SELECT * FROM user WHERE id = 1 FOR UPDATE;
-- 其他事务无法修改id=1的行
UPDATE user SET name = 'Tom' WHERE id = 1;
COMMIT;

乐观锁实现 #

-- 1. 版本号方式
ALTER TABLE user ADD COLUMN version INT DEFAULT 0;

-- 更新时检查版本
UPDATE user
SET name = 'Tom', version = version + 1
WHERE id = 1 AND version = 0;

-- 影响行数=0，说明版本冲突

-- 2. 时间戳方式
UPDATE user
SET name = 'Tom', update_time = NOW()
WHERE id = 1 AND update_time = '2023-01-01 10:00:00';

死锁与检测 #

-- 死锁示例
-- 事务1
START TRANSACTION;
UPDATE account SET balance = balance - 100 WHERE id = 1;
-- 等待...
UPDATE account SET balance = balance + 100 WHERE id = 2;

-- 事务2
START TRANSACTION;
UPDATE account SET balance = balance - 100 WHERE id = 2;
-- 等待...
UPDATE account SET balance = balance + 100 WHERE id = 1;
-- ⚠️ 死锁！

-- MySQL自动检测死锁，回滚其中一个事务

-- 查看锁信息
SHOW ENGINE INNODB STATUS;
-- 查看 LATEST DETECTED DEADLOCK 部分

---

主从复制 #

复制原理 #

复制流程:

1. Master记录变更到Binlog
2. Slave IO线程读取Binlog，写入Relay Log
3. Slave SQL线程重放Relay Log到数据

复制模式 #

模式	优点	缺点
异步复制	Master无阻塞	可能数据丢失
半同步复制	可靠性高	性能略降
全同步复制	数据最可靠	性能最差

配置主从复制 #

-- Master配置 (my.cnf)
[mysqld]
server-id = 1
log-bin = mysql-bin
binlog_format = ROW

-- 创建复制用户
CREATE USER 'repl'@'%' IDENTIFIED BY 'password';
GRANT REPLICATION SLAVE ON *.* TO 'repl'@'%';
FLUSH PRIVILEGES;

-- 查看Master状态
SHOW MASTER STATUS;
-- 记录 File 和 Position

-- Slave配置 (my.cnf)
[mysqld]
server-id = 2
relay-log = relay-bin
read-only = 1

-- 配置Slave
CHANGE MASTER TO
  MASTER_HOST='master_ip',
  MASTER_USER='repl',
  MASTER_PASSWORD='password',
  MASTER_LOG_FILE='mysql-bin.000001',
  MASTER_LOG_POS=123;

-- 启动Slave
START SLAVE;

-- 检查Slave状态
SHOW SLAVE STATUS\G
-- Slave_IO_Running: Yes
-- Slave_SQL_Running: Yes

延迟问题与优化 #

---

分库分表 #

为什么分库分表 #

分库分表策略 #

维度	策略	优点	缺点
垂直分库	按业务模块	职责清晰，降低单机压力	跨库查询复杂
垂直分表	按字段访问频率	减少磁盘IO	查询需JOIN
水平分库	按数据量扩展	提高并发能力	分片路由复杂
水平分表	按数据量拆分	提升查询性能	跨表查询复杂

路由算法 #

// 1. 哈希取模（最常用）
int db = user_id % db_count;
int table = user_id % table_count;

// 2. 范围分片
// user_id 0-1000 → db1.table1
// user_id 1001-2000 → db1.table2

// 3. 字符串哈希（如用户名）
int hash =(userName.hashCode());
int db = Math.abs(hash) % db_count;

// 4. 一致性哈希
// 环形结构，节点变化影响最小

分库分表挑战 #

挑战	解决方案
主键ID	雪花算法、UUID、号段
跨分片查询	分布式事务、数据冗余
分页查询	两次查询（获取最大ID）
排序聚合	先分片聚合再汇总
扩缩容	预留分片槽位、数据迁移工具

---

面试题汇总 #

索引篇 #

1. 为什么使用B+树作为索引结构？
2. 聚簇索引与非聚簇索引区别？
3. 最左前缀原则是什么？
4. 索引失效场景有哪些？
5. 什么是回表？如何避免？
6. 索引查询执行流程？(WHERE id=10000) ⭐⭐⭐⭐⭐

事务篇 #

1. ACID特性及实现？
2. 四种隔离级别及解决的问题？
3. MVCC原理？
4. 幻读如何解决？
5. 事务隔离级别设置？
6. Redo Log的作用和原理？ ⭐⭐⭐⭐⭐
7. Undo Log的作用和原理？ ⭐⭐⭐⭐⭐
8. Redo Log与Binlog的区别？

锁篇 #

1. MySQL有哪些锁类型？
2. 行锁粒度与性能？
3. 乐观锁 vs 悲观锁？
4. 死锁与解决？
5. GAP锁作用？

主从复制篇 #

1. 主从复制原理？
2. Binlog三种格式区别？
3. 延迟问题及优化？
4. 半同步复制原理？

分库分表篇 #

1. 为什么分库分表？
2. 分片策略选择？
3. 分库分表带来的问题？
4. 主键生成方案？
5. 跨分片查询如何处理？

---

面试题答案详解 #

索引篇 #

1. 为什么使用B+树作为索引结构？

答案:

B+树特性	优势
多叉树	树的高度更低(一般3-4层)，查询IO次数更少
叶子节点有顺序	叶子节点通过双向链表连接，范围查询极快
数据全在叶子节点	非叶子节点只存键，不存数据，相同磁盘页能存更多索引，树更矮
查询稳定	每次查询都要到叶子节点，查询性能稳定

对比其他结构:

结构	缺点
二叉树	树高可能很大，IO次数多
平衡树	频繁旋转，维护成本高，范围查询需中序遍历
哈希索引	不支持范围查询

---

2. 聚簇索引与非聚簇索引区别？

答案:

对比项	聚簇索引	非聚簇索引
数据存储	索引和数据在一起	索引和数据分离
查找方式	直接通过索引找到数据行	先找索引，再回表查
一个表几个	只能有一个	可以有多个
InnoDB主键	默认聚簇索引	非主键是非聚簇索引

回表示例:

-- 查询age=25，使用idx_age索引（非聚簇）
-- 流程：
-- 1. 通过idx_age找到主键id=1001
-- 2. 通过主键回表找到完整数据行 ←
SELECT * FROM user WHERE age = 25;

---

3. 最左前缀原则是什么？

答案:

联合索引按照定义顺序，从左往右使用索引。

-- 联合索引 (name, age, gender)
CREATE INDEX idx_name_age_gender ON user(name, age, gender);

-- ✅ 使用索引
WHERE name = 'Tom'                     -- 使用name
WHERE name = 'Tom' AND age = 25        -- 使用name, age
WHERE name = 'Tom' AND gender = 'male' -- 使用name

-- ❌ 不使用索引
WHERE age = 25                         -- 跳过name，索引失效

---

4. 索引失效场景有哪些？

答案:

场景	示例
函数运算	WHERE YEAR(create_time) = 2024
隐式类型转换	WHERE phone = 123（phone是varchar类型）
LIKE左模糊	WHERE name LIKE '%Tom'
OR条件	WHERE id = 1 OR name = 'Tom'
负向查询	WHERE status != 1

---

5. 什么是回表？如何避免？

答案:

回表:从二级索引查到主键后，需要再去聚簇索引查完整数据行的过程。

避免回表:使用覆盖索引（查询字段完全包含在索引中）。

-- 联合索引 (user_id, status)
CREATE INDEX idx_user_status ON order(user_id, status);

-- ✅ 覆盖索引，不需要回表
SELECT user_id, status FROM order WHERE user_id = 1001;

---

6. 索引查询执行流程？(WHERE id=10000) ⭐⭐⭐⭐⭐

答案:

假设id是主键（聚簇索引），查询步骤：

步骤	说明
1	优化器选择PRIMARY索引
2	从Buffer Pool或磁盘加载根节点
3	根节点二分查找，找到中间节点的指针
4	加载中间节点，继续二分查找
5	加载叶子节点，找到id=10000的行
6	返回完整数据行

关键点:

• B+树高度一般3-4层，查询需要3-4次IO
• 聚簇索引叶子节点存完整数据，不需要回表
• 先查Buffer Pool，未命中才读磁盘
• 页(Page)是InnoDB存储的基本单位(16KB)

二级索引 vs 聚簇索引:

• 聚簇索引: 一次查找，直接取数据
• 二级索引: 先查索引拿到主键，再回表查聚簇索引

---

事务篇 #

1. ACID特性及实现？

答案:

特性	说明	实现机制
原子性	要么全成功，要么全失败	Undo Log
一致性	事务前后数据状态一致	由其他三个特性保证
隔离性	并发事务互不干扰	锁 + MVCC
持久性	提交后永久生效	Redo Log + Binlog

---

2. 四种隔离级别及解决的问题？

答案:

隔离级别	脏读	不可重复读	幻读	说明
Read Uncommitted	✅	✅	✅	能读未提交的数据，几乎不用
Read Committed	❌	✅	✅	只能读已提交的，Oracle默认
Repeatable Read	❌	❌	✅	可重复读，MySQL默认
Serializable	❌	❌	❌	串行化，性能差

---

3. MVCC原理？

答案:

MVCC = Multi-Version Concurrency Control，多版本并发控制。

核心组件:

组件	作用
Undo Log	保存旧版本，用于回滚和MVCC
Read View	判断哪些版本可见
隐藏列	DB_TRX_ID（事务ID）、DB_ROLL_PTR（回滚指针）

---

4. 幻读如何解决？

答案:

MySQL RR级别使用Next-Key Lock解决幻读:

锁类型	说明
Record Lock	记录锁，锁单行
Gap Lock	间隙锁，锁范围不包含记录
Next-Key Lock	前两者结合，锁范围+记录

---

5. 事务隔离级别设置？

答案:

-- 查看
SELECT @@transaction_isolation;

-- 设置
SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;
SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ;

---

6. Redo Log的作用和原理？ ⭐⭐⭐⭐⭐

答案:

作用: 保证事务的持久性，崩溃恢复。

核心机制: WAL (Write Ahead Log)

• 先写日志，再写数据
• 事务提交时只需要Redo Log刷盘，不需要数据刷盘
• 后台线程异步刷脏页

写入流程:

1. 修改Buffer Pool中的数据页
2. 写Redo Log Buffer
3. 事务提交时Redo Log刷盘
4. 后台线程异步刷数据页

两阶段提交:

• Prepare: Redo Log写入Prepare状态
• 写Binlog
• Commit: Redo Log标记为Commit

关键参数:

• innodb_flush_log_at_trx_commit
  • 0: 每秒刷盘，性能最好但不安全
  • 1: 每次提交刷盘，最安全但性能差（默认）
  • 2: 写入OS Cache，折中

---

7. Undo Log的作用和原理？ ⭐⭐⭐⭐⭐

答案:

两大作用:

1. 事务回滚 → 保证原子性
2. MVCC快照读 → 提供多版本，实现隔离性

存储内容:

• 逻辑日志，记录"如何撤销"
• 保存修改前的旧值

回滚流程:

• 出错时，沿着Undo Log反向执行，把数据改回去

与MVCC关系:

• Undo Log构成版本链
• Read View判断哪个版本可见
• 实现读写不冲突

清理机制:

• Purge线程清理不需要的旧版本
• Undo Log可以复用

---

8. Redo Log与Binlog的区别？

答案:

对比项	Redo Log	Binlog
作用	崩溃恢复、持久性	主从复制、归档
内容	物理日志（页修改）	逻辑日志（SQL/行变更）
时机	事务执行过程中写	事务提交时写
大小	固定大小，循环覆盖	追加写入，可切换
引擎	InnoDB特有	Server层，所有引擎共用
恢复	重放恢复数据页	主从/误操作恢复

---

锁篇 #

1. MySQL有哪些锁类型？

答案:

锁兼容性:

请求/持有	S	X
S	✅兼容	❌冲突
X	❌冲突	❌冲突

---

2. 行锁粒度与性能？

答案:

锁	粒度	性能	场景
表锁	大	差	全表操作
记录锁	小	好	单行修改
Next-Key Lock	中	中	范围查询

注意行锁锁的是索引，不是记录:

-- 不走索引 → 全表扫描→锁整个表！！
UPDATE user SET name = 'Tom' WHERE name = 'Tom';

---

3. 乐观锁 vs 悲观锁？

答案:

对比	乐观锁	悲观锁
假设	假设冲突少	假设冲突多
实现	版本号/CAS	显式加锁 (SELECT FOR UPDATE)
适用	多读少写	多写

---

4. 死锁与解决？

答案:

死锁条件:

1. 互斥条件
2. 请求与保持
3. 不可剥夺
4. 循环等待

查看和解决:

-- 查看死锁日志
SHOW ENGINE INNODB STATUS;

---

5. GAP锁作用？

答案:

Gap锁作用: 防止幻读，锁范围不包含记录。

---

主从复制篇 #

1. 主从复制原理？

答案:

---

2. Binlog三种格式区别？

答案:

格式	说明	优点	缺点
Statement	记录SQL语句	日志小	相同SQL数据可能不一致
Row	记录每行数据变更	数据一致，安全	日志很大
Mixed	混合前两者	平衡	需要选时机

---

3. 延迟问题及优化？

答案:

优化方案:

-- MySQL 8.0+多线程复制
SET GLOBAL slave_parallel_workers = 4;

---

4. 半同步复制原理？

答案:

复制类型	说明	性能	可靠性
异步	Master写完Binlog就返回	快	差
半同步	Master等至少一个Slave确认收到才返回	中	中
全同步	Master等所有Slave确认才返回	慢	高

---

分库分表篇 #

1. 为什么分库分表？

答案:

数据量	问题
1000万-5000万	查询变慢
5000万以上	必须考虑分库分表

解决的问题:

1. 表大，索引大，查询慢
2. 数据量太大，单库扛不住
3. 备份恢复困难

---

2. 分片策略选择？

答案:

策略	说明	优点	缺点
Hash取模	user_id % N	数据均匀	扩容困难
范围分片	user_id 0-1000	扩容容易	数据可能不均

---

3. 分库分表带来的问题？

答案:

问题	解决方案
主键ID	雪花算法/UUID/号段
跨库查询	数据冗余/全局表
分页排序	先查每个分片再汇总
分布式事务	Saga/Seata

---

4. 主键生成方案？

答案:

方案	说明	优缺点
UUID	128位	无序，索引性能差
自增主键	简单	多实例冲突
雪花算法	64位	趋势递增，分布式

---

5. 跨分片查询如何处理？

答案:

方案	说明	适用场景
全局表	每个库都有一份	字典/配置表
字段冗余	把关联字段冗余过来	少量关联
应用层Join	查完A库再查B库	数据量小
中间件	ShardingSphere/MyCat	通用

---

🔗 相关笔记 #

• 架构设计
• RocketMQ
• Redis

---

最后更新: 2026-05-13