浅谈前缀索引
一.什么是前缀索引
所谓前缀索引说白了就是对字符串或前n个字符建立索引
二.为什么选择前缀索引
一般来说使用前缀索引，可能都是因为整个字段的数据量太大，没有必要针对整个字段建立索引，前缀索引仅仅是选择一个字段的前n个字符作为索引，这样一方面可以节约索引空间，另一方面则可以提高索引效率，当然很明显，这种方式也会降低索引的选择性。这里又涉及到一个概念，索引选择性
三.什么是索引选择性
它是指不重复的索引值和数据表的记录总数的比值，取值范围在 [0,1] 之间。索引的选择性越高则查询效率越高，因为选择性高的索引可以让 MySQL 在查找时过滤掉更多的行。
那是不是选择性越高的索引越好呢？也不全是，索引选择性最高为 1，唯一索引选择性肯定为1，搜索的时候就能直接通过搜索条件定位到具体一行记录，这个时候虽然性能最好，但是也是最费空间的，而前缀恰恰就是希望能够在索引的性能和空间之间找到一个平衡，我们希望能够选择足够长的前缀以保证较高的选择性，但是又希望索引不要太过于占用存储空间。
四.前缀索引的n应该怎么确定
那么我们该如何选择一个合适的索引选择性呢？索引前缀应该足够长，以便前缀索引的选择性接近于索引的整个列，即前缀的基数应该接近于完整列的基数
五.验证一下
我们来看一个实际的问题，下图的表中假设有5kw数据，除主键外，我们没有其他的索引
现在我们有一个需求：根据手机号查询用户id，那前缀索引应该怎么玩呢或者说怎么确定这个n
1.首先我们可以通过如下 SQL 得到全列选择性 (这里结果为1，即没有重复的值)
sql:SELECT COUNT(DISTINCT phone) / COUNT(*) FROM test
2.调试我们的n（索引选择性）
sql: SELECT COUNT(DISTINCT LEFT(phone, N)) / COUNT(*) FROM test;
这里的思路就是不停的追加n的值，获取到最近全列选择性（也就是1），但是N最小的值
其实我们基于表中的数据也可以看出，前三位已经可以达到唯一确定数据的作用了（现实中数据量大，需要调试）
确定好前缀索引的n接下来就可以去创建并使用了
sql: alter table test add index phone_index(phone(3));
执行一个查询sql
结合执行计划，我们来分析一下执行过程
- 从 phone_index 索引中找到第一个值为 134的记录（phone 的前3个字符）
- 由于 phone_index 是二级索引，叶子结点保存的是主键值，所以此时拿到了主键 id
- 拿着主键 id 去回表，在主键索引上找到 id 所在行的完整记录，返回给 server 层。
- server 层判断其 phone 是不是 13499213214（所以执行计划的Extra 为 Using where）
1.如果不是，这行记录丢弃。
2.如果是，将该记录加入结果集 - 索引叶子结点上数据之间是有单向链表维系的，所以接着第一步查找的结果，继续向后读取下一条记录，然后重复 2、3、4 步，直到在 phone_index 上取到的值不为 “134” 时，循环结束。
如果我们建立了前缀索引的选择性为 1，那么就不需要第 5 步了因为满足条件的值就一条，如果前缀索引选择性小于 1，就需要第五步。
从上流程中，可以合理选择前缀索引长度能够既节省空间，又提高搜索效率。
六：前缀索引的缺点
凡事都有二面行，那么前缀索引真的完美么，当然不是
我们再看一个sql
说好的索引覆盖呢？（注意看 Extra 是 Using where）
前缀索引中，B+Tree 里保存的就不是完整的 phone 字段的值，必须要回表才能拿到需要的数据。所以，用了前缀索引，就拿不到数据的完成值，必须回表，也用不了覆盖索引了
七. 总结
- 前缀索引是一种能使索引更小，更快的有效办法，但另一方面也有其缺点：mysql无法使用其前缀索引做ORDER BY和GROUP BY，也无法使用前缀索引做覆盖扫描
- 要明确使用前缀索引的目的与优势：大大节约索引空间，从而提高索引效率
- 真正的难点在于：要选择足够长的前缀以保证较高的选择性