Comparison of Btree and Hash indexes in Mysql, mysqlbtreehash

Comparison of Btree and Hash indexes in Mysql, mysqlbtreehash

The most common index structure of mysql is btree (O (log (n), but there are always some situations where we want to use other types of indexes for better performance. Hash is one of the options. For example, when we retrieve user IDs through user names, they always have a one-to-one relationship. The operator used is only =. If we use hash as the index data structure, the time complexity can be reduced to O (1 ). Unfortunately, in the current mysql version (5.6), hash only supports MEMORY and NDB engines, while our most commonly used INNODB and MYISAM do not support hash indexes.

In any case, you still need to understand the differences between the two indexes, which are translated from the mysql official documentation below.

B-Tree index features

B-Tree indexes can be used on comparison operators such as =,>, >=, <, <=, and. It can also be used for the LIKE operator as long as its query condition is a constant that does not start with a wildcard. Indexes can be used as follows:
Copy codeThe Code is as follows:
SELECT * FROM tbl_name WHERE key_col LIKE 'Patrick % ';
SELECT * FROM tbl_name WHERE key_col LIKE 'pat % _ ck % ';

In the following two cases, indexes are not used:
Copy codeThe Code is as follows:
SELECT * FROM tbl_name WHERE key_col LIKE '% Patrick % ';
SELECT * FROM tbl_name WHERE key_col LIKE other_col;

The first is because it starts with a wildcard, and the second is because no constant is used.

If you use... LIKE '% string %' and the string contains more than three characters. MYSQL uses the Turbo Boyer-Moore algorithm to initialize the query expression, and then uses this expression to make the query faster.

For such a query, col_name is null can use the index of col_name.

Any index that does not cover all where and-level conditions will not be used. That is to say, to use an index, the first column of the index must appear in each AND group.

The following WHERE condition uses an index:
Copy codeThe Code is as follows:
... WHERE index_part1 = 1 AND index_part2 = 2 AND other_column = 3
/* Index = 1 OR index = 2 */
... WHERE index = 1 or a = 10 AND index = 2
/* Optimized to "index_part1 = 'hello '"*/
... WHERE index_part1 = 'hello' AND index_part3 = 5
/* Indexes of index1 can be used, but index2 and index3 are not used */
... WHERE index1 = 1 AND index2 = 2 OR index1 = 3 AND index3 = 3;

The following WHERE condition does not use an index:

Copy codeThe Code is as follows:
/* Index_part1 is not used */
... WHERE index_part2 = 1 AND index_part3 = 2

/* Index does not appear in each where clause */
... WHERE index = 1 or a = 10

/* No indexes cover all columns */
... WHERE index_part1 = 1 OR index_part2 = 10

Sometimes mysql does not use indexes even if they are available. For example, when mysql estimates that the index will read most of the row data. (In this case, a full table scan may be faster than using an index because it requires less retrieval ). However, if the statement uses LIMIT to LIMIT the number of returned rows, mysql uses an index. Because the efficiency of using indexes is higher when the number of results rows is small.

Hash index features

Hash indexes are different from the features described above:

1. They can only be used for peer-to-peer comparison, such as the = and <=> operators (but much faster ). They cannot be used for Range Query conditions such as <. If the system only needs to use a storage structure like "key-value pairs", try to use hash indexes.
2. The optimizer cannot use hash indexes to accelerate the order by operator. (These indexes cannot be used to search for values in the next order)
3. mysql cannot determine the number of data entries between two values (this requires the range query operator to determine which index to use ). If you convert a MyISAM table into a MEMORY table that relies on hash indexes, some statements (performance) may be affected ).
4. Only the complete key can be used to search for a row of data. (If the B-tree index is used, any segment of a key can be used for searching. I think it may mean that the LIKE operator with wildcard characters does not work ).

Postscript

By the way, record some problems encountered during mysql usage:

Sometimes you may encounter garbled characters when using scripts to migrate data. Even setting the table character set to utf8 does not help. In this case, add a set names utf8 statement before executing the SQL statement.