MySQL index type btree the difference between index and hash index

SOURCE One

Hash index structure of the particularity, its retrieval efficiency is very high, index retrieval can be located at once, unlike B-tree index need from the root node to the side point, and finally access to the page node so many IO access, so the Hash index query efficiency is much higher than the B-tree index.

Probably a lot of people have doubts, since the efficiency of the hash index is much higher than b-tree, why do we not all use hash index and also use B-tree index? Everything has two sides, hash index is the same, although the hash index is high efficiency, but the hash index itself due to its particularity also brought a lot of limitations and drawbacks, mainly have the following.

(1) Hash index can only meet "=", "in" and "<=>" query, can not use range query.

Because the hash index comparison is the hash value after the hash operation, so it can only be used for the equivalent of filtering, can not be used for range-based filtering, because the corresponding hash algorithm after processing the hash value of the size of the relationship, and can not be guaranteed and hash before the exact same.

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

Because the hash index is stored in the hash after the hash value, and the size of the hash value is not necessarily the same as the key value before the hash operation, so the database can not use the index data to avoid any sorting operations;

(3) Hash index cannot use partial index key query.

For the composite index, the hash index in the calculation of the hash value when the combination index key merge and then calculate the hash value together, rather than calculate the hash value alone, so by combining the index of the previous or several index key query, the Hash index can not be exploited.

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

As already known, the hash index is the index key through the hash operation, the hash value of the result of hashing and the corresponding line pointer information stored in a hash table, because the different index keys exist the same hash value, so even if the number of data that satisfies a hash key value of the record bar, also can not The query is completed directly from the Hash index, or the actual data in the table is accessed, and the corresponding results are obtained.

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

For low-selectivity index keys, if a hash index is created, then there will be a large number of record pointer information associated with the same hash value. This can be very cumbersome to locate a record, wasting multiple table data access and resulting in poor overall performance.

SOURCE Two

1. Hash index lookup data is basically able to locate the data at once, of course, there are a lot of collisions performance will also decline. And the Btree index will have to be found on the node next to each other, it is obvious that the efficiency of hash index is higher than btree in the data exact search;

2. So imprecise search, it is also obvious, because the hash algorithm is based on the equivalent calculation, so for "like" and other scopes to find the hash index is invalid, not supported;

3. For the optimal prefix of Btree supported federated indexes, Hash is also not supported, and the fields in the federated index are either full or unused. Mention the best prefix incredibly all appear confused, it seems sometimes empty too bad;

4. Hash does not support index sorting, the index value and calculated hash value is not necessarily the same size.

Description of the manual address: http://dev.mysql.com/doc/refman/5.6/en/create-index.html

MySQL index type btree the difference between index and hash index