mysql索引下推是什么意思,mysql全文索引详解

mysql索引下推是什么意思,mysql全文索引详解,MySQL索引下推详细

本文主要介绍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索引下推的更多信息,请搜索我们以前的文章或继续浏览下面的相关文章。希望你以后能支持我们!

mysql索引下推是什么意思,mysql全文索引详解