The InnoDB Engine

The InnoDB Engine

InnoDB was designed for transaction processing—specifically, processing of many

short-lived transactions that usually complete rather than being rolled back. It

remains the most popular storage engine for transactional storage. Its performance

and automatic crash recovery make it popular for nontransactional storage needs,

too.

InnoDB stores its data in a series of one or more data files that are collectively known

as a tablespace. A tablespace is essentially a black box that InnoDB manages all by

itself. In MySQL 4.1 and newer versions, InnoDB can store each table’s data and

indexes in separate files. InnoDB can also use raw disk partitions for building its

tablespace. See “The InnoDB tablespace” on page 290 for more information.

InnoDB uses MVCC to achieve high concurrency, and it implements all four SQL

standard 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 structure as well, preventing phantoms from being inserted.

InnoDB tables are built on a clustered index, which we will cover in detail in

Chapter 3. 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,

secondary 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. InnoDB doesn’t

compress its indexes.

At the time of this writing, InnoDB can’t build indexes by sorting, which MyISAM

can do. Thus, InnoDB loads data and creates indexes more slowly than MyISAM.

Any operation that changes an InnoDB table’s structure will rebuild the entire table,

including all the indexes.

InnoDB was designed when most servers had slow disks, a single CPU, and limited

memory. Today, as multicore servers with huge amounts of memory and fast disks

are becoming less expensive, InnoDB is experiencing some scalability issues.

20 | Chapter 1: MySQL Architecture

InnoDB’s developers are addressing these issues, but at the time of this writing, several

of them remain problematic. See “InnoDB Concurrency Tuning” on page 296

for more information about achieving high concurrency with InnoDB.

Besides its high-concurrency capabilities, InnoDB’s next most popular feature is foreign

key constraints, which the MySQL server itself doesn’t yet provide. InnoDB also

provides extremely fast lookups for queries that use a primary key.

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

indexes in memory for very fast lookups, and an insert buffer to speed inserts. We

cover these extensively later in this book.

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 surprises and exceptions you should be aware of before

building an application with InnoDB.

　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　《high performance mysql 2》