前缀索引,在性能和空间中寻找平衡

本文涉及的产品
RDS MySQL Serverless 基础系列,0.5-2RCU 50GB
云数据库 RDS MySQL,高可用系列 2核4GB
RDS MySQL Serverless 高可用系列,价值2615元额度,1个月
简介: 前缀索引,在性能和空间中寻找平衡

我们在项目的具体实践中,有时候会遇到一些比较特殊的字段,例如身份证号码。

松哥之前有一个小伙伴做黑龙江省的政务服务网,里边有一些涉及到用户身份证存储的场景,由于存储的数据大部分都是当地的,此时如果想给身份证号码建立索引的话,小伙伴们知道,身份证前六位是地址码,在这样的场景下,给身份证字段建立索引的话,前六位的区分度是很低的,甚至前十位的区分度都很低(因为出生年份毕竟有限,一个省份上千万人口,出生年份重复率是很高的),不仅浪费存储空间,查询性能还低。

那么有没有办法解决这个问题呢?我们今天就来聊一聊前缀索引,聊完之后相信大家自己就有答案了。

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 获取脚本下载链接。

我们来大致上看下这个表结构:

dd728de7202603b04499e371de68eb37.png

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

Git 小伙伴们应该都会用吧?不同于 Svn,Git 上的版本号不是数字而是一个 Hash 字符串,但是我们在具体应用的时候,比如你要做版本回退,此时并不需要输入完整的的版本号,只需要输入版本号前几个字符就行了,因为根据前面这一部分就能确定出版本号了。

那么这张表里边的 user_uuid 字段也是这意思,如果我们想给 user_uuid 字段建立索引,就没有必要给完整的字符串建立索引,我们只需要给一部分字符串建立索引。

可能有小伙伴还是不太明白,我举一个例子,比如说我现在想按照 user_uuid 字段来查询,但是查询条件我没有必要写完整的 user_uuid,我只需要写前面一部分就可以区分出我想要的记录了,我们来看如下一条 SQL:

d73550217773874b3f33abb1b308c399.png

大家看到,user_uuid 我只需要给出一部分就能唯一锁定一条记录。

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

通过上面这个例子我们就可以看出来,如果给 user_uuid 字段建立索引,可能并不需要给完整的字符串建立索引,只需要给一部分前缀字符串建立索引。

那么给前面几个字符串建立索引呢?这个可不是拍脑门,需要科学计算,我们继续往下看。

3.2 前缀索引

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

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

1e8dc0b06553d89535eacde96e4b9be9.png

可以看到,结果为 1。全列选择性为 1 说明这一列的值都是唯一不重复的。

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

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

7c323bd7fb53cad48541f27aa94e6752.png

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;

b97b7258ec2df2daa6ee9368ad159315.png

可以看到,第二行就是我们刚刚创建的前缀索引。

接下来我们分析查询语句中是否用到该索引:

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

9af318cc9e109bb1390606b4a2899219.png

可以看到,这个前缀索引已经用上了。

具体搜索流程是这样:

从 user_uuid_index 索引中找到第一个值为 39352f81-1 的记录(user_uuid 的前十个字符)。

由于 user_uuid 是二级索引,叶子结点保存的是主键值,所以此时拿到了主键 id 为 1。

拿着主键 id 去回表,在主键索引上找到 id 为 1 的行的完整记录,返回给 server 层。

server 层判断其 user_uuid 是不是 39352f81-165e-4405-9715-75fcdf7f7068(所以执行计划的 Extra 为 Using where)。

如果不是,这行记录丢弃。

如果是,将该记录加入结果集。

索引叶子结点上数据之间是有单向链表维系的,所以接着第一步查找的结果,继续向后读取下一条记录,然后重复 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,小伙伴们知道,这里应该是要用到覆盖索引,我们来看看执行计划:

0199b9939962abb3c49ad9cee97cf2e2.png

咦,说好的索引覆盖呢?(注意看 Extra 是 Using where 不是 Using index)。

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

4. 回到开始的问题

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

由于身份证前六位区分度太低,所以我们可以考虑将身份证倒序存储,倒序存储之后,为前六位或者前八位(可以自行计算选择性)建立前缀索引,这样的建立的索引选择性就会比较高,同时对空间的占用也会比较小。在查询的时候使用 reverse 反转身份证号码即可,像下面这样:

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

5.小结

好啦,这就是前缀索引,感兴趣的小伙伴赶紧体验一把吧~

相关实践学习
每个IT人都想学的“Web应用上云经典架构”实战
本实验从Web应用上云这个最基本的、最普遍的需求出发,帮助IT从业者们通过“阿里云Web应用上云解决方案”,了解一个企业级Web应用上云的常见架构,了解如何构建一个高可用、可扩展的企业级应用架构。
MySQL数据库入门学习
本课程通过最流行的开源数据库MySQL带你了解数据库的世界。   相关的阿里云产品:云数据库RDS MySQL 版 阿里云关系型数据库RDS(Relational Database Service)是一种稳定可靠、可弹性伸缩的在线数据库服务,提供容灾、备份、恢复、迁移等方面的全套解决方案,彻底解决数据库运维的烦恼。 了解产品详情: https://www.aliyun.com/product/rds/mysql 
相关文章
|
机器学习/深度学习 数据采集 算法
大模型开发:什么是时间序列预测,以及如何处理此类数据?
时间序列预测分析历史数据以预测未来,涉及数据收集、预处理、模型选择(如ARIMA或DeepAR)、模型训练、评估及未来值预测。处理时序数据需注意时间依赖性,预处理和模型选择对准确性影响大。
385 3
|
Java 关系型数据库 MySQL
【最新汇总】Java面试题
整理的一些大厂公司的Java面试题,都来挑战一下,看看自己有多厉害吧
156 1
|
人工智能 自然语言处理 机器人
掌握 BERT:自然语言处理 (NLP) 从初级到高级的综合指南(2)
掌握 BERT:自然语言处理 (NLP) 从初级到高级的综合指南(2)
|
缓存 Perl
如何修改openeuler为阿里源
修改openeuler为阿里源
5578 0
|
Java Maven
启动SpringBoot 错误: 找不到或无法加载主类 com.xx.xxApplication
1、在项目名称上右击–Reload from disk 2、清理项目里面的class文件进行重新编译, 鼠标点击右侧Maven,找到项目名称,然后点击Lifecycle中的clean/compile/install
2410 1
|
传感器 网络协议 物联网
嵌入式开发中的常用实时操作系统(RTOS)介绍
选择合适的RTOS是嵌入式开发中的关键步骤,不同的RTOS具有各自的优势和适用场景。开发者需要根据项目需求综合考虑硬件平台、实时性要求、开发资源和支持等因素,选择最合适的RTOS系统。例如,如果需要开源和灵活的解决方案,可以选择Zephyr或RT-Thread;如果需要高性能和商业支持,可以选择VxWorks或ThreadX。希望本文能帮助您更好地了解各类RTOS,并为您的嵌入式开发项目提供参考。
886 1
|
Web App开发 前端开发 安全
2024年新一代WebOffice内嵌网页组件,Web网页在线编辑Word/Excel/PPT
WebOffice控件面临兼容性、用户体验和维护难题。随着浏览器更新,依赖插件的技术不再适用,如Chrome不再支持NPAPI和PPAPI。产品普遍不支持多版本Office并存,定制能力弱,升级复杂。猿大师办公助手提供了解决方案,它兼容多种浏览器,包括最新版和国产浏览器,不依赖插件,支持文档对比,具有丰富的功能和接口,兼容多种Office版本,允许源码级定制,提供终身技术支持,并实现静默在线升级。适用于多种行业和操作系统。
788 116
|
11月前
|
运维 安全 API
通义灵码知识库问答增强:知识库构建与管理指南
通义灵码能够结合企业知识库的私域数据,生成贴合企业特点的回答。充分发挥检索增强技术的优势,构建高质量的企业知识数据以及合理的知识库权限管理是必不可少的。本文将为您详细介绍如何构造与管理一个高质量的企业知识库。
通义灵码知识库问答增强:知识库构建与管理指南
|
SQL DataWorks API
DataWorks产品使用合集之如何解决查询列数太多不展示结果
DataWorks作为一站式的数据开发与治理平台,提供了从数据采集、清洗、开发、调度、服务化、质量监控到安全管理的全套解决方案,帮助企业构建高效、规范、安全的大数据处理体系。以下是对DataWorks产品使用合集的概述,涵盖数据处理的各个环节。
|
JavaScript Java 测试技术
客运自助售票小程序+ssm+vue.js附带文章和源代码设计说明文档ppt
客运自助售票小程序+ssm+vue.js附带文章和源代码设计说明文档ppt
116 1