开发者社区> 浣熊干面包> 正文
阿里云
为了无法计算的价值
打开APP
阿里云APP内打开

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

简介:
+关注继续查看

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

前缀索引似乎是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,如需转载请自行联系原作者

版权声明:本文内容由阿里云实名注册用户自发贡献,版权归原作者所有,阿里云开发者社区不拥有其著作权,亦不承担相应法律责任。具体规则请查看《阿里云开发者社区用户服务协议》和《阿里云开发者社区知识产权保护指引》。如果您发现本社区中有涉嫌抄袭的内容,填写侵权投诉表单进行举报,一经查实,本社区将立刻删除涉嫌侵权内容。

相关文章
【解决报错】‘async with‘ outside async function
简介:【解决报错】‘async with‘ outside async function
82 0
JVM系列之:String,数组和集合类的内存占用大小
JVM系列之:String,数组和集合类的内存占用大小
86 0
指针入门及指针内存大小
指针入门及指针内存大小
29 0
RocketMQ的invokeSync call timeout异常的解决办法
在RocketMQ客户端的DefaultMQPushConsumer的start方法被执行时,时不时会报出invokeSync call timeout异常,如下:
747 0
ceph - 索引
架构与部署 openstack 与 ceph (架构) openstack 与 ceph (monitor初始化) openstack 与 ceph (osd 部署) openstack 与 ceph ( pool 管理 ) openstack 管理二十二 - cinder 连接多个存储 backend openstack 管理二十三 - nova compu
1758 0
C++中类的内存空间大小(sizeof)分析
    在C语言中存在关于结构体的存储空间大小是比较深入的话题,其中涉及计算机的基本原理、操作系统等。我认为对齐是C语言中让很多初学者都拿不准摸不透的问题,特别是在跨平台的情况下,对齐这种问题更加的复杂多变,每一种系统都有自己独特的对齐方式,在Windows中经常是以结构体重最大内置类型的存储单元的字节数作为对齐的基准,而在Linux中,所有的对齐都是以4个字节对齐。
851 0
C++中类的内存空间大小(sizeof)分析
    在C语言中存在关于结构体的存储空间大小是比较深入的话题,其中涉及计算机的基本原理、操作系统等。我认为对齐是C语言中让很多初学者都拿不准摸不透的问题,特别是在跨平台的情况下,对齐这种问题更加的复杂多变,每一种系统都有自己独特的对齐方式,在Windows中经常是以结构体重最大内置类型的存储单元的字节数作为对齐的基准,而在Linux中,所有的对齐都是以4个字节对齐。
711 0
SQL优化-索引、查询优化及分页算法方案
SQL优化-索引、查询优化及分页算法方案 (一)深入浅出理解索引结构   实际上,您可以把索引理解为一种特殊的目录。微软的SQL SERVER提供了两种索引:聚集索引(clustered index,也称聚类索引、簇集索引)和非聚集索引(nonclustered index,也称非聚类索引、非簇集索引)。
1068 0
文章
问答
文章排行榜
最热
最新
相关电子书
更多
RowKey与索引设计:技巧与案例分析
立即下载
Phoenix 全局索引原理与实践
立即下载
PostgresChina2018_赖思超_PostgreSQL10_hash索引的WAL日志修改版final
立即下载