Mysql—前缀/后缀索引

前缀索引

1. 针对blob、text、很长的varchar字段，mysql不支持索引他们的全部长度。需建立前缀索引
2. 前缀索引应该足够长，使前缀索引的选择性趋紧于索引整个列的选择性。
3. 前缀索引又不能太长，降低索引的大小，减少空间占用。
4. Alter table tableName add key/index (column(X)).
5. 缺点：无法应用于order by 和 group by，也无法做覆盖扫描。

计算合适前缀长度方法：

先计算比对整列统计数和前缀列统计数

select count(*) as cnt ,city from cityTable group by city order by cnt limit 10。计算要建立索引列的值出现次数的Top10.

select count(*) as cnt ,left(city,3) as pref from cityTable group by pref order by cnt limit 10.计算列前3字符为前缀的值出现次数Top10。前缀数从下到大依次统计。直到统计数趋近于字段统计数。

再计算选择性

select count(distinct city)/count(*) from cityTable.计算整列的选择性。

select count(distinct left(city,3))/count(*) from cityTable.计算前缀列的选择性。从小到大递增。统计出趋近于整列选择性的前缀长度。

然后再将前缀值带回统计数中。查看值分布是否均匀。防止极端情况下的部分值过大，导致索引效率降低问题。

后缀索引

mysql不支持后缀索引

可以通过在表中添加一个新列，用于保存要被建立后缀索引的字段倒排值。然后建立前缀索引。

场景：查询邮箱后缀