创业网站怎么做,广州网站seo推广,wordpress 4.7 多站点,企业网站做多大对于长字符串#xff0c;可用如下方式建立索引#xff1a; #xff08;1#xff09;前缀索引 #xff08;2#xff09;字符串倒叙前缀索引 #xff08;3#xff09;添加hash字段并在hash字段上加索引 #xff08;4#xff09;字段拆分#xff08;一个字段可拆分为两… 对于长字符串可用如下方式建立索引 1前缀索引 2字符串倒叙前缀索引 3添加hash字段并在hash字段上加索引 4字段拆分一个字段可拆分为两个以上 假设现在表User 中存在email字段为长字符串。如果email字段上没有索引那么这个语句只能做全表扫描
select f1,f2 from User where email 1540984562qq.com;在email字段上创建索引 –创建的index1索引包含整个字符串
alter table User add index index1(email);–创建的index2索引只包含字符串前6个字节
alter table User add index index2(email(6));前缀索引占用的空间更小但是会增加扫描的次数。 使用前缀索引定义好长度就可以做到既节省空间又不额外增加太多查询成本。 在建立索引时关注区分度可区分度越高意味着重复的键值越少
可以依次选取不同长度的前缀来看这个值比如看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(email,7)) as L7,
from User;前缀索引对覆盖索引的影响
如果我们的语句是 select id,email from User where email ‘1540984562qq.com’; 这个语句只要求返回id和email字段。 如果使用的整个字符串作为索引会触发覆盖索引不需要回表ID索引再查询。 而如果使用的是前缀索引就还得回表ID索引去获取email字段的完整值
所以如果使用了前缀索引就相当于放弃掉了覆盖索引对查询性能的优化了。
使用 倒序 与 hash 构造新字段
1、倒序 如果我们对身份证号码进行前缀索引提取会发现前面的号码重复率极高。 此时可以使用倒序存储 select field_list from t where id_card reverse(‘input_id_card_string’); 由于身份证最后几位的重复逻辑较小所以最后6位很可能提供了足够的区分度 2、hash字段 在表上再创建一个整数字段来保存身份证的校验码同时在这个字段上创建索引。
alter table t add id_card_crc int unsigned,add index(id_card_crc);然后每次插入新纪录的时候都同时用crc32()这个函数得到校验码填到这个新字段。 由于校验码可能存在冲突也就是说两个不同的身份证号通过crc32()函数得到的结果可能相同 所以查询语句还要判断id_card是否精确相同。
select field_list from t where id_card_crc crc32(input_id_card_string) and id_card input_id_card_string;两者异同点 相同 都不支持范围查询只支持等值查询 不同 1、占用额外空间不同 倒序存储不会消耗额外的存储空间。hash需要增加一个字段。 若倒序存储使用4个字节作为前缀不够也会消耗额外字段 2、CPU消耗方面 倒序每次都要调用reverse函数 hash需要调用crc32函数。reverse的时间复杂度会更小 3、查询效率 hash字段查询性能相对更稳定发生冲突概率很小可以认为每次查询平均扫描行数为1. 倒序存储方式仍然使用的是前缀索引方式还会增加扫描行数
总结
