MySQL面经（一）

最新推荐文章于 2025-12-18 19:34:09 发布

原创最新推荐文章于 2025-12-18 19:34:09 发布 · 882 阅读

12 ·

CC 4.0 BY-SA版权

文章标签：

#mysql #数据库

后端专栏收录该内容

9 篇文章

订阅专栏

SQL执行的流程

连接阶段
- 客户端建立TCP连接
- 连接器验证账号权限
解析阶段
- 分析器：词法/语法分析
- 预处理器：检查表/列是否存在
优化阶段
- 优化器选择最优执行计划
- 决定索引使用和JOIN顺序
执行阶段
- 执行引擎调用存储引擎接口
- InnoDB主要操作：
  - 读：检查Buffer Pool → 磁盘
  - 写：undo log → Buffer Pool → redo log
返回结果
- 通过连接返回数据给客户端

MySQL的架构

服务层
- 连接管理：线程池处理客户端连接；认证和权限验证
- SQL接口：接收SQL命令（DML/DDL等）；返回查询结果
- 解析器：词法分析 → 语法分析 → 语法树；
- 优化器：生成执行计划；索引选择（基于成本估算）
- 查询缓存（8.0已移除）
存储引擎层
- InnoDB 事务/行锁/外键主流OLTP业务
- MyISAM 表锁/全文索引读多写少/已逐渐淘汰
- Memory 内存表/临时表高速缓存
- Archive 高压缩比/只追加日志存储
物理存储层
- 表空间文件（.ibd）:存储索引和数据（InnoDB）
- 日志文件:Redo Log（重做日志）、Undo Log（回滚日志）、Binlog（归档日志）

MySQL的事务

事务是将一组数据库操作打包成一个不可分割的工作单元，要么全部执行成功，要么全部回滚到初始状态。

事务的特性

ACID

原子性：事务是最小工作单元，不可再分割；通过 UNDO LOG 实现回滚能力
一致性：事务执行前后数据库保持合法状态
隔离性：并发事务相互隔离（通过锁/MVCC实现）、4种隔离级别控制隔离程度
持久性：事务提交后修改永久有效、通过 REDO LOG 保证故障恢复

事务的隔离级别

读未提交：事务可以读取其他事务未提交的修改（不加锁）可能出现：脏读；不可重复读；幻读
读已提交：只能读取已提交的数据（使用MVCC）可能出现：不可重复读；幻读
可重复读：事务期间多次读取同一数据结果一致（MVCC+通过间隙锁(Gap Lock)）可能出现：幻读
串行化：完全串行执行事务（所有SELECT语句自动转为SELECT … FOR SHARE，临键锁（Next-Key Lock）Record Lock+Gap Lock）

脏读、不可重复读、幻读

脏读 (Dirty Read)：一个事务读取了另一个未提交事务修改过的数据
不可重复读 (Non-repeatable Read)：同一事务内，多次读取同一数据返回不同结果（被其他已提交事务修改）
幻读 (Phantom Read)：同一事务内，多次执行相同范围查询返回不同的行集合（被其他已提交事务新增/删除）

MySQL的锁

行锁

记录锁（Record Lock）：属于单个行记录上的锁。
间隙锁（Gap Lock）：锁定一个范围，不包括记录本身。
临键锁（Next-Key Lock）：Record Lock+Gap Lock，锁定一个范围，包含记录本身，主要目的是为了解决幻读问题

全局锁

共享锁（S 锁）：又称读锁，事务在读取记录的时候获取共享锁，允许多个事务同时获取（锁兼容）。
排他锁（X 锁）：又称写锁/独占锁，事务在修改记录的时候获取排他锁，不允许多个事务同时获取。如果一个记录已经被加了排他锁，那其他事务不能再对这条事务加任何类型的锁（锁不兼容）。

意向锁（表锁）

意向共享锁（Intention Shared Lock，IS 锁）：事务有意向对表中的某些记录加共享锁（S 锁），加共享锁前必须先取得该表的 IS 锁。
意向排他锁（Intention Exclusive Lock，IX 锁）：事务有意向对表中的某些记录加排他锁（X 锁），加排他锁之前必须先取得该表的 IX 锁。

MySQL的日志

redo Log

redo log 它是物理日志，记录内容是“在某个数据页上做了什么修改”，属于 InnoDB 存储引擎。让 MySQL 拥有了崩溃恢复能力。保证事务的持久性。
在事务执行过程中可以不断写入，且两阶段提交（redo log 的写入拆成了两个步骤prepare和commit）

bin Log

binlog 是逻辑日志，记录内容是语句的原始逻辑，属于MySQL Server 层。MySQL 数据库的数据备份、主备、主主、主从都离不开 binlog，需要依靠 binlog 来同步数据，保证数据一致性。
只在提交事务时才写入

undo Log

每一个事务对数据的修改都会被记录到 undo log ，当执行事务过程中出现错误或者需要执行回滚操作的话，MySQL 可以利用 undo log 将数据恢复到事务开始之前的状态。保证事务的原子性。

MVCC

MVCC 是一种并发控制机制，用于在多个并发事务同时读写数据库时保持数据的一致性和隔离性。它是通过在每个数据行上维护多个版本的数据来实现的。当一个事务要对数据库中的数据进行修改时，MVCC 会为该事务创建一个数据快照，而不是直接修改实际的数据行。

读操作（SELECT）：
当一个事务执行读操作时，它会使用快照读取。快照读取是基于事务开始时数据库中的状态创建的，因此事务不会读取其他事务尚未提交的修改。
写操作（INSERT、UPDATE、DELETE）：
当一个事务执行写操作时，它会生成一个新的数据版本，并将修改后的数据写入数据库。
事务提交和回滚：
当一个事务提交时，它所做的修改将成为数据库的最新版本，并且对其他事务可见。
当一个事务回滚时，它所做的修改将被撤销，对其他事务不可见。

实现原理

InnoDB通过三个隐藏字段维护数据版本：
- DB_TRX_ID（6字节）：记录创建/最后一次修改该记录的事务ID
- DB_ROLL_PTR（7字节）：回滚指针，指向undo log中的旧版本
- DB_ROW_ID（6字节）：隐含自增行ID（无主键时生成）
ReadView机制
- m_ids：生成ReadView时活跃的事务ID列表
- min_trx_id：最小活跃事务ID
- max_trx_id：预分配的下一个事务ID
- creator_trx_id：创建该ReadView的事务ID
  当用户在这个事务中要读取某个记录行的时候，InnoDB 会将该记录行的 DB_TRX_ID 与 Read View 中的一些变量及当前事务 ID 进行比较，判断是否满足可见性条件

不同隔离级别下的MVCC

读已提交 ReadView生成在每条SELECT语句总是读取最新已提交版本
可重复读 ReadView生成在事务的第一个SELECT 固定使用事务开始时的数据快照

INNODB

InnoDB 支持事务（Transaction），适合需要数据一致性的场景，采用行级锁（Row-Level Locking），适合高并发写入场景（如电商订单）。支持外键（Foreign Key），确保数据完整性。主键索引是聚簇索引，数据文件按主键排序存储，查询主键时效率极高。有事务日志（redo log），崩溃后能自动恢复。

MyISAM 不支持事务，如果操作中途崩溃，可能导致数据不一致。采用表级锁（Table-Level Locking），写操作会阻塞所有读操作，不适合高并发写入。不支持外键，需在应用层维护关联关系。索引和数据文件分离，主键索引和非主键索引都是非聚簇索引。崩溃后可能数据损坏，需手动修复（myisamchk 工具）。