前缀索引
当要索引的列字符很多时， 索引长度会很大且搜索变慢。
可以只索引列开始的部分字符串 ，节约索引空间， 从而提高索引效率
对于列值较长，比如BLOB、TEXT、VARCHAR，就 “必须” 使用前缀索引，即将值的前一部分作为索引。因为索引的存储也是需要空间的，同样索引太长维护起来也比较困难。
比如我们给User表中的邮箱添加前缀索引，
# 将email的前7个字符作为索引。alter table user add index index1(email(7));
前缀索引和普通索引比较
分别将email作为索引和前7个字符作为索引来看看在性能上有什么差异
alter table user add index index1(email);
alter table user add index index2(email(7));
假设user表中有这样几条数据(id,name,email)：
(1,”陈某”,”chenmou1993@xxx”)、
(2,”张某”,”chenmou1994@xxx”)、
(3,”李某”,”chenmou1995@xxx”)、
(4,”王某”,”chenmou1996@xxx”)。
index1和index2的索引树如下两张图：
执行下面的查询语句，Mysql如何利用索引来查询呢？
select * from user where email="chenmou1995@xxx";
【1】普通索引的执行过程
1 从index1索引树找到满足索引值是chenmou1995@xxx的这条记录，取得id=2的值；
2 到主键上查到主键值是id=2的行，判断email的值是正确的，将这行记录加入结果集；
3取index1索引树上刚刚查到的位置的下一条记录，发现已经不满足email=chenmou1995@xxx的条件了，循环结束。
这个过程中，只需要回主键索引取一次数据，所以系统认为只扫描了一行。
【2】前缀索引的执行过程
1 从index2索引树找到满足索引值是chenmou的记录，找到的第一个是id=1；
2 到主键上查到主键值是id=1的行，判断出email的值不是chenmou1995@xxx，这行记录丢弃；
3 取index2上刚刚查到的位置的下一条记录，发现仍然是chenmou，取出id=2，再到ID索引上取整行然后判断，这次值对了，将这行记录加入结果集；
4 重复上一步，直到在idxe2上取到的值不是chenmou时，循环结束。在这个过程中，要回主键索引取4次数据，也就是扫描了4行。
结论：
使用前缀索引后，可能会导致查询语句读数据的次数变多。
但是对于这个查询语句来说，如果建立的前缀索引的长度为13呢？那么满足chenmou1995的记录只有一个，这样就可以直接定位到id=2，此时不但空间缩小了，扫描的行数也减少了。
如何建立最佳性能的前缀索引
使用前缀索引，只要定义好长度，就可以做到既节省空间，又不用额外增加太多的查询成本。
建立前缀索引的区分度越高越好，意味着重复的键值越少。
如何统计区分度
只需要判断数据库中重复的次数即可
这里查询邮箱前4，5，6，7重复的数量。
肯定是前缀索引越多重复的可能性越低，但是我们找到一个关键点，刚好重复的几率不高，长度也不长。
select
count( distinct left(email,4)) as L4,
count( distinct left(email,5)) as L5,
count( distinct left(email,6)) as L6,
count( distinct left(email,7)) as L7
from member;
身份证号码如何做前缀索引
对于使用前缀区分度不太好的情况，比如，我们国家的身份证号，一共18位，其中前6位是地址码，所以同一个县的人的身份证号前6位一般会是相同的。 这时候如果对身份证号做长度为6的前缀索引的话，这个索引的区分度就非常低了。
按照我们前面说的方法，可能你需要创建长度为12以上的前缀索引，才能够满足区分度要求。
但是，索引选取的越长，占用的磁盘空间就越大，相同的数据页能放下的索引值就越少，搜索的效率也就会越低。
那么，如果我们能够确定业务需求里面只有按照身份证进行等值查询的需求，还有没有别的处理方法呢？这种方法，既可以占用更小的空间，也能达到相同的查询效率。现在简单的介绍一种解决此种问题的方式，当然方法肯定不止一种，如下：
倒序存储
select field_list from t where id_card = reverse('输入的身份证号');
由于身份证号的最后6位没有地址码这样的重复逻辑，所以最后这6位很可能就提供了足够的区分度。当然了，实践中你不要忘记使用count(distinct)方法去做个验证。
前缀索引会导致覆盖索引失效
查询语句如下：select id,name from user where email=”chenmou1995@xxx”;
由于使用了前缀索引，因此必须会回表验证查询到的时候正确，此处使用了覆盖索引也是无效的。
也就是说，使用前缀索引就用不上覆盖索引对查询性能的优化了，这也是你在选择是否使用前缀索引时需要考虑的一个因素。
覆盖索引使用总结
1 如果字符串长度很短，建议直接用全部作为索引。
2 使用前缀索引注意分析区分度，区分度越高越好。
3 使用前缀索引需要考虑覆盖索引失效的问题。
若有收获，就点个赞吧
0 人点赞
