高性能MYSQL---索引（二）_mysql判断哈希索引冲突是否高-CSDN博客

本文链接：https://blog.csdn.net/ciyanxi3469/article/details/88218503

在上一部分中，我们介绍了最常见的B-Tree索引，该索引是我们通常所说的索引类型，有序。无需匹配索引的所有查询列，且因为其有序所以可以用作order by。
索引是由搜索引擎负责实现的，行级锁和事务也是由搜索引擎负责实现的。

哈希索引

哈希索引基于哈希表来实现，只能精确的匹配索引的所有的列的查询，因为搜索引擎会将定义的所有索引的列计算出来一个哈希码，哈希码是一个较小的值，并且不同的键值的行计算出来的哈希码也不相同。哈希索引将所有的哈希码存储在索引中，同时在哈希表中保存指向每一个数据行的指针。

这里需要声明一点，在MYSQL中，只有Memory引擎显示支持哈希索引。这也是Memory引擎表的默认索引类型，Memory引擎同时也支持唯一哈希索引（因为可能多个列的哈希值相同，通常这种情况下需要对其进行处理），这点与其他的数据库引擎不同。

如果多个列的哈希值相同，Memory引擎的索引会以链表的方式存放多个记录指针到同一个哈希条目中。

举个例子来说明下

CREATE TABLE testhash（
	fname VARCHAR(50) NOT NULL,
	LNAME VARCHAR(50) NOT NULL,
	KEY USING HASH(fname)
）ENGINE=MEMORY

上面的建表语句显示声明了数据库的存储引擎使用MEMORY，并且指明了该表的索引是fname，下面来看看hash索引具体是怎么操作的

SELECT * FROM testhash;

fname	lname
Arjen	Lentz
Baron	Schwartz
Peter	Zaitsev
Vadim	Tkachenko

因为我们将索引设置为fname列，所以我们会将每一条数据的fname列计算出哈希值，并且以该哈希值为key，数据行的指针为value
假设我们的计算hash的函数是f()
f(‘Arjen’)=2323
f(‘Baron’)=7437
f(‘Peter’)=8784
f(‘Vadim’)=2458

所以哈希索引的存储如下：

槽	值
2323	指向第一行的指针
2458	指向第四行的指针
7437	指向第二行的指针
8784	指向第三行的指针

这里声明一下，我们需要注意每个槽的编号是顺序的，但是其value并不是有序的。

mysql> SELECT lname FROM testhash WHERE fname = ‘Peter’;

针对上面的sql，Memory存储引擎会先计算’Peter’的哈希值，并且使用该值寻找对应的指针记录。对照上面的表可以看到’Peter’所对应的的值是8784，所以直接查找第三行的指针。最后一步我们需要比较下’Peter’的值和第三行的值是否相等，二次确认来确保要查找的行。

哈希索引的hash值是非常小的，所以索引的结构十分的紧凑，这也让哈希索引的查找速度非常的快。

哈希索引的限制：

哈希索引只包含哈希值和其对应的行指针，而不存储字段，所以不能使用索引的值来避免读取行。
哈希索引数据并不是按照索引值得顺序存储的，只能保证其槽有序，因此不能用于order by。
哈希索引不支持部分索引列匹配查找，因为哈希索引始终是使用索引列的全部内容来计算hash值。
hash索引只支持等值比较查询，包括=, IN(), <=>，也不支持任何范围查找，如WHERE price >= 100。
访问哈希索引的数据非常快，除非有很多的哈希冲突（不同的索引列值却有相同的哈希值）。当出现哈希冲突的时候，存储引擎必须便利链表中所有的行指针，逐行进行比较，知道找到所有复合条件的行。
如果哈希冲突很多的话，一些索引维护操作的代价很高。例如：如果在某个选择性很低（哈希冲突多）的列上建立哈希索引，那么从表中删除一行的时候，存储引擎需要遍历对应哈希值的链表上的每一行，找到并删除对应的行的引用，冲突越多，代价越大。

哈希索引只适用于某些特定场合。但是一旦可以成功的使用哈希索引，则他带来的性能将提升的非常显著。哈希索引非常适合查找表的需求

1.1 自适应哈希索引
InnoDB中的一个特殊的功能。InnoDB默认支持的是B-Tree索引，当InnoDB注意到某些索引值被使用的非常频繁的时候，他会在内存中基于B-Tree索引上再创建一个哈希索引，这样就让B-Tree索引有了哈希索引的优点。这是一个内部的行为，系统自动控制，但是用户可以关闭该功能。
1.1.1 创建自定义的哈希索引
我们自己定义的哈希索引其实是一个伪哈希索引。

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

例如：
mysql>SELECT id FROM url WHERE url=“http://www.mysql.com”;
我们可以删除原来URL列上的索引，然后新增一个被索引的url_crc列，使用CRC32做哈希。
变成：
mysql > SELECT id FROM url WHERE url = “http://www.mysql.com” AND url_crc=CRC32（http://www.mysql.com“”）;