MySQL 底层原理与索引命中手册

进阶整理 · 重索引命中机制与 SQL 实例分析 · 深入 InnoDB 存储引擎、日志、事务、锁

1. 架构总览

1.1 Server 层 vs 存储引擎层

MySQL 分为两层：Server 层负责连接、SQL 解析、优化、执行与日志；存储引擎层(InnoDB)负责数据的实际存取。

• 连接器：管理连接、校验身份与权限。
• 分析器：词法+语法分析，生成解析树。
• 优化器：决定用哪个索引、表连接顺序，输出执行计划。
• 执行器：按执行计划逐行调用存储引擎接口。

2. InnoDB 存储引擎

2.1 页（Page）与行

InnoDB 以页（16KB 默认）为最小 I/O 单元，磁盘与内存之间按页读写。数据按行存储，行格式：COMPACT / DYNAMIC / REDUNDANT。

• 页结构包含：页头、Infimum/Supremum 记录（边界）、用户记录（User Records）、页目录、页尾。
• 同一页内的记录通过单向链表连接，靠 next_record 指针。
• 页目录（Page Directory）提供二分查找能力，减少遍历。

2.2 聚簇索引（主键索引）

InnoDB 必须有聚簇索引。数据本身就按 B+ 树组织在聚簇索引的叶子节点里。如果没有主键会怎样？

• 定义了主键 → 用主键做聚簇索引。
• 没有主键但有非空唯一键 → 用该唯一键。
• 否则 → 自动生成一个隐藏的 rowid 做聚簇索引。

2.3 二级索引（辅助索引）

二级索引的叶子节点存的是主键值（不是完整行）。因此通过二级索引查数据需要"回表"：先按二级索引定位主键，再回聚簇索引取整行。

2.4 表空间与文件

InnoDB 数据存储在 .ibd（表空间）中。区分段、区、页：

2.5 Buffer Pool（缓冲池）

内存中的缓存，用来加速读写。核心机制：

• LRU 链表淘汰冷页，提高命中率。
• 读请求优先查 Buffer Pool，未命中再读磁盘；写优先写 buffer + redo log（WAL）。
• Change Buffer：对非唯一二级索引的写操作，如页不在内存，先记入 change buffer 延迟合并，减少随机 IO。
• innodb_buffer_pool_size 通常设为物理内存的 50%-75%。

3. 索引机制（核心）

3.1 为什么用 B+ 树而非 B 树 / 哈希 / 红黑树

3.2 联合索引与最左前缀

联合索引 idx(a,b,c) 建立后，先按 a 排、再按 b、再按 c。因此命中规则是最左前缀。

• 能用 a、a+b、a+b+c。
• 查询条件从最左列开始连续命中才有效；跳过某列或从中间列开始，后续列的排序失效。

3.3 回表与覆盖索引

回表：二级索引找到主键后再回聚簇索引取整行。覆盖索引：查询所需字段都在索引里，无需回表。

优化技巧：需要回表时尽量"用覆盖索引"，把常见查询字段塞进联合索引（注意别塞太多导致索引过大）。

3.4 索引下推（ICP, Index Condition Pushdown）

MySQL 5.6+ 优化：把非索引能覆盖的过滤条件下推到存储引擎，在二级索引扫描时就先过滤，减少回表次数。

3.5 前缀索引

对长字符串（如 email、content）可只索引前 N 个字符，减小索引体积。但会降低区分度（长尾冲突），需权衡。

ALTER TABLE t ADD INDEX idx_email(email(10));   -- 只用前10字符
-- 区分度校验
SELECT COUNT(DISTINCT email)/COUNT(*) AS full_sel,
       COUNT(DISTINCT LEFT(email,10))/COUNT(*) AS pre_sel FROM t;

3.6 函数索引 / 表达式索引

当查询对列做了函数运算（DATE()、LOWER()），普通索引失效，需用函数索引或生成列（MySQL 5.7+ / 8.0 支持）。

4. 索引命中与失效（SQL 实例）

4.1 EXPLAIN 解析

EXPLAIN SELECT * FROM t WHERE a=1 AND b=2;
-- 关注: type / key / rows / Extra

• key：实际用到的索引；rows：预估扫描行数；Extra：是否 Using index(覆盖索引)、Using filesort(排序)、Using temporary(临时表)、Using index condition(ICP)。
• 目标：type 至少 range，理想 ref/const；rows 小；Extra 不含 filesort/temporary。

4.2 索引失效的场景（对照 SQL）

下面以 user(id PK, name VARCHAR, age INT, email VARCHAR, idx(name,age), idx(email)) 为例。

① 对列做函数 / 运算 → 失效

-- 失效
SELECT * FROM user WHERE name = CONCAT('张','三');
SELECT * FROM user WHERE age + 1 = 30;
-- 命中（等价改写）
SELECT * FROM user WHERE name = '张三';
SELECT * FROM user WHERE age = 29;

② 隐式类型转换 → 失效

-- 如果 phone 是 VARCHAR 类型
SELECT * FROM user WHERE phone = 13800000000;  -- 隐式转成数字比较 → 索引失效
SELECT * FROM user WHERE phone = '13800000000'; -- 字符串匹配 → 命中

因为 '1'<'9' 的字符串排序与数字不同，MySQL 会把列做转换，导致索引失效。

③ LIKE 以 % 开头 → 失效

SELECT * FROM user WHERE name LIKE '%张%';   -- 失效（前缀不定）
SELECT * FROM user WHERE name LIKE '张%';    -- 可命中（前缀确定 → 范围）
SELECT * FROM user WHERE name LIKE '%张';    -- 失效

④ 最左前缀被破坏（联合索引）

SELECT * FROM user WHERE age = 30;            -- 跳过 name → 失效
SELECT * FROM user WHERE name='张三' AND age=30; -- 命中 name,age
SELECT * FROM user WHERE name='张三';          -- 只用 name → 部分命中

⑤ 使用 OR 且两侧条件未同列索引 → 失效

SELECT * FROM user WHERE name='张三' OR email='[email protected]';
-- 若 OR 两侧分别走不同索引，MySQL 常退化为 全表/index_merge。
-- name/email 有索引仍可能走 index_merge，但若一侧无索引则全表。

⑥ 对列隐式取部分（SUBSTRING / 连接）

SELECT * FROM user WHERE SUBSTRING(name,1,1)='张'; -- 失效

⑦ 范围条件之后的列（联合索引）

-- 假设 idx(name, age, email)
SELECT * FROM user WHERE name='张三' AND age>20 AND email='a@b';
-- age 是范围，email 无法用于排序/索引定位（只是过滤）→ 只命中 name,age

⑧ 索引列可空但查询用 IS NULL 等

MySQL 对 IS NULL/IS NOT NULL 的索引优化较复杂，通常仍可走索引但代价可能高于全表，视统计而定。

4.3 查询优化实战（SQL 重写）

排序（filesort）优化

-- 想避免 filesort，ORDER BY 的字段要满足索引的最左前缀且方向一致
SELECT * FROM user WHERE name='张三' ORDER BY age; -- 命中索引，无需 filesort

4.4 索引设计原则

• 区分度高、查询频繁的列放联合索引最左。
• 最左前缀 = 高频查询列优先。
• 索引不是越多越好：写会变慢、占空间、更新成本高。
• 用 EXPLAIN 验证，用 SHOW INDEX / information_schema 排查冗余索引。

5. 事务与隔离级别

5.1 ACID

5.2 隔离级别

InnoDB 默认 RR 级别。幻读在 RR 下可通过间隙锁或 SELECT ... FOR UPDATE 规避。

5.3 MVCC（多版本并发控制）

MVCC 通过隐藏列 + undo log 版本链 + Read View 实现非阻塞读（快照读）。

• 每行有隐藏字段 trx_id（最近修改本文的事务ID）和 roll_pointer（指向 undo 链）。
• Read View：记录当时活跃事务的 ID 列表，判断某版本是否可见。
• RC：每次 SELECT 生成新 Read View；RR：事务内首次 SELECT 生成一次，后续复用 → 保证可重复读。

6. 日志系统

6.1 redo log（重做日志）

实现 WAL（Write-Ahead Logging）：先写 redo，再写内存，最后刷盘。崩溃后根据 redo 恢复已提交数据，保证持久性。

• 记录"页的物理修改"，循环写（固定大小）。
• innodb_flush_log_at_trx_commit：0=每秒刷、1=提交即刷（最安全）、2=提交写OS缓存。

6.2 undo log（回滚日志）

用于回滚事务和 MVCC 多版本。记录反向操作，逻辑日志。回滚段在表空间内，也进行 MVCC 版本链构建。

6.3 binlog（归档日志）

Server 层日志，记录"逻辑操作"，用于主从复制、数据恢复。格式：STATEMENT / ROW / MIXED。

6.4 两阶段提交（2PC）

为保证 redo log 与 binlog 一致，事务提交采用两阶段：

1. 写 redo log 记录"prepare"状态。
2. 写 binlog。
3. 写 redo log 记录"commit"状态。

崩溃恢复时，若 binlog 有记录且 redo 为 prepare，则提交；否则回滚。

7. 锁机制

7.1 锁类型

7.2 行锁细究

• 记录锁 Record Lock：锁具体某个索引记录。
• 间隙锁 Gap Lock：锁左开右开区间，防止幻读，仅 RR 级别生效。
• 临键锁 Next-Key = 记录锁 + 间隙锁：锁 (前一个值, 当前值]，范围查询默认加。

7.3 死锁

两个事务互相等待对方持有的锁。InnoDB 通过 innodb_deadlock_detect(默认 on) 主动检测并回滚代价小的事务。降低死锁：按固定顺序加锁、减少事务时间、缩小加锁范围。

8. 速查清单