MySQL8.0的InnoDB和MyISAM是否支持hash索引

今天和同学探讨说MySQL哪些存储引擎支持hash索引，因为所看书籍MySQL版本和现有的MySQL版本有出入，故中间出了点歧义。所以就手动敲了一下代码，测试了一下MySQL8.0中的存储引擎是如何支持hash索引的。

我们都知道MySQL最常用的存储引擎为InnoDB和MyISAM。它们默认的存储引擎都是B-Tree(实质为B+Tree)。他们本身都是不支持hash索引的。但是我们在建表时给某些字段添加hash索引，或者后期为某表添加hash索引时，如果他们的存储引擎为InnoDB或MyISAM，则sql脚本本身是不会报错的，但是我们会发现，该hash索引字段的index_type仍然为BTREE。如果存储引擎为Memory,因为这个引擎本是就是支持hash索引的，索引其hash索引字段的index_type自然为HASH。OK，say is one thing,coding is another thing.展示例子如下：

1、InnoDB测试用例：

已有users表，存储引擎为InnoDB

1.1、为useraddress字段添加hash索引：

alter table users add index idx_hash_userAddress using hash(useraddress);

1.2、查看表定义：

show create table users;

结果如下：

CREATE TABLE `users` (
  `id` int(7) NOT NULL AUTO_INCREMENT COMMENT '用户ID',
  `userName` varchar(50) DEFAULT NULL COMMENT '用户名',
  `userAge` int(11) DEFAULT NULL COMMENT '年龄',
  `userAddress` varchar(200) DEFAULT NULL COMMENT '家庭住址',
  `state_id` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `idx_state_id` (`state_id`),
  KEY `idx_hash_userAddress` (`userAddress`),
  FULLTEXT KEY `userAddress` (`userAddress`,`userName`)
) ENGINE=InnoDB AUTO_INCREMENT=2016000010 DEFAULT CHARSET=utf8

可以看到我们刚刚创建的hash索引，后面并没有using hash，所以依然是B-Tree索引。

1.3、查看表的索引：

show index from users;

同时，我们还能发现InnoDB8.0是支持全文索引的，即为index_type为FULLTEXT的索引。

2、MyISAM测试用例：

2.1、建表：

create table testHashInMyisam(
 views char(5) not NULL
)engine=myisam

2.2、查看表定义：

show create table testHashInMyisam;

结果如下：

CREATE TABLE `testHashInMyisam` (
  `views` char(5) NOT NULL
) ENGINE=MyISAM DEFAULT CHARSET=utf8

2.3、为表添加hash索引：

alter table testHashInMyisam add index idx_hash_views using hash(views);

2.4、查看表定义：

show create table testHashInMyisam;

结果如下：

CREATE TABLE `testHashInMyisam` (
  `views` char(5) NOT NULL,
  KEY `idx_hash_views` (`views`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8

2.5、查看表索引：

show index from testHashInMyisam

3、Memory测试用例：

3.1、建表：

create table testHashInMemory(
 views char(5) not NULL
)engine=Memory

3.2、查看建表语句：

show create table testHashInMemory;

结果如下：

CREATE TABLE `testHashInMemory` (
  `views` char(5) NOT NULL
) ENGINE=MEMORY DEFAULT CHARSET=utf8

3.3、给表添加hash索引：

alter table testHashInMemory add index idx_hash_views using hash(views);

3.4、查看表定义：

show create table testHashInMemory;

结果如下：

CREATE TABLE `testHashInMemory` (
  `views` char(5) NOT NULL,
  KEY `idx_hash_views` (`views`) USING HASH
) ENGINE=MEMORY DEFAULT CHARSET=utf8

3.5、查看表的索引：

show index from testHashInMemory;

不过也不要失望，虽然常见存储引擎并不支持 Hash 索引，但 InnoDB 有另一种实现方法：自适应哈希索引。InnoDB 存储引擎会监控对表上索引的查找，如果观察到建立哈希索引可以带来速度的提升，则建立哈希索引。

我们可以通过 SHOW ENGINE INNODB STATUS 来查看当前自适应哈希索引的使用状况.