only for equality comparisons Hash Index Characteristics

http://dev.mysql.com/doc/refman/5.7/en/index-btree-hash.html

Hash Index Characteristics

Hash indexes have somewhat different characteristics from those just discussed:

• They are used only for equality comparisons that use the = or <=> operators (but are very fast). They are not used for comparison operators such as <that find a range of values. Systems that rely on this type of single-value lookup are known as “key-value stores”; to use MySQL for such applications, use hash indexes wherever possible.

• The optimizer cannot use a hash index to speed up ORDER BY operations. (This type of index cannot be used to search for the next entry in order.)

• MySQL cannot determine approximately how many rows there are between two values (this is used by the range optimizer to decide which index to use). This may affect some queries if you change a MyISAM or InnoDB table to a hash-indexed MEMORY table.

• Only whole keys can be used to search for a row. (With a B-tree index, any leftmost prefix of the key can be used to find rows.)

 

 

 

app

0-Hash索引限相等比较：= 或<=>，但比B-Tree快

1-Hash索引对ORDER BY 无效

 

 

https://dev.mysql.com/doc/refman/5.7/en/glossary.html

B-tree

A tree data structure that is popular for use in database indexes. The structure is kept sorted at all times, enabling fast lookup for exact matches (equals operator) and ranges (for example, greater than, less than, and BETWEEN operators). This type of index is available for most storage engines, such as InnoDB and MyISAM.

Because B-tree nodes can have many children, a B-tree is not the same as a binary tree, which is limited to 2 children per node.

Contrast with hash index, which is only available in the MEMORY storage engine. The MEMORY storage engine can also use B-tree indexes, and you should choose B-tree indexes for MEMORY tables if some queries use range operators.

The use of the term B-tree is intended as a reference to the general class of index design. B-tree structures used by MySQL storage engines may be regarded as variants due to sophistications not present in a classic B-tree design. For related information, refer to the InnoDB Page Structure Fil Header section of the MySQL Internals Manual.

See Also hash index.

 

 

app

0-Hash索引仅可用于MEMORY storage engine.