《MYSQL》字符串字段怎么加索引

阿边_
311 阅读6分钟

字符串字段怎么加索引

建表语句

mysql> create table member(
id bigint unsigned primary key,
email varchar(64), 
... 
)engine=innodb;

当我们使用email字段作为条件查询时，会执行下面这个sql

mysql> select f1, f2 from member where email='xxx';

普通索引

在没有建立索引时，mysql会进行全表扫描。那我们给email字段建立一个索引，执行如下语句

mysql> alter table member add index idx_email(email);

再次执行上述的查询语句时，会采用索引查询，效率比全表查询高上不少。

这里把email索引结构给画出来了，根据图片分析一下执行流程：

img

mysql> select f1, f2 from member where email='zhangssxyz@xxx.com';

当用户执行上面sql时

  1. 会去索引文件从根节点开始查询email = 'zhangssxyz@xxx.com'的节点，并取得对应的ID值
  2. 根据ID值到主键索引文件中查找对应的数据，找到以后，加入到结果集中
  3. 继续往后查找，直到找到第一条emial != 'zhangssxyz@xxx.com'的数据，返回结果集

在上述流程中，每一个索引节点存储的数据时一页（16kb），这种情况下，邮箱的长度太长会导致单个节点能存储的索引数较少。我们可以采取下面这种方式来优化

mysql> alter table member add index idx_email(email(6));

前缀索引

这种索引叫做前缀索引，指定了索引长度，即'zhangssxyz@xxx.com'作为索引时，只截取前六个字符串'zhangs'作为索引。

这样做的好处在于，截取六个字符串作为索引，索引文件中每一个节点存储的key增加了。但是，这样做也存在一些弊端，从执行流程分析，来看一下存在什么问题。

这里也将索引结构截了出来

img

取前缀索引时，我们再来分析执行流程，还是上面那条查询语句

  1. 将查询的zhangssxyz@xxx.com与索引数中的第一个节点比较，前六位字符串相等，取出ID1。
  2. 使用ID1到主键索引中搜索记录，并比较ID1行数据的email值与zhangssxyz@xxx.com值不相等。则丢弃该条记录
  3. 继续到index2索引树中，重复第一步操作，取出ID2
  4. 使用ID2到主键索引中继续查询，比较ID2行数据对应的email值 == zhangssxyz@xxx.com，将结果存入结果集。
  5. 重复第三步和第四步的步骤，直到在index2索引树中，找到第一个索引 != zhangs的，就结束查询。

通过执行流程我们可以发现，我们在index2索引表中取了四次ID值，并用ID值到主键索引文件中进行了四次查询。对比第一种索引，虽然索引长度限制了，每个节点存储的key增多了，但是，回表操作的次数却从一次增加到四次了，感觉有点得不偿失呢？其实不然，这里我们可以对第二种索引再做一下改变。我们将索引的长度从6个变成7个，这样再走上述的流程，就会发现，只需要在index2中取出ID2，之后回表查询取出email进行比较即可。再比较下一个key时，zhangss != zhangsy就结束此次查询了。

到这里，你是不是有一些疑问呢？实际业务中，如何判断索引长度应该取多少呢？这里提供一种思路来计算区分度

select count(distinct left(列名, 索引长度))/count(*) from table

区分度在90%以上，即可满足存储的key增多，回表的次数也不会增加。

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

当我们使用前缀索引时，因为索引是截取过的，即使截取后的key和传入的值完全相等，mysql也不能判断该索引就是需要查找的记录，所以还是会取到ID值到主键索引文件去进行二次查询，也就无法避免回表操作。

所以，在使用了前缀索引的情况下，覆盖索引也就失效了。

看到这里，字符串添加索引是不是胸有成竹多了？但是，你还需要注意一些特殊情况，比如说，有一个查询是建立在身份证号上，你应该如何创建索引呢？

结合刚刚的方法，我们去给身份证前6位做截取，你会发现同一个镇上的人前六位都是相同的，如果我们继续扩大索引的长度，一个索引节点存储的key又会变少了，有没有什么好的解决方案呢？

这里提供两种方案

逆向存储

具体做法如下，以510500xxxxxx135236为例,

  1. 建立索引的时候，还是以6位为长度创建前缀索引
  2. 数据存储的时候，将"510500xxxxxx135236"先反转成"632531xxxxxx005015"再进行存储。此时索引的key为"632531"。

当执行身份证号查询时，sql操作如下

mysql> SELECT id, name, reverse(id_card) as id_card FROM member WHERE id_card = reverse('510502199512138029');

具体执行如下：

  1. 使用反转后的字符串"632531xxxxxx005015"到普通索引表中去查询，发现和"632531"相等，则取出ID值
  2. 只用ID值回表查询获取ID值对应的行数据，并判断id_card字段值与"632531xxxxxx005015"是否一致。如果一致，则加入结果集
  3. 判断下一个key与"632531xxxxxx005015"的前六位是否相等，不相等则结束语句，如果相等，则重复上面的流程

基于该方法，key占用的索引长度较少，索引的区分度也较高

Hash索引

具体做法如下

  1. 我们需要创建一个id_card_crc字段，用来存储身份证号hash后的值。
  2. 进行数据插入时，调用crc32()算法，求的hash后的值
INSERT INTO member (id_card, id_card_crc, name) value ('510502199512138033', CRC32('510502199512138033'), "王五");

3.进行身份证号查询时，也需要进行crc32()算法

SELECT ID,name,id_card from member where id_card = '510502199512138033' and id_card_crc = CRC32('510502199512138033');

根据这种方法也能找到我们需要的数据，但需要扩增一个字段去存储hash计算后的值。

这里进行一个总结：

  1. 字符串字段建立普通索引（在覆盖索引的情况下，不会回表操作，但索引节点存储的key数量较少）
  2. 前缀索引（覆盖索引失效，节省空间，但扫描次数会增多 ）
  3. 倒序存储（前缀索引的一种，用来避免前缀索引区分度不高的问题）
  4. hash存储（需要另外一个单独的列用来存储hash后的值，占用少量空间，也能解决前缀索引区分度不高的问题）
avatar
文章
阅读
粉丝