mysql 索引下推，举例说明
chatGptMySQL 索引下推（Index Condition Pushdown, ICP）
索引下推（Index Condition Pushdown，简称 ICP）是 MySQL 中的一个优化特性，它可以让查询条件在使用索引时，尽早地在索引扫描阶段进行过滤，而不必等到后续的表数据检索阶段再进行过滤。这能够减少数据访问量，进步查询服从。
索引下推的原理
在没有索引下推的环境下，查询通常会先扫描整个表的数据，再通过 WHERE 子句进行过滤。而通过索引下推，MySQL 在扫描索引时，会只管将 WHERE 子句中的条件提前应用到索引扫描阶段，从而减少读取不须要的数据。
索引下推的示例
假设我们有一个 users 表，表结构如下：
sql
CREATE TABLE users ( id INT PRIMARY KEY, name VARCHAR(100), age INT, country VARCHAR(50) );
而且 users 表上有一个联合索引：
sql
CREATE INDEX idx_name_age ON users(name, age);
1. 没有索引下推的环境
假设我们实行以下查询：
sql
SELECT * FROM users WHERE name = 'Alice' AND age > 30;
[*] 没有索引下推的环境下，MySQL 会首先扫描 users 表上的索引（idx_name_age）。然后，它会根据索引扫描的次序逐一读取符合条件的 name = 'Alice' 的记录，并加载到内存中。接着，在内存中对这些记录进行过滤，以确认它们的 age > 30。
如许，虽然 MySQL 通过索引找到了 name = 'Alice' 的记录，但它仍然必要读取所有符合 name = 'Alice' 条件的记录，然后再在后续阶段过滤出 age > 30 的记录。这会导致不须要的额外计算，特殊是当 name = 'Alice' 记录很多时，服从较低。
2. 索引下推的环境
引入索引下推后，MySQL 会在使用索引扫描的过程中，将部分查询条件下推到索引扫描阶段。即在扫描 idx_name_age 索引时，不仅根据 name = 'Alice' 过滤，还会将 age > 30 也一并下推到索引扫描阶段，从而克制扫描不符合条件的记录。
sql
SELECT * FROM users WHERE name = 'Alice' AND age > 30;
[*]使用索引下推时，MySQL 会通过 idx_name_age 索引扫描时，直接过滤掉不满足 age > 30 的记录，克制了后续的额外计算和内存操作。
索引下推的实行过程
假设查询 SELECT * FROM users WHERE name = 'Alice' AND age > 30; 使用了 idx_name_age 索引，实行流程如下：
[*]索引扫描：MySQL 使用 idx_name_age 索引查找所有 name = 'Alice' 的记录。
[*]条件下推：对于每一条 name = 'Alice' 的记录，MySQL 会查抄 age > 30 条件。
[*]如果符合条件，继承返回该记录。
[*]如果不符合条件，跳过该记录（不读取到内存中）。
[*]效果返回：最终只有符合 name = 'Alice' 且 age > 30 条件的记录会被返回。
索引下推的上风
[*]减少数据扫描：通过在索引阶段进行条件过滤，克制了无效记录的读取。
[*]进步查询服从：减少了后续不须要的内存操作和数据过滤，尤其在数据量较大时，能明显进步查询服从。
[*]减少 I/O：对于大数据量的表，索引下推能明显减少磁盘 I/O，由于只有符合条件的记录会被读取和返回。
索引下推的实用条件
索引下推并不是实用于所有查询条件，它有一些实用条件：
[*] WHERE 子句的条件字段必须包含在索引中：索引下推只能对包含在索引中的条件进行优化。例如，WHERE name = 'Alice' AND age > 30，如果索引是 name 和 age，那么索引下推就能发挥作用。
[*] 条件字段可以是索引的一部分：索引下推实用于联合索引中的部分字段。如果查询条件涉及多个字段，MySQL 只能在索引中找到的字段上实行下推操作。
查察索引下推的实行计划
可以使用 EXPLAIN 来查察查询的实行计划，从中可以确认 MySQL 是否使用了索引下推。
sql
EXPLAIN SELECT * FROM users WHERE name = 'Alice' AND age > 30;
在 EXPLAIN 的输出中，如果出现了 Using index condition，则表现 MySQL 在该查询中使用了索引下推。
例如：
text
+----+-------------+-------+------------+-------+---------------+-----------+---------+------+------+----------+-------------+ | id | select_type | table | type | key | key_len | ref | rows | Extra | +----+-------------+-------+------------+-------+---------------+-----------+---------+---------+--------+--------------------+ | 1 | SIMPLE | users | ref | idx_name_age | 103 | const | 10 | Using index condition | +----+-------------+-------+------------+-------+---------------+-----------+---------+---------+--------+--------------------+
上面的 Extra 列中有 Using index condition，表明 MySQL 使用了索引下推。
总结
[*]索引下推是 MySQL 的一个紧张优化特性，旨在进步查询服从。
[*]它通过将部分查询条件下推到索引扫描阶段，减少了无效数据的读取，减少了内存操作，特殊在处理大数据量时明显进步性能。
[*]在某些环境下，索引下推能够克制全表扫描，明显进步查询性能。
索引下推的实现和效果依赖于查询条件、索引的结构和数据分布，因此现实效果可能会有所差别。
楼仔
索引条件下推优化 （Index Condition Pushdown (ICP) ） 是 MySQL5.6 添加的，⽤于优化数据查询。 不使⽤索引条件下推优化时存储引擎通过索引检索到数据，然后返回给 MySQL Server，MySQL Server 进⾏ 过滤条件的判定。 当使⽤索引条件下推优化时，如果存在某些被索引的列的判定条件时，MySQL Server 将这⼀部分判定条件下 推给存储引擎，然后由存储引擎通过判定索引是否符合 MySQL Server 转达的条件，只有当索引符合条件时 才会将数据检索出来返回给 MySQL 服务器。 例如⼀张表，建了⼀个联合索引（name, age），查询语句： select * from t_user where name like '张%' and age=10; ，由于 name 使⽤了范围查询，根据最左匹配原则： 不使⽤ ICP，引擎层查找到 name like '张%' 的数据，再由 Server 层去过滤 age=10 这个条件，如许⼀来，就回 表了两次，浪费了联合索引的另外⼀个字段 age 。 https://i-blog.csdnimg.cn/direct/5780cc8d60ef44c2a7341d3c3fae1f56.png 但是，使⽤了索引下推优化，把 where 的条件放到了引擎层执⾏，直接根据 name like '张%' and age=10 的条 件进⾏过滤，减少了回表的次数。索引条件下推优化可以减少存储引擎查询基础表的次数，也可以减少 MySQL 服务器从存储引擎接收数据的次数。 https://i-blog.csdnimg.cn/direct/70d0c8b1704c4f509d74c5ca46eabfce.png
免责声明：如果侵犯了您的权益，请联系站长，我们会及时删除侵权内容，谢谢合作！更多信息从访问主页：qidao123.com:ToB企服之家，中国第一个企服评测及商务社交产业平台。
页:
[1]