|
 |
|
| |
14.4. The MEMORY (HEAP) Storage Engine
The MEMORY storage engine creates tables with
contents that are stored in memory. Formerly, these were known as
HEAP tables. MEMORY is the
preferred term, although HEAP remains supported
for backward compatibility.
Each MEMORY table is associated with one disk
file. The filename begins with the table name and has an extension
of .frm to indicate that it stores the table
definition.
To specify explicitly that you want to create a
MEMORY table, indicate that with an
ENGINE table option:
CREATE TABLE t (i INT) ENGINE = MEMORY;
As indicated by the name, MEMORY tables are
stored in memory. They use hash indexes by default, which makes
them very fast, and very useful for creating temporary tables.
However, when the server shuts down, all rows stored in
MEMORY tables are lost. The tables themselves
continue to exist because their definitions are stored in
.frm files on disk, but they are empty when
the server restarts.
This example shows how you might create, use, and remove a
MEMORY table:
mysql> CREATE TABLE test ENGINE=MEMORY
-> SELECT ip,SUM(downloads) AS down
-> FROM log_table GROUP BY ip;
mysql> SELECT COUNT(ip),AVG(down) FROM test;
mysql> DROP TABLE test;
MEMORY tables have the following
characteristics:
Space for MEMORY tables is allocated in
small blocks. Tables use 100% dynamic hashing for inserts. No
overflow area or extra key space is needed. No extra space is
needed for free lists. Deleted rows are put in a linked list
and are reused when you insert new data into the table.
MEMORY tables also have none of the
problems commonly associated with deletes plus inserts in
hashed tables.
MEMORY tables can have up to 32 indexes per
table, 16 columns per index and a maximum key length of 500
bytes.
-
The MEMORY storage engine implements both
HASH and BTREE indexes.
You can specify one or the other for a given index by adding a
USING clause as shown here:
CREATE TABLE lookup
(id INT, INDEX USING HASH (id))
ENGINE = MEMORY;
CREATE TABLE lookup
(id INT, INDEX USING BTREE (id))
ENGINE = MEMORY;
General characteristics of B-tree and hash indexes are
described in Section 7.4.5, “How MySQL Uses Indexes”.
You can have non-unique keys in a MEMORY
table. (This is an uncommon feature for implementations of
hash indexes.)
If you have a hash index on a MEMORY table
that has a high degree of key duplication (many index entries
containing the same value), updates to the table that affect
key values and all deletes are significantly slower. The
degree of this slowdown is proportional to the degree of
duplication (or, inversely proportional to the index
cardinality). You can use a BTREE index to
avoid this problem.
Columns that are indexed can contain NULL
values.
MEMORY tables use a fixed-length row
storage format.
MEMORY tables cannot contain
BLOB or TEXT columns.
MEMORY includes support for
AUTO_INCREMENT columns.
You can use INSERT DELAYED with
MEMORY tables. See
Section 13.2.4.2, “INSERT DELAYED Syntax”.
MEMORY tables are shared among all clients
(just like any other non-TEMPORARY table).
-
MEMORY table contents are stored in memory,
which is a property that MEMORY tables
share with internal tables that the server creates on the fly
while processing queries. However, the two types of tables
differ in that MEMORY tables are not
subject to storage conversion, whereas internal tables are:
If an internal table becomes too large, the server
automatically converts it to an on-disk table. The size
limit is determined by the value of the
tmp_table_size system variable.
MEMORY tables are never converted to
disk tables. To ensure that you don't accidentally do
anything foolish, you can set the
max_heap_table_size system variable to
impose a maximum size on MEMORY tables.
For individual tables, you can also specify a
MAX_ROWS table option in the
CREATE TABLE statement.
The server needs sufficient memory to maintain all
MEMORY tables that are in use at the same
time.
To free memory used by a MEMORY table when
you no longer require its contents, you should execute
DELETE or TRUNCATE
TABLE, or remove the table altogether using
DROP TABLE.
If you want to populate a MEMORY table when
the MySQL server starts, you can use the
--init-file option. For example, you can put
statements such as INSERT INTO ... SELECT
or LOAD DATA INFILE into this file to load
the table from a persistent data source. See
Section 5.2.1, “mysqld Command Options”, and
Section 13.2.5, “LOAD DATA INFILE Syntax”.
If you are using replication, the master server's
MEMORY tables become empty when it is shut
down and restarted. However, a slave is not aware that these
tables have become empty, so it returns out-of-date content if
you select data from them. When a MEMORY
table is used on the master for the first time since the
master was started, a DELETE statement is
written to the master's binary log automatically, thus
synchronizing the slave to the master again. Note that even
with this strategy, the slave still has outdated data in the
table during the interval between the master's restart and its
first use of the table. However, if you use the
--init-file option to populate the
MEMORY table on the master at startup, it
ensures that this time interval is zero.
-
The memory needed for one row in a MEMORY
table is calculated using the following expression:
SUM_OVER_ALL_BTREE_KEYS(max_length_of_key + sizeof(char*) × 4)
+ SUM_OVER_ALL_HASH_KEYS(sizeof(char*) × 2)
+ ALIGN(length_of_row+1, sizeof(char*))
ALIGN() represents a round-up factor to
cause the row length to be an exact multiple of the
char pointer size.
sizeof(char*) is 4 on 32-bit machines and 8
on 64-bit machines.
Additional resources
|
|
|
