MySQL 的表类型 - HEAP 表类型

Tags: database mysql

MySQL 中的表类型

我们都知道访问内存中的数据比访问磁盘中的数据要快很多。因此,基于这个思路,是否 有可能让 MySQL 将数据竟可能多的保存在内存中呢?答案当然是:yes!并且 MySQL 有很多办法可以做到,查询缓存,将所有索引保存在内存中,或者更极端的,将整张表保存在内存中。本文的后续部分,将会介绍 HEAP 表类型,可以将表保存在内存中。

MySQL 表结类型包括:

  • 'MyISAM':默认的表类型,它使用表锁定,来提供大多数网站所需要的高性能检索和低性能插入/更新特性。

  • 'InnoDB':支持师傅和引用完整性特性

  • 'ISAM':较老的 MySQL 表类型,现在已经被 MyISAM 替换

  • 'BDB':同样是事务安全的表类型

  • 'MERGE':MyISAM 表类型的集合,可以将它们看做一个整体使用

创建 HEAP 表

创建 HEAP 表非常简单,仅需要指定表类型为 HEAP 即可,比如:

mysql> CREATE TABLE heapofdust (id INT, fname VARCHAR (40)) TYPE=HEAP;
Query OK, 0 rows affected (0.08 sec)

注意,HEAP 表不能包含 TEXT 或者 BLOB 类型字段,如果你打算创建包含这类字段的 HEAP 表,会得到一个错误,比如:

mysql> CREATE TABLE heapofleaves (id INT, fname VARCHAR (40), description TEXT) TYPE=HEAP;
ERROR 1163: The used table type doesn't support BLOB/TEXT columns

如果语法没有偶问题,你现在可以像使用普通的 MyISAM 表一样 INSERT 和 SELECT 数据了,不过会快很多。早在 2002 年,John Lim 编制了一些基准来测量 HEAP 的性能,它发现 HEAP 比 MyISAM 快一些,但是快得不多。有可能在使用 MySQL 4 的表缓存时,差异会更少。下面是他的测试结果:

Table type  100 000 inserts (seconds)   Inserts per s   50 000 selects (s)  Selects/s
MySQL 3.23 Heap (Mem used: 10Mb)    23.82   4,198   21.96   2,276
MySQL 3.23 MyISAM   33.42   2,992   25.91   1,930

你可以在 PHP Everywhere site 查看详细的基准测试资料(同样也对比了其他 DBMS)。

当然,脱离你的实际配置来谈论基准测试是没有意义的,一个硬盘速度很慢的系统可以从 HEAP 表类型得到更多的速度提升。更重要的是,你是否有足够的内存在容纳所整张表的数据。如果在任何时候数据超出内存,表会被自动转换为 MyISAM,这可能会在不合适的时候发生。首先,MySQL 变量 max_heap_table_size 会限制 HEAP 表大小的最大值(在转换成 MyISAM 之前),这个变量在 my.cnf(或者 my.ini)中配置。你在 通过硬件以及 Mysqld 变量优化 MySQL 这篇文章中了解到更多的 MySQL 变量配置。

此外,也可以在创建表的时候指定 MAX_ROW 属性来限制表大小(你可以同时使用这两种方式):

mysql> CREATE TABLE heapofleaves (id INT, fname VARCHAR (40)) TYPE=HEAP MAX_ROWS=1;

HEAP 表和索引

HEAP 表在使用索引时有一些限制。让我们看看这些行为,并与一个相同的 MyISAM 对比。首先,我们将创建两个相同的表,一个使用 HEAP 表类型,另一个使用 MyISAM:

mysql> CREATE TABLE myisam1 (id INT, name VARCHAR(40) NOT NULL, INDEX(name)) TYPE MYISAM;

mysql> CREATE TABLE heap1 (id INT, name VARCHAR(40) NOT NULL, INDEX(name)) TYPE=HEAP;

mysql> INSERT INTO myisam1 VALUES(1,'ian'),(2,'Mzwake'),(3,'Helena');

mysql> INSERT INTO heap1 VALUES(1,'ian'),(2,'Mzwake'),(3,'Helena');

现在我们将执行一个简单的查询来返回指定的数据。我们将使用 EXPLAIN 来查看查询是如何使用索引的(在这里查看看 EXPLAIN 使用和索引类型更多细节):

mysql> EXPLAIN SELECT * FROM heap1 WHERE name='ian';
+-------+------+---------------+------+---------+-------+------+------------+
| table | type | possible_keys | key  | key_len | ref   | rows | Extra      |
+-------+------+---------------+------+---------+-------+------+------------+
| heap1 | ref  | name          | name |      40 | const |   10 | where used |
+-------+------+---------------+------+---------+-------+------+------------+

mysql> EXPLAIN SELECT * FROM myisam1 WHERE name='ian';
+---------+------+---------------+------+---------+-------+------+------------+
| table   | type | possible_keys | key  | key_len | ref   | rows | Extra      |
+---------+------+---------------+------+---------+-------+------+------------+
| myisam1 | ref  | name          | name |      40 | const |    1 | where used |
+---------+------+---------------+------+---------+-------+------+------------+

正如期望的,索引的使用是一致的和高效的。然而,当我们修改查询来检索记录范围(所以名字大于指定的名字),注意两种表类型之间的差别:

mysql> EXPLAIN SELECT * FROM myisam1 WHERE name>'ian';
+---------+-------+---------------+------+---------+------+------+------------+
| table   | type  | possible_keys | key  | key_len | ref  | rows | Extra      |
+---------+-------+---------------+------+---------+------+------+------------+
| myisam1 | range | name          | name |      40 | NULL |    2 | where used |
+---------+-------+---------------+------+---------+------+------+------------+

mysql> EXPLAIN SELECT * FROM heap1 WHERE name>'ian';
+-------+------+---------------+------+---------+------+------+------------+
| table | type | possible_keys | key  | key_len | ref  | rows | Extra      |
+-------+------+---------------+------+---------+------+------+------------+
| heap1 | ALL  | name          | NULL |    NULL | NULL |    3 | where used |
+-------+------+---------------+------+---------+------+------+------------+

MyISAM 使用 range 连接,而 HEAP 表使用 'ALL' 连接,最差的连接类型。HEAP 表类型仅会针对 =<=> 操作符使用索引。如果你的应用程序非常依赖 range 索引,HEAP 表会比 MyISAM 表慢,让我们看看另一个重要的差别:

mysql> EXPLAIN SELECT * FROM myisam1 WHERE name LIKE 'ia%';
+---------+-------+---------------+------+---------+------+------+------------+
| table   | type  | possible_keys | key  | key_len | ref  | rows | Extra      |
+---------+-------+---------------+------+---------+------+------+------------+
| myisam1 | range | name          | name |      40 | NULL |    1 | where used |
+---------+-------+---------------+------+---------+------+------+------------+

mysql> EXPLAIN SELECT * FROM heap1 WHERE name LIKE 'ia%';
+-------+------+---------------+------+---------+------+------+------------+
| table | type | possible_keys | key  | key_len | ref  | rows | Extra      |
+-------+------+---------------+------+---------+------+------+------------+
| heap1 | ALL  | name          | NULL |    NULL | NULL |    3 | where used |
+-------+------+---------------+------+---------+------+------+------------+

HEAP 表仅能使用所有的 key 来返回结果,而 MyISAM 可以使用前缀。下面是这些差别的总结:

  • HEAP 不能够索引 BLOB 或 TEXT 字段

  • HEAP 不能使用部分键(前缀)

  • HEAP 表不支持 AUTO_INCREMENT 字段

  • HEAP 表仅能在 =<=> 操作符上使用索引

  • HEAP 表在 ORDER BY 子句上无法使用索引

  • HEAP 的索引无法提供两行记录见有多少条记录的信息,这些信息可以为优化器选择索引提供帮助。

最后 3 个差别之间是相关的,HEAP 表使用 hash 索引可以快速查找指定的记录,不过按范围查找数据会非常慢,并且没有检索记录随后的记录。

使用 HEAP 表

当创建临时表(不包含 BLOB 或者 TEXT 字段)时 MySQL 会默认使用 HEAP 表。如果临时表变得太大(比如达到 max_heap_table_size 以及 tmp_table_size),临时表会自动转换成 MyISAM 表。

当可用内存可以容纳表以及表更新不频繁时可以使用 HEAP 表。由于 HEAP 表数据时保存在内存中的,所以当设备宕机或者掉电,会丢失所有数据,因此不建议保存重要的数据更新,不过非常适合保存其他表(更新的数据保存在这些表中) 的数据子集,使这些数据可以快速访问。理想的使用场景是在 MyISAM 表更新数据,然后每天依据这个表生成 HEAP 表,来提供快速的以及 disk-free 数据访问。比如,每天依据 myisam1 表的数据创建 heap1 表:

mysql> CREATE TABLE heap2 TYPE=HEAP SELECT name FROM myisam1;
Query OK, 3 rows affected (0.02 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> SELECT * FROM heap2;
+--------+
| name   |
+--------+
| Helena |
| ian    |
| Mzwake |
+--------+
3 rows in set (0.00 sec)

mysql> ALTER TABLE heap2 ADD INDEX(name);
Query OK, 3 rows affected (0.00 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> DESC heap2;
+-------+----------+------+-----+---------+-------+
| Field | Type     | Null | Key | Default | Extra |
+-------+----------+------+-----+---------+-------+
| name  | char(40) |      | MUL |         |       |
+-------+----------+------+-----+---------+-------+

现在 heap2 表已经具备索引(与原始 MyISAM 表不同的索引),并且存储在内存中来提供闪电般快速方位。与此同时,可以更新 MyISAM 表并不会影响 HEAP 表的访问速度。更新的数据可以在第二天被访问到。这是一个小势力,不过希望能给你一些在自己环境中使用 HEAP 表的灵感,祝你好运!

本文链接:http://www.4byte.cn/learning/37780/mysql-de-biao-lei-xing-heap-biao-lei-xing.html