MYSQL common storage engine InnoDB and MyISAM do not support Hash indexes, their default indexes are b-tree. But if you define a type of hash,mysql when you create an index, you don't get an error, and you see that index through show create TABLE is also a Hash, except that the index is actually b-tree.
For example, the DDL for table data_dict:
CREATE TABLE ' data_dict ' ( ' data_type ' varchar (+) NOT null COMMENT ' data dictionary type ', ' data_code ' tinyint (4) NOT NULL COMM ENT ' Data dictionary code ', ' data_name ' varchar (UP) not NULL COMMENT ' data dictionary value ', PRIMARY KEY (' data_type ', ' Data_code ')) engine= InnoDB DEFAULT Charset=utf8 comment= ' data dictionary table ';
We establish a Hash index for the Data_name field:
ALTER TABLE data_dict ADD INDEX data_dict_dn USING HASH (data_name);
Printing results:
Rows affected: 0
Time: 0.345s
Then view the build table DDL:
SHOW CREATE TABLE data_dict;
Printing results:
CREATE TABLE ' data_dict ' (
' data_type ' varchar (+) not NULL COMMENT ' data dictionary type ',
' Data_code ' tinyint (4) Not NULL COMMENT ' data dictionary code ',
' Data_name ' varchar (+) not NULL COMMENT ' data dictionary value ',
PRIMARY KEY (' data_type ', ' Data_code '),
KEY ' data_dict_dn ' (' data_name ') USING HASH
) Engine=innodb DEFAULT Charset=utf8 comment= ' data dictionary table '
is hash, so we thought the hash index was created successfully.
In fact, we are all fooled by MYSQL, we use the SHOW INDEXES from statement to retrieve the table index:
SHOW INDEXES from Data_dict;
Printing results:
Back to the prototype. 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 view the usage of the current adaptive hash index through show ENGINE INNODB status:
=====================================
2015-07-07 10:51:19 1d68 INNODB MONITOR OUTPUT
=====================================
Per second averages calculated from the last seconds
......
-------------------------------------
INSERT BUFFER and ADAPTIVE HASH INDEX
-------------------------------------
Ibuf:size 1, free list len 2633, seg size 2635, 0 merges
Merged operations:
Insert 0, delete mark 0, delete 0
Discarded operations:
Insert 0, delete mark 0, delete 0
Hash table Size 348731, node heap has 2 buffer (s)
0.00 Hash SEARCHES/S, 0.00 Non-hash SEARCHES/S
......
From here we can see information about the Adaptive Hash Index: Usage size, usage, use of adaptive hash index searches per second, and more.
MYSQL various storage engine features comparison of the detailed list:
From what we can see,
- InnoDB supports transactions, supports row-level locking, supports indexes such as B-tree, Full-text, and does not support Hash indexing;
- MyISAM does not support transactions, supports table-level locking, supports indexes such as B-tree, Full-text, and does not support Hash indexing;
- Memory does not support transactions, support table-level locking, Support B-tree, Hash and other indexes, do not support Full-text index;
- NDB support transactions, support row-level locking, Support Hash index, do not support B-tree, full-text and other indexes;
- Archive does not support transactions, support table-level locking, does not support B-tree, Hash, Full-text and other indexes;
You can use the show ENGINES statement to view the storage engines supported by your MYSQL Server, such as the results of the 5.6.25-log Win version of the author's test for native tests as follows:
As you can see, InnoDB is the default storage engine for this version of MYSQL, and only InnoDB can support transactions, row-level locking, foreign keys, supported memory is hash-based, and data is stored in RAM for temporary tables. You do not see a NDB that supports both transactional and hash indexes.
Resources
- Http://dev.mysql.com/doc/refman/5.6/en/storage-engines.html
- Http://dba.stackexchange.com/questions/2817/why-does-mysql-not-have-hash-indices-on-myisam-or-innodb
Copyright NOTICE: This article for Bo Master original article, without Bo Master permission not reproduced.
Beware of InnoDB and MyISAM creating Hash index Traps
