Mysql - 字符串字段如何创建索引
背景
我们在业务场景中经常会碰到通过某个字符串查询对应记录的情况。比如常见的邮箱登录、手机号登录。
如果不给它创建索引,则MySQL就会进行全局扫描,非常耗时。
那类似邮箱地址这样的字符串,我们应该如何给它创建索引呢?
这里简单介绍几种方法。
几种方法
比如我们有一张表user_info用于存储用户登录信息,包括自增主键ID、邮箱地址、对应密码。
这里经常会用到的SQL操作是select email, password from user_info where email="XXXXXXX"
1、直接创建索引---适用于【数据量不大或字符串不长】
直接使用email来创建索引。这种方法虽然简单粗暴且会占用大量的存储空间,但好处是回表操作只需一次就行。
2、使用前缀索引---适用于【前缀区分度高】
所谓前缀索引,比如前面的示例,就是将email字段的前缀截取下来,然后作为索引来使用。
添加索引的SQL语句为:alter table user_info add index index_eamil(email(9)),这里就添加了一个email字段前9位字符为前缀索引。
假如email字段的前9位字符就可以唯一的标示一个email地址,比如前9位是不同的数字,后面都是@qq.com。那么这样就做到了完全的区分度。在选择某email时,比如select email, password from user_info where email="123456789@qq.com",MySQL会拿前9位去索引查找,查找得到则会回表到主键树中获取到对应的记录。
使用前缀索引,一定要权衡好前缀选取的长度,因为前缀的长度就意味着区分度。如果前缀索引可以完全区分,那当然是最好的,这样只需要回表一次就可以拿到数据了。区分度越高越好,区分度越高,也就使得回表操作次数越少。
只要前缀长度取的合适,就可以既有满意的区分度,又有满意的存储空间占用。
不过使用前缀索引也有一个劣势,在于使用前缀索引会影响到覆盖索引的使用。
3、倒序存储---适用于【前缀区分度低,后缀区分度高】
倒序存储即是将对应字段倒序过来再取前缀创建索引。针对于前缀区分度低,但后缀区分度高时使用。
比如我们需要存储一个user_info用户信息表,存储的是自增ID、身份证号、密码。身份证号就属于前缀区分度低(前缀都是一些通用代号),但后缀区分度高的字符串。
在存储以及查询时也需要将身份证号倒过来使用。
比如,在查询时使用语句:select user_id, password from user_info where user_id=reverse(XXXXXX)
4、hash存储---适用于【无需范围查询且普适性较高】
将对应字段hash之后作为索引存储,hash之后冲突的概率较低,也可大大降低回表的次数。只是由于需要存储字段hash之后的数据(新增一个字段),会增加额外存储空间的占用。
总结
【推荐】国内首个AI IDE,深度理解中文开发场景,立即下载体验Trae
【推荐】编程新体验,更懂你的AI,立即体验豆包MarsCode编程助手
【推荐】抖音旗下AI助手豆包,你的智能百科全书,全免费不限次数
【推荐】轻量又高性能的 SSH 工具 IShell:AI 加持,快人一步
· 全程不用写代码,我用AI程序员写了一个飞机大战
· MongoDB 8.0这个新功能碉堡了,比商业数据库还牛
· 记一次.NET内存居高不下排查解决与启示
· 白话解读 Dapr 1.15:你的「微服务管家」又秀新绝活了
· DeepSeek 开源周回顾「GitHub 热点速览」