mysql-索引下推

最新推荐文章于 2025-11-30 16:29:01 发布

原创最新推荐文章于 2025-11-30 16:29:01 发布 · 700 阅读

10 ·

CC 4.0 BY-SA版权

文章标签：

#mysql #数据库

### 表结构如下

CREATE TABLE test1 (
id int(10) NOT NULL,
a varchar(20) DEFAULT NULL,
b varchar(20) DEFAULT NULL,
c varchar(20) DEFAULT NULL,
PRIMARY KEY (id),
KEY a_b_c (a,b,c) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

sql 如下：select a,b,c from test1 where a = ‘sad’ and b like ‘dsa%’ and c= ‘sdad’

ASSISTANT

在 SQL 查询中：

SELECT a, b, c FROM test1 WHERE a = 'sad' AND b LIKE 'dsa%' AND c = 'sdad';

以及表结构中有一个联合索引：

KEY `a_b_c` (`a`,`b`,`c`) USING BTREE

✅ 结论：这个 SQL 查询不会发生回表（0 次回表）

原因分析：

查询的字段是 a, b, c，而联合索引 (a, b, c) 正好覆盖了这些字段。
- 这意味着这是一个 覆盖索引（Covering Index） 查询。
- MySQL 可以直接从索引中获取所有需要的数据，而无需回到主键索引（聚簇索引）去查找完整行记录。
WHERE 条件完全命中索引的最左前缀：
- a = 'sad' → 精确匹配，走索引第一列。
- b LIKE 'dsa%' → 以常量开头的前缀匹配，可以走索引第二列（范围扫描）。
- c = 'sdad' → 在 b 的范围扫描内进一步过滤，可以使用索引第三列。
- 所以整个 WHERE 条件可以在索引树中完成过滤。
不需要回表的原因：
- 回表是指通过二级索引找到主键后，再用主键去聚簇索引中查找其他未包含在索引中的字段。
- 但本查询 SELECT 的字段 a, b, c 全部包含在二级索引 a_b_c 中，因此无需回表。

验证方法：

可以使用 EXPLAIN 查看执行计划：

EXPLAIN SELECT a,b,c FROM test1 WHERE a = 'sad' AND b LIKE 'dsa%' AND c = 'sdad';

关注以下字段：

type: 应该是 range 或 ref。
key: 应该显示使用了 a_b_c 索引。
Extra: 如果看到 Using index，就表示使用了覆盖索引，没有回表。
因为查询字段被索引完全覆盖，MySQL 可以直接从索引中获取所有数据，无需回表。

🎯 什么是“索引下推”（Index Condition Pushdown, ICP）？

索引下推（ICP） 是 MySQL 5.6 引入的一种优化技术。它的核心思想是：

在存储引擎层（InnoDB）就提前过滤掉不满足 WHERE 条件的索引记录，而不是把所有可能记录都返回给 Server 层再过滤。

这样可以减少回表次数（虽然这个查询不回表，但 ICP 仍能减少索引扫描量）。

🔍 在sql查询中，ICP 如何工作？

WHERE 条件：

WHERE a = 'sad'          -- 索引第一列，精确匹配
  AND b LIKE 'dsa%'      -- 索引第二列，范围匹配（前缀）
  AND c = 'sdad'         -- 索引第三列，等值匹配

🚫 没有 ICP 的情况（MySQL < 5.6）：

使用索引定位 a = 'sad'。
对于所有 a = 'sad' 的记录，只要 b LIKE 'dsa%'，就全部返回给 Server 层。
Server 层再逐行判断 c = 'sdad' 是否成立。
如果 SELECT 的字段不在索引中，还需要回表取数据 —— 但这里是覆盖索引，所以不回表。

→ 但即使不回表，也要把很多 b LIKE 'dsa%' 但 c != 'sdad' 的记录传给 Server 层，造成无谓的传输和过滤开销。

✅ 有 ICP 的情况（MySQL >= 5.6，默认开启）：

使用索引定位 a = 'sad'。
在存储引擎层遍历索引时，对每条记录直接判断：
- b LIKE 'dsa%' AND c = 'sdad'
只有同时满足这两个条件的索引条目，才返回给 Server 层。
Server 层直接使用这些“预过滤”后的索引记录（因为是覆盖索引，直接输出）。

→ 大幅减少从引擎层传到 Server 层的数据量！

📌 ICP 的触发条件：

查询使用了二级索引（ a_b_c 就是）。
WHERE 条件中包含索引列上的非最左前缀范围条件之后的列（ c = 'sdad' 就是在 b LIKE ... 之后）。
MySQL 版本 ≥ 5.6。
查询执行计划中 Extra 字段会出现：Using index condition

🔎 如何验证是否使用了索引下推？

执行：

EXPLAIN SELECT a,b,c FROM test1 WHERE a = 'sad' AND b LIKE 'dsa%' AND c = 'sdad';

如果看到：

Extra: Using index condition

说明使用了 索引下推（ICP）。

⚠️ 注意：如果看到的是 Using where; Using index，那说明没有用 ICP（可能版本低或条件不满足）。
如果看到 Using index condition，就是启用了 ICP。

✅ 总结问题：

这个 SQL 会回表几次？

→ 0 次回表（因为是覆盖索引）

索引下推在这里起什么作用？

→ 虽然不回表，但索引下推能减少引擎层传给 Server 层的索引记录数，在 b LIKE 'dsa%' 的基础上，提前在引擎层过滤掉 c != 'sdad' 的记录，提升性能。

💡 举个对比例子：

假设索引中有 100 条记录满足 a = 'sad' AND b LIKE 'dsa%'，但只有 5 条满足 c = 'sdad'：

❌ 无 ICP：引擎传 100 条给 Server，Server 过滤剩 5 条。
✅ 有 ICP：引擎自己过滤，只传 5 条给 Server。

→ 减少了 95 条无用记录的传输和上层判断！

✅ 最终结论：

回表次数：0
使用了索引下推（ICP）：是（MySQL ≥ 5.6 且条件满足）
优化效果：减少索引扫描后无效记录的传输，提升查询效率

📌 建议：

在生产环境中，使用 EXPLAIN FORMAT=JSON 或 EXPLAIN ANALYZE（MySQL 8.0+）可以更清晰看到 ICP 是否生效、扫描行数等细节。

例如：

EXPLAIN FORMAT=JSON SELECT a,b,c FROM test1 WHERE a = 'sad' AND b LIKE 'dsa%' AND c = 'sdad';

会明确显示 "condition": "c = 'sdad'" 被下推到存储引擎执行。

下面增加数据库字段和查询列字段

CREATE TABLE test1 (
id int(10) NOT NULL,
a varchar(20) DEFAULT NULL,
b varchar(20) DEFAULT NULL,
c varchar(20) DEFAULT NULL,
d varchar(20) DEFAULT NULL,
e varchar(20) DEFAULT NULL,
PRIMARY KEY (id),
KEY a_b_c (a,b,c) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
sql如下：
select a,b,c,d,e from test1 where a = ‘sad’ and b like ‘dsa%’ and c= ‘sdad’

ASSISTANT

修改了表结构，增加了字段 d 和 e，并且 SQL 查询也改成了：

SELECT a, b, c, d, e FROM test1 WHERE a = 'sad' AND b LIKE 'dsa%' AND c = 'sdad';

索引仍然是：KEY a_b_c (a, b, c)

✅ 核心问题：这个 SQL 会回表几次？

📌 为什么？

1. 查询字段不再被索引完全覆盖

SELECT 的字段是：a, b, c, d, e
索引 a_b_c 只包含 a, b, c
d 和 e 不在索引中 → 必须回表到聚簇索引（主键索引）中获取！

→ 不再是覆盖索引查询，必须回表！

2. WHERE 条件仍然可以使用索引

a = 'sad' → 精确匹配第一列
b LIKE 'dsa%' → 前缀匹配第二列（范围扫描）
c = 'sdad' → 第三列等值，在范围扫描内过滤（配合索引下推 ICP 优化）

→ MySQL 会用 a_b_c 索引来定位符合条件的记录。

3. 回表过程：

在二级索引 a_b_c 中找到所有满足 a = 'sad' AND b LIKE 'dsa%' AND c = 'sdad' 的索引条目。
每个索引条目包含：(a, b, c, id) → InnoDB 二级索引叶子节点存储的是 索引列 + 主键。
用每个条目中的 id（主键），回表到聚簇索引中查找完整行记录，以获取 d 和 e。
最终返回 a, b, c, d, e。

→ 每一条满足条件的记录，都会触发一次回表！

📊 举个例子：

假设索引中找到 10 条记录满足 WHERE 条件：

引擎层通过索引定位到 10 个 (a, b, c, id)。
需要获取 d 和 e → 对这 10 个 id，每个都要回表一次。
→ 总共回表 10 次。

⚙️ 索引下推（ICP）还生效吗？

✅ 仍然生效！

即使需要回表，ICP 仍然可以在存储引擎层提前过滤掉不满足 c = 'sdad' 的记录，从而减少回表次数！

例如：

如果没有 ICP：索引中 a='sad' AND b LIKE 'dsa%' 有 100 条 → 全部回表 → 100 次回表。
有 ICP：引擎层过滤后只剩 10 条满足 c = 'sdad' → 只回表 10 次。

→ ICP 虽不能避免回表，但能显著减少回表次数！

🔍 如何验证？

执行：

EXPLAIN SELECT a,b,c,d,e FROM test1 WHERE a = 'sad' AND b LIKE 'dsa%' AND c = 'sdad';

会看到：

key: a_b_c → 使用了索引
Extra: Using index condition → 使用了索引下推（ICP）
没有 Using index → 说明不是覆盖索引，需要回表

💡 优化建议：

如果经常执行这个查询，可以考虑：

方案1：扩展索引为覆盖索引（推荐）

ALTER TABLE test1 DROP INDEX a_b_c,
ADD INDEX idx_cover (a, b, c, d, e);

→ 查询字段全在索引中，0 回表 + ICP 优化，性能最佳！

方案2：保持原索引，但接受回表（适合 d/e 很大或更新频繁）

如果 d 或 e 是大字段（如 TEXT），或表更新频繁，扩展索引可能带来写放大，此时可以接受回表 + ICP。

✅ 最终总结：

项目	说明
是否回表	✅ 是
回表次数	= 满足 `WHERE` 条件的记录数
是否使用索引	✅ 使用 `a_b_c` 索引定位
是否使用索引下推（ICP）	✅ 是，减少无效回表
是否覆盖索引	❌ 否，因为 `d`, `e` 不在索引中
优化建议	扩展索引为 `(a,b,c,d,e)` 实现覆盖索引