Welcome everyone

Mysql 索引理解

mysql 曹绍军 148浏览 0评论

首先来聊一下索引的分类[按索引的数据结构分]:

分类
特性
B+tree 索引 具体的结构下文会重点讲解,此处提一下的是,同样是B-tree 索引,不同的存储引擎使用也是有所差异的,例如,MyISAM 使用前缀压缩技术使得索引更小,但InnoDB 则按照原数据格式进行存储。MyISAM索引通过数据的物理位置引用被索引的行,但InnoDB 则根据主键引用被索引的行。
哈希索引 基于hash 表实现得,只有精确匹配索引所有得列得查询才有效,对于每一行数据,存储引擎都会对所有的索引列计算一个哈希码,哈希索引将所有的哈希码存储在索引中,同时在哈希表中保存指向每个数据行的指针。也就是说先找找到哈希码,然后通过对应的指针找到数据行。哈希码是有顺序的,但是

对应的数据行不一定是顺序的。

1 哈希索引只包含哈希值和行指针,而不存储字段值,所以不能使用索引中的值来避免读取行。不过,访问内存中的行的速度很快,所以大部分情况下这一点对性能的影响并不明显。

2 哈希索引数据并不是按照索引值顺序存储的,所以也就无法用于排序。

3 哈希索引也不支持部分索引列匹配查找,因为哈希索引始终是使用索引列的全部内容来计算哈希值的。

4 哈希索引只支持等值比较查询,包括=、IN()、<>(注意和<=>是不同的操作)。也不支持任何范围查询,例如 WHERE price>100

5 访问哈希索引的数据非常快,除非有很多哈希冲突(不同的索引列值却有相同的哈希值)。当出现哈希冲突的时候,存储引擎必须遍历链表中所有的行指针,逐行进行比较,直到找到所有符合条件的行。

6 如果哈希冲突很多的话,一些索引维护操作的代价也会很高。例如,如果在某个选择性很低(哈希冲突很多)的列上建立哈希索引,那么当从表中删除一行时,存储引擎需要遍历对应哈希值的链表中的每一行,找到并删除对应行的引用,冲突越多,代价越大。

7 记住不要使用SHA1()和MD5()作为哈希函数。因为这两个函数计算出来的哈希值是非常长的字符串,会浪费大量空间,比较时也会更慢。

空间数据索引 R-tree MyISAM表支持空间索引,可以用作地理数据存储。和B-Tree索引不同,这类索引无须前缀查询。空间索引会从所有维度来索引数据。查询时,可以有效地使用任意维度来组合查询。必须使用 MySQL的GIS相关函数如 MBRCONTAINS()等来维护数据。 MySQL的GIS支持并不完善,所以大部分人都不会使用这个特

性。开源关系数据库系统中对GIS的解决方案做得比较好的是 PostgreSQL的 PostGIS。

全文索引 全文索引是一种特殊类型的索引,它查找的是文本中的关键词,而不是直接比较索引中的值。全文搜索和其他几类索引的匹配方式完全不一样。它有许多需要注意的细节,如停用词、词干和复数、布尔搜索等。全文索引更类似于搜索引擎做的事情,而不是简单的 WHERE条件匹配。

索引从结构上来讲基本也就这几种,了解这几种足够了,还有其他的像树形索引等等,了解一下就行。

 

重点说一下B+ tree 索引:

首先什么是B- tree:

每个节点中不仅包含数据的key值,还有data值。而每一个页的存储空间是有限的,如果data数据较大时将会导致每个节点(即一个页)能存储的key的数量很小,当存储的数据量很大时同样会导致B-Tree的深度较大,增大查询时的磁盘I/O次数,进而影响查询效率。

B+Tree是在B-Tree基础上的一种优化,使其更适合实现外存储索引结构:

在B+Tree中,所有数据记录节点都是按照键值大小顺序存放在同一层的叶子节点上,而非叶子节点上只存储key值信息,这样可以大大加大每个节点存储的key值数量,降低B+Tree的高度。非叶子节点只存储键值信息。 所有叶子节点之间都有一个链指针。数据记录都存放在叶子节点中。

 

可以看出,在所有叶子节点上,是组成的一种链式环结构,可以看成一个双向链表,因此可以对B+Tree进行两种查找运算:一种是对于主键的范围查找和分页查找,另一种是从根节点开始,进行随机查找。

InnoDB存储引擎中页的大小为16KB,一般表的主键类型为INT(占用4个字节)或BIGINT(占用8个字节),指针类型也一般为4或8个字节,也就是说一个页(B+Tree中的一个节点)中大概存储16KB/(8B+8B)=1K个键值(因为是估值,为方便计算,这里的K取值为〖10〗^3)。

也就是说一个深度为3的B+Tree索引可以维护10^3 * 10^3 * 10^3 = 10亿 条记录。实际情况中每个节点可能不能填充满,因此在数据库中,B+Tree的高度一般都在2~4层。mysql的InnoDB存储引擎在设计时是将根节点常驻内存的,也就是说查找某一键值的行记录时最多只需要1~3次磁盘I/O操作。此处就明白了比二叉树好在什么地方了。

 

强调一个按键值顺序存储的特点:

个人理解这个特点非常重要,我们所说的最左匹配原则,范围查找后面的字段则不能使用索引,等等一些使用索引的规范或者约束,都能通过这个特性找到答案。

首先这个顺序指的是什么循序?

假如创建一个(a,b)的联合索引,那么它的索引树是这样的

 

 

在此举例用的是多个列的索引,一个列的索引键值循序即小的在父节点左边,大的在父节点右边

a的值是有顺序的,1,1,2,2,3,3,而b的值是没有顺序的1,2,1,4,1,2,所以直接取b=XX得时候,没有办法利用索引,因为联合索引首先是按a排序的,b是无序的。【为什么要最左匹配原则,最左匹配原则一定是针对联合索引来说的,要记住这个对象】

但是,a值相等的情况下,b值又是按顺序排列的,但是这种顺序是相对的,左边得列相同得情况下靠右得一个才是顺序得,所以最左匹配原则遇上范围查询就会停止,剩下的字段都无法使用索引。例如a = 1 and b = 2 a,b字段都可以使用索引,因为在a值确定的情况下b是相

对有序的,而a>1and b=2,a字段可以匹配上索引,但b值不可以,因为a的值是一个范围,在这个范围中b是无序的。 【为什么遇上范围查找,剩下列得索引会失效】

 

 

基于以上这些,就能理解索引的这些优点了:

1.索引大大减少了服务器需要扫描的数据量。
2.索引可以帮助服务器避免排序和临时表。
3.索引可以将随机IO变为顺序IO。

 

聚簇索引和非聚簇索引:

首选明白为什么叫聚簇?后者聚簇的什么东西?

聚簇索引并不是一种单独的索引类型,而是一种数据存储方式。当表有聚簇索引时,它的数据行实际上存放在索引的叶子页中。术语“聚簇”表示数据行和相邻的键值紧凑地存储在一起。因为无法同时把数据行存放在两个不同的地方,所以一个表只能有一个聚簇索引。

键值怎么取?

聚簇索引默认是主键,如果表中没有定义主键,InnoDB 会选择一个唯一的非空索引代替。如果没有这样的索引,InnoDB 会隐式定义一个主键来作为聚簇索引。

在B+tree 的特性,加上数据行和相邻的键值紧凑地存储在一起这个特点,就形成了聚簇索引。该特性也就衍生出了一些特点:

1 可以把相关数据保存在一起。

2 数据访问更快。聚簇索引将索引和数据保存在同一个 B-Tree中,因此从聚簇索引中获取数据通常比在非聚簇索引中查找要快。

3 聚簇数据最大限度地提高了I/O密集型应用的性能,但如果数据全部都放在内存中,则访问的顺序就没那么重要了,聚簇索引也就没什么优势了。

4 插入速度严重依赖于插入顺序。更新聚簇索引列的代价很高,因为会强制 InnoDB将每个被更新的行移动到新的位置。

5 非聚簇索引访问需要两次索引查找,而不是一次。

对第五点的解释:

非聚簇索引中保存的“行指针”的实质。要记住非聚簇索引叶子节点保存的不是指向行的物理位置的指针,而是行的主键值。这意味着通过非聚簇索引查找行,存储引擎需要找到二级索引的叶子节点获得对应的主键值,然后根据这个值去聚簇索引中查找到对应的行。

 

但从这个图可能看不出聚簇索引的优势,但是:

由于行数据和叶子节点存储在一起,同一页中会有多条行数据,访问同一数据页不同行记录时,已经把页加载到了Buffer中,再次访问的时候,会在内存中完成访问,不必访问磁盘。这样主键和行数据是一起被载入内存的,找到叶子节点就可以立刻将行数据返回了,如果按照主键Id来组织数据,获得数据更快。

  1. 聚簇索引适合用在排序的场合,非聚簇索引不适合
  2. 取出一定范围数据的时候,使用用聚簇索引

回答一个问题,为什么主键通常建议使用自增id?

聚簇索引的数据的物理存放顺序与索引顺序是一致的,即:只要索引是相邻的,那么对应的数据一定也是相邻地存放在磁盘上的。

 

 

 

转载请注明:汪明鑫的个人博客 » Mysql 索引理解

喜欢 (1)

说点什么

您将是第一位评论人!

提醒
avatar
wpDiscuz