The particularity of the Hash index structure, itsretrieval efficiency is very high, index retrieval can be located at once, unlike the B-tree index needs to be 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, the Hash index is the same,Although the hash index efficiency is high, but the hash index itself because of 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 is compared to the hash value after the hash operation, so it can only be used forfiltering of equivalentcannot be used for range-based filtering because the size of the hash value after the corresponding hash algorithm is not guaranteed to be exactly the same as before the hash operation.
(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,Therefore, the database cannot use the indexed data to avoid any sort operations;
(3) Hash index cannot use partial index key query.
For a composite index, the hash index calculates the hash value when the combination index key is combined and the hash value is calculated together, instead of the hash value alone, soThe Hash index cannot be exploited when querying with the previous or several index keys of the combined index.
(4)The Hash index cannot avoid table scans at any time.
As I have known before,Hash IndexAfter the index key is hashed, the hash value of the hash result and the corresponding line pointer information are stored in a hash table.because the same Hash value exists for different index keys，So even if we take the number of records that satisfy a hash key value, we can not complete the query directly from the hash index, or we should compare the actual data in the table, and get the corresponding result.
(5) When a hash index encounters a large number of equal hash values, performance is not necessarily higher than the B-tree index.
ForselectivityLower index keys, if you create a hash index, then there will be a large number of record pointer information stored in the same hash value associated. This can be very cumbersome to locate a record, wasting multiple table data access, resulting in poor overall performance
2. B-tree Index
The B-tree index is the most frequently used index type in a MySQL database, and all storage engines except the Archive storage engine support B-tree indexes. Not only in MySQL, but in many other database management systems, the B-tree index is also the most important index type, mainly because the storage structure of the B-tree index has a very good performance in data retrieval of the database.
In general, the physical files of the B-tree index in MySQL are mostly stored in the structure of the Balance tree, that is, all the data that is actually needed is stored in the leaf node of the tree, and the shortest path to any leaf node is exactly the same length The same, so we all call it B-tree index of course, it is possible that various databases (or MySQL's various storage engines) will slightly transform the storage structure when storing their own b-tree indexes. such as the B-tree index of the INNODB storage engine actually uses the storage structure is b+tree, that is, on the basis of the B-tree data structure made a small transformation, in each
The leaf node contains information about the index key, and it stores pointers to the last leafnode adjacent to the leaf node, primarily to speed up the efficiency of retrieving multiple neighboring leaf node.
In the INNODB storage engine, there are two different forms of indexes, one is the Cluster form of the primary key index (Primary key), the other is the same as other storage engines (such as the MyISAM storage engine) stored in the same general B-tree index, the index in the Inno The DB storage engine is known as secondary Index. Let's take a picture of how these two indexes are stored
form to make a comparison.
The Primary Key is stored as Clustered in the left side of the diagram, and the normal B-tree index is on the right. Both Root Node and Branch Nodes are exactly the same. And the Leaf Nodes there is a difference. In Prim, Leaf Nodes holds the actual data of the table, not only the data of the primary key field, but also the order of the data of the other fields in the primary key value. While secondary index is not much different from other common B-tree indexes, the Leaf Nodes contains information about the index key and stores the Innodb primary key value.
Therefore, in Innodb if access to data through the primary key is very high, and if the data is accessed through secondary index, INNODB first through the relevant information secondary index, through the corresponding index key to retrieve the Leaf node, It is necessary to retrieve the corresponding data row by the primary key value stored in Leaf Node and then through the primary key index. The primary key and non-primary key indexes of the MyISAM storage engine differ very little, except that the index key of the primary key index is a unique and non-null key. and the MyISAM Storage Engine index and INNODB of the secondary index storage structure is basically the same, the main difference is only MyISAM storage engine on the Leaf Nodes above the index key information, and then stored directly to the MyISAM data file Information in the corresponding data row (such as row number), but does not hold key value information for the primary key
The difference between MySQL's btree index and hash index
