本文主要介绍MySQL索引下推。索引下推(ICP)是对MySQL使用索引从表中检索数据行的优化。下面介绍一下相关信息,有需要的朋友可以参考一下。
目录
1、最左侧前缀原则2、返回表3、索引下推
前言:
索引下推(ICP)是MySQL使用索引从表中检索数据行的一个优势。
在没有索引下推的情况下,MySQl通过存储引擎遍历索引来定位表中的数据行,并将它们返回给MySQL server,然后由MySQl server判断WHERE条件来确认数据行是否被添加到结果集中。
当开启索引下推,且WHERE条件只能由索引中的列来评估时,MySQL server会将这部分WHERE条件下推至存储引擎,然后存储引擎会使用索引条目来评估所推送的索引条件,只有满足条件时才会从表中读取。
索引下推可以减少存储引擎访问数据表和MySQL服务器访问存储引擎的次数。
你还有点困惑吗?没错。毫无疑问,上面这一段相当难懂,但请不要灰心。我将用最简单易懂的语言带你完成索引下推。
总结一下:
最左前缀原则
惠标
1、最左前缀原则
MySQL在建立联合索引时会遵循最左前缀原则。例如,现在用户表建立了一个联合索引(id、姓名、年龄)。根据最左前缀原则,只有当SQL的条件部分命中(id,name)或(id,name,age)时,才能使用这个联合索引。
能使用该索引的情况如下:
SELECT * FROM id=1的用户
SELECT * FROM用户,其中id=1,名称=张三
SELECT * FROM用户,其中id=1,姓名=张三,年龄=18岁
不能使用该索引的情况如下:
SELECT * FROM用户,其中name=张三
SELECT * FROM年龄=18岁的用户
SELECT * FROM用户名=张三,年龄=18岁
对于联合索引,Mysql将始终匹配到右侧,直到遇到范围查询(,between,like)并停止匹配。
2、回表
MySQL InnoDB引擎支持两种索引
聚集索引 :
索引(在B树的叶节点上)存储数据行(真实数据)。主键存储在
普通索引 :
索引中(在B树的叶节点上)这里我们主要关注聚集索引。官方文件有如下描述
在有主键的表中,InnoDB使用主键作为聚集索引。
对于没有主键的表,InnoDB使用第一个唯一索引作为聚集索引。
当没有主键和唯一索引时,MySQL将生成一个隐藏的6字节行ID字段作为聚集索引。
当MySQL普通索引无法一次性获取所有数据时,通过普通索引获取主键值,然后通过主键值在聚簇索引中定位记录。这个过程称为返回表。您可以通过建立覆盖索引来减少表返回的数量。例如,如果您现在想通过id号查找姓名,您应该建立ID号和姓名的联合索引(ID,name)。查询时可以通过这个索引直接得到name name的值,不再需要在聚簇索引中查找。这是叠加索引。
3、索引下推
首先创建一个用户表
创建表“学生”(
` id int(11)NOT NULL AUTO _ INCREMENT,
` name varchar(255)默认为空,
` age` int默认值为0,
` class varchar(255)默认为空,
主键(` id `),
关键字` index_two` (`name`,`age `)
)ENGINE=InnoDB
//向此表添加复合索引
(`姓名`, `年龄`)
给表插入数据
插入到“学生”(“姓名”、“年龄”、“班级”)值中(“彭彭”,21,“1”);
插入到“学生”(“姓名”、“年龄”、“班级”)值中(“彭彭”,22,“2”);
插入到“学生”(“姓名”、“年龄”、“班级”)值中(“彭彭”,23,“3”);
插入到“学生”(“姓名”、“年龄”、“班级”)值中(“彭彭”,24,“4”);
插入到“学生”(“姓名”、“年龄”、“班级”)值中(“彭彭”,25,“5”);
查询插入的数据如下
接下来explain下面这个SQL
解释select * from学生,其中姓名如“peng%”,年龄=23;
您可以看到额外的字段显示为USING INDEX CONDITION,这表明该SQL使用了索引下推。让我们分析一下上面的SQL语句:
在MySQL5.6之前,只能在name字段中找到符合条件的行,然后返回到表中。可以在聚集索引中找到数据行,然后可以比较年龄字段,将符合条件的数据添加到结果集中。
在MySQL5.6中,引入了索引下推优化。在索引的遍历过程中,首先判断索引包含的字段,这里判断年龄字段。直接排除年龄字段不满足的数据行,减少返回表的次数。
问答区
问题1
当复合索引列为(姓名,年龄,地址)时,以下SQL可以使用索引吗?select * from姓名如“peng%”且年龄=23的学生;
可以,meeting 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索引下推的更多信息,请搜索我们以前的文章或继续浏览下面的相关文章。希望你以后能支持我们!