@[toc]最近几篇都是关于索引的。今天我们来看看前缀索引。1、什么是前缀索引？说白了，所谓前缀索引就是索引文本的前几个字符（具体是索引时指定多少个字符），这样创建的索引越小，查询越快。这有点类似于在Oracle中使用Left函数在字段上创建函数式索引，只不过MySQL的前缀索引在查询时会自动在内部完成匹配，不需要使用Left函数。那么为什么不索引整个字段呢？一般来说，使用前缀索引可能是因为整个字段的数据量很大，不需要为整个字段建立索引。前缀索引只选取一个字段的部分字符作为索引，一方面可以节省索引空间，另一方面可以提高索引效率。当然，很明显这种方法也会降低索引的选择性。这里涉及到另外一个概念，什么是索引选择性？2、什么是索引选择性关于索引选择性（IndexSelectivity），是指唯一索引值（也称基数）与数据表中记录总数的比值，取值范围为[0,1]之间。索引的选择性越高，查询效率就越高，因为选择性高的索引可以让MySQL在查找的时候过滤掉更多的行。那么有朋友要问了，是不是选择性越高的指标越好呢？当然不是！最高索引选择性为1，如果索引选择性为1，则为唯一索引。搜索时，可以通过搜索条件直接定位到特定的一行记录！此时虽然性能最好，但也是最耗空间的，不符合我们创建前缀索引的初衷。一开始我们之所以创建前缀索引而不是唯一索引，是希望在索引的性能和空间之间找到一个平衡点。我们希望能够选择一个足够长的前缀来保证高选择性（这样在查询过程中就不需要扫描很多行），但是希望索引不会占用太多的存储空间。那么我们如何选择合适的指标选择性呢？索引前缀应该足够长，以便前缀索引的选择性接近被索引的整个列，即前缀的基数应该接近整个列的基数。首先，我们可以通过如下SQL获取全列选择性：SELECTCOUNT(DISTINCTcolumn_name)/COUNT(*)FROMtable_name;然后通过如下SQL获取一定长度前缀的选择性：SELECTCOUNT(DISTINCTLEFT(column_name,prefix_length))/COUNT(*)FROMtable_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全列索引的选择性：SELECTCOUNT(DISTINCTuser_uuid)/COUNT(*)FROMsystem_user;可以看到，结果为1，整列的选择性为1，说明这一列的值是唯一的，不重复的。接下来，让我们尝试几个不同的prefix_lengths，看看它们的选择性如何。宋哥在这里测试了5种不同的prefix_lengths。再来看看它们的选择性：8和9的选择性是一样的，因为在uuid字符串中，第9个字符串是-，而所有uuidfirst的9个字符串都是一样的，所以8个字符和9个字符的区别字符串是一样的。当prefix_length为10时，selectivity已经是1，也就是说在这300W+条数据中，如果我使用user_uuid字段查询，只需要输入前十个字符就可以唯一定位到一条具体的记录。还等什么，赶紧创建前缀索引：altertablesystem_useraddindexuser_uuid_index(user_uuid(10));查看刚刚创建的前缀索引：showindexfromsystem_user；你可以看到第二行是我们刚刚创建的前缀索引。接下来分析查询语句中是否使用了索引：select*fromsystem_userwhereuser_uuid='39352f81-165e-4405-9715-75fcdf7f7068';可以看出已经使用了这个前缀索引。具体查找过程如下：从user_uuid_index索引中找到第一条值为39352f81-1（user_uuid的前十个字符）的记录。由于user_uuid是二级索引，叶子节点存放的是主键值，所以此时主键id为1。回到主键id的表，在主键索引上找到id为1的行的完整记录，返回给server层。server层判断其user_uuid是否为39352f81-165e-4405-9715-75fcdf7f7068（所以执行计划的Extra是Usingwhere）。如果不是，则丢弃该行记录。如果是，则将记录添加到结果集中。索引叶子节点上的数据之间存在单向链表，所以按照第一步的结果，继续向后读取下一条记录，然后重复步骤2、3、4，直到得到user_uuid_index时该值不是39352f81-1，循环结束。如果我们已经建立了一个前缀索引，并且前缀索引的选择性为1，则不需要第5步，如果前缀索引的选择性小于1，则需要第5步。从上面的案例中，小伙伴们可以看出我们不仅节省了空间，还提高了搜索效率。3.3一个问题在使用了前缀索引之后，我们来看一个问题。我们看下面的查询SQL：selectuser_uuidfromsystem_userwhereuser_uuid='39352f81-165e-4405-9715-75fcdf7f7068';这次不是select*，而是selectuser_uuid，根据宋哥之前的文章（是时候检验一下使用索引的姿势是否正确了！），大家都知道这里要用到覆盖索引，一起来看看attheexecutionplan:嘿，商定的指标覆盖毛呢？（注意Extra是Usingwhere）。大家想一想，在前缀索引中，B+Tree并没有存储user_uuid字段的完整值，必须要回表才能拿到需要的数据。因此，使用前缀索引，不使用覆盖索引。4.总结好了，这就是前缀索引，请根据你项目的实际需要来使用。今天先说这么多，剩下的以后再说吧~参考资料：https://blog.csdn.net/dhrome/...
