mysql怎么给字符串字段加索引?

1. 怎么给字符串字段加索引?

  1. 案例:给邮箱加索引

    -- 普通索引,包含了每个记录的整个字符串;
    alter table User add index index1(email);    
    -- 前缀索引,对于每个记录都是只取前 6 个字节
    ALTER TABLE User ADD INDEX index2(email(6)); 
    
  2. 前缀索引:

    1. 优点:占用空间比较小
    2. 缺点:会增加额外的扫描次数
  3. 执行以下SQL

       select id,name,email from SUser where email='zhangssxyz@xxx.com';
    
    • 使用index1:
      ​ 1. 从index1索引树上寻找满足条件的索引值 zhangssxyz@xxx.com的记录主键为ID2
      ​ 2. 到主键上去找主键值为ID2的行,判断email值是正确的,将这条记录加入索引值
      ​ 3. 取index1索引树上刚刚查的的下一条记录,发现不满足条件,循环结束
      ​ 4. 这个过程只向主键取了一次数据,所以系统认为只扫描了一行
    • 使用index2:
      1. 从index2索引树上找满足索引值zhangs的记录,找到的第一个是ID1
        1. 到主键找主键值ID1,判断email,如果不是则丢弃,如果是则加入结果集
          1. 重复上一步i,知道idnex2上取到的值不是 zhangs时,循环结束
          2. 这个过程,向主键取了4次数据,扫描了4行
  4. 根据实际效果,使用前缀索引,要定义和长度,就可以做到既节省空间,有不用额外增多太多的查询成本

    1. 怎么才能确定我该使用多长的前缀呢?

    2. 关注区分度,区分度越高,意味着重复的值越少,索引要统计带建立的索引上有多少不同的值,建议使用以下SQL取统计一下有多少个不同的值

    3. 使用前缀索引会损伤区分度,一般选择不小于95%的比例

      select count(distinct email) as L from SUser;
      -- 统计4-7个字节的前缀索引
      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(eamil,7)) as L7
      from SUer;
      

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

    1. 使用前缀索引就用不上覆盖索引对查询性能的优化了,这个也是在选择是否使用前缀索引时需要考虑的一个因素】、

    3. 区分度不大,怎么利用前缀索引?

    1. 例如身份证号?

    2. 使用倒序存储

      -- 一般身份证号后6位不会重复
      select field_list from t where id_card = reverse('input_id_card_string')
      
    3. 使用hash字段

      --在表上创建一个整数字段,保存身份证号的校验码,同时在这个字段上创建索引
      alter table t add id_card_crc int unsigned, add index (id_card_crc)
      -- 因为crc32()函数,得到的结果可能重复,所以需要增加判断
      select field_list from t where id_card_crc = crc32('input_id_card_string') and id_card='input_id_card_string'
      
    4. 这两种手段都不支持范围查询,

      • 占用空间:差不多,hash新增4个字节占用的空间,而倒序存储使用4个字节的前缀长度应该不够,相互抵消
      • cpu消耗: reverse相比较crc32()消耗资源小一些
      • 查询效率 : hash比较好,扫描结果解决1行
  • 0
    点赞
  • 0
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值