Mysql如何创建hash类型索引

mysql> use test;
Database changed
mysql> create table test1(id int)engine=innodb;
Query OK, 0 rows affected (0.02 sec)

创建一个innodb存储引擎的表test

mysql> create index index_test using hash on test1(id);
Query OK, 0 rows affected (0.02 sec)
Records: 0  Duplicates: 0  Warnings: 0

创建索引using hash

mysql> show index from test1;
+-------+------------+------------+--------------+-------------+-----------+-------------+----------+--------+------+---
---------+---------+---------------+
| Table | Non_unique | Key_name   | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | In
dex_type | Comment | Index_comment |
+-------+------------+------------+--------------+-------------+-----------+-------------+----------+--------+------+---
---------+---------+---------------+
| test1 |          1 | index_test |            1 | id          | A         |           0 |     NULL | NULL   | YES  | BT
REE      |         |               |
+-------+------------+------------+--------------+-------------+-----------+-------------+----------+--------+------+---
---------+---------+---------------+
1 row in set (0.00 sec)

但是还是BTREE类型的,后来我去查原因,原来innodb不支持hash

 

 

 

我改用memory,创建成功

mysql> create table test2(id int)engine=memory;
Query OK, 0 rows affected (0.01 sec)

mysql> create index index_test using hash on test2(id);
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> show index from test2;
+-------+------------+------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table | Non_unique | Key_name   | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+-------+------------+------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| test2 |          1 | index_test |            1 | id          | NULL      |           0 |     NULL | NULL   | YES  | HASH       |         |               |
+-------+------------+------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
1 row in set (0.00 sec)
 

 

 

 

展开阅读全文

没有更多推荐了,返回首页