mysql深入理解前缀索引
2020-03-02| 程成| 161| 0| MySQL

有时候需要很长的索引字符串,这样会使得索引变的很大而且很慢.通常可以索引开始的部分字符,这样可以大大节省空间提升索引效率,但这样也会降低索引的选择性.索引的选择性是指,不重复的索引值和数据表的记录总数的比值,范围从1#T到1之间.索引的选择性越高则查询效率越高,因为选择性高的索引可以让mysql在查找时过滤掉更多的行,唯一索引的选择性是1,这是最好的索引选择性,性能也是最好的. 


    一般情况下某个列前缀的选择性也是足够高的,足以满足查询性能.对于BLOB,TEXT或者很长的varchar类型的列,必须使用前缀索引,因为mysql不允许索引这些列的完整长度. 


    诀窍在于要选择足够长的前缀以保证较高的选择性,同时又不能太长.前缀长的选择性接近于索引整个列.换句话说,前缀的基数应该接近于完整列的基数. 


    为了决定前缀合适长度,需要找到最常见值的列表,然后和最常见的前缀列表进行比较. 


如下构建一张表:


image.png


有了数据集,数据分布不是真实分布,仅为演示.首先找到最常见的城市列表:



image.png


如上每个值都出现了6-7次,现在找出最频繁出现城市的前缀,先从前缀字母开始:



image.png


每个前缀出现的都比原来城市次数多,因此唯一前缀比唯一城市要少得多,然后增加前缀长度,直到这个前缀的选择性接近完整列的选着性,计算合适前缀长度的一个办法计算完整列的选择性,并使前缀的选择性趋于完整列的选择性.如下计算完整列的选择性:



1583116657683190.png


计算前缀选择性趋于或接近0.43这个值:



1583116676924921.png


查询显示当前缀长度达到5的时候,再增加长度,选择性提升幅度已经不大.


只看平均选择性是不够的,也有列外情况,需要考虑最坏情况下的选择性,平均选择性会让你认为前缀长度为3或4的索引已经足够,但是如果数据分布很不均匀就会有陷阱.


上面示例如果找到合适前缀长度,下面示例如何创建前缀索引:



1583116689861156.png


原文地址:http://blog.itpub.net/30317998/viewspace-2654308/





×
作者:程成
QQ:492245711