Mysql Hash index for Memory Engine

在memory引擎中，只支持hash index,hash index 对于等值查询效率是非常高的，但是对范围查询无能无力，从它的实现原理就能得到，每个值经过hash算法之后，得到一个hash值，通过比较hash值得到具体的数据，很明显hash值是没有任何规律的。

 

mysql> create table tb(id int) engine=memory;
Query OK, 0 rows affected (0.02 sec)

mysql> insert into tb values(1),(2),(3),(4),(5),(6),(7),(8),(9),(10);
Query OK, 10 rows affected (0.00 sec)
Records: 10 Duplicates: 0 Warnings: 0

mysql> create index idx_id on tb(id);
Query OK, 10 rows affected (0.01 sec)
Records: 10 Duplicates: 0 Warnings: 0

mysql> explain select * from tb where id=1;
+----+-------------+-------+------+---------------+--------+---------+-------+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+------+---------------+--------+---------+-------+------+-------------+
| 1 | SIMPLE | tb | ref | idx_id | idx_id | 5 | const | 2 | Using where |
+----+-------------+-------+------+---------------+--------+---------+-------+------+-------------+
1 row in set (0.00 sec)
#等值查询，用到了索引
mysql> explain select * from tb where id=5;
+----+-------------+-------+------+---------------+--------+---------+-------+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+------+---------------+--------+---------+-------+------+-------------+
| 1 | SIMPLE | tb | ref | idx_id | idx_id | 5 | const | 2 | Using where |
+----+-------------+-------+------+---------------+--------+---------+-------+------+-------------+
1 row in set (0.00 sec)

mysql> explain select * from tb where id>5;
+----+-------------+-------+------+---------------+------+---------+------+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+------+---------------+------+---------+------+------+-------------+
| 1 | SIMPLE | tb | ALL | idx_id | NULL | NULL | NULL | 10 | Using where |
+----+-------------+-------+------+---------------+------+---------+------+------+-------------+
1 row in set (0.00 sec)
#范围查询，全表扫描了
mysql> explain select * from tb where id<2;
+----+-------------+-------+------+---------------+------+---------+------+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+------+---------------+------+---------+------+------+-------------+
| 1 | SIMPLE | tb | ALL | idx_id | NULL | NULL | NULL | 10 | Using where |
+----+-------------+-------+------+---------------+------+---------+------+------+-------------+
1 row in set (0.00 sec)

mysql> show create table tb;
+-------+---------------------------------------------------------------------------------------------------------------+
| Table | Create Table |
+-------+---------------------------------------------------------------------------------------------------------------+
| tb | CREATE TABLE `tb` (
  `id` int(11) DEFAULT NULL,
  KEY `idx_id` (`id`)
) ENGINE=MEMORY DEFAULT CHARSET=latin1 |
+-------+---------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

mysql> show index from tb;
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| tb    |          1 | idx_id   |            1 | id          | NULL      |           5 |     NULL | NULL   | YES  | HASH       |         |               |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
1 row in set (0.00 sec)