MySQL Hash Index VS btree index

MySQL AHI (Adaptive Hash Index): No sacrifice of any of the features and reliability of things;

According to the matching pattern of the search, MySQL uses the first half of the B-tree index key (the part that can be found with Btree index) to set up the hash index arbitrarily. Hash index establishes hashindex only for index in page with high frequency of access

If the data of a table is all in memory, hash index can speed up the query speed; InnoDB itself has the mechanism to monitor the frequency of index queries; the performance of query by hash index is much higher than that of monitoring index query frequency and maintaining hash index structure overhead. For multiple concurrent connections, Read/write lock creates a competitive lock on the hash index and, when there is a partial where column like pattern, the adaptive hash is not appropriate and is recommended for shutdown. It is also difficult to predict whether such features are suitable for such a particular application scenario.

For the use of the InnoDB Adaptive Hash Section:

You can use the show engine InnoDB status\g semaphores section to see if there are a lot of thread in the waiting on a rw-latch created in btr0sea.c, this time the adaptive hash is more than More appropriate.

Comparison of B-tree and Hash index:

Understanding B-tree and Hash data structures is useful for predicting the performance of queries on different storage engines (using the index of these structures), especially for memory storage engines;

Characteristics of B-tree Index:

The B-tree index can be used for =,>,>=,<,<= and between calculations, and can also be used for queries like matching patterns;

SELECT * from Tbl_name WHERE key_col like ' patrick% '; (You can use an index)

SELECT * from Tbl_name WHERE key_col like ' pat%_ck% '; (You can also use indexes)

SELECT * from Tbl_name WHERE key_col like '%patrick% '; (no index is used)

For the third query, MySQL uses the turbo Boyer-moore algorithm to initialize the pattern for faster lookups.

If a child is col_name indexed, col_name is null takes an index.

The Btree index tends to the leftmost principle, especially in the where and condition where the first field must be referenced;

The following example sentences are used in the index:

WHERE index_part1=1 and index_part2=2 and other_column=3

WHERE index=1 or a=10 and index=2 (can use INDEX1 or INDEX2)

WHERE index_part1= ' Hello ' and index_part3=5

WHERE index1=1 and index2=2 or index1=3 and index3=3; (You can use index1, but not necessarily index2,index3)

The following examples do not use the index:

WHERE index_part2=1 and index_part3=2; (Index_part1 not used)

WHERE index=1 OR a=10 (no index is used)

WHERE index_part1=1 OR index_part2=10 (no index is used)

Special case: Some cases MySQL will not use the index, although the query fields are indexed,

When the MySQL optimizer estimates (optimization model) uses the index to scan most rows, the table scan scan may reduce costs because of fewer lookups. At this point, if you use the Limit statement to request a certain amount of rows this will use the index.

Hash index feature:

Can only be equivalent operations, can not be < or range lookup operations, which is more suitable for the KV type of data,

You cannot use the hash key for an order by statement, which can be done in a program.

MySQL is not sure how much rows there are between two values in general. Attention should be paid to the MyISAM conversion to the memory engine.

The key values for the index must all be used, not the first half as Btree.

This article is from the "Technology Achievement Dream" blog, please be sure to keep this source http://weipengfei.blog.51cto.com/1511707/1211579

See more highlights of this column: http://www.bianceng.cnhttp://www.bianceng.cn/database/MySQL/