Databases Reference
In-Depth Information
Each leaf node in the clustered index contains the primary key value, the transaction
ID, and rollback pointer InnoDB uses for transactional and MVCC purposes, and the
rest of the columns (in this case,
col2
). If the primary key is on a column prefix, InnoDB
includes the full column value with the rest of the columns.
Also in contrast to MyISAM, secondary indexes are very different from clustered in-
dexes in InnoDB. Instead of storing “row pointers,” InnoDB's secondary index leaf
nodes contain the primary key values, which serve as the “pointers” to the rows. This
strategy reduces the work needed to maintain secondary indexes when rows move or
when there's a data page split. Using the row's primary key values as the pointer makes
the index larger, but it means InnoDB can move a row without updating pointers to it.
Figure 5-8
illustrates the
col2
index for the example table. Each leaf node contains the
indexed columns (in this case just
col2
), followed by the primary key values (
col1
).
Figure 5-8. InnoDB secondary index layout for the layout_test table
These diagrams have illustrated the B-Tree leaf nodes, but we intentionally omitted
details about the non-leaf nodes. InnoDB's non-leaf B-Tree nodes each contain the
indexed column(s), plus a pointer to the next-deeper node (which might be either an-
other non-leaf node or a leaf node). This applies to all indexes, clustered and secondary.
Figure 5-9
is an abstract diagram of how InnoDB and MyISAM arrange the table. This
illustration makes it easier to see how differently InnoDB and MyISAM store data and
indexes.
If you don't understand why and how clustered and nonclustered storage are different,
and why it's so important, don't worry. It will become clearer as you learn more, es-
pecially in the rest of this chapter and in the next chapter. These concepts are compli-
cated, and they take a while to understand fully.
Inserting rows in primary key order with InnoDB
If you're using InnoDB and don't need any particular clustering, it can be a good idea
to define a
surrogate key
, which is a primary key whose value is not derived from your
