网店网站开发,wordpress移动版设置,网络,太原网站建设外包索引下推介绍 索引下推#xff08;INDEX CONDITION PUSHDOWN#xff0c;简称 ICP#xff09;是在 MySQL 5.6 针对扫描二级索引的一项优化改进。总的来说是通过把索引过滤条件下推到存储引擎#xff0c;来减少 MySQL 存储引擎访问基表的次数以及 MySQL 服务层访问存储引擎的…索引下推介绍 索引下推INDEX CONDITION PUSHDOWN简称 ICP是在 MySQL 5.6 针对扫描二级索引的一项优化改进。总的来说是通过把索引过滤条件下推到存储引擎来减少 MySQL 存储引擎访问基表的次数以及 MySQL 服务层访问存储引擎的次数。ICP 适用于 MYISAM 和 INNODB本篇的内容只基于 INNODB。 在讲这个技术之前你得对mysql架构有一个简单的认识见下图 ● MySQL 服务层也就是 SERVER 层用来解析 SQL 的语法、语义、生成查询计划、接管从 MySQL 存储引擎层上推的数据进行二次过滤等等。 ● MySQL 存储引擎层按照 MySQL 服务层下发的请求通过索引或者全表扫描等方式把数据上传到 MySQL 服务层。 ● MySQL 索引扫描根据指定索引过滤条件遍历索引找到索引键对应的主键值后回表过滤剩余过滤条件。 ● MySQL 索引过滤通过索引扫描并且基于索引进行二次条件过滤后再回表。 实战数据准备
delete from user1;
drop table user1;CREATE TABLE user1 (id int(11) NOT NULL AUTO_INCREMENT,name varchar(50) NOT NULL,age tinyint(4) NOT NULL,address varchar(50) NOT NULL,PRIMARY KEY (id),KEY idx_name_age (name,age)
) ENGINEInnoDB DEFAULT CHARSETutf8mb4;INSERT INTO user1 (name, age, address) VALUES
(Alice, 40, address1),
(Amy, 23, address2),
(Tom, 18, address3),
(Mike, 22, address4);explain SELECT * FROM user1 WHERE name LIKE A% and age 23;# 查看索引下推是否开启
select optimizer_switch
# 开启索引下推
set optimizer_switchindex_condition_pushdownon;
# 关闭索引下推
set optimizer_switchindex_condition_pushdownoff;索引下推实战 不使用索引下推实现 Explain SELECT * FROM user1 WHERE name LIKE A% and age 40;使用索引下推实现 Explain SELECT * FROM user1 WHERE name LIKE A% and age 40;索引下推的使用条件 ● ICP目标是减少全行记录读取从而减少IO 操作只能用于非聚簇索引。聚簇索引本身包含的表数据也就不存在下推一说。 ● 只能用于range、 ref、 eq_ref、ref_or_null访问方法 ● where 条件中是用 and 而非 or 的时候。 ● ICP适用于分区表。 ● ICP不支持基于虚拟列上建立的索引比如说函数索引 ● ICP不支持引用子查询作为条件。 ● ICP不支持存储函数作为条件因为存储引擎无法调用存储函数。 参考《图解索引覆盖、索引下推以及如何避免索引失效》
