mysql memory 索引_memory型表支持Hash索引

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)

觉得文章有用？立即：

和朋友一起 共学习 共进步！

猜您喜欢