原创 

mysql 前缀索引详解

分类:Mysql    573人阅读    IT小君  2021-07-21 21:00

转载于:https://blog.csdn.net/dhrome/article/details/72853153

mysql前缀索引的索引选择性

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

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

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');

  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8

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

select count(distinct name) / count(*) from music;

  • 1

完整列索引选择性

③ 找出合适的前缀长度

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;

  • 1

索引

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

alter table music add index music_index(name(2));

  • 1

查看前缀索引

⑤ 前缀索引被使用

select * from music where name like 's%';

  • 1

前缀索引被使用

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

点击广告,支持我们为你提供更好的服务

HTML5现代家居装潢公司网站模板

html5 svg夜空中星星流星动画场景特效

HTML5 Canvas竖直流动线条背景动画特效

css鼠标跟随文字模糊特效

HTML5数字产品服务公司网站模板

小众时尚单品在线电子商务网站模板

现代时尚家具公司网站模板

中小型创意设计服务公司网站模板

响应式咖啡饮品宣传网站模板

canvas炫酷鼠标移动文字粒子特效

js+css3抽奖转盘旋转点餐代码

html5 canvas进度条圆环图表统计动画特效

响应式时尚单品在线商城网站模板

有机水果蔬菜HTML5网站模板

jQuery右端悬浮带返回顶部特效

html5图标下拉搜索框自动匹配代码

网页设计开发公司网站模板

css+js实现的颜色渐变数字时钟动画特效

响应式太阳能能源公司网站模板

html5 canvas彩色碎片组合球形旋转动画特效

点击广告,支持我们为你提供更好的服务
 工具推荐 更多»
点击广告,支持我们为你提供更好的服务