@TOC

系列文章

三、MySQL 强人“锁”难《死磕 MySQL 系列 三》

四、S 锁与 X 锁的爱恨情仇《死磕 MySQL 系列 四》

五、如何抉择一般索引和惟一索引《死磕 MySQL 系列 五》

六、五分钟，让你明确 MySQL 是怎么抉择索引《死磕 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 形式，查问稳固，不反对范畴查问。

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