Be alert to InnoDB and MyISAM Hash index creation traps
MySql common storage engines InnoDB and MyISAM do not support Hash indexes. Their default indexes are B-Tree. However, if you define the index type as Hash when creating the index, MySql will not report an error, and you can view that the index is also Hash through show create table, but the index is actually B-Tree.
For example, the DDL table data_dict:
Create table 'data _ dict '('data _ type' varchar (32) not null comment 'data dictionary type', 'Data _ Code' tinyint (4) not null comment 'data dictionary Code', 'Data _ name' varchar (64) not null comment 'data dictionary value', primary key ('data _ type ', 'Data _ Code') ENGINE = InnoDB default charset = utf8 COMMENT = 'data dictionary table ';
We create a Hash index for the data_name field:
ALTER TABLE data_dict ADD INDEX data_dict_dn USING HASH (data_name);
Print result:
Affected rows: 0
Time: 0.345 s
View the table creation DDL:
SHOW CREATE TABLE data_dict;
Print result:
Create table 'data _ dict '(
'Data _ type' varchar (32) not null comment 'data dictionary type ',
'Data _ Code' tinyint (4) not null comment 'data dictionary Code ',
'Data _ name' varchar (64) 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'
It is Hash, so we thought the Hash index was created successfully.
In fact, this is not the case. We are all cheated by MySql. We use the show indexes from statement to retrieve the table index:
SHOW INDEXES FROM data_dict;
Print result:
Back to the original shape. But don't be disappointed. Although common storage engines do not support Hash indexes, InnoDB has another implementation method: adaptive Hash indexes. The InnoDB Storage engine monitors the query of table indexes. If it is observed that creating a hash index can increase the speed, a hash index is created.
We can use show engine innodb status to view the current usage of the adaptive hash index:
============================================
10:51:19 1d68 INNODB MONITOR OUTPUT
============================================
Per second averages calculated from the last 36 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
......
We can see the information about the adaptive hash index, such as the size, usage, and the use of the adaptive hash index search per second.
For details about the features of various MySql storage engines, refer:
We can see that,
InnoDB supports transactions, row-level locking, B-tree, Full-text, and other indexes, but does not support Hash indexes. MyISAM does not support transactions and table-level locking, supports indexes such as B-tree and Full-text, but does not support Hash indexes. Memory does not support transactions, table-level locking, and B-tree and Hash indexes, full-text indexes are not supported. NDB supports transactions, row-level locking, Hash indexes, and B-tree and Full-text indexes. Archive does not support transactions, table-level locking is supported. indexes such as B-tree, Hash, and Full-text are not supported. You can use the SHOW ENGINES statement to view the storage engine supported by your MySql Server, for example, the result of 5.6.25-log Win used for local testing is as follows:
It can be seen that InnoDB is the default storage engine of this version of MySql, and only InnoDB can support transactions, row-level locking, and foreign keys. The supported MEMORY is based on hash, data is stored in the memory, which is applicable to temporary tables. No NDB supports transactions and hash indexes.
