还不知道MySQL怎么给字符串加索引？

2022-04-19 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支持前缀索引，可定义字符串的一部分作为索引。
若创建索引的语句不指定前缀长度，那么索引默认包含整个字符串。
比如，这俩在email字段创建索引的语句：
alter table SUser add index index1(email);alter table SUser add index index2(email(6));

第一个语句创建的index1索引，包含每个记录的整个字符串
第二个语句创建的index2索引，对每个记录都只取前6个字节
可见，email(6)索引结构中每个邮箱字段都只取前6字节（zhangs），占用空间更小，这就是前缀索引优势。
这同时带来损失：可能会增加额外的记录扫描次数。
看看下面这语句，在这俩索引定义分别怎么执行。select id,name,email from SUser where email='zhangssxyz@xxx.com'; 如果使用index1，执行顺序如下：

从index1索引树找到满足索引值 ‘zhangssxyz@xxx.com’的记录，取得ID2的值
到主键上查到主键值是ID2的行，判断email值是正确的，将改行记录加入结果集
取index1索引树上刚刚查到位置的下条记录，发现已不满足email=’zhangssxyz@xxx.com’条件，结束循环
该过程，只需回主键索引取一次数据，所以系统认为只扫描一行。
如果使用是index2，执行顺序如下：
从index2索引树找到满足索引值是’zhangs’的记录，找到的第一个是ID1
到主键上查到主键值是ID1的行，判断出email的值不是’zhangssxyz@xxx.com’，该行记录丢弃
取index2上刚刚查到的位置的下条记录，仍是’zhangs’，取出ID2，再到ID索引取整行判断，这次值对，将该行记录加入结果集
重复上一步，直到在idxe2上取值不是’zhangs’，结束循环结束
该过程，要回主键索引取4次数据，即扫描4行。
对比发现，使用前缀索引，可能导致查询语句读数据的次数变多。
但对该查询语句，如果定义index2不是email(6)而是email(7），即取email字段前7字节构建索引，即满足前缀’zhangss’记录只有一个，也能直接查到ID2，只扫描一行结束。
即使用前缀索引，定义好长度，就可做到既节省空间，又不用增加额外太多的查询成本。
要给字符串创建前缀索引1 确定前缀长度在建立索引时我们关注的是区分度，区分度越高越好。区分度越高，重复的键值越少。因此可通过统计索引上有多少不同值判断要使用多长前缀。
可使用如下语句，计算该列上有多少不同值select count(distinct email) as L from SUser; 依次选取不同长度前缀来测该值，比如看4~7个字节前缀索引：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; 使用前缀索引可能会损失区分度，所以需要预先设定一个可接受损失比例，比如5% 。
然后，在返回的L4~L7中，找出不小于 L * 95%的值，假设L6、L7都满足时，即可选择前缀长度最短为6 。2 前缀索引对覆盖索引的影响看如下SQL：select id,email from SUser where email='zhangssxyz@xxx.com'; 与前例SQL语句：select id,name,email from SUser where email='zhangssxyz@xxx.com'; 相比，该语句只要求返回id和email 。
若使用index1，可利用覆盖索引，从index1查到结果后直接返回，不需回到ID索引再查一次。
而若使用index2（email(6)），得回ID索引再判断email字段值。
即使将index2定义改为email(18)，虽然index2已包含所有信息，但InnoDB还是要回id索引再查，因为系统并不确定前缀索引的定义是否截断了完整信息。
即前缀索引根本用不上覆盖索引对查询的优化，这也是选择是否使用前缀索引时需要考虑的因素。3 其他方案对类似邮箱这样字段，前缀索引可能还行。但遇到前缀区分度不好的，怎么办？
比如身份证号18位，前6位是地址码，所以同县人身份证号前6位一般相同。
推荐阅读

上一篇：史上最恐怖的行星

下一篇：找到答案有没有外星人,科学家对此(如果我们发现了外星生命)