Mysql hash index and mysql hash

Mysql hash index and mysql hash

From: High-Performance Mysql

1. the hash index is implemented based on the hash table. Only queries that match exactly all the columns of the index are valid. For each row of data, the storage engine calculates a hash code for all the index columns, the hash code is stored in the index, and only each row pointer is saved in the hash table.

In mysql, only memory engine displays support for hash indexes and non-unique hash indexes. It is also the default index type of memory.

Note:

(1) hash index data is not stored in the order of index values, so it cannot be used for sorting.

(2) Some indexes cannot be matched.

(3) query by range is not supported. Only query by equivalent comparison is supported, including (=, IN (), and <=>)

(4) If there are many hash conflicts (different index column values have the same hash value), it is very costly to maintain indexes.

2. the InnoDB engine can "adaptive hash Index ". You can create custom hash indexes. However, the search is still performed in B + Tree, but the hash value instead of the Key itself is used for index search.