表使我们经常会用到的，但是 表的也不是提高读的万能，在有些情况下，可能会比其实表的B-TREE更慢  。

CREATE TABLE `m_test` (
            `` t() unsigned NOT  DEFAULT 0,
            `` 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=Inno;

　　如：

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

mysql--@local:17db 07:33:45>>explain  * from mem_test  id>3;
+----+-------------+----------+------+---------------+------+---------+------+------+-------------+
| id | select_type | table    | type | possible_keys | key | key_len | ref | rows | ra       |
+----+-------------+----------+------+---------------+------+---------+------+------+-------------+
| 1 | SIME      | mem_test |  | PRIMARY       | NULL | NULL    | NULL |   15 | Using where |
+----+-------------+----------+------+---------------+------+---------+------+------+-------------+
1 row in  (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 情况下来提高

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 |  | 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：在多列索引的情况下，只有全部指定才能利用扫描，而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      |  |    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)

　　哈哈，它也用到索引了  。

所以要选择合适的引擎至关重要  。