mysql> create table test_memory
-> (i smallint not null auto_increment,
-> name varchar(10),primary key(i))
-> engine=memory charset=gbk;
Query OK, 0 rows affected (0.01 sec)
mysql> SHOW INDEX FROM test_memory;
+————-+————+———-+————–+————–+——–+——+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment |
+————-+————+———-+——————+———-+——–+——+
| test_memory | 0 | PRIMARY | 1 | i | NULL | 0 | NULL | NULL | | HASH | |
+————-+————+———-+——————+———-+——–+——+-
1 row in set (0.02 sec)
memory型表创建的PK，默认的就是hash索引
mysql> explain select * from test_memory where i>1 /G;
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: test_memory
type: ALL
possible_keys: PRIMARY
key: NULL
key_len: NULL
ref: NULL
rows: 2
Extra: Using where
1 row in set (0.04 sec)
mysql> explain select * from test_memory where i=1 /G;
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: test_memory
type: const
possible_keys: PRIMARY
key: PRIMARY
key_len: 2
ref: const
rows: 1
Extra:
1 row in set (0.01 sec)
从这里两个对比中发现，hash index支持”=”等式查询，不支持”>=”,”<=”,”<”,”>”,”<>”,”between”,”!=”,”like”
hash索引的创建：
mysql> create table test_memory_sp
-> (i smallint not null,
-> name varchar(10))
-> engine=memory charset=gbk;
Query OK, 0 rows affected (0.10 sec)
mysql> create index ind_hash using hash on test_memory_sp(i);
Query OK, 0 rows affected (0.02 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> insert into test_memory_sp values(1,’A');
Query OK, 1 row affected (0.00 sec)
mysql> insert into test_memory_sp values(1,’B');
Query OK, 1 row affected (0.00 sec)
mysql> insert into test_memory_sp values(2,’C');
Query OK, 1 row affected (0.00 sec)
mysql> explain select * from test_memory_sp where i=1 /G;
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: test_memory_sp
type: ref
possible_keys: ind_hash
key: ind_hash
key_len: 2
ref: const
rows: 2
Extra:
1 row in set (0.00 sec)