文章详情

短信预约-IT技能 免费直播动态提醒

请输入下面的图形验证码

提交验证

短信预约提醒成功

前缀索引,中看也中用!

2024-12-02 07:17

关注

1.什么是前缀索引

所谓前缀索引说白了就是对文本的前几个字符建立索引(具体是几个字符在建立索引时指定),这样建立起来的索引更小,所以查询更快。这有点类似于 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,那么就不需要第 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)。

大家想想,前缀索引中,B+Tree 里保存的就不是完整的 user_uuid 字段的值,必须要回表才能拿到需要的数据。所以,用了前缀索引,就用不了覆盖索引了。

4.小结

好啦,这就是前缀索引,请大家结合自己项目的实际需求使用。今天就先聊这么多,剩下的我们以后再扯吧~


来源:江南一点雨内容投诉

免责声明:

① 本站未注明“稿件来源”的信息均来自网络整理。其文字、图片和音视频稿件的所属权归原作者所有。本站收集整理出于非商业性的教育和科研之目的,并不意味着本站赞同其观点或证实其内容的真实性。仅作为临时的测试数据,供内部测试之用。本站并未授权任何人以任何方式主动获取本站任何信息。

② 本站未注明“稿件来源”的临时测试数据将在测试完成后最终做删除处理。有问题或投稿请发送至: 邮箱/279061341@qq.com QQ/279061341

软考中级精品资料免费领

  • 历年真题答案解析
  • 备考技巧名师总结
  • 高频考点精准押题
  • 2024年上半年信息系统项目管理师第二批次真题及答案解析(完整版)

    难度     813人已做
    查看
  • 【考后总结】2024年5月26日信息系统项目管理师第2批次考情分析

    难度     354人已做
    查看
  • 【考后总结】2024年5月25日信息系统项目管理师第1批次考情分析

    难度     318人已做
    查看
  • 2024年上半年软考高项第一、二批次真题考点汇总(完整版)

    难度     435人已做
    查看
  • 2024年上半年系统架构设计师考试综合知识真题

    难度     224人已做
    查看

相关文章

发现更多好内容

猜你喜欢

AI推送时光机
位置:首页-资讯-后端开发
咦!没有更多了?去看看其它编程学习网 内容吧
首页课程
资料下载
问答资讯