场景 建表 1 2 3 4 5 CREATE TABLE SUser( id BIGINT UNSIGNED PRIMARY KEY, name VARCHAR (64 ), email VARCHAR (64 ) ) ENGINE= InnoDB;
查询
创建索引 1 2 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
获取index2上的下一条记录,发现仍然是zhangs
,取得主键为ID2
重复上面的步骤,直到index2上取得的值不为zhangs
为止
整个过程,需要回表 4次,系统认为扫描了4行
假设index2为email(7)
,满足前缀zhangss
只有一个,只需要回表一次
使用前缀索引,如果能定义好长度 ,即能节省空间 ,又不会增加太多的查询成本
前缀索引的长度 原则:区分度 。使用前缀索引一般都会损失区分度 ,预设一个可接受的损失比例 ,在该损失比例内,寻找最短 前缀长度
1 2 3 4 5 6 7 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;
前缀索引与覆盖索引
如果使用index1 ,可以利用覆盖索引 ,不需要回表
如果使用index2 ,就必须回表 ,获得整行记录后再去判断email字段的值
即使index2为email(18)
(包含了所有信息),还是需要回表
因为系统不确定前缀索引的定义是否截断了完整信息
因此,前缀索引是用不上覆盖索引对查询性能的优化
其他手段 场景:前缀的区分度非常差,例如居民身份证(前6位是地址码)
倒序存储 1 SELECT field_list FROM t WHERE id_card = REVERSE('input_id_card_string' );
增加hash字段 1 2 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讲》