关于mysql:前缀索引在性能和空间中寻找平衡

@[toc]
咱们在我的项目的具体实际中，有时候会遇到一些比拟非凡的字段，例如身份证号码。

松哥之前有一个小伙伴做黑龙江省的政务服务网，里边有一些波及到用户身份证存储的场景，因为存储的数据大部分都是当地的，此时如果想给身份证号码建设索引的话，小伙伴们晓得，身份证前六位是地址码，在这样的场景下，给身份证字段建设索引的话，前六位的区分度是很低的，甚至前十位的区分度都很低（因为出世年份毕竟无限，一个省份上千万人口，出世年份反复率是很高的），不仅节约存储空间，查问性能还低。

那么有没有方法解决这个问题呢？咱们明天就来聊一聊前缀索引，聊完之后置信大家本人就有答案了。

1. 什么是前缀索引

有时候为了晋升索引的性能，咱们只对字段的前几个字符建设索引，这样做既能够节约空间，还能缩小字符串的比拟工夫，B+Tree 上须要存储的索引字符串更短，也能在肯定水平上升高索引树的高度，进步查问效率。

MySQL 中的前缀索引有点相似于 Oracle 中对字段应用 Left 函数来建设函数索引，只不过 MySQL 的这个前缀索引在查问时是外部主动实现匹配的，并不需要应用 Left 函数。

不过前缀索引有一个缺点，就是有可能会升高索引的 选择性。

2. 什么是索引选择性

对于索引的选择性（Index Selectivity），它是指不反复的索引值（也称为基数 cardinality)和数据表的记录总数的比值，取值范畴在 (0,1] 之间。索引的选择性越高则查问效率越高，因为选择性高的索引能够让 MySQL 在查找时过滤掉更多的行。

那有小伙伴要问了，是不是选择性越高的索引越好呢？当然不是！索引选择性最高为 1，如果索引选择性为 1，就是惟一索引了，搜寻的时候就能间接通过搜寻条件定位到具体一行记录！这个时候尽管性能最好，然而也是最费空间的，这不合乎咱们创立前缀索引的初衷。

咱们一开始之所以要创立前缀索引而不是惟一索引，就是心愿可能在索引的性能和空间之间找到一个均衡，咱们心愿可能抉择足够长的前缀以保障较高的选择性（这样在查问的过程中就不须要扫描很多行），然而又心愿索引不要太过于占用存储空间。

那么咱们该如何抉择一个适合的索引选择性呢？索引前缀应该足够长，以便前缀索引的选择性靠近于索引的整个列，即前缀的基数应该靠近于残缺列的基数。

首先咱们能够通过如下 SQL 失去全列选择性：

SELECT COUNT(DISTINCT column_name) / COUNT(*) FROM table_name;

而后再通过如下 SQL 失去某一长度前缀的选择性：

SELECT COUNT(DISTINCT LEFT(column_name, prefix_length)) / COUNT(*) FROM table_name;

在下面这条 SQL 执行的时候，咱们要留神抉择适合的 prefix_length，直至计算结果约等于全列选择性的时候，就是最佳后果了。

3. 创立前缀索引

3.1 一个小案例

举个例子，咱们来创立一个前缀索引看看。

松哥这里应用的数据样例是网上找的一个测试脚本，有 300W+ 条数据，做 SQL 测试优化是够用了，小伙伴们在公众号后盾回复 mysql-data-samples 获取脚本下载链接。

咱们来大抵上看下这个表构造：

这个表有一个 user_uuid 字段，咱们就在这个字段上做文章。

Git 小伙伴们应该都会用吧？不同于 Svn，Git 上的版本号不是数字而是一个 Hash 字符串，然而咱们在具体利用的时候，比方你要做版本回退，此时并不需要输出残缺的的版本号，只须要输出版本号前几个字符就行了，因为依据后面这一部分就能确定出版本号了。

那么这张表里边的 user_uuid 字段也是这意思，如果咱们想给 user_uuid 字段建设索引，就没有必要给残缺的字符串建设索引，咱们只须要给一部分字符串建设索引。

可能有小伙伴还是不太明确，我举一个例子，比如说我当初想依照 user_uuid 字段来查问，然而查问条件我没有必要写残缺的 user_uuid，我只须要写后面一部分就能够辨别出我想要的记录了，咱们来看如下一条 SQL：

大家看到，user_uuid 我只须要给出一部分就能惟一锁定一条记录。

当然，下面这个 SQL 是松哥测试过的，给定的 '39352f%' 条件不能再短了，再短就会查出来两条甚至多条记录。

通过下面这个例子咱们就可以看进去，如果给 user_uuid 字段建设索引，可能并不需要给残缺的字符串建设索引，只须要给一部分前缀字符串建设索引。

那么给后面几个字符串建设索引呢？这个可不是拍脑门，须要科学计算，咱们持续往下看。

3.2 前缀索引

首先咱们通过如下 SQL 来看一下 user_uuid 全列索引选择性是多少：

SELECT COUNT(DISTINCT user_uuid) / COUNT(*) FROM system_user;

能够看到，后果为 1。全列选择性为 1 阐明这一列的值都是惟一不反复的。

接下来咱们先来试几个不同的 prefix_length，看看选择性如何。

松哥这里一共测试了 5 个不同的 prefix_length，大家来看看各自的选择性：

8 和 9 的选择性是一样的，因为在 uuid 字符串中，第 9 个字符串是 -，所有的 uuid 第九个字符串都一样，所以 8 个字符和 9 个字符串的区分度就一样。

当 prefix_length 为 10 的时候，选择性就曾经是 1 了，意思是，在这 300W+ 条数据中，如果我用 user_uuid 这个字段去查问的话，只须要输出前十个字符，就能惟一定位到一条具体的记录了。

那还等啥，连忙创立前缀索引呗：

alter table system_user add index user_uuid_index(user_uuid(10));

查看刚刚创立的前缀索引：

show index from system_user;

能够看到，第二行就是咱们刚刚创立的前缀索引。

接下来咱们剖析查问语句中是否用到该索引：

select * from system_user where user_uuid='39352f81-165e-4405-9715-75fcdf7f7068';

能够看到，这个前缀索引曾经用上了。

具体搜寻流程是这样：

1. 从 user_uuid_index 索引中找到第一个值为 39352f81-1 的记录（user_uuid 的前十个字符）。
2. 因为 user_uuid 是二级索引，叶子结点保留的是主键值，所以此时拿到了主键 id 为 1。
3. 拿着主键 id 去回表，在主键索引上找到 id 为 1 的行的残缺记录，返回给 server 层。

4. server 层判断其 user_uuid 是不是 39352f81-165e-4405-9715-75fcdf7f7068（所以执行打算的 Extra 为 Using where）。

   1. 如果不是，这行记录抛弃。
   2. 如果是，将该记录退出后果集。
5. 索引叶子结点上数据之间是有单向链表维系的，所以接着第一步查找的后果，持续向后读取下一条记录，而后反复 2、3、4 步，直到在 user_uuid_index 上取到的值不为 39352f81-1 时，循环完结。

如果咱们建设了前缀索引并且前缀索引的选择性为 1，那么就不须要第 5 步了，如果前缀索引选择性小于 1，就须要第五步。

从下面的案例中，小伙伴们看到，咱们既节俭了空间，又进步了搜寻效率。

3.3 一个问题

应用了前缀索引后，咱们来看一个问题，大家来看如下一条查问 SQL：

select user_uuid from system_user where user_uuid='39352f81-165e-4405-9715-75fcdf7f7068';

这次不是 select *，而是 select user_uuid，小伙伴们晓得，这里应该是要用到笼罩索引，咱们来看看执行打算：

咦，说好的索引笼罩呢？（留神看 Extra 是 Using where 不是 Using index）。

大家想想，前缀索引中，B+Tree 里保留的就不是残缺的 user_uuid 字段的值，必须要回表能力拿到须要的数据。所以，用了前缀索引，就用不了笼罩索引了。

4. 回到开始的问题

在本文一开始，松哥抛出了一个问题，如何给身份证建设索引更高效？

因为身份证前六位区分度太低，所以咱们能够思考将身份证倒序存储，倒序存储之后，为前六位或者前八位（能够自行计算选择性）建设前缀索引，这样的建设的索引选择性就会比拟高，同时对空间的占用也会比拟小。在查问的时候应用 reverse 反转身份证号码即可，像上面这样：

explain select * from user where id_card=reverse('正序的身份证号码');

5. 小结

好啦，这就是前缀索引，感兴趣的小伙伴连忙体验一把吧~