MySQL索引下推详细

目录
  • 1、最左前缀原则
  • 2、回表
  • 3、索引下推

前言:

索引下推(ICP)是针对MySQL使用索引从表中检索数据行的情况的优

  • 在没有索引下推的情况下,MySQL通过存储引擎遍历索引来定位表中的数据行并将它们返回给MySQl服务器,服务器再进行WHERE条件的判断,确认是否将数据行加入结果集。
  • 开启索引下推,且WHERE条件部分可以仅使用索引中的列来评估,这时MySQL服务器会将这部分WHERE条件下推到存储引擎,接着存储引擎使用索引条目评估推送的索引条件,仅当满足该条件时才从表中进行读取

索引下推可以减少存储引擎访问数据表的次数以及MySQL服务器访问存储引擎的次数。

是不是还有点懵呢,那就对了,毫无疑问上面这段话理解起来相当费劲,但请不要灰心,我将用最通俗易懂的语言来带你了解索引下推。

总结一下:

  • 最左前缀原则
  • 回表

1、最左前缀原则

  MySQL在建立联合索引时会遵循最左前缀原则,比如现在User表建立了联合索引(id,name,age)根据最左前缀原则只有在SQL的条件部分命中(id)、(id,name)或者(id, name, age)时才能使用到这个联合索引。

能使用该索引的情况如下:

SELECT * FROM USER WHERE id = 1

SELECT * FROM USER WHERE id = 1 and name = 'zhangsan'

SELECT * FROM USER WHERE id = 1 and name = 'zhangsan' and age = 18

不能使用该索引的情况如下:

SELECT * FROM USER WHERE name = 'zhangsan'

SELECT * FROM USER WHERE age = 18

SELECT * FROM USER WHERE name = 'zhangsan' and age = 18

对于联合索引mysql会一直向右匹配直到遇到范围查询(>、<、betweenlike)就停止匹配。

2、回表

MySQLInnoDB引擎下支持两种索引

  • 聚集索引 :索引里(B+树的叶子结点上)存储的是数据行(真实的数据)
  • 普通索引 :索引里(B+树的叶子结点上)存储的是主键

这里着重说一下聚集索引,官方文档有以下描述

  • 在有主键的表,InnoDB将主键作为聚集索引
  • 没有主键的表,InnoDB使用第一个唯一索引作为聚集索引
  • 即没有主键也没有唯一索引时,MySQL将生成一个隐藏的6字节大小的row ID字段作为聚集索引

  MySQL通过普通索引没法一次性将数据拿全的情况下,通过普通索引获取主键值,再通过主键值到聚集索引中定位到记录,这个过程就叫回表。可以通过建立覆盖索引来减少回表,比如现在要通过身份证号查姓名,那就建立身份证号和姓名的联合索引(idname),当查询时可以通过这个索引直接拿到姓名name得值,不再需要去聚集索引里查找了,这就是覆盖索引。

3、索引下推

首先创建一个用户表

CREATE TABLE `student` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(255)  DEFAULT NULL,
  `age` int  DEFAULT 0,
  `class` varchar(255)  DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `index_two` (`name`,`age`)
) ENGINE=InnoDB;

//这张表增加一个复合索引
 (`name`,`age`)

给表插入数据

INSERT INTO  `student` (`name`, `age`, `class`) VALUES  ('pengpeng', 21, '1');
INSERT INTO  `student` (`name`, `age`, `class`) VALUES  ('pengpeng', 22, '2');
INSERT INTO  `student` (`name`, `age`, `class`) VALUES  ('pengpeng', 23, '3');
INSERT INTO  `student` (`name`, `age`, `class`) VALUES  ('pengpeng', 24, '4');
INSERT INTO  `student` (`name`, `age`, `class`) VALUES  ('pengpeng', 25, '5');

查询插入的数据如下

接下来explain下面这个SQL

explain select * from student where name like 'peng%' and age = 23;

可以看到Extra字段显示为USING INDEX CONDITION,这就表明这个SQL使用了索引下推,我们分析下上面这个SQL语句:

在MySQL5.6之前,只能从name字段中找出符合条件的行然后开始回表,到聚集索引上找出数据行,再对age字段进行对比,把符合条件的数据加入到结果集中。

在MySQL5.6引入了索引下推优化,在索引的遍历过程中,对索引中包含字段先做判断,这里对age字段进行判断。直接将age字段不满足的数据行排除,从而减少回表的次数。

问答区

问题1 当复合索引列为(name,age,address)时 以下SQL能使用索引吗?

select * from student where name like 'peng%' and age = 23;

  可以,遇到like会中断后续元素的匹配,但只能使用name这个字段,mysql会一直向右匹配直到遇到范围查询(>、<、between、like)就停止匹配。范围列可以用到索引,但是范围列后面的列无法用到索引。即索引最多用于一个范围列,因此如果查询条件中有两个范围列则无法全用到索引。

问题2 索引下推只能存在联合索引里吗?

是的,非联合索引无法使用索引下推。

问题3 索引下推在哪些情况下无法使用?

下推条件遇到子查询

下推条件遇到函数

非InnoDB表和MyISAM表

问题4 索引下推如何开启和关闭?

// 索引下推默认是开启的
set optimizer_switch='index_condition_pushdown=off'; // 关闭
set optimizer_switch='index_condition_pushdown=on'; // 开启

总结

  索引下推在非主键索引上的优化,可以有效减少回表的次数,大大提升了查询的效率,在平时工作中可以根据业务情况通过优化索引来达到使用索引下推,提高业务吞吐量。

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

(0)

相关推荐

  • MySQL索引下推(ICP)的简单理解与示例

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

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

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

  • Mysql性能优化之索引下推

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

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

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

  • MySQL带你秒懂索引下推

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

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

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

  • MySQL索引下推详细

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

  • MySQL索引事务详细解析

    目录 一.索引 1.概念 2.作用 3.缺陷 4.使用场景 5.使用 1.查看索引 2.创建索引 3.删除索引 6.案例 二.事务 1.为什么使用事务 2.事务的概念 3.使用 4.特性 一.索引 1.概念 索引是一种特殊的文件,包含着对数据表里所有记录的引用指针,可以对表中的一列或者多列创建索引,并指定索引的类型,各类索引有各自的数据结构实现. (这里对于索引也只是简单了解,写了些皮毛) 更浅显易懂的来说:数据库的数据保存在硬盘,硬盘不知道具体保存在哪个位置,索引就是用来告诉硬盘数据在哪个位置

  • 一篇文章带你了解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索引结构详细解析

    目录 简介 索引结构(树) 为什么用树,而不用哈希表 BTree索引 B+Tree索引 聚簇索引与非聚簇索引 索引分类 性能分析 索引创建场景 简介 在数据之外,数据库系统还维护着满足特定查找算法的数据结构,这些数据结构以某种方式引用(指向)数据,这样就可以在这些数据结构上实现高级查找算法.这种数据结构,就是索引. 一般来说索引本身也很大,不可能全部存储在内存中,因此索引往往以索引文件的形式存储的磁盘上. 优点: 1.类似大学图书馆建书目索引,提高数据检索的效率,降低数据库的IO成本. 2.通过

随机推荐