当前位置：首页 > news >正文

MySQL 面试知识点详解（索引、存储引擎、事务与隔离级别、MVCC、锁机制、优化）

news 2025/4/5 21:55:49

一、索引基础概念

1 索引是什么？

定义：索引是帮助MySQL高效获取数据的有序数据结构，类似书籍的目录。
核心作用：减少磁盘I/O次数，提升查询速度（以空间换时间）。

2 索引的优缺点

优点	缺点
加速查询（WHERE、JOIN、ORDER BY）	占用磁盘空间
保证数据唯一性（唯一索引）	增删改操作变慢（维护索引）
加速表间连接	过多索引增加优化器选择成本

2、索引数据结构

1. B+树（默认结构）

特点：
- 多叉平衡树，层数少（3~4层可存千万级数据）。
- 叶子节点存储数据（InnoDB存主键值或完整数据行）。
- 叶子节点通过指针串联，支持范围查询。

B+树 vs B树：

对比项	B+树	B树
数据存储位置	仅叶子节点存数据	所有节点均可存数据
查询稳定性	稳定（查询路径长度一致）	不稳定
范围查询	高效（叶子节点链表连接）	需中序遍历

2. 哈希索引（MEMORY引擎）

特点：
- 基于哈希表，精确查询O(1)时间复杂度。
- 不支持范围查询和排序，仅适合等值查询。

3、索引类型与使用场景

1. 索引分类

类型	说明	示例
主键索引	唯一标识，不允许NULL	`PRIMARY KEY (id)`
唯一索引	列值唯一，允许NULL	`UNIQUE KEY (email)`
普通索引	无唯一性约束	`INDEX idx_name (name)`
联合索引	多列组合索引	`INDEX idx_age_name (age,name)`
全文索引	文本内容分词搜索（InnoDB支持）	`FULLTEXT (content)`

2. 聚簇索引 vs 非聚簇索引

对比项	聚簇索引（InnoDB）	非聚簇索引（MyISAM）
数据存储	索引与数据文件绑定	索引与数据文件分离
主键查询	直接定位数据行	需回表查询（二次查找）
叶子节点内容	存储完整数据行	存储数据行的物理地址

二、存储引擎

1. InnoDB vs MyISAM

特性	InnoDB	MyISAM
事务支持	✅ ACID 事务支持	❌ 不支持事务
锁机制	行级锁（默认）、表级锁、间隙锁	表级锁（读锁/写锁）
外键约束	✅ 支持	❌ 不支持
崩溃恢复	✅ Redo/Undo Log 保证数据恢复	❌ 需手动修复表
全文索引	✅（5.6+版本）	✅ 原生支持
适用场景	高并发读写、OLTP（如电商、支付）	读多写少、静态数据（如日志、报表）
文件结构	`.ibd`（数据+索引）	`.frm`（表结构）、`.MYD`（数据）、`.MYI`（索引）

核心区别：

事务与锁粒度：InnoDB 通过行级锁和 MVCC 支持高并发，MyISAM 表锁在写入时阻塞其他操作。
数据完整性：InnoDB 支持外键约束和崩溃自动恢复，MyISAM 无此功能。

2. 其他存储引擎

MEMORY：数据存储在内存中，读写极快但重启丢失，适用于临时表或缓存。
ARCHIVE：压缩存储历史数据，适合归档场景，不支持索引。

三、事务与隔离级别

1. ACID 特性

原子性（Undo Log）：事务操作要么全成功，要么全失败。
一致性：事务前后数据满足完整性约束（如主键唯一）。
隔离性（MVCC + 锁）：通过隔离级别控制并发事务的可见性。
持久性（Redo Log）：事务提交后数据持久化到磁盘。

2. 隔离级别与问题

隔离级别	脏读	不可重复读	幻读	实现方式
读未提交	✅	✅	✅	无锁
读已提交 (RC)	❌	✅	✅	MVCC + 行锁
可重复读 (RR)	❌	❌	❌（InnoDB 通过间隙锁解决）	MVCC + 间隙锁
串行化	❌	❌	❌	表级锁

四、MVCC(多版本并发控制)

MySQL中的MVCC（多版本并发控制）是一种用于提高数据库并发性能的机制，通过维护数据的多个版本来实现非锁定读，从而减少读写操作的冲突。

1. MVCC 的核心思想

MVCC允许不同事务看到数据的不同版本，通过为每个修改操作创建新版本（而非直接覆盖旧数据），实现读写操作的并发执行。读操作访问快照（历史版本），写操作创建新版本，从而避免加锁阻塞。

2. MVCC 的核心组件

（1）隐藏字段

InnoDB为每行数据添加两个隐藏字段：

DB_TRX_ID：记录最后一次修改该行的事务ID。
DB_ROLL_PTR：回滚指针，指向该行的上一个版本（Undo Log中的记录）。

（2）Undo Log

作用：存储数据的历史版本，用于事务回滚和MVCC读取旧版本。
结构：每次修改操作（INSERT/UPDATE/DELETE）均生成对应的Undo Log，形成版本链。例如：
- INSERT操作记录删除的Undo Log。
- DELETE操作记录插入的Undo Log。
- UPDATE操作记录反向更新的Undo Log。
清理机制：当无事务需要旧版本时，通过Purge线程清理过期Undo Log。

（3）Read View（一致性视图）

作用：决定事务能看到哪些数据版本，根据隔离级别生成不同策略。
结构：
- m_ids：生成Read View时活跃（未提交）的事务ID列表。
- min_trx_id：活跃事务中的最小ID。
- max_trx_id：下一个将分配的事务ID（当前最大ID+1）。
- creator_trx_id：创建该Read View的事务ID（仅当自身有修改时存在）。

3. 数据可见性判断规则

事务通过Read View检查数据行的DB_TRX_ID，判断版本可见性：

已提交且早于所有活跃事务：
trx_id < min_trx_id → 可见。
由当前事务自身修改：
trx_id == creator_trx_id → 可见。
未提交或在Read View之后开启：
trx_id >= max_trx_id → 不可见。
活跃事务中的未提交修改：
trx_id在m_ids列表中 → 不可见。
已提交但位于活跃事务范围内：
其他情况 → 可见。

事务遍历版本链，直到找到满足条件的版本或链尾（表示数据不可见）。

4. 不同隔离级别的实现

READ COMMITTED：
每次SELECT生成新的Read View，总能读取最新提交的数据。
REPEATABLE READ（默认）：
首次SELECT生成Read View，后续读操作复用该视图，保证可重复读。
SERIALIZABLE：
退化为加锁读写，不使用MVCC。

5. MVCC 工作流程示例

假设事务A（ID=100）执行查询：

生成Read View：活跃事务ID列表为[150, 160]，min_trx_id=150，max_trx_id=200。
读取数据行：
- 当前行DB_TRX_ID=90（小于150且未在活跃列表）→ 可见。
- 若行DB_TRX_ID=170（介于150-200且不在活跃列表）→ 可见。
- 若行DB_TRX_ID=150（在活跃列表中）→ 不可见，沿版本链查找更早版本。

五、锁机制

1. 锁类型

分类维度	类型	说明
操作粒度	表锁	锁定整张表（MyISAM 默认），并发度低。
	行锁	锁定单行数据（InnoDB 默认），并发度高，但可能死锁。
锁模式	共享锁（S锁）	允许其他事务读，阻止写操作（`SELECT ... LOCK IN SHARE MODE`）。
	排他锁（X锁）	阻止其他事务读写（`SELECT ... FOR UPDATE`）。
锁范围	间隙锁	锁定索引记录的间隙，防止插入（解决幻读）。
	意向锁	表级锁，用于快速判断表中是否存在行锁（IS/IX 锁）。

2. 锁优化建议

InnoDB：
- 通过索引减少锁范围，避免全表扫描。
- 控制事务大小，减少锁持有时间。
MyISAM：
- 设置 low_priority_updates=1 提升读优先级。
- 启用并发插入（concurrent_insert=1）减少写阻塞。

六、数据库优化

1. SQL 优化

索引优化：
- 避免 SELECT *，使用覆盖索引减少回表。
- 联合索引遵循最左前缀原则。
查询优化：
- 避免 LIKE '%前缀'，改用全文索引或倒排索引。
- 拆分复杂查询，减少临时表使用。

2. 架构优化

读写分离：主库处理写操作，从库处理读操作。
分库分表：按业务或数据量分片（如水平分表）。
缓存策略：使用 Redis 缓存热点数据，减少数据库压力。

3. 设计优化

字段类型：优先使用整型、ENUM 替代字符串，减少存储空间。
避免 NULL：NULL 增加查询复杂度，尽量设置默认值。

七、高频面试题

InnoDB 如何解决幻读？
- RR 隔离级别下，通过 间隙锁 锁定索引范围，阻止其他事务插入新数据。
什么是 MVCC？
- 多版本并发控制，通过事务版本链和 ReadView 实现非锁定读，减少锁冲突。
如何避免死锁？
- 按固定顺序访问资源，减少事务粒度，设置合理的超时时间。
索引失效的常见场景？
- 对索引列使用函数或运算，OR 连接非索引列，隐式类型转换。
MySQL中的日志系统
- Bin Log：用于主从复制和数据恢复（逻辑日志）。
- Redo Log：保证事务持久性（物理日志）。
- Undo Log：保证事务原子性（逻辑日志）。