前缀索引

当字段类型为字符串(varchar、text、longtext 等)时,有时候需要索引很长的字符串,这会让索引变得很大,查询时会浪费大量的磁盘 IO,影响查询效率,而且索引是有长度的(如果是单字段索引,则字段长度不能超过 767 字节,如果是联合索引,则每个字段的长度不应超过 767 字节,并且所有字段长度合计不能超过 3072 字节)假设你的字段长度超过了索引的最大长度,那么你就无法为该字段创建索引,此时可以只将字符串的一部分前缀建立索引,这样可以大大节约索引空间,从而提高索引效率

现有 employee 表,如下所示

为 employee 表的 email 字段建立长度为 6 的索引(语法如下)

create index idx_email_6 on employee (email(6));

创建成功后的索引如下

索引的 Sub_part 列显示的是 6 ,代表使用该字符串的前 6 位作为前缀索引

这里我们选取了字段的前 6 个字符作为前缀索引,前缀的长度会对索引的搜索性能产生影响吗,索引的前缀选择多大比较合适呢?

可以根据索引的选择性来决定,选择性是指不重复的索引值和表中总记录数的比值,索引选择性越高则查询效率越高,唯一索引的选择性是 1,这是最好的索引选择性,性能也是最高的

选择性计算公式(不重复的数据 / 总数)

// 查询总记录数
select count(*) from employee;
// 查询 email 前 6 位不重复的记录数
select count(*) from (select count(*) from employee group by substring(email,1,6)) deri;