字符串建立索引的优化
1. 建立前缀索引
假设建立一个支持邮箱登录的用户表, 对于邮件字段来说, 可以有以下几种建立索引的方式:
直接对整个字符串建立索引
alter table SUser add index index1(email);
对整个字符串的前一部分建立索引 - 前缀索引
alter table SUser add index index2(email(6));
方式 2 相较于 方式 1 来说, 利用前缀索引, 占用的空间更小. 但有可能造成性能的损失, 读取数据的次数变多.
假设在 user 表中存在 zhangsp1234@gmail.com, zhangs1pzxyz@qq.com , zhangssxyz@xxx.com, 三条记录.
有这样一条语句 select id,name,email from SUser where email='zhangssxyz@xxx.com';
使用 index1 索引时, 流程如下:
在 index1 中, 找到名字是 zhangssxyz@xxx.com 的记录, 获取 ID.
在主键索引上对应 ID 的行, 判断 email 是否正确, 将记录加入结果集.
接着取 index1 索引的下一条记录, 发现不满足 email 格式, 结束循环.
使用 index2 索引:
在 index2 中, 找到名字是 zhangs 的记录, 获取 ID.
在主键索引上对应 ID 的行, 这时拿到的是
zhangss1234@gmail.com
的行, 发现不符合, 丢弃.
接着在 index2 循环, 拿到下一条记录 ID.
在主键索引上对应 ID 的行, 这时拿到的是
zhangsspzxyz@qq.com
的行, 发现不符合, 丢弃.
接着在 index2 循环, 拿到下一条记录 ID.
在主键索引上对应 ID 的行, 这时拿到的是 zhangssxyz@xxx.com 的行, 发现符合, 纳入结果集.
接着在 index2 循环, 发现记录格式不符合, 结束循环.
看这个过程, 很容易发现, 前缀索引会增加查询语句读取数据的次数.
但如果将前缀索引的 email(6) 改成 email(7), 就会减少查询的次数, 对应在主键索引上只搜索一次. 这就说明, 如果能合适的设置前缀索引的长度, 就能在空间和效率上取得平衡.
如何找到合适的前缀索引长度
在建立索引时, 应该去关注区分度, 区分度越高, 则说明重复的键值越少.
可以通过执行查询来统计列上有多少不同的值.
- MySQL> select
- count(distinct email)as L,
- 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%, 用 L 的数量 * 区分度比例(1-5%=95%), 然后看在 L4 到 L7 中哪个满足.
前缀索引的影响
在之前覆盖索引的文章中, 如果查询的列的信息被包含在二级索引上, 那么就可以避免回表的过程, 进而减少查询次数, 提供效率. 但如果在建立索引时, 使用了前缀索引, 那么无论满不满足覆盖索引的规则, 都会回表. 因为系统不能确定前缀索引是否截取了完成信息, 进而必须做一次判断.
也就是说, 前缀索引除了会增加查询语句的次数, 还会禁止使用覆盖索引.
2. 倒序存储
对于邮箱这类的字符串来说, 由于前几位有较大的区分度, 所以用前缀索引还不错. 但如果是区分度不好的情况, 比如身份证, 前 6 位都是地址码, 很多人都会一样. 这时如果想要使用前缀索引, 就需要至少 12 位以上, 对应查询效率和空间都不是很合适.
一个比较好的办法是将字符串倒序存储, 将区分度高的字符开头.
例如:
- MySQL> select field_list from t \
- where id_card = reverse('input_id_card_string');
3. 使用 hash 字段
在网络传输时, CRC - 循环冗余校验被用于检验文件. 对应在 MySQL 里也有这个函数, crc32().
该函数的返回范围是 0-4294967296 也就是 4 字节, 相对于其他字符串来说, 属于较短的长度.
在创建表时, 可再创建一个整数字段, 来保存这类字符串, 如身份证的校验码 (crc32() 的返回值), 并为该字段创建索引.
如:
- MySQL> alter table t add id_card_crc int unsigned, \
- add index(id_card_crc);
在插入记录时, 将 crc32() 的结果插入到记录中.
但由于 crc32() 只有 32 位的特性, 容易发生 hash 碰撞, 就是说可能两个字符串经过计算后得到相同的验证码. 这时就存在冲突, 所以还需要判断下查询的值是否一致.
如:
- MySQL> select field_list from t where \
- id_card_crc=crc32('input_id_card_string') and \
- id_card='input_id_card_string'
总结
我们知道, MySQL 中使用的是 B+ 树来存储索引的, 这自然就是有序的, 所以前缀查询就支持范围查询.
而 Hash 字段和倒序查询两种方式就不行了, 倒序查询是按照倒序字符串存储的, 而 hash 字段和字符串本身也没有关系, 这就意味着这两种方式是不支持范围查询的.
在占用空间上来说, 倒序存储占用的是和普通索引的一样的空间. 而 hash 字段, 需要增加一个字段来存在 hash 校验码.
在 CPU 消耗, 倒序时, 每次读和写都需要调用 reverse 函数. hash 方式需要额外调用 crc32() 函数. 两个函数实现来看, reverse 函数 CPU 消耗会少些.
在查询效率上, hash 字段查询性能更好稳定些. 虽然可能存在冲突的情况, 但概率很小. 而倒序存储还是用前缀索引的方式, 会额外增加扫描行数.
总结一下, 一般提高查询字符串的效率有如下方式:
直接创建完成索引, 但占用空间较大.
创建前缀索引, 节省空间, 但会增加扫描次数, 不能利用覆盖索引.
倒序存储, 再创建前缀索引, 节省空间, 增加扫描次数, 不能利用覆盖索引.
hash 字段, 性能稳定, 但占用额外的空间, 不支持范围查询.
来源: https://www.cnblogs.com/michael9/p/13219915.html