如何给特殊字符串加索引：如身份证、邮箱等

• 1 建表语句

  CREATE TABLE `user` (
    `id` int(11) NOT NULL AUTO_INCREMENT,
    `id_card` varchar(18) NOT NULL,
    `email` varchar(50) NOT NULL,
    PRIMARY KEY (`id`)
  ) ENGINE=InnoDB
  
  INSERT INTO `test`.`user`(`id`, `id_card`, `email`) VALUES (1, '321023199303164012', 'wangheling@163.com');
  INSERT INTO `test`.`user`(`id`, `id_card`, `email`) VALUES (2, '321023199003184012', 'wangwu@163.com');
  INSERT INTO `test`.`user`(`id`, `id_card`, `email`) VALUES (3, '321023199003251234', 'wangsan@sina.com');
  INSERT INTO `test`.`user`(`id`, `id_card`, `email`) VALUES (4, '021236199503251235', 'wangsi@163.com');
  
  

  user表使用邮箱作为登录账号。

  2 邮箱字段建立索引

  这里我们用登录账号，也就是email字段作为索引。

  2.1 直接给email加索引

  select * from user where email = 'wangheling@163.com'

  2.1.1 执行流程

  因为叶子节点存放的就是email的完整值，在非聚集索引里根据email查到了主键id=1，然后进行回表，这里发生一次回表.

  2.1.2 存在问题

  因为邮箱较长，因为b+tree节点是按页存取的，默认16k，如果键值过长，导致问题就是每页存放的键值数量就较少，会增加树高，增加IO次数。

  2.2 前缀索引email(4)

  select * from user where email = 'wangheling@163.com'

  2.2.1 执行流程

  因为叶子节点只会存放email前四个字节的值，所以在非聚集索引里查到四条记录，还要进行四次回表操作，比对email=‘wangheling@163.com’记录进行筛选。

  2.2.2 存在问题

  因为email长度截取了，那么b+tree每个节点存储键值数量多了，树高就低了，那么带来的问题就是，增加了回表的次数。

  2.3 前缀索引email(6)

  select * from user where email = 'wangheling@163.com'

  2.3.1 执行流程

  因为叶子节点只会存放email前六个个字节的值，所以在非聚集索引里查到一条记录，只要进行一次回表操作，比对email=‘wangheling@163.com’，得到结果。

  2.4 阶段小结

  通过上面三个案例可以得到一个结论：

  使用前缀索引，定义好合适的长度，可以在空间和查询效率取得一个平衡。

  那么这个前缀索引长度如何选取呢？
  建立索引的原则就是选取离散度大的字段，那么我们可以计算使用多少长度离散度大：

  select count(distinct left(filed,length)) from tableName;
  

  我们通过如下sql：

  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,
    count(distinct left(email,8)) as L8
  from user;
  

  结果如下：
  
  可以看到当长度为6时，区分度最大，可以email(6)。

  2.5 前缀索引带来其他性能问题

  比如：

  select id, email from user where email = 'wangheling@163.com';
  

  上面这个sql，当我们没有使用前缀索引，利用了覆盖索引，无需回表，而如果使用了前缀索引，因为叶子节点没有保存完整的email信息，那么会进行回表。

  当我们使用前缀索引，就是利用不到覆盖索引，需要回表，所以得根据业务场景来选择是否使用前缀索引。

  3 身份证字段建立索引

  身份证长度为18，不适合作为索引。身份证号码特点：前6位代表地址，中间8位为年月日。

  3.1 前缀索引

  前面谈到前缀索引，就不说了。这里存在问题就是，当维护系统是一个市政系统，因为前6位区分度就不高了，所以需要截取长度要更长，还是浪费空间。

  3.2 倒序存储

  因为身份证后六位区分度高，那么我们可以将身份证倒序存储，然后索引为id_card(6)

  select * from user where id_card = reverse('输入的正序身份证号码')；
  

  倒序存储只适用等值查询。

  3.3 哈希

  可以新增一个字段存储身份证号码的哈希值,加上索引，存入身份证时候，对身份证进行crc3()计算，得到的值存入id_card_crc,索引长度为4，因为hash可能会发生碰撞，所以查询时候加上身份证作为筛选条件：

  select * from user where id_card_crc = crc32("输入的身份证号码") and id_card = '输入身份证号'；
  

  哈希存储只适用等值查询。

  3.4 身份证号码拆分存储

  可以将区分度高的，比如后六位单独存储。

  4 总结

  对于长度较长的字符串，我们可以这么建立索引：

  • 前缀索引
  • 倒序存储
  • 哈希存储
  • 字段拆分