索引下沉设计指将高频索引页驻留内存并优化结构以减少磁盘随机IO，核心包括：构建覆盖索引避免回表、压缩B+树高度、冷热分离提升缓存命中、规避隐式转换与函数导致的索引失效。

索引下沉设计不是标准术语，而是工程实践中对“将高频访问的索引页优先保留在内存（如Buffer Pool）中，同时通过结构优化降低磁盘随机IO”的一种形象化描述。核心目标是让查询尽可能命中内存中的索引节点，避免回表或逐层下探时频繁触发磁盘随机读。

聚焦覆盖索引，减少回表随机IO

回表是随机IO的主要来源之一：当二级索引不包含查询所需全部字段时，需根据主键回到聚簇索引（如InnoDB的B+树叶子页）查找数据行，而这些主键分布往往离散，导致大量磁盘寻道。

• 对高频查询，显式创建覆盖索引（INCLUDE字段或联合索引包含SELECT列），使索引本身就能响应查询，彻底规避回表
• 例如：SELECT user_id, nickname, avatar FROM users WHERE status = 1 AND city = 'shanghai'，可建联合索引 (status, city, user_id, nickname, avatar)
• 注意索引宽度——过宽会降低缓存效率，需在覆盖性与体积间权衡

控制索引层级，压缩B+树高度

B+树每多一层，一次范围查询或等值查询就多一次磁盘IO（尤其首层非叶节点未命中时）。树高取决于扇区大小、键长、指针开销和填充因子。

• 使用更短的数据类型：主键优选BIGINT而非VARCHAR(36) UUID；索引字段避免冗余前缀（如email(50)而非email(255)）
• 定期分析并优化索引碎片：OPTIMIZE TABLE（MySQL）或VACUUM FULL（PostgreSQL）可重建索引、提升页密度
• 对写多读少场景，适当调高填充因子（如从50%→75%），减少分裂频次，长期维持更低树高

冷热分离：把稳定索引页“钉”进内存

并非所有索引页都值得常驻内存。将访问密集的“热”索引路径（如时间范围前缀、状态码前缀）与低频“冷”分支隔离，可显著提升Buffer Pool命中率。

• 利用分区表按时间/状态切分，使热点分区（如order_status = 'paid' AND create_time > '2025-01-01'）的索引独立加载，冷分区索引可被快速淘汰
• MySQL 8.0+ 支持SET GLOBAL innodb_buffer_pool_dump_pct = 75 + 定期innodb_buffer_pool_load_now，实现重启后热索引页快速预热
• 应用层配合：对确定性的高频查询路径（如首页推荐位ID列表），可预加载对应索引页范围（通过SELECT ... FOR UPDATE或空查触发载入）

避免隐式转换与函数索引陷阱

看似合理的SQL写法，可能让已有索引完全失效，被迫全索引扫描甚至全表扫描，引发大量随机IO。

• 禁止在WHERE条件中对索引列使用函数或表达式：WHERE YEAR(create_time) = 2025 → 改为 WHERE create_time >= '2025-01-01' AND create_time
• 确保参数类型与索引列严格一致：WHERE user_id = '123'（字符串）vs user_id INT，会导致隐式转换，索引失效
• 谨慎使用函数索引（MySQL 8.0+/PostgreSQL）：虽能支持表达式查询，但其B+树键值已变形，缓存局部性下降，且维护开销更高