The physical row structure for an InnoDB
table depends on the row format specified when the table was
created. InnoDB
uses the
COMPACT
format by default, but the
REDUNDANT
format is available to retain
compatibility with older versions of MySQL. To check the row
format of an InnoDB
table, use
SHOW TABLE STATUS
.
The compact row format decreases row storage space by about 20% at the cost of increasing CPU use for some operations. If your workload is a typical one that is limited by cache hit rates and disk speed, compact format is likely to be faster. If the workload is a rare case that is limited by CPU speed, compact format might be slower.
Rows in InnoDB
tables that use
REDUNDANT
row format have the following
characteristics:
Each index record contains a six-byte header. The header is used to link together consecutive records, and also in row-level locking.
Records in the clustered index contain fields for all user-defined columns. In addition, there is a six-byte transaction ID field and a seven-byte roll pointer field.
If no primary key was defined for a table, each clustered index record also contains a six-byte row ID field.
Each secondary index record also contains all the primary key fields defined for the clustered index key that are not in the secondary index.
A record contains a pointer to each field of the record. If the total length of the fields in a record is less than 128 bytes, the pointer is one byte; otherwise, two bytes. The array of these pointers is called the record directory. The area where these pointers point is called the data part of the record.
Internally, InnoDB
stores fixed-length
character columns such as
CHAR(10)
in a fixed-length
format. InnoDB
does not truncate trailing
spaces from VARCHAR
columns.
An SQL NULL
value reserves one or two
bytes in the record directory. Besides that, an SQL
NULL
value reserves zero bytes in the
data part of the record if stored in a variable length
column. In a fixed-length column, it reserves the fixed
length of the column in the data part of the record.
Reserving the fixed space for NULL
values
enables an update of the column from NULL
to a non-NULL
value to be done in place
without causing fragmentation of the index page.
Rows in InnoDB
tables that use
COMPACT
row format have the following
characteristics:
Each index record contains a five-byte header that may be preceded by a variable-length header. The header is used to link together consecutive records, and also in row-level locking.
The variable-length part of the record header contains a bit
vector for indicating NULL
columns. If
the number of columns in the index that can be
NULL
is N
, the
bit vector occupies
CEILING(
bytes. (For example, if there are anywhere from 9 to 15
columns that can be N
/8)NULL
, the bit vector
uses two bytes.) Columns that are NULL
do
not occupy space other than the bit in this vector. The
variable-length part of the header also contains the lengths
of variable-length columns. Each length takes one or two
bytes, depending on the maximum length of the column. If all
columns in the index are NOT NULL
and
have a fixed length, the record header has no
variable-length part.
For each non-NULL
variable-length field,
the record header contains the length of the column in one
or two bytes. Two bytes will only be needed if part of the
column is stored externally in overflow pages or the maximum
length exceeds 255 bytes and the actual length exceeds 127
bytes. For an externally stored column, the two-byte length
indicates the length of the internally stored part plus the
20-byte pointer to the externally stored part. The internal
part is 768 bytes, so the length is 768+20. The 20-byte
pointer stores the true length of the column.
The record header is followed by the data contents of the
non-NULL
columns.
Records in the clustered index contain fields for all user-defined columns. In addition, there is a six-byte transaction ID field and a seven-byte roll pointer field.
If no primary key was defined for a table, each clustered index record also contains a six-byte row ID field.
Each secondary index record also contains all the primary key fields defined for the clustered index key that are not in the secondary index. If any of these primary key fields are variable length, the record header for each secondary index will have a variable-length part to record their lengths, even if the secondary index is defined on fixed-length columns.
Internally, InnoDB
stores fixed-length,
fixed-width character columns such as
CHAR(10)
in a fixed-length
format. InnoDB
does not truncate trailing
spaces from VARCHAR
columns.
Internally, InnoDB
attempts to store
UTF-8
CHAR(
columns in N
)N
bytes by trimming
trailing spaces. (With REDUNDANT
row
format, such columns occupy 3 ×
N
bytes.) Reserving the minimum
space N
in many cases enables
column updates to be done in place without causing
fragmentation of the index page.
User Comments
Add your own comment.