我正在努力理解为什么没有按照我预期的方式创建索引。我正在调查在现有外键上创建HASH索引时发生了什么。
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索引,用于强制执行该约束。
现在我希望这个索引是一个HASH,所以我添加它:
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 | | |
+-------+------------+------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
InnoDB存储引擎只支持BTREE
索引,不支持HASH
索引。
当您尝试创建HASH
索引时,它会静默地将索引转换为BTREE
。