MySQL中给字符串字段加索引

---

前言

学完了MySQL索引部分我们清楚的认识到给子段添加索引可以快速的进行查询节约时间。但是索引有很多。那么对于字段怎么加索引加什么索引。加到索引不同效率肯定也会有不同的。接下来我们研究下怎么给字符串字段加索引

---

一、前缀索引和普通索引

我们依旧是通过一个例子进行讲解。

我们用邮箱登录这个业务。创建了一个用户表SQL句如下

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

要是有邮箱登录业务代码中一定会出现如下这样的SQL语句

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

对于这查询语句相比加上索引效率效率更高。
但是加上什么索引呢
如果只是普通的加上索引那么相应的索引对应的B+树中存储的就这email索引列的全部内容。想必都知道一个邮箱账号包含的字符串是很长。如果把这一个很长的字符串充当索引那是很浪费存储空间的。为此我们可以使用前面提到过前缀索引即把email的一部分字符串设置为索引。接下来我们分析学习下两者的效率。

针对email字段创建如下两个不同的索引进行分析

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

第一个语句创建的index1索引里面包含了每个记录的整个字符串而第二个语句创建的index2
索引里面对于每个记录都是只取前6个字节。

针对这两个的存储存储结构图如下所示
对index1:

对index2

从图中的存储可以看出email(6)这个存储占用的空间更小。这是使用前缀索引的优势但是查询效率上呢接下来我们分析一下。

执行下面的SQL语句看看不同的索引执行流程有何不同

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

如果使用的是index1即email整个字符串的索引结构执行顺序是这样的

• 从index1索引树找到满足索引值的这条记录取得ID2的值
• 到主键上查到主键值是ID2的行判断email的值是正确的将这行记录加入结果集
• 取index1索引树上刚刚查到的位置的下一条记录发现已经不满足条件了,循环结束。

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

如果使用的是index2 即email(6)索引结构执行顺序是这样的

• 从index2索引树找到满足索引值是’zhangs’的记录找到的第一个是ID1
• 到主键上查到主键值是ID1的行判断出email的值不是这行记录丢弃
• 取index2上刚刚查到的位置的下一条记录发现仍然是’zhangs’取出ID2再到ID索引上取整行然后判断这次值对了将这行记录加入结果集
• 重复上一步直到在idxe2上取到的值不是’zhangs’时循环结束。

通过这个对比你很容易就可以发现使用前缀索引后可能会导致查询语句读数据的次数变多。

通过看使用前缀索引结构进行检索。如果设置的前缀个数较少那各个字段的区分度不大就会有很多重合的索引就需要多次回表进行检查。区分度越高越好。因为区分度越高意味着重复的键值越少。但是要存储的字符串就会越多所以要平衡下找到最好的前缀索引。

二、前缀索引对覆盖索引的影响

我们将上面的SQL查询语句变成下面的

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

如果使用index1即email整个字符串的索引结构的话可以利用覆盖索引从index1查
到结果后直接就返回了不需要回到ID索引再去查一次。而如果使用index2即email(6)索引结
构的话就不得不回到ID索引再去判断email字段的值。

将index2的定义修改为email(18)的前缀索引这时候虽然index2已经包含了所有的信息但InnoDB还是要回到id索引再查一下因为系统并不确定前缀索引的定义是否截断了完整信

也就是说使用前缀索引就用不上覆盖索引对查询性能的优化了这也是你在选择是否使用前缀
索引时需要考虑的一个因素。

对前缀索引方式的优化

三、优化前缀索引

对于邮箱这样的使用前缀比较合适但是如果像身份证这样的因为身份证前很多位都是表示地理信息的所以每个人的区分度不大。

为了解决这个区分度的问题设计了如下两种方法

第一种方式倒序存储
存储身份证号的时候把它倒过来存每次查询的时候可以这么写

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

第二种方式使用hash字段
在表上再创建一个整数字段来保存身份证的校验码同时在这个字段上创建索引

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

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

它们这两个都不支持范围查询。倒序存储的字段上创建的索引是按照倒序字符串的方式排序的已经没有办法利用索引方式查出身份证号码在[ID_X, ID_Y]的所有市民了。同样
地hash字段的方式也只能支持等值查询。

键值越少。