发表于 | 分类于 | 阅读次数 593

请添加图片描述

11 | 怎么给字符串加索引？

要解决的问题：支持邮箱登录的情况下，如何给邮箱加索引？

    1. 前缀索引
    • alter table SUser add index index2(email(6))
    • email(6) 这个索引结构中每个邮箱字段都只取前 6 个字节（即：zhangs），所以占用的空间会更小，这就是使用前缀索引的优势
    • 可能会增加额外的记录扫描次数
    • 使用前缀索引，定义好长度，就可以做到既节省空间，又不用额外增加太多的查询成本
    1. 区分度不够时用不了前缀索引，可试试倒叙存储
    • 记得用count（distinct）验证下区分度
    1. 使用hash字段
    • 表上再创建一个字段，来保存身份证的校验码（使用函数crc32()），同时在此字段上设置索引。 ===》转移查询目标
    1. 直接创建完整索引（占用空间大）

倒叙存储和使用hash字段的异同点

    1. 占用的额外空间
    • 倒叙存储在主键索引上，没有消耗额外空间
    • hash需要额外增加一个字段
    1. CPU消耗
    • 倒叙每次读和写要调用reverse，占用资源更少
    • hash需额外调用crc32()
    1. 查询效率
    • 倒叙用的还是前缀索引的方式，还是会增加扫描行数
    • 而hash虽然有冲突，但概率小，扫描行数接近1

前缀索引（类似模糊查询）

  • 弊端

      1. 增加扫描行数
      1. 有可能用不上覆盖索引对查询性能的优化
      1. 会损失区分度，可预先设定可接受的损失比例

  • 创建索引的语句不指定前缀长度，默认包含整个字符串

  • 定义好前缀长度可以节省空间和查询成本

  • 如何确定前缀长度？

    • 区分度：区分度越高越好（重复的键值越少），根据统计索引上不同的值的数量来确定前缀长度。

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

使用前缀索引就用不上覆盖索引对查询性能的优化了

即使你将 index2 的定义修改为 email(18) 的前缀索引，这时候虽然 index2 已经包含了所
有的信息，但 InnoDB 还是要回到 id 索引再查一下，因为系统并不确定前缀索引的定义是
否截断了完整信息。

使用email（6），不得不回到主键索引树去判断email字段的值，因此覆盖索引被浪费了

举个栗子