MySQL内存表的弊端

MySQL内存表使我们经常会用到的，但是 MySQL内存表的也不是提高读性能的***工具，在有些情况下，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)  

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

【编辑推荐】

MySQL独立表空间的优缺点

mysql数据库大小写的问题讨论

MySQL MyISAM表结构的恢复

MySQL InnoDB表结构的恢复

深度解析MySQL创建关联表