乐趣区

关于java:数据库从B树讲到索引优化听不明白你找我

一、什么是索引

在进行索引之前,咱们先提前温习一下数据库的构造

数据库索引,是数据库管理系统中一个排序的数据结构,以帮助疾速查问、更新数据库表中数据。就像咱们以前用的新华字典的目录一样,能帮忙咱们疾速查问到某一个字。

具体的来看一下

二、索引的分类

分类角度索引名称数据结构 B + 树,Hash 索引,R-Tree 等存储层面聚簇索引,非聚簇索引逻辑层面主键索引,一般索引,复合索引,惟一索引,空间索引等

这样说有点不明确?图解奉上

还不明确,没关系,这么看图的确有点形象,那咱们就来看一下实例

三、索引实例剖析(以 InnoDB 为例)

3.1 InnoDB 下索引的构造

InnoDB 下,表都是依据主键程序以索引的模式寄存的,这种数据存储形式也被称为聚簇索引,“聚簇”就是示意数据行和相邻的键值紧凑的存储在一起,也就是数据行实际上是存储在索引的叶子页中。咱们创立一张表来理论阐明下 InnoDB 下的索引构造,建表语句如下:

create table person(id int primary key, age int not nullindex (age)engine=InnoDB;

而后咱们插入五条数据别离为(1,15),(2,17),(6,20),(10,18),(19,21),索引的树结构如下:

上图中展现了两局部内容,第一个图为聚簇索引(主键索引)的内容,能够看到,数据依照 Id 的大小排序,对应的索引会蕴含该索引的整行数据。

第二个图展现了用 age 做索引的索引结构图,也就是非聚簇索引(非主键索引),能够看到索引以年龄排序,然而和主键索引不同的是,年龄索引对应的却是 Id,所以咱们能够晓得非主键索引记录的内容就是主键索引的值。

这里可能有同学会有疑难,如果我建表的时候没有指定主键的话,索引构造又是如何的呢?其实在 InnoDB 中,如果没有定义主键,那么他会抉择一个惟一的非空索引代替。如果没有这样的索引,那么他会隐式的定义一个主键来作为聚簇索引。所以无论你是否设置主键,InnoDB 还是会帮你满足以上图的模式来索引数据。接下来咱们剖析下索引查问的流程。

3.2 索引查问剖析

假如咱们执行一条查问语句 select * from person where ID = 6 , 因为间接应用的是主键 ID 查问,所以就会用主键索引,因为主键索引间接关联了整行所有数据,所以,引擎只有执行一次就能查问出后果。

如果执行的 sql 语句是非主键索引

select * from person where age = 18

上述语句会走 age 的一般索引,索引先依据 age 搜寻等于 18 的索引记录,找到 ID=10 的记录,而后再到主键索引搜寻一次,而后拿出须要查问的数据。

从一般索引查出主键索引,而后查问出数据的过程叫做回表。因为回表须要多执行一次查问,这也是为什么主键索引要比一般索引要快的起因,所以,咱们要尽量应用主键查问。

3.3 笼罩索引

咱们通常创立索引的根据都是依据查问的 where 条件,然而这只是咱们通常的做法,咱们依据下面的剖析能够晓得,如果要想查问效率高,第一,应用主键索引,第二,防止回表,也就是尽可能的在索引中就能获取想要的数据。如果一个索引蕴含了须要查问的字段,那么咱们就叫做“笼罩索引”。

那么如何建设一个笼罩索引呢?答案是通过联结索引来实现,通过联结索引的字段来笼罩要查问的字段,从而达到索引笼罩的成果。

咱们把下面的建表语句革新下,来剖析下如何实现笼罩索引。

 CREATE TABLE `person` (`id` int(11) NOT NULL,  `age` int(11) DEFAULT NULL,  `name` varchar(20) DEFAULT NULL,  `sex` varchar(1) DEFAULT NULL,  PRIMARY KEY (`id`),  KEY `name_age` (`name`,`age`)) ENGINE=InnoDB DEFAULT CHARSET=latin1;

下面我创立了一个 name 和 age 的联结索引,索引结构图示意如下:

咱们依据图能够晓得,联结索引是和创立索引字段程序无关的,下面这个例子就是先以 name 排序,而后 name 雷同再以 age 为规范排序。那么咱们建表后该如何达到笼罩索引的成果呢?置信有些同学曾经晓得了怎么写 sql 能够达到笼罩索引成果,sql 如下:

select name,age from person where name = “Barry”

因为咱们须要查问的字段 name 和 age,都在索引中能够间接查问到了,所以不须要查找到主键 ID,而后再回表了。

看到这里,必定有同学会说,既然这样的话,我把所有须要查问的字段组合都建上联结索引不就行了吗?答案是:不行。因为索引也是须要耗费空间的,而且保护索引也是须要老本的,这一点我会在前面的优缺点中提到。那么有没有别的形式能够尽可能的实现不回表的成果呢?这里咱们就要引入 MySql 的 最左前缀准则 了。

什么叫最左前缀准则呢?就是在索引的匹配中,能够以索引的最左 N 个字段, 也能够是字符串索引的最左 N 个字符。比方在上图中,要查问以 A 结尾的名字,查问语句就是

select name from person where name like ‘A%’

这个时候就能够满足最左前缀规定来应用索引查问了,这里就会依赖索引查问到第一个首字母是 A 的名字,而后向后遍历,直到不满足条件为止。

那么最左 N 个字段是什么意思呢?意思就是索引(name,age), 能够间接利用 name 来当做独自索引应用,能够只应用联结索引的局部字段,然而必须是程序统一,比方索引(a,b,c),如果要想应用最左前缀规定,能够应用索引 a,ab。

咱们也能够利用该规定来少保护一个或多个索引,比方咱们须要 a,ab,abc 的查问,那就只须要 (a,b,c) 联结索引就满足要求了。

3.4 索引下推

在 MySql 5.6 版本中引入了一个新个性,叫做“索引条件推送(index condition pushdown)”,这也称为 索引下推。那么索引下推是这个什么东东呢?其实从“索引条件推送”这个名字就能够表明,这个个性是能够在索引中的字段进行条件判断,而后过滤不满足条件的记录,缩小回表的次数。

比方以上图中的数据为准,sql 如下:

select * from person where name like ‘A%’ and age =19;

那么如果没有索引下推的状况下,首先会依据索引查问出名字以 A 结尾的所有记录,而后查问出 ID,而后回表去查问对应的 ID 记录,最初再判断 age=19,返回满足条件的语句。因为满足 A 结尾的记录有 2 条,所以这种状况下,会回表 2 次。

在索引下推状况下,InnoDB 会在索引外部直接判断 age=19 是否满足条件,过滤掉不满足条件的记录,所以只返回了一条,也就是只须要回表一次。从而进步了性能。

3.5 索引的长处与毛病

说了这么多对于索引的内容,咱们来谈谈索引的优缺点。

长处:

  • 缩小服务器须要扫描的数据量
  • 索引能够帮忙服务器防止排序和长期表
  • 索引能够将随机 IO 变为程序 IO

毛病

  • 索引会占用额定的存储空间
  • 索引的保护须要肯定的老本,插入数据后须要保障原来的索引有序,所以也会影响肯定的数据库性能。
    • *

所以在优化方面,索引优化算是数据库优化中很重要的一个环节,这里因为篇幅起因我就不一一详解了,相干的内容我曾经残缺整顿成思维导图,每一个方面整顿的都很具体

须要这份优化思维导图以及下面的常识图的老铁,同步的还有相干的视频解说以及学习文档,还不快点口头
关注公众号:Java 架构师联盟,每日更新技术好文

局部材料曾经上传到我的 git 仓库中:有须要的能够下载

https://gitee.com/biwangsheng/mxq

退出移动版