写点什么

字符串可以这样加索引,你知吗?《死磕 MySQL 系列 七》

作者:咔咔
  • 2021 年 12 月 08 日
  • 本文字数:2195 字

    阅读完需:约 7 分钟

字符串可以这样加索引,你知吗?《死磕MySQL系列 七》

相信大多数小伙伴跟咔咔一样,给字符串添加索引从未设置过长度,今天就来聊聊如何正确的给字符串加索引。


一、如何建立索引


大多数系统都会存在用户表,并且系统初始设计使用了手机号码登录的。


这是产品提出了一个需求,让系统也可以支持邮箱登录。



肯定知道的是若不给邮箱字段添加索引执行查询是会全表扫描。


此时你心里窃喜这还不简单,给邮箱字段加个索引完事呗!但要做到复杂的需求做好,简单的需求要最好,减轻一切对系统的压力。


此时的你拿起键盘就执行了alter table table_name add index idx_field (field)



有部分小伙伴不喜欢命令行创建索引,喜欢使用 phpmyadmin 工具来操作 MySQL,那么在建立索引时有没有发现后边可以设置大小呢?



通过上边给大家展示的图片知道字符串建立索引是可以定义长度的,那么两者有什么区别。


使用命令行alter table table_name add index idx_field (field)直接创建的索引默认是包含整个字符串。


若这样执行就指定了索引前缀长度alter table table_name add index idx_field (field(6))


一图解千愁,看一下建立的两个索引结构是什么样的。


索引一结构图



索引二结构图



从图中可以看到,指定了索引长度为 6 那么就只取邮箱字段的前 6 个字段,相对索引包含整个字符串来说每个节点存储的数据会更多。


索引那篇文章也给大家说了建立索引在合适的范围内越小越好。


万物皆两面,有坏就有好,第六期文章误选索引的因素之一就是扫描行数。


索引长度减少带来的影响就是索引基数变大,从而增加额外的扫描记录数(执行 explain 的 row 字段)。


此时要执行select id,name,email from mac_user where email='1397393964@qq.com';


给整个字符串添加索引执行流程


1、从 email 索引树找到满足 1397393964@qq.com 的记录,得到主键 ID 为 1


2、根据 ID 为 1 到主键索引树找到这条记录并判断 email 是否正确,将这行记录假如结果集。


3、重复第一步,直到不满足查询条件,循环结束。


指定索引长度执行流程


1、从 email 索引树找到满足 139739 的记录,得到主键 ID 为 1


2、根据 ID 为 1 到主键索引树找到这条记录并判断 email 不正确,丢弃这行记录。


3、在 email 索引树找刚刚查询的下一条记录,发现还是 139739,去除 ID2,再到 ID 的索引树进行判断,当值对后加入结果集。


4、再继续重复上一步,直到不满足查询条件,循环结束。


结论


在模拟执行流程过程中很容易就发现,使用前缀索引会导致读取数据的次数增加,那是不是就代表使用前缀索引会增加查询代价呢?


肯定不是的,试想此时定义的长度是 6 那么设置为 7 或者 8 呢!是不是会好很多,图中的案例为了方便设置了三个一样的数据,但实际情况基本不会出现这样的情况。


建立索引关注的是区分度,只有区分度越高,重复值就越少,查询效率就越高。


所以使用前缀索引,只要定义好长度,就可以坐到既节省空间,又不用额外增加太多的查询代价。


二、创建索引如何确定使用多长的前缀


MySQL 中关键词distinct可以返回本列不同的结果集。


例如查询 email 列有多少个不同的值select count(distinct email) as num from mac_user。


如何计算列不同前缀有多少行


结合 MySQL 自带的函数 left 来实现,例如select count(distinct left (email,4)) as num4 from mac_user,截取 email 的前四个字符串计算有多少行。


再用这个值去除总数得到的就是比例,根据业务情况来判断多少比例可以。


三、使用前缀索引的影响


使用前缀索引会增加扫描行数,同时也会使覆盖索引失效。


为什么会影响覆盖索引?


若执行语句为select id,email from mac_user where email = '1397393964@qq.com'


使用整个字符串索引结构查询可以使用覆盖索引,从 email 索引获取到结果就直接返回了,不用再进行回表。


若使用前缀索引在 email 索引获取到结果后还需要回到 id 索引在查一下判断查询的 email 的值是否正确。


哪怕是设置了大于了 email 的长度也会回表再进行判断,因为 MySQL 并不知道定义的前缀是否截取了完整信息。


结论


使用前缀索引会增加扫描行数,同样也使用不到覆盖索引。这个因素是你选择是否使用前缀索引要考虑的一个因素。


如果你不知道使用前缀索引还是全字符串索引,本地进行测试选一个合适的方案上到生产环境即可。


四、如何把不可以变为可以使用


假设身份认证系统存储的是身份证号,应该都知道身份证号前 6 位是地址码,同县的身份证号前 6 位一般是一样的。


这样使用前缀索引的话区分度会十分低,不但没有起到加速查询的作用,反而会造成索引区分度不大影响查询性能。


若把索引长度越长则每个节点存放的索引值就越少,查询效率也会变的低效。


如果解决这种场景


第一种方案


存储数据时将数据倒叙存储,查询时在正序处理一下即可


第二种方案


在表中新增一个字段,存储数据的 hash 值,给 hash 添加前缀索引。


区别


使用这两种方案共同点都不支持范围查询,都只能等值查询。


从占用空间来看:倒叙方式不会增加额外的存储空间,hash 会增加一个字段。两者在空间不相上下


从 CPU 消耗来看:倒叙需要使用函数 reverse,hash 需要使用 crc32 ,reverse 消耗会小


从查询效率来看:hash 查询更稳定,crc32 计算的值虽有冲突但概率非常小,基本每次查询的平均扫描行数接近 1。而倒叙使用的前缀索引方式,还会增加扫描行数。


五、总结


直接给字符串创建占用空间。


创建前缀索引,节省空间,会增加扫描行数,无法使用覆盖索引。


倒叙存储,创建前缀索引解决区分度不大的问题。


使用 hash 方式,查询稳定,不支持范围查询。


坚持学习、坚持写作、坚持分享是咔咔从业以来所秉持的信念。愿文章在偌大的互联网上能给你带来一点帮助,我是咔咔,下期见

发布于: 16 小时前阅读数: 6
用户头像

咔咔

关注

还未添加个人签名 2021.04.08 加入

还未添加个人简介

评论

发布
暂无评论
字符串可以这样加索引,你知吗?《死磕MySQL系列 七》