MySQL：` `SHOW INDEX`显示与` `SHOW CREATE TABLE`不同
MySQL：` `SHOW INDEX`显示与` `SHOW CREATE TABLE`不同
提问于 2017-01-31 22:47:08
我试图理解为什么索引不是按照我预期的方式创建的。当我在现有的外键之上创建哈希索引时，我正在调查发生了什么。
create table users (id int, primary key(id));
create table temp (id int not null, primary key(id), user_id int(11) default null);现在还没有外键。show create table temp;输出您所期望的内容：
| temp | CREATE TABLE `temp` (
`id` int(11) NOT NULL,
`user_id` int(11) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 |和show index from temp
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| temp | 0 | PRIMARY | 1 | id | A | 0 | NULL | NULL | | BTREE | | |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+一切都很好。现在让我们添加一个FK：
alter table temp add foreign key key_name (user_id) references users (id);这向我们展示了show create table temp;
| temp | CREATE TABLE `temp` (
`id` int(11) NOT NULL,
`user_id` int(11) DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `key_name` (`user_id`),
CONSTRAINT `temp_ibfk_1` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 |和show index from temp;
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| temp | 0 | PRIMARY | 1 | id | A | 0 | NULL | NULL | | BTREE | | |
| temp | 1 | key_name | 1 | user_id | A | 0 | NULL | NULL | YES | BTREE | | |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+到目前为止一切顺利:我们添加了一个外键约束，MySQL自动添加了一个BTREE索引，它使用该索引来强制执行该约束。
现在我希望这个索引是一个散列，所以我要添加它：
create index index_name using hash on temp (user_id);这就是它变得奇怪的地方。当您按预期执行show create table temp时，可以看到KEY (...) USING HASH条目：
| temp | CREATE TABLE `temp` (
`id` int(11) NOT NULL,
`user_id` int(11) DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `index_name` (`user_id`) USING HASH,
CONSTRAINT `temp_ibfk_1` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 |但是，当您使用show index from temp;时
+-------+------------+------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+-------+------------+------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| temp | 0 | PRIMARY | 1 | id | A | 0 | NULL | NULL | | BTREE | | |
| temp | 1 | index_name | 1 | user_id | A | 0 | NULL | NULL | YES | BTREE | | |
+-------+------------+------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+什么都没变！而且索引仍然是BTREE！。
这里发生什么事情？
编辑:有人提醒我，也许在某种程度上，create index ...和alter table add index ...不一样。我试过了，他们肯定在做同样的事情。alter table temp add index (user_id) using hash通向
| temp | CREATE TABLE `temp` (
`id` int(11) NOT NULL,
`user_id` int(11) DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `index_name` (`user_id`) USING HASH,
KEY `user_id` (`user_id`) USING HASH,
CONSTRAINT `temp_ibfk_1` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 |
+-------+------------+------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+-------+------------+------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| temp | 0 | PRIMARY | 1 | id | A | 0 | NULL | NULL | | BTREE | | |
| temp | 1 | index_name | 1 | user_id | A | 0 | NULL | NULL | YES | BTREE | | |
| temp | 1 | user_id | 1 | user_id | A | 0 | NULL | NULL | YES | BTREE | | |
+-------+------------+------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+回答 1
Stack Overflow用户
回答已采纳
发布于 2017-01-31 22:55:03
InnoDB存储引擎仅支持BTREE索引，不支持HASH索引。
当您尝试创建HASH索引时，它会将索引静默地转换为BTREE。
页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接：
https://stackoverflow.com/questions/41960458
复制相关文章
相似问题
腾讯云开发者
