MySQL索引下推详细
作者:程序员巴士 发布时间:2024-01-18 10:01:07
前言:
索引下推(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
会一直向右匹配直到遇到范围查询(>、<、between
、like
)就停止匹配。
2、回表
MySQL
在InnoDB
引擎下支持两种索引
聚集索引 :索引里(B+树的叶子结点上)存储的是数据行(真实的数据)
普通索引 :索引里(B+树的叶子结点上)存储的是主键
这里着重说一下聚集索引,官方文档有以下描述
在有主键的表,
InnoDB
将主键作为聚集索引没有主键的表,
InnoDB
使用第一个唯一索引作为聚集索引即没有主键也没有唯一索引时,MySQL将生成一个隐藏的6字节大小的
row ID
字段作为聚集索引
MySQL
通过普通索引没法一次性将数据拿全的情况下,通过普通索引获取主键值,再通过主键值到聚集索引中定位到记录,这个过程就叫回表。可以通过建立覆盖索引来减少回表,比如现在要通过身份证号查姓名,那就建立身份证号和姓名的联合索引(id
,name
),当查询时可以通过这个索引直接拿到姓名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'; // 开启
总结
索引下推在非主键索引上的优化,可以有效减少回表的次数,大大提升了查询的效率,在平时工作中可以根据业务情况通过优化索引来达到使用索引下推,提高业务吞吐量。
来源:https://segmentfault.com/a/1190000040780253


猜你喜欢
- 本文将通过解读render函数的源码,来分析vue中的vNode是如何创建的。在vue2.x的版本中,无论是直接书写render函数,还是使
- 完美的渐变透明效果。支持IE,Firefox渐变,自己写的JS框架中用的东西,发出来了。修正完全隐藏时,偶尔不display = "
- 废话就不多说了,直接上内容。<form action="/home/search" method="ge
- Python 如何转换string到float?简单几步,让你轻松解决。打开软件,新建python项目,如图所示右键菜单中创建.py文件,如
- 主类别表名:Navtion_TopSubject 主键fTopID Char(36) 次类别表名:Navtion_NodeSubject 外
- 这小节我们要介绍Go里面的流程控制以及函数操作。流程控制流程控制在编程语言中是最伟大的发明了,因为有了它,你可以通过很简单的流程描述来表达很
- 第一中方法:比较详细以下的文章主要介绍的是MySQL 数据库开启远程连接的时机操作流程,其实开启MySQL 数据库远程连接的实际操作步骤并不
- 前情提要:作为刚入门机器视觉的小伙伴,第一节课学到机器视觉语法时觉得很难理解,很多人家的经验,我发现都千篇一律,功能函数没解析,参数不讲解,
- 一、Tensorflow安装1、Tensorflow介绍Tensorflow是广泛使用的实现机器学习以及其它涉及大量数学运算的算法库之一。T
- 一、使用步骤 1.引入库(安装Python环境、PyQt、PyQt-tools)from PyQt5 import QtCore,
- 如果你已经理解了block formatting contexts那么请继续,否则请先看看这篇文章。Overflow能够做一些很牛掰的事情,
- tips:如果根目录下有favicon.ico,可省去<link rel="shortcut icon" ...&
- 素数(也称质数),是指除了1和该数本身,不能被任何正整数整除的正整数。判断一个正整数m是否为素数,只要判断m可否被2~根号m之中的任何一个正
- 1.游戏背景介绍(写在前面的废话): 五月初的某天,看到某网推荐了这款游戏,Pongo,看着还不错的样子
- 如何干预执行计划 - - 使用hints提示基于代价的优化器是很聪明的,在绝大多数情况下它会选择正确的优化器,减轻了DBA的负担。但有时它也
- 本文实例讲述了Python大数据之使用lxml库解析html网页文件。分享给大家供大家参考,具体如下:lxml是Python的一个html/
- <'% '************************************************
- 1. 写在前面的话在开发数据库时,特别是写存储过程,遇到比较复杂的需求,使用临时表可以简化很多逻辑。曾经在一家互联网金融公司供职,公司数据组
- matplotlib默认根据数据系列自动缩放坐标轴范围。pyplot模块中的autoscale函数可以切换是否自动缩放坐标轴范围,xlim(
- 前言最开始想尝试在windows下面安装python3.6,虽然python安装成功,但在安装Cryto模块用pip3 install py