MySQL-11：怎么给字符串字段加索引

叁歲伎倆 2022-12-24 11:52 173阅读 0赞

例如字段email，如果没有在email字段上加索引，就会走全表扫描。

在email字段上创建索引的方式：

mysql> alter table SUser add index index1(email);
        或
        mysql> alter table SUser add index index2(email(6));

**第一种：** 索引包含整个email字符串；**第二种：** 索引只包含email的前6个字节，这个是基于MySQL支持前缀索引。

**l两种方式存储结构上的区别：** 第二种由于只取前6个字节，索引占用的空间会比较小，但同时可能会增加扫描表的次数，这都是因为数据的区分度问题，例如 abb，abc，abcd这三个数据，我们用ab去匹配，都是匹配的。

所以，我们只要把控住数据的区分度问题，就可以达到占用空间小，又不用增加太多查询次数。

我们可以使用distinct关键字来查看字段在不同字节长度的时候有多少不同的情况。

**例如：**

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;

在使用前缀索引可能会顺势数据区分度，我们可以预设一个可接受的区分度损失比例，选择合适的前缀长度。

### 前缀索引对覆盖索引的影响 ###

在使用前缀索引下，InnoDB引擎不知道当前索引是否已经包含了字段的全部信息，都会去主键索引确认一下情况，这样就会损失覆盖索引对性能的优化，就算你使用了字段的全部字节，例如：email的字节长为16，alter table SUser add index index2(email(16))。

### 其他情况 ###

邮箱在使用前缀索引的效果还是不错的。

**但是待优化的字段位身份证号呢？**

我们的身份证一共18位，前6位是地址码，我们需要选取12以上的前缀才能与区分度，索引越长，占用的空间也就越大，会导致存储的数据减少，性能降低，**但我们可以用以下两种方法去尝试：**

**1. 倒序存储**

我们使用MySQL的reserve()函数来将字符串反转存储。

mysql> select field_list from t where id_card = reverse('input_id_card_string');

**2. 哈希存储**

可以采用crc32()函数来得到一个哈希值，再在数据表中加入一个字段，来存储这个哈希值。

mysql> alter table t add id_card_crc int unsigned, add index(id_card_crc);
    
    mysql> select field_list from t where id_card_crc=crc32('input_id_card_string') and id_card='input_id_card_string'

**两种方法的异同点：**

**相同点：** 不接受范围查询，只接受等值查询。

**不同点：**

1.  Hash字段的方法会增加额外的占用空间
2.  crc32()函数消耗的性能比reserve()函数多
3.  Hash字段冲突可能性小，分布广，倒序存储的方式可能还是会增加扫描行数。

**练习问题：**

1.  如何给字段串加索引？不同索引方式在存储结构上有什么不同？
2.  在执行查询操作时，执行顺序是怎样的？
3.  如何去查看数据区分度的问题？如何去定义前缀长度？
4.  前缀索引对覆盖索引的引发的问题？
5.  倒序存储和Hash字段方式怎样实现？有哪些异同点？