1.查询频繁
2.区分度高(离散度)
3.长度小
4.尽可能覆盖常用的查询字段
区分度高(离散度):100万用户,性别基本为男/女各50万,区分度就很低了.
索引长度直接影响索引文件的大小,影响增删改的速度,并间接影响查询速度(占用内存多)。联合索引中,区分度大的字段放前面
对于一些较长但又需要经常查询的字段,可以截取前面部分作为索引
例子:比如成语表的成语字段长度为4-20左右。
针对该列中的值,从左往右截取部分来建索引
1:截的越短,重复度越高,区分度越小,索引效果越不好
2:截取的越长,重复度越低,区分度越高,索引效果越好,但是索引长度越大,带来的影响也越大-增删改慢,间接影响查询。
所以,需要再区分度+长度 两者取一个平衡。
方法:截取不同长度,并测试区分度,取一个合适的长度
select ( (select count(distinct left(`word`,1)) from dict)/( select count(*) from dict) )
select ( (select count(distinct left(`word`,2)) from dict)/( select count(*) from dict) )
select ( (select count(distinct left(`word`,3)) from dict)/( select count(*) from dict) )
因为innodb 的左前缀原则,xxx%有效,而%xxx则无效。
对于左前缀区分度不高的字段,该如何建立索引
比如网址,前缀都是http://www
技巧一:
将数据反过来存储,比如moc.udiab.www:ptth;
技巧二:
使用伪哈希
添加一个url_crc32字段,
使用crc32算法将网址转为整形存储,查询的时候查询该网址的crc32值。
crc32是一种哈希算法,能把字符串算为32为整数。
crc32的计算结果可能有重复,但是概率不高,可以在查询后再做相应过滤
多列索引
考虑因素,列的查询效率,区分度,同时还要结合具体业务。
网友评论