MySQL-优化-ICP(Index condition pushdown)详解

2024-02-23 01:32
文章标签 mysql 详解 优化 database index icp condition pushdown

本文主要是介绍MySQL-优化-ICP(Index condition pushdown)详解，希望对大家解决编程问题提供一定的参考价值，需要的开发者们随着小编来一起学习吧！

文章目录

  • MySQL-优化-ICP(Index condition pushdown)详解
    • 一、关于ICP
    • 二、什么情况下可以用到MySQL ICP特性
    • 三、疑问：如下这个sql为什么会使用到ICP呢？
    • 四、代码控制
      • server层处理
      • engine层处理

MySQL-优化-ICP(Index condition pushdown)详解

一、关于ICP

ICP是index condition pushdown的简称，目的是为了减少server层和Innodb层的交互次数，加快查询效率。
官方文档给了一个例子，如下
假设有一张表people的二级索引INDEX (zipcode, lastname, firstname)，那么对于如下的查询

SELECT * FROM people
WHERE zipcode='95054'
AND lastname LIKE '%etrunia%'
AND address LIKE '%Main Street%';

MySQL可以利用索引来检索zipcode=‘95054’，但是没有办法通过索引查找来检索lastname LIKE ‘%etrunia%’，如果没有ICP的话，第一次交互过程中，查到所有的匹配zipcode='95054’的主键，第二次根据主键拿到整行数据信息，server层再根据整行数据来过滤lastname LIKE ‘%etrunia%’，address LIKE ‘%Main Street%’。如果有ICP的话，第一次交互的过程中，就可以根据lastname LIKE '%etrunia%'进行过滤了。

如上文档只是给了一个例子，而并不是说一定必须是组合索引，条件中包含了最左侧条件，然后是其他索引列的模糊匹配，我们知道非左前缀的模糊匹配，是无法通过B+树进行检索的，所以要通过ICP来进行判断。

二、什么情况下可以用到MySQL ICP特性

首先必要条件就是要打开ICP的开关，默认时开启的，如下

mysql> show global variables like '%optimizer_switch%'-> ;
+------------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Variable_name    | Value                                                                                                                                                                                                                                                                                                                                                                                                              |
+------------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| optimizer_switch | index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=off,engine_condition_pushdown=on,index_condition_pushdown=on,mrr=on,mrr_cost_based=on,block_nested_loop=on,batched_key_access=off,materialization=on,semijoin=on,loosescan=on,firstmatch=on,duplicateweedout=on,subquery_materialization_cost_based=on,use_index_extensions=off,condition_fanout_filter=on,derived_merge=on |
+------------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)mysql>
mysql>
mysql> set global optimizer_switch='index_condition_pushdown=on'-> ;
Query OK, 0 rows affected (0.00 sec)mysql> show global variables like '%optimizer_switch%';
+------------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Variable_name    | Value                                                                                                                                                                                                                                                                                                                                                                                                              |
+------------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| optimizer_switch | index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=off,engine_condition_pushdown=on,index_condition_pushdown=on,mrr=on,mrr_cost_based=on,block_nested_loop=on,batched_key_access=off,materialization=on,semijoin=on,loosescan=on,firstmatch=on,duplicateweedout=on,subquery_materialization_cost_based=on,use_index_extensions=off,condition_fanout_filter=on,derived_merge=on |
+------------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

官方手册8.2.1.5 Index Condition Pushdown Optimization中有相关的介绍，关于什么时候可以利用到ICP特性的，如下

  • 只有在access type，也就是执行计划的type列为rang，ref，eq_ref,ref_or_null时，才有可能会用到ICP
  • ICP对于分区表同样生效，无论是Innodb，还是MyISAM。
  • 对于InnoDB类型的表来说，ICP只适用于二级索引，ICP的目的时为了读取整行数据的数量，减少IO，所以对于主键索引来讲，本身就是整行数据，所以不需要ICP
  • 不支持虚拟列上的索引
  • 不支持自查询
  • 引用存储函数的条件不能下推。存储引擎不能调用存储功能
  • 触发条件不能被下推，这点我也没有深入了解下，有时间看下。官方说明Triggered conditions cannot be pushed down. (For information about triggered conditions, see Section 8.2.2.4, “Optimizing Subqueries with the EXISTS Strategy”.)

下面对比下有无ICP时，数据检索过程的区别

关闭ICP时如下：

  • 读取二级索引元组，然后使用索引元组中的主键去聚集索引上定位整行数据
  • 拿其他的where条件来看此行数据是否满足，满足的话保留，不满足的话丢弃

打开ICP时如下：

  • 获取二级索引元组
  • 检测此二级索引元组是否满足下推的条件，如果不满足，读取下一条二级索引元组
  • 如果满足，通过此二级索引元组到主键索引中定位读取整行数据
  • 然后再检测此行数据是否满足其他未能下推的条件，如果满足保留，如果不满足，丢弃。

如果使用到了ICP的话，explain结果的extra列，会有Using index condition.

三、疑问：如下这个sql为什么会使用到ICP呢？

如下：

mysql> show create table test_in\G
*************************** 1. row ***************************Table: test_in
Create Table: CREATE TABLE `test_in` (`id` int(11) NOT NULL AUTO_INCREMENT,`name` varchar(10) DEFAULT NULL,`age` int(11) DEFAULT NULL,PRIMARY KEY (`id`),UNIQUE KEY `age` (`age`),KEY `name` (`name`)
) ENGINE=InnoDB AUTO_INCREMENT=61596517 DEFAULT CHARSET=utf8mb4
1 row in set (0.00 sec)mysql> explain select * from test_in where age in (1,2,3,4,5,6,7,8);
+----+-------------+---------+------------+-------+---------------+------+---------+------+------+----------+-----------------------+
| id | select_type | table   | partitions | type  | possible_keys | key  | key_len | ref  | rows | filtered | Extra                 |
+----+-------------+---------+------------+-------+---------------+------+---------+------+------+----------+-----------------------+
|  1 | SIMPLE      | test_in | NULL       | range | age           | age  | 5       | NULL |    8 |   100.00 | Using index condition |
+----+-------------+---------+------------+-------+---------------+------+---------+------+------+----------+-----------------------+
1 row in set, 1 warning (0.00 sec)

可以看到并不是只有在多列索引中才会用到ICP特性，对于索引条件的in或者or的查询，都会用到。而下推的条件正是in或者or条件。

这种索引下推对性能有很大的提升吗？这部分正在测试研究中。

四、代码控制

关于ICP的流程控制大体上分为两部分，一是server层判断是否可以进行ICP，并且确定ICP的条件，传递给存储引擎；二是存储引擎去真正的执行ICP的过程。

server层处理

server层的处理在sql优化阶段，入口函数为QEP_TAB::push_index_cond(JOIN_TAB const*, unsigned int, Opt_trace_object*) sql_select.cc:1769,如下

 if (condition() &&tbl->file->index_flags(keyno, 0, 1) &HA_DO_INDEX_COND_PUSHDOWN &&hint_key_state(join_->thd, tbl, keyno, ICP_HINT_ENUM,OPTIMIZER_SWITCH_INDEX_CONDITION_PUSHDOWN) &&join_->thd->lex->sql_command != SQLCOM_UPDATE_MULTI &&join_->thd->lex->sql_command != SQLCOM_DELETE_MULTI &&!has_guarded_conds() &&type() != JT_CONST && type() != JT_SYSTEM &&!(keyno == tbl->s->primary_key &&tbl->file->primary_key_is_clustered())) //判断是否满足ICP的各种前置条件{DBUG_EXECUTE("where", print_where(condition(), "full cond",QT_ORDINARY););Item *idx_cond= make_cond_for_index(condition(), tbl,keyno, other_tbls_ok); //生成下推条件DBUG_EXECUTE("where", print_where(idx_cond, "idx cond", QT_ORDINARY););if (idx_cond){/*Check that the condition to push actually contains fields fromthe index. Without any fields from the index it is unlikelythat it will filter out any records since the conditions onfields from other tables in most cases have already beenevaluated.*/idx_cond->update_used_tables();if ((idx_cond->used_tables() & table_ref->map()) == 0){/*The following assert is to check that we only skip pushing theindex condition for the following situations:1. We actually are allowed to generate an index condition on anothertable.2. The index condition is a constant item.3. The index condition contains an updatable user variable(test this by checking that the RAND_TABLE_BIT is set).*/DBUG_ASSERT(other_tbls_ok ||                                  // 1idx_cond->const_item() ||                         // 2(idx_cond->used_tables() & RAND_TABLE_BIT) );     // 3DBUG_VOID_RETURN;}Item *idx_remainder_cond= 0;/*For BKA cache we store condition to special BKA cache fieldbecause evaluation of the condition requires additional operationsbefore the evaluation. This condition is used in JOIN_CACHE_BKA[_UNIQUE]::skip_index_tuple() functions.*/if (join_tab->use_join_cache() &&/*if cache is used then the value is TRUE only for BKA[_UNIQUE] cache (see setup_join_buffering() func).In this case other_tbls_ok is an equivalent ofcache->is_key_access().*/other_tbls_ok &&(idx_cond->used_tables() &~(table_ref->map() | join_->const_table_map))){cache_idx_cond= idx_cond;trace_obj->add("pushed_to_BKA", true);}else{idx_remainder_cond= tbl->file->idx_cond_push(keyno, idx_cond);DBUG_EXECUTE("where",print_where(tbl->file->pushed_idx_cond, "icp cond", QT_ORDINARY););}/*Disable eq_ref's "lookup cache" if we've pushed down an indexcondition. TODO: This check happens to work on current ICP implementations, butthere may exist a compliant implementation that will not work correctly with it. Sort this out when we stabilize the conditionpushdown APIs.*/if (idx_remainder_cond != idx_cond){ref().disable_cache= TRUE;trace_obj->add("pushed_index_condition", idx_cond);}Item *row_cond= make_cond_remainder(condition(), TRUE);DBUG_EXECUTE("where", print_where(row_cond, "remainder cond",QT_ORDINARY););if (row_cond){if (idx_remainder_cond)and_conditions(&row_cond, idx_remainder_cond);idx_remainder_cond= row_cond;}set_condition(idx_remainder_cond);trace_obj->add("table_condition_attached", idx_remainder_cond);}}

engine层处理

负责处理ICP逻辑在函数row_search_mvcc(unsigned char*, page_cur_mode_t, row_prebuilt_t*, unsigned long, unsigned long) row0sel.cc:5723,如下

	switch (row_search_idx_cond_check(buf, prebuilt, rec, offsets)) {case ICP_NO_MATCH:if (did_semi_consistent_read) {row_unlock_for_mysql(prebuilt, TRUE);}goto next_rec;case ICP_OUT_OF_RANGE:err = DB_RECORD_NOT_FOUND;goto idx_cond_failed;case ICP_MATCH:break;}

其中row_search_idx_cond_check来判断索引元组是否匹配下推的条件。

这篇关于MySQL-优化-ICP(Index condition pushdown)详解的文章就介绍到这儿，希望我们推荐的文章对编程师们有所帮助！


http://www.chinasem.cn/article/737104。

相关文章

Vue3 的 shallowRef 和 shallowReactive：优化性能

Vue3 的 shallowRef 和 shallowReactive：优化性能

大家对 Vue3 的 ref 和 reactive 都很熟悉，那么对 shallowRef 和 shallowReactive 是否了解呢？ 在编程和数据结构中，“shallow”（浅层）通常指对数据结构的最外层进行操作，而不递归地处理其内部或嵌套的数据。这种处理方式关注的是数据结构的第一层属性或元素，而忽略更深层次的嵌套内容。 1. 浅层与深层的对比 1.1 浅层（Shallow） 定义
阅读更多...
Spring Security基于数据库验证流程详解

Spring Security基于数据库验证流程详解

Spring Security 校验流程图 相关解释说明（认真看哦） AbstractAuthenticationProcessingFilter 抽象类 /*** 调用 #requiresAuthentication(HttpServletRequest, HttpServletResponse) 决定是否需要进行验证操作。* 如果需要验证，则会调用 #attemptAuthentica
阅读更多...
SQL中的外键约束

SQL中的外键约束

外键约束用于表示两张表中的指标连接关系。外键约束的作用主要有以下三点： 1.确保子表中的某个字段（外键）只能引用父表中的有效记录2.主表中的列被删除时，子表中的关联列也会被删除3.主表中的列更新时，子表中的关联元素也会被更新 子表中的元素指向主表 以下是一个外键约束的实例展示
阅读更多...
基于MySQL Binlog的Elasticsearch数据同步实践

基于MySQL Binlog的Elasticsearch数据同步实践

一、为什么要做 随着马蜂窝的逐渐发展，我们的业务数据越来越多，单纯使用 MySQL 已经不能满足我们的数据查询需求，例如对于商品、订单等数据的多维度检索。 使用 Elasticsearch 存储业务数据可以很好的解决我们业务中的搜索需求。而数据进行异构存储后，随之而来的就是数据同步的问题。 二、现有方法及问题 对于数据同步，我们目前的解决方案是建立数据中间表。把需要检索的业务数据，统一放到一张M
阅读更多...
如何去写一手好SQL

如何去写一手好SQL

MySQL性能 最大数据量 抛开数据量和并发数，谈性能都是耍流氓。MySQL没有限制单表最大记录数，它取决于操作系统对文件大小的限制。 《阿里巴巴Java开发手册》提出单表行数超过500万行或者单表容量超过2GB，才推荐分库分表。性能由综合因素决定，抛开业务复杂度，影响程度依次是硬件配置、MySQL配置、数据表设计、索引优化。500万这个值仅供参考，并非铁律。 博主曾经操作过超过4亿行数据
阅读更多...
HDFS—存储优化（纠删码）

HDFS—存储优化（纠删码）

纠删码原理 HDFS 默认情况下，一个文件有3个副本，这样提高了数据的可靠性，但也带来了2倍的冗余开销。 Hadoop3.x 引入了纠删码，采用计算的方式，可以节省约50％左右的存储空间。 此种方式节约了空间，但是会增加 cpu 的计算。 纠删码策略是给具体一个路径设置。所有往此路径下存储的文件，都会执行此策略。 默认只开启对 RS-6-3-1024k
阅读更多...
性能分析之MySQL索引实战案例

性能分析之MySQL索引实战案例

文章目录 一、前言二、准备三、MySQL索引优化四、MySQL 索引知识回顾五、总结 一、前言 在上一讲性能工具之 JProfiler 简单登录案例分析实战中已经发现SQL没有建立索引问题，本文将一起从代码层去分析为什么没有建立索引？ 开源ERP项目地址：https://gitee.com/jishenghua/JSH_ERP 二、准备 打开IDEA找到登录请求资源路径位置
阅读更多...
使用opencv优化图片（画面变清晰）

使用opencv优化图片（画面变清晰）

文章目录 需求影响照片清晰度的因素 实现降噪测试代码 锐化空间锐化Unsharp Masking频率域锐化对比测试 对比度增强常用算法对比测试 需求 对图像进行优化，使其看起来更清晰，同时保持尺寸不变，通常涉及到图像处理技术如锐化、降噪、对比度增强等 影响照片清晰度的因素 影响照片清晰度的因素有很多，主要可以从以下几个方面来分析 1. 拍摄设备 相机传感器：相机传
阅读更多...
MySQL数据库宕机，启动不起来，教你一招搞定！

MySQL数据库宕机，启动不起来，教你一招搞定！

作者介绍：老苏，10余年DBA工作运维经验，擅长Oracle、MySQL、PG、Mongodb数据库运维（如安装迁移，性能优化、故障应急处理等）公众号：老苏畅谈运维欢迎关注本人公众号，更多精彩与您分享。 MySQL数据库宕机，数据页损坏问题，启动不起来，该如何排查和解决，本文将为你说明具体的排查过程。 查看MySQL error日志 查看 MySQL error日志，排查哪个表（表空间
阅读更多...
OpenHarmony鸿蒙开发（ Beta5.0）无感配网详解

OpenHarmony鸿蒙开发（ Beta5.0）无感配网详解

1、简介 无感配网是指在设备联网过程中无需输入热点相关账号信息，即可快速实现设备配网，是一种兼顾高效性、可靠性和安全性的配网方式。 2、配网原理 2.1 通信原理 手机和智能设备之间的信息传递，利用特有的NAN协议实现。利用手机和智能设备之间的WiFi 感知订阅、发布能力，实现了数字管家应用和设备之间的发现。在完成设备间的认证和响应后，即可发送相关配网数据。同时还支持与常规Sof
阅读更多...

Copyright China编程 23002807@qq.com

沪ICP备2023033072号-2