The difference between MySQL's btree index and hash index

MySQL btree index and Hash index of the difference Ash index structure of the particularity, its retrieval efficiency is very high, index retrieval can be located at once, unlike the B-tree index needs from the root node to the side point, and finally access to the page node so many IO access, so Hash Index queries are much more efficient than b-tree indexes.

Maybe a lot of people have doubts, since the efficiency of the hash index is much higher than b-tree, why do we not all use the hash index and also use the B-tree index? Everything has two sides, hash index is the same, although the hash index is high efficiency, but the hash index itself due to its particularity also brought a lot of limitations and drawbacks, mainly have the following.

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

Because the hash index comparison is the hash value after the hash operation, so it can only be used for the equivalent of filtering, can not be used for range-based filtering, because the corresponding hash algorithm after processing the hash value of the size of the relationship, and can not be guaranteed and hash before the exact same.

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

Because the 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 key value before the hash operation, so the database can not use the index data to avoid any sorting operations;

(3) Hash Indexes cannot be queried with partial index keys.

For composite indexes, the hash index calculates the hash value when the combination index key is combined and then the hash value is calculated together, rather than the hash value, so the hash index cannot be exploited when querying with the previous or several index keys of the combined index.

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

already know that the hash index is the index key through the hash operation, the hash value of the result of hashing and the corresponding line pointer information is stored in a hash table, because the different index keys exist the same hash value, so even if the value of a hash key to meet the data Record the number of bars, you can not directly complete the query from the Hash index, or to access the actual data in the table for the corresponding comparison, and get the corresponding results.

(5) When a hash index encounters a large number of hash values equal, performance is not necessarily higher than the B-tree index.

for low-selectivity index keys, if you create a hash index, there will be a large number of record pointer information that is associated with the same hash value. This can be very cumbersome to locate a record, wasting multiple table data access and resulting in poor overall performance.

The difference between the Btree index and the hash index of MySQL