The pros and cons of Btree and hash in the index method of MySQL indexes

Tag: Based on action blank SQL cannot correlate efficiency scan LAN

The difference between btree and hash in the index method of MySQL indexes

  in MySQL, most indexes (such as PRIMARY Key,unique,index and fulltext) are stored in Btree, but the memory engine allows you to select Btree Index or hash index. Two different types of indexes have their own different scopes of use.  hash index structure of the particularity, its retrieval efficiency is very high, index retrieval can be located at once, unlike the B-tree index needs 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.   may be 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 the hash index and also use the 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 indexes can only satisfy "=", "in" and "<=>" queries, and cannot use range queries.   Because the hash index comparison is the hash value after the hash operation, so it can only be used for the equivalent filtering, not for the range-based filtering, because after the corresponding hash algorithm processing 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 on 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 indexed data to avoid any sort operations;  (3) Hash Indexes cannot be queried with partial index keys.   for composite index, hash index when calculating the hash value is the combination index key merge and then calculate the hash value together, rather than calculate the hash value separately, so through the combination index of the previous or several index key query, the hash index can not be exploited.   (4) Hash index cannot avoid table scanning at any time.   already know that 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 a hash key value to meet the data record and cannot be queried directly from the Hash index,The corresponding comparisons are made by accessing the actual data in the table, and the corresponding results are obtained.   (5) When a hash index encounters a lot of equal hash values, performance is not necessarily higher than the B-tree index.   for low-selectivity index keys, if you create a hash index, there will be a large number of record pointer information that is 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.

Advantages and disadvantages of btree and hash in the index method of the MySQL index