MySQL 使用伪HashCode作为索引提高性能 | Eddie'Blog
MySQL 使用伪HashCode作为索引提高性能

MySQL 使用伪HashCode作为索引提高性能

eddie 315 2021-05-19

目录

索引调优技巧

  • 长字段的索引调优
    • 索引选择性 = 不重复的索引值/数据表的总记录数
      • 数值越大,表示选择性越高,性能越好
  • 使用组合索引的技巧
  • 覆盖索引
  • 排序优化
  • 冗余、重覆索引的优化

长字段的索引调优

  • 索引选择性 = 不重复的索引值/数据表的总记录数
    • 数值越大,表示选择性越高,性能越好

示例

-- 基础查询,first_name因为可能字段过长关系,追加索引会影响效率,因为体积大
EXPLAIN
select *
from employees
where first_name = 'Eddie';

-- 追加CRC32字段的索引
ALTER TABLE `employees`.`employees` 
ADD INDEX `test_first_name_crc32`(`first_name_crc32`) USING BTREE

-- 插入追加 first_name_crc32 字段的数据
insert into employees (emp_no, birth_date, first_name, last_name, gender, hire_date, first_name_crc32)
    value (
           999992, now(),
           'Eddie',
           'Lee', 'M', now(),
           CRC32('Eddie123')
    );


-- 查看生成的hash code值
SELECT CRC32('Eddie123') as name;  

-- 加 "and first_name = 'Eddie'" 防止哈斯冲突的时候,依然可以找到数据
EXPLAIN
select *
from employees
where first_name_crc32 = CRC32('Eddie123')
  and first_name = 'Eddie';  
	

小节

  • 引入Hash字段,作为索引
    • 伪 "Hash索引"
  • 使用前缀索引

备注

我们知道 Mysql 默认引擎是 InnoDB 引擎,而 InnoDB 支持的索引类型为 B-tree 类型,如果对数据表有一个根据长字符查询的sql,使用 B-tree 索引在大数据量的情况下将会导致性能低下,然而 InnoDB 不支持Hash索引,不过我们可以在 B-tree 基础上创建一个伪哈希索引。这和真正的哈希索引不是一回事,因为还是使用 B-Tree 索引进行查找,但是它使用哈希值而不是键本身进行索引查找。你需要做的是在查询的 where 子句中手动指定使用哈希函数。


例如我们有一个表需要存储大量的 first_name ,并根据 first_name 进行搜索查找。如果使用 B-Tree first_name,存储的内容就会很大,因为 first_name 本身就很长,我们首先看下表情况(first_name 字段有索引):



正常情况下会有如下查询:
select * from employees where first_name = 'Eddie';


若删除原来 first_name 列上的索引,而新增一个被索引的 first_name_crc32 列,使用 CRC32 作为哈希,就可以使用如下的方式进行查询:


select * from employees where first_name_crc32 = CRC32('Eddie123') and first_name = 'Eddie';



这样做的性能会非常高,因为 MySQL 优化器会使用这个选择性很高而体积很小的基于 first_name_crc32 列的索引来完成查找。即使有多个记录有相同的索引值,查找依然很快,只需要根据哈希值做快速的整形比较就能找到索引条目,然后一一比较返回对应的行。另一种方式就是对完整的 first_name 字符串做索引,那样会非常慢。



这样实现的缺陷是需要维护哈希值。可以手动维护,也可以使用触发器实现。但是需要==注意的是,如果使用这种方式优化的话,切记请勿使用 SHA1() 和 MD5() 作为哈希函数==。因为这个两个函数计算出来的哈希值是非常长的字符串,会浪费大量空间,比较时也会更慢。SHA1() 和 MD5() 是强加密函数,设计目标是最大限度消除冲突,但这里并不需要这样的高的要求。



如果数据表非常大,CRC32() 会出现大量的哈希冲突,则可以考虑自己实现一个简单的64位哈希函数。这个自定义函数要返回整数,而不是字符串,或者在查询中 处理哈希冲突 :当使用哈希索引进行查询的时候,必须在 where 子句中包含常量值(即上面sql中的 ==and first_name = 'Eddie';== ),因为一旦哈希冲突的话,不带常量值将返回多条结果集,导致查询无法正常工作。