前缀索引是指仅利用索引字段的前一部门建立的索引。相较而言,前缀索引可以减小索引的大小,且在索引的长度选择的比较是当地额情况下,并不会太多地降低索引的选择性。合适的前缀索引应当在选择性和索引大小之间取得平衡。在创建前缀索引之前,应当对数据的分布情况进行测试。在对字段建立前缀索引之前,先计算字段的选择性,在测试不同长度的字段的前缀索引的选择性,在选择性较理想的情况下建立索引。
可以使用如下SQL语句去计算某个字段的选择性:
select count(distinct [column]) / count(*) as lenFull from [tableName]
使用如下SQL语句计算某长度的前缀索引的选择性:
select count(distinct left ([column], 4)) / count(*) as len4 ,
count(distinct left ([column], 5)) / count(*) as len5 ,
count(distinct left ([column], 6)) / count(*) as len6 ,
count(distinct left ([column], 7)) / count(*) as len7 ,
count(distinct left ([column], 8)) / count(*) as len8 from [tableName]
有表如下:
name_py | name_zh |
boxigua | 薄西瓜 |
houxigua | 厚冬瓜 |
liuyishou | 刘一手 |
liuerniu | 刘二牛 |
lidaniu | 李大牛 |
lierniu | 李二牛 |
lisanniu | 李三牛 |
wangfeng | 汪峰 |
wangliyan | 王立言 |
wanglide | 王立德 |
wangligong | 王立功 |
wangsanli | 王三立 |
zhangchu | 张楚 |
zhangliming | 张黎明 |
zhaliangyong | 查良镛 |
zhaliangyong | 查良永 |
wangliyan | 汪力言 |
字段选择性测试与定长索引选择性测试结果如下:
另外,使用聚集函数对定长前缀的结果分组结果如下:
所以,选择长度8构造前缀索引比较合适。选择性较高且不会出现特定的“黑洞”(必出长度为2的时候,li和liu这俩大姓混在一起,对li的查询索引的选择性就太差)