《高性能MySQL》读书笔记（三）------ 前缀索引的长度测试

前缀索引是指仅利用索引字段的前一部门建立的索引。相较而言，前缀索引可以减小索引的大小，且在索引的长度选择的比较是当地额情况下，并不会太多地降低索引的选择性。合适的前缀索引应当在选择性和索引大小之间取得平衡。在创建前缀索引之前，应当对数据的分布情况进行测试。在对字段建立前缀索引之前，先计算字段的选择性，在测试不同长度的字段的前缀索引的选择性，在选择性较理想的情况下建立索引。 

可以使用如下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的查询索引的选择性就太差）