Databases Reference
In-Depth Information
separate files. InnoDB can also use raw disk partitions for building its tablespace, but
modern filesystems make this unnecessary.
InnoDB uses MVCC to achieve high concurrency, and it implements all four SQL stan-
dard isolation levels. It defaults to the
REPEATABLE READ
isolation level, and it has a
next-key
locking
strategy that prevents phantom reads in this isolation level: rather than
locking only the rows you've touched in a query, InnoDB locks gaps in the index struc-
ture as well, preventing phantoms from being inserted.
InnoDB tables are built on a
clustered index
, which we will cover in detail in later chap-
ters. InnoDB's index structures are very different from those of most other MySQL
storage engines. As a result, it provides very fast primary key lookups. However,
sec-
ondary indexes
(indexes that aren't the primary key) contain the primary key columns,
so if your primary key is large, other indexes will also be large. You should strive for a
small primary key if you'll have many indexes on a table. The storage format is platform-
neutral, meaning you can copy the data and index files from an Intel-based server to a
PowerPC or Sun SPARC without any trouble.
InnoDB has a variety of internal optimizations. These include predictive read-ahead for
prefetching data from disk, an adaptive hash index that automatically builds hash in-
dexes in memory for very fast lookups, and an insert buffer to speed inserts. We cover
these later in this topic.
InnoDB's behavior is very intricate, and we highly recommend reading the “InnoDB
Transaction Model and Locking” section of the MySQL manual if you're using
InnoDB. There are many subtleties you should be aware of before building an appli-
cation with InnoDB, because of its MVCC architecture. Working with a storage engine
that maintains consistent views of the data for all users, even when some users are
changing data, can be complex.
As a transactional storage engine, InnoDB supports truly “hot” online backups through
a variety of mechanisms, including Oracle's proprietary MySQL Enterprise Backup and
the open source Percona XtraBackup. MySQL's other storage engines can't take hot
backups—to get a consistent backup, you have to halt all writes to the table, which in
a mixed read/write workload usually ends up halting reads too.
The MyISAM Engine
As MySQL's default storage engine in versions 5.1 and older, MyISAM provides a large
list of features, such as full-text indexing, compression, and spatial (GIS) functions.
MyISAM doesn't support transactions or row-level locks. Its biggest weakness is un-
doubtedly the fact that it isn't even remotely crash-safe. MyISAM is why MySQL still
has the reputation of being a nontransactional database management system, more
than a decade after it gained transactions! Still, MyISAM isn't all that bad for a non-
transactional, non-crash-safe storage engine. If you need read-only data, or if your
