持续创作，加速成长！这是我参与「掘金日新计划 · 6 月更文挑战」的第2天，点击查看活动详情

背景

我们维护一个支持手机、邮箱登录的系统，表结构定义如下：

CREATE TABLE `card_info` (
   `id` int(11) unsigned primary key,
  `account` varchar(200) NOT NULL COMMENT '加密机加密后的手机 、邮箱',
  ...
 ) ENGINE=InnoDB

业务代码中有根据账号查询的sql语句：

select f1,f2 from card_info where account='xxx';

如果account字段不建立索引，那么会全表扫描。

如何建立索引

account是加密的账号，加密后的字段较长，不适合直接创建索引。索引的长度会直接影响索引文件的大小，从而影响查询效率。

计算数据的前缀字符串的区分度。前七个字符的区分度为2，前八个字符的区分度为17，要创建较长的索引才能使区分度达到要求。因此不适合创建前缀索引。

select count(distinct left(account,7))  from card_info; =>  2

select count(distinct left(account,8))  from card_info; =>  17

创建hash字段索引的缺陷是需要额外的空间存储hash字段，但同时，索引的长度变小了很多，这样做的性能会很高。

新增一个被索引的account_hash列，查找的时候只需要根据哈希值做快速的整数比较就能找到行数据，然后一一比较返回对应的行。

hash值的维护可以手动维护也可以使用触发器维护。

在查询的时候，where语句中需要包含常量值。

select f1,f2 from card_info where account_hash = 'xxx' and account = 'xxx';

account = 'xxx'需要带上，hash值可能会有哈希冲突，一旦出现hash冲突，会返回多个结果，这时需要通过对比account的值找到我们要找的数据。

如果有错误的地方欢迎大家指出！

大家一般会对较长的字段如何添加索引呢？欢迎讨论。