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

例如字段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字段方式怎样实现?有哪些异同点?
已标记关键词 清除标记
©️2020 CSDN 皮肤主题: 书香水墨 设计师:CSDN官方博客 返回首页