MySQL索引下推（ICP）的简单理解与示例

前言

索引下推（Index Condition Pushdown, 简称ICP）是MySQL 5.6 版本的新特性，它能减少回表查询次数，提升检索效率。

MySQL体系结构

要明白索引下推，首先要了解MySQL的体系结构：

上图来自MySQL官方文档。

通常把MySQL从上至下分为以下几层：

  • MySQL服务层：包括NoSQL和SQL接口、查询解析器、优化器、缓存和Buffer等组件。
  • 存储引擎层：各种插件式的表格存储引擎，实现事务、索引等各种存储引擎相关的特性。
  • 文件系统层: 读写物理文件。

MySQL服务层负责SQL语法解析、触发器、视图、内置函数、binlog、生成执行计划等，并调用存储引擎层去执行数据的存储和检索。“索引下推”的“下”其实就是指将部分上层（服务层）负责的事情，交给了下层（存储引擎）去处理。

索引下推案例

假设用户表数据和结构如下：

id age birthday name
1 18 01-01 User1
2 19 03-01 User2
3 20 03-01 User3
4 21 03-01 User4
5 22 05-01 User5
6 18 06-01 User6
7 24 01-01 User7

创建一个联合索引(age, birthday)，并查询出年龄>20，且生日为03-01的用户：

select * from user where age>20 and birthday="03-01"

由于age字段使用了范围查询，根据最左前缀原则，这种情况只能使用age字段进行范围查询，索引中的birthday字段无法使用。使用explain查看执行计划：

+------+-------------+-------+-------+---------------+--------------+---------+------+------+-----------------------+
| id   | select_type | table | type  | possible_keys | key          | key_len | ref  | rows | Extra                 |
+------+-------------+-------+-------+---------------+--------------+---------+------+------+-----------------------+
|    1 | SIMPLE      | user  | range | age_birthday  | age_birthday | 4       | NULL | 3    | Using index condition |
+------+-------------+-------+-------+---------------+--------------+---------+------+------+-----------------------+

可以看到虽然使用了age_birthday索引，但是索引长度key_len只有4，说明只有联合索引只有age字段生效了（因为age字段是int类型，占用4个字节）。最后Extra列的Using index condition表示这个查询使用了索引下推优化。

为在没有索引下推的情况下，执行步骤如下：

  • 存储引擎根据索引查找出age>20的用户id，分别是：4,5,7
  • 存储引擎到表格中取出id in (4,5,7)的3条记录，返回给服务层
  • 服务层过滤掉不符合birthday="03-01"条件的记录，最后返回查询结果为id=4的1行记录。

如果开启了索引下推优化，执行步骤如下：

  1. 存储引擎根据索引查找出age>20的用户id，并使用索引中的birthday字段过滤掉不符合birthday="03-01"条件的记录，最后得到id=4；
  2. 存储引擎到表格中取出id=4的1条记录，返回给服务层；
  3. 服务层过滤掉不符合birthday="03-01"条件的记录，最后返回查询结果为id=4的1行记录。

启用索引下推后，把where条件由MySQL服务层放到了存储引擎层去执行，带来的好处就是存储引擎根据id到表格中读取数据的次数变少了。在上面这个例子中，没有索引下推时需要多回表查询2次。并且回表查询很可能是离散IO，在某些情况下，对数据库性能会有较大提升。

总结

到此这篇关于MySQL索引下推（ICP）的简单理解与示例的文章就介绍到这了,更多相关MySQL索引下推（ICP）内容请搜索我们以前的文章或继续浏览下面的相关文章希望大家以后多多支持我们！

赞 (0)

相关推荐

  • Mysql性能优化之索引下推

    Mysql性能优化之索引下推

    索引下推(index condition pushdown )简称ICP,在Mysql5.6的版本上推出,用于优化查询. 在不使用ICP的情况下,在使用非主键索引(又叫普通索引或者二级索引)进行查询时,存储引擎通过索引检索到数据,然后返回给MySQL服务器,服务器然后判断数据是否符合条件 . 在使用ICP的情况下,如果存在某些被索引的列的判断条件时,MySQL服务器将这一部分判断条件传递给存储引擎,然后由存储引擎通过判断索引是否符合MySQL服务器传递的条件,只有当索引符合条件时才会将数据检索出

  • MySQL索引下推（ICP）的简单理解与示例

    MySQL索引下推（ICP）的简单理解与示例

    前言 索引下推(Index Condition Pushdown, 简称ICP)是MySQL 5.6 版本的新特性,它能减少回表查询次数,提升检索效率. MySQL体系结构 要明白索引下推,首先要了解MySQL的体系结构: 上图来自MySQL官方文档. 通常把MySQL从上至下分为以下几层: MySQL服务层:包括NoSQL和SQL接口.查询解析器.优化器.缓存和Buffer等组件. 存储引擎层:各种插件式的表格存储引擎,实现事务.索引等各种存储引擎相关的特性. 文件系统层: 读写物理文件. M

  • 一篇文章读懂什么是MySQL索引下推（ICP）

    一篇文章读懂什么是MySQL索引下推（ICP）

    目录 一.简介 二.原理 三.实践 3.1 不使用索引下推 3.2 使用索引下推 四.使用条件 五.相关系统参数 总结 一.简介 ICP(Index Condition Pushdown)是在MySQL 5.6版本上推出的查询优化策略,把本来由Server层做的索引条件检查下推给存储引擎层来做,以降低回表和访问存储引擎的次数,提高查询效率. 二.原理 为了理解ICP是如何工作的,我们先了解下没有使用ICP的情况下,MySQL是如何查询的: 存储引擎读取索引记录: 根据索引中的主键值,定位并读取完

  • 五分钟让你快速弄懂MySQL索引下推

    五分钟让你快速弄懂MySQL索引下推

    大家好,我是老三,今天分享一个小知识点--索引下推. 如果你在面试中,听到MySQL5.6"."索引优化" 之类的词语,你就要立马get到,这个问的是"索引下推". 什么是索引下推 索引下推(Index Condition Pushdown,简称ICP),是MySQL5.6版本的新特性,它能减少回表查询次数,提高查询效率. 索引下推优化的原理 我们先简单了解一下MySQL大概的架构: MySQL服务层负责SQL语法解析.生成执行计划等,并调用存储引擎层去执

  • MySQL索引下推详细

    MySQL索引下推详细

    目录 1.最左前缀原则 2.回表 3.索引下推 前言: 索引下推(ICP)是针对MySQL使用索引从表中检索数据行的情况的优 在没有索引下推的情况下,MySQL通过存储引擎遍历索引来定位表中的数据行并将它们返回给MySQl服务器,服务器再进行WHERE条件的判断,确认是否将数据行加入结果集. 开启索引下推,且WHERE条件部分可以仅使用索引中的列来评估,这时MySQL服务器会将这部分WHERE条件下推到存储引擎,接着存储引擎使用索引条目评估推送的索引条件,仅当满足该条件时才从表中进行读取 索引下

  • 一篇文章带你了解MySQL索引下推

    一篇文章带你了解MySQL索引下推

    目录 前言 SELECT 语句执行过程 什么是索引下推? 动手实验: 索引下推限制 拓展:虚拟列 总结 前言 本文围绕这三个话题来学习索引下推: SELECT 语句执行过程 什么是索引下推? 索引下推限制 SELECT 语句执行过程 MySQL 数据库由 Server 层和 Engine 层组成: Server 层: 有 SQL 分析器.SQL 优化器.SQL 执行器,用于负责 SQL 语句的具体执行过程. Engine 层: 负责存储具体的数据,如最常使用的 InnoDB 存储引擎,还有用于在

  • 一篇文章带你掌握MySQL索引下推

    目录 1.什么是索引下推 2.案例 2.1.MySQL5.5版本 2.2.MySQL5.7版本 3.小结 1.什么是索引下推 索引下推(Index Condition PushDown,简称ICP)是从MySQL5.6开始引入的一个特性,索引下推通过减少回表的次数来提高数据库的查询效率; 2.案例 准备: ①.为了演示索引下推,需要安装MySQL5.5和MySQL5.7两个版本的MySQL,因为索引下推是MySQL5.6版本中开始引入的新特性,所以这两个版本就可以演示出索引下推的特点; ②.数据

  • 五分钟带你搞懂MySQL索引下推

    五分钟带你搞懂MySQL索引下推

    目录 什么是索引下推 索引下推优化的原理 索引下推的具体实践 没有使用ICP 使用ICP 索引下推使用条件 相关系统参数 总结 如果你在面试中,听到MySQL5.6"."索引优化" 之类的词语,你就要立马get到,这个问的是"索引下推". 什么是索引下推 索引下推(Index Condition Pushdown,简称ICP),是MySQL5.6版本的新特性,它能减少回表查询次数,提高查询效率. 索引下推优化的原理 我们先简单了解一下MySQL大概的架构:

  • MySQL带你秒懂索引下推

    MySQL带你秒懂索引下推

    目录 一.索引下推优化的原理 二.索引下推的具体实践 1.没有使用ICP 2.使用ICP 三.索引下推使用条件 索引下推(Index Condition Pushdown,简称ICP),是MySQL5.6版本的新特性,它能减少回表查询次数,提高查询效率. 一.索引下推优化的原理 我们先简单了解一下MySQL大概的架构: MySQL服务层负责SQL语法解析.生成执行计划等,并调用存储引擎层去执行数据的存储和检索. 索引下推的下推其实就是指将部分上层(服务层)负责的事情,交给了下层(引擎层)去处理.

  • MySQL查询性能优化索引下推

    MySQL查询性能优化索引下推

    目录 前言 1. 索引下推的作用 2. 案例实践 3. 索引下推配置 4. 索引下推原理剖析 5. 索引下推应用范围 前言 前面已经讲了MySQL的其他查询性能优化方式,没看过可以去了解一下: MySQL查询性能优化七种方式索引潜水 MySQL查询性能优化武器之链路追踪 今天要讲的是MySQL的另一种查询性能优化方式 — 索引下推(Index Condition Pushdown,简称ICP),是MySQL5.6版本增加的特性. 1. 索引下推的作用 主要作用有两个: 减少回表查询的次数 减少存

随机推荐