mobile wallpaper 1mobile wallpaper 2mobile wallpaper 3mobile wallpaper 4
Profile Image of the Author
Souloss
公告
欢迎来到我的博客！这是一条示例公告
标签
3286 字
9 分钟
MySQL 面试题
2024-02-11
面试
面试
/
数据库
/
工程实践

本文整理了 MySQL 面试中常见的知识点，涵盖索引原理、事务隔离、锁机制、存储引擎对比、SQL 优化以及分库分表等核心内容。

一、MySQL 索引原理#

1.1 什么是索引？为什么需要索引？#

索引是帮助 MySQL 高效获取数据的数据结构，类似于书籍的目录。索引存储在存储引擎中，不同存储引擎实现的索引类型可能不同。

索引的作用：

  • 提高查询效率：通过索引可以快速定位数据，避免全表扫描
  • 降低 I/O 成本：减少磁盘读取次数
  • 加速排序和分组：索引本身有序，可优化 ORDER BY 和 GROUP BY 操作
  • 唯一性约束：通过唯一索引保证数据唯一性

索引的代价：

  • 占用额外的存储空间
  • INSERT、UPDATE、DELETE 操作需要维护索引，降低写入性能

1.2 MySQL 有哪些索引类型？#

按数据结构分类：

索引类型说明
B+树索引InnoDB 默认索引类型，适合范围查询
Hash 索引Memory 引擎支持，只支持等值查询
全文索引用于全文搜索，InnoDB 5.6+ 支持
空间索引用于地理数据，基于 R-Tree

按逻辑功能分类：

索引类型说明
主键索引数据唯一且不能为 NULL，一张表只能有一个
唯一索引数据唯一但允许 NULL
普通索引基本索引类型，无限制
组合索引多个字段组合的索引
前缀索引对字符串的前几个字符建立索引

1.3 为什么 MySQL 使用 B+树而不是 B 树？#

B+树相比 B 树的优势：

┌─────────────────────────────────────────────────────────┐
│                    B+树 vs B树                          │
├─────────────────────────────────────────────────────────┤
│  B+树特点：                                              │
│  1. 非叶子节点不存储数据，只存储键值                      │
│  2. 叶子节点存储所有数据，并用指针串联                    │
│  3. 单个节点可存储更多键值，树更矮胖                      │
│  4. 范围查询效率更高（叶子节点链表遍历）                   │
├─────────────────────────────────────────────────────────┤
│  MySQL 选择 B+树的原因：                                 │
│  • 减少磁盘 I/O：树更矮，读取次数更少                     │
│  • 范围查询优化：叶子节点链表可直接遍历                   │
│  • 全表扫描更快：只需遍历叶子节点链表                     │
└─────────────────────────────────────────────────────────┘

关键点：B+树的非叶子节点不存储数据，使得每个节点能存储更多索引键值，从而降低树的高度。对于磁盘存储来说，减少一次 I/O 读取就能大幅提升性能。

1.4 什么是聚簇索引和非聚簇索引？#

聚簇索引（Clustered Index）：

  • 索引结构和数据存储在一起
  • InnoDB 的主键索引就是聚簇索引
  • 一张表只能有一个聚簇索引
  • 数据按主键顺序物理存储

非聚簇索引（Secondary Index）：

  • 索引和数据分开存储
  • 叶子节点存储的是主键值，不是数据地址
  • 查询时需要「回表」操作：先查二级索引得到主键，再回主键索引查数据
-- 回表示例
-- 假设有索引 idx_name(name)，查询：
SELECT * FROM user WHERE name = '张三';


-- 执行过程：
-- 1. 在 idx_name 中找到 name='张三' 的主键 id
-- 2. 根据主键 id 回聚簇索引查找完整数据

如何避免回表？ 使用覆盖索引，即查询的字段都在索引中：

-- 创建组合索引
CREATE INDEX idx_name_age ON user(name, age);


-- 使用覆盖索引，无需回表
SELECT name, age FROM user WHERE name = '张三';

1.5 什么是最左前缀原则？#

组合索引遵循最左前缀原则，即索引 (a, b, c) 可以支持以下查询：

-- 走索引
WHERE a = 1
WHERE a = 1 AND b = 2
WHERE a = 1 AND b = 2 AND c = 3


-- 不走索引
WHERE b = 2           -- 缺少最左列 a
WHERE c = 3           -- 缺少最左列 a
WHERE b = 2 AND c = 3 -- 缺少最左列 a


--  部分走索引（只用 a）
WHERE a = 1 AND c = 3 -- a 走索引，c 不走（中间断了）

注意事项：

  • 范围查询（>、<、BETWEEN、LIKE）会终止索引传递
  • =、IN 可以乱序，优化器会自动调整顺序

1.6 什么是索引下推（Index Condition Pushdown）？#

索引下推是 MySQL 5.6 引入的优化，将索引过滤条件下推到存储引擎层处理。

-- 假设有索引 idx_name_age(name, age)
SELECT * FROM user WHERE name LIKE '张%' AND age = 20;

无索引下推时：

  1. 存储引擎根据 name LIKE '张%' 筛选出多条记录
  2. 返回给 Server 层，Server 层再根据 age = 20 过滤

有索引下推时：

  1. 存储引擎根据 name LIKE '张%' AND age = 20 直接过滤
  2. 只返回符合条件的记录给 Server 层

优势：减少回表次数，降低存储引擎和 Server 层之间的数据传输。

二、事务隔离级别#

2.1 什么是事务的 ACID 特性？#

特性说明
Atomicity（原子性）事务是不可分割的工作单位，要么全部成功，要么全部失败
Consistency（一致性）事务执行前后，数据从一个一致状态变换到另一个一致状态
Isolation（隔离性）多个事务并发执行时，一个事务的执行不应影响其他事务
Durability（持久性）事务一旦提交，对数据的修改是永久性的

2.2 MySQL 有哪些事务隔离级别？#

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


-- 设置隔离级别
SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;
隔离级别脏读不可重复读幻读
READ UNCOMMITTED（读未提交）是是是
READ COMMITTED（读已提交）否是是
REPEATABLE READ（可重复读）否否是（InnoDB 解决）
SERIALIZABLE（串行化）否否否

InnoDB 默认隔离级别：REPEATABLE READ（可重复读），并通过 MVCC 和 Next-Key Lock 解决了幻读问题。

2.3 什么是脏读、不可重复读、幻读？#

┌────────────────────────────────────────────────────────────────┐
│                        并发问题说明                            │
├────────────────────────────────────────────────────────────────┤
│  脏读：读到其他事务未提交的数据                                 │
│  ┌─────────┬───────────────────────────────────────────────┐  │
│  │ 事务A   │ UPDATE user SET age=20 WHERE id=1            │  │
│  │ 事务B   │ SELECT age FROM user WHERE id=1  -- 读到20    │  │
│  │ 事务A   │ ROLLBACK  -- 事务B读到的数据是无效的           │  │
│  └─────────┴───────────────────────────────────────────────┘  │
├────────────────────────────────────────────────────────────────┤
│  不可重复读：同一事务内两次读取结果不同（针对 UPDATE）          │
│  ┌─────────┬───────────────────────────────────────────────┐  │
│  │ 事务A   │ SELECT age FROM user WHERE id=1  -- 返回 18   │  │
│  │ 事务B   │ UPDATE user SET age=20 WHERE id=1            │  │
│  │ 事务B   │ COMMIT                                     │  │
│  │ 事务A   │ SELECT age FROM user WHERE id=1  -- 返回 20  │  │
│  └─────────┴───────────────────────────────────────────────┘  │
├────────────────────────────────────────────────────────────────┤
│  幻读：同一事务内两次读取记录数不同（针对 INSERT/DELETE）       │
│  ┌─────────┬───────────────────────────────────────────────┐  │
│  │ 事务A   │ SELECT * FROM user WHERE age>18  -- 5 条      │  │
│  │ 事务B   │ INSERT INTO user(age) VALUES(20)             │  │
│  │ 事务B   │ COMMIT                                     │  │
│  │ 事务A   │ SELECT * FROM user WHERE age>18  -- 6 条      │  │
│  └─────────┴───────────────────────────────────────────────┘  │
└────────────────────────────────────────────────────────────────┘

2.4 什么是 MVCC？如何实现的？#

MVCC（Multi-Version Concurrency Control，多版本并发控制） 是一种不加锁就能实现并发读的机制，核心思想是为每次事务生成数据快照。

核心组件：

  1. 隐藏字段：每行记录包含 DB_TRX_ID（事务 ID）、DB_ROLL_PTR（回滚指针）
  2. Undo Log：存储数据的历史版本，形成版本链
  3. Read View：定义事务可见性规则

Read View 可见性判断：

Read View 包含：
- min_trx_id：当前活跃事务中最小的事务 ID
- max_trx_id：下一个将分配的事务 ID
- m_ids：活跃事务 ID 列表
- creator_trx_id：创建该 Read View 的事务 ID


判断规则：
1. DB_TRX_ID < min_trx_id → 可见（事务已提交）
2. DB_TRX_ID >= max_trx_id → 不可见（事务后开启）
3. DB_TRX_ID 在 m_ids 中 → 不可见（事务未提交）
4. DB_TRX_ID = creator_trx_id → 可见（自己的修改）
5. 否则 → 可见（事务已提交）

不同隔离级别的 MVCC 实现：

  • READ COMMITTED：每次 SELECT 生成新的 Read View
  • REPEATABLE READ：只在第一次 SELECT 生成 Read View，后续复用

2.5 MySQL 如何解决幻读？#

InnoDB 在 REPEATABLE READ 隔离级别下，通过 MVCC + Next-Key Lock 解决幻读：

快照读（普通 SELECT）：通过 MVCC 解决，读取历史快照数据。

当前读（SELECT … FOR UPDATE、INSERT、UPDATE、DELETE）：通过 Next-Key Lock 解决。

Next-Key Lock = Record Lock（记录锁）+ Gap Lock（间隙锁）


假设表中有数据：id = 1, 5, 10
执行：SELECT * FROM user WHERE id > 3 AND id < 8 FOR UPDATE


锁定范围：
- Record Lock：锁定 id = 5 这条记录
- Gap Lock：锁定 (1, 5) 和 (5, 10) 两个间隙


效果：其他事务无法在这个范围内插入新记录

三、MySQL 锁机制#

3.1 MySQL 有哪些锁类型？#

按锁粒度分类：

锁类型说明开销并发度
表锁锁定整张表小低
行锁锁定单行记录大高
页锁锁定数据页中中

按锁模式分类：

锁类型说明
共享锁（S 锁）允许多个事务同时读取
排他锁（X 锁）只允许一个事务写入，阻塞其他读写
意向锁表级锁，表示事务打算在行上加锁

意向锁的作用：当事务要在行上加锁时，先在表上加意向锁。其他事务想加表锁时，只需检查意向锁，无需逐行检查。

3.2 InnoDB 行锁有哪些类型？#

锁类型说明
Record Lock锁定单条索引记录
Gap Lock锁定索引记录之间的间隙，不包含记录本身
Next-Key LockRecord Lock + Gap Lock，锁定记录及前面的间隙
Insert Intention Lock插入意向锁，INSERT 操作时设置

锁兼容矩阵：

           |  S   |  X   |  IS  |  IX  |
───────────┼──────┼──────┼──────┼──────|
S (共享)   |  是  |  否  |  是  |  否  |
X (排他)   |  否  |  否  |  否  |  否  |
IS (意向共享)|  是  |  否  |  是  |  是  |
IX (意向排他)|  否  |  否  |  是  |  是  |

3.3 什么时候会加行锁？什么时候会加表锁？#

行锁加锁场景：

  • SELECT ... FOR UPDATE：加排他行锁
  • SELECT ... LOCK IN SHARE MODE：加共享行锁
  • UPDATE、DELETE：加排他行锁
  • INSERT：加插入意向锁

注意：只有通过索引条件检索数据，InnoDB 才会使用行锁，否则使用表锁！

-- 假设 name 字段没有索引
UPDATE user SET age = 20 WHERE name = '张三';  -- 锁全表！


-- 正确做法：给查询条件加索引
CREATE INDEX idx_name ON user(name);
UPDATE user SET age = 20 WHERE name = '张三';  -- 只锁匹配行

3.4 什么是死锁？如何避免？#

死锁是指两个或多个事务互相持有对方需要的锁，导致都无法继续执行。

-- 死锁示例
-- 事务A                    -- 事务B
BEGIN;                     BEGIN;
UPDATE user SET age=1
WHERE id=1;               UPDATE user SET age=2
                          WHERE id=2;
UPDATE user SET age=1
WHERE id=2;  -- 等待B释放id=2的锁
                          UPDATE user SET age=2
                          WHERE id=1;  -- 等待A释放id=1的锁
-- 死锁！

避免死锁的方法：

  1. 按固定顺序加锁：所有事务按相同顺序访问资源
  2. 减少事务持有锁的时间：事务尽量短小
  3. 降低隔离级别：如使用 READ COMMITTED
  4. 合理设计索引：减少锁范围
  5. 设置锁等待超时：innodb_lock_wait_timeout

四、InnoDB 与 MyISAM 对比#

4.1 InnoDB 和 MyISAM 有什么区别？#

特性InnoDBMyISAM
事务支持是否
外键是否
锁粒度行锁表锁
MVCC是否
崩溃恢复自动恢复需要修复
聚簇索引是否
全文索引5.6+ 支持原生支持
存储限制64TB256TB
主键要求必须有可无

4.2 如何选择存储引擎？#

选择 InnoDB：

  • 需要事务支持
  • 高并发写入场景
  • 需要外键约束
  • 数据安全性要求高

选择 MyISAM：

  • 只读或读多写少
  • 需要全文索引（MySQL 5.6 以前）
  • 对事务无要求

现代实践：MySQL 5.5 以后 InnoDB 是默认引擎，大多数场景推荐使用 InnoDB。

五、SQL 优化#

5.1 如何使用 EXPLAIN 分析 SQL？#

EXPLAIN SELECT * FROM user WHERE name = '张三';

重点关注字段：

字段说明优化关注点
type访问类型避免 ALL（全表扫描）
key实际使用的索引确认是否命中索引
rows预估扫描行数越少越好
Extra额外信息避免 Using filesort、Using temporary

type 从优到差：

system > const > eq_ref > ref > range > index > ALL
  • system/const：单行查询，主键或唯一索引
  • eq_ref：JOIN 时使用主键或唯一索引
  • ref：使用非唯一索引
  • range：范围查询（>、<、BETWEEN、IN）
  • index：索引全扫描
  • ALL：全表扫描，需优化

5.2 常见的 SQL 优化方法？#

1. 避免 SELECT *

-- 不推荐
SELECT * FROM user WHERE id = 1;


-- 推荐：只查询需要的字段
SELECT id, name, age FROM user WHERE id = 1;

2. 避免索引失效

-- 索引失效
WHERE name LIKE '%张'        -- 前置模糊匹配
WHERE YEAR(create_time) = 2023  -- 函数操作
WHERE age + 1 = 20          -- 计算
WHERE name = '张三' OR age = 20  -- OR 连接不同字段


-- 索引有效
WHERE name LIKE '张%'
WHERE create_time >= '2023-01-01' AND create_time < '2024-01-01'
WHERE age = 19
WHERE name = '张三' UNION SELECT * FROM user WHERE age = 20

3. 优化分页查询

-- 深分页效率低
SELECT * FROM user LIMIT 1000000, 10;


-- 方法1：使用子查询先查 id
SELECT * FROM user a
JOIN (SELECT id FROM user LIMIT 1000000, 10) b
ON a.id = b.id;


-- 方法2：记住上次的最大 id
SELECT * FROM user WHERE id > 1000000 LIMIT 10;

4. 优化 ORDER BY

-- Using filesort
SELECT * FROM user ORDER BY create_time;


-- 为排序字段加索引
CREATE INDEX idx_create_time ON user(create_time);

5. 批量插入优化

-- 单条插入
INSERT INTO user (name) VALUES ('张三');
INSERT INTO user (name) VALUES ('李四');


-- 批量插入
INSERT INTO user (name) VALUES ('张三'), ('李四'), ('王五');

5.3 如何分析和优化慢查询？#

开启慢查询日志：

-- 查看配置
SHOW VARIABLES LIKE '%slow_query%';


-- 开启慢查询日志
SET GLOBAL slow_query_log = 'ON';
SET GLOBAL long_query_time = 1;  -- 超过 1 秒记录

分析慢查询：

  1. 使用 EXPLAIN 查看执行计划
  2. 检查是否命中索引
  3. 检查是否有全表扫描
  4. 检查是否有临时表或文件排序
  5. 考虑是否需要建立合适的索引

六、分库分表与主从复制#

6.1 什么是分库分表？什么时候需要？#

分库分表是将数据拆分到多个数据库或表中，解决单库单表数据量过大的问题。

拆分策略：

策略说明适用场景
垂直分库按业务拆分，不同业务放不同库业务耦合度低
垂直分表按字段拆分，常用字段和不常用字段分表宽表优化
水平分库同一表数据按规则拆分到不同库数据量大
水平分表同一表数据按规则拆分到不同表单表数据量大

分表键（Sharding Key）选择：

  • 数据分布均匀
  • 常用查询条件
  • 避免跨分片查询
-- 按用户 ID 取模分表
-- user_0, user_1, user_2, ... user_15
SELECT * FROM user_{user_id % 16} WHERE user_id = 100;

6.2 主从复制的原理是什么？#

┌─────────────────────────────────────────────────────────────┐
│                     主从复制流程                            │
├─────────────────────────────────────────────────────────────┤
│                                                             │
│   Master                                                    │
│   ┌─────┐    ①写入 Binlog    ┌────────┐                    │
│   │ SQL │ ─────────────────→ │ Binlog │                    │
│   └─────┘                    └────────┘                    │
│                                  │                          │
│                                  │ ②Dump Thread推送         │
│                                  ↓                          │
│   Slave                          │                          │
│   ┌──────────────────────────────┼─────────────────────┐   │
│   │                         ③IO Thread                  │   │
│   │                    ┌────────────────┐               │   │
│   │                    │ Relay Log       │ ←─────────────┘   │
│   │                    └────────────────┘                   │
│   │                          │ ④SQL Thread                  │
│   │                          ↓                               │
│   │                    ┌────────────────┐                   │
│   │                    │ Slave 数据库    │                   │
│   │                    └────────────────┘                   │
│   └─────────────────────────────────────────────────────────┘   │
│                                                             │
└─────────────────────────────────────────────────────────────┘

核心线程：

  • Binlog Dump Thread（Master）：将 Binlog 发送给 Slave
  • I/O Thread（Slave）：将 Binlog 写入 Relay Log
  • SQL Thread（Slave）：读取 Relay Log 执行 SQL

复制模式：

模式说明
异步复制Master 不等待 Slave 确认，性能高但可能丢数据
半同步复制Master 等待至少一个 Slave 确认，平衡性能和安全
全同步复制Master 等待所有 Slave 确认，安全但性能低

6.3 主从复制有什么问题？如何解决？#

1. 主从延迟

产生原因：

  • Slave 单线程回放（MySQL 5.6 之前）
  • 大事务执行时间长
  • 网络延迟

解决方案：

  • 开启并行复制（MySQL 5.6+ slave_parallel_workers）
  • 拆分大事务
  • 关键业务读主库

2. 数据一致性

解决方案：

  • 使用半同步复制
  • 应用层实现最终一致性
  • 使用分布式事务框架（如 Seata）

6.4 读写分离如何实现？#

中间件方案：

  • ProxySQL：高性能 MySQL 代理
  • MyCat：数据库中间件，支持分库分表
  • ShardingSphere：Apache 开源生态，支持读写分离和分片

应用层方案：

// 伪代码示例
if (isWriteOperation(sql)) {
    connection = masterDataSource.getConnection();
} else {
    connection = slaveDataSource.getConnection();
}

注意：考虑主从延迟，写后读场景应路由到主库。

七、MySQL 日志系统#

7.1 MySQL 有哪些日志？#

日志类型作用
Redo LogInnoDB 引擎日志，保证持久性（崩溃恢复）
Undo Log回滚日志，保证原子性和 MVCC
BinlogServer 层日志，主从复制和数据恢复
Relay Log从库中继日志，存储从主库接收的 Binlog
Error Log错误日志，记录启动、运行、停止时的错误
Slow Query Log慢查询日志，记录执行时间长的 SQL
General Log通用日志，记录所有 SQL

7.2 Redo Log 和 Binlog 有什么区别？#

特性Redo LogBinlog
所属层InnoDB 引擎层MySQL Server 层
格式物理日志（数据页修改）逻辑日志（SQL 语句或行变更）
写入方式循环写，空间固定追加写，文件可无限增长
用途崩溃恢复主从复制、数据备份恢复

7.3 两阶段提交是什么？#

保证 Redo Log 和 Binlog 一致性的机制：

┌─────────────────────────────────────────────────────────┐
│                     两阶段提交流程                        │
├─────────────────────────────────────────────────────────┤
│                                                         │
│  1. 写 Redo Log（prepare 阶段）                         │
│     ┌─────────┐                                         │
│     │ Redo   │ ← 状态：prepare                         │
│     │ Log    │                                         │
│     └─────────┘                                         │
│           ↓                                             │
│  2. 写 Binlog                                           │
│     ┌─────────┐                                         │
│     │ Binlog │                                         │
│     └─────────┘                                         │
│           ↓                                             │
│  3. 写 Redo Log（commit 阶段）                          │
│     ┌─────────┐                                         │
│     │ Redo   │ ← 状态：commit                          │
│     │ Log    │                                         │
│     └─────────┘                                         │
│                                                         │
│  崩溃恢复判断：                                          │
│  • Redo Log 是 prepare + Binlog 存在 → 提交事务         │
│  • Redo Log 是 prepare + Binlog 不存在 → 回滚事务       │
│                                                         │
└─────────────────────────────────────────────────────────┘

八、总结#

MySQL 面试核心知识点：

  1. 索引：B+树结构、聚簇索引与非聚簇索引、最左前缀原则、覆盖索引
  2. 事务：ACID 特性、隔离级别、MVCC 原理、幻读解决方案
  3. 锁：行锁、表锁、间隙锁、死锁避免
  4. 存储引擎：InnoDB 与 MyISAM 对比与选择
  5. 优化：EXPLAIN 分析、索引优化、慢查询处理
  6. 高可用：分库分表、主从复制、读写分离
  7. 日志：Redo Log、Binlog、两阶段提交

掌握这些知识点，能够应对大多数 MySQL 面试场景。

支持与分享

如果这篇文章对你有帮助，欢迎支持作者或分享给更多人

赞助
MySQL 面试题
https://blog.souloss.com/posts/interview/mysql/
作者
Souloss
发布于
2024-02-11
许可协议
CC BY-NC-SA 4.0

部分信息可能已经过时

Python 内存管理与性能优化
为什么 React 使用虚拟 DOM
相关文章 智能推荐
1
中间件面试题
面试 面试中常见的中间件题目——MySQL 索引原理、Redis 数据结构与持久化、Kafka 消息队列等知识点整理。
2
面试题库
面试 系统整理的面试题库，涵盖计算机基础、编程语言（Go/Python）、数据库（MySQL/Redis）、云原生（Kubernetes/容器）、系统设计与云安全等核心知识点。
3
Redis 面试题
面试 面试中常见的 Redis 题目——数据结构、持久化、集群方案、缓存穿透/击穿/雪崩、分布式锁等知识点整理。
4
系统设计面试题
面试 面试中常见的系统设计题目——短 URL 设计、Feed 流设计、延迟任务队列、秒杀系统等高频题型的分析与解答。
5
计算机基础面试题
面试 面试中常见的计算机基础题目——网络七层模型、进程与线程、内存管理、系统调用等核心知识点整理。
随机文章 随机推荐
1
Go 1.23 变化深度解析：iter 包与 Timer 改进
golang 2022-11-23
2
云事件响应与取证：当攻击已经发生
云安全攻防技术 2025-04-01
3
技术内幕揭秘
原理 2023-08-31
4
TCP流控与拥塞控制：从滑动窗口到BBR
互联网运作 2022-06-29
5
代码重构：模块化架构设计
操作系统 2021-05-18
© 2026 Souloss. All Rights Reserved. / RSS / Atom / Sitemap
Powered by Astro & souloss  Version 9.0