今天有朋友问题,MEMORY 引擎的表查询速度竟然比MYISAM引擎慢!
熟读手册后,你就不用有这样的疑问了。

我们来小解决下。
示例表结构:
create table t1_memory (
id int unsigned not null auto_increment primary key,
a1 decimal(15,12),
a2 decimal(15,12),
remark varchar(200) not null,
key idx_u1 (a1,a2)
) engine memory;

create table t1_myisam (
id int unsigned not null auto_increment primary key,
a1 decimal(15,12),
a2 decimal(15,12),
remark varchar(200) not null,
key idx_u1 (a1,a2)
) engine myisam;
示例SQL语句:
select * from t1_memory where a1>110 and a1<111 and a2>23 and a2<24;
select * from t1_myisam where a1>110 and a1<111 and a2>23 and a2<24;

语句执行计划:
explain
select * from t1_memory where a1>110 and a1<111 and a2>23 and a2<24;

query result

id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE t1_memory ALL idx_u1 (NULL) (NULL) (NULL) 3000 Using where

explain
select * from t1_myisam where a1>110 and a1<111 and a2>23 and a2<24;

query result

id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE t1_myisam range idx_u1 idx_u1 9 (NULL) 1 Using where

根本原因就是默认MEMORY 引擎采用HASH索引, 所以对于RANGE INDEX 来说,我们要修改成BTREE索引。
解决办法:
变化索引类型
alter table t1_memory drop key idx_u1, add key idx_u1 using btree (a1,a2);

优化后执行计划:
explain
select * from t1_memory where a1>110 and a1<111 and a2>23 and a2<24;

query result

id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE t1_memory range idx_u1 idx_u1 9 (NULL) 2 Using where

看到了吧,咱也用上了索引。哈哈。