运维开发网

MySQL如何索引一个字符串字段

运维开发网 https://www.qedev.com 2022-08-06 21:16 出处:网络
本文主要介绍了MySQL怎么给字符串字段加索引,文中通过示例代码介绍的非常详细,具有一定的参考价值,感兴趣的小伙伴们可以参考一下

本文主要介绍了MySQL怎么给字符串字段加索引,文中通过示例代码介绍的非常详细,具有一定的参考价值,感兴趣的小伙伴们可以参考一下

假设,你现在正在维护一个支持电子邮件登录的系统。用户表的定义如下:

create table SUser(ID bigint unsigned primary key,email varchar(64), ... )engine=innodb;

因为要使用邮箱登录,所以业务代码中必须出现这样的语句:

select f1, f2 from SUser where email='xxx';

如果email这个字段没有索引,那么这个语句只能做全表扫描。

1)那么我可以在电子邮件地址字段上建立索引吗?

MySQL支持前缀索引,可以将字符串的一部分定义为索引。

2)如果创建索引的语句没有指定前缀长度,会发生什么?

索引将包含整个字符串。

3)能否举例说明?

alter table SUser add index index1(email);或alter table SUser add index index2(email(6));

1 index1包含每条记录的整个字符串。

在index2中,每个记录只取前6个字节。

4)这两种定义在数据结构和存储上有什么区别?



很明显email(6)的索引结构会占用更小的空间空。

5)邮件(6)的索引结构有什么缺点吗?

额外记录扫描次数可能会增加。

6)在这两种索引定义下,下面的语句是如何执行的?

select id,name,email from SUser where email='zhangssxyz@xxx.com';

1(即整个电子邮件字符串的index1结构),以及执行顺序。

满足从索引1索引树中找到的索引的值是rsquozhangss XYZ @ XXX . com rsquo;这条记录,得到ID2的值;

在表中查找主键值为ID2的那一行,判断email的值是正确的,将这一行记录添加到结果集中;

继续到索引树中的下一条记录,发现email=#39不满足;zhangss XYZ @ XXX . com rsquo;条件,循环就结束了。

在这个过程中,只需要从主键索引中检索一次数据,所以系统认为只扫描了一行。

索引(即电子邮件(6)的索引结构)和执行顺序

从索引2索引树中找到的索引值是rsquo张;记录,找到的第一个是ID1;

在主键上找到主键值为ID1的行,并确定email的值不是rsquozhangss XYZ @ XXX . com rsquo;,这一行记录被丢弃;

记录下一个刚刚在索引2上找到的位置,发现它仍然是rsquo张;,取出ID2,然后从ID索引中取出整行并判断,这次值是对的,并将这一行记录添加到结果集中;

重复上一步,直到idxe2上获得的值不是rsquo张;当循环结束时。

在此过程中,主键索引被检索四次,即扫描四行。

7)从上面的比较中可以得出什么结论?

使用前缀索引后,查询语句可能会读取更多次数据。

8)前缀索引真的没用吗?

如果我们定义的索引2不是email(6)而是email(7),它满足前缀rsquozhangssrsquo只有一条记录,直接到ID2,只扫描一行就结束了。

9)那么使用前缀索引有什么注意事项呢?

选择合理的长度

10)为字符串创建前缀索引时,我如何知道应该使用多长的前缀索引?

计算索引中有多少不同的值,以确定要使用多长的前缀。

11)如何计算索引中有多少不同的值?

select count(distinct email) as L from SUser;

12)得到多少个不同的值对应于指数后,下一步该怎么做?

依次选择不同长度的前缀来查看该值。

select count(distinct left(email,4))as L4, count(distinct left(email,5))as L5, count(distinct left(email,6))as L6, count(distinct left(email,7))as L7,from SUser;

然后在L4~L7中,找到不小于L *的95%的第一个值,说明通过这个指标可以找到95%以上的数据。

13)前缀索引对覆盖索引有什么影响?

以下SQL语句:

select id,email from SUser where email='zhangssxyz@xxx.com';

与前面示例中的SQL语句相比

select id,name,email from SUser where email='zhangssxyz@xxx.com';

相比之下,第一条语句只需要返回id和email字段。

如果使用index1(即整个邮件字符串的索引结构),通过查找邮件就可以得到ID,所以不需要返回表。这是覆盖索引。

如果使用index2(即email(6)索引结构),就要回到ID索引来判断email字段的值。

14)那我为什么不把index2的定义改成email(18)的前缀index呢?

这个18是你自己定义的。系统不知道18的长度是否比我邮件的长度长,所以还是会回表查看验证。

总之,使用前缀索引并不能通过覆盖索引来优化查询性能。

15)对于邮箱这样的字段,使用前缀索引的效果可能会好一些。但是,当前缀身份证的区分度不够好的时候,怎么办呢?

所选索引较长。

但是,它越长,disk 空空间就越大,同一页上能容纳的索引值就越少,影响查询效率。

16)如果可以确定业务需求只包含根据身份证等价查询的需求,有没有其他的处理方式?

既然是从同一个地方来的,我就倒着存。什么时候这样查询?

select field_list from t where id_card = reverse('input_id_card_string');

使用count(distinct)方法进行验证。

使用哈希字段。在表上创建另一个整数字段来保存身份证的校验码,并在这个字段上创建一个索引。

alter table t add id_card_crc int unsigned, add index(id_card_crc);

每次插入新记录时,通过函数crc32()获得的校验码用于填充这个新字段。因为校验码可能会冲突,也就是说两个不同的ID号通过crc32()函数得到的结果可能是一样的,所以你的查询语句的where部分要判断id_card的值是否完全相同。

select field_list from t where id_card_crc=crc32('input_id_card_string') and id_card='input_id_card_string'

这样索引的长度就变成了4个字节(int类型),比原来小了很多。

17)使用反向存储和使用哈希字段有什么异同?

相同点:不支持范围查询。

在逆序存储的字段上创建的索引是按照逆序字符串排序的,没有办法通过索引找出所有ID号为[ID_X,ID_Y]的市民。同样,哈希字段只能支持等价查询。

区分

根据占用的额外空空间,逆序存储方式是在主键索引上,不会消耗额外空空间,而哈希字段方法需要添加一个字段。当然,在反向存储模式下,使用4字节的前缀长度是不够的。如果更长,这种消耗几乎会被额外的哈希字段抵消。

CPU消耗方面,逆序模式下每次读写需要调用一次reverse函数,而哈希字段模式下需要调用一次crc32()函数。如果只看这两个函数的计算复杂度,反函数额外消耗的CPU资源会更小。

从查询效率来看,使用哈希字段的查询性能相对更稳定。因为crc32计算的值有冲突的概率,但概率很小,所以可以认为每次查询的平均扫描行数接近1。毕竟倒存模式还是用前缀索引模式,也就是说会增加扫描行数。

案例:如果你在维护一个学校的学生信息库,学生登录名的统一格式是rdquo学号@ gmail.comquot学号的规则是十五位,其中前三位是市号,第四至第六位是学号,第七至第十位是学年,后五位是序号。

18)系统登录时,要求学生输入登录名和密码,验证无误后才能继续使用系统。如果只考虑登录验证这种行为,你会如何设计这个登录名的索引?

一个学校每年估计有2万新生,50年只有100万记录。能省多少空间?可以在所有字段中直接索引。省去了开发转化和限制的风险,在遇到大量问题时不得不使用后两种方法。

其实直接全字段索引就够了,一个学校数据库的数据量和查询压力不会太大。从优化数据表的角度来说:。后缀@gmail可以存储在单个字段中,也可以由业务代码来保证。.城市号和学校号估计保持不变,也可以用业务代码配置。.然后可以直接存储年份和序号,这个字段可以被所有字段索引。

关于MySQL如何索引字符串字段的文章到此结束。关于MySQL字符串字段的更多信息

0

精彩评论

暂无评论...
验证码 换一张
取 消