MySQL 架构
(图片来源于《MySQL 实战 45 讲》)
索引下推
索引下推,简称 ICP,英文全称:Index Condition Pushdown
,用于优化查问。其外围点就在于把数据筛选的过程放在了 存储引擎层
去解决,而不是像之前一样放到 Server 层
去做过滤。
这是 MySQL 5.6 之后新增的性能。
MySQL 5.6+
默认是开启 ICP 的,能够通过以下命令敞开:
SET optimizer_switch = 'index_condition_pushdown=off';
从一个查问开始
如下:
create table if not exists `sakila`.`user` (
`id` int unsigned auto_increment not null ,
`age` tinyint unsigned not null default 0,
`name` varchar(30) not null default '',
`city` varchar(30) not null default '',
primary key (`id`),
key `idx_age_name`(`age`, `name`) using BTREE
) engine=InnoDB;
insert into `sakila`.`user` (`age`, `name`, `city`) values (10, 'Jack', 'Beijing'),
(5, 'Jane', 'Shanghai'),
(21, 'Jhon', 'ChongQing');
当初有以下查问:
select * from `sakila`.`user` where age > 9 and name = 'Jane';
通过 explain
看到以下执行打算:
MySQL [(none)]> explain select * from `sakila`.`user` where age > 9 and name = 'Jane'\G;
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: user
partitions: NULL
type: range
possible_keys: idx_age_name
key: idx_age_name
key_len: 1
ref: NULL
rows: 2
filtered: 33.33
Extra: Using index condition
1 row in set, 1 warning (0.01 sec)
从 Extra: Using index condition
中,咱们晓得这条查问可能应用了 索引下推。
不开启 ICP
- Server 层调用 Engine 查问数据
- Engine 依据联结索引查问
age > 9
的数据 - 找到每一条满足条件(
age > 9
)的数据并依据主键 ID 回表,直到找到不符合条件的后果 - 数据返回给 Server 层,Server 层依据条件过滤(
name = 'Jane'
),流程完结
开启 ICP
- Server 层调用 Engine 查问数据
- Engine 依据联结索引查问
age > 9
的数据,再依据联结索引中已存在的name
字段进行过滤,找到符合条件的数据 - 依据找到符合条件的数据,回表查问
- 返回数据给 Server 层,流程完结
ICP 的限度
- ICP 实用的拜访办法:range、ref、eq_ref 和 ref_or_null
- ICP 能够用于
InnoDB
和MyISAM
表(MySQL 5.7 曾经反对分区表) - 对于 InnoDB,ICP 只用于二级索引
- 子查问不反对索引下推
小结
比照开启 ICP 和不开启 ICP,开启 ICP 数据的过滤放在了引擎层,显著缩小了回表的次数和返回的数据,节俭了磁盘 IO。
参考
- MySQL 索引与 Index Condition Pushdown
- https://mp.weixin.qq.com/s/87…
- MySQL :: MySQL 5.6 Reference Manual :: 8.2.1.5 Index Condition Pushdown Optimization