关键词
字符串索引、前缀索引
0.引子
在生活中，我们常常需要对一个全是字符串的字段设置索引。例如，你在维护一个支持邮箱登陆的系统，用户表的部分定义如下：
mysql> create table SUser(
ID bigint unsigned primary key,
email varchar(64),
...
)engine=innodb;
由于要使用邮箱登陆，所以在业务中会经常使用到对邮箱的索引，其语句类似下面这样：
mysql> select f1, f2 from SUser where email='xxx';
在之前我们已经学过了前缀索引，所以如果我们要为 email 字段建立索引，有两种选择：
mysql> alter table SUser add index index1(email);
或
mysql> alter table SUser add index index2(email(6));
这两种选择有何差异呢？下面我们进行分析。
1.全段索引 和 前缀索引
首先，我们看一下两个索引的结构：
11-index1结构.jpg
11-index2结构.jpg
两者的差异如下：
- 相比于 index1，使用前缀索引的 index2 每个节点只用保存 6 个字节，占用的哦你关键更小。
- 但是，同时带来的损失是，每个节点保存了更少的信息，因此可能会增加扫描的次数。
例如，对下面这个语句，两个索引就会出现不同的执行方式：
select id,name,email from SUser where email='zhangssxyz@xxx.com';
- 使用 index1，索引只会回表一次。
- 使用 index2，索引会回表四次。
当然，如果你增加前缀的截取量，可以避免这种情况。如果你在定义 index2 的时候使用 email(7) 而不是 email（6），只需要回表一次。
所以，正确定义好前缀索引的长度，既可以节省空间，又不会增加额外的查询成本。
那么，如果确定前缀长度呢？以上面的例子，你可以用下面的语句查询不同长度下的区别：
mysql> 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 SUser;
查询得到的值越大，它的区分值就越大，你可以用它除以数据总量，得到一个区分度。
除了可能增加扫描次数，使用前缀索引还会带来一些问题。
2.前缀索引对覆盖索引的影响
前面我们讲过，MySQL 对索引的优化之一，是可以使用覆盖索引加速查询。但是如果使用前缀索引，就无法使用覆盖索引了。看下面的例子：
select id,email from SUser where email='zhangssxyz@xxx.com';
- 在这个例子中，如果我们使用 index1 进行查询，可以直接使用覆盖索引，而不必进行回表。
- 如果我们使用 inxdex2，即使我们定义的时候使用了 email(18)（这差不多的相当于全段保存了）的前缀索引，MySQL 依然会进行回表，因为它无法确定在此之后是否真的完全等于 'zhangssxyz@xxx.com' 。
可以看到，使用前缀索引虽然降低了空间的使用，但是依然有很多的副作用，在使用这种索引的时候你需要考虑这些副作用。
除了前缀索引，还有一些方式可以优化字符串字段的索引。
3.其它方式
3.1倒序存储
手机号码前面的很多位都是为了区分号码归属地的，所以如果对电话号码设置前缀索引，就会涉及到多次回表。解决这种问题的四个思路是，使用倒序存储。
如果你将电话号码倒序存储，在查询的时候可以这么写：
mysql> select field_list from t where id_card = reverse('input_id_card_string');
- 当然，你需要考虑到使用 reverse 函数的消耗。
3.2使用hash字段
哈希就不多做解释了，对于它的概念读者可以参考我在“数据结构与算法之美”的文集中的相关篇章。
举个例子，如果你的业务中需要使用身份证号，且会有与之相关的大量查询，你可以在表中添加一个字段，并建立索引：
mysql> alter table t add id_card_crc int unsigned, add index(id_card_crc);
在 MySQL 中，crc 函数可以进行哈希计算，我们要在查询和数据插入的时候使用到它。另外，考虑到哈希冲突，在查询语句中需要添加判断。具体查询语句如下：
mysql> select field_list from t where id_card_crc=crc32('input_id_card_string') and id_card='input_id_card_string'
这样，索引的长度变为 4 个字节，小了很多。
3.3以上两种方式的异同
相同：
- 都无法支持范围查询。范围查询的前置条件是，可以将数据组织成为一个有序的序列（详情请左转
），显然上面两种方式都无法创建这样的序列。
差异：
- 哈希方法会额外添加一个字段，而倒序存储不需要（当然，你需执行字符串翻转这样的骚操作）
- 两者都会对 CPU 产生消耗，但是具体来看，reverse 函数的消耗比 crc 更小。
- 使用哈希的查询性能更加稳定，而倒序存储依然可能出现后缀大量重复的情况。
总结
今天我们探究了一些对字符串字段索引的优化方法，具体方式有：
- 直接创建完整索引，可能比较占用空间。
- 创建前缀索引，节省空间，但是会增加查询次数，无法使用覆盖索引的优化。
- 倒序存储，对字符倒序存储，然后再使用前缀索引，绕过前缀区分度过低的问题。
- 创建哈希字段索引，查询性能稳定，需要额外消耗，不支持范围扫描。
你需要根据业务字段的特点进行选择，或者你可以尝试其它优化方法。
上期问题
在上一篇文章最后，我给你留的问题是，为什么经过这个操作序列，explain 的结果就不对了？这里，我来为你分析一下原因。
delete 语句删掉了所有的数据，然后再通过 call idata() 插入了 10 万行数据，看上去是覆盖了原来的 10 万行。
但是，session A 开启了事务并没有提交，所以之前插入的 10 万行数据是不能删除的。这样，之前的数据每一行数据都有两个版本，旧版本是 delete 之前的数据，新版本是标记为 deleted 的数据。
这样，索引 a 上的数据其实就有两份。
然后你会说，不对啊，主键上的数据也不能删，那没有使用 force index 的语句，使用 explain 命令看到的扫描行数为什么还是 100000 左右？（潜台词，如果这个也翻倍，也许优化器还会认为选字段 a 作为索引更合适）
是的，不过这个是主键，主键是直接按照表的行数来估计的。而表的行数，优化器直接用的是 show table status 的值。
这个值的计算方法，我会在后面有文章为你详细讲解。
思考题
如果你在维护一个学校的学生信息数据库，学生登录名的统一格式是”学号 @gmail.com", 而学号的规则是：十五位的数字，其中前三位是所在城市编号、第四到第六位是学校编号、第七位到第十位是入学年份、最后五位是顺序编号。
系统登录的时候都需要学生输入登录名和密码，验证正确后才能继续使用系统。就只考虑登录验证这个行为的话，你会怎么设计这个登录名的索引呢？
以上就是本节内容，希望对你有所帮助。
注：本文章的主要内容来自我对极客时间app的《MySQL实战45讲》专栏的总结，我使用了大量的原文、代码和截图，如果想要了解具体内容，可以前往极客时间
网友评论