Mysql索引下推、索引跳跃、索引覆盖

电魂泡哥
9 阅读5分钟

1.索引覆盖（Covering Index）

索引覆盖的意思就是直接从索引中获取所有需要的字段，跳过回表（即根据索引找到主键后再去数据表查一遍）的步骤。
EXPLAIN标志：Using index。
需要SELECT的字段全部被包含在使用的索引中。

  • 典型场景：需要频繁查询某几个固定字段，且这些字段能组成一个复合索引。

  • 应用示例：例如，一个商品摘要的列表页只需要 name 和 image、price字段。

    -- 查询所需字段 (name, image,price) 完全被索引 idx_product 覆盖
EXPLAIN SELECT name, image,price FROM product WHERE p_id = 10086;

效果：如果使用SELECT *，MySQL需要先通过idx_product 找到主键，再回表到数据页获取其他字段（如amount, note），多了一次随机I/O。而使用索引覆盖，数据直接从索引页返回，性能更高.

2.索引下推(Index Condition Pushdown, ICP)

索引下推的意思就是将WHERE条件中可被索引覆盖的部分下推到存储引擎层进行过滤，减少存储引擎层向上层（Server层）返回的数据量，从而减少回表次数。
适用于二级索引，且WHERE条件中包含索引中非最左前缀列的过滤条件。
EXPLAIN标志：Using index condition。

-- idx_status  (p_id, status) 用于查找，amount > 100 在索引中无法判断，但会被“下推”
SELECT * FROM product WHERE p_id = 10086 AND status = 'PAID' AND amount > 1000;

传统方式会先通过索引找到所有p_id=10086 AND status='PAID'的行的主键，然后逐一回表检查amount > 1000。启用ICP后，MySQL会在存储引擎层利用索引遍历数据，如果某行不满足amount > 1000，就直接跳过，连主键ID都不会取，更不会发起回表请求。这显著减少了无效回表的次数。

3.索引合并 (Index Merge)

查询条件中包含了多个字段，且每个字段都有独立的单列索引，但没有合适的复合索引。
Using intersect(...), Using union(...), Using sort_union(...)
仅适用于单表，不适用于全文索引
对同一个表使用多个索引，分别扫描后将结果合并（取交集、并集等）

-- WHERE 条件中使用了 OR 连接两个不同索引的字段
SELECT * FROM product WHERE p_id = 10086 OR create_time > '2024-01-01';

MySQL会同时使用idx_product（虽然此索引包含p_id列）和idx_create_time这两个索引，分别找到满足各自条件的行的主键ID，然后将这两个ID集合合并（取并集），最后再回表。这避免了只能使用其中一个索引而导致另一个条件被全表扫描的情况。
注意：有可能会碰到死锁情况。
循环更新操作。Index Merge的两个扫描路径(路径1和路径2)的执行可能存在微小的时序差异,导致不同事务在相同SQL下的锁获取顺序不同。

具体来说:

  • 事务A循环更新M1时,可能先扫描idx_material_id,再扫描idx_delivery_docs_no

  • 事务B循环更新M2时,可能先扫描idx_delivery_docs_no,再扫描idx_material_id

  • 这种差异虽然不影响最终结果,但会影响加锁的先后顺序

  • type: index_merge ← 确认使用了Index Merge

  • key: idx_picking_docs_no,idx_material_id ← 同时使用两个索引

优化方案：
使用联合索引 （最佳），批量更新操作，强制使用索引。

核心要点

  1. Index Merge不是万能的: 虽然能优化某些场景,但也可能带来锁顺序不确定性
  2. 联合索引优于Index Merge: 对于固定的多条件查询,联合索引是最佳选择
  3. 批量操作优于循环: 无论索引如何,批量SQL都比循环UPDATE性能更好
  4. EXPLAIN是好帮手: 出现性能问题时,第一时间查看执行计划

避免Index Merge导致问题的建议

  1. 优先建立联合索引: 根据高频查询的WHERE条件设计
  2. 避免过多单列索引: 容易误导优化器
  3. 使用索引提示: 必要时强制使用指定索引
  4. 批量操作代替循环: 减少SQL执行次数
  5. 定期分析执行计划: 及时发现Index Merge的不合理使用

4.索引跳跃扫描 (Index Skip Scan, ISS)

Using index for skip scan。
MySQL 8.0.13 引入，适用于前导列唯一值较少的场景。
查询条件不包含联合索引的最左前缀列时，也能使用该索引。

-- 查询条件 status = 'PAID' 跳过了联合索引 idx_user_status 的最左列 user_id
SELECT * FROM product WHERE status = 'PAID';

在MySQL 8.0之前，此查询无法使用idx_status。ISS优化器会“智能地”将其拆分为若干个子查询，例如：SELECT ... WHERE user_id=1 AND status='PAID' UNION SELECT ... WHERE user_id=2 AND status='PAID' ...，从而有效利用索引。但它的生效前提是，被跳过的列（user_id）的不同取值数量要比较少。

多范围读取 (Multi-Range Read)

Using MRR
主要用于范围查询和Join操作
将二级索引查到的主键ID（Row IDs）先放入缓冲区排序，再按顺序回表访问。

-- 通过 create_time 索引进行范围查询，可能得到大量主键ID
SELECT * FROM product WHERE create_time BETWEEN '2026-01-01' AND '2026-01-31';

idx_create_time索引中存储的主键ID（p_id）可能是无序的。如果直接回表，会导致大量的随机I/O。启用MRR后，MySQL会先将这些主键ID收集到内存的read_rnd_buffer中并排序，然后再按顺序批量回表访问数据页，将随机I/O转化为顺序I/O，极大提升了I/O效率.

avatar
文章
阅读
粉丝