CREATE TABLE SUser(
id BIGINT UNSIGNED PRIMARY KEY,
name VARCHAR(64),
email VARCHAR(64)
) ENGINE=InnoDB;
查询
SELECT id,name,email FROM SUser WHERE email='zhangssxyz@xxx.com';
创建索引
ALTER TABLE SUser ADD INDEX index1(email);
ALTER TABLE SUser ADD INDEX index2(email(6));
index1
索引长度: 整个字符串
从index1索引树找到第一个满足索引值为 zhangssxyz@xxx.com 的记录,取得主键为ID2
到 聚簇索引 上查找值为ID2的行,判断email的值是否正确( Server层行为 ),将该行记录加入结果集
获取index1上的下一条记录,发现不满足 email=zhangssxyz@xxx.com ,循环结束
整个过程,只需要 回表 1次,系统认为只扫描了1行
index2
索引长度: 前6个字节
索引占用的 空间更小 ,增加额外的记录 扫描次数 ,(且不支持 覆盖索引 ,见后面)
从index2索引树找到第一个满足索引值为 zhangs 的记录,取得主键为ID1
到聚簇索引上查找值为ID1的行, email!=zhangssxyz@xxx.com ( Server层行为 ),记录 丢弃
获取index2上的下一条记录,发现仍然是 zhangs ,取得主键为ID2
重复上面的步骤,直到index2上取得的值不为 zhangs 为止
整个过程,需要 回表 4次,系统认为扫描了4行
假设index2为 email(7) ,满足前缀 zhangss 只有一个,只需要回表一次
使用前缀索引,如果能 定义好长度 ,即能 节省空间 ,又 不会增加太多的查询成本
前缀索引的长度
原则: 区分度 。使用前缀索引一般都会 损失区分度 ,预设一个 可接受的损失比例 ,在该损失比例内,寻找 最短 前缀长度
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;
前缀索引与覆盖索引
SELECT id,email FROM SUser WHERE email='zhangssxyz@xxx.com';
如果使用 index1 ,可以利用 覆盖索引 , 不需要回表
如果使用 index2 ,就 必须回表 ,获得整行记录后再去判断email字段的值
即使index2为 email(18) (包含了所有信息),还是需要回表
因为系统 不确定前缀索引的定义是否截断了完整信息
因此, 前缀索引是用不上覆盖索引对查询性能的优化
其他手段
场景:前缀的区分度非常差,例如居民身份证(前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);
SELECT field_list FROM t WHERE id_card_crc=CRC32('input_id_card_string') AND id_card='input_id_card_string';
每次插入新纪录的时候,都需要使用 CRC32() 函数得到校验码
由于校验码可能会 冲突 ,因此查询语句的条件需要加上id_card( 精确匹配 )
索引的长度变为了 4个字节 ,比直接用身份证作为索引所占用的空间小很多
异同点
都 不支持范围查询 ,只支持 等值查询
空间占用
倒序存储:N个字节的索引
增加hash字段:字段+索引
CPU
倒序存储:每次读写都需要额外调用一次 REVERSE 函数,开销比 CRC32 函数略小
增加hash字段:每次读写都需要额外调用一次 CRC32 函数
查询效率
增加 hash 字段方式的查询性能会 更加稳定 一些
CRC32虽然会有一定的冲突概率,但概率非常低,可以认为 平均扫描行数接近1
倒序存储一般会用到 前缀索引 ,这会增加 扫描行数 ( 无法利用覆盖索引,必须回表 )
参考资料
《MySQL实战45讲》
转载请注明出处:http://zhongmingmao.me/2019/01/31/mysql-index-string/
访问原文「MySQL -- 字符串索引」获取最佳阅读体验并参与讨论