前往小程序,Get更优阅读体验!
立即前往
首页
学习
活动
专区
工具
TVP
发布
社区首页 >专栏 >mysql前缀索引的索引选择性

mysql前缀索引的索引选择性

作者头像
全栈程序员站长
发布2022-08-31 19:32:19
6460
发布2022-08-31 19:32:19
举报

大家好,又见面了,我是你们的朋友全栈君。

mysql前缀索引的索引选择性

一. 基础概念 在mysql中建立前缀索引的意义在于相对于整列建立索引,前缀索引仅仅是选择该列的部分字符作为索引,减少索引的字符可以节约索引空间,从而提高索引效率,但这样也会降低索引的选择性 关于索引的选择性,它是指不重复的索引值(也称为基数cardinality)和数据表的记录总数的比值,范围从1/(数据表记录总数)到1之间。索引的选择性越高则查询效率越高,因为选择性高的索引可以让MySQL在查找时过滤掉更多的行。选择性为1的索引叫唯一索引,这是最好的索引选择性,性能也是最好的 建立合理前缀索引的诀窍在于要选择足够长的前缀以保证较高的选择性,同时又不能太长(以便节约空间)。前缀应该足够长,以使得前缀索引的选择性接近于索引的整个列。换句话说,前缀的基数应该接近于完整列的基数

二. 实地测试 ① 创建测试表music,并插入一些数据

代码语言:javascript
复制
create table music( name varchar(30) );

insert into music values('BITE'),('There for you'),('Scarborough Fair'),('Shape of You'),('Marvin Gaye'),('Pretty Girl'),('Pretty Boy'),('Walk Away'),('YOUTH'),('Paris');

insert into music values ('Scarborough Fair'),('Shape of You'),('Marvin Gaye'),('Pretty Girl'),('Pretty Boy'),('Walk Away'),('YOUTH'),('Paris');

② 查看刚刚创建的表的完整列索引选择性

代码语言:javascript
复制
select count(distinct name) / count(*) from music;
完整列索引选择性
完整列索引选择性

③ 找出合适的前缀长度

代码语言:javascript
复制
select count(distinct left(name,1))/count(*) as sel1, count(distinct left(name,2))/count(*) as sel2, count(distinct left(name,3))/count(*) as sel3, count(distinct left(name,4))/count(*) as sel4 from music;
索引
索引

因为该测试表中插入的数据量少,所以可以看到当选择前两个字符作为前缀索引后,索引的选择性达到了0.5,接近完整列的索引选择性0.5556 ④ 建立前缀索引并查看

代码语言:javascript
复制
alter table music add index music_index(name(2));
查看前缀索引
查看前缀索引

⑤ 前缀索引被使用

代码语言:javascript
复制
select * from music where name like 's%';
前缀索引被使用
前缀索引被使用

三. 注意事项 ① 前缀索引是一种能使索引更小,更快的有效办法,但另一方面也有其缺点:mysql无法使用其前缀索引做ORDER BY和GROUP BY,也无法使用前缀索引做覆盖扫描 ② 要明确使用前缀索引的目的与优势: .大大节约索引空间,从而提高索引效率 .对于 BOLB 、 TEXT 或者很长的 VARCHAR 类型的列,必须使用前缀索引,因为 MySQL 不允许索引这些列的完整长度 ③ 前缀索引会降低索引的选择性 ④ 真正的难点在于:要选择足够长的前缀以保证较高的选择性,同时又不能太长, 前缀的长度应该使前缀索引的选择性接近索引整个列,即前缀的基数应该接近于完整列的基数

发布者:全栈程序员栈长,转载请注明出处:https://javaforall.cn/142581.html原文链接:https://javaforall.cn

本文参与 腾讯云自媒体同步曝光计划,分享自作者个人站点/博客。
原始发表:2022年5月2,如有侵权请联系 cloudcommunity@tencent.com 删除

本文分享自 作者个人站点/博客 前往查看

如有侵权,请联系 cloudcommunity@tencent.com 删除。

本文参与 腾讯云自媒体同步曝光计划  ,欢迎热爱写作的你一起参与!

评论
登录后参与评论
0 条评论
热度
最新
推荐阅读
目录
  • mysql前缀索引的索引选择性
相关产品与服务
云数据库 SQL Server
腾讯云数据库 SQL Server (TencentDB for SQL Server)是业界最常用的商用数据库之一,对基于 Windows 架构的应用程序具有完美的支持。TencentDB for SQL Server 拥有微软正版授权,可持续为用户提供最新的功能,避免未授权使用软件的风险。具有即开即用、稳定可靠、安全运行、弹性扩缩等特点。
领券
问题归档专栏文章快讯文章归档关键词归档开发者手册归档开发者手册 Section 归档