The difference between MySQL Hash and ES2en ES3en indexes

The special structure of MySQL Hash index has a very high retrieval efficiency. The index can be located once, unlike B-ES4en index, which requires multiple visits from the root node to the branch point and finally to the page node, so the query efficiency of Hash index is much higher than that of ES7en-ES8en index.
However, many people wonder, since Hash is much more efficient than ES11en-ES12en, why not use ES14en-ES15en instead of Hash? Every coin has two sides, so does Hash index. Although Hash index is highly efficient, Hash index itself, due to its particularity, also brings many limitations and disadvantages, mainly including the following.
(1) MySQL Hash index can only satisfy "=","IN" and" < = > "Query, cannot use range query.
Since the MySQL Hash index compares the Hash value after the Hash operation, it can only be used for equivalent filtering and cannot be used for range-based filtering, because the size of the Hash value processed by the corresponding Hash algorithm cannot be guaranteed to be exactly the same as that before the Hash operation.
(2) MySQL Hash index cannot be used to avoid data sorting operations.
Since the MySQL Hash index contains the Hash value calculated by Hash, and the size relation of Hash value is not exactly the same as the key value before Hash operation, the database cannot use the data of the index to avoid any sort operation.
(3) MySQL Hash index cannot use partial index key to query.
For composite index, Hash index computes the Hash value from 1 after combining the composite index keys, instead of calculating the Hash value separately. Therefore, the Hash index cannot be utilized when the first or several index keys of the composite index are used for query.
(4) MySQL Hash index cannot avoid table scanning at any time.
Already know, Hash index is the index key, through calculation Hash Hash computation results Hash values and the corresponding row pointer information stored in a Hash table, due to the different index key Hash same values, so even if get satisfy a Hash key data on the number of records and cannot complete the query directly from Hash index, or by accessing the actual data in the table are compared, and the corresponding and the corresponding results are obtained.
(5) The performance of MySQL Hash index is not guaranteed to be higher than B-ES70en index when a large number of Hash values are equal.
For low selectivity index keys, if the Hash index is created, there will be a large amount of record pointer information stored with 1 Hash value. This can be cumbersome to locate a single record, resulting in wasted access to table data and poor overall performance.