【Mysql实战45讲】11 给字符串加索引

前缀索引

• 如果给邮箱加索引，可以考虑用前缀索引：alter table testTable add index prefix_index(email(6));
• 如果定义好长度，使用前缀索引可以做到既节省空间，又不额外增加太多查询成本的作用
• 通过 select count(distinct targetField) as L from targetTable; 计算表中某个字段不重的个数
• 通过 select count(distinct left (targetField, 5)) from targetTable; 计算表中某个字段前5位不重的个数。该个数大于L * 95% 即可选择作为前缀长度，如果5和6都满足，则选择5

前缀索引的缺点

• 前缀索引可能会增加扫描行数
• 使用前缀索引后就无法用上覆盖索引对查询性能的优化了。因为无法确定前缀索引是否截取了完整的信息，所以InnoDB总要回到id索引再次查一次

其它方式

• 对于邮箱，尾部可能都是固定的@qq.com，所以可以较为方便的使用前缀索引，但对于身份证号类型的数字就不行了，身份证号有18位，其中前6位是地址码，所以同一个县的人身份证号前六位是相同的，在这个场景下，可以在存身份证号到数据库时，将号码反转后再存，也就是倒序存储：select field_list from t where id_card = reverse(‘input_id_card_string’);
• 除了倒序存储的方式外，也可以使用 hash 字段：在表上再建一个整数字段，用于保存身份证的校验码，同时给这个字段加索引，每次插入新数据时，同时用 crc32() 这个函数获取校验码填到这个新字段中。这两种方法都不支持范围查询