我的编程空间,编程开发者的网络收藏夹
学习永远不晚

MySQL查询性能优化索引下推

短信预约 -IT技能 免费直播动态提醒
省份

北京

  • 北京
  • 上海
  • 天津
  • 重庆
  • 河北
  • 山东
  • 辽宁
  • 黑龙江
  • 吉林
  • 甘肃
  • 青海
  • 河南
  • 江苏
  • 湖北
  • 湖南
  • 江西
  • 浙江
  • 广东
  • 云南
  • 福建
  • 海南
  • 山西
  • 四川
  • 陕西
  • 贵州
  • 安徽
  • 广西
  • 内蒙
  • 西藏
  • 新疆
  • 宁夏
  • 兵团
手机号立即预约

请填写图片验证码后获取短信验证码

看不清楚,换张图片

免费获取短信验证码

MySQL查询性能优化索引下推

前言

前面已经讲了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;

MySQL查询性能优化索引下推

执行计划中的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查询性能优化索引下推

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

MySQL查询性能优化索引下推

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

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

MySQL查询性能优化索引下推

MySQL查询性能优化索引下推

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

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

5. 索引下推应用范围

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

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

MySQL查询性能优化索引下推

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

免责声明:

① 本站未注明“稿件来源”的信息均来自网络整理。其文字、图片和音视频稿件的所属权归原作者所有。本站收集整理出于非商业性的教育和科研之目的,并不意味着本站赞同其观点或证实其内容的真实性。仅作为临时的测试数据,供内部测试之用。本站并未授权任何人以任何方式主动获取本站任何信息。

② 本站未注明“稿件来源”的临时测试数据将在测试完成后最终做删除处理。有问题或投稿请发送至: 邮箱/279061341@qq.com QQ/279061341

MySQL查询性能优化索引下推

下载Word文档到电脑,方便收藏和打印~

下载Word文档

猜你喜欢

MySQL查询性能优化索引下推

目录前言1. 索引下推的作用2. 案例实践3. 索引下推配置4. 索引下推原理剖析5. 索引下推应用范围前言前面已经讲了mysql的其他查询性能优化方式,没看过可以去了解一下:MySQL查询性能优化七种方式索引潜水MySQL查询性能优
2022-08-16

Mysql性能优化之索引下推

索引下推(index condition pushdown )简称ICP,在Mysql5.6的版本上推出,用于优化查询。 在不使用ICP的情况下,在使用非主键索引(又叫普通索引或者二级索引)进行查询时,存储引擎通过索引检索到数据,然后返回给
2022-05-19

MySQL 索引优化与查询性能提升

MySQL 索引的优化是提升数据库查询性能的重要手段。通过合理设计索引、优化查询条件以及采取适当的优化策略,可以显著提高 MySQL 数据库的查询效率和整体性能。

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

导读本文章始发于本人公众号:码猿技术专栏,原创不易,谢谢关注推荐。索引下推(index condition pushdown )简称ICP,在Mysql5.6的版本上推出,用于优化查询。在不使用ICP的情况下,在使用非主键索引(又叫普通索引或者二级索引)进行查
Mysql性能优化:什么是索引下推?
2018-12-31

MySQL高性能索引策略和查询性能优化

前缀索引和索引选择性有时候需要索引很长的字符,这会让索引变得大且慢。一个策略是模拟哈希索引。通常可以索引开始的部分字符,这样可以大大解约索引空间,提高索引效率。但这样会降低索引的选择性。索引的选择性:不重复的索引值(也成为基数)和数据表的记录总数比值。索引的选
MySQL高性能索引策略和查询性能优化
2017-05-17

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

这篇文章主要介绍了MySQL查询性能优化七种方式索引潜水,文章为荣啊主题展开详细的内容介绍,具有一定的参考价值,需要的朋友可以参考一下
2022-11-13

MySQL查询性能优化前,必须先掌握MySQL索引理论

数据库索引在平时的工作是必备的,怎么建好索引,怎么使用索引,可以提高数据的查询效率。而且在面试过程,数据库的索引也是必问的知识点。

mysql-查询性能优化

1、不要取出全部列,取出全部列,会让优化器无法完成索引覆盖扫描这类优化,还会为服务器带来额外的I/O、内存和CPU的消耗。应该严格禁止SELECT * 的写法。MySQL使用如下三种方式应用WHERE条件,从好到坏依次为:  1.1 在索引中使用WHERE条件
mysql-查询性能优化
2021-02-18

PostgreSQL索引优化:加速查询性能的利器!

要成为 PostgreSQL 的专家,你需要深入了解索引的各种类型、索引的使用场景以及其他高级索引优化技术。但是,这个指南会为你提供一个良好的起点,让你在理解和使用索引方面更加熟练。

PHP与MySQL索引的查询语句优化和索引返回的性能优化策略及其对性能的影响

数据库是现代应用开发中不可或缺的一部分,而针对数据库的查询语句优化和索引返回的性能优化是开发人员应该重点关注的问题。索引是一种用于提高数据库查询效率的重要数据结构,它通过在表中创建特定字段的索引来加速数据的查找和排序过程。本文将重点讨论PH
2023-10-21

MySQL索引原理及慢查询优化

MySQL凭借着出色的性能、低廉的成本、丰富的资源,已经成为绝大多数互联网公司的首选关系型数据库。虽然性能出色,但所谓“好马配好鞍”,如何能够更好的使用它,已经成为开发工程师的必修课,我们经常会从职位描述上看到诸如&
2022-05-31

MySQL性能优化,MySQL索引优化,order by优化,explain优化

今天我们来讲讲如何优化MySQL的性能,主要从索引方面优化。

SQL查询优化-MySQL 性能调优

  在进行库表结构设计时,我们要考虑到以后的查询要如何的使用这些表,同样,编写 SQL 语句的时候也要考虑到如何使用到目前已经存在的索引,或是如何增加新的索引才能提高查询的性能。  想要对存在性能问题的查询进行优化,需要能够找到这些查询,下面先看下如何获取有性能问题的SQL。  如何设计最优的数据库表结构,如何建立最好
SQL查询优化-MySQL 性能调优
2024-04-18

MongoDB索引优化指南:提升查询性能的关键

本文将介绍一些MongoDB索引优化的指南,帮助用户更好地利用索引来提高查询性能。

编程热搜

目录