开发者

一文搞懂什么是MySQL前缀索引

目录
  • 一、什么是前缀索引
  • 二、为什么要用前缀索引
  • 三、怎么创建前缀索引
  • 四、使用前缀索引需要注意的事项
  • 五、小结

一、什么是前缀索引

所谓前缀索引,说白了就是对文本的前几个字符建立索引(具体是几个字符在建立索引时去指定),比如以产品名称的前 10 位来建索引,这样建立起来的索引更小,查询效率更快!

有点类似于 oracle 中对字段使用 Left 函数来建立函数索引,只不过 mysql 的这个前缀索引在查询时是内部自动完成匹配的,并不需要使用 Left 函数。

二、为什么要用前缀索引

可能有的同学会发出疑问,为什么不对整个字段建立索引呢?

一般来说,当某个字段的数据量太大,而且查询又非常的频繁时,使用前缀索引能有效的减小索引文件的大小,让每个索引页可以保存更多的索引值,从而提高了索引查询的速度。

比如,客户店铺名称,有的名称很长,有的很短,如果完全按照全覆盖来建索引,索引的存储空间可能会非常的大,有的表如果索引创建的很多,甚至会出现索引存储的空间都比数据表的存储空间大很多,因此对于这种文本很长的字段,我们可以截取前几个字符来建索引,在一定程度上,既能满足数据的查询效率要求,又能节省索引存储空间。

但是另一方面,前缀索引也有它的缺点,MySQL 中无法使用前缀索引进行 ORDER BY 和 GROUP BY,也无法用来进行覆盖扫描,当字符串本身可能比较长,而且前几个字符完全相同,这个时候前缀索引的优势已经不明显了,就没有创建前缀索引的必要了。

因此这又回到一个概念,那就是关于索引的选择性

关于数据库表索引的选择性,我会单独开篇来讲解,大家只需要记住一点:索引http://www.devze.com的选择性越高则查询效率越高,因为选择性高的索引可以让 MySQL 在查找时过滤掉更多的行,数据查询速度更快!

当某个字段内容的前几位区分度很高的时候,这个时候采用前缀索引,可以在查询性能和空间存储方面达到一个很高的性价比

那么问题来了,怎么创建前缀索引呢?

三、怎么创建前缀索引

建立前缀索引的方式,方法很简单,通过如下方式即可创建!

ALTERTABLEtable_nameADDKEY(column_name(prefix_length));

其中prefix_length这个参数,就是前缀长度的意思,通常通过如下方式进行确认,步骤如下:

第一步,先计算某字段全列的区分度。

SELECTCOUNT(DISTINCTcolumn_name)/COUNT(*)FROMtable_name;

第二步,然后再计算前缀长度为多少时和全列的区分度最相似

SELECTCOUNT(DISTINCTLEFT(column开发者_数据库教程_name,prefix_length编程))/COUNT(*)FROMtable_name;

最后,不断地调整prefix_length的值,直到和全列计算出区分度相近,最相近的那个值,就http://www.devze.com是我们想要的值。

下面以某个测试表为例,数据体量在 100 万以上,表结构如下!

CREATETABLE`tb_test`(
`idjs`bigint(20)unsignedNOTNULLAUTO_INCREMENT,
`name`varchar(100)DEFAULTNULL,
PRIMARYKEY(`id`)
)ENGINE=InnoDBAUTO_INCREMENT=1DEFAULTCHARSET=utf8mb4;

一文搞懂什么是MySQL前缀索引

一文搞懂什么是MySQL前缀索引

测试一下正常的带name条件查询,效率如下:

select*fromtb_testwherenamelike'1805.59281427%'

一文搞懂什么是MySQL前缀索引

我们以name字段为例,创建前缀索引,找出最合适的prefix_length值

首先,我们大致计算一下name字段全列的区分度。

一文搞懂什么是MySQL前缀索引

可以看到,结果为 0.9945,也就是说全局不相同的数据率在99.45%这个比例。

下面我们一起来看看,不同的prefix_length值下,对应的数据不重复比例。

prefix_length5,区分度为0.2237

一文搞懂什么是MySQL前缀索引

prefix_length10,区分度为0.9944

一文搞懂什么是MySQL前缀索引

prefix_length11,区分度为0.9945

一文搞懂什么是MySQL前缀索引

通过对比,我们发现当prefix_length11,最接近全局区分度,因此可以为name创建一个长度为11的前android缀索引,创建索引语句如下:

altertabletb_testaddkey(name(11));

下面,我们再试试上面那个语句查询!

一文搞懂什么是MySQL前缀索引

创建前缀索引之后,查询效率倍增

四、使用前缀索引需要注意的事项

是不是所有的字段,都适合用前缀索引呢?

答案显然不是,在上文我们也说到了,当某个索引的字符串列很大时,创建的索引也就变得很大,为了减小索引体积,提高索引的扫描速度,使用索引的前部分字符串作为索引值,这样索引占用的空间就会大大减少,并且索引的选择性也不会降低很多,这时前缀索引显现的作用就会非常明显,前缀索引本质是索引查询性能和存储空间的一种平衡。

对于 BLOB 和 TEXT 列进行索引,或者非常长的 VARCHAR 列,就必须使用前缀索引,因为 MySQL 不允许索引它们的全部长度。

但是如果某个字段内容,比如前缀部分相似度很高,此时的前缀索引显现效果就不会很明显,采用覆盖索引效果会更好!

五、小结

好了,本文主要围绕前缀索引做了一次初步的知识讲解,具体数据库表索引的选择性,还需要结合业务实际需求来考虑!

以上就是一文搞懂什么是MySQL前缀索引的详细内容,更多关于MySQL前缀索引的资料请关注我们其它相关文章!

0

上一篇:

下一篇:

精彩评论

暂无评论...
验证码 换一张
取 消

最新数据库

数据库排行榜