MySQL 如何建立合适索引

如何建立合适的索引

什么叫做合适的索引？

笔者认为，在满足基本业务需求的条件下，尽可能的节约索引带来的时间和空间成本，或者是当时间与空间互斥时，选择一种最适合自己的索引方案。其实就是寻找最优解的过程。

字符串字段索引

创建完整索引

将字段完整字符串放进索引。

这种方法简单粗暴，就是有一个弊端：当字符串过长时可能比较占用空间。

创建前缀索引

顾名思义，字符串截取头部一定长度放进索引。

例如有一个邮箱字段 email 其中一行的值为 juneone9@163.com，如果对 email创建前缀索引，指定长度为 8，那么该行在索引中的值就为juneone9。

时间换空间

前缀索引是一种以时间换空间的策略。既然减小了索引的长度，势必也会降低索引的区分度，例如 juneone9@qq.com、juneone9@163.com、juneone9@gmail.com这几个值在前缀索引里都为juneone9。

执行select id,name,email from t where email= 'juneone9@163.com'; 时，完整索引只需要扫描一行即可得到结果，而前缀索引可能需要增加额外的记录扫描次数，造成时间消耗。

虽然如此，如果能保证截取的前缀依然有足够的区分度，那么将节省大量空间的同时，保证查询的执行效率。这就需要好好把握前缀索引的指定长度。

那么如何选择合适的前缀长度呢？

• 首先确定需要建立前缀索引列上的区分度，也就是不同值的个数
• 确定一个可以区分度损失的比例，例如5%。那么就要求前缀索引的区分度要在 (95% * 列总区分度) 以上
• 查询不同前缀长度的索引区分度

# 查询列总区分度
SELECT
	count( DISTINCT email ) AS L 
FROM t;
	
# 查询不同前缀索引长度的区分度
SELECT
	count( DISTINCT LEFT ( email, 1 ) ) AS L1,
	count( DISTINCT LEFT ( email, 2 ) ) AS L2,
	count( DISTINCT LEFT ( email, 3 ) ) AS L3,
	count( DISTINCT LEFT ( email, 4 ) ) AS L4 
FROM t;

对覆盖索引的影响

使用前缀索引将无法利用覆盖索引机制来优化查询。

即使前缀索引包括了所有的字符串，依然需要回表到主键索引查询，因为系统不确定是否有截断完整的信息

倒序存储和Hash字段

当字符串首部具有足够区分度创建前缀索引，才有可观的收益，例如邮箱等；但如果是身份证字符串，一般前六位是地址码，同一县的人前六位是一样的，这时可能需要很长的前缀才具有足够的区分度。

遇到这种情况，就不适合直接创建前缀索引了，有什么解决方法吗？ 一般有两种思路借鉴。

1. 倒序存储
   • 像身份证这种，前六位可能没什么区分度，但是后六位可是有很高的区分度，如果把它进行倒序存储，倒序的身份证建立前缀索引即可。
   • 每次查询时都要通过 翻转函数reverse处理
2. 增加 hash 字段
   • 在表上增加一个整数字段，保存身份证的 hash 码，并对这个字段建立索引
   • 每次查询需要通过 hash函数crc32()计算出身份证的 hash 码，作为条件查询。另外，不同身份证可能计算得到的 hash 码一致，这就需要条件加入 身份证精确匹配

两种方法的异同点：

• 相同点：
  • 两种方式都不支持范围查询。
• 不同点：
  • 额外占用空间：两者差不多。倒序存储需要建立前缀索引；增加 hash字段是一个整型字段，它的索引长度变为 4个字节，相比前缀索引应该小很多。
  • CPU消耗：倒序存储读写调用 reverse 函数；hash 字段方式需要调用 crc32() 函数。一般来说前者消耗更小。
  • 查询效率：倒序存储还是基于前缀索引；hash 字段更稳定，哈希冲突概率非常小。后者的扫描行数应该会小于前者。