MySQL查询性能优化索引下推

目录
  • 前言
  • 1. 索引下推的作用
  • 2. 案例实践
  • 3. 索引下推配置
  • 4. 索引下推原理剖析
  • 5. 索引下推应用范围

前言

前面已经讲了MySQL的其他查询性能优化方式，没看过可以去了解一下：

MySQL查询性能优化七种方式索引潜水

MySQL查询性能优化武器之链路追踪

今天要讲的是MySQL的另一种查询性能优化方式 — 索引下推（Index Condition Pushdown，简称ICP），是MySQL5.6版本增加的特性。

1. 索引下推的作用

主要作用有两个：

  • 减少回表查询的次数
  • 减少存储引擎和MySQL Server层的数据传输量

总之就是了提升MySQL查询性能。

2. 案例实践

创建一张用户表，造点数据验证一下：

CREATE TABLE `user` (
  `id` int NOT NULL AUTO_INCREMENT COMMENT '主键',
  `name` varchar(100) NOT NULL COMMENT '姓名',
  `age` tinyint NOT NULL COMMENT '年龄',
  `gender` tinyint NOT NULL COMMENT '性别',
  PRIMARY KEY (`id`),
  KEY `idx_name_age` (`name`,`age`)
) ENGINE=InnoDB COMMENT='用户表';

在 姓名和年龄 (name,age) 两个字段上创建联合索引。

查询SQL执行计划，验证一下是否用到索引下推：

explain select * from user where name='一灯' and age>2;

执行计划中的Extra列显示了Using index condition，表示用到了索引下推的优化逻辑。

3. 索引下推配置

查看索引下推的配置：

show variables like '%optimizer_switch%';

如果输出结果中，显示 index_condition_pushdown=on，表示开启了索引下推。

也可以手动开启索引下推：

set optimizer_switch="index_condition_pushdown=on";

关闭索引下推：

set optimizer_switch="index_condition_pushdown=off";

4. 索引下推原理剖析

索引下推在底层到底是怎么实现的？

是怎么减少了回表的次数？

又减少了存储引擎和MySQL Server层的数据传输量？

在没有使用索引下推的情况，查询过程是这样的：

  • 存储引擎根据where条件中name索引字段，找到符合条件的3个主键ID
  • 然后二次回表查询，根据这3个主键ID去主键索引上找到3个整行记录
  • 把数据返回给MySQL Server层，再根据where中age条件，筛选出符合要求的一行记录
  • 返回给客户端

画两张图，就一目了然了。

下面这张图是回表查询的过程：

  • 先在联合索引上找到name=‘一灯’的3个主键ID
  • 再根据查到3个主键ID，去主键索引上找到3行记录

下面这张图是存储引擎返回给MySQL Server端的处理过程：

我们再看一下在使用索引下推的情况，查询过程是这样的：

  • 存储引擎根据where条件中name索引字段，找到符合条件的3行记录，再用age条件筛选出符合条件一个主键ID
  • 然后二次回表查询，根据这一个主键ID去主键索引上找到该整行记录
  • 把数据返回给MySQL Server层
  • 返回给客户端

现在是不是理解了索引下推的两个作用：

  • 减少回表查询的次数
  • 减少存储引擎和MySQL Server层的数据传输量

5. 索引下推应用范围

  • 适用于InnoDB 引擎和 MyISAM 引擎的查询
  • 适用于执行计划是range, ref, eq_ref, ref_or_null的范围查询
  • 对于InnoDB表，仅用于非聚簇索引。索引下推的目标是减少全行读取次数，从而减少 I/O 操作。对于 InnoDB聚集索引，完整的记录已经读入InnoDB 缓冲区。在这种情况下使用索引下推 不会减少 I/O。
  • 子查询不能使用索引下推
  • 存储过程不能使用索引下推

再附一张Explain执行计划详解图：

到此这篇关于MySQL查询性能优化索引下推的文章就介绍到这了,更多相关MySQL索引下推内容请搜索我们以前的文章或继续浏览下面的相关文章希望大家以后多多支持我们！

赞 (0)

相关推荐

  • MySQL带你秒懂索引下推

    MySQL带你秒懂索引下推

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

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

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

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

  • MySQL索引下推详细

    MySQL索引下推详细

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

  • 通过MySQL慢查询优化MySQL性能的方法讲解

    通过MySQL慢查询优化MySQL性能的方法讲解

    随着访问量的上升,MySQL数据库的压力就越大,几乎大部分使用MySQL架构的web应用在数据库上都会出现性能问题,通过mysql慢查询日志跟踪有问题的查询非常有用,可以分析出当前程序里有很耗费资源的sql语句. 慢查询日志我们可以通过my.cnf文件设置开启,下面先来看一下相关参数的意义 log-slow-queries <slow_query_log_file> 存放slow query日志的文件.你必须保证mysql server进程mysqld_safe进程用户对该文件有w权限. lo

  • 大幅优化MySQL查询性能的奇技淫巧

    大幅优化MySQL查询性能的奇技淫巧

    回顾 MySQL / InnoDB 的改善历史.你能很容易发现.在MySQL 5.6稳定版本中从来没有在read-only 这么快的提速,它很容易搞懂,以及在read-only(RO)有着良好的扩张性.也很期待它在read+write(RW)上达到一个较高水平.(特别是在读取数据是数据库主要工作的时候) 然而.我们对于RO在 MySQL 5.6的表现也十分的高兴,在5.7这个版本中,主要工作集中在 read+write (RW)上, 因为在大数据的处理上还没能达到我们的期望.但是RW依赖RO下.

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

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

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

  • MySQL数据库查询性能优化策略

    优化查询 使用Explain语句分析查询语句 Explain 用来分析 SELECT 查询语句,开发人员可以通过分析 Explain 结果来优化查询语句. 通过对查询语句的分析,可以了解查询语句的执行情况,找出查询语句执行的瓶颈,从而优化查询语句. 使用索引查询 MySql中提高性能的一个最有效的方式就是对数据表设计合理的索引. 索引提供了高效访问数据的方法,并且加快查询速度. 如果查询时没有使用索引,那么查询语句将扫描表中所有的记录.在数据量大的时候,这样查询速度会很慢. 使用索引进行查询,查

  • mysql查询时offset过大影响性能的原因和优化详解

    mysql查询时offset过大影响性能的原因和优化详解

    前言 mysql查询使用select命令,配合limit,offset参数可以读取指定范围的记录.本文将介绍mysql查询时,offset过大影响性能的原因及优化方法. 准备测试数据表及数据 1.创建表 CREATE TABLE `member` ( `id` int(10) unsigned NOT NULL AUTO_INCREMENT, `name` varchar(10) NOT NULL COMMENT '姓名', `gender` tinyint(3) unsigned NOT NU

  • mysql千万级数据分页查询性能优化

    mysql数据量大时使用limit分页,随着页码的增大,查询效率越低下. 实验 1.直接使用用limit start, count分页语句: select * from order limit start, count 当起始页较小时,查询没有性能问题,我们分别看下从10, 100, 1000, 10000开始分页的执行时间(每页取20条), 如下: select * from order limit 10, 20 0.016秒 select * from order limit 100, 20

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

    MySQL查询性能优化索引下推

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

  • MySQL查询性能优化七种方式索引潜水

    MySQL查询性能优化七种方式索引潜水

    目录 前言: 有读者可能会一脸懵? 啥是索引潜水? 你给起的名字的吗?有没有索引蛙泳? 这个名字还真不是我起的,今天要讲的知识点就叫索引潜水(Index dive) . 先要从一件怪事说起: 我先造点数据复现一下问题,创建一张用户表: CREATE TABLE `user` (  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT COMMENT '主键ID',  `name` varchar(100) NOT NULL DEFAULT '' COM

  • MySQL查询性能优化武器之链路追踪

    MySQL查询性能优化武器之链路追踪

    目录 前言 1. 查看optimizer trace配置 2. 开启optimizer trace 3. 线上问题复现 3. 使用optimizer trace 前言 MySQL优化器可以生成Explain执行计划,我们可以通过执行计划查看是否使用了索引,使用了哪种索引? 但是到底为什么会使用这个索引,我们却无从得知. 好在MySQL提供了一个好用的分析工具 — optimizer trace(优化器追踪),可以帮助我们查看优化器生成执行计划的整个过程,以及做出的各种决策,包括访问表的方法.各种

  • MySQL数据库查询性能优化的4个技巧干货

    MySQL数据库查询性能优化的4个技巧干货

    目录 前言 SQL的执行频率 慢查询日志 show profiles详情分析 explain执行计划 1.ID参数 2.select_type参数 3.type参数 前言 MySQL性能优化是一个老生常谈的问题,无论是在实际工作中还是面试中,都不可避免遇到相应的场景,下面博主就总结一些能够帮助大家解决这个问题的小技巧. SQL优化之前需要确认哪些SQL需要优化,这时就需要引起SQL性能分析工具,主要优化的是查询语句. SQL的执行频率 SQL性能优化一般是针对查询语句,所以在定位是否需要优化之前

  • Sql Server 查询性能优化之走出索引的误区分析

    Sql Server 查询性能优化之走出索引的误区分析

    据了解绝大多数开发人员对于索引的理解都是一知半解,局限于大多数日常工作没有机会.也什么没有必要去关心.了解索引,实在哪天某个查询太慢了找到查询条件建个索引就ok,哪天又有个查询慢了,再建立个索引就是,或者干脆把整个查询SQL直接发给DBA,让DBA直接帮忙优化了,所以造成的状况就是开发人员对于索引的理解.认识很局限,以下就把我个人对于索引的理解及浅薄认识和大家分享下,希望能解除一些大家的疑惑,一起走出索引的误区 误区1.在表上建立了索引,在查询时用到了索引的列,索引就一定会生效 首先明确下这样的

  • Mysql数据库性能优化之子查询

    Mysql数据库性能优化之子查询

    记得在做项目的时候, 听到过一句话, 尽量不要使用子查询, 那么这一篇就来看一下, 这句话是否是正确的. 那在这之前, 需要介绍一些概念性东西和mysql对语句的大致处理. 当Mysql Server的连接线程接收到Client发送过来的SQL请求后, 会经过一系列的分解Parse, 进行相应的分析, 然后Mysql会通过查询优化器模块, 根据该Sql所涉及到的数据表的相关统计信息进行计算分析. 然后在得出一个Mysql自认为最合理最优化的数据访问方式, 也就是我们常说的"执行计划",

  • 解析MySQL数据库性能优化的六大技巧

    数据库表表面上存在索引和防错机制,然而一个简单的查询就会耗费很长时间.Web应用程序或许在开发环境中运行良好,但在产品环境中表现同样糟糕.如果你是个数据库管理员,你很有可能已经在某个阶段遇到上述情况.因此,本文将介绍对MySQL进行性能优化的技巧和窍门. 1.存储引擎的选择如果数据表需要事务处理,应该考虑使用InnoDB,因为它完全符合ACID特性.如果不需要事务处理,使用默认存储引擎MyISAM是比较明智的.并且不要尝试同时使用这两个存储引擎.思考一下:在一个事务处理中,一些数据表使用Inno

  • Mysql数据库性能优化一

    Mysql数据库性能优化一

    今天,数据库的操作越来越成为整个应用的性能瓶颈了,这点对于Web应用尤其明显.关于数据库的性能,这并不只是DBA才需要担心的事,而这更是我们程序员需要去关注的事情.当我们去设计数据库表结构,对操作数据库时(尤其是查表时的SQL语句),我们都需要注意数据操作的性能.这里,我们不会讲过多的SQL语句的优化,而只是针对MySQL这一Web应用最多的数据库. mysql的性能优化无法一蹴而就,必须一步一步慢慢来,从各个方面进行优化,最终性能就会有大的提升. Mysql数据库的优化技术 对mysql优化是

  • Mysql数据库性能优化三（分表、增量备份、还原）

    Mysql数据库性能优化三（分表、增量备份、还原）

    接上篇Mysql数据库性能优化二 对表进行水平划分     如果一个表的记录数太多了,比如上千万条,而且需要经常检索,那么我们就有必要化整为零了.如果我拆成100个表,那么每个表只有10万条记录.当然这需要数据在逻辑上可以划分.一个好的划分依据,有利于程序的简单实现,也可以充分利用水平分表的优势.比如系统界面上只提供按月查询的功能,那么把表按月拆分成12个,每个查询只查询一个表就够了.如果非要按照地域来分,即使把表拆的再小,查询还是要联合所有表来查,还不如不拆了.所以一个好的拆分依据是 最重要的

随机推荐