禅道博客

分享专业技术知识,文章内容干货满满

字符串加索引

2022-10-21 08:56:00
代婷婷
原创 69
摘要:本篇读书笔记介绍了如何使用前缀索引以及其他索引方式。包括前缀索引的特点、如何定义前缀索引的长度、前缀索引的影响等内容。

如何使用前缀索引

一.前缀索引占用的空间会更小:

email建立索引,email(6)来构建索引。


二.查询语句读数据的次数可能会变多:查询email = zhangsan@xxx.com

1. 索引为email,在email索引树上查到这条记录,然后到主键索引树上查询到这一行,判断email是否满足,然后查索引树的下一条记录

2. 索引为email(6),在email(6)索引树上查到满足zhangs的记录,然后到主键索引树上查询到这一行,判断Email是否满足,然后从索引树查询下一条记录,发现还是满足zhangs,继续,直到在索引树上查到不是zhangs时,结束

用email(6)来构建的话,可能重复的比较多,可能用email(7)来构建重复的就不多了

总结:使用前缀索引,定义好长度,可以既省空间,查询成本也不会增加太多。


三.如何定义前缀索引的长度

通过统计索引上有多少个不同的值来判断要使用多长的前缀。

select count(distinct email) from user;

然后选取不同的长度的前缀索引

select count(distinct left(email,4)), count(distinct left(email,5)), count(distinct left(email,6)), count(distinct left(email,7)) from user;

使用前缀做因会损失区分度,在可接受的比例范围内就可以选取使用该长度的前缀索引。


四.前缀索引会对覆盖索引有影响

Select id,email from user where email = zhangsan@xxx.com ’;

Select id,name,email from user where email = zhangsan@xxx.com ’;

第一个查询语句只要求返回id和email,如果使用email真个字符串作为索引,可以利用覆盖索引,从索引树查到结果就直接返回

第二个查询语句还需要到主键索引树上再去判断email的值,然后返回结果


其他索引方式

比如身份证号,前6位是地址码,如果是同一个地区的人,用长度为6的前缀索引区分度很低。

1. 可以使用倒叙存储

2. 使用 hash 字段

1. 从占用的额外空间来看,倒序存储方式在主键索引上,不会消耗额外的存储空间,而 hash 字段方法需要增加一个字段。当然,倒序存储方式使用 4 个字节的前缀长度应该是不够的,如果再长一点,这个消耗跟额外这个 hash 字段也差不多抵消了。

2. 在 CPU 消耗方面,倒序方式每次写和读的时候,都需要额外调用一次 reverse 函数,而 hash 字段的方式需要额外调用一次 crc32() 函数。如果只从这两个函数的计算复杂度来看的话,reverse 函数额外消耗的 CPU 资源会更小些。

3. 从查询效率上看,使用 hash 字段方式的查询性能相对更稳定一些。因为 crc32 算出来的值虽然有冲突的概率,但是概率非常小,可以认为每次查询的平均扫描行数接近 1。而倒序存储方式毕竟还是用的前缀索引的方式,也就是说还是会增加扫描行数。

发表评论
评论通过审核后显示。