03.索引高效使用策略
文章目录
问题: 索引高效使用策略：请综合阐述覆盖索引（Covering Index）、索引下推（ICP）和最左前缀匹配原则，它们分别如何减少回表次数、减少Server层与引擎层交互，从而提升查询吞吐量？
👇 点击展开/折叠 面试背诵模板（建议先背这个）
一、覆盖索引（Covering Index）
定义：查询所需的所有字段（SELECT子句中的所有列）都包含在索引中，MySQL只需扫描索引即可返回数据，无需回表到聚簇索引读取完整行。
如何减少回表次数：执行 SELECT id, name FROM user WHERE age = 25，若联合索引 (age, name, id) 存在且age为查询条件，由于索引叶子节点已存储 age、name、id（主键自动包含在二级索引中），MySQL直接从索引返回结果，回表次数降为0。回表本质是随机I/O，覆盖索引将其转化为顺序I/O（仅扫描索引页），查询吞吐量显著提升。
设计要点：在创建联合索引时，将查询频繁出现的字段放入索引中，尤其是SELECT子句中的字段，且合理控制索引字段数量（避免过度膨胀）。对于高频查询，可牺牲部分写入性能换取读性能。
二、索引下推（Index Condition Pushdown，ICP）
定义：MySQL 5.6+引入，将Server层中与索引相关的WHERE条件（index condition）下推到存储引擎层，在引擎层检索索引时提前过滤，只把满足条件的行返回给Server层。
如何减少Server层与引擎层交互：
- ICP关闭（传统流程）：存储引擎通过索引定位到所有
age=25的行，逐行回表读取完整行，再交给Server层进行name LIKE '%张%'过滤，回表次数多，Server层接收大量无效数据。 - ICP开启：存储引擎在索引遍历时直接评估
name LIKE '%张%'（若该字段在索引中），直接跳过不满足条件的行，仅对满足所有索引相关条件的行进行回表，回表次数显著减少，Server层仅接收有效数据。
适用条件：ICP适用于二级索引中的范围查询和部分模糊匹配（如LIKE前缀模糊），且未启用 FORCE INDEX 等控制选项。
三、最左前缀匹配原则
定义：MySQL联合索引 (a, b, c) 按照字段顺序构建B+树，查询条件必须从索引最左侧字段开始连续匹配，索引才能有效发挥作用。如 WHERE a=1 AND b=2 可用；WHERE b=2 跳过a，索引基本失效（type为ALL或index）。
如何减少回表：遵循最左原则，确保查询条件能够使用联合索引的大范围过滤，将扫描行数降到最低，从而减少因扫描大量行产生的回表操作。设计联合索引时，将等值查询的字段放在左侧，范围查询的字段放在右侧（如 (gender, age)，gender 等值，age 范围）。
常见误区：
- 只要包含最左字段，索引即可使用（
WHERE a=1 AND c=3可用a列部分，但c无法被索引，需回表过滤）。 - 范围查询（
>、<、BETWEEN）会截断后续字段，如WHERE a>1 AND b=2中b字段无法使用索引（a范围扫描后，b无法排序）。
四、三者协同提升吞吐量的实践策略
| 策略 | 核心收益 | 适用场景 |
|---|---|---|
| 覆盖索引 | 消除回表，降低随机I/O | 高频查询覆盖特定字段组合 |
| 索引下推 | 减少回表次数 + 减少Server层数据交互 | 多条件筛选（含模糊/范围查询） |
| 最左前缀匹配 | 保证索引有效 + 减少扫描范围 | 联合索引设计遵循查询习惯 |
组合应用示例：
查询 SELECT id, name, age FROM user WHERE gender = 'M' AND age > 20 AND name LIKE '张%'。
- 设计联合索引
(gender, name, age, id)：- 最左前缀：先匹配
gender = 'M'和name LIKE '张%'（前缀匹配），充分利用索引。 - 索引下推：
age > 20条件在索引遍历时下推过滤，仅对满足所有条件的行回表。 - 覆盖索引：若索引包含
(id, name, age)，所有SELECT字段已在索引中，无需回表。
- 最左前缀：先匹配
最终效果：扫描范围极小 + 回表次数降至最低 + Server层处理量极小，查询吞吐量远高于未优化的SQL。