Whether InnoDB and MyISAM support hash indexing

Today and classmates talk about MySQL which storage engine support hash index, because the MySQL version of the book and the existing MySQL version of the discrepancy, so there is a bit of ambiguity. So just hit the code manually and test how the storage engine in MySQL8.0 supports the hash index.

We all know that MySQL's most commonly used storage engine is InnoDB and MyISAM. Their default storage engine is B-tree (in essence, b+tree). They are not supported by hash indexes themselves. However, if we add a hash index to some fields when we are building a table, or when we add a hash index to a table later, if their storage engine is InnoDB or MyISAM, the SQL script itself will not give an error, but we will find that the hash index field index_ Type is still btree. If the storage engine is memory, because this engine is to support the hash index, the index of its hash index field Index_type natural hash. Ok,say is one thing,coding is another thing. Examples are shown below:

1. InnoDB Test Case:

Already have users table, the storage engine is InnoDB

1.1. Add a hash index to the useraddress field:

Alter Table Add index idx_hash_useraddress using hash (useraddress);

1.2. View the table definition:

Create table users;

The results are as follows:

CREATE TABLE' users ' (' ID 'int(7) not NULLAuto_increment COMMENT'User ID', ' UserName 'varchar( -)DEFAULT NULLCOMMENT'User name', ' Userage 'int( One)DEFAULT NULLCOMMENT'Age', ' useraddress 'varchar( $)DEFAULT NULLCOMMENT'Home Address', ' state_id 'int( One)DEFAULT NULL,        PRIMARY KEY(' id '),KEY' idx_state_id ' (' state_id '),KEY' idx_hash_useraddress ' (' useraddress '), FulltextKEY' useraddress ' (' useraddress ', ' userName ')) ENGINE=InnoDB auto_increment=2016000010 DEFAULTCHARSET=Utf8

You can see the hash index we just created, and there is no using hash behind it, so it is still the B-tree index.

1.3. View the index of the table:

Index  from

At the same time, we can also find that InnoDB8.0 is supported for full-text indexing, that is, Index_type is the fulltext index.

2. MyISAM Test Case:

2.1, build the table:

Create Table Testhashinmyisam (        char(5notNULL    ) engine= 

2.2. View the table definition:

Create table Testhashinmyisam;

The results are as follows:

CREATE TABLE ' Testhashinmyisam ' (        char(5notNULL    ) ENGINE=  DEFAULT CHARSET=

2.3. Add a hash index to the table:

Alter Table Add index idx_hash_views using hash (views);

2.4. View the table definition:

Create table Testhashinmyisam;

The results are as follows:

CREATE TABLE ' Testhashinmyisam ' (    char(5notNULL,    KEY  ' idx_hash_views ' (' views ')) ENGINE=DEFAULT CHARSET=UTF8

2.5. View Table Index:

Index  from Testhashinmyisam

3. Memory test Case:

3.1, build the table:

Create Table testhashinmemory (        char(5notNULL    ) engine= Memory

3.2. View the statement of the build table:

Create table testhashinmemory;

The results are as follows:

CREATE TABLE ' Testhashinmemory ' (        char(5notNULL    ) ENGINE=  DEFAULT CHARSET=UTF8

3.3. Add a hash index to the table

Alter Table Add index idx_hash_views using hash (views);

3.4. View the table definition:

Create table testhashinmemory;

The results are as follows:

CREATE TABLE ' Testhashinmemory ' (        char(5notNULL,        KEY  ' idx_hash_views ' (' views ') USING hash    ) ENGINE=DEFAULT CHARSET= UTF8

3.5. View the index of the table:

Index  from Testhashinmemory;

But don't be disappointed, though the common storage engine does not support hash indexing, there is another way to implement InnoDB: adaptive hash Indexing. The InnoDB storage engine monitors the lookup of indexes on the table and establishes a hash index if a hash index is observed to increase the speed.

We can see the current usage of the adaptive Hash Index through show ENGINE INNODB status

Whether InnoDB and MyISAM support hash indexing