mysql内存表弊端
内存表的也不是提高读性能的万能工具，在有些情况下，可能会比其实表类型的B-TREE更慢
CREATE TABLE `mem_test` (? ?? ?? ?? ?? ?? ?? ?? ?? ?? ?? ?? ?? ?? ?? ?? ?? ?? ?? ?? ?
? ?? ?? ?? ?`id` int(10) unsigned NOT NULL DEFAULT '0',? ?? ?? ?? ?? ?? ?? ?? ?? ?? ?? ?? ?? ???
? ?? ?? ?? ?`name` varchar(10) DEFAULT NULL,? ?? ?? ?? ?? ?? ?? ?? ?? ?? ?? ?? ?? ?? ?? ?? ?? ?
? ?? ?? ?? ?`first` varchar(10) DEFAULT NULL,? ?? ?? ?? ?? ?? ?? ?? ?? ?? ?? ?? ?? ?? ?? ?? ?? ?
? ?? ?? ?? ?PRIMARY KEY (`id`),? ?? ?? ?? ?? ?? ?? ?? ?? ?? ?? ?? ?? ?? ?? ?? ?? ?? ?? ?? ?? ???
? ?? ?? ?? ?KEY `NewIndex1` (`name`,`first`)? ?? ?? ?? ?? ?? ?? ?? ?? ?? ?? ?? ?? ?? ?? ?? ?? ?
? ?? ?? ? ) ENGINE=MEMORY ;

CREATE TABLE `innodb_test` (? ?? ?? ?? ?? ?? ?
? ?? ?? ?? ?? ?`id` int(10) unsigned NOT NULL DEFAULT '0',
? ?? ?? ?? ?? ?`name` varchar(10) DEFAULT NULL,? ?? ?? ?? ?
? ?? ?? ?? ?? ?`first` varchar(10) DEFAULT NULL,? ?? ?? ?? ?
? ?? ?? ?? ?? ?PRIMARY KEY (`id`),? ?? ?? ?? ?? ?? ?? ?? ???
? ?? ?? ?? ?? ?KEY `NewIndex1` (`name`,`first`)? ?? ?? ?? ?
? ?? ?? ?? ? ) ENGINE=InnoDB;

如：
1：在= 或者<=> 情况下，飞快，但是在如< 或>情况下，他是不使用索引
mysql--root@localhost:17db 07:33:45>>explain select * from mem_test where id>3;
+----+-------------+----------+------+---------------+------+---------+------+------+-------------+
| id | select_type | table? ? | type | possible_keys | key | key_len | ref | rows | Extra? ?? ? |
+----+-------------+----------+------+---------------+------+---------+------+------+-------------+
| 1 | SIMPLE? ?? ?| mem_test | ALL | PRIMARY? ?? ? | NULL | NULL? ? | NULL |? ?15 | Using where |
+----+-------------+----------+------+---------------+------+---------+------+------+-------------+
1 row in set (0.00 sec)

mysql--root@localhost:17db 07:33:49>>explain select * from innodb_test where id>3;
+----+-------------+-------------+-------+---------------+---------+---------+------+------+-------------+
| id | select_type | table? ?? ? | type | possible_keys | key? ???| key_len | ref | rows | Extra? ?? ? |
+----+-------------+-------------+-------+---------------+---------+---------+------+------+-------------+
| 1 | SIMPLE? ?? ?| innodb_test | range | PRIMARY? ?? ? | PRIMARY | 4? ?? ? | NULL |? ? 7 | Using where |
+----+-------------+-------------+-------+---------------+---------+---------+------+------+-------------+
1 row in set (0.00 sec)

2：不能用在order by情况下来提高速度
mysql--root@localhost:17db 07:33:55>>explain select * from innodb_test order by id;
+----+-------------+-------------+-------+---------------+---------+---------+------+------+-------+
| id | select_type | table? ?? ? | type | possible_keys | key? ???| key_len | ref | rows | Extra |
+----+-------------+-------------+-------+---------------+---------+---------+------+------+-------+
| 1 | SIMPLE? ?? ?| innodb_test | index | NULL? ?? ?? ? | PRIMARY | 4? ?? ? | NULL |? ?15 |? ?? ? |
+----+-------------+-------------+-------+---------------+---------+---------+------+------+-------+
1 row in set (0.00 sec)

mysql--root@localhost:17db 07:34:27>>explain select * from mem_test order by id;
+----+-------------+----------+------+---------------+------+---------+------+------+----------------+
| id | select_type | table? ? | type | possible_keys | key | key_len | ref | rows | Extra? ?? ?? ? |
+----+-------------+----------+------+---------------+------+---------+------+------+----------------+
| 1 | SIMPLE? ?? ?| mem_test | ALL | NULL? ?? ?? ? | NULL | NULL? ? | NULL |? ?15 | Using filesort |
+----+-------------+----------+------+---------------+------+---------+------+------+----------------+
1 row in set (0.00 sec)

3：不能确定俩值之间有多少行
mysql--root@localhost:17db 07:37:14>>explain select count(1) from mem_test where id>3 and id<6;
+----+-------------+----------+------+---------------+------+---------+------+------+-------------+
| id | select_type | table? ? | type | possible_keys | key | key_len | ref | rows | Extra? ?? ? |
+----+-------------+----------+------+---------------+------+---------+------+------+-------------+
| 1 | SIMPLE? ?? ?| mem_test | ALL | PRIMARY? ?? ? | NULL | NULL? ? | NULL |? ?20 | Using where |
+----+-------------+----------+------+---------------+------+---------+------+------+-------------+
1 row in set (0.00 sec)

mysql--root@localhost:17db 07:40:35>>explain select count(1) from innodb_test where id>3 and id<6;
+----+-------------+-------------+-------+---------------+---------+---------+------+------+--------------------------+
| id | select_type | table? ?? ? | type | possible_keys | key? ???| key_len | ref | rows | Extra? ?? ?? ?? ?? ?? ???|
+----+-------------+-------------+-------+---------------+---------+---------+------+------+--------------------------+
| 1 | SIMPLE? ?? ?| innodb_test | range | PRIMARY? ?? ? | PRIMARY | 4? ?? ? | NULL |? ? 1 | Using where; Using index |
+----+-------------+-------------+-------+---------------+---------+---------+------+------+--------------------------+
1 row in set (0.00 sec)

4：在多列索引的情况下，只有全部指定才能利用hash扫描，而B-tree确可以利用索引的最左端来查找
mysql--root@localhost:17db 07:37:07>>explain select * from innodb_test where name='b';
+----+-------------+-------------+------+---------------+-----------+---------+-------+------+--------------------------+
| id | select_type | table? ?? ? | type | possible_keys | key? ?? ? | key_len | ref? ?| rows | Extra? ?? ?? ?? ?? ?? ???|
+----+-------------+-------------+------+---------------+-----------+---------+-------+------+--------------------------+
| 1 | SIMPLE? ?? ?| innodb_test | ref | NewIndex1? ???| NewIndex1 | 33? ?? ?| const |? ? 8 | Using where; Using index |
+----+-------------+-------------+------+---------------+-----------+---------+-------+------+--------------------------+
1 row in set (0.00 sec)

mysql--root@localhost:17db 07:37:10>>explain select * from mem_test where name='b';
+----+-------------+----------+------+---------------+------+---------+------+------+-------------+
| id | select_type | table? ? | type | possible_keys | key | key_len | ref | rows | Extra? ?? ? |
+----+-------------+----------+------+---------------+------+---------+------+------+-------------+
| 1 | SIMPLE? ?? ?| mem_test | ALL | NewIndex1? ???| NULL | NULL? ? | NULL |? ?20 | Using where |
+----+-------------+----------+------+---------------+------+---------+------+------+-------------+
当然内存表也可以手动添加btree
CREATE INDEX BTREE_index USING BTREE on mem_test(name,first)
mysql--root@localhost:17db 03:36:41>>explain select * from mem_test where name='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 | Using where |
+----+-------------+----------+------+-----------------------+-------------+---------+-------+------+-------------+
1 row in set (0.00 sec)
哈哈，它也用到索引了。
所以选择合适的存储引擎至关重要。