Mysql 系列 | 索引（字符串字段创建索引）

系统中经常会使用到邮箱登录

要验证登录信息常常要根据邮箱查询用户信息，select * from user where email = 'xxx'

怎么给 email 字段创建索引呢？

创建完整索引

/* email 整个字段作为索引 */
alter table user add index index1(email);

• 用 index1 检索，在 index1 索引树中找到对应的 ID2，然后回表查到其他信息。在 index1 索引中查询下一条，不符合条件，查询结束，扫描一行。

（丁奇原图）

创建前缀索引

/* email 前六个字符作为索引 */
alter table user add index index2(email(6));

• index2 只取前六个字符，占用空间较小

• 用 index2 查询，先找到 ID1，回表查其他信息。在 index2 索引树中向后循环，查到 ID2、ID3、ID4。需要回表四次，扫描四行。虽然索引占用空间较小，但是增加了扫描行数。

• 如果把 email(6) 改为 email(7)，则只需要扫描一行。合理设置前缀索引长度，既能节省空间又不增加查询成本。

• 根据索引区分度，选择合适的前缀索引长度。查看不同长度对应有多少数据

select count(distinct email) as L from user;

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,
from user;

• 使用前缀索引时，覆盖索引就会无效，需要综合考量

倒序存储，再创建前缀索引

• 比如市民信息系统中，存储身份证号的字段，前六位地址信息都是一样的。

• 倒序存储然后创建前缀索引，可能绕过前面相同的地址信息，同时能减少索引内存占用，但是无法使用覆盖索引。

• 无法通过范围区间检索

创建 HASH 字段索引

• 新增一个字段存放身份证校验码，同时创建索引。

• crc32() 得到的校验码，不同身份证有可能是相同的，所以查询时要追加判断身份证号是否相同

• hash 字段索引区分度更大，几乎不会有重复的，查询性能稳定。

• 有额外的存储和计算成本，不支持范围扫描