InnoDB 索引与执行计划一条 SQL 从发起到返回中间经历了什么SQL → 解析器(AST) → 优化器(选执行计划) → 执行器 → 存储引擎(InnoDB)本文聚焦最后一步InnoDB 如何用 BTree 找数据以及你怎么用EXPLAIN看懂它。一、物理存储页与 BTree1.1 页Page—— InnoDB 的最小 I/O 单位默认 16 KB。所有读写都以页为单位不是单行。页内部文件头尾 页目录稀疏索引支持页内二分 用户记录 空闲空间。为什么偏偏是 16 KB太小如 4 KB单页存的 keyptr 少BTree 层数多I/O 次数多太大如 1 MB读入内存浪费空间Buffer Pool 命中率下降必须是 OS 页4 KB整数倍避免 InnoDB 页与 OS 页错位导致的 I/O 放大结论16 KB 4 × 4 KB平衡了树高度与内存浪费1.2 BTree 的核心优势维度B-TreeBTreeInnoDB非叶子节点存 key 数据 ptr只存 key ptr叶子节点存部分数据存全部数据行叶子连接无双向链表范围扫描高效单页 key 容量较少多很多核心收益非叶子节点单页能存更多 key → 树更矮 → I/O 次数更少。设计哲学BTree 将数据全部压在叶子节点并用链表串联本质是“牺牲树的高度换取范围遍历的效率”。1.3 三层 BTree 能存多少行假设主键BIGINT 8 字节页内指针 6 字节单页 keyptr ≈ 16384 / 14 ≈1170 个。Root Page (16KB) 第 1 层: ≈1170 个 keyptr │ ┌─────────────────────┼─────────────────────┐ ▼ ▼ ▼ Index Page (16KB) Index Page (16KB) Index Page (16KB) 第 2 层: ≈1170 个 ≈1170 个 ≈1170 个 │ │ │ ▼ ▼ ▼ Leaf Page (16KB) Leaf Page (16KB) Leaf Page (16KB) 第 3 层: ≈16 行数据 ≈16 行 ≈16 行根节点1 页 × 1170 1170 个二级索引页二级索引1170 页 × 1170 1,368,900 个叶子页叶子页1,368,900 × 16 行 ≈2190 万行DBA 经验法则「单表超 2000 万考虑分库分表」的物理依据就在此。超过这个数树可能升至 4 层查询 I/O 增至 4 次。1.4 聚簇索引 vs 二级索引维度聚簇索引Clustered二级索引Secondary存储内容完整数据行(索引列, 主键值)叶子节点大小大小表中数量唯一主键多个回表不需要需要除非覆盖索引聚簇索引主键选取 fallback有主键 → 主键无主键 → 第一个NOT NULL UNIQUE列都没有 → 隐藏 6 字节ROW_ID。回表代价二级索引查到主键后到聚簇索引取完整行这一步是随机 I/O是性能瓶颈。覆盖索引Covering IndexSELECT列全部在联合索引中Extra显示Using index无需回表。1.5 联合索引的排序逻辑联合索引(a, b)在 BTree 中先按a升序a相同再按b升序叶子节点物理顺序(1,1) → (1,2) → (1,3) → (2,1) → (2,2) → (3,1) ...这是理解最左前缀和ORDER BY 索引优化的底层基础。1.6 行溢出一行数据超过约 8 KB 时长字段TEXT/BLOB移至独立的溢出页原页仅留 20 字节指针。行格式溢出策略DYNAMICMySQL 5.7 默认完全移走原页仅留 20 字节指针COMPACT老式原页保留前 768 字节 指针二、索引核心机制2.1 最左前缀法则查询必须从最左列开始遇到范围查询、、BETWEEN、LIKE xx%会截断后续列匹配和IN不截断-- 索引(user_id, order_date, status)WHEREuser_id1ANDorder_date2026-01-01ANDstatus1-- user_id 等值生效order_date 范围生效status 被截断失效2.2 Cardinality基数索引中不重复值的预估数量。高基数主键、UUID→ 优化器倾向用索引低基数性别→ 可能放弃索引走全表扫描。统计信息由ANALYZE TABLE或后台采样更新非精确值过时可能导致优化器误判。三、key_len —— 诊断索引命中的「透视镜」EXPLAIN中的key_len表示实际使用的索引字节长度。各类型字节占用类型不允许 NULL允许 NULLTINYINT12INT45BIGINT89DATETIME (5.6)56DATE34TIMESTAMP45CHAR(N) utf8mb4N×4N×41VARCHAR(N) utf8mb4N×42N×43DECIMAL(12,2)672VARCHAR 变长需 2 字节记录实际长度。1允许 NULL 时需 1 字节 NULL bitmap。DECIMAL 字节推导DECIMAL(M,D)每 9 位打包 4 字节剩余位按 1-21B、3-42B、5-63B、7-84B。DECIMAL(12,2) 整数 10 位9 位 4B 1 位 1B 5B 小数 2 位1B6 字节。实战推演-- 联合索引 (user_id INT NOT NULL, status TINYINT NULL)WHEREuser_id1ANDstatus2-- key_len 411 6两列全用WHEREuser_id1-- key_len 4只用 user_idWHEREuser_id1ANDstatus2-- key_len 4status 范围截断四、访问方法type—— 性能天梯type一句话本质触发场景system表只有一行系统表几乎遇不到const主键/唯一索引等值命中结果转常量WHERE id 1eq_refJOIN 中驱动表每行精准命中唯一索引一条JOIN ON 唯一索引列ref非唯一索引等值匹配捡完所有副本WHERE name 张三range沿 BTree 链表遍历连续区间BETWEEN、、index扫描整个二级索引叶子链表覆盖索引查询ALL扫描整个聚簇索引叶子链表无索引 / 优化器放弃ref vs range 的本质区别两者看起来都是沿链表遍历但停止条件不同ref遇到第一个不同的键值就停收集同一键值的所有副本range到达范围上限的键值才停遍历连续区间内多个不同键值const vs eq_ref vs ref单表唯一查const只查一次变常量JOIN 唯一查eq_ref查 N 次每次命中一条非唯一等值ref查完所有重复值才停五、过滤机制全链路WHERE 条件中的列按处理阶段分为三类┌─────────────────────────────────────────────────────────────┐ │ Index Key索引键 │ │ ├── 用于 BTree 精确定位扫描起点和终点 │ │ ├── 组成连续等值匹配的列 第一个范围匹配的列 │ │ └── 处理时机树形遍历时存储引擎层 │ ├─────────────────────────────────────────────────────────────┤ │ Index Filter索引过滤 │ │ ├── 索引扫描范围内额外过滤的列 │ │ ├── 非最左前缀的后续索引列被范围查询截断 │ │ ├── ICP 开启回表前在引擎层过滤Extra: Using index cond│ │ └── ICP 关闭回表后在 Server 层过滤 │ ├─────────────────────────────────────────────────────────────┤ │ Table Filter表过滤 │ │ ├── 完全不在索引里的列 │ │ ├── 处理时机回表后Server 层 │ │ └── Extra: Using where │ └─────────────────────────────────────────────────────────────┘ICP索引条件下推MySQL 5.6 引入将 Index Filter 下推到存储引擎层执行。场景执行流程回表次数无 ICP索引定位 → 全部回表 → Server 过滤高有 ICP索引定位 → 引擎层过滤 → 仅满足条件的回表低MRR多范围读取解决回表的随机 I/O问题扫描二级索引收集满足条件记录的主键 ID存入read_rnd_buffer排序按主键顺序回表 →随机 I/O 变顺序 I/O优化手段核心目标关键区别覆盖索引避免回表最彻底ICP减少回表回表前过滤MRR优化回表无法避免回表但让回表更高效Extra 速查Extra含义好坏Using index覆盖索引无需回表✓✓Using index conditionICP 生效✓Using whereServer 层 Table Filter✗Using filesort额外排序✗Using temporary创建临时表✗✗Using join bufferJOIN 未走索引✗六、EXPLAIN vs EXPLAIN ANALYZE维度EXPLAIN估算EXPLAIN ANALYZE实测是否执行 SQL❌ 只解析✅真实执行DML 会改数据行数rows估算actual rows真实耗时无 / costactual time毫秒级可信度可能过时绝对可信cost vs actual timecost 是优化器的内部决策货币无量纲估算actual time 是真实物理耗时。调优时 100% 信任 actual time。当cost低但actual time高时 → 统计信息过时或索引区分度差 → 执行ANALYZE TABLE。七、索引设计规则7.1 联合索引设计 8 原则原则说明违反代价最左前缀查询从最左列开始索引完全失效高选择性列优先区分度高的列放最左rows 过多等值优先于范围、IN放前、放后范围截断后续列排序列放最后ORDER BY放联合索引末尾Using filesort覆盖索引SELECT列尽量包含在索引中额外回表避免冗余索引已有(a,b)不要建(a)浪费空间 写入变慢避免索引列运算WHERE a12全表扫描避免前导通配 LIKEWHERE name LIKE %xx全表扫描7.2 何时走全表扫描typeALL索引列选择性 5%5.x/ 20%8.0索引列做了运算或函数转换隐式类型转换字符串列用 INT 查OR连接的列没有共同索引7.3 索引数量上限维度限制单表索引数建议 ≤ 5-6 个单索引列数建议 ≤ 5 列单索引总 key_len≤ 767 字节utf8mb4 下 VARCHAR(191)八、索引失效 5 场景#失效场景示例失效性质1索引列函数/运算WHERE user_id12语法失效2LIKE 前导通配WHERE order_no LIKE %123语法失效3隐式类型转换WHERE order_no123列是 VARCHAR转换失效4OR 部分无索引WHERE user_id1 OR amount500成本失效5违反最左前缀WHERE pay_time2026-01-01索引首列是 user_id语法失效三大失效性质语法失效possible_keysNULL优化器根本不考虑转换失效possible_keys有值但keyNULL成本失效possible_keys有值但keyNULL优化器算完放弃隐式转换的毁灭性order_no123(列是VARCHAR)→ MySQL 把字符串转数字CAST(order_noASSIGNED)123→ BTree 存的是字符串转数字后不再有序 → 索引失效 →ORD00000001转数字0→ 永远查不到 实测走索引0.154ms vs 隐式转换5510ms**35779倍差距**九、调优实战 4 步法问题 SQLSELECT*FROMordersWHEREuser_id12345ANDstatus1ANDpay_time2026-01-01ORDERBYcreate_timeDESCLIMIT20;Step 1EXPLAIN 看计划type: ref, key: idx_user_status, rows: 5000, Extra: Using where; Using filesortStep 2EXPLAIN ANALYZE 定位真实瓶颈索引定位快0.35ms找到 5000 条回表 Table Filterpay_time耗时 12.5~98.7ms剩 3500 条排序是最大瓶颈125.3~127.8ms占 60%Step 3优化方案新索引(user_id, status, pay_time, create_time)user_id, statusIndex Key精确定位pay_timeIndex FilterICP 下推回表前过滤create_time覆盖 ORDER BY消除 filesortStep 4验证type: ref, key: 新索引, Extra: Using index condition -- Using filesort 消失耗时从 ~130ms 降至 ~20msFORCE INDEX 经典案例-- 优化器自动选 idx_user_status(2列) → cost0.28 → actual0.232ms-- FORCE INDEX 选 idx_user_status_paytime(3列) → cost0.71 → actual0.077ms ✓结论cost 说前者便宜actual time 说后者快 3 倍。优化器无法预知 ICP 过滤后actual rows0。十、主键设计与页分裂主键选型的本质是回到BTree 的物理有序性。10.1 自增主键顺序插入新 ID 总是最大插入位置在 BTree 最右侧页尾部。页满时申请新页追加末尾无需移动数据 →无页分裂写入性能极高。批量插入时会持有AUTO-INC表级锁直到语句结束。10.2 随机主键旧版 UUID新 ID 随机落在某页中间 →页分裂Page Split锁定该页X 锁申请新页原页后半数据搬移到新页修改前后页指针及父节点索引可能级联分裂释放锁灾难后果写入放大、锁竞争、空间碎片、树变高、Change Buffer 失效。10.3 破解方案方案设计适用场景自增主键 UUID 二级索引主键BIGINT AUTO_INCREMENT业务 UUID 加唯一二级索引存量老系统无法改动主键UUID v7 / Snowflake 做主键前 48 位为毫秒时间戳整体趋势递增新建分布式系统二级索引也会分裂但伤害小得多只存(UUID, 主键)约 30 字节/条16KB 页存约530 条分裂频率仅为聚簇索引的 1/30。注意Snowflake/UUID v7 的趋势递增不等于严格连续偶尔触发少量数据移动但绝无级联分裂。需防范NTP 时钟回拨导致 ID 变小引发分裂。核心速查表EXPLAIN 12 列关注等级列名关注等级说明type高访问类型ALL index range ref constkey高实际使用索引key_len高用了几列rows高预估扫描行数Extra高Using index / filesort / wherepossible_keys中候选索引filtered中过滤后剩余百分比执行流程一句话Index Key 树形定位 → Index Filter ICP过滤 → 回表 → Table Filter Server过滤 → 返回第一性原理理解 BTree 的物理存储结构叶子节点存数据、双向链表串联是掌握 MySQL 调优的根基。调优的本质就是让查询尽可能少地遍历 BTree 叶子节点并尽可能减少随机回表 I/O。