The difference between btree and hash indexes in MySQL

We know that MySQL has two indexes: btree and hash. Do you know the difference between them? This article briefly describes the difference between the two index types in MySQL, btree and hash. Due to the particularity of the hash index structure, its retrieval efficiency is very high. The index retrieval can be located at one time. Unlike the B-Tree index, which requires multiple IO accesses from the root node to the branch node and finally to the page node, Hash The query efficiency of the index is much higher than that of the B-Tree index.

Many people may have questions again. Since the efficiency of Hash index is much higher than that of B-Tree, why don't everyone use Hash index but also use B-Tree index?

Everything has two sides, and Hash index is the same. Although Hash index is highly efficient, Hash index itself also brings many limitations and disadvantages due to its particularity, mainly as follows.

(1) Hash index can only satisfy "=","IN" and "<=>" queries, and range queries cannot be used.

Since the Hash index compares the Hash value after Hash operation, it can only be used for equal value filtering and cannot be used for range-based filtering, because the Hash value after processing by the corresponding Hash algorithm The size relationship is not guaranteed to be exactly the same as before the Hash operation.

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

Since the Hash index stores the Hash value after Hash calculation, and the size relationship of the Hash value is not necessarily exactly the same as the key value before the Hash operation, so the database cannot use the index data to avoid any Sorting operation;

(3) Hash index cannot be queried using part of the index key.

For the combined index, when the Hash index calculates the Hash value, the combined index keys are merged and then the Hash value is calculated together, instead of calculating the Hash value separately, so the previous one or several index keys of the combined index are used. When querying, the Hash index cannot be used.

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

As we know before, Hash index is to store the Hash value of the Hash operation result and the corresponding row pointer information in a Hash table after Hash operation is performed on the index key. Since different index keys have the same Hash value , so even if you get the number of records that satisfy a certain Hash key value, you cannot directly complete the query from the Hash index. You still have to make corresponding comparisons by accessing the actual data in the table and get the corresponding results.

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

For index keys with low selectivity, if you create a Hash index, there will be a large number of record pointer information stored in the same Hash value. In this way, it will be very troublesome to locate a certain record, and it will waste multiple accesses to the table data, resulting in low overall performance.

Above we have introduced the differences between btree and hash indexes from five aspects. I believe everyone has an understanding of them, and I hope you can use them in your work.

Related recommendations:

Mysql-index-BTree type [simplified]

MySQL index optimization btree hash rtree

Mysql optimization road----hash index optimization

The above is the detailed content of The difference between btree and hash indexes in MySQL. For more information, please follow other related articles on the PHP Chinese website!