当前位置:   article > 正文

mysql的前缀索引

mysql的前缀索引
  1. 什么是前缀索引
    1. 所谓的前缀索引,说白了就是对文本的前几个字符建立索引(具体是几个字符在建立索引时去指定),比如以产品名称的前10位来建索引,这样建立起来的索引更小,查询效率更快
    2. 有点类似oracle中对字段使用left函数来建立函数索引,只不过mysql的这个前缀索引在查询时是内部自动完成匹配的,并不需要left函数
  2. 为什么要用前缀索引
    1. 一般来说,当某个字段的数据量太大,而且查询又非常的频繁时,使用前缀索引能有效的减少索引文件的大小,让每个索引页可以保存更多的索引值,从而提高了索引查询的速度
    2. 比如,客户店铺名称,有的名称很长,有的很短,如果完全按照全覆盖来建索引,索引的存储空间可能会非常的大,有的表如果索引创建的很多,甚至会出现索引存储的空间都比数据表的存储空间大很多,因此对于这种文本很长的字段,我们可以截取前几个字符来建索引,在一定程度上,既能满足数据的查询效率要求,又能节省索引存储空间
    3. 但是另一个方面,前缀索引也有他的缺点,mysql中无法使用前缀索引进行order by 和group by,也无法用来进行覆盖扫描,当字符串本身可能比较长,而且前几个字符完全相同,这个时候前缀索引的优势已经不明显了,就没有创建前缀索引的必要了
    4. 索引的选择性越高则查询效率越高,因为选择性高的索引可以让mysql在查找时过滤掉更多的行,数据查询速度更快
    5. 当某个字段内容的前几位区分度很高的时候,这个时候采用前缀索引,可以在查询性能和空间存储方面达到一个很高的性价比
  3. 如何创建前缀索引
    1. alter table table_name add key(column_name(prefix_length))
    2. 其中prefix_length这个参数,就是前缀长度的意思,通常通过如下方式进行确认
      1. 第一步,先计算某个字段全列的区分度:
        1. select count(distinct column_name) / count(*) from table_name;
      2. 第二步:然后在计算前缀长度为多少时和全列的区分度相似
        1. select count(distinct left(column_name,prefix_length)) / count(*) from table_name;
      3. 最后,不断地调整prefix_length的值,直到和全列计算出区分度相近,最相近的那个值,就是我们想要的值
  4. 使用前缀索引需要注意的事项
    1. 当某个索引字段的字符串列很大时,创建的索引也就变得很大,为了减小索引体积,提高索引的扫描速度,使用索引的前部分字符串作为索引值,这样索引占用的空间就会大大减少,并且索引的选择性也不会降低很多,这时前缀索引显现的作用就会非常明显,前缀索引本质是索引查询性能和存储空间的一种平衡
    2. 对于blob和text列进行索引,或者非常长的varchar列,就必须使用前缀索引,因为MySQL不允许索引他们的全部长度
    3. 但是如果某个字段内容,比如前缀索引部分相似度很高,此时的前缀索引显现效果就不会很明显,采用覆盖索引效果会更好
声明:本文内容由网友自发贡献,版权归原作者所有,本站不承担相应法律责任。如您发现有侵权的内容,请联系我们。转载请注明出处:【wpsshop博客】
推荐阅读
相关标签
  

闽ICP备14008679号