1. 怎么给字符串字段加索引?
-
案例:给邮箱加索引
-- 普通索引,包含了每个记录的整个字符串; alter table User add index index1(email); -- 前缀索引,对于每个记录都是只取前 6 个字节 ALTER TABLE User ADD INDEX index2(email(6));
-
前缀索引:
- 优点:占用空间比较小
- 缺点:会增加额外的扫描次数
-
执行以下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:
- 从index2索引树上找满足索引值
zhangs
的记录,找到的第一个是ID1- 到主键找主键值ID1,判断email,如果不是则丢弃,如果是则加入结果集
- 重复上一步i,知道
idnex2
上取到的值不是zhangs
时,循环结束 - 这个过程,向主键取了4次数据,扫描了4行
- 重复上一步i,知道
- 到主键找主键值ID1,判断email,如果不是则丢弃,如果是则加入结果集
- 从index2索引树上找满足索引值
- 使用index1:
-
根据实际效果,使用前缀索引,要定义和长度,就可以做到既节省空间,有不用额外增多太多的查询成本
-
怎么才能确定我该使用多长的前缀呢?
-
关注区分度,区分度越高,意味着重复的值越少,索引要统计带建立的索引上有多少不同的值,建议使用以下
SQL
取统计一下有多少个不同的值 -
使用前缀索引会损伤区分度,一般选择不小于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. 前缀索引对覆盖索引的影响
- 使用前缀索引就用不上覆盖索引对查询性能的优化了,这个也是在选择是否使用前缀索引时需要考虑的一个因素】、
3. 区分度不大,怎么利用前缀索引?
-
例如身份证号?
-
使用倒序存储
-- 一般身份证号后6位不会重复 select field_list from t where id_card = reverse('input_id_card_string')
-
使用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'
-
这两种手段都不支持范围查询,
- 占用空间:差不多,hash新增4个字节占用的空间,而倒序存储使用4个字节的前缀长度应该不够,相互抵消
- cpu消耗: reverse相比较crc32()消耗资源小一些
- 查询效率 : hash比较好,扫描结果解决1行
-