MySQL 笔记整理（11） --怎么给字符串字段加索引？

笔记记录自林晓斌（丁奇）老师的《mysql实战45讲》

（本篇内图片均来自丁奇老师的讲解，如有侵权，请联系我删除）

11） --怎么给字符串字段加索引？

　　日常工作中的登录系统，你很可能会使用emai这个字段。因此也很容易遇到类似这样的语句：

mysql> select *from user where email = 'xxx';

　　我们知道，如果没有索引那就只能使用全表扫描。并且mysql是支持前缀索引的，也就是说，你可以顶一个字符串的一部分作为索引。默认地，如果你创建索引的语句不指定前缀长度，那么索引就会包含整个字符串。由于email字段通常较长，如果你直接使用email作为索引的话，索引占用的空间就会较大。所以可以采用前缀索引的优势，比如，建立索引email(6)，每个邮箱字段只取前6个字节。但这也会带来损失，如你可能会增加额外的记录扫描行数。因为前6位相同的邮箱就没办法直接通过eamil的索引来判断了。因此我们建议使用前缀索引，定义好长度，就可以做到既节省空间，又不用额外增加太多的查询成本。

前缀索引对覆盖索引的影响：

　　我们知道，普通索引存储的是主键索引的值，因此，如果你不是select* 而是select id，email就可以不进行回表，也就是覆盖索引。但此处有一个隐藏的问题，即，如果你使用了email字段来作索引，没问题可以利用覆盖索引的性质来进行快速的查询。但如果你使用的是前缀索引email(255)，(我们db中的email数据的长度均不超过它)，虽然索引覆盖了全部长度的email的值，还是不能使用覆盖索引的性质。因为系统并不确定前缀索引是否截断了完整的信息，因此必须进行回表。

其他方式：

　　当然，你也可能会遇到字段的前缀区分度不够高的情况。如我们国家的身份证号，对于同一个城市的人，身份证号前几位的区分度很低。此时，你可以有别的方式来进行处理如：

• 使用倒序来存储数据。
• 使用hash字段存储。

　　这两种方式的相同点是都不支持范围查询，并且都提供了足够多的区分度。另外，hash计算的结果虽有冲突的可能，但概率很低，因此可以认为每次查询的扫描行数接近1.但使用hash的方式会额外增加计算的消耗以及额外的存储空间的消耗。

上期问题：

　　如果没有session a的配合，只是单独执行 delete from t; call idata()； explain这三条语句，会看到explain结果中rows字段其实还是再10000左右，即使用了索引，这是为什么呢？

　　答：delete语句删掉了所有的数据，然后通过call idata()插入了10万行数据，看上去是覆盖了原来的10万行。但是，session a开启了事务并没有提交，所以之前插入的10w行数据不能删除，这样，之前的数据每一行都会有两个版本，旧版本是delete之前的数据，新版本是标记为delete的语句。这样索引a上的数据其实是有两份的。由于mysql是使用删除标记来删除记录的，并不从索引和数据文件中真正删除。如果delete和insert中间的间隔相对较小，purge线程还没来得及清理记录。如果主键相同的情况下，新insert会沿用delete之前的记录空间，因此统计信息不变。

问题:

　　如果你要维护学生信息的数据库，学生登录名统一是"学号@gamail.com"，学号的规则是十五位的数字，前三位是城市编号，四到六位是学校编号，七到十位是入学年份，最后五位是顺序编号，只考虑登录验证的话，你会怎么设计这个登录名的索引呢？