贵金属行情
微信公众号

MySQL复习笔记-索引类型

引言

原文链接:https://yunsonbai.top/2020/11/02/mysql-indextype/
最近在复习MySQL的一些知识,结合自己的实践,记录了一些知识点,本文整理了一下索引类型,并写了一些是使用心得。

前言

索引包括:B-Tree索引、哈希索引、全文索引,本文着重记录了一下B-Tree索引和哈希索引读书和使用心得。

首先明确的一点是MySQL的索引是在存储引擎层实现的

没有特殊说明,谈论索引,多半说的是B-Tree,对于InnoDB来说每个叶子节点的逻辑页大小为16K。另外B-Tree对索引列是顺序组织存储,非常适合做范围查找。索引对多个值进行排序的依据是create table 语句中定义索引时列的顺序。

联合索引需注意:按照匹配最左原则
比如这样创建索引key(a, b, c)
使用a,b,c、ab、a时是都可以用到索引的,使用b,c、b、c、则都不行,因为没有这bc两列都不是最左数据列
不能跳过中间索引,例如a,c则只能使用部分索引,也就是只能使用a这个索引。
如果查询中某个列使用了范围查询,那该列右边的列都将无法使用索引,例如 where a=xx and b > xx and c=xx,这个c则不能使用索引,也就是说在使用范围查询时要思考一下利弊。

B-Tree索引

一般说索引的时候都是在聊这种索引,InnoDB使用的是B+Tree,是对B-Tree的升级。树的层数有限,从根节点一直延伸到叶子节点,子节点只存索引值,而叶子节点则记录指向数据本身的指针,这样就好比书的目录,查询起来,根据索引值,快速定位到要查内容的页码(真实数据地址),然后在取出内容即可,当然有些时候不需要去原表取数据,比如 “覆盖索引”的概念

关于B+Tree,网上有不少好的图例,可以看看这个 BTree和B+Tree

哈希索引

说实话我没有直接用过这种索引类型,从书上的描述来讲,只有精确匹配索引所有列的查询才有效,存储引擎会对所有列计算一个哈希码,将哈希码存储在索引中,同时在表中保存指向每个数据行的指针。

MySQL中memory引擎显式的支持这个索引类型,但笔者用的更多的是使用类似于哈希索引的方式。InnoDB引擎有个特殊的功能:自适应哈希索引。当InnoDB注意到某些索引值被使用的非常频繁时,会在内存中基于B-Tree索引之上在创建一个哈希索引,这样能实现更快速度的查询,这是个完全内部行为,使用者无法感知。

在一般使用InnoDB的方式中,我们可以对某一列(可以是对url或者其他比较长的内容)或者所有列计算一个哈希值,这里的哈希值尽量是int类型的哈希值,例如crc32,它算出来的值较小,基于它的查找会非常的迅速,但是需要程序多维护这个字段。需要注意的是crc32会有一定的冲突概率,需要使用这样的查询语句(比如对url做crc32):
select * from test where url_crc32 = 12234 and url=’xxxxx’
有的人可能会问了

  • 既然有冲突,那你这语句照样有可能使得需要过滤的条数很多,效率会高么
  • 能不能直接找个不冲突的

第一个问题,据crc32的测试报告中,约1820万条数据中会有38638个数据冲突,这里并不是说38638个完全一样的,而是这些数据存在冲突,也就是说,上边的查询语句中会过滤掉几乎全部的数据,然后剩下可怜的1条或几条数据,做一下url比对,这个效率还是相当可观的,而且你还得经常能“庆幸”的遇到冲突数据,对于绝大部分的查询语句是很难遇到冲突语句的。

第二个问题,倒是可以找到一个补充的算法,计算哈希值,但是会得不偿失,2000万数据量32位的哈希值和64位的哈希值,存储空间会差一大截,更重要的是,由于数值变大除了耗内存外,还会损失查询性能。当然非常有必要使用不冲突的哈希值,也是可以的。这就是一种权衡,根据实际需要来敲定到底用哪个。

举几个例子,
如果业务数据不会超过10000条,你甚至业务初期都不用考虑哈希数据列;
如果不会超过1000万条,那crc32将是你的不错的选择;
如果没有必要非要完全不冲突(比如爬虫系统中,判定某个url是否爬过等),个人经验3000万条以下使用crc32没有任何问题,当然还需要取决于你的表结构,如果表结构过于复杂几十个字段,这个结论还有待商榷;
如果一定要不能冲突,那只能选择更好的哈希算法了。

yunsonbai wechat
微信公众号