字符串加索引

最新推荐文章于 2024-10-09 15:46:48 发布

渔老师

最新推荐文章于 2024-10-09 15:46:48 发布

阅读量1.1k

点赞数

分类专栏： mysql 文章标签： mysql 数据库

本文链接：https://blog.csdn.net/weixin_36723038/article/details/121645706

版权

mysql 专栏收录该内容

14 篇文章 0 订阅

订阅专栏

本文探讨了如何在MySQL中使用前缀索引提升邮箱查询效率，包括索引的不同定义、查询实例分析、前缀索引对覆盖索引的影响，以及倒序存储和hash字段的解决方案。了解如何平衡空间、性能和区分度，以实现高效数据库管理。

摘要由CSDN通过智能技术生成

文章目录

字符串加索引

字符串加索引

在邮箱字段建立合理索引：

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

使用邮箱登录，SQL

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

邮箱email字段没有索引，Mysql会做全表扫描。
MySQL支持前缀索引，可以定义字符串的一部分作为索引。如果创建索引的字段不指定前缀长度，默认的话索引会包含全部字符串。

1. 前缀索引：

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

index1，包含了记录的整个字符串。
index2，对于每个记录都知取前6个字节。

两种不同定义在数据和结构上的区别

index1索引

index2索引

Index2由于emal(6)只取前6个字节，所以占用的空间更小，这是前缀索引的优势
但是可能会增加额外的记录扫描次数

1. 查询实例：

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

Index1：

从index1索引书找到满足索引值是‘zhangssxyz@xxx.com’的这条记录，取得ID2
到主键上查ID2的行，判断eamil结果，正确就加入结果集。
去index1所i因上刚刚查到的位置的吓一跳记录，发现不满足这个条件，循环结束。

这个过程中，只需要回主键索引取一次数据，索引系统认为只扫描了一行。

Index2：

从index2索引树找到满足 'zhangs’的记录，找到的是ID1。
到主键上查到主键值是ID1的行，判断出email不满足条件。所以丢弃记录。
取下一条记录，再到主键索引上判断，正确加入结果集。
重复上一步，直到indenx2上取到的值不是 'zhangs’时结束循环。

因此：使用前缀索引可能会导致查询语句读数据的次数变多。因此前缀索引需要定义好长度，就可以既节省空间，又不用增加查询成本。
在建立索引的时候，需要关注区分度，区分度越高越好，区分度越高意味着重复的键值越少。可以通过统计索引上有多少不同的值来判断要使用多长的前缀。

先判断这个列上有多少不同的值。

mysql> select count(distinct email) as L from SUser;

再依次选取不同长度前缀来看。比如取4~7个前缀索引

mysql> 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;

同时，使用前缀索引可能会损失区分度，所以需要设定一个可以接受的损失比例。

2. 前缀索引对覆盖索引的影响

两条sql对比
- select id,email from SUser where email='zhangssxyz@xxx.com';
- select id,name,email from SUser where email='zhangssxyz@xxx.com';

第一个sql只要返回id和email。
如果使用index1(即eamil整个字符串的索引结构)的话，可以利用覆盖索引，从index1查到结果后就直接返回了，不需要回到id索引再去查一下。
使用index2(即email(6)索引结构的话)，就不得不回到id索引再去判断email的值。
前缀索引对覆盖索引的影响，因为DB需要回表判断前缀信息是否和条件值是一致的。
前缀索引由于只保存了字段的一个前缀（不完整），在二级索引上无法将数据确定出来，因此必须需要回表一次以确定数据行；而前缀索引的目的是在二级索引树上保存更多的数据以减少回表的次数。

3. 其他方式

索引选取的越长，占用的磁盘空间就越大，相同的数据也能放下的索引值就越少，搜索的效率就会越低。
如果在实际业务中存在身份证进行等值查询的需求。可以有两种方法达到占用更小空间，获取相同查询效率。

1. 倒叙存储

身份证后六位没有地址码的重复逻辑，所以最后6位就提供了区分度。在实践中还需要count(distinct)方法去做验证。

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

2. 使用hash字段

可以在表上再创建一个整数字段，来保存身份证的校验码，同时在这个字段上创建索引

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

然后每次插入新记录的时候，都同时用 crc32() 这个函数得到校验码填到这个新字段。由于校验码可能存在冲突，也就是说两个不同的身份证号通过 crc32() 函数得到的结果可能是相同的，所以你的查询语句 where 部分要判断 id_card 的值是否精确相同。

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

这样，索引的长度变成了 4 个字节，比原来小了很多。

3. 两种方法异同点

相同点：

都不支持范围查询
1. 倒叙存储的字段上创建的索引时按照倒序字符串的方式排序的，已经没有办法利用索引查出区间的身份证。
2. hash只支持等值查询。

不同点：

从占用的额外空间看
1. 倒序存储方式在主键索引上，不会消耗额外的存储空间。
2. hash字段需要增加一个字段。
从CPU消耗看：
1. 倒序方式每次写和读的时候，都需要调用一次reverse函数。
2. hash字段的方式需要额外调用一次crc32()函数。
3. 从两个函数的计算复杂度看，reverse函数消耗的cpu资源会更小。
从查询效率看：
1. 使用hash更稳定，因为crc32算出来的值虽然后冲突的概率，但是概率非常小。可以认为每次查询的平均扫描行数接近1.
2. 倒序存储方式使用的前缀索引的方式，还是会增加扫描行数。