Btree索引和Hash索引

索引对于数据库性能的影响,非常的关键,索引的主要作用呢是告诉存储引擎,如何快速的找到我们所需要的数据,当表中的

数据比较少时,查询的频率也比较低的情况下,索引的作用呢可能还不是太明显,因为这个时候呢,表中的数据,差不多完全

可以缓存到内存中,所以就算是进行全表扫描,也不会太慢,而随着表中数据越来越多,查询频率也越来越高,内存已经不能完全

缓存数据的时候,索引的作用就显得越来越重要,在实际的工作中,发现人们总是忽略,或者是过分强调索引的作用,所以会有

两种极端的表现,一种是表中除了主键外,没有任何的索引,另一种是给表中的每一列,建立一个索引,这两种做法都是不正确的,

太多的或者太少的索引呢,都会对数据库的性能带来不好的影响,只有在正确的列上建立正确的索引的情况下,才能增强数据库的

处理能力,为了能够正确的使用索引,我们先看看MYSQL支持的索引类型有哪些,刚才我们提到了,索引的主要作用呢,其实就是告诉

存储引擎,如何快速找到我们所需要的数据,所以说呢,MYSQL的索引呢,是在存储引擎层来实现的,而不是在MYSQL的服务器层来实现的,

这就决定了不同的存储引擎,他的索引呢,可能用的方式是不同的,同时也不是所有的存储引擎,都会支持所有的索引类型,即使是同一种

类型的索引,在不同的存储引擎上,其底层的实现呢,是不相同,现在我们先来看看MYSQL支持的索引类型,以及他们的优缺点,B树索引呢,

是最常见的一种索引类型,通常我们所说的索引呢,就指的B树索引,它使用的是B+树的结构来存储数据,在B+树中,每一个叶子节点呢,

都包含指向下一个节点的指针,这样可以方便的进行叶子节点的遍历,下面我们就来看看B树节点的一些特点,首先第一个特点就是我们

已经说过了的,B树索引是以B+的结构呢,存储数据的,对于不熟悉数据结构的来说呢,可能不明白什么是B+树,那下面我们就简单的图示呢

来给大家讲解一下,B+树的存储结构是什么样子的,从这张图中我们可以看到

B+树是一种平衡的查找树,每一个叶子到根部的距离呢,是相同的,并且所有的节点呢,都是按照键值的大小来顺序

存放的,同一层叶子节点上的,并且各个叶子节点呢,使用指针来互相连接的,前面说到了,这样做的好处呢,是可以方便的

快速进行查找,这就是典型的B树索引的存储结构,但是对于不同存储引擎来说,具体的实现呢可能会有不同,比如说MYISAM

索引呢,在叶子节点上呢,通过数据的物理位置,来进行引用行的,而innodb呢,则是通过组建,来引用索引的行的,我们接下来

看一下索引的其他一些特点,从前面的图示中可以看到,B树索引呢,是可以加快存储引擎查找速度的,通常情况下,索引的大小呢,

远小于表中数据的大小,使用了B树索引存储引擎呢,存储引擎就不再需要全表扫描,来获取需要的数据了,取而代之的呢就是从,

从索引的根节点进行搜索,在索引的根节点中,存放了指向下层节点的指针,存储引擎呢,根据这些指针向下层进行查找,通过比较

节点页的值,和要查找的值呢,就可以得到,合适的指针,进入下层的子节点,而这些指针呢,实际上是定义了子节点中值的上限和

下限,所以最终存储引擎呢,要找到对应的值呢,就可以确定,所查找的数据是不存在的,最终存储引擎是通过B树索引找到符合要求的

叶子节点,叶子节点比较特别,他的指针是指向被索引的数据,而不是其他的叶子节点,前面说过了,在innodb存储引擎中呢,叶子节点

指向的是主键,而在MYISAM中呢,叶子节点指向的是物理地址,另外,由于B树索引呢,对索引是顺序存储的,所以B树索引的最后一个特点呢,

就是他很适合进行范围查找,这一点和后面的hash索引有很大的不同

每当我们建立索引的时候呢,就希望查询呢可以向我们所想象的那样来使用这个索引,但是实际情况呢,MYSQL并不像

我们所希望的那样,使用我们所建立的索引,那么这其中有很多原因,下面我们就来看看B树索引呢,在什么情况下才能

被使用到,第一种使用B树索引的查询呢,是全局匹配的查询,那么全职匹配的呢,是指和索引中的所有列,来进行匹配,

比如下面的过滤条件,如果我们在订单号,order_sn上建立了一个B树索引,这时我们就要查找,订单为9876这样的一个订单,

这样就是进行一个全局匹配的一个查询,那么第二种可以使用到B树的查询呢,就是匹配最左前缀的查询,还以前面的查询

为例,我们在order_sn列上,并没有建立索引,而是建立了一个order_sn和order_date两个列,也就是订单号和订单日期,

这两个列上的联合索引,对于上面的查询条件呢,是可以利用联合索引的,也就是说,只要联合索引的第一列,符合查询条件,

这个索引就会被用到,但是如何只是这个联合索引的第二列符合查询条件,比如说,查询所有order_date等于2016年1月1号的

订单,这样的查找条件呢,是无法利用到联合索引的,这个就叫做最左前缀的查询,那么第三种可以利用的查询呢,是匹配列

前缀的查询,那么匹配列前缀和最左前缀呢,是不同的,匹配列前缀呢,是可以匹配某一列的开头部分,比如下面这个查询条件,

查询所有以9876开头的订单号,这个查询也可以利用到前面所说的order_sn列开头的联合索引

前面提到过,由于B树索引的存储特点呢,所以B树索引的更适合的是范围查找,比如下面这个查找,就可以利用到

B树索引,我们可以查到订单号,大于某一个订单号的订单,或者某一个订单号在某一个区间的订单,都是一个范围

查找,我们很少会使用到订单号查找,只是给大家举个例子,那么另外呢,B树索引呢,还可以精确的匹配左前列,并范围

匹配另外一列这样的查找,以前面所定义的order_sn和order_date两个联合索引来说呢,就是说,索引可以精确匹配order_sn

列,和订单日期列呢,一个范围内的一种查询,最后B树索引呢还可以使用在只访问索引那种查询上,这种索引就称之为覆盖

索引,也就是查询只需要访问索引,而无需访问数据行,这种查询的效率也是非常高的,以上呢就是B树索引,可以使用的

一些场景,B树索引除了可以使用在查询过滤之外呢,还可以使用在ORDER BY程序中,因为B树索引是按顺序来存储的,所以

B树索引是按照某种方式来查找到值,同样也可以按照这种方式来对数据进行排序,要全面的了解B树索引呢,我们还要看看在

使用B树索引时呢,是有什么样的限制的,下面我们就来看看B树索引的一些限制

下面我们就来看看B树索引的一些限制,我们在平常的工作中,经常会遇到,明明在查询过滤条件上建立索引,

但是查询却无法使用到索引的这种情况,这和很多因素是有关的,比如说使用索引,所命中的数据呢,如果占用表中

大部分数据时,MYSQL的查询优化器会认为,使用全表扫描的方式呢,会性能更好,所以就不会使用索引的方式来进行

查询了,此外在使用B树索引时呢,还会有一些限制,如果是按照受限制的方式来使用索引的话,则同样不会用到相同

位置的索引,下面我们就来看看这些限制有哪些,首先使用B树索引的第一个限制是,如果我们不是按照索引的最左列

进行查找的,则无法使用到索引,还拿我们之前的订单号和订单日期所建立的联合索引为例,如果索引的顺序是先订单号,

后是订单日期,也就是说,订单号是最左列,而如果我们在查询中呢,只通过订单日期列来作为查询条件呢,就无法使用到

在订单号和订单日期上的联合索引,另外在使用B树索引时呢,也不能跳过索引中左边的列,而使用索引,这种情况呢,和上面

De情况稍微有点不同,这里说的是左边的列,而不是最左边的列,一个以三列所组成的联合索引为例,来给大家讲解一下其中

的不同之处,加入我们由订单日期,下单人的姓名,和下单人的电话,所组成的联合索引,如果我们在查找中呢,包括了订单日期,

和下单人的电话,那么对于这个查询来说呢,只能使用订单日期这一列来进行查询过滤,而无法使用到下单人电话这一列,这是

因为我们的查询条件中,跳过了下单人姓名这一列,而且通常来说,对通过使用索引来进行使用not in,和不等于过滤条件的查询呢,

也是无法使用到索引的,最后在使用B树索引时呢,请注意,如果查询中使用了索引中某个列的范围查询,则在这个索引中,这个使用

范围查询的所有列呢,都无法使用索引来进行查找了,以上就是使用B树索引的一些限制,限制我们已经了解了B树索引的存储

结构,使用范围和使用的限制,那么下面我们再来看看另外一种在MYSQL中,比较常用的索引,hash索引的一些特点

在MYSQL的一些存储引擎中,除了B树索引以外,还有支持Hash索引的存储引擎,比如我们在介绍存储引擎的时候就说过,

memory存储引擎就是支持hash和b树这两种索引的存储引擎,并且呢在默认情况下,memory存储引擎呢,我们还是hash

索引,另外innodb呢,也支持hash索引,不过innodb的hash索引呢不是我们自己来建立的,而是innodb存储引擎呢根据

B树索引使用的一种使用情况呢,来自行建立的,所以也称之为自适应哈希索引,下面我们来看看哈希索引有哪些特点,

首先哈希索引是基于哈希表来实现的,只有查询条件精确匹配,hash索引中所有的列时,才能够使用到哈希索引,也就是

说,哈希索引只能用到等值查询中,如果我们要进行范围查询,或者模糊查询的话呢,就不能使用到哈希索引了,另外呢

存储引擎对哈希索引所包含的所有的列呢,都会为每一行计算一个hash码,hash码通常都会比较小,并且不同键值的行呢,

hash码通常是不一样的,哈希索引中呢,存储的就是这个哈希码,所以就解释了哈希索引只能支持这种全局匹配的查询了,

因为只有这样哈希才能匹配,同时在哈希索引的表中呢,还保存了每一个哈希索引,所代表的数据行的指针,由于哈希索引呢,

本身只存储了,哈希码,所以哈希索引的存储结构呢,十分紧凑的,这也使得哈希索引呢找到数据的速度会非常快,但是呢,

哈希索引也存在一些限制,下面我们就来看看这些限制

下面我们就来看看这些限制,首先使用哈希索引查找数据呢,必须进行两次读取,前面我们说过了,哈希索引包括的

只是键值和哈希码,以及对应的行的指针,索引中并没有保存字段的值,所以在使用哈希索引时,必须通过哈希索引

找到对应的行,然后对行的记录进行读取,所以呢,如果使用哈希索引的话,是需要进行两次查找的,不过呢,无论是

memory存储引擎,还是innodb中频繁被访问到的行,基本上都是缓存在内存中的,而对于内存中的行呢,数据访问的

速度会非常的快,所以大部分情况下,这一部分对性能的影响不会太明显,第二点呢,是因为哈希索引呢,是按照哈希

码的顺序来进行存储的,而不是按照键值的顺序来存储的,所以就没有办法像B树索引那样,来进行查询结果的排序操作,

对于哈希索引的第三个限制,我们刚才在讲解哈希索引的存储结构时呢,已经提到过了,那就是哈希索引呢,只可以进行

全键值的匹配查找,而不支持部分键值的匹配查找,同时也不支持键值的范围查找,这也是哈希索引,存储特点所决定的,

因为我们无法把一个范围值或者具体的哈希码来匹配,所以也就不能进行这样的查询了,最后哈希索引在计算哈希码时呢,

是有可能发生哈希冲突的,所谓的哈希冲突呢,也就是不同的索引列呢,他计算的哈希码呢,是相同的,那么对于经常使用

哈希函数的人来说呢,可以知道,这种情况是无法避免的,特别是为了保证哈希索引的查询效率,哈希码通常都比较小,

就算是md5这样的哈希值呢,也是有可能存在冲突的,在哈希索引中呢,一旦产生大量的哈希冲突,就会影响哈希索引的查找,

和维护的性能,所以哈希索引不适合用在选择性很差的列上,选择性差也就是说,键值列的重复值很多,比如性别这样的列,

不管有多少行数据,只有两种可选择值,所以选择性就很差,这样的列上是不可以使用哈希索引的,再比如身份证列,大家知道,

身份证的重复概率呢,是很小的,所以在这一列上建立哈希索引就很合适,哈希索引和B树索引呢,是MYSQL中最常用的两种

索引类型,索引优化也是针对这两种索引来进行的,那么在进行具体的索引优化策略之前呢,我们还要明确两个概念,一个是

我们为什么要使用索引,也就是说,使用索引能给我们带来哪些好处,另一个是索引是不是越多性能就越好

使用索引可以让我们快速的定位到我们所需要的数据,但是这并不是索引的唯一作用,前面我们在介绍B树索引时呢,

提到过,B树索引呢,索引的键值是按顺序来存储的,所以我们还可以利用B树索引来order by,或者group by这样的操作,

我们来说,使用索引呢,可以给我们带来三个好处,首先索引呢,可以大大的减少存储引擎需要扫描的数据的数量,索引文件

的大小呢,通常会远远小于数据的大小,以innodb来说,innodb存储引擎呢,发生一次IO,那么最小的是存取单位呢,是以页

来为单位的,所以一页内可以存储的信息越多,那么他的这种读取效率也就越快,默认情况下呢,innodb的大小为16K,由于

索引的大小,通常要比一行的大小要小的多,所以一页内可以存储更多的索引的数据,因此通过索引,来进行查找的话呢,

所需要读取页的数量呢,也就会越少,这样也就减少了存储引擎,需要扫描的数据的数量,加快了数据的查找的速度,另外由于

B树索引是按键值的顺序存放的,所以我们要利用B树索引来进行排序,这样就避免了使用排序IO消耗,同时也提高了MYSQL的

处理的能力,最后由于B树索引的键值是按顺序存放的,而数据行的物理地址呢,通常是随机分布的,所以使用索引进行查找呢,

是可以把这种随机的IO呢,整为顺序的IO的,这样可以更加充分的发挥磁盘的IO性能

那么在现实的工作中呢,经常有人喜欢,给表加上read索引,而不管这个索引是否有用,他们会认为,索引可以加快

数据查找的效率,所以即使是现在用不到,那么给列上加上一些索引呢,也是没有坏处的,前不久我刚刚遇到过,

这样一种情况,他要求非常急切的在一张表上,加上一些索引,那么我就问他们为什么要加上这些索引呢,得到的

回答是,是因为这个表中,导入数据的时候呢,很慢,所以他们希望,增加一些索引,以加快数据的导入速度,由此可见

开发人员对于索引是存在很大的误解的,大家不要奇怪,现实中这样的例子并不少见,索引虽然可以加快查找的速度,

但是也会给我们的性能带来一定的损耗,只有索引帮助存储引擎快速查找到所带来的好处,要大于其所带来的消耗

时的索引才是有效地,那我们来看看索引会给我们的性能带来哪些损耗,首先索引,会增加数据库写入的成本,由于我们

在进行数据更新,插入,必须要同时呢,对相关的索引和统计信息,进行维护,所以呢,索引越多,修改数据所需要的时间呢,

就会越长,为了解决这个问题呢,innodb引入了一个插入缓存,把多次插入操作,合并成一次,所以说,我们一开始就说到的,

开发人员想增加索引,以加快数据的导入速度,是绝对错误的,想增加数据导入的最好做法呢,是把所有的索引,全部删除掉,

当然了,对于innodb而言呢,至少要保留一个自增的ID主键,否则插入性能会更差,通常来说,大多数人都知道,索引对数据库的

写操作,造成的一种性能的影响,可能很少人会知道,过多的索引同样也会影响数据库查询的性能,因为MYSQL的查询优化器呢,

会根据索引的统计信息,和查询的条件,来为查询选择合适的索引,如果对于一个查询有很多的索引,对可以使用,则会增加

MYSQL查询优化器对于查询的时间,从而也会影响数据库的查询效率,可见太多的索引呢,无论是对写操作,还是对读操作来说,

都是没有什么好处的,只有在适当的列上建立适当的索引,这个时候索引才能提高数据库的性能,下面我们就来看看建立索引的

一些策略