MySQL中的哈希索引

原创
2019/09/22 22:52
阅读数 55

Memory中的哈希索引


哈希索引是基于哈希表实现的,只有精确匹配索引所有列的查询才有效。对于每一行数据,存储引擎都会对所有的索引列计算一个哈希码,哈希码是一个较小的值,并且不同键值的行计算出来的哈希码也不一样。哈希索引将所有的哈希码存贮在索引中,同时在哈希表中保存指向每个数据行的指针。

在MySQL中只有Memory引擎显式支持哈希索引。这也是Memory引擎表的默认索引类型。

Memory引擎支持非唯一索引,这在数据库世界里是比较与众不同的。如果多个列的哈希值相同,索引会以链表的方式存放多个记录指针到同一个哈希列表中。

创建一个使用hash索引的表,

CREATE TABLE testhash (
	fname	VARCHAR(50)		NOT NULL,
	lname	VARCHAR(50)		NOT NULL,
	KEY USING HASH(fname)
) ENGINE=MEMORY;

用下式查询,

mysql> SELECT lname FROM testhash WHERE fname='Peter';

mysql先计算“Peter”的哈希值,并使用该值寻找对应的记录指针。找到指针指向的行后,比较fname是否为“Peter”,以确保是查找的行。

因为索引自身只需存贮对应的哈希值,所以索引的结构十分紧凑,这也让哈希索引查找的速度非常快。

然而也有局限,懒得敲字了,直接上图

因为这些限制,哈希索引只适用于某些特定的场合。然而一旦使用哈希索引,则它带来的性能提升非常显著

InnoDB中的自适应哈希索引


InnoDB引擎中有一个特殊的功能叫做“自适应哈希索引(adaptive hash index)”。当InnoDB注意到某些索引值被使用的非常频繁时,它会在内存中基于B-Tree索引上再创建一个哈希索引,这样就让B-Tree索引也具有哈希索引的一些优点,比如快速的哈希查找。这是一个完全自动的、内部的行为,用户无法控制或者配置,不过如果有必要,完全可以关闭该功能。

可以通过参数 innodb_adaptive_hash_index 查看是否开启。默认是打开的。

mysql> show variables like "innodb_adaptive_hash_index";
+----------------------------+-------+
| Variable_name              | Value |
+----------------------------+-------+
| innodb_adaptive_hash_index | ON    |
+----------------------------+-------+

关闭自适应哈希索引,

mysql> set innodb_adaptive_hash_index = 0;

创建自定义哈希索引


如果存储引擎不支持哈希索引,则可以类似于InnoDB创建哈希索引,这样可以享受一些哈希索引的便利。

思路:B-Tree基础上创建一个伪哈希索引。这和真正的哈希索引不是一回事,因为还是使用B-Tree进行查找,但是使用哈希值而不是键本身进行索引查找,你需要在查找的where子句中手动指定使用哈希函数。

实例: 如需要存储大量URL,并并需要根据URL进行搜索查找。如果使用B-Tree来存储URL,存贮的内容会很大。

一般情况下的查询语句,

mysql> SELECT id FROM url WHERE url = 'https://my.oschina.net/depeng414';

若删除原来url列上的索引,而新增一个被索引的url_crc列,使用CRC32做哈希,就可以这样查询,性能更高。

mysql> SELECT id FROM url WHERE url = 'https://my.oschina.net/depeng414' 
    -> AND url_crc = CRC32('https://my.oschina.net/depeng414');

因为MySQL优化器会使用这个选择性很高而体积很小的基于url_crc列的索引来完成查找(只需根据哈希值做快速的整数比较就能找到索引条目)。

当然,没有免费的午餐,为了查找的速度就要牺牲维护的便捷性。这样实现的缺陷就是需要维护哈希值。

可以手动维护或者选择触发器实现。先创建表,

CREATE TABLE pseudohash (
	id int unsigned  NOT NULL auto_increment,
	url varchar(225) NOT NULL,
	url_crc int unsigned NOT NULL DEFAULT 0,
	PRIMARY KEY(id)
);

然后创建触发器。先临时修改下语句分隔符,这样就可以在触发器定义语句中使用分号

mysql> delimiter //
mysql> create trigger pseudohash_crc_ins before insert on pseudohash for each row begin  
    -> set new.url_crc=crc32(new.url);
    -> end;
    -> //
Query OK, 0 rows affected

mysql> delimiter //
mysql> create trigger pseudohash_crc_upd before update on pseudohash for each row begin 
    -> set new.url_crc=crc32(new.url);
    -> end;
    -> //
Query OK, 0 rows affected

delimiter ;

验证下,

mysql> insert into pseudohash (url) values ('http://www.baidu.com');
Query OK, 1 row affected

mysql> select * from pseudohash;
+----+----------------------+------------+
| id | url                  | url_crc    |
+----+----------------------+------------+
|  1 | http://www.baidu.com | 3500265894 |
+----+----------------------+------------+
1 row in set

切记,不要使用 SHA1() 和 MD5() 作为哈希函数,这两个函数是强加密函数,设计目标是最大限度消除哈希冲突,这里不需要这样的高的要求。

如果数据表非常大,可以自己实现一个哈希函数,这里就不敲了,我想绝大多数java程序员大多时候也可不会用到。即使用到时再学也为时不晚。

展开阅读全文
0
0 收藏
分享
加载中
更多评论
打赏
0 评论
0 收藏
0
分享
返回顶部
顶部