select id,domain_name from domain where domain_name = 'baidu.com';

为了加快查询效率，我们一般会在domain_name字段上添加索引，那此时我们该选择普通索引还是唯一索引比较合适呢？

接下来，我们就从这两种索引对查询语句和更新语句的性能影响来进行分析。

查询语句

查询语句会通过 B+ 树从树根开始，按层搜索到叶子节点（即数据页），之后在数据页内部通过二分法来定位记录。

普通索引：查找到满足条件的第一个记录后，仍需要查找下一个记录，直到碰到第一个不满足条件的记录为止。

唯一索引：由于索引定义了唯一性，查找到第一个满足条件的记录后，就会停止继续检索。

虽然看上去唯一索引比较占优势，但实际上，这个不同带来的性能差距是微乎其微的。因为InnoDB 的数据是按数据页为单位来读写的，每个数据页的大小默认是 16KB。读取数据时，会以页为单位，将其整体读入内存中。对于普通索引来说，要多做的那一次“查找和判断下一条记录”的操作，就只需要一次指针寻找和一次计算，并不会占用过多的时间。

更新语句

当需要更新一个数据页时，如果数据页在内存中就直接更新；否则，InooDB 会将这些更新操作缓存在 change buffer 中，在下次查询需要访问这个数据页的时候，再将数据页读入内存，然后执行 change buffer 中与这个页有关的操作。

这样可以减少读磁盘，语句的执行速度会得到明显的提升。同时，数据读入内存是需要占用 buffer pool 的，所以这种方式还能够避免占用内存，提高内存利用率。

而对于唯一索引来说，所有的更新操作都要先判断这个操作是否违反唯一性约束。也就是说，判断时需要把对应的数据页读入内存，而更新内存中的数据会比使用 change buffer 效率更高，因此，唯一索引的更新就不能使用 change buffer，实际上也只有普通索引可以使用。

不过，change buffer 并不是适用于所有场景的，对于写多读少的业务来说，页面在写完以后马上被访问到的概率比较小，此时 change buffer 的使用效果最好，比如账单类、日志类的系统。

但是对于写入后立即需要查询的场景，change buffer 随机访问 IO 的次数不会减少，反而增加了 change buffer 的维护代价，此时使用 change buffer 就不合适了。

所以，我们应该根据具体场景，选择适合的索引，使其尽可能的发挥作用。

二. 字符串如何添加索引

在我们平时的业务中，往往需要给字符串类型的字段添加索引，尤其是针对邮箱，身份证号码这种相对较长的字符串，该如何设计索引才能在节省空间的同时又提高检索效率呢？

前缀索引的优化

对于邮箱这种长度较长的字段，直接创建索引会占据较大的空间，如下图所示：

所以我们可以使用前缀索引来进行优化，如下图所示：

这种方式可以节省空间，但是同时带来的损失是，可能会增加额外的记录扫描次数。

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

• 从 index1 索引树找到满足索引值是’zhangssxyz@xxx.com’的记录，取得 ID2的值；

• 到主键上查到主键值是 ID2 的行，判断 email 的值是正确的，将这行记录加入结果集；

• 取 index1 索引树上刚刚查到的位置的下一条记录，发现已经不满足
  email='zhangssxyz@xxx.com’的条件了，循环结束。

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

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

• 从 index2 索引树找到满足索引值是’zhangs’的记录，找到的第一个是 ID1；

• 到主键上查到主键值是 ID1 的行，判断出 email 的值不符合要求，丢弃这行记录；

• 取 index2 上刚刚查到的位置的下一条记录，发现仍然是’zhangs’，取出 ID2，再到
  ID 索引上取整行然后判断，这次符合要求，将这行记录加入结果集；

• 重复上一步，直到在 idxe2 上取到的值不是’zhangs’时，循环结束。

在这个过程中，要回主键索引取 4 次数据，也就是扫描了 4 行。

通过对比发现，使用前缀索引后，可能会导致查询语句读数据的次数变多。

但是，对于这个查询语句来说，如果你定义的 index2 是 email(7)，也就是说取 email 字段的前 7 个字节来构建索引的话，能够直接查到 ID2，只扫描一行就结束了。

可见，使用前缀索引，只要定义好长度，就可以做到既节省空间，又不用额外增加太多的查询成本了。

对于类似于邮箱这样的字段来说，使用前缀索引的效果可能还不错。但是，遇到前缀的区分度不够好的情况，比如身份证号，又该怎么办呢？

我们可以按照前面的逻辑，创建长度为 12 以上的前缀索引，但是，索引选取的越长，占用的磁盘空间就越大，相同的数据页能放下的索引值就越少，搜索的效率也就会越低。

所以我们可以采用两种处理方式来更好的提高搜索效率，分别是 倒序存储 和 hash字段 。

倒序存储和hash字段

1. 倒序存储

是指把字符串倒过来存，每次查询的时候，你可以这么写：

mysql> select id from t where id_card = reverse('input_id_card_string');

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

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