Differences between Hash and B-Tree indexes in MySQL _ MySQL

What are the differences between two indexes Hash and B-Tree in MySQL? what are the differences between MySQL Hash indexes and B-Tree indexes? I believe many people have such questions. The difference between the two is analyzed in detail below for your reference.

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

But many people 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) MySQL Hash indexes only support "=", "IN" and "<=>" queries, and cannot use range queries.

Because the MySQL Hash index compares the Hash values 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) MySQL Hash indexes cannot be used to avoid data sorting.

Because MySQL 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) MySQL 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) MySQL 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 MySQL 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.