Table of Contents [+/-]
MyISAM
Storage Engine [+/-]InnoDB
Storage Engine [+/-]InnoDB
Contact InformationInnoDB
in MySQL 3.23InnoDB
ConfigurationInnoDB
Startup Options and System VariablesInnoDB
TablesInnoDB
Data and Log
FilesInnoDB
DatabaseInnoDB
Database to Another MachineInnoDB
Transaction Model and LockingInnoDB
Multi-VersioningInnoDB
Table and Index StructuresInnoDB
Disk I/O and File Space ManagementInnoDB
Error HandlingInnoDB
Performance Tuning and TroubleshootingInnoDB
TablesMERGE
Storage Engine [+/-]MEMORY
(HEAP
) Storage EngineBDB
(BerkeleyDB
) Storage
Engine [+/-]EXAMPLE
Storage EngineARCHIVE
Storage EngineCSV
Storage EngineBLACKHOLE
Storage EngineISAM
Storage EngineMySQL supports several storage engines that act as handlers for different table types. MySQL storage engines include both those that handle transaction-safe tables and those that handle nontransaction-safe tables:
The original storage engine was ISAM
, which
managed nontransactional tables. This engine has been replaced
by MyISAM
and should no longer be used. It is
deprecated in MySQL 4.1, and is removed in subsequent MySQL
release series.
In MySQL 3.23.0, the MyISAM
and
HEAP
storage engines were introduced.
MyISAM
is an improved replacement for
ISAM
. The HEAP
storage
engine provides in-memory tables. The MERGE
storage engine was added in MySQL 3.23.25. It allows a
collection of identical MyISAM
tables to be
handled as a single table. All three of these storage engines
handle nontransactional tables, and all are included in MySQL by
default. Note that the HEAP
storage engine
has been renamed the MEMORY
engine.
The InnoDB
and BDB
storage
engines that handle transaction-safe tables were introduced in
later versions of MySQL 3.23. Both are available in source
distributions as of MySQL 3.23.34a. BDB
is
included in MySQL-Max binary distributions on those operating
systems that support it. InnoDB
also is
included in MySQL-Max binary distributions for MySQL 3.23.
Beginning with MySQL 4.0, InnoDB
is included
by default in all MySQL binary distributions. In source
distributions, you can enable or disable either engine by
configuring MySQL as you like.
The EXAMPLE
storage engine was added in MySQL
4.1.3. It is a “stub” engine that does nothing. You
can create tables with this engine, but no data can be stored in
them or retrieved from them. The purpose of this engine is to
serve as an example in the MySQL source code that illustrates
how to begin writing new storage engines. As such, it is
primarily of interest to developers.
NDBCLUSTER
is the storage engine
used by MySQL Cluster to implement tables that are partitioned
over many computers. It is available in source code
distributions as of MySQL 4.1.2 and binary distributions as of
MySQL-Max 4.1.3.
MySQL Cluster is covered in a separate chapter of this Manual. See Chapter 15, MySQL Cluster, for more information.
The ARCHIVE
storage engine was added in MySQL
4.1.3. It is used for storing large amounts of data without
indexes in a very small footprint.
The CSV
storage engine was added in MySQL
4.1.4. This engine stores data in text files using
comma-separated values format.
The BLACKHOLE
storage engine was added in
MySQL 4.1.11. This engine accepts but does not store data and
retrievals always return an empty set.
To determine which storage engines your server supports by using the
SHOW ENGINES
statement. The value in
the Support
column indicates whether an engine
can be used. A value of YES
,
NO
, or DEFAULT
indicates that
an engine is available, not available, or available and currently
set as the default storage engine.
mysql> SHOW ENGINES\G
*************************** 1. row ***************************
Engine: MyISAM
Support: DEFAULT
Comment: Default engine as of MySQL 3.23 with great performance
*************************** 2. row ***************************
Engine: HEAP
Support: YES
Comment: Alias for MEMORY
*************************** 3. row ***************************
Engine: MEMORY
Support: YES
Comment: Hash based, stored in memory, useful for temporary tables
*************************** 4. row ***************************
Engine: MERGE
Support: YES
Comment: Collection of identical MyISAM tables
*************************** 5. row ***************************
Engine: MRG_MYISAM
Support: YES
Comment: Alias for MERGE
...
This chapter describes each of the MySQL storage engines except for
NDBCLUSTER
, which is covered in
Chapter 15, MySQL Cluster.
For information about storage engine support offered in commercial MySQL Server binaries, see MySQL Enterprise Server 5.1, on the MySQL Web site. The storage engines available might depend on which edition of Enterprise Server you are using.
When you create a new table, you can specify which storage engine to
use by adding an ENGINE
or
TYPE
table option to the
CREATE TABLE
statement:
CREATE TABLE t (i INT) ENGINE = INNODB; CREATE TABLE t (i INT) TYPE = MEMORY;
ENGINE
is the preferred term, but cannot be used
before MySQL 4.0.18. TYPE
is available beginning
with MySQL 3.23.0, the first version of MySQL for which multiple
storage engines were available. TYPE
is supported
for backward compatibility but is deprecated.
If you omit the ENGINE
or TYPE
option, the default storage engine is used. Normally, this is
MyISAM
, but you can change it by using the
--default-storage-engine
or
--default-table-type
server startup
option, or by setting the default-storage-engine
or default-table-type
option in the
my.cnf
configuration file.
You can set the default storage engine to be used during the current
session by setting the
storage_engine
or
table_type
variable:
SET storage_engine=MYISAM; SET table_type=BDB;
When MySQL is installed on Windows using the MySQL Configuration
Wizard, the InnoDB
storage engine can be selected
as the default instead of MyISAM
. See
Section 2.3.4.6, “The Database Usage Dialog”.
To convert a table from one storage engine to another, use an
ALTER TABLE
statement that indicates
the new engine:
ALTER TABLE t ENGINE = MYISAM; ALTER TABLE t TYPE = BDB;
See Section 12.1.5, “CREATE TABLE
Syntax”, and
Section 12.1.2, “ALTER TABLE
Syntax”.
If you try to use a storage engine that is not compiled in or that
is compiled in but deactivated, MySQL instead creates a table using
the default storage engine, usually MyISAM)
.
(Before MySQL, MyISAM
is always used for
unavailable storage engines.) type MyISAM
. This
behavior is convenient when you want to copy tables between MySQL
servers that support different storage engines. (For example, in a
replication setup, perhaps your master server supports transactional
storage engines for increased safety, but the slave servers use only
nontransactional storage engines for greater speed.)
This automatic substitution of the default storage engine for unavailable engines can be confusing for new MySQL users. In MySQL 4.1, a warning is generated when a storage engine is automatically changed.
For new tables, MySQL always creates an .frm
file to hold the table and column definitions. The table's index and
data may be stored in one or more other files, depending on the
storage engine. The server creates the .frm
file above the storage engine level. Individual storage engines
create any additional files required for the tables that they
manage.
A database may contain tables of different types. That is, tables need not all be created with the same storage engine.
Transaction-safe tables (TSTs) have several advantages over nontransaction-safe tables (NTSTs):
They are safer. Even if MySQL crashes or you get hardware problems, you can get your data back, either by automatic recovery or from a backup plus the transaction log.
You can combine many statements and accept them all at the same
time with the COMMIT
statement
(if autocommit is disabled).
You can execute
ROLLBACK
to
ignore your changes (if autocommit is disabled).
If an update fails, all of your changes are reverted. (With nontransaction-safe tables, all changes that have taken place are permanent.)
Transaction-safe storage engines can provide better concurrency for tables that get many updates concurrently with reads.
You can combine transaction-safe and nontransaction-safe tables in
the same statements to get the best of both worlds. However,
although MySQL supports several transaction-safe storage engines,
for best results, you should not mix different storage engines
within a transaction with autocommit disabled. For example, if you
do this, changes to nontransaction-safe tables still are committed
immediately and cannot be rolled back. For information about this
and other problems that can occur in transactions that use mixed
storage engines, see Section 12.3.1, “START TRANSACTION
,
COMMIT
, and
ROLLBACK
Syntax”.
Note that to use the InnoDB
storage engine in
MySQL 3.23, you must configure at least the
innodb_data_file_path
startup
option. In 4.0 and up, InnoDB
uses default
configuration values if you specify none. See
Section 13.2.3, “InnoDB
Configuration”.
Nontransaction-safe tables have several advantages of their own, all of which occur because there is no transaction overhead:
Much faster
Lower disk space requirements
Less memory required to perform updates
User Comments
More information about how to pick the best MySQL Storage engine for your real life scenario:
http://www.softwareprojects.com/resources/programming/t-mysql-storage-engines-1470.html
Add your own comment.