InnoDB 存储引擎与 B+ 树索引
001 InnoDB 使用聚簇索引组织表数据，聚簇索引的叶子节点存储的是什么？
难度： 基础
- A. 完整的一行数据（含主键），表数据与主键索引同构
- B. 只存储主键值和指向行数据的堆地址指针
- C. 只存储二级索引的键值，不包含任何行数据
- D. 只存储该行的 undo 版本链，不存储最新值
查看答案与解析
正确答案A
正确原因： InnoDB 主键聚簇索引的叶子页直接保存整行记录，因此按主键查找通常只需一次索引定位。
关键边界： 没有显式主键时 InnoDB 会优先选唯一非空索引，再没有则生成隐藏 rowid 作为聚簇键。
错误选项辨析： 堆表才用行指针，InnoDB 的二级索引叶子存主键值而非指针，undo 版本链位于回滚段而非索引叶子。
002 关于 MyISAM 与 InnoDB 的差异，下列说法正确的是？
难度： 基础
- A. InnoDB 支持事务、外键与行级锁，MyISAM 只支持表级锁且不支持事务
- B. InnoDB 不支持崩溃恢复，MyISAM 崩溃后必然丢数据
- C. MyISAM 支持行级锁，因此写并发高于 InnoDB
- D. InnoDB 只能使用表级锁，不能对行加锁
查看答案与解析
正确答案A
正确原因： InnoDB 是 MySQL 默认存储引擎，支持事务、外键、崩溃恢复与行级锁；MyISAM 无事务、无外键，锁粒度是整表。
关键边界： MyISAM 的全文索引与压缩表仍有历史适用场景，但 MySQL 8.0 中系统表已全部改用 InnoDB。
错误选项辨析： 三个错误选项都把两者的能力说反，属于对存储引擎特性的常见记忆混淆。
003 二级索引查询出现“回表”时，数据库实际做了什么？
难度： 基础
- A. 把二级索引整页复制到内存后重新执行一次 SQL
- B. 先在二级索引中找到主键值，再拿主键去聚簇索引定位完整行
- C. 直接扫描全表并把结果与二级索引做笛卡尔积
- D. 丢弃二级索引结果，改用优化器随机猜测的行地址
查看答案与解析
正确答案B
正确原因： 二级索引叶子只保存索引列与主键，SELECT 需要其他列时必须回聚簇索引再取一次，这就是回表。
关键边界： 若查询所需列全部包含在索引内（覆盖索引），优化器可跳过回表。
错误选项辨析： 回表是确定性的主键查找，不是重新执行、全表扫描或随机猜测。
004 覆盖索引（covering index）对查询性能的贡献是什么？
难度： 基础
- A. 索引包含了表中全部列，任何查询都不需要扫描
- B. 查询所需列都包含在索引中，避免回表从而减少随机 I/O
- C. 覆盖索引不占存储空间，且不需要随写入维护
- D. 只有 COUNT(*) 才能使用覆盖索引，普通查询不行
查看答案与解析
正确答案B
正确原因： 当 SELECT 的列与 WHERE、ORDER BY 所需列都在某个二级索引内时，直接扫描索引即可返回结果。
关键边界： 覆盖索引会增加写入维护成本与存储占用，并非越多越好，要按真实查询模式取舍。
错误选项辨析： 覆盖索引指查询被索引覆盖，不等于包含所有列，也不能免除索引维护代价。
005 InnoDB 磁盘页（page）的默认大小是多少？
难度： 基础
- A. 4KB
- B. 8KB
- C. 16KB
- D. 64KB
查看答案与解析
正确答案C
正确原因： InnoDB 默认 page 大小为 16KB，B+ 树按页读写，一次磁盘 I/O 至少读入一页。
关键边界： 可通过 innodb_page_size 配置为 4KB 或 8KB，但只能在初始化表空间时决定，之后不可更改。
错误选项辨析： 8KB 是 PostgreSQL 的默认块大小，4KB 常见于操作系统页，64KB 常用于大页配置，均非 InnoDB 默认值。
006 InnoDB 为什么选择 B+ 树而不是哈希表作为默认索引结构？
难度： 基础
- A. 哈希表无法持久化到磁盘，因此数据库都不能使用
- B. B+ 树每个节点只存放一条记录，定位速度最慢
- C. B+ 树有序，天然支持范围查询与排序，哈希只擅长等值匹配
- D. B+ 树不支持任何等值查询，只能做全表扫描
查看答案与解析
正确答案C
正确原因： B+ 树叶子节点按序相连，区间扫描只需沿链表顺序读取，同时兼顾等值与范围查询。
关键边界： InnoDB 仍提供自适应哈希索引加速热点等值访问，但它是内部优化，不替代 B+ 树。
错误选项辨析： 哈希表可以持久化（如 MySQL 的 MEMORY 引擎），B+ 树节点存放多条记录且等值查询同样高效。
007 关于 InnoDB 自适应哈希索引（Adaptive Hash Index），正确的是？
难度： 基础
- A. 需要 DBA 用 CREATE INDEX 手动创建并指定存储位置
- B. 会立即替换所有 B+ 树索引，范围查询也因此更快
- C. 只能建立在主键上，二级索引完全无法受益
- D. 由 InnoDB 根据热点访问自动为 B+ 树构建的索引，只加速等值查询
查看答案与解析
正确答案D
正确原因： 自适应哈希索引是 InnoDB 在内存中按访问频率自动构建的哈希索引，命中时把 B+ 树查找变成哈希查找。
关键边界： 它只对等值访问有效，且占用缓冲池内存，极端场景下可关闭。
错误选项辨析： 自适应哈希索引无需手工创建，不替换 B+ 树，也不限于主键，只是加速热点等值路径。
008 Change Buffer（变更缓冲）主要用于优化什么操作？
难度： 基础
- A. 缓存主键聚簇索引的全部写操作，包括整行更新
- B. 缓存 SELECT 查询结果，让重复查询直接命中内存
- C. 只对唯一索引生效，普通二级索引不参与
- D. 缓存对二级索引页的插入、删除与更新，合并后再落盘，减少随机 I/O
查看答案与解析
正确答案D
正确原因： 二级索引页不在缓冲池时，写操作先记入 change buffer，之后由后台或读取触发合并回原页，避免每次写都随机读盘。
关键边界： 唯一索引因需要立即检查唯一性，通常不使用 change buffer。
错误选项辨析： 聚簇索引写必须直接落页，查询结果缓存是另一个概念（MySQL 8.0 已移除查询缓存），唯一索引正是被排除的场景。
009 全文索引（FULLTEXT）适合解决哪类查询？
难度： 基础
- A. 对文本内容分词后做包含与相关性搜索，配合 MATCH AGAINST 使用
- B. 对整数主键做等值查找以加速 JOIN
- C. 对 JSON 字段做路径提取与范围过滤
- D. 对日期列做区间统计并替代普通二级索引
查看答案与解析
正确答案A
正确原因： FULLTEXT 索引面向自然语言文本，支持分词、词频统计与相关性排序。
关键边界： 中文全文检索需要合适的分词器或 ngram 配置，且全文索引不用于常规等值与范围查询。
错误选项辨析： 整数等值、JSON 路径、日期区间应分别使用 B+ 树索引、多值索引或普通二级索引。
010 关于 InnoDB 行格式对变长字段与 NULL 的处理，正确的是？
难度： 基础
- A. 变长字段通过长度列表记录实际长度，NULL 用 NULL 标志位标记，不占额外数据空间
- B. VARCHAR(n) 在行内固定占用 n 个字节，短值也会浪费
- C. NULL 与空字符串在行内完全等价，存储开销相同
- D. 行内不保存任何记录头信息，全靠列定义推断
查看答案与解析
正确答案A
正确原因： InnoDB 记录头包含变长字段长度列表与 NULL 位图，变长列只保存实际字节数。
关键边界： 行格式有 COMPACT、DYNAMIC 等差异，超长字段可能溢出存储到溢出页。
错误选项辨析： VARCHAR 不固定占用声明长度，NULL 与空字符串的语义和存储都不同，记录头元数据始终存在。
011 把主键设计成 UUID 随机字符串，相比自增整数主键，对 InnoDB 写入的主要影响是？
难度： 进阶
- A. UUID 字符串比整数索引占用更少空间，写入更快
- B. 主键随机导致插入位置分散，频繁页分裂与页碎片，产生写入放大
- C. 自增主键会造成索引顺序倒排，查询必须全表扫描
- D. 主键顺序与数据页布局无关，选择哪种都等价
查看答案与解析
正确答案B
正确原因： B+ 树按主键顺序组织数据，随机主键使新记录插入到任意页面，触发页分裂并产生碎片。
关键边界： 若业务必须用 UUID，可考虑有序 UUID，或保留自增主键、把 UUID 作为二级唯一键。
错误选项辨析： 字符串占空间更大，自增顺序插入更友好，主键顺序直接决定数据页布局与写入路径。
012 InnoDB 中“页分裂”发生在什么时候？
难度： 进阶
- A. 删除记录时自动压缩相邻页并合并索引
- B. 向已写满的索引页插入记录时，需要把记录分散到多个页面重新组织
- C. 查询语句超过超时阈值时自动触发
- D. 只有 MyISAM 表才会出现，InnoDB 不会分裂
查看答案与解析
正确答案B
正确原因： B+ 树节点容量有限，顺序打乱的插入会迫使满页拆分为两页，并调整父节点指针。
关键边界： 页合并发生在删除导致页使用率过低时，与页分裂是两个方向相反的过程。
错误选项辨析： 删除触发的是页合并，查询不会触发分裂，InnoDB 聚簇索引同样会发生页分裂。
013 对 SELECT * FROM t ORDER BY id LIMIT 100000, 20 这类深分页，常见的优化是？
难度： 进阶
- A. 把 LIMIT 值改大，让一次查询返回更多行
- B. 删除 ORDER BY 子句，让优化器自由返回任意行
- C. 先只从索引取主键做分页，再回表取 20 条完整行（延迟关联）
- D. 为所有列建立覆盖索引，使索引体积小于数据行
查看答案与解析
正确答案C
正确原因： 深分页的代价在于必须先扫描并丢弃前 100000 行，延迟关联先缩小主键范围再取数。
关键边界： 业务上也可改用基于上一页最后 id 的键集分页，避免大 offset。
错误选项辨析： 改大 LIMIT、去掉排序或建全列索引都不能解决扫描已丢弃行的问题，全列索引反而非常庞大。
014 为什么高频查询推荐使用覆盖索引？
难度： 进阶
- A. 覆盖索引使写入完全免费，可以无限叠加
- B. 覆盖索引无需维护，建立后永不失效
- C. 索引已包含所需列，免回表与部分排序/临时表，减少随机 I/O 与锁访问
- D. 覆盖索引只能用于 SELECT，DML 一律自动全表扫描
查看答案与解析
正确答案C
正确原因： 覆盖索引让执行计划从“索引加回表”变成纯索引扫描，显著降低随机读开销。
关键边界： 每增加一个索引都会放大写入与存储成本，必须按真实查询模式权衡。
错误选项辨析： 索引维护成本始终存在，覆盖索引也可能被优化器放弃，DML 同样会使用索引定位记录。
015 关于 InnoDB 缓冲池（Buffer Pool），正确的是？
难度： 进阶
- A. 只缓存查询结果集，不缓存表数据页
- B. 缓冲池内容在重启后必然全部丢失，redo log 也因此无用
- C. 缓冲池越大越安全，无需监控命中率与刷盘频率
- D. 缓存数据页与索引页，使用类 LRU 算法管理，脏页由后台线程刷盘
查看答案与解析
正确答案D
正确原因： 缓冲池按页缓存 InnoDB 数据与索引，写操作先修改内存中的页，脏页由后台异步刷盘。
关键边界： 缓冲池配置过大可能挤占系统内存引发交换，需要结合命中率与刷盘指标调优。
错误选项辨析： 缓冲池缓存的是页而非结果集，redo log 正是为保证崩溃后能恢复未刷盘的修改。
016 Change Buffer 对唯一索引与普通二级索引的处理有何不同？
难度： 进阶
- A. 唯一索引比普通索引更适合 change buffer，因为数据量更小
- B. 两者都会被 change buffer 永久缓存，从不合并
- C. change buffer 缓存的是聚簇主键索引页的修改
- D. 普通二级索引可先缓存再合并；唯一索引需立即检查唯一性，通常直接读写原页
查看答案与解析
正确答案D
正确原因： 唯一性约束要求插入或更新时立刻确认没有冲突记录，无法延迟，因此很少受益于 change buffer。
关键边界： change buffer 的合并由后台线程或读取触发，内存占用受 innodb_change_buffer_max_size 控制。
错误选项辨析： 唯一索引被明确排除，合并一定会发生，change buffer 只作用于二级索引。
017 大量并发插入自增列时，MySQL 8.0 默认的自增锁行为是？
难度： 实战
- A. 使用 innodb_autoinc_lock_mode=2，批量插入时不再持表级 AUTO-INC 锁，性能更好但自增值可能不连续
- B. 自增锁在事务提交前一直持有，任何插入都必须串行
- C. 8.0 已禁止使用自增列，必须手动生成主键
- D. 事务回滚后，已分配的自增值会回退给后续事务复用
查看答案与解析
正确答案A
正确原因： MySQL 8.0 默认 autoinc_lock_mode=2（交错模式），简单插入可在不锁表的情况下分配自增值。
关键边界： 该模式下自增值可能因预分配出现空洞，主从复制需使用 row 格式 binlog 保证一致。
错误选项辨析： 锁模式 0 或 1 才存在持续锁表，自增列并未被禁用，已分配值不会因回滚被复用。
018 生产环境对大表执行 ALTER TABLE 加索引，最稳妥的认识是？
难度： 实战
- A. 所有 ALTER TABLE 都能瞬间完成且完全不占磁盘
- B. 8.0 多数 DDL 支持 INSTANT/INPLACE 算法，但仍可能占额外空间、产生复制延迟，需评估窗口
- C. 加索引必须重建整张表所有数据，8.0 也无例外
- D. 在线 DDL 不允许在从库执行，只能在主库操作
查看答案与解析
正确答案B
正确原因： INPLACE、INSTANT 算法显著缩短锁表时间，但部分操作仍会重建表或暂存日志，空间与复制延迟要监控。
关键边界： 具体算法取决于操作类型，例如加列可能 INSTANT，重建索引可能 INPLACE 但需要额外空间。
错误选项辨析： 并非所有 DDL 都零成本，也并非都要全量重建，从库执行 DDL 同样受支持但需评估延迟。
019 设计 InnoDB 主键时，比较合理的原则是？
难度： 基础
- A. 主键越随机越好，可以让数据分布更均匀
- B. 主键必须使用有业务意义的列，如身份证或手机号
- C. 选择短小、唯一、趋势递增且业务上不轻易变化的列或代理键
- D. 主键可以允许 NULL，只要业务层保证不重复
查看答案与解析
正确答案C
正确原因： 短且递增的主键让聚簇索引更紧凑并减少页分裂，业务无关的代理键避免后续修改。
关键边界： 若业务键的唯一性由唯一约束保障即可，主键优先考虑稳定性与插入性能。
错误选项辨析： 随机主键造成碎片，业务主键可能变化且通常更长，InnoDB 主键列不允许 NULL。
020 InnoDB 二级索引的叶子节点为什么保存主键值而不是行指针？
难度： 进阶
- A. 主键值总是比行指针占用更少字节
- B. 二级索引必须保存完整行数据才能支持回表
- C. 行指针在 MySQL 中不存在，只有 PostgreSQL 才使用
- D. 聚簇索引页分裂移动后主键值不变，二级索引无需更新，保持稳定
查看答案与解析
正确答案D
正确原因： 若保存行指针，聚簇索引页重排会让指针失效，需要级联更新所有二级索引；保存主键则索引自洽。
关键边界： 这正是 InnoDB 二级索引回表要用主键查找的原因，也是覆盖索引能省一次查找的基础。
错误选项辨析： 主键不一定比指针小，二级索引不存完整行，堆表（如 PostgreSQL）才普遍使用行指针或物理地址。