MySQL 字符串索引优化方案

字符串建立索引的优化#

1. 建立前缀索引#

假设建立一个支持邮箱登录的用户表，对于邮件字段来说，可以有以下几种建立索引的方式：

1. 直接对整个字符串建立索引

alter table SUser add index index1(email);

对整个字符串的前一部分建立索引 - 前缀索引

alter table SUser add index index2(email(6));

方式 2 相较于 方式 1 来说，利用前缀索引，占用的空间更小。但有可能造成性能的损失，读取数据的次数变多。
假设在 user 表中存在zhangsp1234@gmail.com, zhangs1pzxyz@qq.com , zhangssxyz@xxx.com, 三条记录。
有这样一条语句 select id,name,email from SUser where email='zhangssxyz@xxx.com';
使用 index1 索引时，流程如下：
在 index1 中，找到名字是 zhangssxyz@xxx.com 的记录，获取 ID.
在主键索引上对应 ID的行，判断 email 是否正确，将记录加入结果集。
接着取 index1 索引的下一条记录，发现不满足 email 格式，结束循环。
使用 index2 索引：
在 index2 中，找到名字是 zhangs 的记录，获取 ID.
在主键索引上对应 ID的行，这时拿到的是 zhangss1234@gmail.com 的行， 发现不符合，丢弃。
接着在 index2 循环，拿到下一条记录 ID。
在主键索引上对应 ID的行，这时拿到的是 zhangsspzxyz@qq.com 的行， 发现不符合，丢弃。
接着在 index2 循环，拿到下一条记录 ID。
在主键索引上对应 ID的行，这时拿到的是 zhangssxyz@xxx.com 的行， 发现符合，纳入结果集。
接着在 in