MySQL 索引条件下推
MySQL 索引 面试 About 1,817 words索引条件下推
Index Condition Pushdown
简称ICP
,也称索引条件下推。
MySQL
中默认开启了索引下推。
开启
set optimizer_switch='index_condition_pushdown=on';
关闭
set optimizer_switch='index_condition_pushdown=off';
ICP 使用条件
- 如果表访问的类型为
range
、ref
、eq_ref
、ref_or_null
可以使用ICP
。 ICP
可以用于InnoDB
和MyISAM
表,包括分区表InnoDB
和MyISAM
表。- 对于
InnoDB
表,ICP
仅用于二级索引。ICP
的目标是减少全行读取次数,从而减少I/O
操作。 - 当
SQL
使用覆盖索引时,不支持ICP
。因为这种情况使用ICP
不会减少I/O
。 - 相关子查询的条件不能使用
ICP
。
概念
如果部分where
条件可以仅使用索引中的列进行筛选,则MySQL
服务器会把这部分where
条件放到存储引擎中筛选。然后存储引擎通过使用索引条目来筛选数据,并且只有在满足这一条件时才从表中读取行。
好处
ICP
可以减少存储引擎必须访问表的次数和MySQL
服务器必须访问存储引擎的次数。
坏处
ICP
的加速效果取决于在存储引擎内通过ICP
筛选的数据的比例。
解释
更多针对联合索引。where
条件中有这个字段,但这个字段不走索引。
create table t_user(id int primary key, name varchar(25), age int, no int);
create index idx_age_name on t_user(age, name);
insert into t_user values(1, 'aaa', 10, 1), (2,'bbb', 20, 2), (3, 'ccc', 30, 3);
Using index condition
:表示使用了索引条件下推。
本来是要带着age=10
回表查询,再带着name like '%abc'
回表查询。
使用了索引下推后:
- 先将
age=10
回表查询得到数据。 - 然后
name like '%abc'
再在age=10
查询得到的数据中进行筛选,过滤掉不符合的行数据。 - 再回表查询得到最终数据。
(因为索引中包含了这两个字段的值,所以第二部不用回表)
mysql> explain select * from t_user where age = 10 and name like '%abc';
+----+-------------+--------+------------+------+---------------+--------------+---------+-------+------+----------+-----------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+--------+------------+------+---------------+--------------+---------+-------+------+----------+-----------------------+
| 1 | SIMPLE | t_user | NULL | ref | idx_age_name | idx_age_name | 5 | const | 1 | 33.33 | Using index condition |
+----+-------------+--------+------------+------+---------------+--------------+---------+-------+------+----------+-----------------------+
1 row in set, 1 warning (0.00 sec)
Views: 2,081 · Posted: 2022-05-13
————        END        ————
Give me a Star, Thanks:)
https://github.com/fendoudebb/LiteNote扫描下方二维码关注公众号和小程序↓↓↓
Loading...