MySQL

MySQL 是后端面试的"重头戏"：索引、事务、锁、调优四条线。本篇侧重实战问法，通用原理见"数据库原理"篇。

Q1: 什么是聚簇索引和二级索引？什么是回表？「🟢 校招/初级」

考察点：InnoDB 存储结构的理解。

参考答案：

聚簇索引：叶子节点存完整行数据；InnoDB 的主键索引就是聚簇索引，一张表只有一个。
二级索引（非主键索引）：叶子节点存主键值。
回表：通过二级索引查到主键后，再去聚簇索引查完整行，多一次树查找。
覆盖索引：查询的列都在索引里，无需回表，EXPLAIN 中 Extra 显示 Using index。

追问延伸：

没有显式主键的表，InnoDB 怎么处理？（隐藏 row_id）
为什么不建议用 UUID 做主键？（页分裂、索引体积）

Q2: 联合索引的最左前缀原则是什么？「🟡 中级」

考察点：索引设计的基本功。

参考答案：

联合索引 (a, b, c) 按 a→b→c 逐层排序；查询必须从最左列开始匹配才能用上索引。
WHERE a=1 AND b=2 完整使用；WHERE b=2 用不上；WHERE a=1 AND c=3 只用到 a。
范围查询会截断后续列：WHERE a=1 AND b>2 AND c=3 中 c 用不上索引排序（8.0 的 index skip scan 有部分优化）。
索引下推（ICP）：MySQL 5.6+ 把部分条件下推到存储引擎层过滤，减少回表。

追问延伸：

ORDER BY 怎么用索引避免 filesort？
索引列上做函数运算为什么失效？（8.0 的函数索引了解一下）

Q3: 一条 SQL 很慢，你怎么排查和优化？「🟡 中级」

考察点：实战调优思路，必问题。

参考答案：

定位：开启慢查询日志（slow_query_log），找到目标 SQL。
分析：EXPLAIN 看执行计划，重点看：
- type：至少要到 range 级别，避免 ALL（全表扫描）。
- key：实际使用的索引；rows：预估扫描行数。
- Extra：Using filesort（额外排序）、Using temporary（临时表）是坏信号。
优化手段：加索引/调整索引、改写查询（避免 SELECT *、子查询改 JOIN）、分页优化（深分页用游标）、必要时加缓存或分表。

追问延伸：

LIMIT 100000, 10 深分页怎么优化？（延迟关联 / 游标分页）
EXPLAIN 的 type 从好到坏能排个序吗？

Q4: redo log、undo log、binlog 的区别？「🟡 中级」

考察点：日志体系，事务与复制的基础。

参考答案：

redo log：InnoDB 引擎层的物理日志，记录"某页某偏移做了什么修改"，循环写，用于崩溃恢复（WAL）。
undo log：逻辑日志，记录反向操作，用于事务回滚和 MVCC 版本链。
binlog：Server 层的逻辑日志（追加写），用于主从复制和数据恢复；有三种格式（statement/row/mixed，常用 row）。
两阶段提交：redo prepare → 写 binlog → redo commit，保证两份日志一致。

追问延伸：

为什么需要两阶段提交？只写一个行不行？
误删了一张表，怎么用 binlog 恢复？

Q5: MySQL 主从复制的原理和延迟问题？「🔴 高级」

考察点：高可用架构基础。

参考答案：

流程：主库写 binlog → 从库 IO 线程拉取写入中继日志（relay log）→ SQL 线程重放。
复制模式：异步（默认，可能丢数据）、半同步（至少一个从库确认）、组复制（MGR）。
主从延迟原因：从库单线程重放跟不上主库并发写（5.7+ 支持并行复制）、大事务、从库机器性能差。
应对：业务读主库（强一致场景）、等待 GTID 一致、读写分离中间件做延迟检测。

追问延伸：

主库挂了怎么切换？怎么避免脑裂？
基于 GTID 的复制比基于位点好在哪？

Q6: 分库分表了解吗？「🔴 高级」

考察点：大数据量场景的架构能力。

参考答案：

优先顺序：先优化索引和 SQL → 读写分离 → 分表 → 分库，不要一步到位。
垂直拆分：按业务模块拆；水平拆分：按行拆（按 ID 取模或按时间范围）。
带来的问题：跨分片 JOIN、分布式事务、全局唯一 ID（雪花算法）、扩容迁移（一致性哈希/双写方案）、跨库排序分页。
常用中间件：ShardingSphere、MyCat。

追问延伸：

分片键怎么选？选错了会有什么后果？
扩容时怎么做到不停机数据迁移？

Q7: MySQL 的事务隔离级别有哪些？分别解决什么问题？「🟢 校招/初级」

考察点：事务ACID中隔离性的基础概念。

参考答案：

四个隔离级别（从低到高）：

READ UNCOMMITTED（读未提交）：能读到其他事务未提交的数据 → 脏读
READ COMMITTED（读已提交）：只能读到已提交的数据 → 解决脏读，但不可重复读
REPEATABLE READ（可重复读）：同一事务内多次读取结果一致 → 解决不可重复读，仍可能幻读（MySQL InnoDB 的 RR 通过 MVCC+Next-Key Lock 基本解决了幻读）
SERIALIZABLE（串行化）：所有事务串行执行 → 解决所有问题，但性能最差

三个问题：

脏读：读到了未提交的数据
不可重复读：同一事务内，两次读同一行数据不一样（被其他事务修改并提交了）
幻读：同一事务内，两次查询结果集行数不一样（其他事务插入/删除了行）

MySQL 默认隔离级别：REPEATABLE READ

追问延伸：

不可重复读和幻读的区别？（一个是行内容变了，一个是行数变了）
为什么 MySQL 默认是 RR 而不是 RC？（历史原因，主从复制基于 statement 格式时 RC 有问题）

Q8: MVCC 是什么？怎么实现的？「🔴 高级」

考察点：InnoDB事务隔离的核心原理，高频深度题。

参考答案：

MVCC（Multi-Version Concurrency Control，多版本并发控制）：通过数据行的多个版本，实现读写不阻塞。

实现原理：

隐藏字段：每行数据有两个隐藏列
- DB_TRX_ID：最近一次修改该行的事务 ID
- DB_ROLL_PTR：回滚指针，指向 undo log 中的历史版本
undo log 版本链：每次修改都生成一个新版本，通过回滚指针连成链表
ReadView：读视图，决定当前事务能看到哪些版本
- m_ids：生成 ReadView 时活跃的事务 ID 列表
- min_trx_id：最小活跃事务 ID
- max_trx_id：下一个要分配的事务 ID
- creator_trx_id：创建 ReadView 的事务 ID

可见性规则（从最新版本开始找）：

版本 trx_id < min_trx_id → 可见（已提交）
版本 trx_id >= max_trx_id → 不可见（还没开始）
版本 trx_id 在 m_ids 中 → 不可见（还活跃着）
版本 trx_id 不在 m_ids 中 → 可见（已提交）

RC 和 RR 的区别：

RC：每次 SELECT 都生成新的 ReadView → 能读到最新提交的数据
RR：只在第一次 SELECT 时生成 ReadView → 之后读的都是同一个快照

追问延伸：

MVCC 和 Next-Key Lock 怎么配合解决幻读？
快照读和当前读的区别？（普通 SELECT 是快照读，SELECT ... FOR UPDATE 是当前读）

Q9: MySQL 中有哪些锁？行锁、表锁、意向锁、Gap 锁？「🔴 高级」

考察点：锁机制的全面理解，InnoDB并发控制的核心。

参考答案：

按粒度分：

表级锁：LOCK TABLES、元数据锁（MDL）、意向锁 → 锁粒度大，并发低
行级锁：共享锁（S）、排他锁（X）、Gap 锁、Next-Key Lock → 锁粒度小，并发高

行锁类型（InnoDB）：

共享锁（S锁）：读锁，允许其他事务读，不允许写 → SELECT ... LOCK IN SHARE MODE
排他锁（X锁）：写锁，不允许其他事务读和写 → UPDATE/DELETE/INSERT 自动加
意向锁（IS/IX）：表级锁，表示"有事务打算给行加 S/X 锁"，用来快速判断表锁能否加（避免逐行检查）
- 意向锁之间都兼容
- IS 和表级 S 兼容，和表级 X 不兼容
- IX 和表级 S/X 都不兼容
Gap锁（间隙锁）：锁定一个范围（开区间），不包含记录本身，防止幻读
- 只在 RR 隔离级别下存在
- 两个事务可以同时持有同一个 Gap 的 Gap 锁（不冲突，因为都是禁止插入）
Next-Key Lock：Gap 锁 + 行锁（左开右闭区间），InnoDB 默认的行锁算法

追问延伸：

什么情况下会触发表锁？（全表扫描没走索引时，行锁可能升级？其实不会升级，是逐行加锁但相当于表锁）
Gap 锁是为了解决什么问题？（幻读）

Q10: EXPLAIN 执行计划各字段含义？重点看什么？「🟡 中级」

考察点：SQL调优的必备工具使用能力。

参考答案：

常用字段：

id：查询编号，id 相同执行顺序从上到下；id 不同，id 大的先执行
select_type：查询类型
- SIMPLE：简单查询（无 UNION/子查询）
- PRIMARY：主查询
- SUBQUERY：子查询
- DERIVED：派生表
- UNION / UNION RESULT
table：查询的表
type：访问类型（从好到坏）：
- system > const > eq_ref > ref > range > index > ALL
- 至少要达到 range 级别，最好是 ref 以上
possible_keys：可能用到的索引
key：实际用到的索引
key_len：索引使用的字节数（越短越好，但要结合业务）
ref：索引列的比较对象
rows：预估扫描行数（越少越好）
Extra：额外信息
- Using index：覆盖索引（不用回表）✓
- Using where：需要回表过滤
- Using filesort：需要额外排序（不是索引排序）✗
- Using temporary：用了临时表 ✗
- Using index condition：索引下推（ICP）✓

追问延伸：

Using filesort 一定慢吗？数据量小的时候很快
key_len 怎么计算？（根据字段类型和是否允许 NULL）

Q11: 索引失效的场景有哪些？「🟡 中级」

考察点：索引设计和优化的实战经验。

参考答案：

常见索引失效场景：

违反最左前缀原则（联合索引不从最左列开始）
索引列上做运算、函数、类型转换
隐式类型转换（如字符串字段用数字查询）
LIKE 以通配符开头（%xxx）
OR 连接的条件中有非索引列
NOT IN / != / <> （部分场景）
IS NULL / IS NOT NULL （数据分布影响，优化器可能选择全表扫描）
数据量太小，优化器认为全表扫描更快
索引列区分度太低（如性别字段，只有两个值）

注意：MySQL 优化器会基于成本选择，不是命中规则就一定失效

追问延伸：

OR 条件怎么优化才能用上索引？（改成 UNION 或确保两边都有索引）
怎么验证索引是否生效？（EXPLAIN 看 key 字段）

Q12: MySQL 死锁是什么？怎么排查和避免？「🔴 高级」

考察点：并发事务冲突的深入理解。

参考答案：

死锁：两个或多个事务互相等待对方释放锁，形成循环等待，都无法继续执行。

产生死锁的四个必要条件：

互斥：资源不能共享
持有并等待：持有一个锁的同时请求另一个
不可剥夺：锁不能被强制剥夺
循环等待：形成等待环

排查：

SHOW ENGINE INNODB STATUS 查看最近一次死锁日志
开启 innodb_print_all_deadlocks 打印所有死锁到错误日志
分析死锁日志中的事务、锁、等待关系

避免策略：

统一加锁顺序（所有事务按相同顺序访问资源）
减少锁持有时间（事务尽量短）
降低隔离级别（RC 级别下 Gap 锁更少，死锁概率低）
用更细粒度的锁（减少锁范围）
死锁自动回滚代价小的事务（InnoDB 自动处理）

追问延伸：

InnoDB 怎么检测死锁？（等待图，检测是否有环）
死锁和锁等待超时的区别？

Q13: Buffer Pool 是什么？有什么作用？「🟡 中级」

考察点：InnoDB内存结构的理解。

参考答案：

Buffer Pool：InnoDB 的缓冲池，缓存磁盘上的数据页（索引页和数据页），减少磁盘 IO。

默认大小：128MB，生产环境通常设置为物理内存的 50%-75%
内部结构：
- 默认 16KB 一页
- 用链表管理：Free List（空闲页）、Flush List（脏页）、LRU List（缓存页）
LRU 优化：不是简单的 LRU，而是分为 young 区和 old 区（5:3 比例）
- 新页插入到 old 区头部（midpoint 位置）
- 停留时间超过阈值才移到 young 区
- 防止全表扫描把热点数据全部挤掉（Buffer Pool 污染）
刷脏页时机：
- redo log 满了（checkpoint）
- Buffer Pool 不够用了，淘汰 LRU 尾部
- MySQL 空闲时
- MySQL 正常关闭时

追问延伸：

Buffer Pool 调优参数有哪些？（innodb_buffer_pool_size、innodb_buffer_pool_instances）
为什么要分 young 和 old 区？（解决预读失效和 Buffer Pool 污染）

Q14: MySQL 字符集 utf8 和 utf8mb4 的区别？「🟢 校招/初级」

考察点：基础知识，但实际工作中常踩坑。

参考答案：

utf8（utf8mb3）：MySQL 中的"假 utf8"，最多 3 字节，只支持 BMP（基本多文种平面）的字符，不支持 emoji 和生僻字
utf8mb4：真正的 UTF-8，最多 4 字节，支持所有 Unicode 字符（包括 emoji）
排序规则（collation）：
- utf8mb4_general_ci：不区分大小写，速度快，但排序不够准确
- utf8mb4_unicode_ci：不区分大小写，基于 Unicode 标准排序，更准确
- utf8mb4_0900_ai_ci：MySQL 8.0 默认，更准确，ai=不区分重音，ci=不区分大小写
常见坑：
- 存储 emoji 报错（Incorrect string value）
- 迁移时字符集不一致
- 字符集和排序规则不一致导致 JOIN 索引失效

追问延伸：

怎么查看数据库/表/列的字符集？
varchar(255) 中的 255 是字节还是字符？（字符）

Q15: MySQL 的存储引擎有哪些？InnoDB 和 MyISAM 的区别？「🟢 校招/初级」

考察点：存储引擎的基础知识和选型能力。

参考答案：

主要存储引擎对比：

特性	InnoDB	MyISAM	Memory
事务	支持	不支持	不支持
行锁	支持	只有表锁	只有表锁
外键	支持	不支持	不支持
聚簇索引	是	否（非聚簇）	-
崩溃恢复	支持（redo log）	不支持	不支持
全文索引	5.6+ 支持	支持	不支持
适用场景	大多数业务	只读/统计	临时表

InnoDB 成为默认引擎（MySQL 5.5+）的原因：

支持事务（ACID）
支持行锁（并发性能好）
支持崩溃恢复（redo log）
支持外键
MVCC 多版本并发控制

InnoDB 表空间结构：

表空间（Tablespace）→ 段（Segment）→ 区（Extent，1MB，64页）→ 页（Page，16KB）→ 行（Row）
系统表空间、独立表空间（file-per-table，默认）、通用表空间
页是 InnoDB 最小的 I/O 单位

追问延伸：

什么时候还用 MyISAM？（极少，如历史归档表只读场景）
页大小 16KB 可以改吗？（innodb_page_size，可设 4K/8K/16K/32K/64K）

Q16: MySQL 的 JOIN 有哪些类型？怎么优化？「🟡 中级」

考察点：多表查询的性能优化能力。

参考答案：

JOIN 算法（MySQL 8.0+）：

Nested Loop Join（NLJ）：
- 驱动表的每行去被驱动表查找匹配
- 被驱动表有索引时效率好（索引查找）
- 无索引时退化为 Block Nested Loop
Block Nested Loop（BNL）：
- 被驱动表无索引时使用
- 把驱动表数据放入 join_buffer，批量扫描被驱动表
- 减少被驱动表扫描次数
Hash Join（8.0.18+）：
- 用驱动表构建哈希表，被驱动表探测
- 替代 BNL，等值连接性能大幅提升
- 以前只有 NLJ，无索引时性能很差

JOIN 优化原则：

小表驱动大表：数据量小的表做驱动表（IN 优于 EXISTS 的原理）
被驱动表关联字段加索引：避免全表扫描
减少 JOIN 的表数量：超过 3 张表考虑拆分
用 STRAIGHT_JOIN 强制驱动表顺序（谨慎使用）
避免 SELECT *：减少内存和网络开销

JOIN 类型：

INNER JOIN：取交集
LEFT JOIN：左表全 + 右表匹配
RIGHT JOIN：右表全 + 左表匹配
FULL JOIN：并集（MySQL 用 UNION 模拟）

追问延伸：

怎么判断哪个是驱动表？（EXPLAIN 中 id 相同时先执行的表）
Hash Join 什么情况下会被禁用？（非等值连接、ORDER BY 限制）

Q17: MySQL 8.0 有哪些重要新特性？「🟡 中级」

考察点：对 MySQL 演进和新特性的了解。

参考答案：

重要新特性：

窗口函数（Window Functions）：
- ROW_NUMBER()：行号
- RANK() / DENSE_RANK()：排名
- LAG() / LEAD()：前后行引用
- NTILE()：分桶
- SUM() OVER()：累计求和
- 应用：排行榜、环比/同比、移动平均
CTE（公共表表达式）：
- WITH 语句，支持递归查询
- 简化复杂 SQL、层级查询（如组织树）
JSON 增强：
- JSON_EXTRACT、JSON_CONTAINS 等函数
- JSON 索引（函数索引）
- JSON_TABLE：JSON 转表
降序索引（Descending Index）：
- 8.0 之前 DESC 索引实际是 ASC
- 8.0 真正支持降序索引
不可见索引（Invisible Index）：
- 索引存在但对优化器不可见
- 安全测试删除索引的影响
原子 DDL：
- DDL 操作原子性（要么成功要么回滚）
- 之前 DDL 失败可能留下残留
Redo Log 重构：
- 无锁设计，性能提升
- 动态切换 redo log 文件大小
角色（Role）管理：
- 类似 Oracle 的角色
- 简化权限管理

追问延伸：

窗口函数和 GROUP BY 的区别？
递归 CTE 怎么查组织树？

Q18: MySQL 的索引有哪些类型？B+Tree 为什么适合做索引？「🟡 中级」

考察点：索引底层数据结构的理解。

参考答案：

索引类型：

B+Tree 索引（默认）：InnoDB 的主要索引结构
Hash 索引：Memory 引擎支持，InnoDB 自适应哈希索引（AHI）
Fulltext 索引：全文索引，倒排索引结构
R-Tree 索引：空间索引，用于 GIS 数据

B+Tree 为什么适合做索引：

磁盘 IO 少：树的高度低（3-4 层就能存千万级数据），每层一次 IO
范围查询友好：叶子节点用双向链表连接，范围查询只需要遍历链表
数据局部性好：节点大小等于页大小（16KB），一次 IO 读取一页
查询稳定：所有数据都在叶子节点，查询时间复杂度稳定为 O(logN)

对比 B-Tree：

B-Tree 非叶子节点也存数据 → 每页存的 key 更少 → 树更高 → IO 更多
B+Tree 非叶子节点只存 key → 每页存更多 key → 树更矮 → IO 更少

对比 Hash 索引：

Hash：O(1) 等值查询快，但不支持范围查询、排序
B+Tree：等值查询 O(logN)，但支持范围查询、排序、最左前缀

对比跳表（Redis ZSet）：

跳表：实现简单、范围查询也不错、但磁盘 IO 不友好
B+Tree：磁盘 IO 友好、更适合磁盘存储的数据库

追问延伸：

为什么不用红黑树/AVL 树做索引？（二叉树太高，IO 次数多）
InnoDB 的自适应哈希索引是什么？（热点数据自动建哈希索引）

Q19: MySQL 的慢查询分析和 pt-query-digest 怎么用？「🟡 中级」

考察点：SQL 调优工具链的使用能力。

参考答案：

慢查询分析工具链：

慢查询日志：
- 开启：slow_query_log = ON
- 阈值：long_query_time = 1（秒）
- 日志文件：slow_query_log_file
- 记录内容：SQL、执行时间、扫描行数、返回行数
mysqldumpslow（内置）：
- 基本统计：按执行次数、平均时间、总时间排序
- mysqldumpslow -s t -t 10 slow.log（按总时间取前10）
- 简单但功能有限
pt-query-digest（Percona Toolkit，推荐）：
- 更详细的统计分析
- 输出：SQL 指纹、执行次数、总/平均/最大/最小时间、扫描行数
- 按响应时间排序，自动归类相似 SQL
- pt-query-digest slow.log > report.txt
- 输出包含：
  - Overall：总览统计
  - Top 10：按指标排序的前10条 SQL
  - Profile：每条 SQL 的详细统计
  - Query 1：第一条 SQL 的详细分析
EXPLAIN ANALYZE（8.0+）：
- 不仅有执行计划，还有实际执行统计
- 每步操作的实际行数、循环次数、耗时
- 比 EXPLAIN 更准确（实际执行）
Performance Schema：
- 细粒度性能数据
- 查看锁等待、IO 等待、等待事件
- SELECT * FROM performance_schema.events_waits_summary_global_by_event_name

排查流程：

开启慢查询日志
pt-query-digest 分析找到 Top N 慢 SQL
EXPLAIN 看执行计划
针对性优化（加索引、改 SQL、改架构）
验证优化效果

追问延伸：

慢查询日志会不会影响性能？（有轻微影响，生产可以采样开启）
除了慢查询日志还有哪些发现慢 SQL 的方式？（APM 工具、监控告警）

Q20: MySQL 表结构设计有哪些最佳实践？「🟡 中级」

考察点：数据库设计的实战经验。

参考答案：

字段类型优化：

整数：能用 TINYINT 就不用 INT；无负数用 UNSIGNED
字符串：定长用 CHAR（如手机号），变长用 VARCHAR；能选整型不用字符串
时间：用 DATETIME 或 TIMESTAMP（4字节，2038年问题）；不用字符串存时间
金额：用 DECIMAL(10,2) 或分单位整数存储，不用 FLOAT/DOUBLE
布尔：用 TINYINT(1) 或 BOOLEAN
大文本：超过 768 字节考虑存对象存储或单独表

索引设计原则：

区分度高（基数/表行数 > 0.3）
查询频繁（WHERE/ORDER BY/GROUP BY 用到的列）
联合索引遵循最左前缀
覆盖索引减少回表
单表索引数不超过 5-6 个
避免冗余和重复索引

表设计原则：

适当反范式化（减少 JOIN、提升查询性能）
大字段拆分到扩展表
冷热数据分离
预留字段不如加列灵活
表名/字段名规范统一（下划线、小写、不复数）
每张表有自增主键、创建时间、更新时间
禁止外键（应用层保证）
避免 NULL（用默认值代替）

命名规范：

表名：小写+下划线，如 user_order
索引名：idx_字段名（普通索引）、uk_字段名（唯一索引）
字段名：小写+下划线，不使用数据库保留字

追问延伸：

为什么 MySQL 禁止 SELECT * 在生产环境？
反范式化到什么程度合适？（以查询性能和一致性需求为度）

Q21: MySQL 的索引下推（ICP）是什么？怎么减少回表？「🟡 中级」

考察点：索引优化的高级特性理解。

参考答案：

索引下推（Index Condition Pushdown，ICP）：MySQL 5.6+ 引入，把 WHERE 过滤条件下推到索引层执行，减少回表次数。

没有 ICP 时（5.6 之前）：

存储引擎根据联合索引找到满足最左前缀的记录
对每条记录回表（通过主键去聚簇索引取完整行）
MySQL Server 层再用其他 WHERE 条件过滤 → 大量无效回表

有 ICP 时（5.6+）：

存储引擎根据联合索引找到满足最左前缀的记录
在索引层直接用其他 WHERE 条件过滤（不回表）
只有满足所有索引层条件的记录才回表 → 大幅减少回表次数

示例：

sql

-- 联合索引 idx_name_age(name, age)
SELECT * FROM user WHERE name LIKE '张%' AND age > 20;

无 ICP：name LIKE '张%' 走索引，所有匹配的记录都回表，再用 age > 20 过滤
有 ICP：name LIKE '张%' 走索引，在索引层直接用 age > 20 过滤，只有同时满足的才回表

EXPLAIN 中看到 Using index condition 表示使用了 ICP。

触发条件：

需要联合索引（单列索引没有 ICP 的意义）
WHERE 条件中有一部分可以用索引过滤
不适用于覆盖索引（不需要回表就没有 ICP 的意义）

追问延伸：

ICP 和覆盖索引的区别？
Using index condition 和 Using where 的区别？

Q22: MySQL 的 binlog 有哪几种格式？有什么区别？「🟡 中级」

考察点：binlog 在主从复制和数据恢复中的核心参数理解。

参考答案：

三种 binlog 格式：

Statement（语句级别）：
- 记录原始 SQL 语句
- 优点：日志量小、可读性好
- 缺点：有些函数（NOW()、UUID()、RAND()）在从库执行结果不一致
- 适用：没有不确定函数的简单 SQL
Row（行级别，推荐）：
- 记录每行数据的变更（before image + after image）
- 优点：数据一致性最好、可以精确恢复
- 缺点：日志量大（一条 UPDATE 影响 1 万行，就有 1 万条变更记录）
- 适用：数据一致性要求高、CDC 数据同步
Mixed（混合模式）：
- MySQL 自动选择：普通 SQL 用 Statement，含不确定函数的用 Row
- 折中方案

对比：

格式	日志量	一致性	可读性	适用场景
Statement	小	差	好	简单SQL
Row	大	好	差	CDC、数据同步
Mixed	中	中	中	折中

MySQL 5.7+ 默认 Row 格式。

主从复制影响：

Statement 格式下，如果 SQL 含 UUID()、RAND() 等函数，主从数据可能不一致
Row 格式下，主从数据严格一致
Statement 格式下，主从复制性能好（只需执行一条 SQL）
Row 格式下，主从复制可能慢（要执行很多行变更）

追问延伸：

Canal 是基于哪种 binlog 格式做 CDC 的？（Row）
怎么查看 binlog 内容？

Q23: MySQL 自增主键为什么不连续？「🟡 中级」

考察点：自增主键机制的深入理解。

参考答案：

自增主键不连续的原因：

事务回滚：插入数据时分配了 ID，事务回滚后 ID 不会回收（已分配的 ID 不复用）
批量插入预分配：INSERT ... VALUES(...), (...), (...) 批量插入时，MySQL 预分配 ID（中间可能有空洞）
INSERT ... ON DUPLICATE KEY UPDATE：冲突时 ID 已分配，更新不会释放
REPLACE：先删除再插入，删除释放了行但 ID 不回退
自增锁释放：MySQL 5.7 默认 innodb_autoinc_lock_mode=1，批量插入时用表级自增锁，预分配 ID 可能有多余

innodb_autoinc_lock_mode 三种模式：

0（traditional）：每次 INSERT 都用表锁，串行分配，无空洞（性能差）
1（consecutive，默认 5.7）：简单 INSERT 用轻量锁，批量 INSERT 用表锁（可能有空洞）
2（interleaved，默认 8.0）：全部用轻量锁，并发分配（空洞最多但性能最好）

MySQL 8.0 默认改为 mode=2：

更高并发性能
但空洞更多
配合 binlog_format=row（8.0 默认）保证复制安全

设计建议：

自增主键只保证唯一和递增，不保证连续
业务不应该依赖自增 ID 的连续性
如果需要连续编号，用业务编号（单独维护）

追问延伸：

自增 ID 用完了怎么办？（报错，需要提前规划用 BIGINT）
自增 ID 和 UUID 怎么选？（自增 ID 性能好但单调暴露信息，UUID 安全但索引性能差）

Q24: MySQL 行锁在什么情况下会"退化"为表锁？「🟡 中级」

考察点：行锁实际行为的理解，避免锁范围扩大。

参考答案：

行锁不会真正"退化"为表锁，但以下情况行锁的范围可能很大（相当于锁表）：

没有走索引：
- 如果 WHERE 条件没有使用索引，InnoDB 不得不扫描全表
- 扫描的每一行都会加锁 → 相当于锁表
- 如：UPDATE user SET age = 20 WHERE name = '张三'（name 没有索引）
走索引但范围太大：
- UPDATE user SET age = 20 WHERE id > 0（主键范围扫描，几乎覆盖全表）
- 所有扫描到的行都会加 Next-Key Lock
LIKE 前缀匹配：
- WHERE name LIKE '%张'（前缀通配符，不走索引）
- 全表扫描加锁
隐式类型转换：
- WHERE phone = 13800138000（phone 是 varchar，传入整数）
- 索引失效 → 全表扫描加锁
函数/运算：
- WHERE YEAR(create_time) = 2026 → 函数导致索引失效
- WHERE id + 1 = 100 → 运算导致索引失效

行锁范围（RR 隔离级别）：

等值查询命中：行锁 + Gap 锁（Next-Key Lock）
等值查询未命中：Gap 锁（锁定间隙，防止插入）
范围查询：锁定范围内的所有 Gap + 行（可能很大）

排查方法：

SHOW ENGINE INNODB STATUS 查看锁信息
performance_schema.data_locks（8.0+）查看锁详情

避免锁范围扩大的建议：

确保 WHERE 条件走索引
避免大范围更新（分批更新）
避免在索引列上做运算/函数
注意隐式类型转换

追问延伸：

怎么查看当前持有锁的情况？
Next-Key Lock 是什么？（行锁 + Gap 锁，左开右闭区间）

Q25: MySQL 主从复制延迟怎么排查和优化？「🔴 高级」

考察点：主从延迟的排查和优化能力。

参考答案：

主从复制延迟的排查：

查看延迟：
- SHOW SLAVE STATUS：看 Seconds_Behind_Master
- 监控工具：Prometheus + mysqld_exporter
排查方向：
- 主库大事务：单个事务太大，从库执行慢
- 主库写 QPS 高：从库单线程回放跟不上
- 从库硬件差：CPU/IO 瓶颈
- 从库有慢查询：从库的读请求影响复制线程
- 网络延迟：主从跨机房

优化方案：

复制模式升级：
- 异步复制（默认）：主库不等从库 → 延迟大但性能好
- 半同步复制（Semi-Sync）：主库等至少一个从库收到 binlog → 减少数据丢失
- MGR（MySQL Group Replication）：基于 Paxos 的强一致方案
并行复制（从库多线程回放）：
- MySQL 5.7：基于组提交（Group Commit）的并行复制（slave_parallel_type=LOGICAL_CLOCK）
- MySQL 8.0：基于 WriteSet 的并行复制，更细粒度
- slave_parallel_workers：设置并行线程数
减少主库大事务：
- 大事务拆分成小事务
- 批量操作分批执行（每批 1000 行）
从库优化：
- 从库硬件升级
- 关闭从库的慢查询（只做备份，不做读）
- 优化从库配置（innodb_flush_log_at_trx_commit=2 降低 IO）
架构优化：
- 读写分离时读不强求最新（最终一致）
- 关键业务读主库
- 多级缓存（本地缓存 + Redis）

半同步复制 vs MGR：

方案	一致性	性能	复杂度
异步	低	高	低
半同步	中	中	中
MGR	高	中	高

追问延伸：

半同步复制超时后会怎样？（降级为异步复制）
MGR 的 Paxos 和 Raft 有什么区别？

Q26: MySQL 的 Online DDL 怎么做？加字段会锁表吗？「🟡 中级」

考察点：DDL 操作对业务影响的理解。

参考答案：

DDL 操作的锁类型：

COPY（最差）：创建新表 → 复制数据 → 切换表名 → 删除旧表，全程锁表
INPLACE（5.6+）：在原表上修改，部分操作支持并发读写
INSTANT（8.0.12+）：只修改元数据，瞬间完成，不锁表

各操作的锁情况：

操作	5.7	8.0
加列（默认在末尾）	INPLACE（允许读写）	INSTANT
加列（指定位置）	INPLACE（允许读）	INSTANT
删除列	INPLACE（允许读写）	INPLACE
修改列类型	COPY（锁表）	COPY
加索引	INPLACE（允许读写）	INPLACE
修改列名	INPLACE	INPLACE
加默认值	INPLACE	INSTANT

Online DDL 的限制：

大表加索引可能耗时很长（需要扫描全表构建索引）
变更过程中 undo log 增多
可能触发 "Waiting for table metadata lock"

生产环境大表 DDL 方案：

pt-online-schema-change（Percona Toolkit）：
- 创建影子表 → 创建触发器 → 分批复制数据 → 切换表名
- 对业务影响小
gh-ost（GitHub）：
- 类似 pt-osc 但不用触发器（读 binlog 同步）
- 更轻量
分时段执行：
- 低峰期执行
- 监控锁等待情况

最佳实践：

加列放末尾（可 INSTANT）
避免修改列类型（需要 COPY）
大表 DDL 用 pt-osc / gh-ost
先测试环境验证

追问延伸：

Online DDL 执行中能否被中断？
metadata lock 是什么？（MDL，DDL 时防止并发修改表结构）

Q27: SQL 查询语句的执行顺序是怎样的？「🟢 校招/初级」

考察点：SQL 执行原理的基础理解。

参考答案：

SQL 逻辑执行顺序（不是书写顺序）：

1. FROM          ← 确定数据源（JOIN 在这里执行）
2. ON            ← JOIN 条件
3. JOIN          ← 连接表
4. WHERE         ← 行级过滤
5. GROUP BY      ← 分组
6. HAVING        ← 组级过滤（聚合后过滤）
7. SELECT        ← 选择列（聚合函数在这里计算）
8. DISTINCT      ← 去重
9. ORDER BY      ← 排序
10. LIMIT        ← 分页

书写顺序：SELECT → FROM → JOIN → WHERE → GROUP BY → HAVING → ORDER BY → LIMIT

关键理解点：

WHERE 在 GROUP BY 之前：先过滤行再分组（不能用聚合函数）
HAVING 在 GROUP BY 之后：分组后过滤（可以用聚合函数）
SELECT 在 GROUP BY 之后：所以 SELECT 中可以用聚合函数
ORDER BY 可以用 SELECT 中的别名（因为它在 SELECT 之后执行）

sql

-- 示例：每个部门平均工资大于10000的部门
SELECT dept_id, AVG(salary) as avg_salary
FROM employee
WHERE status = 'active'       -- 先过滤在职员工
GROUP BY dept_id               -- 按部门分组
HAVING AVG(salary) > 10000    -- 再过滤平均工资
ORDER BY avg_salary DESC       -- 按别名排序
LIMIT 10;

追问延伸：

WHERE 和 HAVING 的区别？
为什么 SELECT 中不能用 WHERE 里的别名？

Q28: MySQL 一条 UPDATE 语句的完整执行过程？「🔴 高级」

考察点：SQL 执行全链路的深入理解。

参考答案：

一条 UPDATE user SET name='张三' WHERE id=1 的完整流程：

连接器：验证用户权限，建立连接
查询缓存（8.0已移除）：如果开启了缓存且命中则直接返回
分析器：词法分析（识别 UPDATE、表名、字段名）+ 语法分析（SQL 语法是否正确）
优化器：选择执行计划（走索引还是全表扫描、选择 JOIN 顺序）
执行器：调用存储引擎接口执行

InnoDB 存储引擎层面的执行：

查找数据：通过 id=1 的聚簇索引找到数据页（先查 Buffer Pool，未命中则从磁盘加载）
记录旧值：把修改前的数据写入 undo log（用于回滚和 MVCC）
更新 Buffer Pool：修改 Buffer Pool 中的数据页（变成脏页）
写 redo log：把修改记录写入 redo log（prepare 状态）
写 binlog：把修改记录写入 binlog
提交事务：redo log 改为 commit 状态（两阶段提交）

两阶段提交（保证 redo log 和 binlog 一致）：

阶段1：redo log 写入，标记为 prepare 状态
阶段2：binlog 写入后，redo log 标记为 commit 状态
如果在阶段2之前崩溃：恢复时检查 binlog 是否完整，完整则提交，不完整则回滚

刷盘时机：

redo log：先写内存（log buffer），再刷盘（innodb_flush_log_at_trx_commit 控制）
binlog：先写内存（binlog cache），再刷盘（sync_binlog 控制）
脏页：异步刷盘（由 Buffer Pool 的 LRU 和 checkpoint 机制控制）

追问延伸：

为什么需要两阶段提交？
redo log 和 binlog 的区别？（前者物理日志，后者逻辑日志）

Q29: 为什么不用 redo log 直接写 B+ 树？为什么要先写 redo log？「🔴 高级」

考察点：WAL 机制的深入理解。

参考答案：

WAL（Write-Ahead Logging）：先写日志，后写数据。

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

随机写 vs 顺序写：
- 直接写 B+ 树：修改的数据页分散在磁盘各处 → 随机写 → 极慢
- 写 redo log：追加写（顺序写）→ 极快
写放大：
- 改一行数据（几十字节），但要刷整个 16KB 的数据页
- redo log 只记录修改的部分（物理日志）
性能：
- 顺序写 > 随机写（磁盘性能差距 100 倍以上）
- redo log 先写内存（log buffer），再批量顺序刷盘
- 数据页异步刷盘（Buffer Pool 管理脏页）

redo log 怎么保证持久性：

innodb_flush_log_at_trx_commit=1（默认）：每次事务提交都把 redo log 刷盘 → 最安全
=0：每秒刷盘一次 → 可能丢1秒数据
=2：每次提交写到 OS Buffer，每秒刷盘 → OS 崩溃丢数据

为什么需要 binlog（有了 redo log 还要 binlog）：

redo log 是 InnoDB 引擎层的物理日志（记录"哪一页哪个偏移量改了什么"）
binlog 是 Server 层的逻辑日志（记录"执行了什么 SQL"）
binlog 用于：主从复制、数据恢复（PITR）、CDC
redo log 循环写（空间有限），binlog 追加写（永久保存）

追问延伸：

double write buffer 是什么？（防止页撕裂，16KB页写一半断电）
redo log 满了会怎样？（强制刷脏页，暂停写入）

Q30: MySQL 的索引为什么用 B+ 树而不是 B 树或跳表？「🟡 中级」

考察点：索引数据结构选型的深入理解。

参考答案：

B+ 树 vs B 树：

B 树：非叶子节点也存数据 → 每页存的 key 更少 → 树更高 → IO 更多
B+ 树：非叶子节点只存 key → 每页存更多 key → 树更矮 → IO 更少（3-4层存千万级数据）
B+ 树叶子节点用双向链表连接 → 范围查询高效
B 树范围查询需要中序遍历，效率低

B+ 树 vs 跳表：

B+ 树：每页 16KB，一个节点存很多 key → 树很矮（3-4层）→ 磁盘 IO 次数少
跳表：每个节点存一个 key → 层数高 → 磁盘 IO 多（但内存中差异不大）
B+ 树更适合磁盘存储（按页读写，局部性好）
跳表更适合内存存储（实现简单，Redis ZSet 用跳表）

B+ 树 vs 红黑树/AVL 树：

二叉树：每个节点最多两个子节点 → 树太高 → IO 太多
B+ 树是多叉树 → 树矮 → IO 少

B+ 树叶子节点链表方向：双向链表（前驱+后继指针），方便双向范围扫描。

总结：

磁盘存储 → B+ 树（页大小适配、范围查询友好、树矮）
内存存储 → 跳表/红黑树/哈希表（不需要考虑磁盘 IO）
B+ 树是为磁盘存储量身定做的数据结构

追问延伸：

B+ 树的页大小为什么是 16KB？
Redis 的 ZSet 为什么用跳表不用 B+ 树？

Q31: 数据库三大范式是什么？什么时候需要反范式？「🟢 校招/初级」

考察点：数据库设计理论基础。

参考答案：

三大范式：

第一范式（1NF）：每个字段不可再分（原子性）
- 如：不能有"地址"字段存"省-市-区"，应拆分为 province/city/district
第二范式（2NF）：非主键字段完全依赖主键（不能部分依赖）
- 如：订单表（订单ID, 商品ID, 商品名称）中商品名称只依赖商品ID（联合主键的一部分），应拆分为订单表+商品表
第三范式（3NF）：非主键字段直接依赖主键（不能传递依赖）
- 如：订单表（订单ID, 用户ID, 用户名）中用户名通过用户ID传递依赖订单ID，应拆分为订单表+用户表

反范式（适度冗余）：

为了查询性能，允许适当的字段冗余
如：订单表直接存"商品名称"（冗余），避免每次 JOIN 商品表
场景：读多写少、JOIN 性能差、报表统计

何时反范式：

读写比高（读远多于写）→ 冗余减少 JOIN
实时性要求不高 → 可以接受短暂不一致
历史数据 → 冗余避免数据变更影响
分库分表后 → 跨库 JOIN 困难，需要冗余

追问延伸：

范式和反范式怎么平衡？
BCNF 是什么？

Q32: CHAR 和 VARCHAR 有什么区别？int(1) 和 int(10) 有什么不同？「🟢 校招/初级」

考察点：MySQL 数据类型的实际使用理解。

参考答案：

CHAR vs VARCHAR：

CHAR(n)：定长字符串，n 是字符数，不足补空格，最多 255 字符
- 存储固定：CHAR(10) 存 "abc" 占 10 字符空间
- 适合：定长编码、手机号、身份证号
VARCHAR(n)：变长字符串，n 是最大字符数，额外用 1-2 字节记录长度
- 存储变长：VARCHAR(10) 存 "abc" 只占 3+1=4 字节
- 适合：变长内容、姓名、地址
varchar(n) 的 n 是字符数（不是字节数）， utf8mb4 下一个中文占 4 字节

int(1) vs int(10)：

完全相同！都是 4 字节整数，取值范围一样
括号里的数字只是显示宽度（配合 ZEROFILL 使用时才有意义）
int(10) ZEROFILL：显示时补零到 10 位（如 0000000001）
MySQL 8.0.17 已废弃整数类型的显示宽度

其他类型注意事项：

TEXT：最大 65535 字节，不参与索引排序、不能用默认值
BIGINT：8字节，自增 ID 推荐
DECIMAL(10,2)：精确小数，金额必须用 DECIMAL 不用 FLOAT/DOUBLE
TIMESTAMP：4字节，范围到 2038 年；DATETIME：8字节，范围到 9999 年
IP 地址：用 INT UNSIGNED 存储（INET_ATON / INET_NTOA 转换），节省空间

追问延伸：

varchar(255) 和 varchar(256) 有什么区别？
TEXT 和 BLOB 的区别？

Q33: MySQL 的 in 和 exists 有什么区别？怎么选？「🟡 中级」

考察点：SQL 优化的实战理解。

参考答案：

in 和 exists 用于子查询，区别在于驱动方向：

IN：外表驱动内表

sql

SELECT * FROM A WHERE id IN (SELECT a_id FROM B);
-- 先查 B 表获取 a_id 列表，再查 A 表

外表（A）小、内表（B）大 → 用 IN
IN 内部会做 hash 连接，外表每行去匹配

EXISTS：内表驱动外表

sql

SELECT * FROM A WHERE EXISTS (SELECT 1 FROM B WHERE B.a_id = A.id);
-- 先遍历 A 表每行，对每行查 B 表是否有匹配

外表（A）大、内表（B）小 → 用 EXISTS
类似外表每行做一次子查询

选择原则：小表驱动大表

外表小、子查询大 → IN
外表大、子查询小 → EXISTS
MySQL 5.6+ 优化器会自动优化子查询，差异不大
MySQL 8.0 有更多优化（如物化子查询、半连接优化），很多时候 in/exists 差别不大

其他替代方案：

JOIN：大多数情况 JOIN 性能最好（优化器能选择最优驱动顺序）
NOT IN vs NOT EXISTS：NOT IN 可能因 NULL 值导致意外结果，NOT EXISTS 更安全

追问延伸：

NOT IN 有什么坑？（子查询结果含 NULL 时返回空集）
JOIN 和子查询哪个好？

Q34: MySQL 中有哪些页面置换算法？Buffer Pool 的 LRU 怎么优化？「🟡 中级」

考察点：内存管理和缓存淘汰的底层理解。

参考答案：

常见页面置换算法：

OPT（最佳置换）：淘汰未来最长时间不被访问的页 → 理论最优，无法实现
FIFO（先进先出）：淘汰最早进入的页 → 简单但不考虑访问频率
LRU（最近最少使用）：淘汰最久没被访问的页 → 常用，但全表扫描会污染
LFU（最不经常使用）：淘汰访问次数最少的页 → 适合热点数据
Clock（时钟算法/二次机会）：近似 LRU，用访问位实现 → 开销小

Buffer Pool 的 LRU 优化：

不是纯 LRU，而是改进版：young 区 + old 区（默认 3:7 或 5:5）
新页插入 old 区头部（midpoint）
在 old 区停留超过 innodb_old_blocks_time（默认1秒）才移到 young 区
目的：防止全表扫描（预读）把 young 区热点数据挤掉
young 区也有 LRU 变体：1/4 后移策略（频繁访问的页从 young 尾部移到头部）

追问延伸：

为什么不直接用纯 LRU？
LFU 和 LRU 的区别？

Q35: CHAR 和 VARCHAR 的区别？varchar 后面代表字节还是字符？「🟢 校招」

考察点：MySQL 字符串类型的存储机制与字符集理解。

参考答案：

CHAR 与 VARCHAR 的核心区别：

特性	CHAR(n)	VARCHAR(n)
存储方式	定长，不足补空格	变长，按实际长度存储
n 的含义	字符数	字符数（最大字符数）
额外开销	无	1-2 字节记录长度
最大长度	255 字符	65535 字节（所有列共享）
存储效率	定长场景更紧凑	变长场景更省空间
修改性能	不产生碎片	更新可能产生碎片
适用场景	手机号、身份证、MD5	姓名、地址、备注

varchar(n) 的 n 是字符数，不是字节数：

VARCHAR(10) 表示最多存 10 个字符
在 utf8mb4 编码下，一个中文字符占 4 字节，所以 VARCHAR(10) 最多占 10×4 + 2(长度字节) = 42 字节
在 utf8 编码下，一个中文字符占 3 字节，VARCHAR(10) 最多占 10×3 + 2 = 32 字节
在 latin1 编码下，一个字符占 1 字节，VARCHAR(10) 最多占 10×1 + 2 = 12 字节

sql

-- 演示字符数 vs 字节数
CREATE TABLE char_test (
    c CHAR(5),
    v VARCHAR(5)
) CHARSET=utf8mb4;

INSERT INTO char_test VALUES ('你好世界测', '你好世界测'); -- 5个中文字符，可以存入
-- CHAR(5) 占 5*4 = 20 字节
-- VARCHAR(5) 占 5*4 + 2 = 22 字节

SELECT LENGTH(c), CHAR_LENGTH(c),  -- LENGTH 返回字节数，CHAR_LENGTH 返回字符数
       LENGTH(v), CHAR_LENGTH(v)
FROM char_test;
-- 结果: 20, 5, 20, 5

varchar 长度字节的规则：

实际长度 ≤ 255 字节 → 用 1 字节记录长度
实际长度 > 255 字节 → 用 2 字节记录长度

sql

-- varchar(255) vs varchar(256) 的区别
-- utf8mb4 下 varchar(255) 最多 255*4 = 1020 字节 > 255 → 用 2 字节
-- 但在 latin1 下 varchar(255) 最多 255 字节 → 用 1 字节，varchar(256) 用 2 字节
-- 这就是为什么很多历史项目用 varchar(255)：在 latin1/utf8 下只用 1 字节存长度

CHAR 尾部空格的处理差异：

sql

CREATE TABLE space_test (
    c CHAR(5),
    v VARCHAR(5)
);

INSERT INTO space_test VALUES ('ab   ', 'ab   ');  -- ab + 3个空格

SELECT CONCAT('[', c, ']'), CONCAT('[', v, ']') FROM space_test;
-- CHAR: [ab]      ← 尾部空格被截断（检索时去除）
-- VARCHAR: [ab   ] ← 尾部空格保留

追问延伸：

varchar(255) 和 varchar(256) 有什么区别？（长度记录字节数可能不同）
TEXT 类型适合存什么？和 VARCHAR 有什么区别？（不能加默认值、不参与排序）

Q36: MySQL 如何避免重复插入数据？「🟢 校招」

考察点：唯一约束与去重方案的实际应用。

参考答案：

避免重复插入的常见方案：

方案	原理	优点	缺点
唯一索引（UNIQUE）	数据库层强制唯一	简单可靠	需要建索引
INSERT IGNORE	唯一键冲突时忽略	不报错	静默失败可能遗漏
REPLACE INTO	冲突时先删后插	保证最新数据	会变主键、丢失自增
INSERT ... ON DUPLICATE KEY UPDATE	冲突时更新	灵活	语法复杂
先查后插	SELECT 判断再 INSERT	逻辑清晰	并发不安全

方案1：唯一索引 + INSERT IGNORE

sql

-- 建表时加唯一约束
CREATE TABLE user (
    id BIGINT PRIMARY KEY AUTO_INCREMENT,
    username VARCHAR(50) NOT NULL,
    email VARCHAR(100),
    UNIQUE KEY uk_email (email)
);

-- 插入时如果 email 重复则忽略（返回 affected rows = 0）
INSERT IGNORE INTO user (username, email) VALUES ('张三', 'zhangsan@qq.com');
-- 第二次插入相同 email → 不会报错，静默忽略

方案2：REPLACE INTO（先删后插）

sql

-- 冲突时先 DELETE 旧行再 INSERT 新行
REPLACE INTO user (username, email) VALUES ('张三', 'zhangsan@qq.com');
-- 注意：会改变主键 id（自增新值），关联的外键数据可能受影响
-- 返回 affected rows = 2（删1行 + 插1行）

方案3：INSERT ... ON DUPLICATE KEY UPDATE（冲突时更新）

sql

-- 冲突时更新指定字段（最灵活，推荐）
INSERT INTO user (username, email) VALUES ('张三', 'zhangsan@qq.com')
ON DUPLICATE KEY UPDATE username = VALUES(username), update_time = NOW();

-- MySQL 8.0 推荐用别名替代 VALUES() 函数（VALUES() 已废弃）
INSERT INTO user (username, email) VALUES ('张三', 'zhangsan@qq.com') AS new
ON DUPLICATE KEY UPDATE username = new.username, update_time = NOW();

方案4：先查后插（并发不安全，需配合锁）

sql

-- 单线程下可行，并发下有竞态条件
SELECT COUNT(*) FROM user WHERE email = 'zhangsan@qq.com';
-- 如果返回 0，再插入
INSERT INTO user (username, email) VALUES ('张三', 'zhangsan@qq.com');

-- 并发安全的写法：加排他锁
BEGIN;
SELECT * FROM user WHERE email = 'zhangsan@qq.com' FOR UPDATE;
-- 如果不存在则插入
INSERT INTO user (username, email) VALUES ('张三', 'zhangsan@qq.com');
COMMIT;

各方案 affected rows 返回值含义：

INSERT IGNORE：0 = 忽略，1 = 正常插入
REPLACE INTO：1 = 正常插入，2 = 删后插入
ON DUPLICATE KEY UPDATE：1 = 正常插入，2 = 更新

追问延伸：

REPLACE INTO 为什么不推荐在有外键的表上用？（会触发外键级联删除）
ON DUPLICATE KEY UPDATE 的 affected rows 为什么更新时返回 2？（标记为更新操作）

Q37: SQL 查询语句的执行顺序是怎么样的？「🟡 中级」

考察点：SQL 执行原理的深入理解，影响别名使用和优化判断。

参考答案：

SQL 的书写顺序与逻辑执行顺序不同：

书写顺序：SELECT → FROM → JOIN → ON → WHERE → GROUP BY → HAVING → ORDER BY → LIMIT

逻辑执行顺序：
1. FROM          ← 确定数据源表
2. ON            ← JOIN 的连接条件
3. JOIN          ← 执行表连接
4. WHERE         ← 行级过滤（不能用聚合函数）
5. GROUP BY      ← 分组
6. HAVING        ← 组级过滤（可用聚合函数）
7. SELECT        ← 选择列、计算聚合函数、应用别名
8. DISTINCT      ← 去重
9. ORDER BY      ← 排序（可用 SELECT 中的别名）
10. LIMIT        ← 分页截取

执行顺序的关键影响：

sql

-- 示例：查询平均工资超过 10000 的部门，按平均工资降序
SELECT
    dept_id,
    AVG(salary) AS avg_salary,
    AVG(salary) * 12 AS annual_avg  -- SELECT 中可引用前面的聚合
FROM employee
WHERE status = 'active'          -- ① 先过滤在职员工（不能用 avg_salary）
GROUP BY dept_id                  -- ② 再按部门分组
HAVING AVG(salary) > 10000       -- ③ 过滤平均工资（不能用 WHERE）
ORDER BY avg_salary DESC          -- ④ 用 SELECT 中的别名排序
LIMIT 5;

为什么 WHERE 不能用聚合函数：

WHERE 在 GROUP BY 之前执行，此时还没分组，没有聚合值
HAVING 在 GROUP BY 之后执行，此时有分组结果，可以用聚合函数

sql

-- 错误：WHERE 中不能用聚合函数
SELECT dept_id, AVG(salary) FROM employee WHERE AVG(salary) > 10000 GROUP BY dept_id;
-- 报错：Invalid use of group function

-- 正确：用 HAVING
SELECT dept_id, AVG(salary) FROM employee GROUP BY dept_id HAVING AVG(salary) > 10000;

为什么 WHERE 不能用 SELECT 的别名，但 ORDER BY 可以：

WHERE 在 SELECT 之前执行 → 别名还没生成
ORDER BY 在 SELECT 之后执行 → 别名已存在

sql

-- 错误：WHERE 中不能用 SELECT 别名
SELECT salary * 12 AS annual_salary FROM employee WHERE annual_salary > 100000;
-- 报错：Unknown column 'annual_salary'

-- 正确：WHERE 中用原始表达式
SELECT salary * 12 AS annual_salary FROM employee WHERE salary * 12 > 100000;

-- ORDER BY 可以用别名（推荐，更简洁）
SELECT salary * 12 AS annual_salary FROM employee ORDER BY annual_salary DESC;

物理执行顺序（优化器可能调整）：

MySQL 优化器会根据成本重排部分操作（如谓词下推、提前 LIMIT）
但逻辑执行顺序不变，理解逻辑顺序即可

优化器常见重排：
- 谓词下推：把 WHERE 条件提前到 JOIN 之前
- 提前 LIMIT：有 ORDER BY + LIMIT 时，找到足够行就停
- 子查询物化：把子查询结果存为临时表

追问延伸：

GROUP BY 之后为什么 SELECT 的非聚合列必须在 GROUP BY 中？（SQL92 模式 vs ONLY_FULL_GROUP_BY）
LIMIT 是在排序之后执行吗？那大表 LIMIT 为什么还是慢？（需要先排序完才能截取）

Q38: MySQL 的 in 和 exists 有什么区别？「🟡 中级」

考察点：子查询优化与驱动表选择的实战理解。

参考答案：

in 和 exists 的核心区别在于驱动表的方向不同：

特性	IN	EXISTS
驱动方向	外表驱动内表	内表驱动外表（外表逐行匹配）
执行方式	先查子查询，结果做 hash/匹配	外表每行执行一次子查询
适合场景	外表小、子查询结果大	外表大、子查询结果小
NULL 处理	NOT IN 遇 NULL 返回空	NOT EXISTS 不受 NULL 影响
优化器	5.6+ 会自动优化为半连接	5.6+ 会自动优化

IN 的执行逻辑：

sql

-- IN：先执行子查询，拿到 id 列表，再用列表匹配外表
SELECT * FROM orders WHERE user_id IN (SELECT id FROM users WHERE vip = 1);

-- 等价于：
-- 1. SELECT id FROM users WHERE vip = 1  → 得到 (1, 3, 5, 8)
-- 2. SELECT * FROM orders WHERE user_id IN (1, 3, 5, 8)
-- 适合：orders 表小（被驱动表），users 表大

EXISTS 的执行逻辑：

sql

-- EXISTS：外表每行执行一次子查询判断是否存在
SELECT * FROM orders o WHERE EXISTS (
    SELECT 1 FROM users u WHERE u.id = o.user_id AND u.vip = 1
);

-- 等价于：
-- 1. 遍历 orders 每一行
-- 2. 对每行执行 SELECT 1 FROM users WHERE id = orders.user_id AND vip = 1
-- 3. 子查询返回行则该 orders 行保留
-- 适合：orders 表大（驱动表），users 表小

选择原则：小表驱动大表

sql

-- 场景1：A 表 100 行，B 表 100万行
-- 用 IN（A 小 B 大，A 驱动 B）
SELECT * FROM A WHERE id IN (SELECT a_id FROM B);

-- 场景2：A 表 100万行，B 表 100 行
-- 用 EXISTS（A 大 B 小，B 驱动 A 做判断）
SELECT * FROM A WHERE EXISTS (SELECT 1 FROM B WHERE B.a_id = A.id);

NOT IN 的 NULL 陷阱：

sql

-- NOT IN 遇到 NULL 会返回空集！
CREATE TABLE t1 (id INT);
CREATE TABLE t2 (id INT);
INSERT INTO t1 VALUES (1), (2), (3);
INSERT INTO t2 VALUES (1), (NULL);

SELECT * FROM t1 WHERE id NOT IN (SELECT id FROM t2);
-- 预期：2, 3
-- 实际：空集！因为 NOT IN 等价于 id != 1 AND id != NULL → 任何值 != NULL 都是 NULL（false）

-- NOT EXISTS 不受 NULL 影响
SELECT * FROM t1 WHERE NOT EXISTS (SELECT 1 FROM t2 WHERE t2.id = t1.id);
-- 结果：2, 3（正确）

MySQL 8.0 的优化：

优化器会自动将 IN 子查询转换为半连接（Semi-Join）
很多场景下 IN 和 EXISTS 性能差异已不大
但理解原理仍有必要，因为优化器不是万能的

sql

-- 查看优化器是否做了半连接优化
EXPLAIN SELECT * FROM orders WHERE user_id IN (SELECT id FROM users WHERE vip = 1);
-- Extra 列出现 "FirstMatch" 或 "Semi-joined" 表示已优化

追问延伸：

JOIN 能替代 IN/EXISTS 吗？有什么区别？（JOIN 会产生重复行，IN/EXISTS 不会）
NOT IN 的 NULL 陷阱怎么避免？（加 WHERE id IS NOT NULL 或用 NOT EXISTS）

Q39: 给定学生表和成绩表，写 SQL 查询「🟡 中级」

考察点：SQL 实战能力，窗口函数与多表关联。

参考答案：

建表与测试数据：

sql

-- 学生表
CREATE TABLE student (
    id INT PRIMARY KEY,
    name VARCHAR(20),
    class VARCHAR(10)  -- 班级
);

-- 成绩表
CREATE TABLE score (
    student_id INT,
    course VARCHAR(20),
    score INT,
    PRIMARY KEY (student_id, course)
);

INSERT INTO student VALUES (1, '张三', '一班'), (2, '李四', '一班'), (3, '王五', '二班');
INSERT INTO score VALUES
(1, '语文', 90), (1, '数学', 80), (1, '英语', 70),
(2, '语文', 85), (2, '数学', 95), (2, '英语', 75),
(3, '语文', 60), (3, '数学', 100), (3, '英语', 90);

题目1：查询每个学生的总分并排名

sql

-- 方法1：传统 GROUP BY + JOIN（MySQL 7 兼容）
SELECT s.name, IFNULL(t.total, 0) AS total
FROM student s
LEFT JOIN (
    SELECT student_id, SUM(score) AS total
    FROM score
    GROUP BY student_id
) t ON s.id = t.student_id
ORDER BY total DESC;

-- 方法2：窗口函数（MySQL 8.0+，推荐）
SELECT
    s.name,
    SUM(sc.score) AS total,
    RANK() OVER (ORDER BY SUM(sc.score) DESC) AS `rank`
FROM student s
LEFT JOIN score sc ON s.id = sc.student_id
GROUP BY s.id, s.name
ORDER BY total DESC;

-- 结果：
-- 李四  255  1
-- 张三  240  2
-- 王五  250  ...（王五总分250，实际排第2）

题目2：查询每门课程的第一名

sql

-- 方法1：窗口函数（推荐）
SELECT name, course, score
FROM (
    SELECT
        s.name, sc.course, sc.score,
        RANK() OVER (PARTITION BY sc.course ORDER BY sc.score DESC) AS `rank`
    FROM student s
    JOIN score sc ON s.id = sc.student_id
) t
WHERE `rank` = 1;

-- 方法2：关联子查询（MySQL 7 兼容）
SELECT s.name, sc.course, sc.score
FROM score sc
JOIN student s ON s.id = sc.student_id
WHERE sc.score = (
    SELECT MAX(score) FROM score WHERE course = sc.course
);

题目3：查询每个班级各科平均分，行转列显示

sql

-- CASE WHEN 实现行转列
SELECT
    s.class,
    ROUND(AVG(CASE WHEN sc.course = '语文' THEN sc.score END), 1) AS 语文,
    ROUND(AVG(CASE WHEN sc.course = '数学' THEN sc.score END), 1) AS 数学,
    ROUND(AVG(CASE WHEN sc.course = '英语' THEN sc.score END), 1) AS 英语
FROM student s
JOIN score sc ON s.id = sc.student_id
GROUP BY s.class;

-- 结果：
-- 一班  87.5  87.5  72.5
-- 二班  60.0  100.0  90.0

题目4：查询成绩连续上升的学生

sql

-- 窗口函数 LAG 对比上一次成绩
SELECT DISTINCT name
FROM (
    SELECT
        s.name,
        sc.course,
        sc.score,
        LAG(sc.score) OVER (PARTITION BY s.id ORDER BY sc.course) AS prev_score
    FROM student s
    JOIN score sc ON s.id = sc.student_id
) t
WHERE prev_score IS NOT NULL AND score > prev_score;

题目5：查询各科成绩均大于 80 分的学生

sql

-- 方法1：用 HAVING + MIN
SELECT s.name
FROM student s
JOIN score sc ON s.id = sc.student_id
GROUP BY s.id, s.name
HAVING MIN(sc.score) > 80;

-- 方法2：用 NOT EXISTS（排除有低于80分的）
SELECT name FROM student s
WHERE NOT EXISTS (
    SELECT 1 FROM score sc WHERE sc.student_id = s.id AND sc.score <= 80
);

RANK / DENSE_RANK / ROW_NUMBER 的区别：

函数	说明	示例（同分时）
RANK()	同分同排名，跳号	90,90,80 → 1,1,3
DENSE_RANK()	同分同排名，不跳号	90,90,80 → 1,1,2
ROW_NUMBER()	唯一递增编号	90,90,80 → 1,2,3

追问延伸：

查询总分排名第 2 到第 4 名的学生怎么写？（窗口函数 + 子查询 WHERE rank BETWEEN 2 AND 4）
行转列除了 CASE WHEN 还有什么方法？（GROUP_CONCAT 或存储过程动态 SQL）

Q40: 聚簇索引和非聚簇索引的区别？「🟡 中级」

考察点：InnoDB 索引存储结构的深入理解。

参考答案：

聚簇索引与非聚簇索引的核心区别：

特性	聚簇索引	非聚簇索引（二级索引）
叶子节点存储	完整行数据	主键值
数量限制	每张表只有一个	可以有多个
物理排序	数据按聚簇索引键物理有序	独立的 B+ 树结构
查询效率	直接拿到行数据	需要回表查完整行
默认选择	主键（或第一个 NOT NULL 唯一索引，或隐藏 row_id）	用户创建的普通索引

InnoDB 的聚簇索引：

聚簇索引 B+ 树（按主键 id 排序）：
        [id=10 | id=20]              ← 非叶子节点（只存主键+指针）
        /              \
[id=1|id=5|id=10]  [id=15|id=20]    ← 非叶子节点
    /     |     \      /     \
[行数据1][行数据5][行数据10][行数据15][行数据20]  ← 叶子节点（存完整行）

特点：数据本身就按主键有序存储，叶子节点 = 数据页

非聚簇索引（二级索引）：

二级索引 B+ 树（按 name 排序）：
        [name='李' | name='王']
        /                  \
[name='阿'][name='李'][name='王'][name='张']  ← 叶子节点存 (name, 主键id)

查询 SELECT * FROM user WHERE name = '李四'：
1. 在 name 索引树找到 name='李四' → 得到主键 id=2
2. 回表：到聚簇索引树用 id=2 查到完整行数据（多一次 B+ 树查找）

sql

-- 演示回表过程
CREATE TABLE user (
    id INT PRIMARY KEY,       -- 聚簇索引
    name VARCHAR(20),
    age INT,
    INDEX idx_name (name)     -- 二级索引
);

-- 查询1：用主键查询 → 聚簇索引，无需回表
SELECT * FROM user WHERE id = 1;
-- EXPLAIN: type=const, Extra 无 Using index 之外的额外信息

-- 查询2：用 name 查询全部列 → 二级索引 + 回表
SELECT * FROM user WHERE name = '张三';
-- EXPLAIN: type=ref, 需要回表取 age 等其他列

-- 查询3：只查 name → 覆盖索引，无需回表
SELECT name FROM user WHERE name = '张三';
-- EXPLAIN: Extra=Using index（覆盖索引）

MyISAM 与 InnoDB 的区别：

MyISAM：没有聚簇索引，主键索引和二级索引的叶子节点都存行指针（数据行的物理地址），都是非聚簇的
InnoDB：主键索引是聚簇索引（存行数据），二级索引存主键值

聚簇索引的选择优先级（InnoDB 自动选择）：

显式定义的 PRIMARY KEY
第一个所有列都 NOT NULL 的 UNIQUE 索引
自动生成隐藏的 _rowid（6 字节，不可见，不可用）

为什么 InnoDB 推荐用自增主键：

自增主键：新数据顺序追加到叶子节点末尾 → 顺序写 → 无页分裂
UUID 主键：随机值 → 插入位置随机 → 频繁页分裂 → 性能差 + 空间碎片

追问延伸：

为什么二级索引存主键值而不是行指针？（减少二级索引维护成本，行移动时不用更新所有索引）
没有主键的 InnoDB 表会怎样？（用隐藏 row_id 做聚簇索引，但对用户不可见，无法加速查询）

Q41: 联合索引的实现原理？最左前缀原则？「🟡 中级」

考察点：联合索引的 B+ 树结构与查询匹配规则。

参考答案：

联合索引 (a, b, c) 在 B+ 树中的排列方式：

联合索引 (a, b, c) 的 B+ 树：
先按 a 排序，a 相同按 b 排序，b 相同按 c 排序

叶子节点（有序）：
(1,1,1) (1,1,2) (1,2,1) (1,2,3) (2,1,1) (2,1,3) (2,2,1) (3,1,1) ...
 a↑      a↑      a↑      a↑      a↑      a↑      a↑      a↑
         b↑      b↑      b↑      b↑      b↑      b↑
                 c↑              c↑

因为这种排序方式，查询必须从最左列开始才能利用索引的有序性。

最左前缀原则的匹配规则：

sql

CREATE TABLE t (a INT, b INT, c INT, d INT, INDEX idx_abc (a, b, c));

-- ✅ 完整使用索引
WHERE a=1 AND b=2 AND c=3        -- 用到 a, b, c
WHERE a=1 AND b=2                -- 用到 a, b
WHERE a=1                        -- 用到 a

-- ✅ MySQL 优化器会自动调整顺序
WHERE b=2 AND a=1                -- 等价于 a=1 AND b=2，用到 a, b
WHERE c=3 AND b=2 AND a=1        -- 等价于 a=1 AND b=2 AND c=3，全用到

-- ❌ 无法使用索引（缺少最左列 a）
WHERE b=2                        -- 用不到索引
WHERE c=3                        -- 用不到索引
WHERE b=2 AND c=3               -- 用不到索引

-- ⚠️ 部分使用（范围查询截断后续列）
WHERE a=1 AND b>2 AND c=3       -- 只用到 a, b（b 是范围查询，c 用不到索引排序）
WHERE a=1 AND b=2 AND c>3       -- 用到 a, b, c（c 在最后，范围查询不截断前面的）
WHERE a>1 AND b=2 AND c=3       -- 只用到 a（a 是范围查询，截断 b 和 c）

范围查询截断的原因：

sql

-- WHERE a=1 AND b>2 AND c=3
-- a=1 的行：(1,1,1)(1,1,2)(1,2,1)(1,2,3)(1,3,1)(1,3,2)...
-- b>2 过滤后：(1,3,1)(1,3,2)(1,4,1)(1,4,3)...
-- 此时 c 不是连续有序的（1,2,1,3...），所以 c=3 无法用索引二分查找
-- 只能遍历这些行逐个判断 c=3

索引下推（ICP，Index Condition Pushdown，5.6+）：

sql

-- WHERE a=1 AND b>2 AND c=3
-- 没有 ICP：用 a=1 AND b>2 从索引取所有行 → 逐行回表 → 再判断 c=3
-- 有 ICP：用 a=1 AND b>2 从索引取行时，直接在索引层判断 c=3 → 减少回表次数

-- EXPLAIN 中 Extra 显示 "Using index condition" 表示用了 ICP
EXPLAIN SELECT * FROM t WHERE a=1 AND b>2 AND c=3;
-- Extra: Using index condition

最左前缀的"等值匹配 + 范围"模式：

sql

-- 这个模式能用到 a, b, c 全部三列
WHERE a=1 AND b=2 AND c>3 AND c<10
-- a 等值，b 等值，c 范围 → 三个列都能用索引定位

-- 这个模式只用到 a, b
WHERE a=1 AND b BETWEEN 2 AND 5 AND c=3
-- a 等值，b 范围 → c 被截断

LIKE 的最左前缀：

sql

-- LIKE 也遵循最左前缀
WHERE name LIKE '张%'      -- ✅ 可以用索引（前缀匹配）
WHERE name LIKE '%张'      -- ❌ 用不到索引（前缀不确定）
WHERE name LIKE '%张%'     -- ❌ 用不到索引
WHERE name LIKE '张_三'    -- ✅ 可以用索引（_ 匹配单个字符）

联合索引的设计建议：

区分度高的列放前面
等值查询的列放前面，范围查询的列放后面
覆盖常用查询的列

追问延伸：

联合索引 (a,b,c)，WHERE a=1 ORDER BY b,c 能否避免 filesort？（能，因为索引本身就是按 b,c 排序的）
MySQL 8.0 的 Index Skip Scan 是什么？（联合索引首列不同值少时，可跳过首列扫描）

Q42: 什么是覆盖索引？什么情况下会回表？「🟡 中级」

考察点：索引优化与回表机制的理解。

参考答案：

覆盖索引：查询所需的所有列都包含在索引中，不需要回表到聚簇索引取数据。EXPLAIN 中 Extra 显示 Using index。

回表：通过二级索引找到主键值后，再到聚簇索引查找完整行数据。

sql

CREATE TABLE employee (
    id INT PRIMARY KEY,
    name VARCHAR(20),
    age INT,
    dept VARCHAR(20),
    salary INT,
    INDEX idx_name_age (name, age),
    INDEX idx_dept (dept)
);

-- 场景1：覆盖索引（无需回表）
SELECT name, age FROM employee WHERE name = '张三';
-- 查询列 (name, age) 都在联合索引 idx_name_age 中
-- EXPLAIN Extra: Using index

-- 场景2：需要回表
SELECT name, age, salary FROM employee WHERE name = '张三';
-- salary 不在索引中 → 需要回表查聚簇索引取 salary
-- EXPLAIN Extra: 无 Using index

-- 场景3：覆盖索引
SELECT id, name FROM employee WHERE name = '张三';
-- id 是主键，二级索引叶子节点本身就存了主键 → 覆盖索引
-- EXPLAIN Extra: Using index

-- 场景4：需要回表
SELECT * FROM employee WHERE dept = '技术部';
-- SELECT * 包含所有列，idx_dept 只有 dept 和主键 → 需要回表

回表的开销分析：

操作	覆盖索引	需要回表
B+ 树查找次数	1 次（二级索引树）	N+1 次（1次二级索引 + N次聚簇索引）
磁盘 IO	少	多（每行回表可能一次 IO）
适用场景	只查索引列	需要 SELECT * 或非索引列

sql

-- 回表次数 = 查询匹配的行数
-- 如果 name='张三' 匹配 1000 行，就要回表 1000 次

-- 优化：避免不必要的回表
-- 反例：SELECT * 查全列
SELECT * FROM employee WHERE name = '张三';   -- 每行都回表

-- 正例：只查需要的列，尽量覆盖索引
SELECT name, age FROM employee WHERE name = '张三';  -- 覆盖索引，0 次回表

强制使用覆盖索引的场景：

sql

-- 统计各部门人数，不需要行数据
-- 反例：回表取数据但只用 dept 统计
SELECT dept, COUNT(*) FROM employee GROUP BY dept;
-- 如果数据量大，每行都回表取 dept（虽然 dept 在索引里）

-- 正例：用覆盖索引
-- 如果有 INDEX(dept)，COUNT 直接在索引树上完成
SELECT dept, COUNT(*) FROM employee WHERE dept IS NOT NULL GROUP BY dept;
-- EXPLAIN Extra: Using index

覆盖索引的局限性：

索引列不能太多（索引越大，维护成本越高，写入越慢）
VARCHAR/TEXT 等大字段不适合放进索引（索引有最大长度限制）
不是所有查询都能覆盖（业务需要查多列时）

sql

-- 索引列总长度限制
-- InnoDB 索引最大长度：3072 字节（默认 16K 页，4 行预留）
-- utf8mb4 下 VARCHAR(255) = 255*4 = 1020 字节
-- 所以联合索引最多 3 个 VARCHAR(255) 列

-- 不适合覆盖索引的场景：查询列太多
SELECT name, age, dept, salary, hire_date FROM employee WHERE name = '张三';
-- 5 个列全部放进索引不现实 → 只能回表

追问延伸：

SELECT COUNT(*) 能用覆盖索引吗？（能，优化器会选择最小的索引扫描）
索引下推（ICP）和覆盖索引有什么关系？（ICP 减少回表次数，覆盖索引直接消除回表）

Q43: 索引失效有哪些场景？「🟡 中级」

考察点：索引优化的实战经验，高频面试题。

参考答案：

常见的索引失效场景汇总：

场景	示例	原因
函数运算	`WHERE YEAR(date_col) = 2024`	索引存的是原值，函数后无法匹配
隐式类型转换	`WHERE varchar_col = 123`	字符串列被转成数字比较
前导模糊查询	`WHERE name LIKE '%张'`	B+ 树无法定位前缀
OR 部分无索引	`WHERE a=1 OR b=2`（b 无索引）	需要全表扫描 b
不符合最左前缀	联合索引跳过首列	无法利用索引有序性
范围后截断	`WHERE a=1 AND b>2 AND c=3`	b 范围查询截断 c
NOT / != / NOT IN	`WHERE id != 1`	无法用索引二分定位
IS NOT NULL	`WHERE col IS NOT NULL`	部分情况不走索引

1. 对索引列做函数运算

sql

-- ❌ 索引失效
SELECT * FROM orders WHERE YEAR(create_time) = 2024;
-- 等价于对每行调用 YEAR() 函数，无法用索引

-- ✅ 改为范围查询
SELECT * FROM orders WHERE create_time >= '2024-01-01' AND create_time < '2025-01-01';

-- ❌ 索引失效
SELECT * FROM user WHERE LEFT(name, 1) = '张';

-- ✅ 改为 LIKE 前缀
SELECT * FROM user WHERE name LIKE '张%';

-- MySQL 8.0+ 支持函数索引
CREATE INDEX idx_year ON orders ((YEAR(create_time)));

2. 隐式类型转换

sql

CREATE TABLE user (id INT, phone VARCHAR(20), INDEX idx_phone (phone));

-- ❌ 索引失效：phone 是 VARCHAR，传入数字 → MySQL 把 phone 转成数字比较
SELECT * FROM user WHERE phone = 13800138000;
-- 等价于 CAST(phone AS SIGNED) = 13800138000 → 函数运算 → 索引失效

-- ✅ 传入字符串
SELECT * FROM user WHERE phone = '13800138000';

-- 注意：反过来数字列传字符串不会失效
CREATE TABLE t (id INT, INDEX idx_id (id));
SELECT * FROM t WHERE id = '1';  -- ✅ 字符串 '1' 转成数字，不涉及索引列的函数

3. 模糊查询前导通配符

sql

-- ❌ 索引失效
SELECT * FROM user WHERE name LIKE '%张';
SELECT * FROM user WHERE name LIKE '%张%';

-- ✅ 前缀匹配可以走索引
SELECT * FROM user WHERE name LIKE '张%';

-- 全文检索替代方案
SELECT * FROM user WHERE MATCH(name) AGAINST('张' IN BOOLEAN MODE);

4. OR 条件部分无索引

sql

-- ❌ a 有索引，b 无索引 → 全表扫描
SELECT * FROM t WHERE a = 1 OR b = 2;

-- ✅ 都有索引 → 可以用 index_merge
CREATE INDEX idx_b ON t(b);
SELECT * FROM t WHERE a = 1 OR b = 2;  -- 走 index_merge（合并两个索引结果）

-- ✅ 改用 UNION
SELECT * FROM t WHERE a = 1
UNION
SELECT * FROM t WHERE b = 2;

5. 不等于 / NOT IN / IS NOT NULL

sql

-- 这些操作无法用 B+ 树二分查找定位，通常走全表扫描
SELECT * FROM user WHERE id != 1;           -- 可能不走索引
SELECT * FROM user WHERE id NOT IN (1,2,3); -- 可能不走索引
SELECT * FROM user WHERE name IS NOT NULL;  -- 可能不走索引

-- 但 IS NULL 通常可以走索引
SELECT * FROM user WHERE name IS NULL;      -- 通常走索引

6. 范围查询导致后续列索引失效

sql

-- 联合索引 (a, b, c)
SELECT * FROM t WHERE a=1 AND b>10 AND c=5;  -- c 用不到索引（被 b 的范围截断）

-- 优化：把范围查询列放最后
-- 建索引时：(a, c, b) → WHERE a=1 AND c=5 AND b>10 全部能用

7. 字符集不一致导致 JOIN 索引失效

sql

-- 两表 JOIN 时，关联列字符集不同会导致索引失效
-- table1.name CHARSET=utf8
-- table2.name CHARSET=utf8mb4
SELECT * FROM t1 JOIN t2 ON t1.name = t2.name;  -- 索引失效（需要做字符集转换）

-- 解决：统一字符集
ALTER TABLE t1 CONVERT TO CHARACTER SET utf8mb4;

追问延伸：

WHERE id + 1 = 10 会走索引吗？（不会，等价改写为 id = 9 才行）
ORDER BY 什么时候会索引失效？（ORDER BY 列与索引顺序不一致、方向不一致、混合 ASC/DESC）

Q44: 性别字段适合加索引吗？什么字段不适合加索引？「🟡 中级」

考察点：索引选择性（区分度）与索引设计原则。

参考答案：

性别字段不适合加索引，核心原因是区分度太低：

sql

-- 性别字段只有 男/女 两个值
-- 假设 100 万行数据，男 50 万，女 50 万
-- 区分度 = 不同值数量 / 总行数 = 2 / 1000000 = 0.000002

SELECT * FROM user WHERE gender = '男';
-- 优化器判断：走索引要回表 50 万次 → 不如直接全表扫描
-- 结果：即使建了索引也不会被使用

索引选择性（区分度）公式：

选择性 = COUNT(DISTINCT column) / COUNT(*)

选择性越高 → 索引越有效
选择性 > 0.3（30%）→ 索引效果较好
选择性 < 0.1（10%）→ 索引效果差，可能不走索引
选择性 = 1 → 唯一索引，效果最好

字段类型	区分度	是否适合索引	原因
性别（男/女）	极低	❌	只有两个值，走索引不如全表扫
状态（启用/禁用）	极低	❌	同上
布尔值	极低	❌	只有两个值
手机号	高	✅	几乎唯一
身份证号	极高	✅	唯一
用户 ID	极高	✅	唯一
姓名	中	⚠️	有重名，但可配合其他列建联合索引
邮箱	高	✅	基本唯一
创建时间	中高	✅	值分散，适合范围查询
枚举状态（多种）	中低	⚠️	值少时不适合单列索引，可做联合索引首列
UUID	极高	✅ 但不推荐	唯一但插入导致页分裂

不适合加索引的字段：

sql

-- 1. 区分度低的字段
gender TINYINT          -- 男/女
status TINYINT          -- 0/1/2
is_deleted TINYINT      -- 0/1

-- 2. 频繁更新的字段
-- 每次更新都要维护 B+ 树，写入性能下降
login_count INT         -- 频繁更新

-- 3. 大字段
content TEXT            -- 索引太大，可建前缀索引
description VARCHAR(2000)

-- 4. 查询很少使用的字段
-- 索引占用空间，维护有成本，不用就是浪费

-- 5. WHERE 中用不到的字段
-- 只有出现在 WHERE/ORDER BY/GROUP BY/JOIN 中的列才考虑加索引

联合索引解决低区分度问题：

sql

-- 单独性别索引无效，但作为联合索引首列可能有效
-- 场景：查询"某部门的男性员工"
-- 索引 (gender, dept) 无效（gender 区分度低，先过滤出 50% 的行）

-- 应该把区分度高的列放前面
CREATE INDEX idx_dept_gender ON employee (dept_id, gender);
-- 先用 dept_id 过滤到少量行，再用 gender 过滤 → 有效

-- 反例：性别 + 其他字段，性别在前
CREATE INDEX idx_gender_dept ON employee (gender, dept_id);
-- 先用 gender 过滤出 50% → 再用 dept_id → 效果差

前缀索引处理大字段：

sql

-- email 字段可以只索引前 N 个字符
CREATE INDEX idx_email_prefix ON user (email(10));
-- 只索引前 10 个字符，减少索引体积

-- 但前缀索引不能做覆盖索引和 ORDER BY

追问延伸：

为什么 is_deleted = 0 查询即使有索引也不走？（90% 的行都是 is_deleted=0，走索引要回表 90%）
索引列的选择性多少以上才值得建索引？（一般建议 30% 以上，但也要看查询频率）

Q45: MySQL 的隔离级别有哪些？默认是什么？「🟢 校招」

考察点：事务隔离级别的基础知识，必考题。

参考答案：

SQL 标准定义了四种隔离级别，从低到高：

隔离级别	脏读	不可重复读	幻读	性能
读未提交（Read Uncommitted）	可能	可能	可能	最高
读已提交（Read Committed）	避免	可能	可能	高
可重复读（Repeatable Read）	避免	避免	可能*	中
串行化（Serializable）	避免	避免	避免	最低

* MySQL 的可重复读通过 MVCC + 间隙锁在很大程度上已防止幻读

三种并发问题：

sql

-- 脏读：读到其他事务未提交的数据
-- 事务A                    事务B
-- BEGIN;                  BEGIN;
-- UPDATE balance SET 100;
--                          SELECT balance; -- 读到 100（未提交）
-- ROLLBACK;               -- 100 是脏数据
--                          COMMIT;

-- 不可重复读：同一事务内两次读同一行结果不同（其他事务修改并提交）
-- 事务A                    事务B
-- BEGIN;                  BEGIN;
-- SELECT balance; -- 50
--                          UPDATE balance SET 100; COMMIT;
-- SELECT balance; -- 100  ← 不可重复读

-- 幻读：同一事务内两次范围查询结果集不同（其他事务新增/删除行）
-- 事务A                    事务B
-- BEGIN;                  BEGIN;
-- SELECT COUNT(*) WHERE age>20; -- 5
--                          INSERT INTO ... age=25; COMMIT;
-- SELECT COUNT(*) WHERE age>20; -- 6  ← 幻读

MySQL InnoDB 默认隔离级别：可重复读（Repeatable Read）

sql

-- 查看当前隔离级别
SELECT @@transaction_isolation;  -- 8.0
SELECT @@tx_isolation;           -- 5.7

-- 设置隔离级别
SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;
SET GLOBAL TRANSACTION ISOLATION LEVEL REPEATABLE READ;

-- my.cnf 配置
[mysqld]
transaction-isolation = REPEATABLE-READ

各隔离级别的实现机制：

隔离级别	实现机制
读未提交	直接读最新数据，不加锁
读已提交	MVCC（每条 SELECT 生成新 Read View）
可重复读	MVCC（事务开始时生成 Read View，后续不变）
串行化	所有读加共享锁，写加排他锁，完全串行

sql

-- MVCC 的 Read View 机制
-- 读已提交：每次 SELECT 都创建新的 Read View → 能看到最新已提交数据
-- 可重复读：事务第一次 SELECT 时创建 Read View → 整个事务用同一个 Read View

-- Read View 的判断规则：
-- 对于某行数据的某个版本：
-- 1. 版本的 trx_id < min_trx_id（最小活跃事务ID）→ 可见（事务已提交）
-- 2. 版本的 trx_id >= max_trx_id（下一个事务ID）→ 不可见（事务在 Read View 之后创建）
-- 3. min_trx_id <= trx_id < max_trx_id：
--    - trx_id 在活跃事务列表中 → 不可见（事务未提交）
--    - trx_id 不在活跃事务列表中 → 可见（事务已提交）
-- 4. 不可见时，顺着 undo log 版本链找到上一个版本继续判断

不同数据库的默认隔离级别：

MySQL InnoDB：可重复读（RR）
PostgreSQL：读已提交（RC）
Oracle：读已提交（RC）
SQL Server：读已提交（RC）

为什么 MySQL 默认 RR 而不是 RC：

历史原因：早期 binlog 只有 statement 格式，RR 下主从复制更安全
现在 binlog 支持 row 格式后，RC 也可以安全做主从复制
很多互联网公司（如阿里）将默认改为 RC，减少锁竞争

追问延伸：

RC 和 RR 在 MVCC 上有什么区别？（Read View 生成时机不同）
为什么很多大厂把隔离级别改成 RC？（减少间隙锁，提高并发）

Q46: 可重复读隔离级别下怎么防止幻读？「🟡 中级」

考察点：MVCC 与间隙锁的深入理解。

参考答案：

幻读的定义：同一事务内，两次相同的范围查询返回了不同的结果集（其他事务新增了行）。

InnoDB 在可重复读（RR）级别下，通过两种机制防止幻读：

机制1：MVCC（快照读，普通 SELECT）

sql

-- 快照读：通过 Read View + undo log 版本链实现
-- 事务开始时生成 Read View，后续所有快照读都用同一个 Read View
-- 因此即使其他事务插入了新行，也看不到（新行的 trx_id 不在 Read View 可见范围）

-- 事务A                    事务B
-- BEGIN;                  BEGIN;
-- SELECT * FROM t WHERE id > 5; -- 看到 id=6,7,8
--                          INSERT INTO t VALUES (9);
--                          COMMIT;
-- SELECT * FROM t WHERE id > 5; -- 仍然只看到 6,7,8（MVCC 快照读）
-- COMMIT;

-- ✅ MVCC 完全防止了快照读的幻读

机制2：间隙锁 + Next-Key Lock（当前读，SELECT ... FOR UPDATE / UPDATE / DELETE）

sql

-- 当前读：读取最新已提交数据，加锁防止其他事务插入
-- 事务A                            事务B
-- BEGIN;                          BEGIN;
-- SELECT * FROM t                  INSERT INTO t VALUES (9);
--   WHERE id > 5 FOR UPDATE;      -- ❌ 阻塞！（间隙锁）
-- -- 加了 Next-Key Lock          -- 等待事务A提交
-- COMMIT;
--                                  -- 事务A提交后才能插入

Next-Key Lock 的组成：

Next-Key Lock = Record Lock（行锁）+ Gap Lock（间隙锁）

假设表中有 id: 5, 10, 15, 20
SELECT * FROM t WHERE id > 5 AND id < 20 FOR UPDATE;

锁的范围：
- (5, 10]  → Next-Key Lock
- (10, 15] → Next-Key Lock
- (15, 20] → Next-Key Lock

即锁住了 (5, 20] 这个区间，其他事务无法在这个范围内插入新行

间隙锁 (5,10) 防止插入 id=6,7,8,9
记录锁 10 防止修改/删除 id=10

间隙锁的退化规则：

sql

-- 1. 唯一索引等值查询，记录存在 → 退化为 Record Lock
SELECT * FROM t WHERE id = 10 FOR UPDATE;  -- id 是主键
-- 只锁 id=10 这一行，不加间隙锁

-- 2. 唯一索引等值查询，记录不存在 → 退化为 Gap Lock
SELECT * FROM t WHERE id = 12 FOR UPDATE;  -- id=12 不存在
-- 锁住 (10, 15) 间隙，防止插入 11,12,13,14

-- 3. 非唯一索引等值查询 → Next-Key Lock + 退化
SELECT * FROM t WHERE name = '张三' FOR UPDATE;
-- 锁住 name='张三' 的记录 + 前后间隙

-- 4. 范围查询 → Next-Key Lock
SELECT * FROM t WHERE id > 10 FOR UPDATE;
-- 锁住 (10, +∞)

快照读 vs 当前读：

读类型	SQL	读到的数据	锁
快照读	普通 SELECT	Read View 快照	无锁（MVCC）
当前读	SELECT ... FOR UPDATE	最新已提交数据	Next-Key Lock
当前读	SELECT ... LOCK IN SHARE MODE	最新已提交数据	Next-Key Lock（共享）
当前读	UPDATE / DELETE	最新已提交数据	Next-Key Lock

MVCC 不能完全防止幻读的特殊场景：

sql

-- 先快照读后当前读 → 可能"幻读"
-- 事务A                    事务B
-- BEGIN;                  BEGIN;
-- SELECT * FROM t WHERE id > 5; -- 快照读，看到 6,7,8
--                          INSERT INTO t VALUES (9); COMMIT;
-- UPDATE t SET name='x' WHERE id = 9;  -- 当前读触发，更新了 id=9
-- SELECT * FROM t WHERE id > 5; -- 快照读，但现在看到了 9！
-- 原因：UPDATE 后该行事务ID变成当前事务 → Read View 可见

-- 先当前读后快照读也可能
-- 事务A                    事务B
-- BEGIN;                  BEGIN;
-- SELECT * FROM t WHERE id > 5 FOR UPDATE; -- 当前读，看到 6,7,8
--                          INSERT INTO t VALUES (9); -- 阻塞（间隙锁）
-- COMMIT;                  -- 事务A提交，事务B插入成功
--                          COMMIT;
-- BEGIN;
-- SELECT * FROM t WHERE id > 5; -- 新事务的快照读，看到 6,7,8,9

-- 结论：同一事务内混用快照读和当前读可能产生幻读
-- 完全避免：全部用当前读（FOR UPDATE）或加锁后再读

追问延伸：

间隙锁在 RC 隔离级别下还有吗？（RC 没有间隙锁，所以 RC 下当前读可能有幻读）
Next-Key Lock 在什么情况下会退化为 Record Lock？（唯一索引等值查询且记录存在时）

Q47: MySQL 两条 update 语句处理不同主键范围会不会阻塞？「🔴 高级」

考察点：行锁、间隙锁与并发写入的深入理解。

参考答案：

答案取决于隔离级别、索引类型和操作的数据范围。

场景1：RR 隔离级别，操作不同的、已存在的行（不阻塞）

sql

-- 表数据：id = 1, 5, 10, 15, 20
-- 事务A                         事务B
-- BEGIN;                       BEGIN;
-- UPDATE t SET v=1             -- 不阻塞
--   WHERE id = 5;              UPDATE t SET v=2
--                              --   WHERE id = 10;
-- COMMIT;                      COMMIT;

-- ✅ 不阻塞：id=5 和 id=10 是不同的行，各自加 Record Lock，互不影响

场景2：RR 隔离级别，操作相邻主键范围（可能阻塞）

sql

-- 表数据：id = 1, 5, 10, 15, 20
-- 事务A                         事务B
-- BEGIN;                       BEGIN;
-- UPDATE t SET v=1             UPDATE t SET v=2
--   WHERE id BETWEEN 5 AND 10;   -- ❌ 阻塞！
--                              --   WHERE id BETWEEN 10 AND 15;

-- ❌ 阻塞原因：
-- 事务A锁了 (1,5], (5,10], (10,15] 的 Next-Key Lock
-- 事务B要锁 (10,15], (15,20] → id=10 和 (10,15] 被事务A持有 → 阻塞

锁范围分析：

事务A: UPDATE ... WHERE id BETWEEN 5 AND 10
- id=5 是唯一索引等值（命中）→ Record Lock on id=5
- id BETWEEN 5 AND 10 是范围 → Next-Key Lock on (5,10], (10,15)
- 实际锁范围: (1,5] + (5,10] + (10,15) = (1, 15)

事务B: UPDATE ... WHERE id BETWEEN 10 AND 15
- 需要锁 (5,10], (10,15], (15,20]
- id=10 被事务A锁定 → 阻塞

场景3：RC 隔离级别（不阻塞，因为没有间隙锁）

sql

-- 同样的操作在 RC 下
SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;

-- 事务A                         事务B
-- BEGIN;                       BEGIN;
-- UPDATE t SET v=1             UPDATE t SET v=2
--   WHERE id BETWEEN 5 AND 10;   -- ✅ 不阻塞（只要不操作同一行）
--                              --   WHERE id BETWEEN 10 AND 15;
-- COMMIT;                      COMMIT;

-- ✅ 不阻塞：RC 没有间隙锁，只锁命中的行
-- 事务A锁 id=5, id=10
-- 事务B锁 id=10, id=15
-- 但两条语句都更新 id=10 → 事务B会阻塞在 id=10 上
-- 如果范围不重叠（如 5-8 和 10-15）则完全不阻塞

场景4：无索引的 UPDATE（全部阻塞）

sql

-- 表有 10 万行，name 列无索引
-- 事务A                         事务B
-- BEGIN;                       BEGIN;
-- UPDATE t SET v=1             UPDATE t SET v=2
--   WHERE name = '张三';         -- ❌ 阻塞！
--                              --   WHERE name = '李四';

-- ❌ 阻塞原因：
-- name 无索引 → 全表扫描 → 每行都加锁（相当于锁全表）
-- 事务A锁了全部行，事务B等待
-- 这就是为什么 UPDATE 必须走索引！

各场景汇总：

场景	隔离级别	索引	操作范围	是否阻塞	原因
不同行	RR	主键	不重叠	❌ 不阻塞	行锁互不影响
相邻范围	RR	主键	重叠	✅ 阻塞	间隙锁重叠
不相邻范围	RR	主键	不重叠	❌ 不阻塞	间隙不重叠
不同行	RC	主键	不重叠	❌ 不阻塞	无间隙锁
任意	RR/RC	无索引	任意	✅ 阻塞	全表扫描锁全表

sql

-- 查看锁等待情况
SELECT * FROM performance_schema.data_locks;  -- 8.0 查看锁信息
SELECT * FROM performance_schema.data_lock_waits;  -- 查看锁等待

-- 查看锁等待超时
SELECT @@innodb_lock_wait_timeout;  -- 默认 50 秒

-- 死锁检测
SELECT @@innodb_deadlock_detect;  -- 默认 ON
SHOW ENGINE INNODB STATUS;  -- 查看最近一次死锁信息

追问延伸：

如果两条 UPDATE 互相等待对方的锁会怎样？（死锁，InnoDB 自动检测并回滚代价小的事务）
如何减少 UPDATE 的锁范围？（走索引、缩小范围、RC 隔离级别、拆分大事务）

Q48: update 语句的完整执行过程是怎样的？「🔴 高级」

考察点：SQL 执行全链路与存储引擎交互的深入理解。

参考答案：

一条 UPDATE user SET name='张三', age=30 WHERE id=1 的完整执行过程：

Server 层：

1. 连接器
   - 验证用户名/密码，检查权限
   - 从连接池获取/创建线程
   - 维持长连接（show processlist 可见）

2. 查询缓存（MySQL 8.0 已移除）
   - 5.7 及之前：如果开启了 query_cache 且 SQL 命中缓存则直接返回
   - 表有更新时缓存失效 → 写多读少时反而降低性能 → 8.0 移除

3. 分析器（Parser）
   - 词法分析：识别 UPDATE 关键字、表名 user、列名 name/age、值 '张三'/30
   - 语法分析：检查 SQL 语法是否正确
   - 生成解析树（Parse Tree）

4. 优化器（Optimizer）
   - 选择执行计划：id=1 有主键索引 → 走聚簇索引
   - 成本估算：IO 成本 + CPU 成本
   - 生成执行计划

5. 执行器（Executor）
   - 调用存储引擎接口，按执行计划执行
   - 权限校验（update 权限）
   - 调用 InnoDB 引擎接口

InnoDB 存储引擎层：

6. 查找数据页
   - 通过 id=1 在聚簇索引 B+ 树查找
   - 先查 Buffer Pool（内存缓存）
   - 未命中 → 从磁盘读取数据页到 Buffer Pool

7. 记录 undo log（回滚日志）
   - 把修改前的旧值写入 undo log
   - 用于事务回滚和 MVCC（一致性非锁定读）
   - undo log 是逻辑日志（记录"反向操作"）
   - 旧值: name='李四', age=25 → undo log: "把 name 改回 '李四'，age 改回 25"

8. 更新 Buffer Pool 中的数据页
   - 修改内存中的数据页（标记为脏页 dirty page）
   - 不立即写磁盘（延迟写，提高性能）

9. 写 redo log（prepare 状态）
   - 把修改记录写入 redo log buffer（内存）
   - redo log 是物理日志（记录"哪个页哪个偏移量改成什么"）
   - 根据 innodb_flush_log_at_trx_commit 决定何时刷盘
   - 标记为 prepare 状态

10. 写 binlog（Server 层逻辑日志）
    - 把 SQL 的逻辑变更写入 binlog cache（内存）
    - 根据 sync_binlog 决定何时刷盘
    - binlog 用于主从复制、PITR 恢复

11. 提交事务（redo log 改为 commit 状态）
    - redo log 从 prepare → commit
    - 这就是"两阶段提交"
    - 事务对其他事务可见（释放行锁）

两阶段提交的崩溃恢复：

                  redo log          binlog          崩溃恢复
                  ────────          ──────          ────────
正常提交           prepare→commit    已写入           ✅ 提交
prepare 后崩溃     prepare          未写入           🔄 回滚（binlog 无记录）
binlog 后崩溃      prepare          已写入           ✅ 提交（binlog 完整）

sql

-- 相关参数
SHOW VARIABLES LIKE 'innodb_flush_log_at_trx_commit';
-- 0: 每秒刷盘（可能丢1秒数据）
-- 1: 每次提交刷盘（默认，最安全）
-- 2: 每次提交写 OS Buffer，每秒刷盘

SHOW VARIABLES LIKE 'sync_binlog';
-- 0: 由 OS 决定刷盘时机
-- 1: 每次提交刷盘（默认，最安全）
-- N: 每 N 次提交刷盘

各日志的对比：

特性	redo log	undo log	binlog
所属层	InnoDB 引擎层	InnoDB 引擎层	Server 层
日志类型	物理日志	逻辑日志	逻辑日志
作用	崩溃恢复（持久性）	回滚 + MVCC	主从复制 + PITR
写入方式	循环写（覆盖）	随机写	追加写
内容	页的物理修改	反向操作	SQL 逻辑变更
大小	固定（配置）	按需增长	无限增长

执行流程图：

连接器 → 分析器 → 优化器 → 执行器
                                ↓
                        InnoDB 引擎
                                ↓
                   ┌── 1. 查找数据页（Buffer Pool）
                   ├── 2. 写 undo log
                   ├── 3. 更新 Buffer Pool（脏页）
                   ├── 4. 写 redo log（prepare）
                   ├── 5. 写 binlog
                   └── 6. redo log（commit）
                                ↓
                        事务提交完成
                                ↓
                   异步：脏页刷盘（由 checkpoint 控制）

追问延伸：

如果 redo log 写满了会怎样？（强制刷脏页 + 推进 checkpoint，暂停写入）
为什么不用 redo log 直接替代 binlog？（redo log 是循环写无法保留历史；redo log 是物理日志无法跨引擎/跨版本恢复）

Q49: MySQL 是如何保障数据不丢失的？redolog 和 binlog 两阶段提交？「🔴 高级」

考察点：持久性保障机制与两阶段提交原理。

参考答案：

MySQL 保障数据不丢失（持久性，ACID 中的 D）的核心机制：

1. WAL 机制（Write-Ahead Logging）

先写日志，后写数据：
- 修改数据时，不直接写数据页到磁盘
- 先把修改记录写入 redo log（顺序写，极快）
- 数据页（脏页）留在 Buffer Pool 中，异步刷盘
- 崩溃恢复时，重放 redo log 恢复数据

优势：
- 顺序写远快于随机写（磁盘性能差距 100 倍+）
- 改一行（几十字节）不需要刷整个 16KB 数据页
- 合并多个修改，批量刷盘

2. redo log 的刷盘策略

sql

SHOW VARIABLES LIKE 'innodb_flush_log_at_trx_commit';

值	行为	崩溃丢失风险	性能
0	每秒刷盘一次	最多丢 1 秒	最高
1	每次事务提交都刷盘	不丢	最低（默认）
2	每次提交写 OS Buffer，每秒刷盘	OS 崩溃丢数据	中

3. binlog 的刷盘策略

sql

SHOW VARIABLES LIKE 'sync_binlog';

值	行为	崩溃丢失风险	性能
0	由 OS 决定刷盘	OS 崩溃丢数据	最高
1	每次事务提交都刷盘	不丢	最低（默认）
N	每 N 次提交刷盘	最多丢 N 个事务	中

4. 两阶段提交（2PC）保证 redo log 与 binlog 一致

事务提交流程：

阶段一（Prepare）：
  1. InnoDB 写 redo log（记录修改），标记为 PREPARE 状态
  2. redo log 刷盘（根据 innodb_flush_log_at_trx_commit）

阶段二（Commit）：
  3. Server 层写 binlog，刷盘（根据 sync_binlog）
  4. InnoDB 将 redo log 标记为 COMMIT 状态
  5. 事务提交成功，释放锁

         redo log              binlog
         ────────              ──────
Step1:   PREPARE 写入
Step2:                         写入 binlog
Step3:   COMMIT 写入

为什么需要两阶段提交：

场景：不使用两阶段提交，可能出现 redo log 和 binlog 不一致

假设先写 redo log 后写 binlog：
1. 写 redo log 成功（记录 id=1, name='张三'）
2. 写 binlog 之前崩溃
3. 恢复后：redo log 有记录 → 主库 name='张三'
4. 但 binlog 无记录 → 从库不同步 → 主从数据不一致！

假设先写 binlog 后写 redo log：
1. 写 binlog 成功（记录 UPDATE id=1 SET name='张三'）
2. 写 redo log 之前崩溃
3. 恢复后：redo log 无记录 → 主库 name 未变
4. 但 binlog 有记录 → 从库执行了 → 从库 name='张三' → 主从不一致！

两阶段提交解决：
- redo log 先写（prepare），binlog 后写，最后 redo log commit
- 崩溃恢复时检查 redo log 的状态：
  - COMMIT 状态：已提交，无需处理
  - PREPARE 状态：检查 binlog 是否完整
    - binlog 完整（有完整事务结尾）→ 提交（说明 binlog 已写完，只是 commit 标记没写）
    - binlog 不完整 → 回滚（说明 binlog 没写完，需要回滚保持一致）

崩溃恢复的具体逻辑：

扫描 redo log：
  for each redo log entry:
    if redo log 状态 == COMMIT:
      重放该 redo log（数据已确认提交）
    elif redo log 状态 == PREPARE:
      查找对应 binlog：
        if binlog 中有完整事务记录:
          提交该事务（重放 redo log）
        else:
          回滚该事务（用 undo log 回滚）

5. Double Write Buffer（双写缓冲）防止页撕裂

问题：数据页 16KB，操作系统页 4KB
  - 写数据页时需要 4 次 IO（4×4KB）
  - 如果写到第 2 次时断电 → 页撕裂（page corruption）
  - redo log 无法修复物理损坏的页

解决：Double Write
  1. 先把脏页写入 double write buffer（连续磁盘区域，2MB）
  2. 再把脏页写入各自的数据文件位置
  3. 崩溃恢复：
     - 页损坏 → 从 double write buffer 恢复完整页
     - 页完好 → 重放 redo log

  SHOW VARIABLES LIKE 'innodb_doublewrite';  -- 默认 ON

6. 完整的数据安全配置

sql

-- 最高安全级别（双1配置）
SET GLOBAL innodb_flush_log_at_trx_commit = 1;  -- redo log 每次提交刷盘
SET GLOBAL sync_binlog = 1;                     -- binlog 每次提交刷盘
-- 代价：每次事务提交都要 fsync，性能下降

-- 性能与安全的平衡（互联网公司常用）
SET GLOBAL innodb_flush_log_at_trx_commit = 2;  -- 写 OS Buffer，每秒刷盘
SET GLOBAL sync_binlog = 100;                   -- 每 100 次提交刷盘
-- 代价：操作系统崩溃可能丢失少量数据

追问延伸：

双 1 配置为什么性能差？（每次提交都要 fsync 系统调用，磁盘 IO 瓶颈）
什么是组提交（Group Commit）？（多个事务的 redo log/binlog 合并一次刷盘，减少 IO）

Q50: 查询速度很慢有哪些解决方案？EXPLAIN 怎么看？「🟡 中级」

考察点：慢查询排查与优化的实战能力。

参考答案：

慢查询排查流程：

1. 开启慢查询日志 → 2. 定位慢 SQL → 3. EXPLAIN 分析 → 4. 针对性优化 → 5. 验证效果

Step1：开启慢查询日志

sql

-- 查看慢查询配置
SHOW VARIABLES LIKE 'slow_query_log%';
SHOW VARIABLES LIKE 'long_query_time';

-- 开启慢查询日志
SET GLOBAL slow_query_log = ON;
SET GLOBAL long_query_time = 1;  -- 超过 1 秒记录
SET GLOBAL slow_query_log_file = '/var/log/mysql/slow.log';

-- my.cnf 永久配置
[mysqld]
slow_query_log = 1
long_query_time = 1
log_queries_not_using_indexes = 1  -- 记录不走索引的查询

-- 分析慢查询日志
-- mysqldumpslow 工具
mysqldumpslow -s t -t 10 /var/log/mysql/slow.log
-- -s t 按总时间排序，-t 10 取前10条

Step2：EXPLAIN 执行计划详解

sql

EXPLAIN SELECT * FROM orders WHERE user_id = 100 AND status = 'paid';

EXPLAIN 输出字段：

字段	含义	重点关注
id	查询序号	id 大的先执行，相同 id 从上往下
select_type	查询类型	SIMPLE/PRIMARY/SUBQUERY/DERIVED
table	表名
type	访问类型	最重要，性能从好到差
possible_keys	可能用到的索引
key	实际使用的索引	为空表示没走索引
key_len	索引使用长度	判断联合索引用了几个列
ref	索引比较的列	const/列名
rows	预估扫描行数	越小越好
filtered	过滤比例	100 最好，越小说明扫描多但结果少
Extra	附加信息	重点关注

type 字段性能排序（从好到差）：

type	含义	性能
system	表只有一行	最好
const	主键/唯一索引等值查询	极好
eq_ref	JOIN 时被驱动表用主键/唯一索引	极好
ref	非唯一索引等值查询	好
range	索引范围查询	较好
index	扫描整个索引树	一般
ALL	全表扫描	最差

sql

-- const：主键等值查询
EXPLAIN SELECT * FROM orders WHERE id = 1;  -- type=const

-- ref：非唯一索引等值查询
EXPLAIN SELECT * FROM orders WHERE user_id = 100;  -- type=ref（user_id有普通索引）

-- range：索引范围查询
EXPLAIN SELECT * FROM orders WHERE id > 100 AND id < 200;  -- type=range

-- ALL：全表扫描
EXPLAIN SELECT * FROM orders WHERE non_indexed_col = 'xxx';  -- type=ALL

Extra 字段关键信息：

Extra 值	含义	是否需优化
Using index	覆盖索引，无需回表	✅ 好
Using where	Server 层过滤	一般，看情况
Using index condition	索引下推（ICP）	较好
Using filesort	额外排序	⚠️ 需优化
Using temporary	临时表	⚠️ 需优化
Using join buffer	JOIN 无索引用缓存	⚠️ 需优化

Step3：常见慢查询原因与优化方案

sql

-- 1. 全表扫描（无索引或索引失效）
-- 问题：type=ALL，rows=百万级
-- 优化：添加合适索引
CREATE INDEX idx_user_status ON orders(user_id, status);

-- 2. 深分页问题
-- 问题：LIMIT 1000000, 10 需要扫描 100 万行
SELECT * FROM orders ORDER BY id LIMIT 1000000, 10;
-- 优化：游标分页（记住上一页的最大 id）
SELECT * FROM orders WHERE id > 1000000 ORDER BY id LIMIT 10;

-- 3. SELECT * 导致回表
-- 问题：查所有列，二级索引需要回表
SELECT * FROM orders WHERE user_id = 100;
-- 优化：只查需要的列，利用覆盖索引
SELECT id, user_id, status FROM orders WHERE user_id = 100;

-- 4. filesort（ORDER BY 无索引）
-- 问题：Extra=Using filesort
SELECT * FROM orders ORDER BY create_time DESC LIMIT 10;
-- 优化：给排序列加索引
CREATE INDEX idx_create_time ON orders(create_time);

-- 5. 临时表（GROUP BY 无索引）
-- 问题：Extra=Using temporary
SELECT user_id, COUNT(*) FROM orders GROUP BY user_id;
-- 优化：给分组列加索引
CREATE INDEX idx_user_id ON orders(user_id);

-- 6. 子查询效率低
-- 问题：子查询可能产生临时表
SELECT * FROM orders WHERE user_id IN (SELECT id FROM users WHERE vip=1);
-- 优化：改用 JOIN
SELECT o.* FROM orders o JOIN users u ON o.user_id = u.id WHERE u.vip=1;

-- 7. LIKE '%xxx' 全表扫描
SELECT * FROM users WHERE name LIKE '%张三%';
-- 优化：全文索引或 ES 搜索
ALTER TABLE users ADD FULLTEXT INDEX ft_name(name);
SELECT * FROM users WHERE MATCH(name) AGAINST('张三' IN BOOLEAN MODE);

-- 8. 大表 COUNT(*)
SELECT COUNT(*) FROM orders WHERE status='paid';
-- 优化：汇总表、缓存计数、近似值
-- Redis 缓存计数 / 定时统计表

Step4：优化方案总结

优化方向	具体手段
索引优化	加索引、覆盖索引、联合索引顺序、避免索引失效
SQL 改写	避免 SELECT *、子查询改 JOIN、深分页改游标
表结构	大表拆分、冗余字段减少 JOIN、合理数据类型
架构优化	读写分离、分库分表、加缓存（Redis）
配置优化	调大 Buffer Pool、合理连接池

sql

-- EXPLAIN 分析完整示例
EXPLAIN
SELECT o.id, o.amount, u.name
FROM orders o
JOIN users u ON o.user_id = u.id
WHERE o.status = 'paid' AND o.create_time > '2024-01-01'
ORDER BY o.create_time DESC
LIMIT 20;

-- 理想结果：
-- o 表：type=ref（用 idx_status_create_time），rows 少
-- u 表：type=eq_ref（主键关联），rows=1
-- Extra: Using index（覆盖索引），无 filesort，无 temporary

追问延伸：

EXPLAIN ANALYZE（8.0）和 EXPLAIN 有什么区别？（ANALYZE 实际执行并显示真实耗时和行数）
慢查询优化后怎么验证效果？（对比 EXPLAIN 的 rows/type/Extra，用 EXPLAIN ANALYZE 看实际耗时）

Q51: Text 数据类型可以无限大吗？IP 地址如何在数据库里存储？「🟡 中级」

考察点：数据类型选型和存储优化。

参考答案：

Text 类型：

类型	最大长度	适用场景
TINYTEXT	255 字节	短文本
TEXT	65,535 字节（~64KB）	文章摘要
MEDIUMTEXT	16,777,215 字节（~16MB）	文章正文
LONGTEXT	4,294,967,295 字节（~4GB）	超大文本

Text 不是无限大，LONGTEXT 最大 4GB
Text 字段不存储在行内，溢出存储到溢出页（Uncompressed Blob/Off-Page）
大量 Text 字段会导致页分裂和 IO 增加

IP 地址存储方案：

sql

-- 差：用 VARCHAR(15) 存储 IP，浪费空间，查询慢
ip VARCHAR(15)  -- "192.168.1.100"

-- 好：用 INT UNSIGNED 存储，4 字节
ip INT UNSIGNED  -- 3232235876

-- 转换函数
SELECT INET_ATON('192.168.1.100');  -- 字符串→整数: 3232235876
SELECT INET_NTOA(3232235876);        -- 整数→字符串: 192.168.1.100

-- IPv6 用 BIGINT 或 BINARY(16)
SELECT INET6_ATON('::1');            -- 返回 BINARY(16)
SELECT INET6_NTOA(UNHEX('00000000000000000000000000000001'));

存储方式	空间	查询效率	索引
VARCHAR(15)	15 字节	慢	支持但效率低
INT UNSIGNED	4 字节	快	支持且高效
BINARY(16) (IPv6)	16 字节	快	支持

追问延伸：

为什么 TEXT 字段不适合做索引？（溢出存储，索引页不包含实际数据）
JSON 类型在 MySQL 中怎么存储？（MySQL 8.0 用二进制 JSON 格式，支持 JSON 函数查询）

Q52: MySQL 的外键约束是什么？什么时候用？「🟢 校招/初级」

考察点：外键的作用和工程实践。

参考答案：

外键约束：用于维护表间数据一致性，保证从表的引用值必须在主表中存在。

sql

-- 创建外键
CREATE TABLE orders (
    id INT PRIMARY KEY,
    user_id INT,
    FOREIGN KEY (user_id) REFERENCES users(id)
        ON DELETE CASCADE      -- 主表删除时，从表级联删除
        ON UPDATE CASCADE      -- 主表更新时，从表级联更新
);

外键约束的行为：

RESTRICT（默认）：主表有引用时禁止删除/更新
CASCADE：主表删除/更新时，从表级联删除/更新
SET NULL：主表删除时，从表设为 NULL
NO ACTION：同 RESTRICT

是否应该使用外键：

维度	使用外键	不使用外键
数据一致性	DB 保证	应用层保证
性能	插入/删除需检查约束，有开销	无额外开销
扩展性	分库分表困难	容易拆分
并发	可能死锁	无死锁风险
适用场景	单体应用、强一致	微服务、高并发

互联网公司通常不用外键：

性能瓶颈：高并发下外键检查成为瓶颈
分库分表：跨库外键无法实现
微服务：数据一致性由应用层 + 消息队列保证
灵活性：表结构调整不受外键约束限制

追问延伸：

不用外键怎么保证数据一致性？（应用层校验 + 事务 + 补偿机制）
外键和触发器哪个性能更好？（外键在 DB 层优化，通常比触发器快）

Q53: 表的主键用自增 ID 还是 UUID？为什么？「🟡 中级」

考察点：主键选型的工程考量。

参考答案：

维度	自增 ID	UUID
存储	BIGINT 8 字节	VARCHAR(36) 或 BINARY(16)
插入性能	顺序追加，页分裂少	随机写入，页分裂多
索引效率	B+ 树叶子节点顺序增长	B+ 树随机插入，频繁页分裂
可预测性	可猜（安全风险）	不可猜
唯一性	单机唯一	全局唯一
扩展性	分库分表冲突	天然分布式
排序	按时间有序	无序

自增 ID 的优势：

InnoDB 聚簇索引按主键有序，自增 ID 是顺序追加，不会页分裂
插入性能高，缓存友好
主键索引和二级索引都更紧凑

UUID 的问题：

随机值导致 B+ 树频繁页分裂和页移动
插入性能差（尤其是数据量大时）
存储空间大，二级索引更大（因为二级索引存储主键值）

工程实践建议：

sql

-- 1. 单机优先自增 ID
id BIGINT AUTO_INCREMENT PRIMARY KEY

-- 2. 分布式用 Snowflake（趋势递增）
id BIGINT PRIMARY KEY  -- 应用层生成 Snowflake ID

-- 3. UUID 变体：UUID + 时间戳前缀（趋势递增）
-- 如 ULID: 时间戳 + 随机数

-- 4. 如果必须用 UUID，用 BINARY(16) 而非 VARCHAR(36)
id BINARY(16) PRIMARY KEY

追问延伸：

Snowflake ID 怎么生成的？（时间戳 + 机器ID + 序列号）
为什么 UUID 做主键时性能下降明显？（B+ 树随机插入导致页分裂）

Q54: B+ 树的叶子节点链表是单向还是双向？查询数据到了叶子节点后怎么查找？「🟡 中级」

考察点：B+ 树数据结构的底层细节。

参考答案：

InnoDB 的 B+ 树叶子节点是双向链表：

叶子节点结构：
[Page 1] ←→ [Page 2] ←→ [Page 3] ←→ [Page 4]
  ↓             ↓             ↓             ↓
(1,3,5,7)    (9,11,13,15) (17,19,21,23) (25,27,29,31)

每个叶子页（Page）内部是有序数组，页与页之间通过双向链表连接。

到了叶子节点后的查找过程：

二分查找：在叶子页内部的有序数组中二分查找目标记录
范围查询：找到起始位置后，沿链表向后遍历（范围扫描）
双向遍历：双向链表支持向前和向后遍历

sql

-- 范围查询：B+ 树的优势
SELECT * FROM users WHERE id BETWEEN 10 AND 50;
-- 1. 从根节点找到叶子页
-- 2. 在叶子页内二分找到 id=10
-- 3. 沿链表向右遍历直到 id=50

为什么用双向链表：

范围查询：BETWEEN、>、< 需要双向遍历
排序：ORDER BY ASC/DESC 需要正向/反向遍历
删除：删除节点时需要更新前后指针

与 B 树的区别：

B 树：数据在所有节点（内部+叶子），不需要链表
B+ 树：数据只在叶子节点，叶子通过链表连接 → 范围查询更快

追问延伸：

为什么不用单向链表？（反向遍历需要，如 ORDER BY DESC）
B+ 树的非叶子节点存什么？（只存索引键 + 子节点指针，不存数据）

Q55: 索引已经建好了，再插入一条数据，索引会有哪些变化？「🔴 高级」

考察点：索引维护的底层原理。

参考答案：

插入数据时，B+ 树索引的变化：

情况1：叶子页有空间：

在叶子页内找到插入位置
移动后续记录，插入新记录
页内操作，不涉及页分裂

情况2：叶子页满了 → 页分裂：

创建新页
将原页的一半数据移到新页
在父节点插入新页的指针和键值
更新双向链表指针

分裂前：[Page A: 1,3,5,7,9]（已满）
插入 4 → 页分裂
分裂后：[Page A: 1,3,4] ←→ [Page B: 5,7,9]
父节点新增指向 Page B 的指针

情况3：非叶子页满了 → 递归分裂：

页分裂可能向上传播到根节点
根节点分裂 → B+ 树高度 +1

性能影响：

页分裂：IO + 数据移动，性能开销大
随机插入（如 UUID 主键）→ 频繁页分裂
顺序插入（如自增主键）→ 只在最后追加，极少分裂

自增主键为什么快：

自增ID：总是在最右侧追加
[Page: 1,3,5,7] → 插入 9 → [Page: 1,3,5,7,9]（可能不分裂）
                                          ↑ 追加位置

UUID：随机位置插入
[Page: 1,5,9,13] → 插入 7 → 需要移动 9,13 → 可能触发页分裂
                     ↑ 插入位置

追问延伸：

页分裂时锁的是什么？（InnoDB 加排他锁，可能影响并发）
怎么减少页分裂？（用自增主键、定期 OPTIMIZE TABLE 重组表）

Q56: 了解过前缀索引吗？自适应 Hash 索引是什么？「🟡 中级」

考察点：索引优化进阶。

参考答案：

前缀索引：

对长字符串字段（如 VARCHAR(255)），只索引前 N 个字符
减少索引存储空间，提高索引效率

sql

-- 全列索引：索引大，效率可能低
ALTER TABLE users ADD INDEX idx_email(email(255));

-- 前缀索引：只索引前 10 个字符
ALTER TABLE users ADD INDEX idx_email_prefix(email(10));

前缀长度选择：

sql

-- 计算选择性：不同前缀值 / 总行数
SELECT
  COUNT(DISTINCT LEFT(email, 5)) / COUNT(*) AS sel5,
  COUNT(DISTINCT LEFT(email, 10)) / COUNT(*) AS sel10,
  COUNT(DISTINCT LEFT(email, 15)) / COUNT(*) AS sel15
FROM users;
-- 选择性接近 1 的最小前缀长度

前缀索引的局限：

不能用于覆盖索引（前缀索引不存储完整值，需要回表）
不能用于 ORDER BY / GROUP BY
不能用于 WHERE col IS NULL

自适应 Hash 索引（Adaptive Hash Index, AHI）：

InnoDB 自动监控热点查询，对频繁访问的索引页建立 Hash 表
由 InnoDB 自动维护，无需手动干预
将 B+ 树的查找从 O(log n) 降为 O(1)

B+ 树查找：根→内部节点→...→叶子页→二分查找 = O(log n)
AHI 查找：Hash(索引键) → 直接定位到记录 = O(1)

AHI 的工作原理：

监控查询模式，发现某个索引页被高频访问
自动在内存中建立 Hash 表（innodb_adaptive_hash_index）
后续查询直接走 Hash 查找，跳过 B+ 树遍历

AHI 的注意事项：

高并发下 Hash 表的互斥锁可能成为瓶颈（btr0sea.c 的 latch）
极端情况下关闭 AHI 反而性能更好：SET GLOBAL innodb_adaptive_hash_index = OFF

追问延伸：

前缀索引为什么不能做覆盖索引？（索引只存部分值，需要回表读完整值）
AHI 什么情况下反而拖慢性能？（高并发 + 大量不同索引键，Hash 表竞争激烈）

Q57: MySQL 的 count(*) 为什么慢？怎么优化？「🟡 中级」

考察点：count 优化经典面试题。

参考答案：

为什么 count(*) 慢：

InnoDB 的 count(*) 需要遍历聚簇索引（或最小的二级索引），逐行计数：

MVCC：不同事务看到的行数不同，不能缓存一个全局计数
InnoDB 不维护表的行数（MyISAM 维护，所以 MyISAM 的 count(*) 是 O(1)）

sql

-- 慢：需要遍历
SELECT COUNT(*) FROM orders;  -- 大表可能几秒到几十秒

-- 快：MyISAM 维护了元数据
SELECT COUNT(*) FROM myisam_table;  -- O(1)

count 的性能差异：

count(*) ≈ count(1) > count(主键) > count(字段)

count(*) 和 count(1)：不取值，直接计数，最快
count(主键)：取主键值判断非 NULL
count(字段)：取字段值判断非 NULL，非索引字段更慢

优化方案：

sql

-- 1. 用 show table status 估算（不精确，但快）
SHOW TABLE STATUS LIKE 'orders';  -- Rows 字段是近似值

-- 2. 用信息schema估算
SELECT table_rows FROM information_schema.tables
WHERE table_name = 'orders';

-- 3. Redis 缓存计数（精确但有延迟）
-- 写操作时 INCR/DECR Redis 计数器

-- 4. 业务允许的话用 max(id) 估算
SELECT MAX(id) FROM orders;  -- 假设 ID 连续自增

-- 5. 汇总表（精确且快）
CREATE TABLE count_summary (
    table_name VARCHAR(50),
    row_count BIGINT,
    update_time DATETIME
);
-- 定时更新汇总表

-- 6. 用Buffer Pool 充足时，走二级索引扫描（更小的索引）
SELECT COUNT(*) FROM orders USE INDEX(idx_secondary);

追问延伸：

为什么 count(*) 比 count(字段) 快？（* 不取值，字段需要判断 NULL）
为什么 MyISAM 的 count(*) 是 O(1)？（MyISAM 维护了表的行数元数据）

Q58: 深度分页问题怎么解决？「🟡 中级」

考察点：分页优化经典题。

参考答案：

问题：LIMIT offset, size 当 offset 很大时，MySQL 需要扫描 offset+size 行再丢弃前 offset 行。

sql

-- 慢：需要扫描 1000020 行，丢弃前 1000000 行
SELECT * FROM orders ORDER BY id LIMIT 1000000, 20;

原因：MySQL 需要先找到第 offset 行（通过扫描），然后取 size 行。即使有索引，也需要回表读取完整行数据再丢弃。

解决方案：

sql

-- 方案1：子查询延迟关联（推荐）
SELECT * FROM orders o
INNER JOIN (
    SELECT id FROM orders ORDER BY id LIMIT 1000000, 20
) t ON o.id = t.id;
-- 子查询走覆盖索引，不回表；最后只对 20 条回表

-- 方案2：游标分页（记住上一页的最大 ID）
-- 第一页
SELECT * FROM orders WHERE id > 0 ORDER BY id LIMIT 20;
-- 第二页（记住上一页最后 ID=20）
SELECT * FROM orders WHERE id > 20 ORDER BY id LIMIT 20;
-- 第 N 页（记住上一页最后 ID=N*20）
SELECT * FROM orders WHERE id > 1000000 ORDER BY id LIMIT 20;
-- O(20) 而非 O(1000020)

-- 方案3：禁止跳页（只允许上一页/下一页）
-- 产品层面放弃"跳到第 5000 页"功能

方案对比：

方案	性能	精确性	适用场景
子查询延迟关联	好	精确	支持跳页
游标分页	最好	精确	只支持翻页
show table status	快	近似	不需精确
缓存结果	快	可能不一致	分页结果不变

追问延伸：

游标分页为什么不能用 WHERE id > last_id？（可以的，这是最推荐的方式）
深度分页在 ES 中怎么解决？（Search After，类似游标分页）

Q59: 串行化隔离级别是通过什么实现的？一条 update 是不是原子性的？「🟡 中级」

考察点：隔离级别底层实现和事务原子性。

参考答案：

串行化（SERIALIZABLE）的实现：

串行化通过对所有读操作加共享锁实现：

普通 SELECT 变成 LOCK IN SHARE MODE（加 S 锁）
写操作加 X 锁
读写互斥，事务串行执行

sql

-- SERIALIZABLE 下
SET SESSION TRANSACTION ISOLATION LEVEL SERIALIZABLE;

-- 事务 A
BEGIN;
SELECT * FROM users WHERE id = 1;  -- 加 S 锁
-- 事务 B
BEGIN;
UPDATE users SET name = 'x' WHERE id = 1;  -- 需要X锁，被阻塞

-- 对比：RR 级别下普通 SELECT 不加锁（快照读）

隔离级别	读实现	幻读
READ UNCOMMITTED	无锁，读未提交	有
READ COMMITTED	MVCC（每条语句新快照）	有
REPEATABLE READ	MVCC（事务开始时快照）	有（快照读）/无（当前读+Gap锁）
SERIALIZABLE	加共享锁	无

一条 update 是不是原子性的：

是的，单条 SQL 语句是原子性的（InnoDB 保证）：

UPDATE 语句在执行前自动 BEGIN
执行成功后自动 COMMIT
执行失败自动 ROLLBACK

sql

-- 原子性示例
UPDATE account SET balance = balance - 100 WHERE id = 1;
-- 这条语句要么完全成功，要么完全回滚
-- 不会出现"扣了100但没记录"的情况

但多条语句组成的事务不自动保证原子性，需要显式控制：

sql

BEGIN;
UPDATE account SET balance = balance - 100 WHERE id = 1;  -- 扣款
UPDATE account SET balance = balance + 100 WHERE id = 2;  -- 入账
-- 如果第二条失败，需要 ROLLBACK 才能保证原子性
COMMIT;

追问延伸：

串行化级别为什么性能差？（读写互斥，并发度极低）
autocommit=0 和 autocommit=1 有什么区别？（0=需要手动 COMMIT，1=每条语句自动提交）

Q60: 滥用事务有什么弊端？一个事务里特别多 SQL 有什么问题？「🟡 中级」

考察点：事务使用最佳实践。

参考答案：

滥用事务的常见问题：

1. 长事务导致锁竞争：

事务持有锁的时间变长，其他事务等待
高并发下可能死锁

java

// 差：事务范围过大，包含网络调用
@Transactional
public void process() {
    updateOrder();         // DB 操作
    callRemoteAPI();       // 远程调用，可能超时
    updateInventory();    // DB 操作
    sendEmail();           // 发邮件
}
// 远程调用超时 → 事务一直持有锁 → 其他事务阻塞

2. 长事务导致 MVCC 版本链过长：

InnoDB 的 MVCC 需要保留旧版本数据（undo log）
长事务导致旧版本无法回收 → undo 表空间膨胀
查询需要遍历版本链 → 性能下降

3. 事务过大导致 binlog 积压：

事务提交时才写 binlog
大事务的 binlog 量很大，主从同步延迟增大

4. 回滚成本高：

大事务回滚需要执行大量 undo 操作
回滚时间可能比执行时间还长

最佳实践：

java

// 好：事务只包含必要的 DB 操作
@Transactional
public void transfer() {
    accountDao.debit(fromId, amount);   // DB
    accountDao.credit(toId, amount);    // DB
}
// 远程调用、发邮件等放在事务外

// 好：批量操作分批提交
for (List<User> batch : Lists.partition(users, 500)) {
    batchUpdate(batch);  // 每 500 条一个事务
}

原则	说明
事务尽量短	只包含必要的 DB 操作
事务不含远程调用	RPC/HTTP/发邮件放事务外
批量分批提交	大批量操作分批，每批一个事务
避免大事务	单事务建议 < 5 秒、< 1000 条

追问延伸：

怎么排查长事务？（information_schema.innodb_trx 查看运行中的事务）
事务超时怎么设置？（@Transactional(timeout=30) 或 innodb_lock_wait_timeout）

Q61: MySQL 的两次写（Double Write Buffer）了解吗？「🔴 高级」

考察点：MySQL 数据安全的高级机制。

参考答案：

Double Write Buffer（两次写）：防止页断裂（partial page write）导致数据损坏。

问题背景：

InnoDB 页大小 16KB，OS IO 最小单位 4KB
如果写 16KB 时只写了一部分（如 8KB）就宕机 → 页损坏
此时 redo log 也无法恢复（redo log 是物理日志，基于页的修改，页本身坏了就无法重放）

Double Write 流程：

1. 先将脏页写入 Double Write Buffer（内存中的连续 2MB 区域）
2. 将 Double Write Buffer 的数据顺序写入磁盘共享表空间的 2MB 连续区域
3. 再将脏页写入各自的表空间文件

正常流程：内存脏页 → Double Write Buffer → 磁盘共享表空间 → 磁盘各表空间
崩溃恢复：检查各表空间页是否完整
  → 完整：直接使用
  → 不完整：从 Double Write 磁盘区域恢复完整页 → 用 redo log 重放

Double Write 的代价：

每次写操作需要写两次 → 写放大
但 Double Write 是顺序写（共享表空间），性能影响可接受

是否可以关闭：

sql

-- 关闭 Double Write（不推荐，除非有文件系统保证）
SET GLOBAL innodb_doublewrite = OFF;

-- 用了 ZFS / btrfs 等原子写文件系统可以关闭
-- 用了 RDBMS + 快照 + 硬件 RAID 也可以考虑

追问延伸：

Redo log 为什么不能恢复页断裂？（redo log 基于完整页的物理修改，页坏了无法重放）
Change Buffer 是什么？（对非唯一索引的修改先缓存，减少随机 IO）

Q62: InnoDB 行格式有哪些？varchar(n) 中 n 最大取值为多少？「🔴 高级」

考察点：InnoDB 存储格式的底层理解。

参考答案：

InnoDB 行格式：

格式	MySQL 版本	特点
REDUNDANT	< 5.0	旧格式，冗余存储
COMPACT	5.0+	紧凑存储，变长字段长度用 1~2 字节
DYNAMIC	5.7+（默认）	行溢出时只存 20 字节指针，数据全在溢出页
COMPRESSED	5.7+	同 DYNAMIC + 压缩

DYNAMIC 格式（默认）的行溢出处理：

当行数据超过页大小的一半（约 8KB）时，溢出存储
行内只存 20 字节的溢出页指针
大字段（TEXT/BLOB/VARCHAR）的完整数据在溢出页

行内存储：
[记录头][列1值][列2值]...[大字段列: 20字节指针]
                                        ↓
溢出页：[数据部分1] → [数据部分2] → ... (单向链表)

varchar(n) 最大取值：

n 的单位是字符（非字节），最大值取决于：

行的总大小不能超过 65535 字节（InnoDB 限制）
字符集（utf8mb4 一个字符最多 4 字节）
其他列占用的空间

sql

-- utf8mb4 下 varchar 的极限
-- 65535 / 4 = 16383（但还要减去其他开销）

-- 单列最大值
CREATE TABLE test (
    a VARCHAR(16383) CHARSET utf8mb4  -- 可能超出限制
);
-- ERROR 1118 (42000): Row size too large

-- 实际最大值约 16382（减去变长长度记录等开销）
-- 如果行中有其他列，n 要相应减小

-- 多列情况
CREATE TABLE test (
    a VARCHAR(10000) CHARSET utf8mb4,  -- 40000 字节
    b VARCHAR(10000) CHARSET utf8mb4   -- 40000 字节
    -- 80000 > 65535 → 报错
);

追问延伸：

COMPACT 和 DYNAMIC 的区别？（DYNAMIC 行内只存指针，COMPACT 行内存 768 字节前缀）
varchar 长度记录在哪？（行头部的变长字段长度列表）

Q63: MySQL 是怎么加行级锁的？Insert 语句怎么加锁？「🔴 高级」

考察点：锁的加锁规则和加锁分析。

参考答案：

InnoDB 加锁原则：

默认加的是 Next-Key Lock（Gap Lock + Record Lock）
唯一索引等值查询，命中记录 → 退化为 Record Lock
唯一索引等值查询，未命中 → 退化为 Gap Lock
非唯一索引等值查询 → Next-Key Lock + 下一个 Gap Lock
范围查询 → 对范围内每个记录加 Next-Key Lock

等值查询加锁示例：

sql

-- 表数据：id = [5, 10, 15, 20, 25]（主键）

-- 唯一索引等值命中
SELECT * FROM t WHERE id = 10 FOR UPDATE;
-- 加 Record Lock：id=10

-- 唯一索引等值未命中
SELECT * FROM t WHERE id = 12 FOR UPDATE;
-- 加 Gap Lock：(10, 15)

-- 范围查询
SELECT * FROM t WHERE id >= 10 AND id < 20 FOR UPDATE;
-- 加 Next-Key Lock：[10,15], [15,20)
-- 即 id=10 Record Lock + (10,15] Gap + 15 Record + (15,20) Gap

Insert 语句的加锁：

Insert 语句不需要显式加锁，但会做插入意向锁检查：

检查插入位置是否有 Gap Lock → 有则等待（插入意向锁）
检查唯一键冲突 → 有则加 S 锁检查
插入成功后加 Record Lock（隐式的，事务期间持有）

sql

-- 事务 A
BEGIN;
SELECT * FROM t WHERE id > 10 AND id < 20 FOR UPDATE;
-- 加 Gap Lock: (10, 20)

-- 事务 B
INSERT INTO t VALUES (15);  -- 在 Gap 范围内 → 阻塞
-- 需要等待事务 A 提交

-- 事务 B
INSERT INTO t VALUES (25);  -- 在 Gap 范围外 → 不阻塞

唯一键冲突的加锁：

sql

-- 表有唯一索引 idx_name
INSERT INTO t(name) VALUES('Alice');  -- 如果已存在 Alice
-- 1. 加 S 锁检查唯一性
-- 2. 发现冲突 → 返回 Duplicate entry 错误
-- 3. 释放 S 锁

追问延伸：

Gap Lock 在 RC 隔离级别下还生效吗？（RC 禁用 Gap Lock，只在 RR 生效）
插入意向锁和 Gap Lock 的关系？（插入意向锁是特殊的 Gap Lock，互相兼容但不与 Gap Lock 兼容）

Q64: MySQL 的 SQL 执行流程是怎样的？连接器/解析器/优化器/执行器各做什么？「🟡 中级」

考察点：MySQL 架构和 SQL 执行流程。

参考答案：

MySQL 的 SQL 执行流程（Server 层 + 存储引擎层）：

客户端
   ↓
1. 连接器（Connection Manager）
   ↓ 建立连接、鉴权、维持长连接
2. 查询缓存（Query Cache，8.0 已删除）
   ↓ 命中则直接返回
3. 解析器（Parser）
   ↓ 词法分析 → 语法分析 → AST（抽象语法树）
4. 优化器（Optimizer）
   ↓ 选择索引、JOIN 顺序、生成执行计划
5. 执行器（Executor）
   ↓ 调用存储引擎接口，逐行获取数据
6. 存储引擎（InnoDB / MyISAM / ...）
   ↓ B+ 树查找、返回行数据
   ↑
返回结果给客户端

各组件职责：

组件	职责	关键点
连接器	管理连接、鉴权	`max_connections`、长连接
解析器	解析 SQL 语法	检查语法错误、解析表名列名
优化器	生成执行计划	选择索引、JOIN 顺序、成本估算
执行器	执行计划	调用引擎接口、逐行处理
存储引擎	数据存储和检索	B+ 树、Buffer Pool、事务

为什么 8.0 删除了查询缓存：

命中率低：任何写操作都导致缓存失效
维护成本高：每次写操作检查并清理缓存
在并发场景下缓存锁竞争严重

优化器的关键决策：

sql

-- 优化器选择索引
SELECT * FROM users WHERE age = 25 AND city = 'Beijing';
-- idx_age vs idx_age_city
-- 优化器基于成本（扫描行数、回表次数）选择最优索引

-- EXPLAIN 可以查看优化器的决策
EXPLAIN SELECT * FROM users WHERE age = 25 AND city = 'Beijing';

强制使用指定索引：

sql

-- 8.0 推荐方式
SELECT * FROM users USE INDEX(idx_age_city) WHERE age = 25;

-- 旧方式
SELECT * FROM users FORCE INDEX(idx_age_city) WHERE age = 25 AND city = 'Beijing';

追问延伸：

长连接为什么会导致 OOM？（MySQL 执行过程中临时对象在连接对象上积累，需要定期 mysql_reset_connection）
优化器基于什么选择索引？（成本估算：扫描行数 × 读取成本 + 回表成本）

Q65: MySQL 的 undo log 有什么用？Buffer Pool 的 LRU 是怎么管理的？「🔴 高级」

考察点：InnoDB 核心组件的工作原理。

参考答案：

Undo Log 的作用：

事务回滚：保存修改前的数据，事务失败时恢复
MVCC：保存历史版本，实现快照读（RC/RR 隔离级别）

sql

-- 原始数据: name='Alice'
UPDATE users SET name='Bob' WHERE id=1;
-- undo log 记录: {id=1, name='Alice'}

-- MVCC: Read View + undo log 构建版本链
-- 事务 A (RR): 看到的 name='Alice'（快照读）
-- 事务 B (已提交): 看到的 name='Bob'

Undo Log 的生命周期：

事务提交后，undo log 不会立即删除
需要等没有活跃事务依赖该版本时才清理（purge 线程）
长事务导致 undo log 无法回收 → 表空间膨胀

Buffer Pool 的 LRU 优化：

InnoDB 对标准 LRU 做了改进：改进版 LRU + 分区：

Buffer Pool LRU:
[--- young 区（新数据, 5/8 ---)][--- old 区（旧数据, 3/8 ---)]
         ↑                                ↑
    新页插入到 old 区头部           全表扫描的页只进 old 区

改进点1：分区（young / old）：

新读入的页先放入 old 区头部
只有在 old 区停留超过 innodb_old_blocks_time（默认 1 秒）后再次被访问，才晋升到 young 区
防止全表扫描的页把热点数据淘汰

改进点2：midpoint 插入：

不是从 LRU 尾部插入，而是从 midpoint（young/old 分界点）插入

全表扫描时：
1. 读入大量页 → 进入 old 区
2. 如果只访问一次 → 停留在 old 区 → 被 old 区尾部淘汰
3. 不会影响 young 区的热点数据

Buffer Pool 其他管理：

脏页刷新：后台线程定期将脏页刷盘（innodb_flush_neighbors）
预读：检测顺序访问模式，提前读入相邻页
自适应哈希索引：对热点页自动建 Hash 索引

追问延伸：

undo log 和 redo log 的区别？（undo 记录旧值用于回滚，redo 记录新值用于持久化）
Buffer Pool 小了会怎样？（频繁换页，缓存命中率低，IO 增加）
purge 线程做什么？（清理不再需要的 undo log 和已删除的记录）

MySQL ​

Q1: 什么是聚簇索引和二级索引？什么是回表？ 「🟢 校招/初级」 ​

Q2: 联合索引的最左前缀原则是什么？ 「🟡 中级」 ​

Q3: 一条 SQL 很慢，你怎么排查和优化？ 「🟡 中级」 ​

Q4: redo log、undo log、binlog 的区别？ 「🟡 中级」 ​

Q5: MySQL 主从复制的原理和延迟问题？ 「🔴 高级」 ​

Q6: 分库分表了解吗？ 「🔴 高级」 ​

Q7: MySQL 的事务隔离级别有哪些？分别解决什么问题？ 「🟢 校招/初级」 ​

Q8: MVCC 是什么？怎么实现的？ 「🔴 高级」 ​

Q9: MySQL 中有哪些锁？行锁、表锁、意向锁、Gap 锁？ 「🔴 高级」 ​

Q10: EXPLAIN 执行计划各字段含义？重点看什么？ 「🟡 中级」 ​

Q11: 索引失效的场景有哪些？ 「🟡 中级」 ​

Q12: MySQL 死锁是什么？怎么排查和避免？ 「🔴 高级」 ​

Q13: Buffer Pool 是什么？有什么作用？ 「🟡 中级」 ​

Q14: MySQL 字符集 utf8 和 utf8mb4 的区别？ 「🟢 校招/初级」 ​

Q15: MySQL 的存储引擎有哪些？InnoDB 和 MyISAM 的区别？ 「🟢 校招/初级」 ​

Q16: MySQL 的 JOIN 有哪些类型？怎么优化？ 「🟡 中级」 ​

Q17: MySQL 8.0 有哪些重要新特性？ 「🟡 中级」 ​

Q18: MySQL 的索引有哪些类型？B+Tree 为什么适合做索引？ 「🟡 中级」 ​

Q19: MySQL 的慢查询分析和 pt-query-digest 怎么用？ 「🟡 中级」 ​

Q20: MySQL 表结构设计有哪些最佳实践？ 「🟡 中级」 ​

Q21: MySQL 的索引下推（ICP）是什么？怎么减少回表？ 「🟡 中级」 ​

Q22: MySQL 的 binlog 有哪几种格式？有什么区别？ 「🟡 中级」 ​

Q23: MySQL 自增主键为什么不连续？ 「🟡 中级」 ​

Q24: MySQL 行锁在什么情况下会"退化"为表锁？ 「🟡 中级」 ​

Q25: MySQL 主从复制延迟怎么排查和优化？ 「🔴 高级」 ​

Q26: MySQL 的 Online DDL 怎么做？加字段会锁表吗？ 「🟡 中级」 ​

Q27: SQL 查询语句的执行顺序是怎样的？ 「🟢 校招/初级」 ​

Q28: MySQL 一条 UPDATE 语句的完整执行过程？ 「🔴 高级」 ​

Q29: 为什么不用 redo log 直接写 B+ 树？为什么要先写 redo log？ 「🔴 高级」 ​

Q30: MySQL 的索引为什么用 B+ 树而不是 B 树或跳表？ 「🟡 中级」 ​

Q31: 数据库三大范式是什么？什么时候需要反范式？ 「🟢 校招/初级」 ​

Q32: CHAR 和 VARCHAR 有什么区别？int(1) 和 int(10) 有什么不同？ 「🟢 校招/初级」 ​

Q33: MySQL 的 in 和 exists 有什么区别？怎么选？ 「🟡 中级」 ​

Q34: MySQL 中有哪些页面置换算法？Buffer Pool 的 LRU 怎么优化？ 「🟡 中级」 ​

Q35: CHAR 和 VARCHAR 的区别？varchar 后面代表字节还是字符？ 「🟢 校招」 ​

Q36: MySQL 如何避免重复插入数据？ 「🟢 校招」 ​

Q37: SQL 查询语句的执行顺序是怎么样的？ 「🟡 中级」 ​

Q38: MySQL 的 in 和 exists 有什么区别？ 「🟡 中级」 ​

Q39: 给定学生表和成绩表，写 SQL 查询 「🟡 中级」 ​

Q40: 聚簇索引和非聚簇索引的区别？ 「🟡 中级」 ​

Q41: 联合索引的实现原理？最左前缀原则？ 「🟡 中级」 ​

Q42: 什么是覆盖索引？什么情况下会回表？ 「🟡 中级」 ​

Q43: 索引失效有哪些场景？ 「🟡 中级」 ​

Q44: 性别字段适合加索引吗？什么字段不适合加索引？ 「🟡 中级」 ​

Q45: MySQL 的隔离级别有哪些？默认是什么？ 「🟢 校招」 ​

Q46: 可重复读隔离级别下怎么防止幻读？ 「🟡 中级」 ​

Q47: MySQL 两条 update 语句处理不同主键范围会不会阻塞？ 「🔴 高级」 ​

Q48: update 语句的完整执行过程是怎样的？ 「🔴 高级」 ​

Q49: MySQL 是如何保障数据不丢失的？redolog 和 binlog 两阶段提交？ 「🔴 高级」 ​

Q50: 查询速度很慢有哪些解决方案？EXPLAIN 怎么看？ 「🟡 中级」 ​

Q51: Text 数据类型可以无限大吗？IP 地址如何在数据库里存储？ 「🟡 中级」 ​

Q52: MySQL 的外键约束是什么？什么时候用？ 「🟢 校招/初级」 ​

Q53: 表的主键用自增 ID 还是 UUID？为什么？ 「🟡 中级」 ​

Q54: B+ 树的叶子节点链表是单向还是双向？查询数据到了叶子节点后怎么查找？ 「🟡 中级」 ​

Q55: 索引已经建好了，再插入一条数据，索引会有哪些变化？ 「🔴 高级」 ​

Q56: 了解过前缀索引吗？自适应 Hash 索引是什么？ 「🟡 中级」 ​

Q57: MySQL 的 count(*) 为什么慢？怎么优化？ 「🟡 中级」 ​

Q58: 深度分页问题怎么解决？ 「🟡 中级」 ​

Q59: 串行化隔离级别是通过什么实现的？一条 update 是不是原子性的？ 「🟡 中级」 ​

Q60: 滥用事务有什么弊端？一个事务里特别多 SQL 有什么问题？ 「🟡 中级」 ​

Q61: MySQL 的两次写（Double Write Buffer）了解吗？ 「🔴 高级」 ​

Q62: InnoDB 行格式有哪些？varchar(n) 中 n 最大取值为多少？ 「🔴 高级」 ​

Q63: MySQL 是怎么加行级锁的？Insert 语句怎么加锁？ 「🔴 高级」 ​

Q64: MySQL 的 SQL 执行流程是怎样的？连接器/解析器/优化器/执行器各做什么？ 「🟡 中级」 ​

Q65: MySQL 的 undo log 有什么用？Buffer Pool 的 LRU 是怎么管理的？ 「🔴 高级」 ​

MySQL

Q1: 什么是聚簇索引和二级索引？什么是回表？「🟢 校招/初级」

Q2: 联合索引的最左前缀原则是什么？「🟡 中级」

Q3: 一条 SQL 很慢，你怎么排查和优化？「🟡 中级」

Q4: redo log、undo log、binlog 的区别？「🟡 中级」

Q5: MySQL 主从复制的原理和延迟问题？「🔴 高级」

Q6: 分库分表了解吗？「🔴 高级」

Q7: MySQL 的事务隔离级别有哪些？分别解决什么问题？「🟢 校招/初级」

Q8: MVCC 是什么？怎么实现的？「🔴 高级」

Q9: MySQL 中有哪些锁？行锁、表锁、意向锁、Gap 锁？「🔴 高级」

Q10: EXPLAIN 执行计划各字段含义？重点看什么？「🟡 中级」

Q11: 索引失效的场景有哪些？「🟡 中级」

Q12: MySQL 死锁是什么？怎么排查和避免？「🔴 高级」

Q13: Buffer Pool 是什么？有什么作用？「🟡 中级」

Q14: MySQL 字符集 utf8 和 utf8mb4 的区别？「🟢 校招/初级」

Q15: MySQL 的存储引擎有哪些？InnoDB 和 MyISAM 的区别？「🟢 校招/初级」

Q16: MySQL 的 JOIN 有哪些类型？怎么优化？「🟡 中级」

Q17: MySQL 8.0 有哪些重要新特性？「🟡 中级」

Q18: MySQL 的索引有哪些类型？B+Tree 为什么适合做索引？「🟡 中级」

Q19: MySQL 的慢查询分析和 pt-query-digest 怎么用？「🟡 中级」

Q20: MySQL 表结构设计有哪些最佳实践？「🟡 中级」

Q21: MySQL 的索引下推（ICP）是什么？怎么减少回表？「🟡 中级」

Q22: MySQL 的 binlog 有哪几种格式？有什么区别？「🟡 中级」

Q23: MySQL 自增主键为什么不连续？「🟡 中级」

Q24: MySQL 行锁在什么情况下会"退化"为表锁？「🟡 中级」

Q25: MySQL 主从复制延迟怎么排查和优化？「🔴 高级」

Q26: MySQL 的 Online DDL 怎么做？加字段会锁表吗？「🟡 中级」

Q27: SQL 查询语句的执行顺序是怎样的？「🟢 校招/初级」

Q28: MySQL 一条 UPDATE 语句的完整执行过程？「🔴 高级」

Q29: 为什么不用 redo log 直接写 B+ 树？为什么要先写 redo log？「🔴 高级」

Q30: MySQL 的索引为什么用 B+ 树而不是 B 树或跳表？「🟡 中级」

Q31: 数据库三大范式是什么？什么时候需要反范式？「🟢 校招/初级」

Q32: CHAR 和 VARCHAR 有什么区别？int(1) 和 int(10) 有什么不同？「🟢 校招/初级」

Q33: MySQL 的 in 和 exists 有什么区别？怎么选？「🟡 中级」

Q34: MySQL 中有哪些页面置换算法？Buffer Pool 的 LRU 怎么优化？「🟡 中级」

Q35: CHAR 和 VARCHAR 的区别？varchar 后面代表字节还是字符？「🟢 校招」

Q36: MySQL 如何避免重复插入数据？「🟢 校招」

Q37: SQL 查询语句的执行顺序是怎么样的？「🟡 中级」

Q38: MySQL 的 in 和 exists 有什么区别？「🟡 中级」

Q39: 给定学生表和成绩表，写 SQL 查询「🟡 中级」

Q40: 聚簇索引和非聚簇索引的区别？「🟡 中级」

Q41: 联合索引的实现原理？最左前缀原则？「🟡 中级」

Q42: 什么是覆盖索引？什么情况下会回表？「🟡 中级」

Q43: 索引失效有哪些场景？「🟡 中级」

Q44: 性别字段适合加索引吗？什么字段不适合加索引？「🟡 中级」

Q45: MySQL 的隔离级别有哪些？默认是什么？「🟢 校招」

Q46: 可重复读隔离级别下怎么防止幻读？「🟡 中级」

Q47: MySQL 两条 update 语句处理不同主键范围会不会阻塞？「🔴 高级」

Q48: update 语句的完整执行过程是怎样的？「🔴 高级」

Q49: MySQL 是如何保障数据不丢失的？redolog 和 binlog 两阶段提交？「🔴 高级」

Q50: 查询速度很慢有哪些解决方案？EXPLAIN 怎么看？「🟡 中级」

Q51: Text 数据类型可以无限大吗？IP 地址如何在数据库里存储？「🟡 中级」

Q52: MySQL 的外键约束是什么？什么时候用？「🟢 校招/初级」

Q53: 表的主键用自增 ID 还是 UUID？为什么？「🟡 中级」

Q54: B+ 树的叶子节点链表是单向还是双向？查询数据到了叶子节点后怎么查找？「🟡 中级」

Q55: 索引已经建好了，再插入一条数据，索引会有哪些变化？「🔴 高级」

Q56: 了解过前缀索引吗？自适应 Hash 索引是什么？「🟡 中级」

Q57: MySQL 的 count(*) 为什么慢？怎么优化？「🟡 中级」

Q58: 深度分页问题怎么解决？「🟡 中级」

Q59: 串行化隔离级别是通过什么实现的？一条 update 是不是原子性的？「🟡 中级」

Q60: 滥用事务有什么弊端？一个事务里特别多 SQL 有什么问题？「🟡 中级」

Q61: MySQL 的两次写（Double Write Buffer）了解吗？「🔴 高级」

Q62: InnoDB 行格式有哪些？varchar(n) 中 n 最大取值为多少？「🔴 高级」

Q63: MySQL 是怎么加行级锁的？Insert 语句怎么加锁？「🔴 高级」

Q64: MySQL 的 SQL 执行流程是怎样的？连接器/解析器/优化器/执行器各做什么？「🟡 中级」

Q65: MySQL 的 undo log 有什么用？Buffer Pool 的 LRU 是怎么管理的？「🔴 高级」