MySql 自适应哈希索引

一、介绍　　

　　哈希（hash）是一种非常快的查找方法，一般情况下查找的时间复杂度为O（1）。常用于连接（join）操作，如Oracle中的哈希连接（hash join）。

　　InnoDB存储引擎会监控对表上索引的查找，如果观察到建立哈希索引可以带来速度的提升，则建立哈希索引，所以称之为自适应（adaptive）的。

自适应哈希索引通过缓冲池的B+树构造而来，因此建立的速度很快。而且不需要将整个表都建哈希索引，InnoDB存储引擎会自动根据访问的频率

和模式来为某些页建立哈希索引。

二、示例

三、限制

1.只能用于等值比较，例如=， <=>，in

2.无法用于排序

3.有冲突可能

4.Mysql自动管理，人为无法干预。

四、通过SHOW ENGINE INNODB STATUS 查看自适应哈希索引的使用情况

In MySQL 5.7, the adaptive hash index search system is partitioned. Each index is bound to a specific partition, and each partition is protected by a separate latch.

Partitioning is controlled by the innodb_adaptive_hash_index_parts configuration option. In earlier releases, the adaptive hash index search system was

protected by a single latch which could become a point of contention under heavy workloads. The innodb_adaptive_hash_index_parts option is set to 8 by default.

The maximum setting is 512.

The hash index is always built based on an existing B-tree index on the table. InnoDB can build a hash index on a prefix of any length of the key defined for the B-tree,

depending on the pattern of searches that InnoDB observes for the B-tree index. A hash index can be partial, covering only those pages of the index that are often accessed.

You can monitor the use of the adaptive hash index and the contention for its use in the SEMAPHORES section of the output of the SHOW ENGINE INNODB STATUS

command. If you see many threads waiting on an RW-latch created in btr0sea.c, then it might be useful to disable adaptive hash indexing.

-------------------------------------
INSERT BUFFER AND ADAPTIVE HASH INDEX
-------------------------------------
Ibuf: size 1, free list len 0, seg size 2, 94 merges
merged operations:
 insert 280, delete mark 0, delete 0
discarded operations:
 insert 0, delete mark 0, delete 0
Hash table size 4425293, node heap has 1337 buffer(s)
174.24 hash searches/s, 169.49 non-hash searches/s

哈希索引只能用来搜索等值的查询，对于其他查找类型，如范围查找，是不能使用哈希索引的，因此这里出现no--hash searches的情况。

通过hash searches:non-hash searches可以大概了解使用哈希索引后的效率

参考

mysql reference : adaptive hash index