当前位置:Gxlcms > mysql > 高性能的MySQL(5)创建高性能的索引一哈希索引

高性能的MySQL(5)创建高性能的索引一哈希索引

时间:2021-07-01 10:21:17 帮助过:47人阅读

哈希索引(hashindex)基于哈希表实现,只有精确匹配索引的所有列的查询才有效,对于每一行数据,存储引擎都会对所有索引列计算一个哈希码,不同键值的行计算出

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

1、Memory引擎支持哈希索引,也支持B-Tree索引,而且支持非唯一的哈希索引,如果多个列的哈希值相同,索引会以链表的方式存放多个记录指针到同一个哈希条目,这个是和特别的。

举例说明:

CREATE TABLE `testhash` ( `fname` varchar(50) NOT NULL, `lname` varchar(50) NOT NULL, KEY `fname` (`fname`) USING HASH ) ENGINE=MEMORY DEFAULT CHARSET=utf8 |

152834692.png

假设索引使用f()生成哈希码如下

f('Arjen') = 2323

f('Baron') = 7437

f('Peter') = 8784

f('Vadim') = 2458


则哈希索引数据结构如下



2323
指向第1行指针

2458
指向第4行指针

7437
指向第2行指针

8784
指向第3行指针

注意哈希码是有序的,但是数据行不是。

当执行查询的时候

select * from testhash where fname='Peter';

先计算哈希码,然后找到第3行指针,最后比较第3行的值是否为‘Peter’,以确定就是要找的行。

2、哈希索引的限制:

a、哈希索引只包含哈希码和行指针,不存储字段值,所以无法用索引中的值来避免去读取行。

b、哈希索引数据并不是按照索引值顺序存储的,所以也就无法用于排序。

c、哈希索引也不支持部分索引列匹配查找,必须利用所有索引列,因为哈希值是通过所有索引列计算的。

d、哈希索引只支持等值比较查询,包括=、in()、<=>(安全比较)比较包含null的时候用。哈希也不支持任何范围查询,比方说where price > 100

e、哈希索引非常快,除非有哈希冲突(不同的索引值会有相同的哈希值),这个时候引擎必须遍历链表中的所有行来匹配。

f、哈希冲突较多的时候,比方列上相同的值比较多的时候,索引维护代价就会比较高。

InnoDB引擎有一个特殊的功能叫做“自适应哈希索引”,由引擎内部实现,也可以关闭。


3、创建自定义哈希索引

如果存储引擎不支持哈希索引,可以在B-Tree基础上创建一个伪哈希索引。这个和真正的哈希索引不是一回事,还是用到B-Tree进行查找,只是利用键值的哈希值而不是键值来进行索引查找,只需要在where中手动指定哈希函数。

举例说明:

如果需要存储大量的URL,并且需要根据URL进行搜索,如果使用B-Tree来索引URL,存储内容会很大。比方说下面的查询

select * from url where url="http://www.baidu.com";

若删除原来的URL列索引,而新增一个被索引的字段url_crc,使用crc32做哈希就可以使用下面的查询了

select * from url where url_crc=crc32("http://www.baidu.com") and url="http://www.baidu.com";

这样性能就会很高。

这样的缺陷是需要维护哈希值。可以使用触发器来实现维护工作。

创建一张表

CREATE TABLE `pseudohash` ( `id` int(10) unsigned NOT NULL AUTO_INCREMENT, `url` varchar(255) NOT NULL, `url_crc` int(10) unsigned NOT NULL DEFAULT '0', PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8;

创建触发器

//插入 delimiter $$ create trigger pseudohash_crc_ins before insert on pseudohash for each row begin set NEW.url_crc=crc32(NEW.url);end;$$ //更新 create trigger pseudohash_crc_upd before update on pseudohash for each row begin set NEW.url_crc=crc32(NEW.url);end;$$ delimiter ;

163436552.png

163609204.png

尽量避免使用太长的哈希函数,会浪费很多空间。除非出现了大量冲突,,可以考虑自己实现一个简单的64位哈希函数,一个简单的方法是使用MD5()返回一部分值。

164018965.png

有一点值得注意:

当使用哈希索引进行查询的时候,必须在where中同时跟上rul的匹配,一旦出现了哈希冲突,这个真正要查询的值才会帮助匹配出真正的行。



本文出自 “phper-每天一点点~” 博客,请务必保留此出处

人气教程排行