MySQL索引长度和区分度

MySQL索引长度和区分度

索引长度和区分度是相互矛盾的

索引长度太短，那么区分度就很低，索引长度加长，区分度就高，但是索引也是要占内存的

需要找到一个平衡点

比如用户表有个字段username ，要给他加索引，问题是索引长度多少合适？

• 如果设置索引长度为1，占内存少，但是区分度低，区分度低索引的效率越低
• 太长则占内存
• mysql的索引都是排好序的
• 如果区分度高排序越快，区分度越低，排序慢

举个例子： 

李，李四，李四哥

• 如果索引长度取1的话，那么每一行的索引都是 李 这个字，完全没有区分度
• 结果这样三行完全是随机排的，因为索引都一样
• 如果长度取2，那么排序的时候至少前两个是排对了的
• 如果取3，区分度达到100%，排序完全正确

是不是索引越长越好？

答案肯定是错的

需要根据数据库里面的数据来判断，常规的公式是：

test是要加索引的字段，5是索引长度

select count(distinct left(test,5))/count(*) from table;   

求出一个浮点数，这个浮点数是逐渐趋向1的，例如：

• 上图当索引长度达到4的时候就已经趋向1了，所以长度设为4是最佳的
• 在大点增加的索引效果已经很小了，这个地方不是说必须接近1才行
• 其实这个值达到1就已经可以接受了

还有一些特殊的字段需要更长的索引

比如有一个url字段，绝大部分的url都是 http://www. 开头的

这种情况下索引长度取取到11都是无效的，如何解决

• 可以将数据倒序存入数据库
• 对字符串进行crc32哈希处理