Application and restrictions of MySQL Hash Indexes

This article mainly introduces the practical application of MySQL Hash indexes. We all know that in MySQL databases, only Memory storage engine displays support for hash indexes, which are the default index types of Memory tables, although Memory tables can also use B-Tree indexes.

The Memory storage engine supports non-unique MySQL hash indexes, which is rare in the database field. If multiple values have the same hash code, indexes store row pointers in the same hash table using a linked list.

Assume that the following table is created:

 
 

  
  
CREATE TABLE testhash (  
  
  
fname VARCHAR(50) NOT NULL,  
  
  
lname VARCHAR(50) NOT NULL,  
  
  
KEY USING HASH(fname)  
  
  
) ENGINE=MEMORY; 
 
 

The data contained is as follows:

Assume that the index uses the hash function f () as follows:

 
 

  
  
f('Arjen') = 2323  
  
  
f('Baron') = 7437  
  
  
f('Peter') = 8784  
  
  
f('Vadim') = 2458  
 
 

The index structure is roughly as follows:

Slots are sequential, but records are not sequential. When you execute

 
 

  
  
mysql> SELECT lname FROM testhash WHERE fname='Peter'; 
 
 

MySQL calculates the hash value of 'Peter 'and then queries the row pointer of the index. Because f ('Peter ') = 8784, MySQL searches for 8784 in the index and obtains the pointer pointing to record 3.

Because indexes only store very short values, the indexes are very compact. The Hash value does not depend on the Data Type of the column. The index of a TINYINT column is as large as that of a long string column.

Hash indexes have the following restrictions:

1) Because indexes only contain hash code and record pointers, MySQL cannot avoid reading records by using indexes. However, the access records in the memory are very fast and will not have a huge impact on the performance.

2) MySQL hash indexes cannot be used for sorting.

3) Hash indexes do not support partial key matching because the hash value is calculated using the entire index value.

4) Hash indexes only support equivalent comparison, for example, using =, IN () and <=>. WHERE price> 100 cannot accelerate queries.

 

The above content is an introduction to the MySQL hash index. I hope you will get some benefits.