纯净、安全、绿色的下载网站

首页|软件分类|下载排行|最新软件|IT学院

当前位置:首页IT学院IT技术

MySQL索引下推 MySQL索引下推详细

程序员巴士   2021-10-09 我要评论
想了解MySQL索引下推详细的相关内容吗,程序员巴士在本文为您仔细讲解MySQL索引下推的相关知识和一些Code实例,欢迎阅读和指正,我们先划重点:MySQL索引下推,MySQL索引,下面大家一起来学习吧。

前言:

索引下推(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通过普通索引没法一次性将数据拿全的情况下,通过普通索引获取主键值,再通过主键值到聚集索引中定位到记录,这个过程就叫回表。可以通过建立覆盖索引来减少回表,比如现在要通过SFZ号查姓名,那就建立SFZ号和姓名的联合索引(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'; // 开启

总结

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


相关文章

猜您喜欢

  • vscode 无法激活python 虚拟环境 解决win10 vscode 无法激活python 虚拟环境的问题

    想了解解决win10 vscode 无法激活python 虚拟环境的问题的相关内容吗,jianwang16在本文为您仔细讲解vscode 无法激活python 虚拟环境的相关知识和一些Code实例,欢迎阅读和指正,我们先划重点:vscode,无法激活python,虚拟环境,vscode,无法激活python,虚拟环境,下面大家一起来学习吧。..
  • vue使用AES.js vue使用AES.js的步骤详解

    想了解vue使用AES.js的步骤详解的相关内容吗,辰俊分享客在本文为您仔细讲解vue使用AES.js的相关知识和一些Code实例,欢迎阅读和指正,我们先划重点:vue使用AES.js,vue,AES,下面大家一起来学习吧。..

网友评论

Copyright 2020 www.3a4a5a.com 【3A软件站】 版权所有 软件发布

声明:所有软件和文章来自软件开发商或者作者 如有异议 请与本站联系 点此查看联系方式