What is the difference between B-tree indexes and hash indexes in MySQL?

What is the difference between B-tree indexes and hash indexes in MySQL?

This article briefly describes the differences between two index types, btree and hash in mysql.

Due to the particularity of the hash index structure, the retrieval efficiency is very high, and the index retrieval can be located at a time, unlike the B-Tree index that needs to go from the root node to the branch node, the Hash index query efficiency is much higher than that of B-Tree indexes.

Many people may have doubts. Since Hash indexes are much more efficient than B-Tree indexes, why do we need to use B-Tree indexes instead of Hash indexes?

Everything has two sides. The same is true for Hash indexes. Although Hash indexes are highly efficient, Hash indexes also impose many restrictions and drawbacks due to their particularity.

(1) The Hash index only supports "=", "IN" and "<=>" queries, and does not support range queries.

Because the Hash Index compares the Hash value after Hash calculation, it can only be used for equivalent filtering and cannot be used for range-based filtering, because the relationship between the size of Hash values processed by the corresponding Hash algorithm cannot be exactly the same as that before the Hash operation.

(2) Hash indexes cannot be used to avoid data sorting.

Hash indexes store Hash values after Hash calculation, and the relationship between Hash values is not necessarily the same as that before Hash calculation, therefore, the database cannot use the index data to avoid any sort operations;

(3) Hash indexes cannot be queried using some index keys.

For a composite index, when calculating the Hash value, the Hash value is calculated after the composite index is bonded, instead of separately calculating the Hash value, therefore, when one or more index keys are used to query a combined index, the Hash index cannot be used.

(4) Hash indexes cannot avoid table scanning at any time.

As we already know, the Hash index stores the Hash value of the Hash operation result and the row pointer information corresponding to the index key in a Hash table, because different index keys have the same Hash value, the query cannot be completed directly from the Hash index even if the number of records that meet the Hash key value is obtained, you still need to compare the actual data in the Access Table and obtain the corresponding results.

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

For low-selectivity index keys, if a Hash index is created, a large amount of Record Pointer information is stored in the same Hash value. In this way, it will be very troublesome to locate a record, which will waste multiple table data accesses, resulting in low overall performance.