长字符串如何做索引长字段索引

转载

风华正茂的AI 2024-02-29 22:45:52

1.前缀索引和普通索引的区别

　　当系统中有一个长的字符串需要创建索引的时候，常见的有两种方式，一个创建普通索引，全字段创建；另外一种就是创建前缀索引；那么这两种索引有什么区别呢？

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

　　select id,name,email from SUser where email='zhangssxyz@xxx.com';

　　普通索引查询的过程：

在index1索引的内存页查询到该key,去主键索引中找出改行数据比对email字段成功后，把查询的数据放入结果集；
比对刚才查询index1索引树的位置的一个位置，查看不符合条件直接返回；
此过程只经过了一次回表操作

　　前缀索引查询的过程：

在index2索引的内存页查询到该key,去主键索引中找出改行数据比对email字段发现匹配不上；
在index2的索引树中查询刚才位置的下一个key,重复上述步骤，直到全部key搜索完成；
在此过程中可能进行多次回表操作；

　　由此可见前缀索引虽然在存储索引方面占用了更少的空间，减少了索引树中的io次数，但是也会增加回表次数。

　　如果把索引长度变成7：alter table SUser add index index2(email(7)); 回表次数和普通索引就相差无几了；

　　综上所述，在创建前缀索引的时候索引的长度是关键。

2.如何确定前缀索引的长度

　　关系到前缀索引长度的选择的一个重要参数是：索引的区分度，区分度越高，键值的重复度越少，由于长度引起的回表就会越少；不过区分度越高，索引长度越长，越占用空间，所以需要根据业务衡量可以接受的损失比例m%,当 distinct(left(key))/distinct(key)>m%的基础上长度越小越好；

3.前缀索引对覆盖索引的影响

　　即使sql中用到了覆盖索引，因为是使用的前缀索引，拿不到index的完整数据，还是需要回表获取完整的数据，索引前缀索引是无法使用索引覆盖的；

4.当前缀索引区分度低的时候，采取倒叙索引或者hash

　　当对身份证这种前半部分区分度小的字符串创建索引时，推荐使用倒叙创建前缀索引；

　　或者增加一个hash(cardId)的字段，用 crc32()函数计算数值，创建索引，查询的时候也倒叙查询，因为 crc32()这个函数可能把不同的身份证号计算出相同的值，所以产寻得时候需要判定index的值。

5.倒叙索引和hash索引的区别