MySQL

面试追问地图

主问题	必讲关键点	下一层追问
SQL 执行流程	连接、解析、优化、执行、存储引擎	执行计划如何选择、统计信息过期
COUNT 统计	count(*)/count(1)/count(列名) 区别	最小二级索引树遍历
NULL 存储	NULL 位图、不占数据区	唯一索引允许多个 NULL、对索引的影响
B+ 树索引	多路、低高度、叶子链表	为什么不用红黑树/跳表、页分裂
聚簇/二级索引	叶子存储内容、回表	覆盖索引、索引下推
联合索引	最左前缀、排序和范围	范围条件后的列、跳跃扫描
存储引擎	InnoDB vs MyISAM、数据文件、Buffer Pool	事务支持、行锁、崩溃恢复
EXPLAIN	type、key、rows、Extra	filesort、temporary、实际执行分析
JOIN 算法	NLJ、BNL、Hash Join	小表驱动大表的真实含义、join_buffer_size
事务与隔离级别	ACID、隔离级别、并发问题	脏读/不可重复读/幻读、MVCC、RR 幻读漏网场景
MVCC	undo、版本链、Read View	RC/RR 生成时机、当前读
锁	记录锁、间隙锁、临键锁	索引条件如何影响加锁范围
当前读/快照读	MVCC vs 临键锁两套防幻读	RR 下幻读的漏网场景
Buffer Pool	缓存页、young/old 分区、脏页	全表扫描为何不冲掉热数据、刷脏抖动
redo/undo/binlog	持久性、回滚、复制	WAL、两阶段提交、崩溃恢复
大表 DDL	Online DDL、MDL 排队	INSTANT、gh-ost/pt-osc 原理
索引失效与优化	索引失效6种、覆盖索引、前缀索引	隐式转换、函数操作、最左前缀违反
连接池	ConcurrentBag、FastList、字节码代理	连接数公式、maxLifetime 与 wait_timeout 的关系
主从复制	binlog、relay log、重放	延迟、读写一致性、故障切换
分库分表	拆分键、路由、扩容	分布式事务、跨库查询、全局 ID
索引种类	主键/唯一/普通/联合/全文索引	聚簇 vs 二级索引、索引创建时机
悲观锁/乐观锁	先锁后操作 vs 先操作后验证	版本号、ABA 问题、适用场景
数据库对象	视图、存储过程、触发器	为什么互联网公司不推荐
备份恢复	逻辑/物理/快照备份、PITR	备份验证、延迟从库

数据库题必须结合具体 SQL、索引和隔离级别回答；脱离查询条件谈“会不会加锁”通常没有意义。

一、MySQL 基础

MySQL InnoDB 和 MyISAM 的区别？

难度 🟡

快答

特性	InnoDB	MyISAM
事务支持	✅ ACID	❌
锁粒度	行锁（精细）	表锁（粗糙）
外键	✅ 支持	❌
crash 安全	✅ 有日志	❌
全文索引	✅ (5.6+)	✅ 原生
适用	一般应用	读多写少

MySQL 索引如何优化查询？

难度 🔴

快答

-- 查看执行计划
EXPLAIN SELECT * FROM users WHERE name = 'John';
 
-- 如果 type = ALL，说明全表扫描，需要建索引
CREATE INDEX idx_name ON users(name);
 
-- 再次执行，type 应该变为 ref

关键指标：

type：从好到坏 system → const → eq_ref → ref → range → index → ALL
key：使用了哪个索引
rows：扫描的行数（越少越好）
Using where：是否使用了条件过滤
Using filesort：是否使用文件排序（慢）

索引失效的常见原因：

-- ❌ 对索引列进行函数操作
SELECT * FROM users WHERE year(birthday) = 2000;
 
-- ❌ 索引列的类型转换
SELECT * FROM users WHERE id = '123';  -- id 是 int，但用字符串比较
 
-- ❌ 在索引列上 like 查询用前通配符
SELECT * FROM users WHERE name LIKE '%John%';
 
-- ❌ 复合索引不满足最左前缀
CREATE INDEX idx_name_age ON users(name, age);
SELECT * FROM users WHERE age = 30;  -- 没有用到 name，索引失效

如何优化慢查询？

难度 🔴

1. 开启慢查询日志，定位慢查询
2. 用 EXPLAIN 分析执行计划
3. 建立合适的索引（最左前缀）
4. 改写 SQL，减少扫描范围
5. 分库分表或使用缓存

二、SQL 基础

数据库三大范式是什么？

第一范式（1NF）：每一列都是不可分割的原子数据项。
第二范式（2NF）：在 1NF 基础上，非码属性必须完全依赖于候选码（消除非主属性对主码的部分函数依赖）。
第三范式（3NF）：在 2NF 基础上，任何非主属性不依赖于其它非主属性（消除传递依赖）。

JOIN 联表查询类型

类型	说明
INNER JOIN	返回两个表中有匹配关系的行
LEFT JOIN	返回左表所有行，右表无匹配则补 NULL
RIGHT JOIN	返回右表所有行，左表无匹配则补 NULL
FULL JOIN	返回两表所有行（MySQL 需用 UNION 实现）

MySQL 如何避免重复插入？

方式	说明
UNIQUE 约束	列上添加 UNIQUE，插入重复时报错
INSERT … ON DUPLICATE KEY UPDATE	冲突时改为更新已有记录
INSERT IGNORE	冲突时静默忽略，不报错

CHAR 和 VARCHAR 的区别

CHAR：固定长度，存储时末尾补空格，适合长度固定的数据。
VARCHAR：可变长度，按实际长度占用空间。括号内数字是字符数而非字节数。

INT(1) 和 INT(10) 有什么区别？

本质是显示宽度，不影响存储范围（INT 固定 4 字节）。唯一作用场景是配合 ZEROFILL 时补零显示。

IP 地址如何存储？

字符串：VARCHAR(15)，直观但占用空间大。
整数：INT UNSIGNED，用 INET_ATON() / INET_NTOA() 转换，占用小、比较快。

IN 和 EXISTS 的区别

IN 扫描整个子查询结果集，适合子查询结果小的场景。
EXISTS 一旦找到匹配项就立即停止，适合子查询表大的场景。

`count(*)`、`count(1)`、`count(列名)` 有什么区别？

频次 ★★★ · 难度 🟢

	`count(*)`	`count(1)`	`count(列名)`
统计内容	所有行数（包含 NULL）	所有行数（包含 NULL）	该列非 NULL 的行数
是否扫描数据	不读取具体列值，走最小索引树	同左	需判断列值是否为 NULL
性能（InnoDB）	最快（专门优化，无需解引用）	近似 `count(*)`	若列有索引且允许 NULL 则基本同 `count(1)`，但需逐个判 NULL
推荐	✅ 标准推荐	可接受	仅在需要统计非 NULL 行数时使用

原理：count(*) 不会展开为所有列，InnoDB 选择最小的二级索引树遍历（若无二级索引则走聚簇索引），只计数行数不取值。count(列名) 需要检查每行该列是否 NULL，略慢。

MySQL 中 NULL 是怎么存储的？

频次 ★★ · 难度 🟡

InnoDB 每行记录有一个 NULL 值位图，放在行头部的 record header 中。每个允许为 NULL 的列占用 1 bit，标记该列是否为 NULL。

一行最多 32 列时位图占 4 字节（32 bit = 4 byte），超过 32 列则再加 4 字节，每 32 列一组
NULL 不占用数据区实际存储空间——所以允许 NULL 的列在无值时不浪费磁盘空间，但位图本身有额外开销
不建议用 NULL 做索引：唯一索引允许多个 NULL（违反”唯一”直觉）；COUNT(列名) 跳过了 NULL；IS NULL 和 IS NOT NULL 查询有时难以利用索引

SQL 查询语句的执行顺序

FROM -> ON -> JOIN -> WHERE -> GROUP BY -> 聚合函数 -> HAVING -> SELECT -> DISTINCT -> ORDER BY -> LIMIT

三、存储引擎

MySQL 执行一条 SQL 的过程

连接器：建立连接、身份校验
查询缓存：命中则直接返回（MySQL 8.0 已删除）
解析器：词法分析、语法分析，构建语法树
执行器：预处理 -> 优化器选执行计划 -> 调用存储引擎执行

常见追问

执行计划如何选择？→ 优化器基于统计信息（行数、基数、索引区分度）估算各路径代价，选代价最小的执行计划。统计信息过旧时可能选错，用 ANALYZE TABLE 刷新。
统计信息过期会怎样？→ 优化器可能选错索引，比如明明有索引却走全表扫描。排查时对比 EXPLAIN 的 rows 和实际行数差距。

常见存储引擎对比

引擎	事务	行锁	外键	崩溃恢复	count 效率
InnoDB	ACID	行锁	支持	redo log 支持	全表扫描
MyISAM	不支持	表锁	不支持	不支持	变量记录行数
Memory	不支持	表锁	不支持	不支持	快（内存）

InnoDB 成为默认引擎的原因：事务支持、行级锁、崩溃恢复（crash-safe）。

数据文件类型

.frm：表结构定义
.ibd：表数据+索引（innodb_file_per_table=1 时独立表空间）

Buffer Pool 与改进版 LRU

是什么：InnoDB 的内存缓存池（默认 128MB，生产通常设为物理内存 60%~70%），以 16KB 页为单位缓存数据页和索引页。读写都先走 Buffer Pool：修改只改内存页并记 redo log，页变脏页，后台异步刷盘。

为什么不用朴素 LRU：全表扫描或 mysqldump 会一次性读入大量只用一次的页，把真正的热点页全部挤出去（缓存污染）。

InnoDB 的改进：LRU 链表按 5:3 分为 young 区（热数据） 和 old 区（冷数据）：

新读入的页先插到 old 区头部，而不是链表头
页在 old 区停留超过 innodb_old_blocks_time（默认 1s）后再次被访问，才晋升到 young 区
全表扫描的页短时间内密集访问后不再使用，永远进不了 young 区，很快被淘汰

脏页什么时候刷盘：redo log 写满（此时更新全停，性能抖动的常见原因）、Buffer Pool 空闲页不足、后台定时刷、正常关闭。生产上”MySQL 突然变慢一下”常见原因之一就是刷脏页且”连坐”刷邻居页。

常见追问：为什么说 redo log 写满是性能悬崖？→ checkpoint 不能推进，所有更新阻塞等刷脏，QPS 直接跌零；所以要监控 redo 落盘速度和脏页比例（innodb_max_dirty_pages_pct）。

四、索引深入

B+树 vs B树 vs 二叉树 vs Hash

结构	等值查询	范围查询	磁盘 I/O
B+树	O(logN)	优秀（叶子节点链表）	最少（最矮胖）
B树	O(logN)	一般	较多
二叉树	O(logN)	一般	最多（树太高）
Hash	O(1)	不支持	不适用

通用概念：结构本身的机制（多路为什么矮、叶子链表为什么快）见树；本节讲的是选型——同样一族结构，MySQL 为什么落在 B+ 树这个点上。

为什么不用红黑树、跳表：核心是磁盘 I/O 次数而非比较次数。红黑树/二叉树是”高瘦”结构，千万级数据要 20+ 层，每层一次磁盘寻址，树高就是 I/O 次数；B+ 树非叶子节点只存索引键不存数据，一个 16KB 页能放几百个键，三四层就能装下千万级数据，把 I/O 次数压到个位数。跳表（Redis ZSet 用它，见Redis”跳表实现原理”）是内存数据结构，靠随机层数做到近似平衡，没有”页”的概念，直接搬到磁盘无法保证节点在物理上连续存放，范围查询会退化成到处随机寻址——跳表适合内存，B+ 树适合磁盘，两者是为不同介质设计的。

常见追问

页分裂什么时候发生？→ 插入随机主键（如 UUID）时，新记录要插入的页已满，InnoDB 申请新页并挪一半记录过去，导致索引结构重组和碎片。自增主键只在末尾追加，不会触发页分裂。

B+树叶子节点查找优化

数据页内记录按主键顺序组成单向链表，通过页目录（slot 分组 + 二分查找）快速定位到槽，再遍历槽内记录。

聚簇索引 vs 二级索引

聚簇索引：叶子节点存实际数据行，一张表只能有一个。
二级索引：叶子节点存主键值，查询需回表。若查询所需列全部在二级索引中，则为覆盖索引，无需回表。

常见追问

索引下推（ICP）是什么？→ 二级索引查询时，把 WHERE 中部分条件下推到存储引擎层过滤，减少回表次数。比如联合索引 (name, age)，查询 WHERE name LIKE '张%' AND age=10，无 ICP 时每个匹配 name 的行都要回表查 age，有 ICP 则在索引遍历时就过滤 age。

什么字段适合做主键？

唯一、非空
最好自增（随机值如 UUID 会导致页分裂、碎片）
不建议用业务字段

索引下推（ICP）与 Multi-Range Read（MRR）

频次 ★★★ · 难度 🟡

索引下推（ICP, Index Condition Pushdown）：MySQL 5.6+ 优化，将 WHERE 条件中能用索引判断的部分从服务层”下推”到存储引擎层提前过滤，减少回表次数。

-- 联合索引 (zip, last_name)，查询邮编以 10 开头且姓王的人
SELECT * FROM users WHERE zip LIKE '10%' AND last_name = '王';
 
-- 没有 ICP：引擎用 zip 范围扫描找到所有 '10%' 的记录，全部回表
-- → 把完整行返回给 Server 层，Server 再过滤 last_name = '王'
-- 有 ICP：引擎扫描索引时直接用 last_name 条件过滤，只回表满足条件的行
-- → 减少回表次数

MRR（Multi-Range Read）：MySQL 5.6+ 优化，将随机回表转为顺序回表。当二级索引范围查询需要回大量行时，MRR 先收集主键值并排序，再统一回表，把随机 I/O 变成顺序 I/O。

无 MRR：二级索引扫描 → 随机回表（按主键无序访问） → 大量随机 I/O
有 MRR：二级索引扫描 → 收集主键 ID → 按主键排序 → 顺序回表 → 顺序 I/O

EXPLAIN 标识：Extra 字段出现 Using index condition 表示触发了 ICP；Using MRR 表示触发了 MRR。

ICP vs MRR 分工：ICP 减少回表次数（过滤），MRR 优化回表效率（排序），两者可以同时生效。

区分度低的字段（如性别）不建议建索引

区分度 = count(DISTINCT col) / count(*)。区分度接近 0，回表开销大，索引形同虚设。

联合索引与最左前缀原则

联合索引 (a, b, c) 先按 a 排序，a 相同再按 b 排序。

遵循最左匹配：where a=1、where a=1 and b=2 生效
不遵循：where b=2、where c=3 失效
范围查询会中断匹配：where a=1 and b>2 and c=3，c 不走索引（但可走索引下推优化）

常见追问

范围条件后的列还能走索引吗？→ 不能。联合索引 (a,b)，WHERE a>1 AND b=2 中 a 范围查询后 b 无法走索引，因为 a 相同值才排 b。若业务需要，可调整列顺序让等值条件在前。
什么是跳跃扫描？→ MySQL 8.0.13+ 的优化：WHERE b=2 虽不满足最左前缀，但优化器可隐式遍历 a 的不同值，每个值下走 b 索引。Extra 显示 Using index for skip scan，前提是 a 的区分度低。

索引失效的 6 种情况

LIKE 左模糊或全模糊
对索引列使用函数
对索引列进行表达式计算
隐式类型转换
违反最左前缀原则
OR 连接的任一列无索引

索引优化方法

前缀索引：大字符串字段取前缀，减小索引体积
覆盖索引：让索引包含查询所需所有列，避免回表
自增主键：避免页分裂

索引有哪些种类？

频次 ★★★ · 难度 🟡

是什么：MySQL 索引按不同维度可以分成多种类型：

分类维度	类型	说明
数据结构	B+树索引	默认，InnoDB/MyISAM 都支持，范围查询友好
	Hash 索引	Memory 引擎支持，只等值查询，不支持范围
	全文索引（FULLTEXT）	文本内容搜索，分词后建倒排索引
	R 树（空间索引）	地理坐标等空间数据查询
物理存储	聚簇索引	叶子存整行数据，表数据按索引顺序组织
	二级索引（辅助索引）	叶子存主键值，需回表
字段约束	主键索引	唯一 + 非空，一张表一个
	唯一索引	唯一但允许 NULL（多个 NULL 不冲突）
	普通索引	无约束，仅加速查询
	前缀索引	只索引字段前 N 个字符
字段数量	单列索引	一个字段一个索引
	联合索引（复合索引）	多字段按序组成一个索引，遵循最左前缀

常见追问：唯一索引和主键索引的区别？→ 主键索引 = 唯一索引 + 非空约束，一张表只能有一个主键，但可以有多个唯一索引；主键默认是聚簇索引（InnoDB），唯一索引是二级索引。

什么时候需要创建索引？什么时候不需要？

需要创建索引的场景：

WHERE 条件中频繁使用的字段
JOIN 连接字段（被驱动表的连接键）
ORDER BY / GROUP BY 字段
区分度高（选择性好）的字段，如用户 ID、订单号
覆盖索引场景：查询列全部可以包含在联合索引中，避免回表

不需要创建索引的场景：

表数据量很小（几千行以内，全表扫描更快）
区分度极低的字段（性别、布尔值，区分度接近 0，索引形同虚设）
频繁更新的字段（索引维护的代价超过查询收益）
WHERE 条件中不使用的字段
对字段使用函数或表达式查询的列（如 WHERE YEAR(create_time) = 2025，索引失效）

经验法则：索引不是越多越好——每个索引都要占用磁盘空间，且 INSERT/UPDATE/DELETE 时需要维护索引。建索引前先看 SQL 的 WHERE 子句和 EXPLAIN 的执行计划。

什么是全文索引？如何使用？

是什么：全文索引（FULLTEXT）用于文本内容的关键词搜索，分词后建立倒排索引，支持自然语言模式（按相关性排序）和布尔模式（+关键词 -关键词 控制）。

使用：InnoDB（5.6+）和 MyISAM 都支持。CREATE FULLTEXT INDEX idx_content ON articles(title, body)；查询用 SELECT * FROM articles WHERE MATCH(title, body) AGAINST('MySQL 索引' IN NATURAL LANGUAGE MODE)。

与 Elasticsearch 的区别：全文索引是 MySQL 内建功能，适合简单文本搜索（几百 MB 级别）；大规模、多维度、高并发的全文搜索场景（TB 级日志、商品搜索）用 Elasticsearch（见Elasticsearch），MySQL 全文索引的中文分词、相关性排序、扩展性都不如 ES。

JOIN 底层用什么算法？为什么要小表驱动大表？

是什么：EXPLAIN 的 type=ALL/index 之外，JOIN 具体怎么跑由优化器选择的连接算法决定：

算法	原理	触发条件
NLJ（Nested Loop Join，嵌套循环）	驱动表取一行，去被驱动表按索引查匹配行，循环到驱动表结束	被驱动表连接字段有索引
BNL（Block Nested Loop，块嵌套循环）	驱动表整块（`join_buffer`）一次性放入内存，被驱动表全表扫描，逐行和内存里的整块比较	被驱动表连接字段无索引（退化保护）
Hash Join（8.0.18+）	用较小表建内存哈希表（连接键做 key），较大表逐行探测哈希表	无索引可用时优化器优先选它，取代 BNL

为什么小表驱动大表（NLJ 场景）：NLJ 的总扫描代价 ≈ 驱动表行数 × 被驱动表单次索引查找代价。驱动表行数是外层循环次数，越小外层循环越少，被驱动表的索引查找（内层）几乎是常数级——所以要把结果集小的表放驱动表，与”哪个是左表/右表”无关，是”过滤后行数少的那个先来”。

为什么 BNL 要把驱动表整块塞进 join_buffer 再扫被驱动表：如果没有这块 buffer，会退化成”驱动表每一行都全表扫一次被驱动表”（M×N 次磁盘 IO）；有了 buffer，被驱动表只需要扫一次，扫描时同时和内存里缓存的整块驱动表数据比较——用内存换 IO 次数。join_buffer_size 太小时驱动表要分多块，等于被驱动表要被多扫几次。

Hash Join 为什么更快：BNL 是”一次扫描 + 逐行比较”（比较次数是 M×N 级）；Hash Join 建哈希表后是 O(1) 探测，比较次数降到 M+N 级，8.0.18 之后已取代 BNL 成为无索引连接的默认选择，EXPLAIN 里 Extra 会显示 Using join buffer (hash join)。

答题要点：先说 NLJ 是默认路径（有索引才走得通），再说”小表驱动大表”的真实含义是”过滤后行数少的表放外层”，最后主动提一句现代 MySQL（8.0.18+）无索引场景已经是 Hash Join 而非传统认知的”全表扫描套全表扫描”——这句能体现跟得上版本演进。

五、事务与并发控制

事务四大特性（ACID）及实现

特性	实现方式
原子性	undo log
一致性	持久性 + 原子性 + 隔离性
隔离性	MVCC / 锁机制
持久性	redo log

并发问题：脏读、不可重复读、幻读

脏读：读到未提交事务的数据
不可重复读：同一事务内多次读取同一数据结果不一致
幻读：同一事务内多次查询记录数量不一致

四种隔离级别

级别	脏读	不可重复读	幻读	实现
读未提交	可能	可能	可能	读最新数据
读已提交	不可能	可能	可能	每次 SELECT 前生成 Read View
可重复读(默认)	不可能	不可能	可能	事务启动时生成一个 Read View
串行化	不可能	不可能	不可能	加读写锁

MVCC 实现原理

是什么：MVCC（Multi-Version Concurrency Control）通过undo log 版本链 + Read View 实现读不阻塞写、写不阻塞读。

undo log 版本链：聚簇索引记录有两个隐藏列——trx_id（最近修改该记录的事务 ID）和 roll_pointer（指向上一个版本 undo log 的指针）。每次 UPDATE 不直接覆盖数据，而是生成新版本并串成链表：

记录版本链（从新到旧）：
[trx_id=110, name='A'] → [trx_id=105, name='B'] → [trx_id=100, name='C']  ← undo log
  ↑ 当前数据               ↑ 旧版本                  ↑ 最老版本

Read View 可见性判断：事务启动时生成 Read View，包含三个关键字段：

Read View 结构：
- m_ids:   当前活跃事务 ID 列表（未提交的事务）
- min_trx_id: m_ids 中的最小值
- max_trx_id: 已分配的最大事务 ID + 1（下一个要生成的事务 ID）

判断规则：

trx_id < min_trx_id：已提交，可见
trx_id >= max_trx_id：之后启动的事务，不可见
trx_id 在 m_ids 中：事务仍活跃，不可见（沿 roll_pointer 找上一个版本）
trx_id 不在 m_ids 中且 trx_id < max_trx_id：事务已提交，可见

RC 与 RR 的 Read View 生成时机：

RC（读已提交）：每条 SQL 执行前都生成新的 Read View，同一事务内多次查询可能看到不同结果（不可重复读的根源）
RR（可重复读）：事务中第一条 SQL 生成 Read View，之后不再变化，保证同一事务内多次查询结果一致

滥用大事务的弊端

锁定数据过多，易死锁和锁超时
undo log 占用大量存储，回滚时间长
主从延迟（从库需等事务完成才能同步）

当前读 vs 快照读：RR 到底解决幻读了吗？

	快照读	当前读
语句	普通 `SELECT`	`SELECT ... FOR UPDATE / LOCK IN SHARE MODE`、`UPDATE`、`DELETE`、`INSERT`
读什么	Read View 里的历史版本（MVCC）	最新已提交版本，并加锁
防幻读手段	Read View 天然看不到新插入行	临键锁（记录锁 + 间隙锁）锁住范围，阻止插入

-- 示例：RR 下快照读 vs 当前读的行为差异
-- 事务 A                                事务 B
BEGIN;
SELECT * FROM users WHERE age > 20;
-- 结果: [Alice(22), Bob(25)]
                                         INSERT INTO users VALUES ('Eve', 23);
                                         COMMIT;
SELECT * FROM users WHERE age > 20;
-- 结果: [Alice(22), Bob(25)]  ← 快照读，看不到新行
SELECT * FROM users WHERE age > 20 FOR UPDATE;
-- 结果: [Alice(22), Bob(25), Eve(23)]  ← 当前读，看到了

RR 级别幻读没有被完全解决，两个经典漏网场景：

快照读之间夹了当前读：SELECT（快照读，看不到新行）→ UPDATE 碰了新插入的行（当前读，trx_id 变成自己的）→ 再 SELECT 就看见了
先快照读，别的事务插入提交，本事务再当前读：UPDATE/FOR UPDATE 同一范围，影响行数比预期多

答题要点：先分清两种读，再说 MVCC 防快照读幻读、临键锁防当前读幻读，最后主动补”混用两种读仍可能幻读”——这是区分背题和理解的关键点。要彻底避免：一开始就用 SELECT ... FOR UPDATE 或上串行化。

六、锁机制

MySQL 锁分类

级别	类型	说明
全局锁	FTLWR	全库只读，用于逻辑备份
表级锁	表锁	`LOCK TABLES`
表级锁	MDL	元数据锁，CRUD 加读锁，DDL 加写锁
表级锁	意向锁	快速判断表内是否有记录被锁
行级锁	记录锁	锁住一条记录
行级锁	间隙锁	只存在于 RR 级别，解决幻读
行级锁	临键锁	记录锁 + 间隙锁

常见追问

索引条件如何影响加锁范围？→ 有索引时行锁只锁索引命中的记录行（聚簇索引或二级索引对应的主键）；无索引时无法确定锁哪条记录，会锁全表（所有记录加行锁，实际等价表锁）。UPDATE 的 WHERE 条件字段没索引，生产上就是事故。

非索引字段的范围更新会导致全表加锁

如果 update 的 where 条件字段无索引，会触发全表扫描，对所有记录加行级锁，相当于锁住全表。

悲观锁和乐观锁的区别？

频次 ★★★ · 难度 🟡

是什么：两种并发控制策略——悲观锁假设冲突会发生，先加锁再操作；乐观锁假设不会冲突，操作完再验证。

维度	悲观锁	乐观锁
思想	先锁住，再操作	先操作，提交时检查冲突
实现	`SELECT ... FOR UPDATE`、`LOCK IN SHARE MODE`	版本号/时间戳、CAS
冲突少时	锁开销大，性能差	性能好，无锁等待
冲突多时	适合，避免大量重试	回滚/重试代价大
使用场景	扣库存、转账（冲突概率高）	编辑文章、用户信息修改（冲突概率低）

MySQL 中的乐观锁实现：

-- 表加 version 字段
UPDATE products SET stock = stock - 1, version = version + 1
WHERE id = 123 AND version = 5;  -- 如果 version 不是 5，说明被别人改过，更新失败
-- 应用程序检查 affected rows，为 0 则重试或返回错误

常见追问：乐观锁的 ABA 问题？→ 值从 A 变 B 又变回 A，CAS 检测不到中间变化。解决方案：加版本号（version 每次修改都递增），只比较版本号不比较值。

行级锁和表级锁有什么区别？

维度	行级锁	表级锁
粒度	锁住一行	锁住整张表
并发度	高（不同行可并发操作）	低（任何操作互斥）
锁开销	大（需管理大量锁）	小（只需一个锁）
死锁风险	高	低
实现	InnoDB	MyISAM、Memory
适用	OLTP（高并发读写）	读多写少、批量操作

InnoDB 的行锁是怎么加到表上的：InnoDB 的行锁是基于索引实现的。只有通过索引条件检索数据时，InnoDB 才使用行级锁；如果 SQL 没有走索引，会退化为锁住所有行（虽名义上是行锁，效果等同于表锁）——这就是上一节”非索引字段导致全表加锁”的根因。

常见追问：意向锁是什么？→ 行锁和表锁共存时，加表锁前需要遍历所有行检查是否有行锁——太慢。意向锁解决这个问题：加行锁前先在表级加意向锁（IS/IX），加表锁时只需检查表级意向锁，不需遍历行。

七、日志系统

三类核心日志

日志	层级	作用	记录内容
redo log	InnoDB	持久性，崩溃恢复	物理修改
undo log	InnoDB	原子性，事务回滚，MVCC	更新前的旧值
binlog	Server	备份恢复，主从复制	所有表结构变更和数据修改

binlog 三种格式

STATEMENT：记录 SQL 语句（动态函数如 UUID/NOW 可能导致主从不一致）
ROW：记录行数据最终修改结果（5.7.7+ 默认）
MIXED：自动切换上述两种模式

redo log 为什么不用直接写 B+树？

redo log 是顺序写，B+树数据页写入是随机写。顺序写性能远高于随机写。

两阶段提交过程

prepare 阶段：redo log 状态设为 prepare，持久化到磁盘
-> 写入 binlog，持久化到磁盘
commit 阶段：redo log 状态设为 commit

以 binlog 写成功为事务提交成功标识。

Double Write Buffer

MySQL 页大小 16KB，OS 页 4KB，写页不是原子操作，中途断电会导致页损坏。Double Write Buffer 先在共享表空间写一份完整页备份（顺序写），再写到数据文件（随机写）。

八、性能调优

EXPLAIN 关键字段

-- 示例：对用户表按手机号查询
EXPLAIN SELECT name, age FROM users WHERE phone = '13800138000';
-- 输出（简化）：
-- +----+-----------+-------+------+---------+------+--------+---------------------+
-- | id | table     | type  | key  | rows    | Extra                                    |
-- +----+-----------+-------+------+---------+------+--------+---------------------+
-- |  1 | users     | ref   | idx_phone | 1 | Using index condition (索引下推)         |
-- +----+-----------+-------+------+---------+------+--------+---------------------+

字段	含义	好坏判断
type	访问类型	ALL（最差）< index < range < ref < eq_ref < const（最好）
key	实际使用的索引	NULL 表示没用到索引
rows	预估扫描行数	越小越好，与实际行数差距大说明统计信息过期
Extra	附加信息	`Using filesort`（需排序，慢）、`Using temporary`（需临时表，慢）、`Using index`（覆盖索引，好）、`Using index condition`（索引下推，好）

慢查询解决方案

EXPLAIN 分析执行计划
创建合适索引
避免索引失效
覆盖索引优化
LIMIT 深分页优化
大表拆分
引入 Redis 缓存热点数据

九、架构

主从复制过程

三步流程：

主库                                   从库
┌─────────────────┐          ┌─────────────────┐
│ 事务提交写 binlog │──I/O──→│ I/O 线程写入     │
│ binlog 是二进制    │ 拉取   │ relay log        │
│ 日志，记录所有修改  │        │                  │
└─────────────────┘          │ SQL 回放线程      │
                              │ 读取 relay log    │
                              │ 在从库上重放      │
                              └─────────────────┘

主库：事务提交时写 binlog（二进制日志，记录所有数据变更）
从库 I/O 线程：连接到主库，拉取 binlog 写入本地 relay log（中继日志）
从库 SQL 回放线程：读取 relay log 并在从库上逐条执行，保持与主库一致

binlog 三种格式：

格式	记录内容	优点	缺点
STATEMENT	原始 SQL 语句	日志量小	非确定性函数（`NOW()`、`UUID()`）在主从不一致
ROW（默认）	每行变更前后数据	精确，无歧义	日志量大（批量更新尤其明显）
MIXED	自动切换：确定性用 STATEMENT，否则用 ROW	兼顾体积和准确性	仍有边界情况

异步复制：主库提交事务不等待从库确认，存在主从延迟。

常见追问

主从延迟怎么处理？→ 写后读一致性走主库，或等从库 seconds_behind_master 追平。半同步复制（rpl_semi_sync_master_wait_point = AFTER_SYNC）保证至少一个从库收到 binlog 才返回客户端。
主库挂了如何切换？→ 选延迟最小、数据最全的从库升主，补 relay log 追赶；切换后业务重连新主。使用 Proxy（如 ProxySQL）自动路由。
读写不一致怎么办？→ 主从是最终一致，写后立刻读可能读到旧数据。业务上对一致性要求高的操作强制走主库，可容忍延迟的走从库。

分库分表

频次 ★★★★ · 难度 🟡 · 高频：阿里/字节/美团

方式	说明	解决问题
垂直分库	按业务拆分到不同库	单库连接压力
垂直分表	大表拆小表（不常用字段分离）	减少磁盘 I/O
水平分库	同表拆分到不同库/服务器	单库容量和性能
水平分表	同库内拆表	单表数据量过大

分片策略选择

策略	原理	优缺点	场景
Range 分片	按 ID 范围（0~~1kw → shard0，1kw~~2kw → shard1）	扩容简单→直接加 range 不迁移旧数据；但写热点在最新 range，且数据分布不均匀	日志表按时间拆分、用户 ID 递增且持续增长
Hash 分片	对分片键取模 `shard = id % N`	数据均匀分布，无写热点；但 N 变化后全部数据要 rehash 迁移，扩容成本高	均匀读写场景（用户表、订单表）
一致性哈希	哈希环 + 虚拟节点，调整时只迁移相邻节点数据	扩容迁移量最小（约 1/N）；但路由多一层查找，且虚拟节点数需合理配置	读写均衡且需要频繁扩缩容的分布式缓存

常见追问

分片键怎么选？→ 选查询频率最高、分布最均匀的列（如 user_id、order_id）；避免用时间戳直接做 hash 分片键（有写热点）。如果查询条件不包含分片键，所有分片都要扫描（广播查询），所以选对分片键是分库分表的第一步，也是最重要的一步。
分库后跨库查询怎么做？→ 应用层聚合（多次查询在内存合并），或用中间件（ShardingSphere、MyCAT）透明路由。IN 查询可拆分到各分片并行查询再合并。
分布式事务如何处理？→ 用 Seata AT/TCC 或本地消息表+最终一致性。严格一致性场景尽量避免跨库事务，通过数据冗余或业务拆分消除。
全局 ID 怎么生成？→ 雪花算法（时间戳+机器号+序列）、号段模式（Leaf）、Redis INCR。各方案时钟回拨处理见分布式系统。
扩容怎么做？→ Hash 分片最棘手，常见方案：双写迁移（写两个库 + 分阶段切流量）、rehash 迁移（利用 binlog+Kafka 同步 + 双倍虚拟节点逐步切换）；Range 分片只加新 range 即可。停服迁移不可取。
中间件 ShardingSphere 怎么工作？→ 核心流程：SQL 解析 → 分片路由 → SQL 改写（修正表名/库名）→ 多数据源并行执行 → 结果归并（排序/聚合/分页）。ShardingSphere-Proxy 以独立服务运行兼容任何语言，ShardingSphere-JDBC 嵌入应用性能最高但多语言不通用。

千万级大表如何加字段/加索引？

直接 ALTER TABLE 的风险：虽然 5.6+ 支持 Online DDL（建索引、加列多数操作允许并发 DML），但两个坑：

MDL 写锁排队：DDL 开始和结束时要拿 MDL 写锁，若此刻有长事务/慢查询持有 MDL 读锁，DDL 挂起，后面所有新查询跟着排队——线上事故高发点
拷贝表类操作（如改列类型）耗时长、占双倍磁盘，主从延迟飙升

Online DDL 原理（ALGORITHM=INPLACE）：不重建整表拷贝到临时表，变更期间的 DML 记录到 row log，最后短暂加锁回放 row log 收尾。8.0 的 ALGORITHM=INSTANT（加列到表尾）只改元数据，秒级完成。

换表方案（gh-ost / pt-online-schema-change）：

1. 建影子表（新结构）
2. 增量同步：pt-osc 用触发器 / gh-ost 伪装从库解析 binlog（对主库无触发器开销）
3. 后台分批拷贝存量数据（可限速、可暂停）
4. 数据追平后原子 rename 切换

答题要点：先问版本和操作类型（8.0 加列 → INSTANT 直接做）；再说 MDL 排队风险（操作前查 information_schema.innodb_trx 杀长事务、设 lock_wait_timeout）；超大表或需限速灰度 → gh-ost。灰度期间新旧结构兼容策略见Linux与工程化的”数据库灰度兼容”。

数据库连接池（HikariCP）为什么这么快？

是什么：应用与数据库之间维护一批预先建好的连接，业务借用/归还而非每次新建 TCP 连接 + 认证握手。HikariCP 是目前 Spring Boot 默认连接池，标榜”零开销”。

为什么快（相对 Druid/DBCP 等传统实现）：

优化点	传统连接池做法	HikariCP 做法
并发获取连接	`synchronized` + 阻塞队列	`ConcurrentBag`：无锁化的自定义并发容器，线程优先复用自己上次用过的连接（ThreadLocal 缓存一份引用），减少跨线程竞争
遍历连接列表	`ArrayList`/`CopyOnWriteArrayList`	`FastList`：重写 `remove()`，从数组尾部开始找（大概率命中最近添加的元素），避免正向遍历
Connection 代理	JDK 动态代理（反射调用，较慢）	字节码生成代理（编译期生成委托类），方法调用无反射开销
字段可见性	部分用 volatile 保守同步	大量用无锁 CAS 和状态位运算，缩小同步范围

// HikariCP ConcurrentBag.borrow() 核心逻辑（简化）
public T borrow(long timeout, final TimeUnit timeUnit) {
    // 1. 先从 ThreadLocal 取（自己上次用过的连接，零竞争）
    final List<Object> list = threadList.get();
    for (int i = list.size() - 1; i >= 0; i--) {
        final Object entry = list.remove(i);
        if (state == STATE_NOT_IN_USE && compareAndSetState(entry, STATE_NOT_IN_USE, STATE_IN_USE))
            return entry;  // 命中，直接返回
    }
    // 2. ThreadLocal 没命中，从共享队列取（CAS 竞争）
    while (timeout > 0) {
        final Object entry = sharedQueue.poll(timeout);
        if (entry != null && compareAndSetState(entry, STATE_NOT_IN_USE, STATE_IN_USE))
            return entry;
    }
    // 3. 都拿不到，新建连接（需要加锁）
    return createConnection();
}

核心参数体系：

参数	含义	经验值
`maximumPoolSize`	最大连接数	不是越大越好：`连接数 ≈ CPU核数 × 2 + 磁盘数`（HikariCP 官方参考公式），过大会在数据库端排队（见下）
`minimumIdle`	最小空闲连接	官方建议与 maximumPoolSize 设为相同值（固定池大小），避免连接数动态伸缩造成的抖动
`connectionTimeout`	从池里拿连接的等待超时	默认 30s，超时抛异常而非无限等待
`idleTimeout`	空闲连接回收时间	仅在 minimumIdle < maximumPoolSize 时生效
`maxLifetime`	连接最大存活时间	应略小于数据库/中间防火墙的空闲断连时间（如 MySQL `wait_timeout`），防止拿到一个已被服务端悄悄关闭的”死连接”

为什么连接数不是越大越好：数据库处理请求本质是 CPU 密集（执行计划、锁、IO 调度都要 CPU 和磁盘资源），连接数超过 CPU核数×2+磁盘数 后，新增连接只是让更多请求在数据库内部排队等资源，上下文切换开销反而上升——这与线程池”线程数不是越多越好”是同一个道理：池子大小应该匹配下游资源的实际并行能力，而不是应用侧的并发量。

常见追问

maxLifetime 为什么要略小于数据库的 wait_timeout？→ 如果连接池不主动断开，数据库端先于连接池超时悄悄关闭了这条连接，池子还以为它活着，下次借出去业务一用就报 “connection already closed”——生产上”偶发连接失效”的常见根因。连接池排障的完整定位路径（活跃/空闲连接数、连接泄漏、慢 SQL 占用连接过久）见生产排障。
连接池能不能跨多实例共享？→ 不能，连接池是进程内对象；多实例部署时总连接数 = 单实例连接数 × 实例数，扩容时必须核算数据库 max_connections 上限，这是微服务扩容时容易漏算的一步。

十、数据库对象

数据库的视图和存储过程是什么？有什么区别？

对象	是什么	作用	特点
视图（View）	虚拟表，本质是存储的 SELECT 查询	简化复杂查询、隔离底层表结构变化、权限控制	不存储数据（物化视图除外），每次查询实时执行；可更新视图需满足”一对一映射到底层行”
存储过程（Stored Procedure）	预编译的 SQL 语句集合，存在服务器端	封装业务逻辑、减少网络传输、权限控制	支持参数/变量/条件/循环，首次调用后缓存执行计划

常见追问：为什么互联网公司不推荐用存储过程？→ 业务逻辑耦合在数据库层，版本管理/测试/调试困难；数据库 CPU 是瓶颈资源，把计算放在应用层更容易横向扩展；存储过程的语法和调试工具不如主流编程语言。

MySQL 中的触发器是什么？如何使用？

是什么：触发器是与表关联的自动执行程序，在 INSERT/UPDATE/DELETE 之前或之后触发。BEFORE 触发器可修改新值，AFTER 触发器常做审计日志。

限制：触发器在事务内执行，逻辑重会拖慢 SQL；MySQL 不支持同一事件同一时机的多个触发器（5.7 之前）；触发器内的错误会回滚整个事务。生产环境建议将复杂逻辑移到应用层，触发器只做简单审计。

数据库的外键是什么？有什么作用？

是什么：外键约束确保子表引用列的值必须存在于父表的主键或唯一键中，保证引用完整性。

作用：防止”孤儿记录”（订单记录引用了不存在的用户 ID）；级联操作（ON DELETE CASCADE 删用户时自动删关联订单）。

为什么互联网公司普遍不用外键：外键约束由数据库执行，增删改都需要检查，在高并发下成为性能瓶颈；分库分表后外键不可用；逻辑删除（软删除）与外键约束冲突。替代方案：应用层保证数据一致性，定期数据校验脚本兜底。

MySQL 中的临时表是什么？如何使用？

是什么：临时表只在当前会话可见，会话结束时自动删除。CREATE TEMPORARY TABLE temp_result AS SELECT ...。适合复杂查询的中间结果暂存。与普通表同名时优先访问临时表（通过 SHOW TABLES 看不到，但 DROP TABLE 会先删临时表）。

与内存表（Memory 引擎）的区别：临时表是生命周期概念，可以使用 InnoDB 引擎（数据在磁盘）或 Memory 引擎（数据在内存）；Memory 表是引擎概念，断电丢数据，表级锁并发差。

什么是数据库的数据字典？有什么作用？

是什么：数据字典是数据库的元数据中心，存储表结构、索引、列信息、约束、权限等元数据。MySQL 8.0 之前分散在 frm 文件 + information_schema；8.0 起统一存于 InnoDB 系统表空间（mysql.ibd），保证原子性和崩溃安全。

查询方式：SHOW COLUMNS FROM table_name、SELECT * FROM information_schema.TABLES、DESC table_name。

十一、运维与备份

数据库的备份和恢复策略有哪些？

频次 ★★ · 难度 🟡

备份类型：

逻辑备份（mysqldump、mysqlpump）：导出 SQL 语句，可读但慢，大库恢复时间很长
物理备份（xtrabackup、mysqlbackup）：直接拷贝数据文件，快，InnoDB 支持热备份
快照备份：LVM/文件系统快照，秒级备份，适合云环境

恢复策略：

全量备份：定期全量 + binlog 增量恢复，恢复到任意时间点（PITR）
延迟从库：从库故意延迟回放 binlog（如延迟 1 小时），用于快速恢复误操作
备份验证：定期拉备份文件做恢复演练——不做恢复演练的备份等于没做备份

关键参数：mysqldump --single-transaction（InnoDB 一致性备份，不开锁表）、--master-data=2（记录 binlog 位置供恢复用）。

Interview Wiki

探索

面试追问地图

一、MySQL 基础

MySQL InnoDB 和 MyISAM 的区别？

MySQL 索引如何优化查询？

如何优化慢查询？

二、SQL 基础

数据库三大范式是什么？

JOIN 联表查询类型

MySQL 如何避免重复插入？

CHAR 和 VARCHAR 的区别

INT(1) 和 INT(10) 有什么区别？

IP 地址如何存储？

IN 和 EXISTS 的区别

count(*)、count(1)、count(列名) 有什么区别？

MySQL 中 NULL 是怎么存储的？

SQL 查询语句的执行顺序

三、存储引擎

MySQL 执行一条 SQL 的过程

常见存储引擎对比

数据文件类型

Buffer Pool 与改进版 LRU

四、索引深入

B+树 vs B树 vs 二叉树 vs Hash

B+树叶子节点查找优化

聚簇索引 vs 二级索引

什么字段适合做主键？

索引下推（ICP）与 Multi-Range Read（MRR）

区分度低的字段（如性别）不建议建索引

联合索引与最左前缀原则

索引失效的 6 种情况

索引优化方法

索引有哪些种类？

什么时候需要创建索引？什么时候不需要？

什么是全文索引？如何使用？

JOIN 底层用什么算法？为什么要小表驱动大表？

五、事务与并发控制

事务四大特性（ACID）及实现

并发问题：脏读、不可重复读、幻读

四种隔离级别

MVCC 实现原理

滥用大事务的弊端

当前读 vs 快照读：RR 到底解决幻读了吗？

六、锁机制

MySQL 锁分类

非索引字段的范围更新会导致全表加锁

悲观锁和乐观锁的区别？

行级锁和表级锁有什么区别？

七、日志系统

三类核心日志

binlog 三种格式

redo log 为什么不用直接写 B+树？

两阶段提交过程

Double Write Buffer

八、性能调优

EXPLAIN 关键字段

慢查询解决方案

九、架构

主从复制过程

分库分表

千万级大表如何加字段/加索引？

数据库连接池（HikariCP）为什么这么快？

十、数据库对象

数据库的视图和存储过程是什么？有什么区别？

MySQL 中的触发器是什么？如何使用？

数据库的外键是什么？有什么作用？

MySQL 中的临时表是什么？如何使用？

什么是数据库的数据字典？有什么作用？

十一、运维与备份

数据库的备份和恢复策略有哪些？

关系图谱

目录

反向链接

`count(*)`、`count(1)`、`count(列名)` 有什么区别？