14.2.13. InnoDB Table and Index Structures
MySQL stores its data dictionary information for tables in
.frm files in database directories. This is
true for all MySQL storage engines. But every
InnoDB table also has its own entry in the
InnoDB internal data dictionary inside the
tablespace. When MySQL drops a table or a database, it has to
delete both an .frm file or files, and the
corresponding entries inside the InnoDB data
dictionary. This is the reason why you cannot move
InnoDB tables between databases simply by
moving the .frm files.
Every InnoDB table has a special index called
the clustered index where the data for the
rows is stored. If you define a PRIMARY KEY on
your table, the index of the primary key is the clustered index.
If you do not define a PRIMARY KEY for your
table, MySQL picks the first UNIQUE index that
has only NOT NULL columns as the primary key
and InnoDB uses it as the clustered index. If
there is no such index in the table, InnoDB
internally generates a clustered index where the rows are ordered
by the row ID that InnoDB assigns to the rows
in such a table. The row ID is a 6-byte field that increases
monotonically as new rows are inserted. Thus, the rows ordered by
the row ID are physically in insertion order.
Accessing a row through the clustered index is fast because the
row data is on the same page where the index search leads. If a
table is large, the clustered index architecture often saves a
disk I/O when compared to the traditional solution. (In many
database systems, data storage uses a different page from the
index record.)
In InnoDB, the records in non-clustered indexes
(also called secondary indexes) contain the primary key value for
the row. InnoDB uses this primary key value to
search for the row from the clustered index. Note that if the
primary key is long, the secondary indexes use more space.
InnoDB compares CHAR and
VARCHAR strings of different lengths such that
the remaining length in the shorter string is treated as if padded
with spaces.
