还不知道MySQL怎么给字符串加索引？( 二 ) _MySQL

假设维护数据库是个市公民信息系统，若对身份证号做长度6前缀索引，区分度非常低。
可能需创建长度12以上前缀索引，才能够满足区分度要求。
但索引选取越长，占磁盘空间越大，相同数据页能放下索引值越少，查询效率就越低。

若能确定业务需求只有按身份证进行等值查询的需求，还有没有别的处理方法，既可占用更小空间，也能达到相同查询效率？
Yes！
第一种方式使用3.1 倒序存储如果存储身份证号时把它倒过来存，每次查询这么写：select field_list from t where id_card = reverse('input_id_card_string'); 由于身份证号最后6位没有地址码这样重复逻辑，所以最后6位可能提供足够的区分度。
实践中也别忘记使用count(distinct)验证区分度哦！
第二种方式是使用3.2 hash字段可在表再创建整数字段，保存身份证的校验码，同时在该字段创建索引。alter table t add id_card_crc int unsigned, add index(id_card_crc); 每次插新记录时，同时用crc32()函数得到校验码填到该新字段。
由于校验码可能存在冲突，即两不同身份证号crc32()所得结果可能相同（哈希冲突），所以查询语句where部分要判断id_card值是否精确相同。select field_list from t where id_card_crc=crc32('input_id_card_string') and id_card='input_id_card_string' 这索引长度变4字节，比原来小很多。3.3 倒序存储和使用hash字段异同点相同点都不支持范围查询。
倒序存储的字段上创建的索引
按倒序字符串的方式排序，已无法利用索引查出身份证号码在[ID_X, ID_Y]的所有市民
hash字段也只支持等值查询区别占用的额外空间
倒序存储在主键索引上，不会消耗额外存储空间
当然，倒序存储使用4字节前缀长度应该不够，若再长点，这消耗和hash字段也差不多了
hash字段需要加个字段CPU消耗
倒序方式每次读写时，都需额外调用次reverse函数
hash字段需额外调用一次crc32()函数
若只从这俩函数计算复杂度看，reverse函数额外消耗CPU资源较少。查询效率
hash字段查询性能较稳定
因为crc32值虽然会冲突，但概率很小，可认为每次查询的平均扫描行数接近1
倒序存储
还是前缀索引，即还是会增加扫描行数总结字符串字段创建索引的场景，可使用的方式如下：

直接创建完整索引，这样可能比较占用空间
创建前缀索引，节省空间，但增加查询扫描次数，且不能使用覆盖索引
倒序存储，再创建前缀索引，用于绕过字符串本身前缀的区分度不足缺陷
创建hash字段索引，查询性能稳定，有额外存储和计算消耗，跟第三种方式一样不支持范围扫描
实际应用中，根据业务字段的特点选择使用哪种方式。思考题维护学生信息数据库，学生登录名的统一格式是”学号@gmail.com”
学号的则是：十五位的数字，其中前三位是所在城市编号、第四到第六位是学校编号、第七位到第十位是入学年份、最后五位是顺序编号。
系统登录时输入登录名和密码，验证正确后才能继续使用系统。
只考虑登录验证，怎么设计这个登录名的索引呢？
上期我留给你的问题是，给一个学号字段创建索引，有哪些方法。
由于学号规则，无论正向反向前缀索引，重复度都较高。
因为维护的只是一个学校的，因此前面6位（其中，前三位是所在城市编号、第四到第六位是学校编号）固定，邮箱后缀都是@gamil.com，因此可只存入学年份加顺序编号，长度9位。
在此基础，可用数字型存这9位数字。比如201100001，只需占4字节。其实这就是种hash，只是用最简单转换规则：字符串转数字，而刚好我们设定背景，可保证转换结果唯一。
当然了，一个学校的总人数这种数据量，50年才100万学生，这表肯定是小表。为了业务简单，直接存原来的字符串。“优化成本和收益”的思想。