Skip to content

4.1-5 B+ 树索引、聚簇索引与页结构 ​

所属:04 数据存储 / 4.1 MySQL 分级:A 状态:◐ 初稿 关联:4.1-1、4.1-3、4.1-6、4.1-7、4.1-8、4.1-10、4.1-12

  • 题型:原理 / 结构 / 调优

  • 一句话结论:InnoDB 用 B+ 树组织索引,表数据本身就是主键的聚簇索引(索引组织表);二级索引叶子只存“索引键 + 主键”,查非覆盖列要回表。B+ 树的每个节点落在一个 16KB 页上,页内用单向记录链 + 稀疏页目录组织,同层页用双向链表连接;树高通常 3–4 层,一次点查主要是“几次页访问 + 可能的回表随机 IO”,索引设计就是围绕减少页访问和随机 IO 做取舍。

  • 分层要点:

    1. 为什么是 B+ 树:磁盘按页读,索引结构要“矮胖、扇出大、顺序扫描友好”。B+ 树非叶子只存键和子页号,同样 16KB 能放更多键,扇出可达数百到上千,3–4 层覆盖千万到十亿级行;所有数据在叶子,查询路径长度稳定;叶子用双向链表串联,范围扫描和排序只需顺着叶子走。B 树数据分散在所有节点,范围扫描要跨层回溯;哈希不支持范围和排序;跳表是内存结构,磁盘局部性差;LSM 写友好但读放大、压缩成本高。
    2. 页是 B+ 树的物理单位:InnoDB 页默认 16KB(innodb_page_size 初始化后不可改),B+ 树的一个节点就是一个页。页类型包括 INDEX、UNDO、INODE、IBUF_BITMAP、FSP_HDR、SDI、BLOB 等;索引页头的 PAGE_LEVEL 为 0 表示叶子,根页页号固定,树长高时根页不变、层级增加。
    3. 页内结构:FIL Header(38B,页号/页类型/LSN/前后页指针/checksum)→ Page Header(56B,槽数、堆顶、记录数、最大事务 id、层级、索引 id 等)→ Infimum/Supremum(26B 伪记录)→ User Records → Free Space → Page Directory → FIL Trailer(8B)。记录之间用 next_record 单向链表连接,页目录用稀疏槽位(每组 4–8 条,n_owned 最大 8)支持页内二分。
    4. 记录格式:COMPACT/DYNAMIC 行格式依次是变长字段长度列表、NULL 位图、5 字节记录头(4b info bits 含 min_rec/deleted 标记、4b n_owned、13b heap_no、3b record_type、2B next_record 相对偏移)、列数据。聚簇索引叶子记录额外有 DB_TRX_ID(6B)、DB_ROLL_PTR(7B);二级索引记录没有这两个隐藏列。超长 VARCHAR/TEXT/BLOB 在 DYNAMIC 下用 20B 指针溢出到 off-page,单行页内上限约 8126B。
    5. 聚簇索引与二级索引:聚簇索引按主键组织,叶子存整行;非叶子存“主键 + 子页号”。没有主键时选第一个非空唯一索引,再没有就用 6B DB_ROW_ID。二级索引叶子存“索引键 + 主键”,按 (key, PK) 排序;查非索引列要拿主键回聚簇索引再查一次,这就是回表。主键越长,所有二级索引越大。
    6. 查询路径与成本:从根页开始,每层在页内二分定位子页,到叶子后再用页目录二分 + 槽内链表扫描找到记录;等值查询是 O(树高),范围查询定位起点后沿叶子链表扫描。回表是随机 IO,优化手段是覆盖索引(不回表)、ICP(在索引层过滤,减少回表)、MRR(按主键排序批量回表);Using index、Using index condition、Using MRR 是 EXPLAIN 里的关键信号。
    7. 页分裂、合并与写放大:页满时插入触发分裂,随机插入通常按约 50/50 拆,顺序插入会偏向右侧、让左页保持高填充;父页可能继续分裂,树长高。删除后页利用率低于 MERGE_THRESHOLD(默认 50%)会尝试与兄弟页合并。随机主键(UUID)会造成频繁分裂、页填充率低、二级索引膨胀;自增或趋势递增主键能把插入集中到最右页。
    8. 索引设计与优化器:联合索引遵循最左前缀;范围条件会截断后续列的有序利用,但 ICP 仍可在索引层过滤;低选择性列单独建索引收益有限。优化器依赖统计信息(持久化统计、采样页数、基数)和成本模型(server_cost/engine_cost)选索引;统计过期、采样误差、回表成本估算、函数/隐式转换都会导致选错,处理手段是 ANALYZE TABLE、改写 SQL、加更合适的联合索引,FORCE INDEX 只做短期兜底。
  • 图/流程:

text
B+ 树(3 层,聚簇索引):
                 [ root page · level=2 ]
                  /        |         \
          [branch · 1] [branch · 2] ...
            /     \        /    \
       [leaf] → [leaf] → [leaf] → [leaf]   ← 同层双向链表
        整行     整行     整行     整行
text
页(16KB)布局:
+--------------------------------+
| FIL Header (38B)               |  页号/类型/LSN/prev/next/checksum
+--------------------------------+
| Page Header (56B)              |  n_dir_slots/heap_top/n_recs/level/index_id...
+--------------------------------+
| Infimum + Supremum (26B)       |  页内伪记录
+--------------------------------+
| User Records                   |  实际记录,next_record 单向链表
+--------------------------------+
| Free Space                     |
+--------------------------------+
| Page Directory                 |  稀疏槽位,n_owned 分组
+--------------------------------+
| FIL Trailer (8B)               |  checksum/LSN 低 32 位
+--------------------------------+
text
聚簇索引 vs 二级索引:
聚簇索引叶子: [ PK | trx_id | roll_ptr | 其他列... ]  → 整行
二级索引叶子: [ idx_col | PK ]                        → 非覆盖列需回表

SELECT * FROM t WHERE name='x':
  idx_name → 拿到 PK → 聚簇索引再查 → 返回整行
SELECT id, name FROM t WHERE name='x':
  idx_name 已含 name + PK → 覆盖索引,不回表
text
一次点查:
  root(通常常驻 BP)
    → 非叶子页:separator key 二分 → child page no
    → 叶子页:page directory 二分 → 槽内 next_record 扫描
    → 命中记录
  非覆盖查询:再拿 PK 走一次聚簇索引(回表)
text
页分裂:
随机插入(UUID):
  [ 50% | 60% | 70% | 80% ] + 新记录
  → 页满 → 申请新页 → 约 50/50 拆分 → 父页插入 separator
  → 填充率低、碎片多、写放大

顺序插入(自增):
  [ 10% | ... | 90% ] + 新记录
  → 分裂点靠右 → 左页保持高填充 → 新记录进右页
  • 版本/源码锚点:

    • MySQL 5.6:ICP(Index Condition Pushdown)、MRR、BKA。
    • MySQL 5.7:optimizer cost tables(server_cost/engine_cost);innodb_stats_persistent 默认 ON;5.7.7 起 innodb_large_prefix 默认 ON。
    • MySQL 8.0:innodb_default_row_format=DYNAMIC;降序索引、不可见索引;8.0.13 skip scan、函数索引;8.0.18 EXPLAIN ANALYZE、hash join;8.0.12 instant add column。
    • 关键默认值:innodb_page_size=16K、innodb_fill_factor=100、MERGE_THRESHOLD=50、innodb_stats_persistent_sample_pages=20、innodb_stats_transient_sample_pages=8、eq_range_index_dive_limit=200(8.0,5.7 为 10)、range_optimizer_max_mem_size=8MB。
    • 成本常数(8.0):io_block_read_cost=1.0、memory_block_read_cost=0.25、row_evaluate_cost=0.1、key_compare_cost=0.05;5.7 的内存读/行比较更贵。
    • 源码:storage/innobase/btr/btr0btr.cc(btr_page_split_and_insert、btr_page_split、btr_root_raise_and_insert)、btr/btr0cur.cc(btr_cur_search_to_nth_level、btr_cur_optimistic_insert、btr_cur_pessimistic_insert)、btr/btr0pcur.cc、page/page0page.cc(page_cur_search_with_match、page_dir_split_slot)、page/page0cur.cc、rem/rem0rec.cc(rec_get_offsets)、dict/dict0dict.cc(dict_index_get_n_unique_in_tree)、dict/dict0stats.cc(统计信息)、row/row0sel.cc(row_search_mvcc)、handler/ha_innodb.cc(index_read_map)、sql/opt_range.cc、sql/opt_costmodel.cc、sql/opt_explain.cc。
  • B+ 树 vs B 树 / 哈希 / LSM:

维度B+ 树B 树哈希LSM
数据位置全部在叶子所有节点哈希桶memtable + SSTable
非叶子键 + 子页号键 + 数据 + 指针——
范围/排序叶子链表,顺序扫描中序跨层不支持多路归并,读放大
点查O(log n),路径稳定O(log n)O(1) 平均,冲突退化O(log n) + Bloom
写原地更新 + 分裂原地原地追加写 + compaction
典型InnoDB内存索引/部分 DBMEMORY/NDBRocksDB/HBase
  • 页结构:
区域大小关键内容作用
FIL Header38Bpage no、type、LSN、prev/next、checksum页定位、同层链表、恢复校验
Page Header56Bn_dir_slots、heap_top、n_heap、free、garbage、n_recs、max_trx_id、level、index_id页内管理
Infimum/Supremum26B最小/最大伪记录链表边界
User Records变长行/索引记录数据
Free Space变长未分配空间插入
Page Directory变长稀疏槽 + n_owned页内二分
FIL Trailer8Bchecksum、LSN 低 32 位完整性
  • 聚簇索引 vs 二级索引:
维度聚簇索引二级索引
叶子内容整行(含隐藏列)索引键 + PK
排序PK(key, PK)
一个表1 个N 个
查询直接返回行非覆盖列需回表
唯一性PK 唯一唯一索引约束;非唯一允许重复
大小行大小键 + PK,主键越长越大
更新代价改 PK 代价高改索引列 = delete-mark + insert
  • 索引类型与边界:
类型要点适用边界
聚簇索引表即索引主键/范围随机 PK 会分裂
二级索引key + PK非主键条件回表
唯一索引唯一约束业务唯一键仍需回表取列;NULL 可重复
联合索引多列排序多条件/排序最左前缀;范围截断
前缀索引col(n)长字符串不能覆盖完整值、不能用于 ORDER BY
降序索引8.0混合 ASC/DESC纯逆序普通索引可 backward scan
函数索引8.0.13 隐藏生成列表达式查询写法要匹配
不可见索引8.0安全下线优化器忽略
全文/空间倒排/R 树文本/地理非 B+ 树
  • 关键参数:
参数默认/常用作用调优提示
innodb_page_size16KB页大小初始化后不可改;影响行上限/树高
innodb_default_row_formatDYNAMIC行格式影响溢出/压缩/索引键长度
innodb_fill_factor100索引重建时页填充率80–90 可预留增长空间
MERGE_THRESHOLD50页合并阈值频繁删改可评估
innodb_stats_persistentON持久化统计避免重启后统计漂移
innodb_stats_persistent_sample_pages20采样页数越大越准、ANALYZE 越慢
innodb_stats_transient_sample_pages8非持久化采样页数临时/特殊表
innodb_stats_auto_recalcON自动重算统计约 10% 行变化触发
eq_range_index_dive_limit200(8.0)/10(5.7)IN 列表超过阈值改用统计大 IN 列表估算变粗
range_optimizer_max_mem_size8MBrange 优化内存上限超限退化为全表/索引扫描
optimizer_switch多开关ICP/MRR/skip scan 等按版本和场景验证
innodb_online_alter_log_max_size128MBOnline DDL 期间 DML 日志超限 DDL 失败
  • 诊断命令/视图:
命令/视图看什么用途
SHOW INDEX FROM tCardinality、列顺序快速看索引和基数
mysql.innodb_index_statsn_diff_pfx01、n_leaf_pages、size统计信息细节
mysql.innodb_table_statsn_rows、clustered_index_size表级统计
EXPLAIN / FORMAT=JSON/TREEkey、key_len、rows、filtered、Extra执行计划
EXPLAIN ANALYZE实际行数/时间(8.0.18)估算 vs 实际
optimizer_trace候选索引成本为什么选错
information_schema.INNODB_TABLES/INDEXES表/索引元数据页数、列
INNODB_BUFFER_PAGEINDEX_ID、PAGE_TYPE热点页、页分布
SHOW ENGINE INNODB STATUS锁/事务/IO结合锁等待看
  • 深挖点(面试官常追的 6 个):

    1. B+ 树节点就是页,不是一行:树高按“非叶子扇出 × 叶子行数”估算;BIGINT 主键、1KB 行、16KB 页,3 层约千万到两千万行,4 层到十亿级。根页通常常驻 Buffer Pool,所以一次点查实际物理 IO 往往只有 1–2 次。
    2. 页目录不是每行一个槽:每 4–8 条记录一组,n_owned 记录组内条数;先二分槽位,再在组内沿 next_record 线性扫描,最多 8 条。这样既省目录空间,又保证页内查找接近二分。
    3. 聚簇索引不是“额外建的索引”:表本身就是按主键组织的 B+ 树,叶子就是行;二级索引只是“键 → 主键”的辅助结构。理解这一点才能解释回表、覆盖索引、主键长度放大二级索引。
    4. 回表、覆盖、ICP、MRR 的边界:覆盖索引消除回表;ICP 在索引层过滤、减少回表次数,但不改变索引定位;MRR 把回表主键排序后批量读,降低随机 IO,但受 mrr_cost_based 和排序成本影响。四者不是一回事。
    5. 页分裂与主键顺序:随机插入会 50/50 分裂、填充率低、碎片多;自增/趋势递增把插入集中到最右页,分裂点靠右,页更满。UUID 主键还会让所有二级索引变大,BP 能缓存的索引页变少。
    6. 优化器选错索引的根因:统计信息是采样估算,不是精确值;回表成本、范围行数、ORDER BY LIMIT、函数/隐式转换都会影响成本。optimizer_trace 看候选成本,ANALYZE TABLE、改写 SQL、补联合索引是正解,FORCE INDEX 只是临时止血。
  • 追问链:

    1. 为什么 MySQL 用 B+ 树而不是 B 树? → 非叶子只存键和子页号,扇出更大、树更矮;数据全在叶子,查询路径长度稳定;叶子链表让范围扫描和排序只需顺序走叶子。
    2. 为什么不用哈希索引? → 哈希只支持等值,不支持范围、排序、最左前缀;冲突多时退化。InnoDB 只有自适应哈希索引(AHI)做等值加速,不暴露用户哈希索引。
    3. 为什么不用跳表或红黑树? → 它们是内存结构,节点小、树高/扇出不适合磁盘页;随机指针跳转让页局部性变差,一次查询会产生更多随机 IO。
    4. B+ 树和 LSM 怎么选? → 读多、范围查询、事务型 OLTP 选 B+ 树;写多、追加写、能接受读放大和 compaction 的场景选 LSM。
    5. 一棵 B+ 树有多高,能存多少行? → 16KB 页、BIGINT 主键、约 1KB 行,非叶子扇出约 800–1000,叶子约 16 行;3 层约千万到两千万,4 层到十亿级,实际受填充率影响。
    6. 扇出怎么算? → 非叶子记录约 20B(键 + 4B 子页号 + 记录头/开销),16KB/20 ≈ 800;叶子按平均行大小算,行越大每页行数越少。
    7. B+ 树的节点和页是什么关系? → 一个节点就是一个页;PAGE_LEVEL=0 是叶子,根页页号固定,根分裂时页号不变、层级加一。
    8. 一个页里有什么? → FIL Header/Trailer、Page Header、Infimum/Supremum、User Records、Free Space、Page Directory;索引页还有 PAGE_LEVEL、PAGE_INDEX_ID、PAGE_MAX_TRX_ID。
    9. 5 字节记录头里有什么? → 4b info bits(含 min_rec/deleted 标记)、4b n_owned、13b heap_no、3b record_type、2B next_record 相对偏移;总共 40 bit = 5 字节。
    10. 页目录怎么做到页内快速查找? → 稀疏槽位每 4–8 条一组,先二分定位槽,再沿 next_record 在组内线性扫描,最多 8 条。
    11. Infimum/Supremum 是干什么的? → 页内最小/最大伪记录,作为记录链表的头尾边界,不存用户数据。
    12. next_record 是绝对地址吗? → 是相对当前记录位置的偏移;页内记录移动时只需调整偏移,不依赖绝对地址。
    13. 什么是聚簇索引? → 按主键组织、叶子存整行的 B+ 树;InnoDB 是索引组织表,表数据就在聚簇索引里。
    14. 没有主键时 InnoDB 怎么建聚簇索引? → 选第一个非空唯一索引;没有就用 6B DB_ROW_ID 生成隐藏聚簇索引。主键选择会影响所有二级索引大小。
    15. 主键为什么建议自增或趋势递增? → 插入集中到最右页,减少页分裂和碎片,提高页填充率;二级索引也更紧凑。雪花/ULID 等趋势递增方案是折中。
    16. UUID 做主键有什么问题? → 随机插入导致频繁分裂、页填充率低、写放大;36 字符/16B 比 BIGINT 8B 大,所有二级索引膨胀,BP 命中率下降。
    17. 二级索引叶子存什么? → 索引键 + 主键,按 (key, PK) 排序;不存 DB_TRX_ID/DB_ROLL_PTR,所以二级索引的 MVCC 判断常需回表(4.1-3)。
    18. 什么是回表? → 二级索引定位到主键后,再回聚簇索引查整行;查询列不被索引覆盖时必做。
    19. 回表一定慢吗? → 不一定,主键页可能命中 BP;但大量随机回表会放大 IO,优化器可能因此改走全表扫描,或选择覆盖索引。
    20. 什么是覆盖索引? → 查询所需列都在索引里(二级索引隐含包含 PK),不需要回表;EXPLAIN 出现 Using index。
    21. 联合索引的最左前缀是什么? → (a,b,c) 支持 a、a+b、a+b+c;跳过 a 一般不能用,8.0 的 skip scan 在特定低基数场景例外。
    22. 范围条件后面的列还能用吗? → 不能继续用于索引定位和排序,但 ICP 可以把索引列上的条件下推到存储引擎,在索引记录上先过滤,减少回表。
    23. ICP 是什么? → 5.6 引入的 Index Condition Pushdown,把 WHERE 中索引列条件下推到 InnoDB,在索引层过滤后再回表;EXPLAIN 显示 Using index condition。
    24. MRR 是什么? → Multi-Range Read,把待回表的主键排序后批量读聚簇索引,减少随机 IO;EXPLAIN 显示 Using MRR,受 mrr_cost_based 影响。
    25. index merge 说明什么? → 优化器把多个单列索引的结果合并(union/intersect);常见于缺少合适联合索引,通常应补联合索引而不是依赖它。
    26. 前缀索引长度怎么选? → 用 COUNT(DISTINCT LEFT(col,n))/COUNT(*) 找接近全列区分度的最小 n;前缀索引不能覆盖完整列值,也不能直接支持 ORDER BY。
    27. 索引基数/选择性怎么算? → COUNT(DISTINCT col)/COUNT(*),越高越适合 B+ 树索引;低选择性列(性别、状态)单独建索引收益有限。
    28. 统计信息怎么收集? → 采样索引页估算 n_diff_pfx,持久化在 mysql.innodb_index_stats;innodb_stats_persistent_sample_pages 控制采样量,ANALYZE TABLE 可手动更新。
    29. 为什么优化器会选错索引? → 统计过期/采样误差、回表成本估算、范围行数、ORDER BY LIMIT、函数或隐式转换、成本常数;用 optimizer_trace 看候选成本。
    30. FORCE INDEX 能长期用吗? → 不建议。数据分布变化后会反噬,优先改 SQL、补联合索引、更新统计;FORCE INDEX 只做短期兜底。
    31. 页分裂什么时候发生? → 叶子页没有足够空间插入时;父页也可能继续分裂;根页分裂时树长高一层。
    32. 页分裂有什么影响? → 随机写放大、页碎片、填充率低、BP 有效容量下降;顺序/趋势递增主键可显著缓解。
    33. innodb_fill_factor 和 MERGE_THRESHOLD 是什么? → 前者控制索引重建时的页填充率(默认 100),后者控制删除后页利用率低于阈值时尝试合并(默认 50%)。
    34. 索引键最大长度和行溢出? → DYNAMIC/COMPRESSED 下索引键最大 3072B,COMPACT/REDUNDANT 为 767B;联合索引最多 16 列。超长 VARCHAR/TEXT/BLOB 用 20B 指针溢出到 off-page,页内单行上限约 8126B。
    35. 降序索引、函数索引、不可见索引分别解决什么? → 降序索引支持混合 ASC/DESC 排序;函数索引(8.0.13)用隐藏生成列为表达式建索引;不可见索引用于安全下线,优化器默认忽略。
    36. 索引和锁是什么关系? → 锁定读沿索引加 next-key lock;没有合适索引会扫全表、锁大量记录和间隙,死锁概率上升(4.1-10)。
    37. 千万级大表怎么加索引? → 优先 Online DDL ALGORITHM=INPLACE, LOCK=NONE;高峰期用 gh-ost/pt-osc,控制 innodb_online_alter_log_max_size(4.1-12)。
    38. 深分页为什么慢? → LIMIT offset, n 要扫过前 offset 行再丢弃;用延迟关联(先取 PK 再回表)、游标/WHERE id > ?、覆盖索引优化。
    39. ORDER BY 什么时候能用索引? → 排序列是索引的最左前缀、方向一致(或 8.0 降序索引)、范围列之后的列不参与排序;否则走 filesort。ORDER BY ... LIMIT 还会受优化器 prefer_ordering_index 影响。
  • 生产案例/取舍(示例,替换成你的项目):

    • 订单表用 UUID 做主键,写入高峰页分裂严重、二级索引膨胀;改成趋势递增的 BIGINT 后,写放大和索引体积明显下降。
    • 用户表按手机号查询,直接对 20 位字符串建索引太大;用前缀索引 + 精确校验,或改用手机号哈希列,权衡区分度和覆盖能力。
    • 列表分页 LIMIT 1000000,20 越来越慢;改成延迟关联/游标分页后 RT 从秒级降到毫秒级。
    • 联合索引 (status, create_time) 因 status 基数太低,范围扫描行数大;调整顺序或补覆盖列后执行计划稳定。
    • 统计信息过期导致优化器选错索引,业务 RT 抖动;ANALYZE TABLE + 持久化统计 + 监控 innodb_index_stats 后恢复。
    • 大表直接 ALTER TABLE ADD INDEX 锁表;改用 Online DDL/gh-ost,在低峰期分批执行。
    • 二级索引建了 10 个,写入 TPS 上不去;删除冗余索引、合并联合索引后写放大下降。
    • 无索引的 UPDATE ... WHERE status=... 在 RR 下锁大量行和间隙,引发死锁;补索引并缩小事务后解决。
    • 主键用 64 位字符串,所有二级索引都变大、BP 命中下降;改 BIGINT 后内存利用率和 RT 改善。
    • 取舍:B+ 树换范围查询和稳定点查;聚簇索引换直接取行,代价是主键更新贵;覆盖索引换少回表,代价是索引变宽;自增主键换写性能,代价是 ID 可预测/分库分表需改造。
  • 易错点:

    • 认为 B+ 树的节点是一行数据,而不是一个 16KB 页。
    • 认为聚簇索引是额外创建的一个索引,而不是表本身。
    • 认为二级索引叶子存整行,忽略回表。
    • 认为回表一定是随机 IO,忽略 Buffer Pool 命中和 MRR。
    • 认为覆盖索引在任何情况下都完全不回表,忽略二级索引 MVCC 判断和前缀索引。
    • 认为联合索引任意列顺序都能用,忽略最左前缀。
    • 认为范围条件后面的列完全没用,忽略 ICP 仍可过滤。
    • 认为索引一定加速查询,忽略低选择性、小表、写多读少。
    • 认为索引越多越好,忽略写放大和优化器选择成本。
    • 认为 LIKE '%x%' 能走普通 B+ 树索引。
    • 认为列上套函数还能走索引,忽略 8.0 函数索引的例外。
    • 认为隐式类型转换不影响索引。
    • 认为 NULL 不能建索引;InnoDB 会索引 NULL,IS NULL 可用索引。
    • 认为唯一索引只能有一个 NULL;InnoDB 唯一索引允许多个 NULL。
    • 认为主键必须自增才最优,忽略趋势递增/雪花等方案。
    • 认为页分裂只影响插入,忽略碎片和后续查询/BP 效率。
    • 认为 OPTIMIZE TABLE 可以随时做,忽略锁、空间和主从延迟。
    • 认为统计信息实时准确,忽略采样误差和自动重算阈值。
    • 认为 FORCE INDEX 是长期优化方案。
    • 认为索引键越长越好,忽略 utf8mb4、主键长度和二级索引放大。
    • 认为分区表等于分库分表,忽略跨分区查询和全局索引问题。
  • 30 秒口述稿:

InnoDB 用 B+ 树做索引,表本身就是主键的聚簇索引,叶子存整行;二级索引叶子存“索引键 + 主键”,查非覆盖列要回表。B+ 树的节点就是 16KB 页,页内有记录链表和稀疏页目录,同层页用双向链表连起来;3–4 层就能覆盖千万到十亿级数据。点查是根到叶的几次页访问,回表是随机 IO,所以有覆盖索引、ICP、MRR。随机主键会导致页分裂和碎片,自增/趋势递增更好;优化器靠统计信息和成本模型选索引,选错时先看 optimizer_trace,再改 SQL/索引/统计。

  • 3 分钟展开顺序:结论(B+ 树 + 聚簇索引 + 页)→ 为什么 B+ 树(对比 B 树/哈希/LSM)→ 页结构与记录格式 → 聚簇索引/二级索引/回表 → 查询路径与树高/扇出 → 覆盖索引/ICP/MRR → 页分裂/合并/主键顺序 → 统计信息与成本模型/选错索引 → 索引设计边界与生产案例。