The difference between two kinds of index hash and b-tree in MySQL

What is the difference between a MySQL hash index and a b-tree index? I believe many people have such a question, the following is a detailed analysis of the difference between the two for your reference.

MySQL Hash Index structure particularity, its retrieval efficiency is very high, index retrieval can be positioned once, unlike the B-tree index needs from the root node to the side point, the last access to the page node so many IO access, so the Hash index query efficiency is much higher than the B-tree index.

Many people may have doubts, since the hash index is more efficient than b-tree, why do not all use hash index and also use B-tree index? Everything has two sides, hash index is the same, although the hash index is high efficiency, but the hash index itself by In its particularity also brings a lot of limitations and drawbacks, mainly the following.

(1) MySQL Hash index can only satisfy "=", "in" and "<=>" query, cannot use range query.

Because the MySQL hash index is compared to the hash after the hash value, so it can only be used for the equivalent of filtering, can not be used for filtering based on the scope, because after the corresponding hash algorithm to deal with the size of the hash value of the relationship, and can not guarantee the same as the hash operation before.

(2) MySQL Hash index cannot be used to avoid sorting operations on data.

Because the MySQL hash index is stored in the hash after the hash value, and the size of the hash value is not necessarily the same as the hash operation before the key value is exactly the same, so the database can not use the index data to avoid any sort operation;

(3) MySQL Hash index cannot use partial index key query.

For the combined index, the hash index calculates the hash value when the combination index key merges and then calculates the hash value together, instead of calculating the hash value separately, the hash index can not be used when the query is made by the first or several index keys of the combined index.

(4) MySQL Hash index cannot avoid table scans at any time.

As already known before, hash index is the key through the hash operation, the hash of the results and the corresponding row pointer information stored in a hash table, because of the same hash value of different key, so even if the data to meet a hash key value of the number of records, can not To complete the query directly from the Hash index, or to compare it by accessing the actual data in the table, and get the corresponding results.

(5) When a MySQL hash index encounters a large number of hash values that are equal, performance does not necessarily have to be higher than the B-tree index.

For index keys with a low selectivity, if a hash index is created, there will be a large number of record pointer information that is associated with the same Hash value. This will be very cumbersome to locate a record, will waste a lot of table data access, resulting in poor overall performance.