Shulou(Shulou.com)05/31 Report--

MySQL what is the difference between Hash and B-Tree, many novices are not very clear about this, in order to help you solve this problem, the following editor will explain in detail for you, people with this need can come to learn, I hope you can gain something.

Because of the particularity of the MySQL Hash index structure, its retrieval efficiency is very high, and the index retrieval can be located at once, unlike the B-Tree index which needs to access the page node from the root node to the branch node for so many IO visits, so the query efficiency of the Hash index is much higher than that of the B-Tree index.

But many people have doubts again, since the efficiency of Hash index is much higher than that of B-Tree, why don't everyone use Hash index instead of B-Tree index? Everything has two sides, and so is Hash index. although Hash index is efficient, Hash index itself brings a lot of limitations and disadvantages because of its particularity.

(1) MySQL Hash indexes can only satisfy "=", "IN" and "" queries, but cannot use range queries.

Because the MySQL Hash index compares the Hash value after the Hash operation, it can only be used for equivalent filtering, not for range-based filtering, because the size relationship of the Hash value processed by the corresponding Hash algorithm cannot be guaranteed to be exactly the same as before the Hash operation.

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

Because the MySQL Hash index stores the Hash value after Hash calculation, and the size relationship of the hash value is not necessarily the same as the key value before the Hash operation, it is impossible to use the index data to avoid any sort operation.

(3) MySQL Hash indexes cannot be queried by partial index keys.

For a composite index, the Hash index calculates the Hash value by combining the index keys and then calculating the Hash value together, rather than calculating the Hash value separately, so the Hash index cannot be utilized when querying through one or more of the first index keys of the composite index.

(4) MySQL Hash indexes can not avoid table scans at any time.

As we already know, the Hash index stores the Hash value of the Hash operation result and the corresponding row pointer information in a Hash table after the index key is calculated through Hash. Because different index keys have the same Hash value, even if you take the number of records that meet a certain Hash key value, the query cannot be completed directly from the Hash index, or the corresponding comparison should be made by accessing the actual data in the table. And the corresponding results are obtained.

(5) when a large number of hash values are equal, the performance of MySQL Hash index is not necessarily higher than that of B-Tree index.

For index keys with low selectivity, if you create a Hash index, there will be a large amount of record pointer information associated with the same Hash value. In this way, it will be very troublesome to locate a record, which will waste many visits to table data, resulting in poor overall performance.

Is it helpful for you to read the above content? If you want to know more about the relevant knowledge or read more related articles, please follow the industry information channel, thank you for your support.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.