Differences between MySQL BTREE index and hash index

  In the use of MySQL When indexing , choice b-tree still hash

hash Index can only satisfy "=","IN" and "<=>" Inquire about , Can't use range query . such as < , because Hash Index comparison is to carry out Hash After the operation Hash value , So it can only be used for equivalent filtering , Can't be used for range based filtering , Because after the corresponding Hash After algorithm processing Hash The relationship between the size of the value , There's no guarantee of and Hash It's exactly the same before the operation

For composite indexes ,Hash The index is calculating Hash When the value is combined, the index key is combined and then calculated together Hash value , Not separately Hash value , So when you query by combining one or more index keys in front of the index ,Hash Index can't be used

Hash Index is to pass the index key through Hash After the operation , take Hash The result of the calculation Hash The value and the corresponding row pointer information are stored in a Hash In the table , Because different index keys are the same Hash value , So even if you meet someone Hash Number of records of key value data , I can't go from Hash Direct query in index , You still need to access the actual data in the table for corresponding comparison , And get the corresponding result

Hash Index encountered a lot of Hash When the values are equal, the performance is not necessarily better than B-Tree High index .

B-Tree The index can be used in images =,>,>=,<,<= and BETWEEN On these comparison operators . And it can also be used for LIKE The operator , As long as its query condition is a constant that does not start with a wildcard

innodb and myisam The storage engine cannot be used hash Indexes .........