索引下沉设计指将高频索引页驻留内存并优化结构以减少磁盘随机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 > '2024-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) = 2024→ 改为WHERE create_time >= '2024-01-01' AND create_time - 确保参数类型与索引列严格一致：
WHERE user_id = '123'（字符串）vsuser_id INT，会导致隐式转换，索引失效 - 谨慎使用函数索引（MySQL 8.0+/PostgreSQL）：虽能支持表达式查询，但其B+树键值已变形，缓存局部性下降，且维护开销更高
