从MySQL索引下推看性能优化:减少回表,提升查询效率

731 阅读7分钟

接着上篇索引优化全攻略:提升排序、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 '%北京市%';

image-20220327212419933

执行查看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 ;

结果如下。

image-20220327214304560

image-20220327214423178

多次测试效率对比来看,使用ICP优化的查询效率会好一些。这里建议多存储一些数据效果更明显。

1.5 使用前后的扫描过程

在不使用ICP索引扫描的过程:

storage层:只将满足index key条件的索引记录对应的整行记录取出,返回给server层

server 层:对返回的数据,使用后面的where条件过滤,直至返回最后一行。

image-20220327181853000

image-20220327181901279

使用ICP扫描的过程: storage层: 首先将index key条件满足的索引记录区间确定,然后在索引上使用index filter进行过滤。将满足的index filter条件的索引记录才去回表取出整行记录返回server层。不满足index filter条件的索引记录丢弃,不回 表、也不会返回server层。 server 层: 对返回的数据,使用table filter条件做最后的过滤。

image-20220327181910781

image-20220327181918603

使用前后的成本差别 使用前,存储层多返回了需要被index filter过滤掉的整行记录 使用ICP后,直接就去掉了不满足index filter条件的记录,省去了他们回表和传递到server层的成本。 ICP的 加速效果 取决于在存储引擎内通过 ICP筛选 掉的数据的比例。

1.6 ICP的使用条件

  1. 如果表访问的类型为range、ref、eq_ref和ref_or_null可以使用ICP

  2. ICP可以用于InnoDBMyISAM表,包括分区表InnoDBMyISAM

  3. 对于InnoDB表,ICP仅用于二级索引。ICP的目标是减少全行读取次数,从而减少I/o操作。

  4. 当SQL使用覆盖索引时,不支持ICP。因为这种情况下使用ICP不会减少I/O。

    索引覆盖不能使用,一个原因是,索引覆盖,不需要回表。。ICP作用是减小回表,ICP需要回表

  5. 相关子查询的条件不能使用ICP

交流学习

最后,如果这篇文章对你有所启发,请帮忙转发给更多的朋友,让更多人受益!如果你有任何疑问或想法,欢迎随时留言与我讨论,我们一起学习、共同进步。别忘了关注我,我将持续分享更多有趣且实用的技术文章,期待与你的交流!