Java 面试必问：MySQL 索引优化、执行计划、MVCC、锁机制与主从复制全解析

mhmh123

23人浏览 · 2026-09-10 11:10:49

mhmh123 · 2026-09-10 11:10:49 发布

1. 引言

在 Java 后端开发中，MySQL 是使用最广泛的关系型数据库之一。无论是日常业务开发还是面试求职，对 MySQL 核心机制的深入理解都是衡量一个开发者水平的重要标准。本文将从索引优化、执行计划、MVCC、锁机制、主从复制五个核心维度出发，结合实战案例，为你系统梳理 MySQL 的高频知识点与面试要点。

2. 索引优化

2.1 什么是索引

索引是帮助 MySQL 高效获取数据的数据结构。它就像一本书的目录，可以让我们快速定位到目标数据，而无需扫描整张表。索引可以显著提升查询性能，但也会带来额外的存储开销和写入性能损耗。

2.2 索引的数据结构

MySQL 中最常用的索引数据结构是 B+ 树。B+ 树具有以下特点：

所有数据都存储在叶子节点，非叶子节点只存储索引键值，因此树的高度较低，查询效率稳定。
叶子节点之间通过指针相连，形成有序链表，非常适合范围查询和排序操作。
每个节点可以存储多个键值，减少了磁盘 I/O 次数。

除了 B+ 树，MySQL 还支持 Hash 索引（主要用于 Memory 引擎）和 全文索引（用于全文检索场景）。

2.3 索引的分类

索引类型	说明
主键索引	每张表只能有一个，数据按主键顺序存储，叶子节点存储整行数据
唯一索引	索引列的值必须唯一，允许有空值
普通索引	最基本的索引，没有任何限制
联合索引	多个字段组合创建的索引，遵循最左前缀原则
全文索引	用于全文检索，支持中文分词

2.4 索引优化实战

2.4.1 最左前缀原则

联合索引 (a, b, c) 实际上相当于创建了 (a)、(a, b)、(a, b, c) 三个索引。查询时必须从最左侧的字段开始匹配，否则索引将失效。

-- 可以使用索引
SELECT * FROM t WHERE a = 1 AND b = 2;
SELECT * FROM t WHERE a = 1;

-- 无法使用索引（跳过了 a）
SELECT * FROM t WHERE b = 2 AND c = 3;

2.4.2 索引失效场景

对索引列使用函数或表达式计算
使用 LIKE 以通配符开头（如 LIKE '%abc'）
索引列发生隐式类型转换
使用 OR 连接非索引列
NOT IN、!=、<> 操作符

-- 索引失效示例
SELECT * FROM t WHERE DATE(create_time) = '2024-01-01';
SELECT * FROM t WHERE name LIKE '%张';
SELECT * FROM t WHERE id + 1 = 10;

2.4.3 覆盖索引

覆盖索引是指查询的字段全部包含在索引中，无需回表查询。这是优化查询性能的重要手段。

-- 假设有联合索引 (name, age)
-- 以下查询只需扫描索引即可返回结果，无需回表
SELECT name, age FROM t WHERE name = '张三';

2.4.4 索引下推（ICP）

索引下推是 MySQL 5.6 引入的优化。它允许在索引遍历过程中，对索引中包含的字段先做判断，过滤掉不满足条件的记录，减少回表次数。

-- 联合索引 (name, age)，查询条件同时包含 name 和 age
-- 开启 ICP 后，age 条件会在索引层过滤，减少回表
SELECT * FROM t WHERE name = '张三' AND age > 20;

3. 执行计划（EXPLAIN）

3.1 什么是执行计划

执行计划是 MySQL 优化器根据 SQL 语句生成的执行方案。通过 EXPLAIN 关键字可以查看 SQL 的执行计划，帮助我们分析查询性能瓶颈。

3.2 EXPLAIN 核心字段解读

EXPLAIN SELECT * FROM user WHERE id = 1;

字段	说明
id	查询的序列号，id 越大优先级越高
select_type	查询类型（SIMPLE、PRIMARY、SUBQUERY 等）
table	查询涉及的表
type	访问类型，性能从好到差依次为：system > const > eq_ref > ref > range > index > ALL
possible_keys	可能使用的索引
key	实际使用的索引
key_len	使用的索引长度
rows	预估扫描的行数
Extra	额外信息（Using index、Using where、Using filesort 等）

3.3 type 访问类型详解

system：表中只有一行记录，是 const 的特例。
const：通过主键或唯一索引查询，最多返回一行。
eq_ref：多表连接时，被驱动表通过主键或唯一索引访问。
ref：通过非唯一索引查询，可能返回多行。
range：索引范围扫描，如 BETWEEN、>、< 等。
index：全索引扫描，遍历整个索引树。
ALL：全表扫描，性能最差，需要重点优化。

3.4 Extra 常见值

Using index：使用了覆盖索引，无需回表。
Using where：在存储引擎层过滤后，还需要在服务层过滤。
Using filesort：需要额外的排序操作，应尽量避免。
Using temporary：使用了临时表，常见于 GROUP BY、ORDER BY。
Using index condition：使用了索引下推。

3.5 执行计划优化实战

-- 优化前：全表扫描
EXPLAIN SELECT * FROM order WHERE status = 1 AND create_time > '2024-01-01';

-- 优化后：创建联合索引
ALTER TABLE order ADD INDEX idx_status_time (status, create_time);

-- 再次查看执行计划，type 变为 range，rows 大幅减少

4. MVCC（多版本并发控制）

4.1 什么是 MVCC

MVCC（Multi-Version Concurrency Control，多版本并发控制）是 MySQL InnoDB 存储引擎实现隔离级别的一种机制。它通过保存数据的历史版本，让读操作和写操作互不阻塞，从而提升数据库的并发性能。

4.2 MVCC 的核心组成

MVCC 主要依赖三个隐藏字段和 undo log 实现：

DB_TRX_ID：最近修改该行记录的事务 ID。
DB_ROLL_PTR：回滚指针，指向 undo log 中的上一个版本。
DB_ROW_ID：隐藏主键，当表没有主键时自动生成。

4.3 ReadView（读视图）

ReadView 是 MVCC 实现快照读的核心。它记录了当前活跃事务的 ID 列表，用于判断当前事务能看到哪些版本的数据。

ReadView 包含以下关键信息：

m_ids：生成 ReadView 时当前活跃的事务 ID 列表。
min_trx_id：活跃事务中最小的 ID。
max_trx_id：下一个将要分配的事务 ID。
creator_trx_id：创建 ReadView 的事务 ID。

4.4 可见性判断规则

当读取一行记录时，根据该记录的 DB_TRX_ID 与 ReadView 进行比较：

如果 DB_TRX_ID < min_trx_id，说明该版本在 ReadView 生成前已提交，可见。
如果 DB_TRX_ID >= max_trx_id，说明该版本在 ReadView 生成后创建，不可见。
如果 min_trx_id <= DB_TRX_ID < max_trx_id，需要判断 DB_TRX_ID 是否在 m_ids 中：
- 在 m_ids 中，说明事务未提交，不可见。
- 不在 m_ids 中，说明事务已提交，可见。

4.5 快照读与当前读

快照读：普通的 SELECT 语句，读取的是历史版本数据，不加锁。
当前读：SELECT ... FOR UPDATE、UPDATE、DELETE 等操作，读取最新数据并加锁。

4.6 MVCC 与隔离级别

MVCC 主要解决了 读已提交（RC） 和 可重复读（RR） 两个隔离级别下的快照读问题：

RC 级别：每次 SELECT 都会生成新的 ReadView。
RR 级别：只在第一次 SELECT 时生成 ReadView，后续复用，从而解决了不可重复读问题。

5. 锁机制

5.1 锁的分类

MySQL 的锁可以从多个维度进行分类：

分类维度	锁类型	说明
粒度	表级锁、行级锁、页级锁	InnoDB 支持行级锁和表级锁
模式	共享锁（S）、排他锁（X）	S 锁兼容 S 锁，X 锁与任何锁都不兼容
算法	记录锁、间隙锁、临键锁	用于解决幻读问题
思想	悲观锁、乐观锁	乐观锁通过版本号实现

5.2 InnoDB 行锁

InnoDB 的行锁是基于索引实现的，如果查询没有走索引，行锁会升级为表锁。

-- 共享锁（S 锁）
SELECT * FROM t WHERE id = 1 LOCK IN SHARE MODE;

-- 排他锁（X 锁）
SELECT * FROM t WHERE id = 1 FOR UPDATE;

5.3 间隙锁与临键锁

记录锁（Record Lock）：锁定单个行记录。
间隙锁（Gap Lock）：锁定一个范围，但不包含记录本身，用于防止幻读。
临键锁（Next-Key Lock）：记录锁 + 间隙锁的组合，锁定一个范围及范围内的记录。

-- 假设表中有 id 为 1、5、10 的记录
-- 以下查询会锁定 (1, 5] 和 (5, 10] 的范围
SELECT * FROM t WHERE id BETWEEN 3 AND 7 FOR UPDATE;

5.4 死锁

死锁是指两个或多个事务互相持有对方需要的锁，导致都无法继续执行。MySQL 会自动检测死锁，并回滚其中一个事务。

避免死锁的建议：

尽量以固定的顺序访问表和行。
保持事务短小，减少锁持有时间。
为表添加合理的索引，避免行锁升级为表锁。
使用 SHOW ENGINE INNODB STATUS 查看死锁信息。

5.5 乐观锁与悲观锁

悲观锁：认为并发冲突一定会发生，在操作数据前先加锁。

// 悲观锁示例
SELECT * FROM account WHERE id = 1 FOR UPDATE;
// 业务处理
UPDATE account SET balance = balance - 100 WHERE id = 1;

乐观锁：认为并发冲突很少发生，通过版本号或时间戳控制。

// 乐观锁示例
UPDATE account 
SET balance = balance - 100, version = version + 1 
WHERE id = 1 AND version = 1;

6. 主从复制

6.1 什么是主从复制

主从复制是指将一个 MySQL 数据库（主库）的数据同步到一个或多个数据库（从库）的过程。主从复制是实现读写分离、数据备份和高可用性的基础。

6.2 复制原理

MySQL 主从复制基于 binlog（二进制日志） 实现，核心流程如下：

主库将数据变更写入 binlog。
从库的 I/O 线程从主库拉取 binlog，并写入从库的中继日志（relay log）。
从库的 SQL 线程读取中继日志，并重放执行，实现数据同步。

6.3 复制模式

复制模式	说明	优点	缺点
异步复制	主库提交事务后立即返回，不等待从库确认	性能最好	主库宕机可能丢失数据
半同步复制	至少一个从库确认收到 binlog 后主库才提交	数据更安全	性能略有下降
全同步复制	所有从库确认后主库才提交	数据最安全	性能最差

6.4 主从复制配置实战

6.4.1 主库配置

# 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;

-- 查看主库状态
SHOW MASTER STATUS;

6.4.2 从库配置

# my.cnf 从库配置
[mysqld]
server-id = 2
relay-log = mysql-relay-bin

-- 配置主库连接
CHANGE MASTER TO
  MASTER_HOST = '192.168.1.100',
  MASTER_USER = 'repl',
  MASTER_PASSWORD = 'password',
  MASTER_LOG_FILE = 'mysql-bin.000001',
  MASTER_LOG_POS = 154;

-- 启动复制
START SLAVE;

-- 查看复制状态
SHOW SLAVE STATUS\G;

6.5 读写分离

主从复制最常见的应用场景是读写分离：写操作走主库，读操作走从库，从而分担主库压力。

// 使用 Spring 实现简单的读写分离
@Configuration
public class DataSourceConfig {

    @Bean
    @Primary
    public DataSource dataSource() {
        Map<Object, Object> targetDataSources = new HashMap<>();
        targetDataSources.put("master", masterDataSource());
        targetDataSources.put("slave", slaveDataSource());

        RoutingDataSource routingDataSource = new RoutingDataSource();
        routingDataSource.setDefaultTargetDataSource(masterDataSource());
        routingDataSource.setTargetDataSources(targetDataSources);
        return routingDataSource;
    }
}