“MySQL5.6”、“索引优化”,其实都是索引下推!

Mysql【索引覆盖、索引下推、索引合并、索引跳跃】介绍 上图来自MySQL官方文档。MySQL服务层:包括NoSQL和SQL接口、查询解析器、优化器、缓存和Buffer等组件。存储引擎层:各种插件式的表格存储引擎,实现事务、索引等各种存储引擎相关的特性。文件系统层: 读写物理文件。MySQL服务层负责SQL语法解析、触发器、视图、内置函数、binlog、生成执行计划等,并调用存储引擎层去执行数据的存储和检索。我们后续说到的“索引下推”,它的“下”其实就是指将部分上层(服务层)负责的事情,交给了下层(存储引擎)去处理。 阅读详情

如果你在面试中,听到MySQL5.6”、“索引优化” 之类的词语,你就要立马get到,这个问的是“索引下推”。

什么是索引下推

索引下推(Index Condition Pushdown,简称ICP),是MySQL5.6版本的新特性,它能减少回表查询次数,提高查询效率。

索引下推优化的原理

我们先简单了解一下MySQL大概的架构:

MySQL服务层负责SQL语法解析、生成执行计划等,并调用存储引擎层去执行数据的存储和检索。

索引下推的下推其实就是指将部分上层(服务层)负责的事情,交给了下层(引擎层)去处理。

我们来具体看一下,在没有使用ICP的情况下,MySQL的查询:

  • 存储引擎读取索引记录;
  • 根据索引中的主键值,定位并读取完整的行记录;
  • 存储引擎把记录交给Server层去检测该记录是否满足WHERE条件。

使用ICP的情况下,查询过程:

  • 存储引擎读取索引记录(不是完整的行记录);
  • 判断WHERE条件部分能否用索引中的列来做检查,条件不满足,则处理下一行索引记录;
  • 条件满足,使用索引中的主键去定位并读取完整的行记录(就是所谓的回表);
  • 存储引擎把记录交给Server层,Server层检测该记录是否满足WHERE条件的其余部分。

索引下推的具体实践

理论比较抽象,我们来上一个实践。

使用一张用户表tuser,表里创建联合索引(name, age)。

如果现在有一个需求:检索出表中名字第一个字是张,而且年龄是10岁的所有用户。那么,SQL语句是这么写的:

select * from tuser where name like '张%' and age=10;

假如你了解索引最左匹配原则,那么就知道这个语句在搜索索引树的时候,只能用 张,找到的第一个满足条件的记录id为1。

那接下来的步骤是什么呢?

没有使用ICP

在MySQL 5.6之前,存储引擎根据通过联合索引找到name likelike '张%' 的主键id(1、4),逐一进行回表扫描,去聚簇索引找到完整的行记录,server层再对数据根据age=10进行筛选。

我们看一下示意图:

可以看到需要回表两次,把我们联合索引的另一个字段age浪费了。

使用ICP

而MySQL 5.6 以后, 存储引擎根据(name,age)联合索引,找到name likelike '张%',由于联合索引中包含age列,所以存储引擎直接再联合索引里按照age=10过滤。按照过滤后的数据再一一进行回表扫描。

我们看一下示意图:

可以看到只回表了一次。

除此之外我们还可以看一下执行计划,看到Extra一列里 Using index condition,这就是用到了索引下推。

+----+-------------+-------+------------+-------+---------------+----------+---------+------+------+----------+-----------------------+
| id | select_type | table | partitions | type  | possible_keys | key      | key_len | ref  | rows | filtered | Extra                 |
+----+-------------+-------+------------+-------+---------------+----------+---------+------+------+----------+-----------------------+
|  1 | SIMPLE      | tuser | NULL       | range | na_index      | na_index | 102     | NULL |    2 |    25.00 | Using index condition |
+----+-------------+-------+------------+-------+---------------+----------+---------+------+------+----------+-----------------------+

索引下推使用条件

  • 只能用于range、 ref、 eq_ref、ref_or_null访问方法;
  • 只能用于InnoDB和 MyISAM存储引擎及其分区表;
  • 对InnoDB存储引擎来说,索引下推只适用于二级索引(也叫辅助索引);

索引下推的目的是为了减少回表次数,也就是要减少IO操作。对于InnoDB的聚簇索引来说,数据和索引是在一起的,不存在回表这一说。

  • 引用了子查询的条件不能下推;
  • 引用了存储函数的条件不能下推,因为存储引擎无法调用存储函数。

相关系统参数

索引条件下推默认是开启的,可以使用系统参数optimizer_switch来控制器是否开启。

查看默认状态:

mysql> select @@optimizer_switch\G;
*************************** 1\. row ***************************
@@optimizer_switch: index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,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=on,condition_fanout_filter=on,derived_merge=on
1 row in set (0.00 sec)

切换状态:

set optimizer_switch="index_condition_pushdown=off";
set optimizer_switch="index_condition_pushdown=on";
Zabbix在Ubuntu 22.04上的性能优化与监控实战:从基础安装到高级配置 本文详细介绍了在Ubuntu 22.04系统上部署和优化Zabbix监控系统的完整流程,从基础安装到高级配置。内容包括MySQL数据库设置、Zabbix服务器与Web前端优化、性能调优策略以及实战监控场景应用,帮助用户构建高效的企业级监控解决方案。 阅读详情

相关推荐

AI编码的“黑箱危机”终结者:OpenSpec如何让你的代码从“猜谜”变“契约”?

OpenSpec的真正价值,在于它将AI编码从"经验驱动"推向"契约驱动"——当人类与AI对"目标是什么"达成显式共识,编码就从"猜谜游戏"变成了"工程实现"。这种转变不仅提升效率,更重塑了开发者与AI的关系:AI从"替代者"回归"协作者",而人类则聚焦于"定义正确的事"而非"正确地做事"。对于开发者,OpenSpec提供了驾驭AI的缰绳;对于团队,它构建了协作的基石;对于行业,它预示着AI编码工业化的开端。

haolove527的专栏 1351

面试官:什么是索引下推?什么是 MRR 优化?怎么才能更好的为表创建索引?

索引下推也被称为索引条件下推 (Index Condition Pushdown)ICPMySQL新添加的特性,用于优化数据查询的。这里尼恩给大家做一下系统化、体系化的线程池梳理,使得大家可以充分展示一下大家雄厚的 “技术肌肉”,让面试官爱到 “不能自已、口水直流”。也一并把这个题目以及参考答案,收入咱们的 《尼恩Java面试宝典 PDF》,供后面的小伙伴参考,提升大家的 3高 架构、设计、开发水平。

架构师尼恩 1万+

LangChain入门(二)-通过 Google 搜索并返回答案

并复制他给我们生成 api key。(博主使用了GitHub账号登录)-邮箱验证-手机号验证-订阅-api-key。Serpapi 提供了 google 搜索的 api 接口。首先需要我们到 Serpapi 官网上注册一个用户,

Good Luck 1万+

什么是索引下推

索引下推(INDEX CONDITION PUSHDOWN,简称 ICP)是在 MySQL 5.6 针对扫描二级索引的一项优化改进。总的来说是通过把索引过滤条件下推到存储引擎,来减少 MySQL 存储引擎访问基表的次数以及 MySQL 服务层访问存储引擎的次数。ICP 适用于 MYISAM 和 INNODB,本篇的内容只基于 INNODB。MySQL 服务层:也就是 SERVER 层,用来解析 SQL 的语法、语义、生成查询计划、接管从 MySQL 存储引擎层上推的数据进行二次过滤等等。

天明的博客 4697

基于 YOLO26 的车辆实时检测与计数系统

本文提出了一种基于YOLO26的车辆实时检测与计数系统。系统采用CSPDarknet53作为主干网络,结合特征金字塔和多尺度预测实现高精度车辆检测。通过DeepSORT算法进行目标跟踪,并统计车辆数量、行驶方向和交通流量。实验表明,该系统在保持实时性能的同时,检测精度达到92.3%,可有效应用于智能交通管理。系统采用Python实现,支持GPU加速,适用于多种交通场景下的车辆监测需求。

一键难忘的博客 911

4、超声驱动的微/纳米机器人:原理与应用探索

本文系统探讨了超声驱动的微/纳米机器人的物理原理与主要类型,重点分析了声辐射力和声流两种核心效应的作用机制。详细介绍了微棒流子、气泡流子、鞭毛流子和声射流四类典型超声驱动电机的工作原理、特点及局限性,并综述了其在生物医学、微纳制造和微流体操控等领域的应用潜力。文章还总结了当前研究进展与挑战,展望了未来发展方向,为该领域的深入研究提供了理论基础和应用参考。

herb5的博客 135

索引下推详解

本文目录概述适用条件工作过程不使用索引条件下推优化时的查询过程使用索引条件下推优化时的查询过程EXPLAN分析示例配置思考其他 概述 索引条件下推优化(Index Condition Pushdown (ICP) )是MySQL5.6添加的,用于优化数据查询。 不使用索引条件下推优化时存储引擎通过索引检索到数据,然后返回给MySQL服务器,服务器然后判断数据是否符合条件。 **当使用索引条件下推优化时,如果存在某些被索引的列的判断条件时,MySQL服务器将这一部分判断条件传递给存储引擎,然后由存储引擎通

加油,努力,认真 8472

基于HTML5的酒店在线预订系统设计与实现

1.背景介绍 酒店预订系统,作为现代酒店业务中的重要一环,其便捷性、智能化程度直接影响着酒店的服务质量和经营效果。近年来,随着互联网技术和移动设备的快速发展,以及消费者行为的日益电子化,酒店预订系统也正在经历一场深刻的变革。传统的酒店预订系统往往依赖于复杂的软件安装和硬件设备,而新一代的酒店预订系统则更加强调在线、移动和云端的特性。HTML5作为一种前

AI天才研究院 1910

一文彻底理解索引下推

索引下推的产生一定围绕着回表,没有回表那就没必要产生索引下推,因为上面也说了索引下推的目的就是减少回表,而不是避免回表。(题外话:避免回表使用索引覆盖——建立覆盖索引)因为%在前面所以不会走索引,当然如果不是select * ,会使用二级索引的index。二级索引取出的数据是依次回表还是一次回表?索引下推是为了什么发明的?extra=using index condition 发生了索引下推。注意:索引下推是为了减少回表而发明的。引入的一个特性,索引下推。MySQL5.6开始。

zyjzyjjyzjyz的博客 3255

Android系统去掉长按webview界面弹框中的分享功能

Android 系统去解决长按显示内容,去掉分享功能,不论那个界面,是所有界面都需要满足。如果仅仅去掉系统自带的,那么系统来处理如果客户自定义,部分App自定义的菜单项,不是调用系统的,那么还是需要应用去解决的。之前做过Editext,其实还需要去TextView,最难点的就是WebView中的HTML了 去分享功能了。WebView中涉及到分享功能菜单的界面,/***//***//***//***//***//***//***//***//**

十年饮冰,难凉热血 1020

【大模型】从0到1:DeepSeek + Coze API调用全攻略,企业,开发个人专属AI智能体使用详解,小白也能轻松上手

本文介绍了如何通过API将Coze智能体集成到自有系统中的完整步骤:1)获取API token和个人工作空间的space_id;2)通过GET请求获取bot_id;3)使用POST发起对话请求,需包含bot_id、user_id和问题内容;4)通过两次API调用异步获取响应消息。文章还解释了Coze采用异步处理机制的原因,并预告了下期将讲解Java调用Coze接口的实现方法。整个过程需要使用API工具进行调试,确保了系统集成的可行性。

qq_27246521的博客 824

MySQL索引下推详解

MySQL索引下推(ICP)是5.6版本引入的查询优化技术,它允许存储引擎在索引扫描阶段应用部分WHERE条件,减少回表次数和I/O开销。当使用二级索引且需要回表时,ICP可将范围查询条件下推到存储引擎层进行过滤,避免将不符合条件的记录读取到服务器层。通过对比测试发现,ICP能使查询性能提升近50%。触发条件包括:使用InnoDB/MyISAM引擎、存在回表操作、查询条件包含索引列范围查询等。ICP默认开启,可通过optimizer_switch参数控制。合理使用ICP可显著提升大表查询性能。

qq_46769552的博客 4622

风光储并网对电力系统稳定性影响的仿真研究

本文研究了风光储并网对电力系统稳定性的影响,基于MATLAB/Simulink平台搭建了IEEE 9节点测试系统的风光储并网模型。研究对比了光储、风储和风光储三种并网配置在多种干扰下的稳定性表现。结果表明:光储系统对频率变化最敏感,风储系统具有更强的功率波动适应能力,而风光储联合系统通过互补效应展现出最佳综合稳定性。储能系统在所有配置中均显著改善了频率恢复性能,验证了其在新能源并网中的关键支撑作用。研究为高比例可再生能源电力系统的稳定运行提供了仿真依据和技术参考。

huanghm88的专栏 156

Mysql性能优化:什么是索引下推?

什么是索引下推?索引下推(index condition pushdown )简称ICP,在Mysql5.6的版本上推出,用于优化查询。在不使用ICP的情况下,在使用非主键索引(又叫普...

码猿技术专栏 7308

WINCC7.3破解文件

WINCC7.3硬件够破解文件,有需要的下载!WINCC7.3硬件够破解文件,有需要的下载!

android 蓝牙通讯demo

android 下蓝牙通讯demo,实现了发现蓝牙,使能蓝牙,通过蓝牙发送消息。

“MySQL5.6”、“索引优化”,其实都是索引下推

如果你在面试中,听到“MySQL5.6”、“索引优化” 之类的词语,你就要立马get到,这个问的是“索引下推”。 什么是索引下推 索引下推(Index Condition Pushdown,简称ICP),是MySQL5.6版本的新特性,它能减少回表查询次数,提高查询效率。 索引下推优化的原理 我们先简单了解一下MySQL大概的架构: MySQL服务层负责SQL语法解析、生成执行计划等,并调用存储引擎层去执行数据的存储和检索。 索引下推的下推其实就是指将部分上层(服务层)负责的事情,交给了

qq_64513388的博客 2566

TMS_WebGMaps_1.9.8.0_Full_Source

TMS_WebGMaps_1.9.8.0_Full_Source 全源码 delphi cbuilder

MYSQL——索引下推

理解上面的知识后就可以回答以下问题。二级索引取出的数据是依次回表还是一次回表?答:依次回表。索引下推是为了什么发明的?答:减少回表,而不是避免回表(避免回表用覆盖索引)

ksidgx的博客 1717

索引下推,yyds?

索引的问题,已经跟大家聊了两篇文章了~今天再聊一个索引下推问题,也是非常有意思! 索引下推是从 MySQL5.6 开始引入一个特性,英文是 index condition pushdown,一般简称为 ICP,索引下推通过减少回表的次数,来提高数据库的查询效率。 有的小伙伴可能也看过一些关于 ICP 的概念,但是我觉得,概念比较简单,说一下很容易懂,但是在实际应用中,各种各样的情况非常多。所以接下来的内容我想通过几个具体的查询分析来和大家分享 ICP 到底是怎么一回事。 1. 索引下推 为了给大家演示索引下

江南一点雨的专栏 6754

【mysql】MySQL中的回表查询、索引覆盖、索引下推、谓词下推

索引下推(INDEX CONDITION PUSHDOWN,简称 ICP)是在 MySQL 5.6 针对扫描二级索引的一项优化改进。总的来说是通过把索引过滤条件下推到存储引擎,来减少 MySQL 存储引擎访问基表的次数以及 MySQL 服务层访问存储引擎的次数。ICP 适用于 MYISAM 和 INNODB,本篇的内容只基于 INNODB。(1)MySQL 服务层:也就是 SERVER 层,用来解析 SQL 的语法、语义、生成查询计划、接管从 MySQL 存储引擎层上推的数据进行二次过滤等等。

weixin_44823875的博客 5878

MySQL- 索引下推

索引下推(ICP)是 MySQL 5.6 引入的一个重要优化技术,它通过将部分查询条件“下推”到索引扫描阶段来减少回表操作,从而提高查询性能。索引下推特别适合使用复合索引的场景,通过有效地减少不必要的 I/O 操作,能够显著提升查询的执行效率。在实际应用中,可以通过EXPLAIN语句来查看索引下推是否生效,并结合查询模式和索引设计来充分利用这一优化技术。

青衫客36的博客 3238

一文看懂MySQL索引下推(ICP)

本文主要介绍MySQL索引下推(ICP)和索引下推底层原理

weixin_46425661的博客 3966

mysql(四)索引下推

问题1:索引下推如何开启和关闭?(MySQL5.6以后的版本) 问题2:索引下推在哪些情况下无法使用? 2.1下推条件遇到子查询 2.2下推条件遇到函数 2.3非InnoDB表和MyISAM表 注意事项: 1、索引下推只能存在联合索引里 2、范围列可以用到索引,但是范围列后面的列无法用到索引 3、不要使用SELECT * FROM 4、减少子查询、范围等查询、慎用函数。

DJyzh的博客 2192

Mysql-索引下推

MySQL索引下推详细介绍

qq_63366830的博客 1382

索引下推(5.6版本+)

索引下推 有了索引下推优化,可以在有like条件查询的情况下,减少回表次数。 对于user_table表,我们现在有(username,age)联合索引 如果现在有一个需求,查出名称中以“张”开头且年龄小于等于10的用户信息,语句如下: select * from user_table where username like '张%' and age > 10 语句有两种执行可能: 根据...

憧憬的专栏 1万+

MySQL性能优化之:索引下推

转自:https://www.cnblogs.com/Chenjiabing/p/12600926.html 导读 索引下推(index condition pushdown )简称ICP,在Mysql5.6的版本上推出,用于优化查询。 在不使用ICP的情况下,在使用非主键索引(又叫普通索引或者二级索引)进行查询时,存储引擎通过索引检索到数据,然后返回给MySQL服务器,服务器然后判断数据是否符合条件 。 在使用ICP的情况下,如果存在某些被索引的列的判断条件时,MySQL服务器

渣渣 2769

MySQL优化之索引下推

MySQL优化之索引下推

个人博客,欢迎收藏。 1274

MySQL 的回表、覆盖索引、索引下推

在研究mysql二级索引的时候,发现Mysql回表这个操作,往下研究了一下字面意思,找到索引,回到表中找数据 解释一下就是:举个例子说明: 假如和两个字段是个联合索引,我们通过=18这个索引找到了二级索引树对应页所在的数据,但是由于是模糊查询,导致了这个字段的索引失效,我们得到了二级索引的这一页中=18的很多个数据(主键id),我们通过这些主键ID回到主键索引树里再查表里的数据,这个操作就是回表。另外回表的产生也是需要一定条件的,如果一次索引查询就能获得所有的select 记录(也就是联合索引已经包含了你查

想着百万年薪努力的小赵 3892

【深度剖析】MySQL 索引下推:原理、案例与性能提升实战

揭秘MySQL索引下推技术,轻松提升查询性能70%!深入剖析原理、适用场景与实战案例,一招解决回表性能瓶颈,让你的SQL查询速度飞起来!

异常君的博客 1303
上一篇: 阿里P8整理的《百亿级并发系统设计》实战教程,实在是太香了
下一篇: 为什么MySQL数据库索引选择使用B+树?
Java技术那些事儿
博客等级 码龄5年 2306粉丝 2584原创
评论
成就一亿技术人!
拼手气红包6.0元
还能输入1000个字符
 
 条评论被折叠 查看
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付:10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值