Question

五、如何建立合适的索引

Answer 1

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

http://www.baidu.com

http://www.php.cn

http://www.w3school.com

技巧一：

将数据反过来存储，比如moc.udiab.www:ptth;

技巧二：

使用伪哈希

添加一个url_crc32字段，

使用crc32算法将网址转为整形存储，查询的时候查询该网址的crc32值。

crc32是一种哈希算法，能把字符串算为32为整数。

crc32的计算结果可能有重复，但是概率不高，可以在查询后再做相应过滤

多列索引

考虑因素，列的查询效率，区分度，同时还要结合具体业务。