MySQL中常用于检索的字符串字段如何创建索引？

男娘i 2023-07-23 13:56 70阅读 0赞

# 背景 #

我们在业务场景中经常会碰到通过某个字符串查询对应记录的情况。比如常见的邮箱登录、或是手机号登录。  
如果不给它创建索引，则MySQL就会进行全局扫描，非常耗时。那么，类似邮箱地址这样的字符串，我们应该如何给它创建索引呢？这里简单介绍几种方法。

# 几种方法 #

比如我们有一张表user\_info用于存储用户登录信息，包括自增主键ID、邮箱地址、对应密码。  
这里我们经常会用到的SQL操作是`select email, password from user_info where email="XXXXXXX"`

### 直接创建索引 ###

第一种方法当然是直接使用email来创建索引。这种方法虽然简单粗暴且会占用大量的存储空间，但是有一个好处，就是回表操作只需一次就行。

### 使用前缀索引 ###

所谓前缀索引，比如前面的示例，就是将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位去索引查找，查找得到则会回表到主键树中获取到对应的记录。

使用前缀索引，一定要权衡好前缀选取的长度，因为前缀的长度就意味着区分度。如果前缀索引可以完全区分，那当然是最好的，这样只需要回表一次就可以拿到数据了。区分度越高越好，区分度越高，也就使得回表操作次数越少。

只要前缀长度取的合适，就可以既有满意的区分度，又有满意的存储空间占用。

不过使用前缀索引也有一个劣势，在于使用前缀索引会影响到覆盖索引的使用。

### 倒序存储 ###

倒序存储即是将对应字段倒序过来再取前缀创建索引。针对于前缀区分度低，但后缀区分度高时使用。

比如我们需要存储一个user\_info用户信息表，存储的是自增ID、身份证号、密码。身份证号就属于前缀区分度低（前缀都是一些通用代号），但后缀区分度高的字符串。

在存储以及查询时也需要将身份证号倒过来使用。比如，在查询时，使用语句：`select user_id, password from user_info where user_id=reverse(XXXXXX)`

### hash存储 ###

将对应字段hash之后作为索引存储，hash之后冲突的概率较低，也可大大降低回表的次数。只是由于需要存储字段hash之后的数据，会增加额外存储空间的占用。

### 总结 ###

前面简单介绍了一下几种不同的对字符串创建索引的方法。  
总结如下：

<table> 
 <thead> 
  <tr> 
   <th>索引方式</th> 
   <th>优势</th> 
   <th>劣势</th> 
   <th>适用场景</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td>直接创建索引</td> 
   <td>回表次数少</td> 
   <td>索引空间占用大，且每次字符串匹配时间较长</td> 
   <td>数据量不大或字符串不长的情况</td> 
  </tr> 
  <tr> 
   <td>前缀索引</td> 
   <td>索引空间占用小，且每次字符串匹配时间较短</td> 
   <td>可能增加回表次数</td> 
   <td>选取出的前缀可以有较高的区分度</td> 
  </tr> 
  <tr> 
   <td>倒序存储</td> 
   <td>无需添加新字段</td> 
   <td>只能支持单条查询，不支持范围查询</td> 
   <td>前缀区分度低，后缀区分度高</td> 
  </tr> 
  <tr> 
   <td>hash存储</td> 
   <td>冲突概率低，得出的索引区分度较高</td> 
   <td>只能支持单条查询，不支持范围查询，且需要增加新的存储字段</td> 
   <td>无需范围查询且普适性较高</td> 
  </tr> 
 </tbody> 
</table>