MySQL記憶體表的弊端

　MySQL記憶體表使我們經常會用到的，但是 MySQL記憶體表的也不是提高讀效能的萬能工具，在有些情況下，MySQL記憶體表可能會比其實表類型的B-TREE更慢  。

1. CREATE TABLE `mem_test` (                                                               
2.             `id` int(10) unsigned NOT NULL DEFAULT 0,                                           
3.             `name` varchar(10) DEFAULT NULL,                                                      
4.             `first` varchar(10) DEFAULT NULL,                                                     
5.             PRIMARY KEY (`id`),                                                                   
6.             KEY `NewIndex1` (`name`,`first`)                                                      
7.           ) ENGINE=MEMORY ;  
8.  
9. CREATE TABLE `innodb_test` (                     
10.                `id` int(10) unsigned NOT NULL DEFAULT 0,   
11.                `name` varchar(10) DEFAULT NULL,               
12.                `first` varchar(10) DEFAULT NULL,              
13.                PRIMARY KEY (`id`),                            
14.                KEY `NewIndex1` (`name`,`first`)               
15.              ) ENGINE=InnoDB;  
16.  

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

1. mysql--root@localhost:17db 07:33:45>>explain select * from mem_test where id>3;  
2. +----+-------------+----------+------+---------------+------+---------+------+------+-------------+  
3. | id | select_type | table    | type | possible_keys | key | key_len | ref | rows | Extra       |  
4. +----+-------------+----------+------+---------------+------+---------+------+------+-------------+  
5. | 1 | SIMPLE      | mem_test | ALL | PRIMARY       | NULL | NULL    | NULL |   15 | Using where |  
6. +----+-------------+----------+------+---------------+------+---------+------+------+-------------+  
7. 1 row in set (0.00 sec)  
8.  
9. mysql--root@localhost:17db 07:33:49>>explain select * from innodb_test where id>3;  
10. +----+-------------+-------------+-------+---------------+---------+---------+------+------+-------------+  
11. | id | select_type | table       | type | possible_keys | key     | key_len | ref | rows | Extra       |  
12. +----+-------------+-------------+-------+---------------+---------+---------+------+------+-------------+  
13. | 1 | SIMPLE      | innodb_test | range | PRIMARY       | PRIMARY | 4       | NULL |    7 | Using where |  
14. +----+-------------+-------------+-------+---------------+---------+---------+------+------+-------------+  
15. 1 row in set (0.00 sec)  
16.  

　　2：不能用在order by情況下來提高速度

1. mysql--root@localhost:17db 07:33:55>>explain select * from innodb_test order by id;  
2. +----+-------------+-------------+-------+---------------+---------+---------+------+------+-------+  
3. | id | select_type | table       | type | possible_keys | key     | key_len | ref | rows | Extra |  
4. +----+-------------+-------------+-------+---------------+---------+---------+------+------+-------+  
5. | 1 | SIMPLE      | innodb_test | index | NULL          | PRIMARY | 4       | NULL |   15 |       |  
6. +----+-------------+-------------+-------+---------------+---------+---------+------+------+-------+  
7. 1 row in set (0.00 sec)  
8.  
9. mysql--root@localhost:17db 07:34:27>>explain select * from mem_test order by id;  
10. +----+-------------+----------+------+---------------+------+---------+------+------+----------------+  
11. | id | select_type | table    | type | possible_keys | key | key_len | ref | rows | Extra          |  
12. +----+-------------+----------+------+---------------+------+---------+------+------+----------------+  
13. | 1 | SIMPLE      | mem_test | ALL | NULL          | NULL | NULL    | NULL |   15 | Using filesort |  
14. +----+-------------+----------+------+---------------+------+---------+------+------+----------------+  
15. 1 row in set (0.00 sec)  
16.  

　　3：不能確定倆值之間有多少行

1. mysql--root@localhost:17db 07:37:14>>explain select count(1) from mem_test where id>3 and id<6;  
2. +----+-------------+----------+------+---------------+------+---------+------+------+-------------+  
3. | id | select_type | table    | type | possible_keys | key | key_len | ref | rows | Extra       |  
4. +----+-------------+----------+------+---------------+------+---------+------+------+-------------+  
5. | 1 | SIMPLE      | mem_test | ALL | PRIMARY       | NULL | NULL    | NULL |   20 | Using where |  
6. +----+-------------+----------+------+---------------+------+---------+------+------+-------------+  
7. 1 row in set (0.00 sec)  
8.  
9. mysql--root@localhost:17db 07:40:35>>explain select count(1) from innodb_test where id>3 and id<6;  
10. +----+-------------+-------------+-------+---------------+---------+---------+------+------+--------------------------+  
11. | id | select_type | table       | type | possible_keys | key     | key_len | ref | rows | Extra                    |  
12. +----+-------------+-------------+-------+---------------+---------+---------+------+------+--------------------------+  
13. | 1 | SIMPLE      | innodb_test | range | PRIMARY       | PRIMARY | 4       | NULL |    1 | Using where; Using index |  
14. +----+-------------+-------------+-------+---------------+---------+---------+------+------+--------------------------+  
15. 1 row in set (0.00 sec)  
16.  

　　4：在多列索引的情況下，只有全部指定才能利用hash掃描，而B-tree確可以利用索引的最左端來尋找

1. mysql--root@localhost:17db 07:37:07>>explain select * from innodb_test where name=b;  
2. +----+-------------+-------------+------+---------------+-----------+---------+-------+------+--------------------------+  
3. | id | select_type | table       | type | possible_keys | key       | key_len | ref   | rows | Extra                    |  
4. +----+-------------+-------------+------+---------------+-----------+---------+-------+------+--------------------------+  
5. | 1 | SIMPLE      | innodb_test | ref | NewIndex1     | NewIndex1 | 33      | const |    8 | Using where; Using index |  
6. +----+-------------+-------------+------+---------------+-----------+---------+-------+------+--------------------------+  
7. 1 row in set (0.00 sec)  
8.  
9. mysql--root@localhost:17db 07:37:10>>explain select * from mem_test where name=b;  
10. +----+-------------+----------+------+---------------+------+---------+------+------+-------------+  
11. | id | select_type | table    | type | possible_keys | key | key_len | ref | rows | Extra       |  
12. +----+-------------+----------+------+---------------+------+---------+------+------+-------------+  
13. | 1 | SIMPLE      | mem_test | ALL | NewIndex1     | NULL | NULL    | NULL |   20 | Using where |  
14. +----+-------------+----------+------+---------------+------+---------+------+------+-------------+  
15.  

　　當然記憶體表也可以手動添加btree

1. CREATE INDEX BTREE_index USING BTREE on mem_test(name,first)  
2. mysql--root@localhost:17db 03:36:41>>explain select * from mem_test where name=b;  
3. +----+-------------+----------+------+-----------------------+-------------+---------+-------+------+-------------+  
4. | id | select_type | table    | type | possible_keys         | key         | key_len | ref   | rows | Extra       |  
5. +----+-------------+----------+------+-----------------------+-------------+---------+-------+------+-------------+  
6. | 1 | SIMPLE      | mem_test | ref | NewIndex1,BTREE_index | BTREE_index | 33      | const |    9 | Using where |  
7. +----+-------------+----------+------+-----------------------+-------------+---------+-------+------+-------------+  
8. 1 row in set (0.00 sec)  

　　哈哈，它也用到索引了  。
所以要選擇合適的儲存引擎至關重要  。