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

最新推荐文章于 2024-03-07 23:30:49 发布

笔仙笔仙

最新推荐文章于 2024-03-07 23:30:49 发布

阅读量190

点赞数

分类专栏： MySQL笔记文章标签： mysql java

原文链接：https://time.geekbang.org/column/article/71492

版权

MySQL笔记专栏收录该内容

48 篇文章 1 订阅

订阅专栏

一、抛砖引玉
1.如何在邮箱这样的字段上建立合理的索引？
现有语句：
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));
这两个索引的区别是什么？
⭐️：前缀索引减少存储的空间大小，但是会增加扫描次数

通过语句分析一个过程

select id,name,email from SUser where email=‘zhangssxyz@xxx.com’;
如果使用的是 index1（即 email 整个字符串的索引结构），执行顺序是这样的：
1.从 index1 索引树找到满足索引值是’zhangssxyz@xxx.com’的这条记录，取得 ID2 的值；
2.到主键上查到主键值是 ID2 的行，判断 email 的值是正确的，将这行记录加入结果集；
⚠️：这里为什么要从主建上查询ID2的行判断呢？
据回答是：因为数据库引擎不知道保存在索引树的 zhangssxyz@xxx.com是整个字符串的索引还是字符串前若干各字节的索引
3.取 index1 索引树上刚刚查到的位置的下一条记录，发现已经不满足 email='zhangssxyz@xxx.com’的条件了，循环结束。

⭐️：使用前缀索引，定义好长度，就可以做到既节省空间，又不用额外增加太多的查询成本。但是如何确定我应该使用多长的前缀呢？
主要是区分度，区分度越高越好。怎么计算区分度呢？–以上个SQL为例子
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;
在返回的 L4~L7 中，找出不小于 L * 95% 的值，假设这里 L6、L7 都满足，你就可以选择前缀长度为 6。

找到前缀索引的区分度就万事了？当时不是！！！！！！！！！！！！！！！！

🏁：1.前缀索引对覆盖索引的影响

只有在select选取的是[主键+当前索引]的组合下才发生。就不得不回到 ID 索引再去判断当前前缀索引字段的值（这里不管前缀索引阶段的位数多大都会回表，因为系统并不确定前缀索引的定义是否截断了完整信息。）

总结：使用前缀索引就用不上覆盖索引对查询性能的优化了，这也是你在选择是否使用前缀索引时需要考虑的一个因素。

明白了前缀索引对覆盖索引的影响后，就觉得万事大吉了么？当时不是！！！！！！！！！！！！！！！！

问题：遇到前缀的区分度不够好的情况时，我们要怎么办呢？比如身份证号（这里以维护一个市内的用户为例子）。
限制条件：我们能够确定业务需求里面只有按照身份证进行等值查询的需求
答案：

倒序存储。
举例：查询时倒叙后查询，mysql> alter table t add id_card_crc int unsigned, add index(id_card_crc);
使用Hash字段。你可以在表上再创建一个整数字段，来保存身份证的校验码，同时在这个字段上创建索引。
举例：mysql> select field_list from t where id_card_crc=crc32(‘input_id_card_string’) and id_card=‘input_id_card_string’

二、思考题
题目：学生登录名的统一格式是”学号 @gmail.com", 而学号的规则是：十五位的数字，其中前三位是所在城市编号、第四到第六位是学校编号、第七位到第十位是入学年份、最后五位是顺序编号。
要求：系统登录的时候都需要学生输入登录名和密码，验证正确后才能继续使用系统。就只考虑登录验证这个行为的话，你会怎么设计这个登录名的索引呢？

答案：
由于这个学号的规则，无论是正向还是反向的前缀索引，重复度都比较高。因为维护的只是一个学校的，因此前面 6 位（其中，前三位是所在城市编号、第四到第六位是学校编号）其实是固定的，邮箱后缀都是 @gamil.com，因此可以只存入学年份加顺序编号，它们的长度是 9 位。比如 201100001，这样只需要占 4 个字节。其实这个就是一种 hash，只是它用了最简单的转换规则：字符串转数字的规则，而刚好我们设定的这个背景，可以保证这个转换后结果的唯一性。
PS：整个索引字段都存也没关系，因为数据增量不大，10年也没多少人。