场景

建表

1
2
3
4
5
CREATE TABLE SUser(
    id BIGINT UNSIGNED PRIMARY KEY,
    name VARCHAR(64),
    email VARCHAR(64)
) ENGINE=InnoDB;

查询

1
SELECT id,name,email FROM SUser WHERE email='[email protected]';

创建索引

1
2
ALTER TABLE SUser ADD INDEX index1(email);
ALTER TABLE SUser ADD INDEX index2(email(6));

index1

  1. 索引长度：整个字符串
  2. 从index1索引树找到第一个满足索引值为zhangssxyz@xxx.com的记录，取得主键为ID2
    • 到聚簇索引上查找值为ID2的行，判断email的值是否正确（Server层行为），将该行记录加入结果集
  3. 获取index1上的下一条记录，发现不满足email=zhangssxyz@xxx.com，循环结束
  4. 整个过程，只需要回表1次，系统认为只扫描了1行

index2

  1. 索引长度：_前6个字节_
  2. 索引占用的空间更小，增加额外的记录扫描次数，（且不支持覆盖索引，见后面）
  3. 从index2索引树找到第一个满足索引值为zhangs的记录，取得主键为ID1
    • 到聚簇索引上查找值为ID1的行，email!=zhangssxyz@xxx.com（Server层行为），记录丢弃
  4. 获取index2上的下一条记录，发现仍然是zhangs，取得主键为ID2
  5. 重复上面的步骤，直到index2上取得的值不为zhangs为止
  6. 整个过程，需要回表4次，系统认为扫描了4行
  7. 假设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;

前缀索引与覆盖索引

1
SELECT id,email FROM SUser WHERE email='[email protected]';
  1. 如果使用index1，可以利用覆盖索引，不需要回表
  2. 如果使用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';
  1. 每次插入新纪录的时候，都需要使用CRC32()函数得到校验码
  2. 由于校验码可能会冲突，因此查询语句的条件需要加上id_card（精确匹配）
  3. 索引的长度变为了4个字节，比直接用身份证作为索引所占用的空间小很多

异同点

  1. 都不支持范围查询，只支持等值查询
  2. 空间占用
    • 倒序存储：N个字节的索引
    • 增加hash字段：字段+索引
  3. CPU
    • 倒序存储：每次读写都需要额外调用一次REVERSE函数，开销比CRC32函数略小
    • 增加hash字段：每次读写都需要额外调用一次CRC32函数
  4. 查询效率
    • 增加hash字段方式的查询性能会更加稳定一些
      • CRC32虽然会有一定的冲突概率，但概率非常低，可以认为平均扫描行数接近1
    • 倒序存储一般会用到前缀索引，这会增加扫描行数（无法利用覆盖索引，必须回表）

参考资料

《MySQL实战45讲》