理想的索引
1:查询频繁 2:区分度高 3:长度小 4: 尽量能覆盖常用查询字段.
(其中2和3有矛盾)
1: 索引长度直接影响索引文件的大小,影响增删改的速度,并间接影响查询速度(占用内存多).
针对列中的值,从左往右截取部分,来建索引
1: 截的越短, 重复度越高,区分度越小, 索引效果越不好
2: 截的越长, 重复度越低,区分度越高, 索引效果越好,但带来的影响也越大--增删改变慢,并间影响查询速度.
所以, 我们要在 区分度 + 长度 两者上,取得一个平衡.
惯用手法: 截取不同长度,并测试其区分度,
对于一般的系统应用: 区别度能达到0.1,索引的性能就可以接受.
2:对于左前缀不易区分的列 ,建立索引的技巧
如 url列
http://www.google.com
列的前11个字符都是一样的,不易区分, 可以用如下2个办法来解决
1: 把列内容倒过来存储,并建立索引
Moc.udiab.www//:ptth
Ti.euxiz.www//://ptth
这样左前缀区分度大,
(这种可以在语言层面倒序和顺序操作,比如php、go。当然,如果放到客户端js则更省资源,以节约mysql性能)
2: 伪hash索引效果(用crc32函数来构造伪哈希列:把字符串的列,转成整型,来降低索引的长度)
同时存 url_hash列
(这种可以在语言层面倒序和顺序操作,比如php、go。当然,如果放到客户端js则更省资源,以节约mysql性能)
例子:
传统的查询方式:
其索引长度达到50:
而crc32则是:
(crc32可以在语言层面倒序和顺序操作,比如php、go。当然,如果放到客户端js则更省资源,以节约mysql性能)
crc32的索引长度:
word最长长度是14个字(如果索引长度为14,区分度相当高。但若很多的长度只有一两个字,很多空间就浪费了。所以要在长度和区分度中做个平衡):
如果取长度1,且不相同的条数:
而总共有数据条数:
其区分度为:
截不同长度对应的区分度:
生成图表:
这个案例中,如果设置长度为4,长度和区分度可以接受
建索引指定长度: