MySQL 深度指南

答案：连接器(认证/分配线程) → 解析器(词法分析→语法分析→AST) → 优化器(选择执行计划/索引) → 执行器(权限检查→调用存储引擎API) → 存储引擎(读写数据) → 返回结果。

MySQL 8.0 移除了查询缓存功能，因为命中率低且维护成本高。

答案：三个核心原因：

① 磁盘 IO 友好：非叶子节点只存 key，单个 16KB 页容纳更多 key，扇出大(约 1200)，三层可存 2000 万行，每次查询只需 3 次 IO。

② 范围查询高效：叶子节点通过双向链表串联，找到起点后沿链表顺序扫描。

③ 查询性能稳定：所有数据都在叶子节点，每次查询路径长度相同。

聚簇索引：叶子节点存储完整行数据。InnoDB 的主键索引就是聚簇索引，一张表只有一个。数据物理上按主键顺序存储。

非聚簇索引(二级索引)：叶子节点存储主键值。查询时先查二级索引拿到主键，再回表查聚簇索引拿到完整行数据（"回表"过程）。

为什么 InnoDB 建议用自增主键？顺序插入不会导致页分裂，写入性能好。UUID 做主键会导致随机写入、频繁页分裂。

回表：通过二级索引查到主键值后，再回到聚簇索引查完整行数据的过程。每回一次表就是一次 B+ 树查找。

避免方法(覆盖索引)：让查询所需的所有列都在索引中，这样二级索引就能直接返回数据，无需回表。EXPLAIN 中 Extra 显示 Using index。

实战：SELECT id, name FROM user WHERE name='张三'，如果 (name) 索引包含了 name 和 id，就是覆盖索引。

答案：联合索引 (a, b, c) 按 a→b→c 的顺序构建 B+ 树。查询条件必须从最左列开始才能命中索引：

• WHERE a=1 ✓ (命中 a)
• WHERE a=1 AND b=2 ✓ (命中 a,b)
• WHERE a=1 AND b=2 AND c=3 ✓ (全部命中)
• WHERE b=2 ✗ (跳过了 a，无法利用有序性)
• WHERE a=1 AND c=3 △ (仅命中 a，c 无法利用索引)

注意：MySQL 优化器会自动调整 WHERE 条件的顺序，WHERE b=2 AND a=1 也能命中 (a,b)，不需要手写顺序和索引顺序一致。

答案：Index Condition Pushdown (MySQL 5.6+)。在联合索引中，存储引擎在索引遍历过程中就对 WHERE 条件进行过滤，减少回表次数。

例子：索引 (name, age)，查询 WHERE name LIKE '张%' AND age=25

• 无 ICP：存储引擎按 name 找到所有姓张的 → 全部回表 → Server 层判断 age
• 有 ICP：存储引擎在索引中直接判断 age=25 → 只对满足条件的行回表

EXPLAIN 中 Extra 显示 Using index condition。

从低到高：

1. Read Uncommitted：可读到未提交数据，有脏读、不可重复读、幻读。
2. Read Committed (RC)：只读已提交数据，Oracle/PostgreSQL 默认。有不可重复读、幻读。
3. Repeatable Read (RR)：MySQL 默认，同一事务内多次读结果一致。InnoDB 通过 MVCC + Gap Lock 基本解决幻读。
4. Serializable：完全串行化，性能最差，无并发问题。

核心组件：

① 隐藏列：每行数据有 trx_id(最后修改的事务ID) 和 roll_pointer(指向 undo log)。
② Undo Log 版本链：通过 roll_pointer 串联同一行的所有历史版本。
③ Read View：事务开启时创建的"快照"，记录当前活跃事务列表，用来判断哪个版本可见。

可见性规则：从最新版本沿版本链向前遍历，找到第一个对当前 Read View 可见的版本（trx_id 已提交且在 Read View 创建之前）。

Gap Lock 锁住索引记录之间的间隙，防止其他事务在该间隙中插入新记录。

解决幻读：事务A查询 WHERE age > 20，InnoDB 会对 20 之后的间隙加 Gap Lock。事务B尝试 INSERT age=25 会被阻塞。这样事务A再次查询时行数不会变。

注意：Gap Lock 在 RC 级别下不生效（RC 没有 Gap Lock），只在 RR 级别生效。且 Gap Lock 之间不冲突（多个事务可以同时持有同一间隙的 Gap Lock），只和 Insert 冲突。

最核心的四个区别：

① 事务：InnoDB 支持ACID事务，MyISAM 不支持。
② 锁：InnoDB 行级锁，MyISAM 表级锁。高并发下 InnoDB 性能远超 MyISAM。
③ 崩溃恢复：InnoDB 通过 Redo Log 自动恢复，MyISAM 需要 REPAIR TABLE。
④ 聚簇索引：InnoDB 主键索引就是数据(聚簇)，MyISAM 索引和数据分开存储。

结论：MySQL 5.7+ 默认 InnoDB，99% 场景用 InnoDB。除非纯只读日志表等极端场景才考虑 MyISAM。

• Redo Log：记录"做了什么改动"，用于崩溃恢复(持久性)。WAL 机制，顺序写，先写日志再写数据文件。
• Undo Log：记录"改之前的数据"，用于回滚(原子性)和 MVCC 版本链。存在于系统表空间或 undo 表空间中。

类比：Redo = 施工记录(重建用)，Undo = 撤销记录(回退用)。

WAL 原则：先写日志(Redo Log)，再写数据文件。确保即使数据页还没刷到磁盘就崩溃了，也能通过 Redo Log 恢复。

流程：修改数据 → 写入 Buffer Pool(内存) → 写 Redo Log(顺序写,很快) → 返回成功 → 后台线程择机将 Buffer Pool 脏页刷到数据文件。

为什么快？Redo Log 是顺序写(append only)，每次写入只需 seek 到末尾。而数据页是随机写，需要找到对应位置。顺序写性能远高于随机写。

从好到差：

• system：表中只有一行(MyISAM 引擎的 const 特例)
• const：主键/唯一索引等值查询，最多一行匹配
• eq_ref：JOIN 时对每行都通过主键/唯一索引等值查询
• ref：非唯一索引等值查询，可能多行匹配
• range：索引范围扫描 (BETWEEN, >, <, IN)
• index：全索引扫描(比 ALL 好，但仍然扫描全部索引)
• ALL：全表扫描，最差

面试结论：至少达到 range 级别，最好 ref 以上。

优化步骤：

1. 开启慢查询日志：slow_query_log=ON, long_query_time=1
2. 用 mysqldumpslow 或 pt-query-digest 分析 TOP N 慢查询
3. EXPLAIN 分析执行计划，关注 type/key/rows/Extra
4. 常见优化手段：
  • 添加合适的索引(联合索引/覆盖索引)
  • 避免 SELECT *
  • 分页优化(Deferred Join)
  • 避免 %开头的 LIKE
  • 避免在索引列上使用函数
  • 小表驱动大表( EXISTS vs IN)

覆盖索引：查询所需的所有列都在索引中，不需要回表。EXPLAIN 中 Extra 显示 Using index。

原理：二级索引的叶子节点存储了索引列的值 + 主键值。如果查询只需要索引列和主键，就无需回聚簇索引。

实战：SELECT user_id, order_id FROM orders WHERE user_id=100，索引 (user_id) 已经包含 user_id 和 order_id(主键)，自动覆盖。

流程：

1. Master 执行 SQL，写入 Binlog
2. Slave 的 IO Thread 连接 Master，请求 Binlog
3. Master 的 Dump Thread 发送 Binlog 给 Slave
4. Slave 的 IO Thread 将 Binlog 写入本地 Relay Log
5. Slave 的 SQL Thread 读取 Relay Log 并回放 SQL

Binlog 格式：Statement(记录SQL)、Row(记录行变更，推荐)、Mixed(混合)。

死锁：两个事务互相持有对方需要的锁，导致双方都无法继续执行。

避免方法：

1. 按固定顺序访问表和行(如按 id 升序)
2. 大事务拆成小事务，减少锁持有时间
3. 合理使用索引，避免行锁升级为表锁
4. 设置合理的 innodb_lock_wait_timeout

InnoDB 自动检测死锁（wait-for graph 算法），回滚代价最小的事务。

Binlog 是 MySQL Server 层的二进制日志，记录所有DDL和DML操作，主要用于主从复制和数据恢复。

三种格式：

• Statement：记录 SQL 语句。日志量小，但某些函数(如 NOW(), UUID())在从库执行结果可能不一致。
• Row：记录行数据的变更(修改前/修改后)。日志量大，但数据一致性最好，推荐使用。
• Mixed：默认 Statement，不确定的用 Row。

两个核心原因：

1. 避免页分裂：自增主键顺序插入，数据追加到 B+ 树末尾，不会导致已有页面分裂。UUID 随机插入会导致频繁页分裂，产生大量碎片。
2. 二级索引占用空间更小：二级索引叶子节点存主键值，int 占 4 字节，UUID 占 36 字节。所有二级索引都会大很多。

• 快照读：普通 SELECT，读取 MVCC 快照中的数据，不加锁。通过 Read View + Undo Log 版本链实现。
• 当前读：SELECT ... FOR UPDATE / SELECT ... LOCK IN SHARE MODE / INSERT / UPDATE / DELETE，读取最新已提交数据，并加锁。

关键区别：快照读可能读到旧数据(MVCC)，当前读一定读到最新数据且会加锁。

作用：缓存数据页和索引页，减少磁盘 IO。通常设置为物理内存的 60-80%。

优化策略：

1. innodb_buffer_pool_size：设置为可用内存的 60-80%
2. innodb_buffer_pool_instances：多实例减少锁竞争(buffer pool ≥ 1GB 时设为 2-8)
3. LRU 优化：InnoDB 对 LRU 做了改进(young 区 + old 区)，防止全表扫描一次性把热数据挤出去
4. 监控 Buffer pool hit rate，应 > 95%

常见索引失效场景：

1. 对索引列使用函数：WHERE YEAR(create_time)=2024 → 改为范围查询
2. 隐式类型转换：WHERE phone=13800138000 (phone 是 varchar) → 全表扫描
3. LIKE 以 % 开头：WHERE name LIKE '%张'
4. OR 条件中有无索引列：WHERE a=1 OR b=2(b无索引)
5. 不满足最左前缀原则
6. NOT IN / NOT EXISTS / != (部分情况)
7. 优化器认为全表扫描更快(数据量小或选择性差)

问题：InnoDB 页大小 16KB，而操作系统磁盘 IO 通常以 4KB 为单位。如果写 16KB 页时只写了一部分就断电，会出现"页撕裂"（部分新数据+部分旧数据）。

解决：Doublewrite Buffer。先将脏页写入共享表空间的 Doublewrite 区域(连续 2MB 空间)，写成功后再写到真正的数据文件。

恢复：如果写数据文件时崩溃，从 Doublewrite 区域找到完整的页副本进行恢复，再用 Redo Log 重做。

多维度的方案：

1. 索引优化：合理的联合索引、覆盖索引，避免全表扫描
2. 读写分离：主库写、从库读，缓解单库压力
3. 分表：水平分表(按用户ID取模/按时间)或垂直分表(拆冷热列)
4. 缓存：Redis 缓存热点数据，减少 DB 压力
5. 归档：冷数据迁移到历史表
6. 分区表：PARTITION BY RANGE，但限制较多
7. 中间件：ShardingSphere / MyCat 做分库分表路由

Change Buffer：对非唯一二级索引的 DML 操作(INSERT/UPDATE/DELETE)，如果索引页不在 Buffer Pool 中，不立即从磁盘加载，而是将变更缓存在 Change Buffer 中。等后续该页被访问时再合并(merge)。

目的：减少随机 IO。二级索引的修改通常是随机的，通过 Change Buffer 将多次修改合并为一次 IO。

适用：写多读少的场景。如果写完立刻读，Change Buffer 反而增加开销（要 merge）。

• 异步复制：主库提交事务后不等从库确认，直接返回客户端。性能最好，但主库宕机可能丢失未同步的 Binlog。
• 半同步复制：主库提交事务后，等待至少一个从库收到 Binlog 并写入 Relay Log 后才返回。数据安全性提高，但增加延迟(通常 <1ms)。
• 增强半同步(After Sync)：MySQL 5.7+ 默认，先等从库确认再提交事务，比之前的 After Commit 更安全。

• 普通 SELECT：不加锁，走 MVCC 快照读。
• SELECT ... FOR UPDATE：加排他锁(X锁)，当前读。
• SELECT ... LOCK IN SHARE MODE：加共享锁(S锁)。
• UPDATE/DELETE：当前读，对匹配的行加 X 锁。在 RR 级别下还会加 Gap Lock/Next-Key Lock。

重要：如果 UPDATE/DELETE 的 WHERE 条件没有走索引，InnoDB 会对每一行加锁(实际退化为表级锁效果)！所以 UPDATE 一定要走索引。

排查步骤：

1. SHOW PROCESSLIST 找到耗时长的 SQL
2. EXPLAIN 分析执行计划，通常原因是全表扫描(type=ALL)
3. 检查是否有缺失索引或索引失效
4. 检查是否有大量慢查询同时执行
5. SHOW ENGINE INNODB STATUS 看是否有锁等待
6. top -H -p mysql_pid 看具体线程
7. 检查 Buffer Pool 命中率是否过低

AHI：InnoDB 自动监控对 Buffer Pool 中页的访问模式。如果发现某些页被频繁用等值查询访问，会自动为这些页构建内存中的哈希索引。

特点：

• 完全自动，无法手动配置
• 只对等值查询(=, IN)有效
• 由 innodb_adaptive_hash_index 控制
• 在高并发 OLTP 场景下可能提升性能，但在高并发写入场景下可能因为 AHI 的锁竞争导致性能下降

问题：LIMIT 1000000, 10 需要扫描 1000010 行后丢弃前 100 万行。

优化方案：

1. Deferred Join：子查询只查 id(走索引)，再 JOIN 回表
2. 游标分页：WHERE id > last_id ORDER BY id LIMIT 10，适合瀑布流
3. Between：如果 id 连续且有序，用 WHERE id BETWEEN 1000001 AND 1000010
4. 缓存：首页/热门页数据缓存到 Redis

• COUNT(*) 和 COUNT(1)：等价，统计所有行数(含 NULL)。InnoDB 会选最小的索引来遍历，不是全表扫描。
• COUNT(列)：统计该列非 NULL 的行数。如果列不允许 NULL 则和 COUNT(*) 等价。
• COUNT(DISTINCT 列)：统计该列不同值的数量。

为什么 InnoDB 不像 MyISAM 那样直接存储行数？因为 MVCC，不同事务可能看到不同的行数。

1. 移除查询缓存：命中率低，维护成本高
2. 窗口函数：ROW_NUMBER, RANK, DENSE_RANK, LEAD, LAG 等
3. CTE (公用表表达式)：WITH RECURSIVE 递归查询
4. 函数索引：CREATE INDEX idx ON t ((UPPER(name)))
5. 降序索引：真正支持 DESC 索引(之前是假的)
6. Invisible Index：隐藏索引，用于测试
7. 更细粒度的权限控制：角色(Role)支持
8. DDL 原子性：DDL 也支持崩溃恢复

常见方案：

1. UUID：简单但无序，不适合做 MySQL 主键(页分裂)
2. Snowflake(雪花算法)：64位 = 1位符号 + 41位时间戳 + 10位机器ID + 12位序列号。有序、高性能、趋势递增。需解决时钟回拨问题。
3. 数据库自增：简单但性能瓶颈。可设置 auto_increment_increment 和 auto_increment_offset 实现多实例。
4. Redis INCR：高性能，但依赖 Redis 可用性
5. Leaf(美团) / UidGenerator(百度)：工业级方案

ORDER BY 能使用索引的条件：

1. ORDER BY 的列有索引，且顺序与索引一致
2. 多列排序时，列的顺序和方向必须一致(8.0 之前不能混用 ASC/DESC)
3. 如果同时有 WHERE 和 ORDER BY，WHERE 条件也必须能使用同一索引的前缀
4. 不能跨表 JOIN 排序使用索引

判断：EXPLAIN 的 Extra 没有 Using filesort 就说明用了索引排序。

三层保障：

1. Redo Log (WAL)：事务提交时先写 Redo Log 到磁盘(innodb_flush_log_at_trx_commit=1)，即使数据页还没刷盘，崩溃后也能通过 Redo Log 恢复。
2. Doublewrite Buffer：防止页撕裂(部分写入)。写数据页前先写一份到 Doublewrite 区域。
3. Binlog：sync_binlog=1 确保每次提交都刷到磁盘。用于主从复制和 Point-in-Time 恢复。

生产最佳配置：
innodb_flush_log_at_trx_commit=1 (每次提交都刷 Redo Log)
sync_binlog=1 (每次提交都刷 Binlog)
这两个设为 1 才能保证真正的持久性，但会损失约 10-20% 性能。