索引下推（Index Condition Pushdown，简称ICP）是MySQL 5.6版本引入的一种数据库查询优化技术。ICP的主要目的是通过利用数据库引擎中的索引和过滤条件，将部分过滤工作下推到存储引擎层面进行处理，从而减少不必要的数据读取和传输，提升查询性能和整体系统效率。
在传统的查询执行过程中，数据库引擎首先根据索引定位到符合过滤条件的数据行，并将这些数据行读取到内存中，然后再进一步进行过滤操作。而索引下推则在这一步骤中尽可能地将过滤操作下推到存储引擎层面，避免将不符合条件的数据行读取到内存中。具体实现方式可以是通过存储引擎提供的接口或者钩子函数，让存储引擎在读取索引页时就进行额外的过滤操作。
select @@optimizer_switch -- 默认是打开的 -- 关闭ICP set optimizer_switch = 'index_condition_pushdown=off'; -- 打开ICP set optimizer_switch = 'index_condition_pushdown=on'; set profiling=1 -- 使用索引下推 SELECT * FROM EMPLOYEES WHERE first_name LIKE 'James' AND last_name = 'Landry'; (root@localhost) 09:12:52 [test1]> SELECT * FROM EMPLOYEES WHERE first_name LIKE 'James' AND last_name = 'Landry'; +-------------+------------+-----------+---------+---------------+------------+----------+---------+----------------+------------+---------------+ | EMPLOYEE_ID | FIRST_NAME | LAST_NAME | EMAIL | PHONE_decimal | HIRE_DATE | JOB_ID | SALARY | COMMISSION_PCT | MANAGER_ID | DEPARTMENT_ID | +-------------+------------+-----------+---------+---------------+------------+----------+---------+----------------+------------+---------------+ | 127 | James | Landry | JLANDRY | 650.124.1334 | 2007-01-14 | ST_CLERK | 2400.00 | NULL | 120 | 50 | +-------------+------------+-----------+---------+---------------+------------+----------+---------+----------------+------------+---------------+ 1 row in set (0.00 sec) (root@localhost) 09:08:52 [test1]> explain SELECT * FROM EMPLOYEES WHERE first_name LIKE 'James' AND last_name = 'Landry'; +----+-------------+-----------+------------+-------+---------------+-------------+---------+------+------+----------+-----------------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-----------+------------+-------+---------------+-------------+---------+------+------+----------+-----------------------+ | 1 | SIMPLE | EMPLOYEES | NULL | range | EMP_NAME_IX | EMP_NAME_IX | 140 | NULL | 1 | 100.00 | Using index condition | +----+-------------+-----------+------------+-------+---------------+-------------+---------+------+------+----------+-----------------------+ 1 row in set, 1 warning (0.01 sec) show profiles; show profile for query 1; show profile for query 2;
禁用 索引下推，观察执行计划
禁用 Index Condition Pushdown（ICP） 索引下推 (root@localhost) 09:22:32 [test1]> explain SELECT /*+ no_ipc () */ * FROM EMPLOYEES WHERE first_name LIKE 'James' AND last_name = 'Landry'; +----+-------------+-----------+------------+-------+---------------+-------------+---------+------+------+----------+-------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-----------+------------+-------+---------------+-------------+---------+------+------+----------+-------------+ | 1 | SIMPLE | EMPLOYEES | NULL | range | EMP_NAME_IX | EMP_NAME_IX | 140 | NULL | 1 | 100.00 | Using where | +----+-------------+-----------+------------+-------+---------------+-------------+---------+------+------+----------+-------------+ 1 row in set, 2 warnings (0.00 sec) (root@localhost) 09:22:46 [test1]> explain SELECT * FROM EMPLOYEES WHERE first_name LIKE 'James' AND last_name = 'Landry'; +----+-------------+-----------+------------+-------+---------------+-------------+---------+------+------+----------+-------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-----------+------------+-------+---------------+-------------+---------+------+------+----------+-------------+ | 1 | SIMPLE | EMPLOYEES | NULL | range | EMP_NAME_IX | EMP_NAME_IX | 140 | NULL | 1 | 100.00 | Using where | +----+-------------+-----------+------------+-------+---------------+-------------+---------+------+------+----------+-------------+ 1 row in set, 1 warning (0.00 sec)
索引下推是MySQL 5.6及以上版本提供的一种查询优化技术，它通过将部分过滤工作下推到存储引擎层面进行处理，减少了不必要的数据读取和传输，降低了磁盘I/O次数，提高了查询性能和整体系统效率。在复杂查询条件、多列条件的查询场景中，索引下推能够发挥更大的作用。
到此这篇关于MySQL索引下推index condition pushdown的文章就介绍到这了,更多相关MySQL索引下推内容请搜索插件窝以前的文章或继续浏览下面的相关文章希望大家以后多多支持插件窝！
