The
MEMORY
(
HEAP
) Storage Engine
1306
CREATE TABLE lookup
(id INT, INDEX USING HASH (id))
ENGINE = MEMORY;
CREATE TABLE lookup
(id INT, INDEX USING BTREE (id))
ENGINE = MEMORY;
For general characteristics of B-tree and hash indexes, see
Section 8.5.3, “How MySQL Uses
Indexes”
.
• If a
MEMORY
table hash index 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.
•
MEMORY
tables can have nonunique keys. (This is an uncommon feature for implementations of hash
indexes.)
• Columns that are indexed can contain
NULL
values.
•
MEMORY
tables use a fixed-length row-storage format. Variable-length types such as
VARCHAR
are
stored using a fixed length.
•
MEMORY
tables cannot contain
BLOB
or
TEXT
columns.
•
MEMORY
includes support for
AUTO_INCREMENT
columns.
•
MEMORY
supports
INSERT DELAYED
. See
Section 13.2.5.2, “
INSERT DELAYED
Syntax”
.
• Non-
TEMPORARY
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 temporary 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 temporary tables are:
•
MEMORY
tables are never converted to disk tables. If an internal temporary table becomes too
large, the server automatically converts it to on-disk storage, as described in
Section 8.8.5, “How
MySQL Uses Internal Temporary Tables”
.
• The maximum size of
MEMORY
tables is limited by the
max_heap_table_size
[468]
system
variable, which has a default value of 16MB. To have larger (or smaller)
MEMORY
tables, you must
change the value of this variable. The value in effect for
CREATE TABLE
is the value used for the
life of the table. (If you use
ALTER TABLE
or
TRUNCATE TABLE
, the value in effect at that time
becomes the new maximum size for the table. A server restart also sets the maximum size of
existing
MEMORY
tables to the global
max_heap_table_size
[468]
value.) You can set the size
for individual tables as described later in this section.
• The server needs sufficient memory to maintain all
MEMORY
tables that are in use at the same time.
• Memory is not reclaimed if you delete individual rows from a
MEMORY
table. Memory is reclaimed only
when the entire table is deleted. Memory that was previously used for rows that have been deleted
will be re-used for new rows only within the same table. To free up the memory used by rows that
have been deleted, use
ALTER TABLE ENGINE=MEMORY
to force a table rebuild.
To free all the memory used by a
MEMORY
table when you no longer require its contents, you should
execute
DELETE
or
TRUNCATE TABLE
to remove all rows, 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
[409]
option. For example, you can put statements such as
INSERT INTO ... SELECT
or
Содержание 5.0
Страница 1: ...MySQL 5 0 Reference Manual ...
Страница 18: ...xviii ...
Страница 60: ...40 ...
Страница 396: ...376 ...
Страница 578: ...558 ...
Страница 636: ...616 ...
Страница 844: ...824 ...
Страница 1234: ...1214 ...
Страница 1426: ...MySQL Proxy Scripting 1406 The following diagram shows an overview of the classes exposed by MySQL Proxy ...
Страница 1427: ...MySQL Proxy Scripting 1407 ...
Страница 1734: ...1714 ...
Страница 1752: ...1732 ...
Страница 1783: ...Configuring Connector ODBC 1763 ...
Страница 1793: ...Connector ODBC Examples 1773 ...
Страница 1839: ...Connector Net Installation 1819 2 You must choose the type of installation to perform ...
Страница 1842: ...Connector Net Installation 1822 5 Once the installation has been completed click Finish to exit the installer ...
Страница 1864: ...Connector Net Visual Studio Integration 1844 Figure 20 24 Debug Stepping Figure 20 25 Function Stepping 1 of 2 ...
Страница 2850: ...2830 ...
Страница 2854: ...2834 ...
Страница 2928: ...2908 ...
Страница 3000: ...2980 ...
Страница 3122: ...3102 ...
Страница 3126: ...3106 ...
Страница 3174: ...3154 ...
Страница 3232: ...3212 ...