MySQL 八股核心知识点总结

一、MySQL 整体架构

MySQL 采用 客户端 - 服务器（C/S）架构，服务器端分为 连接层、服务层、存储引擎层、物理文件层 四层，各层职责清晰，解耦性强。

层级	核心组件 / 功能	作用说明
连接层	连接器、身份认证、SSL 加密	1. 处理客户端连接（TCP 握手）；2. 验证用户名 / 密码 / 权限（如是否有某库的 SELECT 权限）；3. 分配线程（每连接一个线程，线程池可复用线程）
服务层	SQL 解析器、优化器、执行器、查询缓存（8.0 已移除）、内置函数、存储过程 / 触发器	1. 解析 SQL（词法分析→语法分析，生成语法树）；2. 优化 SQL（选择最优索引、调整 join 顺序）；3. 调用存储引擎接口执行 SQL；4. 处理内置逻辑（如日期函数、聚合函数）
存储引擎层	InnoDB、MyISAM、Memory 等	负责数据的存储和读取，是 MySQL 插件化设计的核心，不同引擎支持的特性不同（如事务、锁）
物理文件层	数据文件、日志文件、配置文件	1. 数据文件（如 InnoDB 的 .ibd 文件存储数据和索引）；2. 日志文件（binlog、redo log 等）；3. 配置文件（my.cnf/my.ini）

二、核心存储引擎对比（InnoDB vs MyISAM）

存储引擎是 MySQL 区别于其他数据库的关键特性，InnoDB 是目前默认且最常用的引擎，MyISAM 已逐渐被淘汰，两者核心差异如下：

特性	InnoDB（5.5+ 默认）	MyISAM
事务支持	支持 ACID 事务（依赖 redo log、undo log）	不支持事务
锁机制	行级锁（默认）+ 表级锁（特殊场景如 ALTER TABLE）	仅支持表级锁
索引结构	聚簇索引（主键索引与数据存储在一起）	非聚簇索引（索引与数据分开存储，.MYI 存索引，.MYD 存数据）
外键支持	支持外键约束	不支持外键
崩溃恢复	支持（依赖 redo log 实现 crash-safe）	不支持（崩溃后可能丢失数据，需手动修复）
适用场景	事务场景（如订单、支付）、高并发写操作	只读场景（如日志统计、静态数据查询）
表空间	支持独立表空间（每表一个 .ibd 文件）和系统表空间	仅支持独立文件（.MYD 数据文件 + .MYI 索引文件）

三、索引核心知识点、

索引是提升 MySQL 查询效率的关键，本质是 “帮助 MySQL 快速定位数据的数据结构”，核心考点围绕 索引类型、结构、失效场景 展开。

1. 索引类型（按功能分类）

索引类型	核心作用	适用场景
主键索引（PRIMARY KEY）	唯一标识数据行，非空且唯一，InnoDB 中是聚簇索引	表必须有且仅有一个主键，用于唯一定位行（如用户表的 user_id）
唯一索引（UNIQUE）	确保索引列值唯一（允许 NULL，但仅一个 NULL）	需唯一约束的字段（如用户表的 phone、email）
普通索引（INDEX）	无约束，仅加速查询	频繁作为 WHERE 条件的字段（如订单表的 order_time、商品表的 category_id）
联合索引（复合索引）	多字段组合的索引（如 (a,b,c)），遵循 “最左前缀原则”	WHERE 条件包含多字段查询（如 WHERE a=1 AND b=2）
全文索引（FULLTEXT）	用于文本内容的模糊查询（如文章标题、内容的关键词搜索）	长文本字段（如 article 表的 content 字段），仅 InnoDB/MyISAM 支持

2. 索引结构（按底层实现分类）

MySQL 索引底层主要用 B+ 树，部分场景用哈希索引，两者差异如下：

结构类型	核心特点	适用场景	缺点
B+ 树索引	1. 平衡树，叶子节点存储数据（InnoDB 聚簇索引）或主键（非聚簇索引）；2. 叶子节点按顺序链表连接，支持范围查询；3. 层高低（百万数据层高通常 3-4 层）	绝大多数场景（等值查询、范围查询）	不适合高频更新的场景（维护树平衡耗时）
哈希索引	1. 基于哈希表，通过哈希值定位数据；2. 等值查询极快（O (1)）	仅等值查询（如 WHERE id=100）	不支持范围查询、排序；存在哈希冲突

关键补充：InnoDB 中，主键索引是聚簇索引（叶子节点存整行数据），非主键索引是二级索引（叶子节点存主键值，查询时需 “回表”—— 通过主键查聚簇索引获取完整数据）。

3. 索引失效场景（高频考点）

以下情况会导致索引无法使用，查询退化为全表扫描，需重点规避：

联合索引不满足最左前缀原则：如联合索引 (a,b,c)，查询条件 WHERE b=2 AND c=3 会失效（缺少 a）；
索引列参与函数 / 运算：如 WHERE SUBSTR(name,1,3)='abc'（name 是索引列）、WHERE id+1=100；
索引列使用不等于（!=、<>）、NOT IN、IS NOT NULL：会导致索引失效（除非数据量极小）；
字符串不加引号：如索引列 phone 是 VARCHAR 类型，查询 WHERE phone=13800138000（缺引号，会隐式转换为数值，索引失效）；
OR 连接非索引列：如 WHERE a=1 OR b=2（a 是索引列，b 不是，整体索引失效）；
LIKE 以 % 开头 ：如 WHERE name LIKE '%abc'（% 开头无法走索引，LIKE 'abc%' 可走索引）；
MySQL 优化器判断全表扫描更快：如表数据量极小（仅 10 行），优化器会选择全表扫描而非走索引。

四、事务与 ACID 特性

事务是数据库保证数据一致性的核心机制，MySQL 中仅 InnoDB 支持事务，需重点掌握 ACID 实现原理、隔离级别、事务日志。

1. ACID 特性及实现原理

特性	定义	InnoDB 实现原理
原子性（A）	事务中操作要么全成功，要么全回滚（不可拆分）	依赖 undo log（事务执行时记录 “反向操作”，如 INSERT 对应 DELETE，回滚时执行反向操作）
一致性（C）	事务执行前后，数据从一个一致状态变到另一个一致状态（如转账前后总金额不变）	由原子性、隔离性、持久性共同保证，配合业务逻辑（如约束校验）
隔离性（I）	多个事务并发执行时，事务之间相互隔离，不影响彼此数据	依赖锁机制（行锁、表锁）和 MVCC（多版本并发控制）
持久性（D）	事务提交后，数据永久保存在磁盘，即使数据库崩溃也不丢失	依赖 redo log（事务执行时实时记录数据修改，提交后刷盘，崩溃后通过 redo log 恢复）

2. 事务隔离级别（SQL 标准 4 级）

隔离级别决定了并发事务间的可见性，级别越高，一致性越强，并发性能越弱。MySQL 中默认隔离级别是 可重复读（RR）。

隔离级别	脏读（读未提交）	不可重复读（读已提交）	幻读（重复查询结果行数变）	实现原理
读未提交（RU）	允许	允许	允许	无锁，直接读最新数据（极少用，数据一致性差）
读已提交（RC）	禁止	允许	允许	MVCC（每次查询生成新的 Read View，仅能看到已提交事务数据）
可重复读（RR，默认）	禁止	禁止	禁止（InnoDB 特殊处理）	MVCC（事务内生成一次 Read View，重复查询看到同一版本数据；用间隙锁防幻读）
串行化（Serializable）	禁止	禁止	禁止	表级锁，事务串行执行（并发性能极差，仅用于强一致性场景）

关键概念
：

脏读：读了其他事务未提交的数据（如事务 A 转账给 B，未提交，事务 B 读了转账后金额，A 回滚后 B 读的是 “脏数据”）；
不可重复读：同一事务内，多次读同一数据，结果不同（如事务 A 读 id=1 的金额为 100，事务 B 改金额为 200 并提交，A 再读变为 200）；
幻读：同一事务内，多次执行同一查询，结果行数不同（如事务 A 查金额 > 100 的有 5 行，事务 B 插入 1 行金额 > 100 的数据并提交，A 再查变为 6 行）。

3. 事务日志（redo log vs undo log）

日志类型	核心作用	特点
redo log	保证事务持久性，记录 “数据修改的物理位置和内容”（如 “表 t1 的 id=1 行，金额从 100 改为 200”）	1. 循环写（固定大小文件，满了覆盖旧日志）；2. 先写日志后写磁盘（WAL 机制，Write-Ahead Logging）
undo log	保证事务原子性，记录 “反向操作”（如 INSERT 记录 DELETE，UPDATE 记录旧值）	1. 可回滚（事务回滚时执行反向操作）；2. 用于 MVCC（生成数据历史版本，供读已提交 / 可重复读隔离级别使用）

五、锁机制

MySQL 锁按粒度分为表级锁、行级锁、页级锁，按功能分为共享锁（S 锁）、排他锁（X 锁），核心考点是 InnoDB 的行锁和间隙锁。

1. 锁粒度对比

锁粒度	适用引擎	优点	缺点	适用场景
表级锁	MyISAM、InnoDB	加锁 / 解锁快，开销小	锁粒度粗，并发冲突多	全表操作（如 ALTER TABLE、批量更新）
行级锁	InnoDB	锁粒度细，并发冲突少	加锁 / 解锁慢，开销大	单行 / 少量行操作（如 WHERE id=1）
页级锁	部分引擎（如 BDB）	介于表级和行级之间	性能一般，MySQL 中极少用	无主流场景

2. InnoDB 行锁类型（基于索引）

InnoDB 的行锁是 基于索引的锁，无索引时会退化为表级锁，核心类型：

共享锁（S 锁）：读锁，多个事务可同时加 S 锁（共享读），但不能加 X 锁（排他写）；
加锁方式：SELECT ... FOR SHARE（MySQL 8.0+）或 SELECT ... LOCK IN SHARE MODE；
排他锁（X 锁）：写锁，一个事务加 X 锁后，其他事务不能加 S 锁或 X 锁（独占写）；
加锁方式：SELECT ... FOR UPDATE，或默认写操作（INSERT、UPDATE、DELETE）自动加 X 锁。

3. 间隙锁（Gap Lock）与临键锁（Next-Key Lock）

InnoDB 在 可重复读（RR）隔离级别 下，为防止幻读引入的特殊锁：

间隙锁：锁定 “索引间隙”（如 id 为 1、3 的行，间隙是 (1,3)），防止其他事务在间隙中插入数据；
临键锁：间隙锁 + 行锁（如锁定 (1,3]，包含间隙 (1,3) 和行 3），是 InnoDB 默认的行锁算法；

示例：表 t1 有 id 为 1、3、5 的行，执行
SELECT * FROM t1 WHERE id BETWEEN 2 AND 4 FOR UPDATE
，InnoDB 会锁定间隙 (1,3)、(3,5) 和行 3，防止插入 id=2、4 的数据，避免幻读。

六、SQL 优化核心技巧

SQL 优化是面试高频题，核心思路是 “让查询走索引、减少数据扫描量”，具体技巧如下：

1. 索引优化

优先给 WHERE 条件字段、JOIN 关联字段、ORDER BY/GROUP BY 字段 建索引；
避免建冗余索引（如建了 (a,b) 联合索引，无需再建 a 单独索引）；
用 自增主键（如 INT UNSIGNED AUTO_INCREMENT），避免用 UUID（哈希值无序，插入时导致 B+ 树频繁分裂）；
联合索引遵循 “最左前缀原则”，查询条件尽量匹配索引前缀（如 (a,b,c) 索引，优先用 WHERE a=1 或 WHERE a=1 AND b=2）。

2. 语句优化

避免全表扫描：WHERE 条件尽量用索引字段，避免 SELECT *（只查需要的字段，减少 IO 和内存占用）；
优化 JOIN 操作：
- 小表驱动大表（如 SELECT * FROM 小表 a JOIN 大表 b ON a.id = b.a_id，减少外层循环次数）；
- JOIN 关联字段加索引（避免驱动表每行都对被驱动表全表扫描）；
- 避免跨库 JOIN（网络开销大，数据一致性难保证）；
优化排序：
- 让排序字段走索引（如 ORDER BY a，a 是索引字段，避免 “文件排序”）；
- 联合索引包含排序字段（如 (a,b) 索引，WHERE a=1 ORDER BY b 可走索引排序）；
优化分页：
- 大分页用 “主键偏移” 替代 LIMIT m,n（如 LIMIT 10000,10 改为 WHERE id > 10000 LIMIT 10，走主键索引）；
避免子查询：子查询会生成临时表，效率低，优先用 JOIN 替代（如 SELECT * FROM t1 WHERE id IN (SELECT id FROM t2) 改为 t1 JOIN t2 ON t1.id = t2.id）。

3. 执行计划分析（EXPLAIN）

通过 EXPLAIN SELECT ... 查看 SQL 执行计划，重点关注以下字段：

type：索引使用类型，从好到差依次是 system > const > eq_ref > ref > range > index > ALL（目标是至少达到 range 或 ref，避免 ALL 全表扫描）；
key：实际使用的索引（若为 NULL，说明未走索引）；
rows：MySQL 预估扫描的行数（行数越少越好）；
Extra：额外信息（如 Using index 表示 “覆盖索引”，

MySQL 核心知识点总结