mysql 状态列加索引 mysql字段加索引

转载

mob64ca14038b36 2023-10-08 18:48:13

文章标签 mysql 状态列加索引字段 mysql 数据 文章分类 MySQL 数据库

例如字段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'

两种方法的异同点：

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

不同点：