一文搞懂MySQL前缀索引
大家好,又见面了,我是你们的朋友全栈君。
引入
通常在开发中我们需要定义字符串类型的字段,例如用户名或者用户邮箱等。 假设我们在维护一个用户登录系统,用户表的定义:
create table User(
ID bigint unsigned primary key,
email varchar(64)
)engine=Innodb;
如果使用邮箱登录的话,查询语句可能这样写:
select ID from User where email='xxx';
如果email字段没有加索引,那么这个语句只能做全表扫描。
前缀索引
MySQL是支持前缀索引的,也就是说,你可以定义字符串的一部分作为索引。如果不指定前缀索引,那么索引就是整个字符串。
例子:
alter table User add index index1(email);
alter table User add index index2(email(6));
第一句SQL创建的索引就是将email整个字符串作为索引;第二个SQL语句创建的索引,只取email字符串的前6个字节作为索引。
存储过程中的具体区别如下图所示
显然可以从图中知道,email(6)这个索引结构中每个邮箱字段只取前6个字节,所以占用的空间更少,这就是使用前缀索引的优势。
缺点: 可能会额外的增加记录扫描的次数。
这个该怎么理解呢?
select id,name,email from User where email =' zhangsan@xx.com';
使用的是将整个字符串作为索引结构。 过程如下:
- 从index1索引树上找到索引值是”zhangsan@xx.com”的这条记录,去的ID2的值
- 到主键中查ID2的这一行,判断email的值是否是正确的,将这行记录装入结果集中;
- 再回到index1这个索引树上,继续判断下一条记录,发现不满足where条件,结束循环。
这个过程中只需要从主键索引树上查找一次数据,系统自认为扫描了一行。
使用前缀索引的执行过程
- 从index2的索引树上,找到满足索引值是“zhangs”的记录,找到第一个是ID1;
- 到主键索引树上查到ID1这一行,判断email的值满不满足where后的条件,不满足这一行丢弃。
- 继续回到index2这个索引树上查下一条记录,发现如果还是”zhangs”,取出ID2,再回到ID2索引树上进行判断,如果值正确,将结果返回结果集中。
- 重复执行以上流程,直到从index2索引树上取出的数据不是“zhangs”,循环结束。
通过以上执行流程的分析你就可以知道,前缀索引会导致扫描的行数变多,这和你所指定前缀的长度有关。或许email(7)中的区分度就比email(6)高,就不会扫描那么多行。
也即是说使用前缀索引,定义好长度,就可以节省空间又不用额外增加太多的查询成本
那怎样定义前缀索引长度比较好呢?
实际上,建立索引时关注的是区分度,区分度越高,越能体现索引的价值和他的优势。因此我们可以通过统计索引上有不同的值来判断要使用多长的前缀。
select count(distinct email) as L from User;
前缀索引对覆盖索引的影响
前面我们说了使用前缀索引可能会增加扫描行数,这会影响性能。其实前缀索引的影响不止如此:
select id ,email from User where email='zhangsan@xx.com';
select id , name, email from User where email='zhangsan@xx.com';
第一句SQL相比于第二条SQL,只返回了id和email。如果使用email整个字符串作为索引的话,可以利用覆盖索引,从index1查到结果直接返回,不需要回表。但是如果使用前缀索引的话,是需要回表进行判断的。
倒序存储与Hash存储
在选取索引的时候,我们需要明白:索引选取的越长,占用的磁盘空间就越大,相同的数据页能放下的索引值就越小,搜索的效率也就会越低。
如果我们在区分度不是很高的场景下,前缀索引的效果就不明显了,我们该如何才去措施提高查询效率。
采用倒序存储方式
select filed_list from t where id_card = reverse('input_id_card')
因为字符串正序的区分度不够明显所以可以看看如果采用倒序的话情况如何,如果倒序的区分度更高,可以采用这种方式。
采用Hash字段
alter table t add id_card_crc int unsigned,add index(id_card_crc);
这里在表t中多加入了一个字段 id_card_crc并把它作为索引。 然后每次插入新纪录的时候,都用crc32函数得到校验码填充到这个新字段中。由于产生的校验码也有可能冲突(相同)所以查询条件部分需要判断id_card的值是否相同。
select field_list from t
where id_card_crc=crc32('input_id_card_string')
and
id_card='input_id_card_string'
两者的对比
相同点
- 都不支持范围查询,只能等值查询。 不同点
- 从查询效率上看,使用的hash字段方式的查询性能相对稳定一点,因为crc_32算出的值虽然有可能冲突,但是概率还是很小的。而倒序方式其实还是用的前缀索引的方式还会增加扫描行数。
- 从存储空间上看,倒序存储不会在主键上消耗额外的空间,但hash字段需要增加一个新字段。
- 从CPU消耗来讲,倒序每次写和读的时候都需要调用reverse函数;hash字段的方式需要嗲用crc32函数。从函数的复杂度讲,reverse效率更高一些。
总结
在向字符串类型的字段加索引的时候,需要考虑前缀索引是否合适,实在不行再加全字段索引。
- 全字段索引相比于前缀索引占用的空间多些。
- 创建前缀索引节省空间,但是会增加查询的扫描行数,并且加了之后不能使用覆盖索引。
- 倒序存储是基于前缀索引的改良版,用于字符串本身区分度不高的情况下。
- 创建hash字段索引,查询稳定但需增加一个额外的字段。
发布者:全栈程序员栈长,转载请注明出处:https://javaforall.cn/146238.html原文链接:https://javaforall.cn
相关文章
- mysql数据库视图索引_MySQL数据库的视图、索引「建议收藏」
- 【MySQL高级】Mysql的体系结构概览及存储引擎以及索引的使用
- 【愚公系列】2023年03月 Java教学课程 108-MySQL数据库(索引)
- MySQL Error number: MY-011540; Symbol: ER_GRP_RPL_SLAVE_SQL_THD_ON_SECONDARY_MEMBER; SQLSTATE: HY000 报错 故障修复 远程处理
- 查询MySQL索引大小的简易方法(mysql查询索引大小)
- 部署分布式MySQL数据库:提升存储空间及性能(分布式mysql数据库)
- 恢复MySQL数据库:简单易行的方法(怎么还原mysql数据库)
- Mysql索引:提升查询速度的神奇武器(mysql索引的作用)
- MySQL建表优化:如何创建有效的索引(mysql建表索引)
- MySQL字符比较:精确实现字符串匹配(mysql字符比较)
- MySQL中创建索引的正确方式(mysql创建索引语句)
- MySQL时间戳转换为直观的日期格式(mysql时间戳转为日期)
- MySQL索引优化:实现更高效的查询算法(mysql索引算法)
- MySQL的唯一索引:优化数据搜索(mysql的唯一索引)
- 如何存储MySQL中二进制数据存储方式研究(mysql二进制数据)
- 创建MySQL聚合索引:优化数据检索性能(mysql创建聚合索引)
- 了解MySQL数据库5.6的新特性与提高数据管理效率(mysql数据库5.6)
- Mysql中强制使用索引的技巧(mysql强制索引)
- Mysql中删除记录的SQL语句(mysql删除sql语句)
- MySQL中使用两个冒号作为字符串连接符号的方法(mysql中两个冒号)
- 使用in操作符时MySQL是否能够充分利用索引MySQL In操作符优化技巧(mysql中in走索引吗)
- 深入浅出MySQL中ER图的作用及实现方法(mysql中er图的作用)
- B站学习MySQL最佳资源尽在这里(b站mysql学习资源)
- ASP搭配MySQL使用的缺点及解决办法(asp使用mysql缺点)
- 活动连接用CMD查看MySQL活动连接情况(cmd产看mysql的)
- 妙用命令行清理MySQL空间(cmd mysql 清屏)
- MySQL优化技巧掌握两级索引的使用方法(mysql两级索引)
- MySQL数据库优化创建两字段唯一索引,实现数据一致性(mysql两字段唯一索引)
- MySQL 索引优化海量数据上亿行的索引建立方法(mysql上亿行建索引)
- MySQL数据修改被禁止,操作被限制(mysql不允许保存更改)