什么是前缀索引、什么情况下才使用前缀索引？

我相信很少业务会用到前缀索引，我也很少用到，但是MySQL既然出现了这个概念，说明还是有用的，所以还是得了解一下。

恰好面试有被问到过，就简单记录一下。

什么是前缀索引？

前缀索引也叫局部索引，比如身份证一共18位，给前 10 位添加索引，类似这种给某列部分信息添加索引的方式叫做前缀索引。

为什么要用前缀索引？

针对blob、text、很长的varchar字段，mysql不支持索引他们的全部长度。

前缀索引能有效减小索引文件的大小，让每个索引页可以保存更多的索引值，从而提高了索引查询的速度。

缺点：

无法应用于 order by 和 group by，也无法做覆盖扫描。

什么情况下适合使用前缀索引？

当字符串本身可能比较长，而且前几个字符就开始不相同，适合使用前缀索引；相反情况下不适合使用前缀索引，比如，整个字段的长度为 20，索引选择性为 0.9，而我们对前 10 个字符建立前缀索引其选择性也只有 0.5，那么我们继续加大前缀字符的长度，但是这个时候前缀索引的优势已经不明显，就没有创建前缀索引的必要了。

索引值重复性越低，查询效率也就越高。

所以：

1. 前缀索引应该足够长，使前缀索引的选择性趋紧于索引整个列的选择性，太短的话就会有大量的索引值重复。
2. 前缀索引又不能太长，因为要降低索引的大小，减少空间占用

---

下面再举个例子深入理解一下。

模拟10000条数据：

CREATE TABLE `t_test_qianzhui` (
    `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
    `name` varchar(255) NOT NULL,
    PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8mb4;


CREATE PROCEDURE `t_test_qianzhui`()
begin
  DECLARE i INT DEFAULT 1;
	WHILE i < 100000 DO
INSERT into t_test_qianzhui(`name`) VALUES (CONCAT('CN.',rand()*1,'.',rand()*100,'.',rand()*1000));
SET i = i+1;
end WHILE;
end;

CALL t_test_qianzhui();

如何确定前缀索引的长度？

答案是 完整列覆盖率。

可以使用以下SQL进行估计：

1、单条数据的完整列：

select count(distinct `name`) / count(*) from t_test_qianzhui;

计算完整列的数据：

mysql> select count(distinct `name`) / count(*) from t_test_qianzhui;
+-----------------------------------+
| count(distinct `name`) / count(*) |
+-----------------------------------+
|                            1.0000 |
+-----------------------------------+
1 row in set (0.05 sec)

2、估算

对每列名进行估算

select count(distinct left(`name`,3))/count(*) as sel3,
count(distinct left(`name`,4))/count(*) as sel4,
count(distinct left(`name`,5))/count(*) as sel5, 
count(distinct left(`name`,6))/count(*) as sel6 ,
count(distinct left(`name`,7))/count(*) as sel7 ,
count(distinct left(`name`,8))/count(*) as sel8 ,
count(distinct left(`name`,9))/count(*) as sel9 ,
count(distinct left(`name`,10))/count(*) as sel10 ,
count(distinct left(`name`,11))/count(*) as sel11 
from t_test_qianzhui;

结果：

mysql> select count(distinct left(`name`,3))/count(*) as sel3,
    -> count(distinct left(`name`,4))/count(*) as sel4,
    -> count(distinct left(`name`,5))/count(*) as sel5,
    -> count(distinct left(`name`,6))/count(*) as sel6 ,
    -> count(distinct left(`name`,7))/count(*) as sel7 ,
    -> count(distinct left(`name`,8))/count(*) as sel8 ,
    -> count(distinct left(`name`,9))/count(*) as sel9 ,
    -> count(distinct left(`name`,10))/count(*) as sel10 ,
    -> count(distinct left(`name`,11))/count(*) as sel11
    -> from t_test_qianzhui;
+--------+--------+--------+--------+--------+--------+--------+--------+--------+
| sel3   | sel4   | sel5   | sel6   | sel7   | sel8   | sel9   | sel10  | sel11  |
+--------+--------+--------+--------+--------+--------+--------+--------+--------+
| 0.0001 | 0.0007 | 0.0066 | 0.0699 | 0.1139 | 0.5537 | 0.9324 | 0.9930 | 0.9991 |
+--------+--------+--------+--------+--------+--------+--------+--------+--------+
1 row in set (0.14 sec)

可以看到 sel9 ，也就是取了9个前缀索引的时候，就 0.9324 约等于 1 （distinct name） 了。

这是个数学问题、你细品~

而再长的话，也是可以的，但是这个覆盖率已经很高了，空间和时间的换取，那就看你的业务了。

3、添加前缀索引

10w条数据，没有加查询耗时大概是0.145秒。

SELECT SQL_NO_CACHE `name` FROM t_test_qianzhui WHERE name ='CN.0.8491296161423713.1.349709836160494.520.0966741145558';

加上前缀索引：

alter table t_test_qianzhui add key (`name`(9));

查询耗时大概是 0.025秒。

假如前缀索引用的不当：

alter table t_test_qianzhui add key (`name`(5));

查询耗时大概是 0.186秒，反而比不加索引的查询更耗时了。

这和数据量、列名、硬件 有关系，想测试的可以把数据加到100w，会更明显。

综上，前缀索引还是有很大的优势的。

后缀索引

既然可以使用前缀索引，那是否可以使用后缀索引呢？

mysql不支持后缀索引的

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

例如查邮箱分类的后缀。