High performance MySQL (5) Create high-performance index one hash index

Hash index is implemented based on hash table, only queries that accurately match all the columns of the index are valid, and for each row of data, the storage engine computes a hash code for all the indexed columns, and the hash codes for the different key values are not the same, and the hash code is stored in the hash index. A pointer to each data is saved in the hash table.

1. The memory engine supports a hash index, also supports a b-tree index, and supports a non unique hash index, and if multiple columns have the same hash value, the index stores multiple record pointers to the same hash entry in the form of a linked list, which is special.

An example is provided:

CREATE TABLE ' Testhash ' (
  ' fname ' varchar ') not NULL,
  ' lname ' varchar (+) NOT NULL,
  KEY ' fname ' (' fname ') US ING HASH
) engine=memory DEFAULT Charset=utf8 |

Suppose the index uses F () to generate the hash code as follows

F (' Arjen ') = 2323
F (' Baron ') = 7437
F (' Peter ') = 8784
F (' Vadim ') = 2458

The hash index data structure is as follows

Note that the hash code is ordered, but the data row is not.

When you execute the query

SELECT * from Testhash where fname= ' Peter ';

The hash code is computed first, then the 3rd row pointer is found, and the value of the 3rd row is compared to ' Peter ' to determine the line to be found.

2, the limit of the hash index:

A, the hash index contains only hash codes and row pointers, and No field values are stored, so the values in the index cannot be used to avoid reading rows.

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

C, hash indexes also do not support partial indexed column matching lookups, all indexed columns must be exploited because the hash value is computed from all indexed columns.

D, the hash index supports only equivalent comparison queries, including =, in (), <=> (security comparison) when comparisons contain null. Hash also does not support any range queries, such as where price > 100

E, hash index is very fast, unless there is a hash conflict (different index values will have the same hash value), this time the engine must traverse all the rows in the list to match.

F, when there are more hash conflicts, for example, when the same value on the column is more, the cost of the index maintenance will be relatively high.

The InnoDB engine has a special function called "Adaptive Hash Index", which is implemented internally by the engine or can be closed.