一、什么是索引下推
-
索引条件下推(Index Condition Pushdown,ICP)是MySQL 5.6版本后引入的一项新特性。它通过减少回表的次数来提高数据库的查询效率。
-
在不使用ICP的情况下,当使用非主键索引(也称为普通索引或二级索引)进行查询时,存储引擎会通过索引检索到数据,然后将数据返回给MySQL服务器,服务器再判断数据是否符合查询条件。
-
而在使用ICP的情况下,如果查询语句中存在某些索引列的判断条件,MySQL服务器将这部分条件传递给存储引擎。存储引擎会根据这些条件判断索引是否符合MySQL服务器传递的条件,只有当索引符合条件时,存储引擎才会将数据检索出来并返回给MySQL服务器。
-
通过使用ICP,数据库可以在存储引擎层级对索引进行条件判断,减少不必要的回表操作,从而提高查询效率。这样可以大大减少从磁盘读取数据的次数,加快查询速度。
-
需要注意的是,ICP的效果取决于具体的查询语句和索引的使用情况。并不是所有的查询都适合使用ICP,有时可能会导致性能下降。因此,在使用ICP时需要进行合理的测试和评估,确保其能够带来性能的提升。
二、MySQL架构图
三、DEMO演示过程
MySQL版本如下:
SELECT VERSION();
下面是创建一个名为user的表,并插入一些测试数据的SQL语句:
DROP TABLE IF EXISTS `t_user`;
CREATE TABLE `t_user` (
`id` bigint NOT NULL AUTO_INCREMENT COMMENT '主键',
`name` varchar(64) NOT NULL COMMENT '用户名',
`age` int(8) NOT NULL COMMENT '年龄',
`address` varchar(255) DEFAULT NULL COMMENT '地址',
`is_delete` tinyint NOT NULL DEFAULT 0 COMMENT '是否删除,默认否',
`create_time` datetime DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
PRIMARY KEY (`id`),
KEY `idx_name_age` (`name`, `age`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
INSERT INTO t_user(name, age, address) VALUES ('李四', 22, '中国');
INSERT INTO t_user(name, age, address) VALUES ('李五', 22, '中国');
INSERT INTO t_user(name, age, address) VALUES ('李六', 23, '中国');
INSERT INTO t_user(name, age, address) VALUES ('张三', 24, '中国');
INSERT INTO t_user(name, age, address) VALUES ('李期', 24, '中国');
在Navicat上执行上述SQL如下:
插入测试数据:
接下来,我们创建一个联合索引来测试索引下推的场景:
CREATE INDEX idx_user_name_age ON user (name, age);
然后,我们可以使用EXPLAIN语句来演示索引下推的效果:
EXPLAIN SELECT * FROM t_user WHERE name LIKE '李%' AND age = 22;
执行以上语句后,输出的结果中,可以观察到Extra列中有一个"Using index"的标记,表示索引下推被成功应用。
总结下SQL执行过程:
- 解析SQL语句,确定查询的表和条件。
- 根据查询条件选择合适的索引,如果存在适用的联合索引,会优先选择联合索引。
- 使用索引检索数据,将满足条件的索引行找出来。
- 将索引行传递给MySQL服务器。
- MySQL服务器根据剩余的条件进行判断,只有满足所有条件的索引行才会被返回。
- 返回结果给客户端。
在使用索引下推的场景下,步骤4中的索引行会根据剩余的条件进行判断,只有满足条件的索引行才会被返回给MySQL服务器,从而减少了不必要的数据传输和判断操作,提高了查询效率。
💎索引下推💎 是 mysql 5.6 优化查询回表的功能,在5.6之前都不支持索引下推,我这里使用的是8.0
关闭索引下推:
SET SESSION optimizer_switch='index_condition_pushdown=off';
再次执行:EXPLAIN SELECT * FROM t_user WHERE name LIKE ‘李%’ AND age = 22;
-
具体来说,Using where表示查询执行时需要在查询引擎层级使用WHERE子句对结果进行过滤。这意味着在查询执行期间,MySQL将扫描表中的每一行,并根据WHERE条件进行过滤,以返回满足条件的结果集。
-
当Using where出现在EXPLAIN执行结果的Extra列时,表示查询过程中需要在查询引擎层级(Server层)执行额外的过滤操作,可能会导致查询性能下降。
-
相比之下,如果EXPLAIN执行结果中出现了Using index condition,那么意味着查询过程中已经使用了索引下推(index condition pushdown),即查询条件已经在存储引擎层级进行了过滤,不需要在查询引擎层级再进行过滤操作。