当要索引字段长度较长,不利于放入索引时,使用前缀索引。可以为索引字段指定长度,即在该字段前n个字符上生成索引。
那么具体取多少个字符做索引?
1.计算该字段选择性 = a
2.分别计算该字段取n个字符的选择性
3.当n与a接近,或n增大对a影响较小时,记得到最优的n
实例:
表house_rent,title字段没有索引,使用选择性计算方法计算前缀个数
//查询分析
explain SELECT * FROM `house_rent` WHERE title like "宣庆街%";
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE house_rent ALL NULL NULL NULL NULL 830 Using where
//计算title字段选择性
select count(distinct title) / count(*) from house_rent;
//结果
0.7114
//计算不同前缀值下title字段选择性
//3个字前缀
select count(distinct left(title,3)) / count(*) from house_rent;
//结果
0.2698
//设定不同前缀字段的选择性比例
字段个数 | 比例 |
3 | 0.2698 |
4 | 0.3383 |
5 | 0.4271 |
6 | 0.5085 |
7 | 0.5444 |
8 | 0.5708 |
9 | 0.5825 |
//由表可见,当设定字符数为8后,再增加字符结果比例增加已呈明显下降趋势。此时虽距离0.7还有差距,但是再增加字符收效已经很小。所以此时就选择8作为前缀数
//建索引
alter table `house_rent` add key title( title(8))
//测试结果
explain SELECT * FROM `wa_fenlei_house_person_rent` WHERE title like "宣庆街%"
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE house_rent ref title title 27 const 1 Using where
//结果从830行缩短为1行