接着上篇索引优化全攻略:提升排序、GROUP BY与分页性能今天我们学习索引下推
索引下推
索引下推(Index Condition Pushdown,简称 ICP)是一种提高查询效率、减少回表操作的技术。它允许 MySQL 在使用联合索引查找数据时,将部分查询条件“下推”到存储引擎层进行过滤。这样可以减少从表中读取的数据行,降低 I/O 操作的开销,从而提高查询性能。需要注意的是,索引下推仅适用于联合索引,且它通过将本应由服务器层处理的操作交给存储引擎来执行,从而实现性能优化。
通俗一点就是通过二级索引査到主键id后回表完再进行where条件过滤 改为 =>二级索引査到数据后直接where过滤一遍再进行回表来减少回表的次数
实战
1.1 使用前后对比
Index Condition Pushdown(ICP)是MySQL 5.6中新特性,是一种在存储引擎层使用索引过滤数据的一种优化方式。
-
如果没有ICP,存储引擎会遍历索引以定位基表中的行,并将它们返回给MySQL服务器,由MySQL服务器评估
WHERE
后面的条件是否保留行。 -
启用ICP后,如果部分
WHERE
条件可以仅使用索引中的列进行筛选,则MySQL服务器会把这部分WHERE
条件放到存储引擎筛选。然后,存储引擎通过使用索引条目来筛选数据,并且只有在满足这一条件时才从表中读取行。- 好处: ICP可以减少存储引擎必须访问基表的次数和MySQL服务器必须访问存储引擎的次数。
- 但是,ICP的
加速效果
取决于在存储引擎内通过ICP筛选
掉的数据的比例。
30多款 IntelliJ IDEA 必备插件:让你笑着提升开发效率!
例子:
key 有索引
EXPLAIN SELECT *FROM s WHERE key>'z'AND key LIKE '%a';
这里条件like '%a' 其实可以在索引里面,算出来哪些符合条件。。。。过滤出符合条件的,再回表。这样回表的数据可以减少很多。还有一个好处,没有索引下推,就需要把数据都回表查出来,,这些数据可能在不同的页当中,又会产生IO
条件下推,下推到下一个条件符不符合。
1.2 ICP的开启/关闭
-
默认情况下启用索引条件下推。可以通过设置系统变量
optimizer_switch
控制:index_condition_pushdown
#关闭索引下推 SET optimizer_switch = 'index_condition_pushdown=off ' ; #打开索引下推 SET optimizer_switch = 'index_condition_pushdown=on ' ;
-
当使用索引条件下推时,
EXPLAIN
语句输出结果中Extra列内容显示为Using index condition
。
1.3ICP使用案例
建表
CREATE TABLE `people` (
`id` INT NOT NULL AUTO_INCREMENT,
`zipcode` VARCHAR ( 20 ) COLLATE utf8_bin DEFAULT NULL,
`firstname` varchar(20)COLLATE utf8_bin DEFAULT NULL,
`lastname` varchar(20) COLLATE utf8_bin DEFAULT NULL,
`address` varchar (50)COLLATE utf8_bin DEFAULT NULL,
PRIMARY KEY ( `id`),
KEY `zip_last_first`( `zipcode` , `lastname`, `firstname`)
)ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8mb3 COLLATE=utf8_bin;
插入数据
INSERT INTO `people` VALUES
( '1', '000001','三','张','北京市'),
( '2', '000002 ','四','李','南京市'),
( '3', '000003', '五','王','上海市'),
( '4 ', '000001','六','赵','天津市');
为该表定义联合索引zip_last_first (zipcode,lastname,firstname)。如果我们知道了一个人的邮编,但是不确定这个人的姓氏,我们可以进行如下检索:
SELECT *FROM people
WHERE zipcode= '000001'
AND lastname LIKE '%张%'
AND address LIKE '%北京市%';
执行查看SQL的查询计划,Extra中显示了Using index condition
,这表示使用了索引下推。另外,Usingwhere
表示条件中包含需要过滤的非索引列的数据,即address LIKE '%北京市%'这个条件并不是索引列,需要在服务端过滤掉。
1.4开启和关闭ICP的性能对比
创建存储过程,主要目的就是插入很多000001的数据,这样查询的时候为了在存储引擎层做过滤,减少IO,也为了减少缓冲池(缓存数据页,没有IO)的作用。
DELIMITER //
CREATE PROCEDURE insert_people( max_num INT )
BEGIN
DECLARE i INT DEFAULT 0;
SET autocommit = 0;
REPEAT
SET i = i + 1;
INSERT INTo people ( zipcode, firstname , lastname , address ) VALUES ( '000001','六', '赵','天津市');
UNTIL i = max_num
END REPEAT;
COMMIT;
END //
DELIMITER ;
调用存储过程
call insert_people(1000000);
首先打开profiling
。
#查看
mysql> show variables like 'profiling%';
+------------------------+-------+
| Variable_name | Value |
+------------------------+-------+
| profiling | OFF |
| profiling_history_size | 15 |
+------------------------+-------+
set profiling=1 ;
执行SQL语句,此时默认打开索引下推。
SELECT * FROM people WHERE zipcode= '000001' AND lastname LIKE '%张%';
再次执行sQL语句,不使用索引下推
SELECT /*+ no_icp (people) */ * FROM people WHERE zipcode='000001' AND lastname LIKE '%张%';
查看当前会话所产生的所有profiles
show profiles\G ;
结果如下。
多次测试效率对比来看,使用ICP优化的查询效率会好一些。这里建议多存储一些数据效果更明显。
1.5 使用前后的扫描过程
在不使用ICP索引扫描的过程:
storage层:只将满足index key条件的索引记录对应的整行记录取出,返回给server层
server 层:对返回的数据,使用后面的where条件过滤,直至返回最后一行。
使用ICP扫描的过程: storage层: 首先将index key条件满足的索引记录区间确定,然后在索引上使用index filter进行过滤。将满足的index filter条件的索引记录才去回表取出整行记录返回server层。不满足index filter条件的索引记录丢弃,不回 表、也不会返回server层。 server 层: 对返回的数据,使用table filter条件做最后的过滤。
使用前后的成本差别 使用前,存储层多返回了需要被index filter过滤掉的整行记录 使用ICP后,直接就去掉了不满足index filter条件的记录,省去了他们回表和传递到server层的成本。 ICP的 加速效果 取决于在存储引擎内通过 ICP筛选 掉的数据的比例。
1.6 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作用是减小回表,ICP需要回表
-
相关子查询的条件不能使用ICP
交流学习
最后,如果这篇文章对你有所启发,请帮忙转发给更多的朋友,让更多人受益!如果你有任何疑问或想法,欢迎随时留言与我讨论,我们一起学习、共同进步。别忘了关注我,我将持续分享更多有趣且实用的技术文章,期待与你的交流!