Scott's world.

MySQL-选取字符串索引笔记

Word count: 1.7kReading time: 6 min
2019/11/22 Share

MySQL-选取字符串索引笔记

假如现在我们要在数据库中查询邮箱号这种具有标识型的字符串数据

我们在前面知道若不加索引则这个查询语句就会进行全表扫描

所以下面我们就来看一下如何给字符串添加索引,提高查询效率

前缀索引

MySQL支持前缀索引,即可以定义字符串的一部分作为索引,若创建索引不指定字符串长度就默认为整个字符串的长度

而指定了长度和没有指定长度的区别就在于指定长度的索引占用的空间会更小

但是带来的损失就可能会增加额外的记录扫描次数

假如现在我们进行一个查询语句

1
select id,name,email from SUser where email='zhangssxyz@xxx.com';
  • 使用整个字符串的索引结构记为index1
    1. 从index1索引树上找到满足索引值为zhangssxyz@xxx.com的这条记录,取得ID2的值
    2. 到主键上查到主键值为ID2的行,判断email的值是正确的,将这行记录加入结果集
    3. 回到index1索引树上刚刚查到的位置的下一条记录,发现已经不满足查询条件,则循环结束
  • 使用指定前缀即email(6)的索引结构记为index2
    1. 从index2索引树找到索引值为zhangs的记录,找到的第一个是ID1
    2. 到主键上查到主键值为ID1的行,判断出email的值不是zhangssxyz@xxx.com,这行记录丢弃
    3. 回到index2索引树上刚刚查到的位置的下一条记录,发现仍然是zhangs,取出ID2,再回到主键索引树上取整行进行判断,这次值对了,将这行加入结果集
    4. 重复上一步,直至index2上取的值为zhangs为止

但其实如果我们使用前缀索引,定义好长度,就可以做到既节省空间,又不用额外增加太多的查询成本。

在选择指定索引的长度时,实际上是根据字符串的区分度,区分度越高则代表重复的键值越少.因此可根据统计索引上有多少个不同的值来判断要使用多长的前缀

判断索引值上多少个不同的值时

可使用下列语句

1
2
3
4
5
6
7
8
9
10
> mysql> select count(distinct email) as L from SUser;
>
> ##然后再依次选取不同长度的前缀来看这个值,比如我们要看一下4~7个字节的前缀索引,可以使用下列语句
> 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;
>

>

使用前缀索引很可能会损失区分度,所以你需要预先设定一个可以接受的损失比例,比如5%。然后,在返回的L4~L7中,找出不小于 L * 95%的值,假设这里L6、L7都满足,你就可以选择前缀长度为6。

覆盖索引的影响

若我们需要查询索引上的数据,如idemail

相比使用前面所说的index1即整个字符串的索引结构的话,就可以利用覆盖索引,从index1索引树上查询到结果就直接返回了,不用再回到主键的索引树上再查询一次,而使用index2即指定了字符串的索引结构的话即使你指定长度为字符串的长度,那么在index2索引树上查询到结构后依然会回到ID索引树上判断email的值

系统并不确定前缀索引的定义是否截断了完整信息

也就是说,使用前缀索引就用不上覆盖索引对查询性能的优化

其他方式

除了前面的提到的前缀索引,但我们有很多情况可以看到前缀的区分度并不是很好的情况,并且索引指定的长度越大则占用的磁盘空间就越大,相同的数据页能放下的索引值就越少,搜索的效率也会越低,所以我们还有其他的方式来给字符串加上索引,提高查询效率

倒序存储

存储字符串数据的时候把它倒过来存,每次查询的时候,此时指定的前缀也就相当于后缀查询

比如下面这条sql语句

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

实践中你不要忘记使用count(distinct)方法去做个验证。

hash字段

在表上创建一个额外的整数字段来保存字符串的数据的标识码,同时在这个字段上创建索引

比如下面这条语句

1
mysql> alter table t add id_card_crc int unsigned, add index(id_card_crc);

然后每次插入新记录的时候,都同时用crc32()这个函数得到校验码填到这个新字段。

由于校验码可能存在冲突,也就是说两个不同的身份证号通过crc32()函数得到的结果可能是相同的,所以你的查询语句where部分要判断id_card的值是否精确相同。

1
2
> mysql> select field_list from t where id_card_crc=crc32('input_id_card_string') and id_card='input_id_card_string'
>

倒序存储和hash字段的异同点

  • 相同点

    不支持范围查询

  • 不同点

    • 从占用的额外空间来看,倒序存储方式在主键索引上,不会消耗额外的存储空间,而hash字段方法需要增加一个字段。当然,倒序存储方式使用4个字节的前缀长度应该是不够的,如果再长一点,这个消耗跟额外这个hash字段也差不多抵消了。

    • 在CPU消耗方面,倒序方式每次写和读的时候,都需要额外调用一次reverse函数,而hash字段的方式需要额外调用一次crc32()函数。如果只从这两个函数的计算复杂度来看的话,reverse函数额外消耗的CPU资源会更小些。

    • 从查询效率上看,使用hash字段方式的查询性能相对更稳定一些。因为crc32算出来的值虽然有冲突的概率,但是概率非常小,可以认为每次查询的平均扫描行数接近1。而倒序存储方式毕竟还是用的前缀索引的方式,也就是说还是会增加扫描行数。

CATALOG
  1. 1. MySQL-选取字符串索引笔记
    1. 1.1. 前缀索引
      1. 1.1.1. 覆盖索引的影响
    2. 1.2. 其他方式
      1. 1.2.1. 倒序存储
      2. 1.2.2. hash字段
      3. 1.2.3. 倒序存储和hash字段的异同点