文档章节

MySQL中的哈希索引

1只特立独行的猪
 1只特立独行的猪
发布于 09/22 22:52
字数 1310
阅读 11
收藏 0

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程序员大多时候也可不会用到。即使用到时再学也为时不晚。

© 著作权归作者所有

1只特立独行的猪
粉丝 1
博文 28
码字总数 16767
作品 0
朝阳
程序员
私信 提问
高性能MySQL-3rd-(五)创建高性能索引

/ -------------------------------------------------------- * 高性能MySQL-3rd-Baron Schwartz-笔记 * 第五章 创建高性能的索引 */ ---------------------------------------------------......

zhmsong
2014/01/15
226
0
高性能mysql:创建高性能的索引

索引是存储引擎用于快速找到记录的一种数据结构。 索引对于良好的性能非常关键,尤其是当表中的数据量越来越大时,索引对性能的影响愈发重要。在数据量较小且负载较低时,不恰当的索引对性能...

背后的辛酸
2018/10/23
0
0
MySQL B+树索引和哈希索引的区别

导读 在MySQL里常用的索引数据结构有B+树索引和哈希索引两种,我们来看下这两种索引数据结构的区别及其不同的应用建议。 二者区别 备注:先说下, 在MySQL文档里,实际上是把B+树索引写成了B...

Hosee
2016/06/03
656
0
高性能MySQL读书笔记 (三)

1. schema与数据类型优化 1.1 数据类型选择 更小: 选择不超过需求范围的最小类型 更简单 避免使用Null: 含有Null列会使索引,索引统计和值更为复杂 分配空间: 根据实际需要分配.使用内存临时表...

whales
2017/10/28
0
0
创建高性能的索引

---title: 《高性能MySQL》の创建高性能的索引date: 2016-04-29 16:07:50categories: 计算机科学tags: 关系型数据库 MySQL --- 0x00前言 本书讲述到定稿前的MySQL5.5版,所以下面内容的适用范...

JoshuaShaw
2016/04/30
933
1

没有更多内容

加载失败,请刷新页面

加载更多

总结

一、设计模式 简单工厂:一个简单而且比较杂的工厂,可以创建任何对象给你 复杂工厂:先创建一种基础类型的工厂接口,然后各自集成实现这个接口,但是每个工厂都是这个基础类的扩展分类,spr...

BobwithB
36分钟前
3
0
java内存模型

前言 Java作为一种面向对象的,跨平台语言,其对象、内存等一直是比较难的知识点。而且很多概念的名称看起来又那么相似,很多人会傻傻分不清楚。比如本文我们要讨论的JVM内存结构、Java内存模...

ls_cherish
39分钟前
3
0
友元函数强制转换

友元函数强制转换 p522

天王盖地虎626
昨天
5
0
js中实现页面跳转(返回前一页、后一页)

本文转载于:专业的前端网站➸js中实现页面跳转(返回前一页、后一页) 一:JS 重载页面,本地刷新,返回上一页 复制代码代码如下: <a href="javascript:history.go(-1)">返回上一页</a> <a h...

前端老手
昨天
5
0
JAVA 利用时间戳来判断TOKEN是否过期

import java.time.Instant;import java.time.LocalDateTime;import java.time.ZoneId;import java.time.ZoneOffset;import java.time.format.DateTimeFormatter;/** * @descri......

huangkejie
昨天
4
0

没有更多内容

加载失败,请刷新页面

加载更多

返回顶部
顶部