The difference between MySQL hash index and B-tree index

What is the difference between MySQL hash index and B-tree index? Believe that a lot of people have this question, the following is the difference between the two detailed analysis, for your reference.

MySQL Hash index structure of the particularity, its retrieval efficiency is very high, index retrieval can be located at once, unlike B-tree index need from the root node to the side point, and finally access to the page node so many IO access, so the Hash index query efficiency is much higher than the B-tree index.

Probably 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 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 due to its particularity also brought a lot of limitations and drawbacks, mainly have the following.

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

Because the MySQL 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 after the corresponding hash algorithm processing hash value of the size of the relationship, and can not be guaranteed and hash before the exact same.

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

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

For the composite index, the hash index in the calculation of the hash value when the combination index key merge and then calculate the hash value together, rather than calculate the hash value alone, so by combining the index of the previous or several index key query, the Hash index can not be exploited.

(4) MySQL hash index cannot avoid table scanning at any time.

As already known, 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 stored in a hash table, because the different index keys exist the same hash value, so even if the number of data that satisfies a hash key value of the record bar, also can not The query is completed directly from the Hash index, or the actual data in the table is accessed, and the corresponding results are obtained.

(5) MySQL Hash index after encountering a large number of hash value is equal, performance will not necessarily be higher than the B-tree index.

For low-selectivity index keys, if a hash index is created, then there will be a large number of record pointer information 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 MySQL hash index and B-tree index