MySQL 面试核心考点：架构、索引与事务实战

1. 不支持范围查询（如 WHERE age > 20）。
2. 不支持排序。
3. 不支持最左前缀匹配。
4. 存在 Hash 冲突风险。

B+Tree 优势：

1. 树高更低：非叶子节点不存数据，单页能存更多 Key，减少磁盘 IO。
2. 范围查询高效：叶子节点通过双向链表连接，遍历方便。
3. 查询稳定：所有查询都走到叶子节点，路径长度一致。

假设节点存 1000 个 Key，3 层 B+Tree 可存 10 亿条数据，仅需 3 次磁盘 IO。

2. 最左前缀原则

联合索引必须从最左边开始匹配，不能跳过中间列。例如索引 idx_a_b_c(a, b, c)：

-- 有效
WHERE a = 1
WHERE a = 1 AND b = 2
WHERE a = 1 AND b = 2 AND c = 3

-- 部分有效（c 用不上）
WHERE a = 1 AND c = 3

-- 无效
WHERE b = 2
WHERE c = 3

注意：查询条件顺序不影响索引使用（优化器会自动调整），但范围查询会导致后续列失效。

WHERE a = 1 AND b > 5 AND c = 3
-- a 走索引，b 走索引（范围），c 失效

3. 索引失效场景

常见导致索引失效的情况包括：

1. 违反最左前缀：联合索引缺少最左列。
2. 索引列运算或函数：如 LEFT(name, 3)='张' 或 YEAR(create_time)=2024。应改为范围查询或前缀匹配。
3. 隐式类型转换：字符串字段传数字参数会导致索引失效。
4. LIKE 以 % 开头：LIKE '%张' 无法利用索引。
5. OR 条件含非索引列：整体不走索引，可用 UNION 替代。
6. 否定查询：NOT IN, !=, <> 可能触发全表扫描。
7. NULL 值判断：视数据分布而定，优化器可能放弃索引。

总结：索引列要保持'干净'，避免加工。

三、事务篇

1. ACID 特性及实现

• 原子性（Atomicity）：不可分割，要么全成功要么全失败。通过 undo log 实现回滚。
• 一致性（Consistency）：事务前后状态一致，由其他三个特性共同保证。
• 隔离性（Isolation）：并发互不干扰。通过 锁 和 MVCC 实现。
• 持久性（Durability）：提交后永久生效。通过 redo log 刷盘实现。

日志区别：redo log 是物理日志，用于崩溃恢复；undo log 是逻辑日志，用于回滚和 MVCC。

2. 事务隔离级别

MySQL 提供四种隔离级别：

• RC (Read Committed)：Oracle 默认，解决脏读，存在不可重复读。
• RR (Repeatable Read)：MySQL 默认，解决脏读和不可重复读，InnoDB 通过 Next-Key Lock 缓解幻读。

3. MVCC 原理

多版本并发控制（MVCC）通过保存历史版本让读写不冲突。

核心组件：

1. 隐藏字段：每行数据含 DB_TRX_ID（修改事务 ID）、DB_ROLL_PTR（回滚指针）、DB_ROW_ID（隐藏主键）。
2. Undo Log：形成版本链，记录旧数据。
3. Read View：快照读时生成，包含活跃事务列表等，用于判断可见性。

可见性规则：对比事务 ID 与 Read View 中的最小/最大 ID 及活跃列表。

生成时机：RC 级别每次 SELECT 生成新视图；RR 级别首次 SELECT 生成并复用，保证了可重复读。

四、锁篇

1. 锁的类型

• 全局锁：锁整个实例，用于备份。
• 表级锁：开销小并发低，包括表锁、MDL、意向锁。
• 行级锁：InnoDB 支持，并发度高。

模式分类：

• 共享锁（S 锁）：读锁，允许并发读。
• 排他锁（X 锁）：写锁，独占资源。

InnoDB 行锁细分：

• Record Lock：锁住单条记录。
• Gap Lock：锁住间隙，防幻读。
• Next-Key Lock：Record + Gap，RR 级别默认算法。

2. 死锁处理

死锁是多个事务互相等待对方锁导致的阻塞。

检测策略：

• 超时：innodb_lock_wait_timeout，默认 50 秒。
• 死锁检测：innodb_deadlock_detect=ON，自动回滚代价小的事务。

避免方法：

1. 固定顺序访问资源。
2. 减小锁粒度和持有时间。
3. 合理使用索引，避免全表扫描锁行。
4. 降低隔离级别或使用乐观锁。

排查命令：

SHOW ENGINE INNODB STATUS;
SELECT * FROM information_schema.INNODB_LOCK_WAITS;

3. 乐观锁 vs 悲观锁

• 悲观锁：认为总有冲突，先加锁再操作（如 SELECT ... FOR UPDATE）。适合写多读少，安全性高但性能开销大。
• 乐观锁：假设冲突少，更新时检查版本号（CAS 思想）。适合读多写少，性能好但需重试机制。

示例：

UPDATE product SET stock = stock - 1, version = version + 1
WHERE id = 1 AND version = 10;

五、SQL 优化篇

1. 慢 SQL 定位与优化

步骤：

1. 开启慢查询日志：设置 long_query_time 阈值。
2. EXPLAIN 分析：关注 type（访问类型）、key（使用索引）、rows（扫描行数）、Extra（额外信息）。
   • 目标：避免 ALL（全表扫描），争取 range 或 ref。
   • Extra 中出现 Using filesort 或 temporary 需优化。
3. 针对性优化：
   • 添加合适索引（联合索引注意顺序）。
   • 避免 SELECT * 和索引列上的函数。
   • 深分页优化（延迟关联或游标分页）。
   • 批量操作代替循环单条插入。

2. 实战案例

某订单查询接口在 500 万数据下响应超 5 秒。

原始 SQL：

SELECT * FROM order
WHERE user_id = 12345 AND status IN (1,2,3)
AND create_time >= '2024-01-01'
ORDER BY create_time DESC LIMIT 0, 20;

优化方案：

1. 加索引：创建 idx_user_status_time(user_id, status, create_time)，将 type 从 ALL 变为 range。
2. 字段精简：只查必要字段，避免回表。
3. 分页优化：使用游标分页（WHERE id < last_id）替代深度 offset。

效果：耗时从 5 秒降至 50 毫秒内。

六、其他高频问题

1. 主从复制原理

基于 binlog 的三步流程：

1. Master 写入 binlog。
2. Slave IO 线程读取 binlog 写入 relay log。
3. Slave SQL 线程重放 relay log。

binlog 格式：

• STATEMENT：记录 SQL，省空间但不保证一致性。
• ROW：记录行变化，一致性好但日志大。
• MIXED：混合模式。

推荐使用 ROW 格式。主从延迟可通过并行复制、硬件升级或避免大事务缓解。

2. 缓存一致性

Cache Aside Pattern（旁路缓存）：

• 读：先读缓存，未命中读库并回写。
• 写：先更新 DB，再删除缓存。

为何删缓存而非更？ 避免并发更新导致旧数据覆盖新数据。 为何先更 DB 再删？ 防止先删缓存期间读请求加载旧数据入缓存。

极端不一致可通过延迟双删或 Canal 监听 binlog 异步更新解决。

3. 索引设计原则

1. 选对字段：高频查询、排序、JOIN 条件、高区分度字段。
2. 联合索引：最左前缀，区分度高的在前，范围查询放最后。
3. 控制数量：一般不超过 5 个，过多影响写入。
4. 主键选择：推荐自增 ID 或雪花算法，避免 UUID 导致页分裂。
5. 去冗余：去除被覆盖的索引（如 (a,b) 存在时 (a) 冗余）。
6. 前缀索引：长字符串字段可只索引前 N 位。

实践上，依据业务查询设计，定期 EXPLAIN 检查，结合慢日志调优。