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--chinastor.com-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--chinastor.com-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--chinastor.com-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--chinastor.com-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--chinastor.com-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--chinastor.com-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--chinastor.com-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--chinastor.com-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--chinastor.com-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)
哈哈，它也用到索引了。
所以要选择合适的存储引擎至关重要。