目录

一、前缀索引 降低字符串长度，降低内存占用，从而提高搜索效率。

截取前缀索引需要注意：区分度和内存占用间取得平衡

前缀索引对覆盖索引的影响：无法使用覆盖索引的优化

二、其他索引方式

1、使用倒序存储，再创建前缀索引  解决区分度问题

2、使用hash字段 增加额外字段

3、倒叙存储和hash字段的比较 （空间占用，cpu消耗，查询效率）

倒叙存储和hash字段，都无法支持范围查询，只可以进行等值查询。

一、前缀索引

现在，几乎所有的系统都支持邮箱登录，如何在邮箱这样的字段上建立合理的索引。

索引选取的越长，占用的磁盘空间就越大，相同的数据页能放下的索引值就越少，搜索的效率也就会越低。MySQL 是支持前缀索引的，也就是说，你可以定义字符串的一部分作为索引。

-- 选取整个字符串做索引
alter table SUser add index index1(email);
-- 选取整个字符串的一部分做索引，前缀索引
alter table SUser add index index2(email(6));

截取前缀索引需要注意：

选择合适的长度，在节省内存空间的同时需要保证内容上的区分度。

前缀索引对 覆盖索引 的影响：

用前缀索引就用不上覆盖索引对查询性能的优化，每次查询都需要进行回表，并对字段进行比较。因为统并不确定前缀索引的定义是否截断了完整信息。

二、其他索引方式

1、使用倒序存储，再创建前缀索引

比如对于给身份证号加索引时候，身份号的前6位代表地区，区分度不好，那么我们就可以是由后六位作为索引，会使用到reverse函数。

2、使用hash字段

在表中增加新的字段列，里面存放身份证号的hash值【用 crc32() 这个函数得到校验码】，并对hash值字段增加索引。

3、倒叙存储和hash字段的比较

  • 从占用的额外空间来看，倒序存储方式在主键索引上，不会消耗额外的存储空间，而 hash 字段方法需要增加一个字段。当然，倒序存储方式使用 4 个字节的前缀长度应该是不够的，如果再长一点，这个消耗跟额外这个 hash 字段也差不多抵消了。
  • 在 CPU 消耗方面，倒序方式每次写和读的时候，都需要额外调用一次 reverse 函数，而 hash 字段的方式需要额外调用一次 crc32() 函数。如果只从这两个函数的计算复杂度来看的话，reverse 函数额外消耗的 CPU 资源会更小些。
  • 从查询效率上看，使用 hash 字段方式的查询性能相对更稳定一些。因为 crc32 算出来的值虽然有冲突的概率，但是概率非常小，可以认为每次查询的平均扫描行数接近 1。而倒序存储方式毕竟还是用的前缀索引的方式，也就是说还是会增加扫描行数。