14.8.1 Overview of InnoDB Row Storage

The storage for rows and associated columns affects performance
for queries and DML operations. As more rows fit into a single
disk page, queries and index
lookups can work faster, less cache memory is required in the
InnoDB buffer pool, and less I/O is required to write out updated
values for the numeric and short string columns.

The data in each InnoDB table is divided into
pages. The pages that make up
each table are arranged in a tree data structure called a
B-tree index. Table data
and secondary indexes
both use this type of structure. The B-tree index that represents
an entire table is known as the
clustered index, which
is organized according to the
primary key columns. The
nodes of the index data structure contain the values of all the
columns in that row (for the clustered index) or the index columns
and the primary key columns (for secondary indexes).

Variable-length columns are an exception to this rule. Columns
such as BLOB and VARCHAR
that are too long to fit on a B-tree page are stored on separately
allocated disk pages called
overflow pages. We call
such columns off-page
columns. The values of these columns are stored in
singly-linked lists of overflow pages, and each such column has
its own list of one or more overflow pages. In some cases, all or
a prefix of the long column value is stored in the B-tree, to
avoid wasting storage and eliminating the need to read a separate
page.

The next section describes the clauses you can use with the
CREATE TABLE and
ALTER TABLE statements to control
how these variable-length columns are represented:
ROW_FORMAT and
KEY_BLOCK_SIZE. To use these clauses, you might
also need to change the settings for the
innodb_file_per_table and
innodb_file_format configuration
options.