MySQL has a well-earned reputation for being easy-to-use and
delivering performance and scalability. Prior to MySQL 5.5,
MyISAM was the default storage engine.
In our experience, most users never changed the default settings. In
MySQL 5.5 and higher,
InnoDB is the
default storage engine. Again, we expect most users will not change
the default settings. But, because of
default settings deliver the benefits users expect from their RDBMS:
Integrity, and Crash
Recovery. Let's explore how using
tables improves your life as a MySQL user, DBA, or developer.
In the first years of MySQL growth, early web-based applications didn't push the limits of concurrency and availability. In recent years, hard drive and memory capacity and the performance/price ratio have all gone through the roof. Users pushing the performance boundaries of MySQL care a lot about reliability and crash recovery. MySQL databases are big, busy, robust, distributed, and important.
InnoDB addresses these top user priorities. The
trend of storage engine usage has shifted in favor of the more
InnoDB. Thus MySQL 5.5 was the logical
transition release to make
InnoDB the default
MySQL continues to work on addressing use cases that formerly
MyISAM tables. In MySQL 5.6 and higher:
InnoDB can perform full-text search using the
FULLTEXT index type. See
Section 126.96.36.199, “InnoDB FULLTEXT Indexes” for details.
InnoDB now performs better with read-only or
read-mostly workloads. Automatic optimizations apply to
InnoDB queries in
autocommit mode, and you
can explicitly mark transactions as read-only with the syntax
START TRANSACTION READ ONLY. See
Section 8.5.3, “Optimizing InnoDB Read-Only Transactions” for details.
Applications distributed on read-only media can now use
InnoDB tables. See
Section 14.3.1, “Configuring InnoDB for Read-Only Operation” for details.
Starting from MySQL 5.5.5, the default storage engine for new tables
InnoDB. This change applies to newly created
tables that don't specify a storage engine with a clause such as
ENGINE=MyISAM. Given this change of default
behavior, MySQL 5.5 might be a logical point to evaluate whether
your tables that do use
MyISAM could benefit from
information_schema databases, that implement some
of the MySQL internals, still use
particular, you cannot switch the grant tables to use
If you use
MyISAM tables but aren't tied to them
for technical reasons, you'll find many things more convenient when
If your server crashes because of a hardware or software issue,
regardless of what was happening in the database at the time,
you don't need to do anything special after restarting the
automatically finalizes any changes that were committed before
the time of the crash, and undoes any changes that were in
process but not committed. Just restart and continue where you
left off. This process is now much faster than in MySQL 5.1 and
buffer pool caches table
and index data as the data is accessed. Frequently used data is
processed directly from memory. This cache applies to so many
types of information, and speeds up processing so much, that
dedicated database servers assign up to 80% of their physical
memory to the
InnoDB buffer pool.
If you split up related data into different tables, you can set up foreign keys that enforce referential integrity. Update or delete data, and the related data in other tables is updated or deleted automatically. Try to insert data into a secondary table without corresponding data in the primary table, and the bad data gets kicked out automatically.
If data becomes corrupted on disk or in memory, a checksum mechanism alerts you to the bogus data before you use it.
When you design your database with appropriate
primary key columns for
each table, operations involving those columns are automatically
optimized. It is very fast to reference the primary key columns
GROUP BY clauses, and
Inserts, updates, deletes are optimized by an automatic
mechanism called change
InnoDB not only allows
concurrent read and write access to the same table, it caches
changed data to streamline disk I/O.
Performance benefits are not limited to giant tables with long-running queries. When the same rows are accessed over and over from a table, a feature called the Adaptive Hash Index takes over to make these lookups even faster, as if they came out of a hash table.
If you have been using
InnoDB for a long time,
you already know about features like transactions and foreign keys.
If not, read about them throughout this chapter. To make a long
Embrace the idea of joins, where data is pulled from multiple tables based on identical ID values from those tables. For fast join performance, define foreign keys on the join columns, and declare those columns with the same data type in each table. The foreign keys also propagate deletes or updates to all affected tables, and prevent insertion of data in a child table if the corresponding IDs are not present in the parent table.
Turn off autocommit. Committing hundreds of times a second puts a cap on performance (limited by the write speed of your storage device).
Group sets of related DML
bracketing them with
START TRANSACTION and
COMMIT statements. While you don't want to
commit too often, you also don't want to issue huge batches of
DELETE statements that run for hours without
LOCK TABLE statements.
InnoDB can handle multiple sessions all
reading and writing to the same table at once, without
sacrificing reliability or high performance. To get exclusive
write access to a set of rows, use the
SELECT ... FOR
UPDATE syntax to lock just the rows you intend to
innodb_file_per_table option to
put the data and indexes for individual tables into separate
files, instead of in a single giant
CREATE TABLESPACE syntax to
create a shared general tablespace for table and index data.
General tablespaces support multiple tables, all row formats,
and can be created in a directory relative to or independent of
the MySQL data directory. General tablespaces were introduced in
Evaluate whether your data and access patterns benefit from the
ROW_FORMAT=COMPRESSED) on the
CREATE TABLE statement. You can compress
InnoDB tables without sacrificing read/write
Run your server with the option
--sql_mode=NO_ENGINE_SUBSTITUTION to prevent
tables being created with a different storage engine if there is
an issue with the one specified in the
ENGINE= clause of
You can compress tables and associated indexes.
You can create and drop indexes with much less performance or availability impact than before.
Truncating a table is very fast, and can free up disk space for
the operating system to reuse, rather than freeing up space
within the system tablespace that only
The storage layout for table data is more efficient for BLOBs
and long text fields, with the
You can monitor the internal workings of the storage engine by
You can monitor the performance details of the storage engine by
There are many performance improvements. In particular, crash
recovery, the automatic process that makes all data consistent
when the database is restarted, is fast and reliable (much
faster than long-time
InnoDB users are used
to). The bigger the database, the more dramatic the speedup.
Most new performance features are automatic, or at most require
setting a value for a configuration option. For details, see
Section 14.12, “InnoDB Performance”. For
InnoDB-specific tuning techniques you can
apply in your application code, see
Section 8.5, “Optimizing for InnoDB Tables”. Advanced users can review
Section 14.11, “InnoDB Startup Options and System Variables”.
Even before completing your upgrade from MySQL 5.1 or earlier to
MySQL 5.5 or higher, you can preview whether your database server or
application works correctly with
InnoDB as the
default storage engine. To set up
InnoDB as the
default storage engine with an earlier MySQL release, either specify
on the command line
--default-storage-engine=InnoDB, or add to your
default-storage-engine=innodb in the
[mysqld] section, then restart the server.
Since changing the default storage engine only affects new tables as
they are created, run all your application installation and setup
steps to confirm that everything installs properly. Then exercise
all the application features to make sure all the data loading,
editing, and querying features work. If a table relies on some
MyISAM-specific feature, you'll receive an error;
ENGINE=MyISAM clause to the
CREATE TABLE statement to avoid the error.
If you did not make a deliberate decision about the storage engine,
and you just want to preview how certain tables work when they're
InnoDB, issue the command
ALTER TABLE table_name ENGINE=InnoDB; for each
table. Or, to run test queries and other statements without
disturbing the original table, make a copy like so:
CREATE TABLE InnoDB_Table (...) ENGINE=InnoDB AS SELECT * FROM MyISAM_Table;
Since there are so many performance enhancements in
InnoDB in MySQL 5.5 and higher, to get a true
idea of the performance with a full application under a realistic
workload, install the latest MySQL server and run benchmarks.
Test the full application lifecycle, from installation, through heavy usage, and server restart. Kill the server process while the database is busy to simulate a power failure, and verify that the data is recovered successfully when you restart the server.
Test any replication configurations, especially if you use different MySQL versions and options on the master and the slaves.
To know what the status of
InnoDB is, whether
you're doing what-if testing with an older MySQL or comprehensive
testing with the latest MySQL:
Issue the command
SHOW ENGINES; to see all
the different MySQL storage engines. Look for
DEFAULT in the
InnoDB is not present at all, you have a
mysqld binary that was compiled without
InnoDB support and you need to get a
InnoDB is present but disabled, go back
through your startup options and configuration file and get rid