携手创作，共同成长！这是我参与「掘金日新计划 · 8 月更文挑战」的第29天，点击查看活动详情

B+树索引（12）之索引前缀

前言

我们知道无论是主键索引（聚簇索引）或者是二级索引都是将指定列信息完整的放到B+树的叶子节点中，并且根据指定列排序，这样查询时就可以根据列值快速定位。

如下测试表结构

CREATE TABLE person_info(
    id INT NOT NULL auto_increment,
    name VARCHAR(100) NOT NULL,
    birthday DATE NOT NULL,
    phone_number CHAR(11) NOT NULL,
    PRIMARY KEY (id),
    KEY idx_name_birthday_phone_number (name, birthday, phone_number)
);

聚簇索引（idx_name_birthday_phone_number）

前缀索引

什么是前缀索引

前缀索引是索引中特殊的一种，根据字面意思上解释它是对文本前几个字符建立索引（具体是几个可以通过语句控制），这样做的好处很明显可以让索引占用字段变小，节约索引空间，减少磁盘IO的性能损耗，数据页存储记录数量增加。

但是！这里需要注意的是前缀索引是一把双刃剑，虽然节约了索引空间，但会导致索引的选择性降低。

什么是索引的选择性

指索引列的不重复值（也就是索引列基数）/ 表数据的记录总数，得到的结果取值区间为**[0,1]**，结果值越大那么索引的选择性越高索引的效率越高，而值越小那么选择性越低索引效率越低，所以当结果值是1也就表明该索引是唯一索引或者主键索引，这种索引就能通过列值直接定位到一条记录，这种性能最好但是也最消耗空间。

而前缀索引就是在性能和空间之间进行取舍，我们希望用最短前缀字符串去区分最多的数据，这就是前缀索引需要做的事情。

前缀索引如何确定

如何选择一个合适的前缀索引呢？

前缀索引的选择性趋近整个列的选择性，也就是说前缀索引的基数和索引列的基数趋近一致。

计算整个表完整列的选择性

select count(DISTINCT column_name) / count(*) from table_name;

计算长度为prefix_length的前缀索引的选择性

-- left 它返回具有指定长度的字符串的左边部分，就是字符串截取
select count(DISTINCT left(column_name, prefix_length)) / count(*) from table_name;

前缀索引如何创建

创建SQL如下

alter table table_name add index index_name(column_name(prefix_length));

前缀索引的查询过程

以测试表person_info为例，如果为name字段创建普通的二级索引，创建SQL如下

alter table person_info add index index_name(name);

那么索引结构图如下

如果将name列创建一个前缀索引

alter table person_info add index index_name(name(2));

那么索引结构图如下

如果我们需要搜索name=Baird的记录那么查询过程应该如下

根据索引的前缀找到第一个前缀为Ba的记录项。
由于是二级索引，所以找到其主键值，也就是为158的记录ID。
根据记录ID去聚簇索引中查找得到name=Barlow，显然不符合要求，丢弃。
去二级索引数中查找第二个前缀为Ba的记录项重复上面的步骤。
直到搜索完name前缀为Ba的所有记录，返回最终结果。

我们可以从第3步可以看出，匹配成功后还是会去聚簇索引中确认是否命中，由此可以得出对前缀索引做索引覆盖无效。

其实不仅仅是索引覆盖，同样因为前缀索引并不是按照完整的列值排序，那么order by、group by同样无效。

前缀索引的使用场景

前缀索引最常用的一个场景就是邮箱的匹配，我们知道邮箱都是有固定的后缀如@xxx.com这类后缀在搜索时都是重复的，所以可以将其前缀提取出来作为索引使用。