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

本文涉及的产品
RDS MySQL Serverless 基础系列,0.5-2RCU 50GB
云数据库 RDS MySQL,集群系列 2核4GB
推荐场景:
搭建个人博客
云数据库 RDS PostgreSQL,集群系列 2核4GB
简介:

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

前缀索引似乎是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,那么我们需要继续加大前缀字符的长度,但是这个时候前缀索引的优势已经不明显,没有太大的建前缀索引的必要了。

本文转自深蓝居博客园博客,原文链接:http://www.cnblogs.com/studyzy/p/4310653.html ,如需转载请自行联系原作者

相关实践学习
如何快速连接云数据库RDS MySQL
本场景介绍如何通过阿里云数据管理服务DMS快速连接云数据库RDS MySQL,然后进行数据表的CRUD操作。
全面了解阿里云能为你做什么
阿里云在全球各地部署高效节能的绿色数据中心,利用清洁计算为万物互联的新世界提供源源不断的能源动力,目前开服的区域包括中国(华北、华东、华南、香港)、新加坡、美国(美东、美西)、欧洲、中东、澳大利亚、日本。目前阿里云的产品涵盖弹性计算、数据库、存储与CDN、分析与搜索、云通信、网络、管理与监控、应用服务、互联网中间件、移动服务、视频服务等。通过本课程,来了解阿里云能够为你的业务带来哪些帮助     相关的阿里云产品:云服务器ECS 云服务器 ECS(Elastic Compute Service)是一种弹性可伸缩的计算服务,助您降低 IT 成本,提升运维效率,使您更专注于核心业务创新。产品详情: https://www.aliyun.com/product/ecs
目录
打赏
0
0
0
0
29
分享
相关文章
MySQL高级篇——覆盖索引、前缀索引、索引下推、SQL优化、主键设计
覆盖索引、前缀索引、索引下推、SQL优化、EXISTS 和 IN 的区分、建议COUNT(*)或COUNT(1)、建议SELECT(字段)而不是SELECT(*)、LIMIT 1 对优化的影响、多使用COMMIT、主键设计、自增主键的缺点、淘宝订单号的主键设计、MySQL 8.0改造UUID为有序
MySQL数据库——索引(6)-索引使用(覆盖索引与回表查询,前缀索引,单列索引与联合索引 )、索引设计原则、索引总结
MySQL数据库——索引(6)-索引使用(覆盖索引与回表查询,前缀索引,单列索引与联合索引 )、索引设计原则、索引总结
260 1
MySQL数据库——索引(5)-索引使用(上),验证索引效率、最左前缀法则、范围查询、索引失效情况、SQL提示
MySQL数据库——索引(5)-索引使用(上),验证索引效率、最左前缀法则、范围查询、索引失效情况、SQL提示
150 0
MySQL索引简介(包含索引优化,索引失效,最左前缀简洁版)
MySQL索引简介(包含索引优化,索引失效,最左前缀简洁版)
145 0
MySQL-索引优化篇(1)_安装演示库 & [前缀索引、联合索引、覆盖索引] & explain参数
MySQL-索引优化篇(1)_安装演示库 & [前缀索引、联合索引、覆盖索引] & explain参数
116 0
MYSQL性能调优05_覆盖索引、索引下推、如何选择合适的索引、Order by与Group by优化、索引设计原则(一)
MYSQL性能调优05_覆盖索引、索引下推、如何选择合适的索引、Order by与Group by优化、索引设计原则(一)
160 0
MYSQL性能调优05_覆盖索引、索引下推、如何选择合适的索引、Order by与Group by优化、索引设计原则(一)
MYSQL性能调优05_覆盖索引、索引下推、如何选择合适的索引、Order by与Group by优化、索引设计原则(三)
MYSQL性能调优05_覆盖索引、索引下推、如何选择合适的索引、Order by与Group by优化、索引设计原则(三)
267 0
MYSQL性能调优05_覆盖索引、索引下推、如何选择合适的索引、Order by与Group by优化、索引设计原则(三)
MYSQL性能调优05_覆盖索引、索引下推、如何选择合适的索引、Order by与Group by优化、索引设计原则(二)
MYSQL性能调优05_覆盖索引、索引下推、如何选择合适的索引、Order by与Group by优化、索引设计原则(二)
175 0
MYSQL性能调优05_覆盖索引、索引下推、如何选择合适的索引、Order by与Group by优化、索引设计原则(二)

相关实验场景

更多