Chapter 14. Storage Engines and Table Types
MySQL 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
non-transaction-safe tables:
MyISAM manages non-transactional tables. It
provides high-speed storage and retrieval, as well as fulltext
searching capabilities. MyISAM is supported
in all MySQL configurations, and is the default storage engine
unless you have configured MySQL to use a different one by
default.
-
The MEMORY storage engine provides in-memory
tables. The MERGE storage engine allows a
collection of identical MyISAM tables to be
handled as a single table. Like MyISAM, the
MEMORY and MERGE storage
engines handle non-transactional tables, and both are also
included in MySQL by default.
Note: The
MEMORY storage engine formerly was known as
the HEAP engine.
The InnoDB and BDB storage
engines provide transaction-safe tables. BDB
is included in MySQL-Max binary distributions on those operating
systems that support it. InnoDB is also
included by default in all MySQL 5.1 binary
distributions. In source distributions, you can enable or
disable either engine by configuring MySQL as you like.
The EXAMPLE storage engine 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.
NDB Cluster is the storage engine used by
MySQL Cluster to implement tables that are partitioned over many
computers. It is available in MySQL-Max 5.1 binary
distributions. This storage engine is currently supported on
Linux, Solaris, and Mac OS X only. We intend to add support for
this engine on other platforms, including Windows, in future
MySQL releases.
The ARCHIVE storage engine is used for
storing large amounts of data without indexes with a very small
footprint.
The CSV storage engine stores data in text
files using comma-separated values format.
The BLACKHOLE storage engine accepts but does
not store data and retrievals always return an empty set.
The FEDERATED storage engine stores data in a
remote database. Currently, it works with MySQL only, using the
MySQL C Client API. In future releases, we intend to enable it
to connect to other data sources using other drivers or client
connection methods.
This chapter describes each of the MySQL storage engines except for
NDB Cluster, which is covered in
Chapter 16, MySQL Cluster.
When you create a new table, you can specify which storage engine to
use by adding an ENGINE table option to the
CREATE TABLE statement:
CREATE TABLE t (i INT) ENGINE = INNODB;
If you omit the ENGINE 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 storage_engine or
table_type system variable.
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;
See Section 13.1.5, “CREATE TABLE Syntax”, and
Section 13.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. 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
non-transactional storage engines for greater speed.)
This automatic substitution of the default storage engine for
unavailable engines can be confusing for new MySQL users. A warning
is generated whenever 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
non-transaction-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
non-transaction-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 non-transaction-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 non-transaction-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 13.4.1, “START TRANSACTION, COMMIT, and ROLLBACK Syntax”.
Non-transaction-safe tables have several advantages of their own,
all of which occur because there is no transaction overhead:
