MySQL内存表使我们经常会用到的，但是MySQL内存表的也不是提高读性能的***工具，在有些情况下，MySQL内存表可能会比其实表类型的B-TREE更慢。

CREATETABLE`mem_test`( 
`id`int(10)unsignedNOTNULLDEFAULT'0', 
`name`varchar(10)DEFAULTNULL, 
`first`varchar(10)DEFAULTNULL, 
PRIMARYKEY(`id`), 
KEY`NewIndex1`(`name`,`first`) 
)ENGINE=MEMORY; 

CREATETABLE`innodb_test`( 
`id`int(10)unsignedNOTNULLDEFAULT'0', 
`name`varchar(10)DEFAULTNULL, 
`first`varchar(10)DEFAULTNULL, 
PRIMARYKEY(`id`), 
KEY`NewIndex1`(`name`,`first`) 
)ENGINE=InnoDB; 

如： 1：在= 或者<=> 情况下，飞快，但是在如< 或>情况下，他是不使用索引

mysql--root@localhost:17db07:33:45>>explainselect*frommem_testwhereid>3; 
+----+-------------+----------+------+---------------+------+---------+------+------+-------------+ 
|id|select_type|table|type|possible_keys|key|key_len|ref|rows|Extra| 
+----+-------------+----------+------+---------------+------+---------+------+------+-------------+ 
|1|SIMPLE|mem_test|ALL|PRIMARY|NULL|NULL|NULL|15|Usingwhere| 
+----+-------------+----------+------+---------------+------+---------+------+------+-------------+ 
1rowinset(0.00sec) 

mysql--root@localhost:17db07:33:49>>explainselect*frominnodb_testwhereid>3; 
+----+-------------+-------------+-------+---------------+---------+---------+------+------+-------------+ 
|id|select_type|table|type|possible_keys|key|key_len|ref|rows|Extra| 
+----+-------------+-------------+-------+---------------+---------+---------+------+------+-------------+ 
|1|SIMPLE|innodb_test|range|PRIMARY|PRIMARY|4|NULL|7|Usingwhere| 
+----+-------------+-------------+-------+---------------+---------+---------+------+------+-------------+ 
1rowinset(0.00sec) 

2：不能用在order by情况下来提高速度

mysql--root@localhost:17db07:33:55>>explainselect*frominnodb_testorderbyid; 
+----+-------------+-------------+-------+---------------+---------+---------+------+------+-------+ 
|id|select_type|table|type|possible_keys|key|key_len|ref|rows|Extra| 
+----+-------------+-------------+-------+---------------+---------+---------+------+------+-------+ 
|1|SIMPLE|innodb_test|index|NULL|PRIMARY|4|NULL|15|| 
+----+-------------+-------------+-------+---------------+---------+---------+------+------+-------+ 
1rowinset(0.00sec) 

mysql--root@localhost:17db07:34:27>>explainselect*frommem_testorderbyid; 
+----+-------------+----------+------+---------------+------+---------+------+------+----------------+ 
|id|select_type|table|type|possible_keys|key|key_len|ref|rows|Extra| 
+----+-------------+----------+------+---------------+------+---------+------+------+----------------+ 
|1|SIMPLE|mem_test|ALL|NULL|NULL|NULL|NULL|15|Usingfilesort| 
+----+-------------+----------+------+---------------+------+---------+------+------+----------------+ 
1rowinset(0.00sec) 

3：不能确定俩值之间有多少行

mysql--root@localhost:17db07:37:14>>explainselectcount(1)frommem_testwhereid>3andid<6; 
+----+-------------+----------+------+---------------+------+---------+------+------+-------------+ 
|id|select_type|table|type|possible_keys|key|key_len|ref|rows|Extra| 
+----+-------------+----------+------+---------------+------+---------+------+------+-------------+ 
|1|SIMPLE|mem_test|ALL|PRIMARY|NULL|NULL|NULL|20|Usingwhere| 
+----+-------------+----------+------+---------------+------+---------+------+------+-------------+ 
1rowinset(0.00sec) 

mysql--root@localhost:17db07:40:35>>explainselectcount(1)frominnodb_testwhereid>3andid<6; 
+----+-------------+-------------+-------+---------------+---------+---------+------+------+--------------------------+ 
|id|select_type|table|type|possible_keys|key|key_len|ref|rows|Extra| 
+----+-------------+-------------+-------+---------------+---------+---------+------+------+--------------------------+ 
|1|SIMPLE|innodb_test|range|PRIMARY|PRIMARY|4|NULL|1|Usingwhere;Usingindex| 
+----+-------------+-------------+-------+---------------+---------+---------+------+------+--------------------------+ 
1rowinset(0.00sec) 

4：在多列索引的情况下，只有全部指定才能利用hash扫描，而B-tree确可以利用索引的最左端来查找

mysql--root@localhost:17db07:37:07>>explainselect*frominnodb_testwherename='b'; 
+----+-------------+-------------+------+---------------+-----------+---------+-------+------+--------------------------+ 
|id|select_type|table|type|possible_keys|key|key_len|ref|rows|Extra| 
+----+-------------+-------------+------+---------------+-----------+---------+-------+------+--------------------------+ 
|1|SIMPLE|innodb_test|ref|NewIndex1|NewIndex1|33|const|8|Usingwhere;Usingindex| 
+----+-------------+-------------+------+---------------+-----------+---------+-------+------+--------------------------+ 
1rowinset(0.00sec) 

mysql--root@localhost:17db07:37:10>>explainselect*frommem_testwherename='b'; 
+----+-------------+----------+------+---------------+------+---------+------+------+-------------+ 
|id|select_type|table|type|possible_keys|key|key_len|ref|rows|Extra| 
+----+-------------+----------+------+---------------+------+---------+------+------+-------------+ 
|1|SIMPLE|mem_test|ALL|NewIndex1|NULL|NULL|NULL|20|Usingwhere| 
+----+-------------+----------+------+---------------+------+---------+------+------+-------------+ 

当然内存表也可以手动添加btree

CREATEINDEXBTREE_indexUSINGBTREEonmem_test(name,first) 
mysql--root@localhost:17db03:36:41>>explainselect*frommem_testwherename='b'; 
+----+-------------+----------+------+-----------------------+-------------+---------+-------+------+-------------+ 
|id|select_type|table|type|possible_keys|key|key_len|ref|rows|Extra| 
+----+-------------+----------+------+-----------------------+-------------+---------+-------+------+-------------+ 
|1|SIMPLE|mem_test|ref|NewIndex1,BTREE_index|BTREE_index|33|const|9|Usingwhere| 
+----+-------------+----------+------+-----------------------+-------------+---------+-------+------+-------------+ 
1rowinset(0.00sec) 

哈哈，它也用到索引了。 所以要选择合适的存储引擎至关重要。