前缀索引,一种优化索引大小的解决方案

本文涉及的产品
RDS MySQL Serverless 基础系列,0.5-2RCU 50GB
云数据库 RDS MySQL,高可用系列 2核4GB
RDS MySQL Serverless 高可用系列,价值2615元额度,1个月
简介:

今天在读一篇关于数据库索引介绍的文章时,该文章提到了前缀索引,对于我这个搞数据库应用开发那么多年的人来说,这个词还真是一个新词,没用过。于是打算研究一番。

前缀索引似乎是MySQL中的一个概念,在SQL Server和Oracle中没提出这个概念。于是就安装了一个MySQL来做实验,搞清楚前缀索引。

前缀索引说白了就是对文本的前几个字符(具体是几个字符在建立索引时指定)建立索引,这样建立起来的索引更小,所以查询更快。有点相当于Oracle中对字段使用Left函数,建立函数索引,只不过MySQL的这个前缀索引在查询时是内部自动完成匹配的,并不需要使用left函数。

别的文章中提到:

MySQL 前缀索引能有效减小索引文件的大小,提高索引的速度。但是前缀索引也有它的坏处:MySQL 不能在 ORDER BY 或 GROUP BY 中使用前缀索引,也不能把它们用作覆盖索引(Covering Index)。

建立前缀索引的语法为:

ALTER TABLE table_name ADD KEY(column_name(prefix_length));

这里最关键的参数就是prefix_length,这个值需要根据实际表的内容,得到合适的索引选择性(Index Selectivity)。索引选择性就是不重复的个数与总个数的比值。

select  1.0 * count( distinct column_name) / count( *)
from table_name

比如我们现在有个Employee表,其中有个FirstName字段,是varchar(50)的,我们查询该字段的索引选择性:

select  1.0 * count( distinct FirstName) / count( *)
from Employee

得到结果0.7500,然后我们希望对FirstName建立前缀索引,希望前缀索引的选择性能够尽量贴近于对整个字段建立索引时的选择性。我们先看看3个字符,如何:

select  1.0 * count( distinct  left(FirstName, 3)) / count( *)
from Employee

得到的结果是0.58784,好像差距有点大,我们再试一试4个字符呢:

select  1.0 * count( distinct  left(FirstName, 4)) / count( *)
from Employee

得到0.68919,已经提升了很多,再试一试5个字符,得到的结果是0.72297,这个结果与0.75已经很接近了,所以我们这里认为前缀长度5是一个合适的取值。所以我们可以为FirstName建立前缀索引:

alter  table test.Employee  add  key(FirstName( 5))

建立前缀索引后查询语句并不需要更改,如果我们要查询所有FirstName为Devin的Employee,那么SQL仍然写成:

select  *
from Employee e
where e.FirstName = ' Devin ';

下面总结一下什么情况下使用前缀索引:

  • 字符串列(varchar,char,text等),需要进行全字段匹配或者前匹配。也就是=‘xxx’ 或者 like ‘xxx%'
  • 字符串本身可能比较长,而且前几个字符就开始不相同。比如我们对中国人的姓名使用前缀索引就没啥意义,因为中国人名字都很短,另外对收件地址使用前缀索引也不是很实用,因为一方面收件地址一般都是以XX省开头,也就是说前几个字符都是差不多的,而且收件地址进行检索一般都是like ’%xxx%’,不会用到前匹配。相反对外国人的姓名可以使用前缀索引,因为其字符较长,而且前几个字符的选择性比较高。同样电子邮件也是一个可以使用前缀索引的字段。
  • 前一半字符的索引选择性就已经接近于全字段的索引选择性。如果整个字段的长度为20,索引选择性为0.9,而我们对前10个字符建立前缀索引其选择性也只有0.5,那么我们需要继续加大前缀字符的长度,但是这个时候前缀索引的优势已经不明显,没有太大的建前缀索引的必要了。
相关实践学习
如何快速连接云数据库RDS MySQL
本场景介绍如何通过阿里云数据管理服务DMS快速连接云数据库RDS MySQL,然后进行数据表的CRUD操作。
全面了解阿里云能为你做什么
阿里云在全球各地部署高效节能的绿色数据中心,利用清洁计算为万物互联的新世界提供源源不断的能源动力,目前开服的区域包括中国(华北、华东、华南、香港)、新加坡、美国(美东、美西)、欧洲、中东、澳大利亚、日本。目前阿里云的产品涵盖弹性计算、数据库、存储与CDN、分析与搜索、云通信、网络、管理与监控、应用服务、互联网中间件、移动服务、视频服务等。通过本课程,来了解阿里云能够为你的业务带来哪些帮助     相关的阿里云产品:云服务器ECS 云服务器 ECS(Elastic Compute Service)是一种弹性可伸缩的计算服务,助您降低 IT 成本,提升运维效率,使您更专注于核心业务创新。产品详情: https://www.aliyun.com/product/ecs
目录
相关文章
|
10月前
|
存储 自然语言处理 关系型数据库
MySQL高级篇——索引的创建与设计原则
索引的分类与使用、MySQL8.0索引新特性、适合创建索引的情况、不适合创建索引的情况
MySQL高级篇——索引的创建与设计原则
|
JavaScript
Element el-form 表单详解
本文目录 1. 前言 2. 基本用法 3. 行内表单 4. 标签对齐方式 5. 调整尺寸 6. 小结
1958 0
Element el-form 表单详解
|
9月前
|
安全 关系型数据库 MySQL
如何将数据从MySQL同步到其他系统
【10月更文挑战第17天】如何将数据从MySQL同步到其他系统
1330 0
|
缓存 druid Java
SpringBoot源码 | prepareContext方法解析
本文主要讲述SpringBoot启动流程源码中的prepareContext()方法
SpringBoot源码 | prepareContext方法解析
|
2月前
|
人工智能 自然语言处理 前端开发
Trae插件Builder模式深度测评:从编程助手到AI工程师的进化
Trae插件最新推出的Builder模式标志着AI辅助编程工具从简单的代码补全向“AI工程师”角色的转变。该模式允许开发者通过自然语言描述项目需求,自动生成完整的项目结构、代码文件和开发流程,支持VSCode、JetBrains IDE及在线Web版本。Builder模式的核心功能包括自然语言项目初始化、智能项目架构设计和多文件协调代码生成,显著提升了开发效率,降低了技术门槛。然而,它在处理复杂业务逻辑和高度定制化需求方面仍有局限。未来,Builder模式将集成云部署、测试套件生成和DevOps流水线等功能
704 2
|
Python
python国内原地址
【7月更文挑战第2天】 国内Python用户为加速包安装常使用镜像源,
3486 1
|
安全 Java 数据库连接
Java报错javax.net.ssl.SSLException MESSAGE: closing inbound before receiving peer‘s close_notify解决方法
Java报错javax.net.ssl.SSLException MESSAGE: closing inbound before receiving peer‘s close_notify解决方法
Java报错javax.net.ssl.SSLException MESSAGE: closing inbound before receiving peer‘s close_notify解决方法
|
存储 NoSQL 关系型数据库
NoSQL数据库特点
【6月更文挑战第11天】NoSQL数据库特点
278 1
|
JSON fastjson Java
(fastjson)java 如何将String(字符串)与JSON互转
(fastjson)java 如何将String(字符串)与JSON互转
757 1
|
算法 关系型数据库 MySQL
MySQL 的索引类型及使用场景
MySQL支持多种类型的索引,每种索引类型都有不同的使用场景。下面是一些常见的索引类型及其适用场景: 1. B-树索引:B-树索引是MySQL的默认索引类型。它适用于经常需要范围查询的列,例如日期范围查询、区间查询等。B-树索引也适用于针对大型表进行排序和分组查询。 2. 唯一索引:唯一索引确保索引列中的值是唯一的。它适用于要求索引列的值不重复的场景。 3. 主键索引:主键索引是唯一索引的一种特殊类型。它用于定义表的主键,并且主键值不能为NULL。主键索引可以帮助快速查找表中的唯一行。 4. 全文索引:全文索引用于全文搜索场景,支持对文本数据进行全文搜索和相关性排序。它适用于需要执行全
611 0