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

超爱吃士力架
VIP.1 初学乍练
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可以用于InnoDB和MyISAM表，包括分区表InnoDB和MyISAM表

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

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

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

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

交流学习

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

avatar
文章
阅读
粉丝