MySQL 8.0 Reference Manual Including MySQL NDB Cluster 8.0
The data files that you define in the configuration file using the
innodb_data_file_path
configuration option form the InnoDB
system tablespace.
The files are logically concatenated to form the system
tablespace. There is no striping in use. You cannot define where
within the system tablespace your tables are allocated. In a newly
created system tablespace, InnoDB
allocates
space starting from the first data file.
To avoid the issues that come with storing all tables and indexes
inside the system tablespace, you can enable the
innodb_file_per_table
configuration option (the default), which stores each newly
created table in a separate tablespace file (with extension
.ibd
). For tables stored this way, there is
less fragmentation within the disk file, and when the table is
truncated, the space is returned to the operating system rather
than still being reserved by InnoDB within the system tablespace.
For more information, see
Section 15.6.3.2, “File-Per-Table Tablespaces”.
You can also store tables in
general
tablespaces. General tablespaces are shared tablespaces
created using CREATE TABLESPACE
syntax. They can be created outside of the MySQL data directory,
are capable of holding multiple tables, and support tables of all
row formats. For more information, see
Section 15.6.3.3, “General Tablespaces”.
Each tablespace consists of database
pages. Every tablespace in a
MySQL instance has the same page
size. By default, all tablespaces have a page size of 16KB;
you can reduce the page size to 8KB or 4KB by specifying the
innodb_page_size
option when you
create the MySQL instance. You can also increase the page size to
32KB or 64KB. For more information, refer to the
innodb_page_size
documentation.
The pages are grouped into
extents of size 1MB for pages
up to 16KB in size (64 consecutive 16KB pages, or 128 8KB pages,
or 256 4KB pages). For a page size of 32KB, extent size is 2MB.
For page size of 64KB, extent size is 4MB. The
“files” inside a tablespace are called
segments in
InnoDB
. (These segments are different from the
rollback segment,
which actually contains many tablespace segments.)
When a segment grows inside the tablespace,
InnoDB
allocates the first 32 pages to it one
at a time. After that, InnoDB
starts to
allocate whole extents to the segment. InnoDB
can add up to 4 extents at a time to a large segment to ensure
good sequentiality of data.
Two segments are allocated for each index in
InnoDB
. One is for nonleaf nodes of the
B-tree, the other is for the
leaf nodes. Keeping the leaf nodes contiguous on disk enables
better sequential I/O operations, because these leaf nodes contain
the actual table data.
Some pages in the tablespace contain bitmaps of other pages, and
therefore a few extents in an InnoDB
tablespace
cannot be allocated to segments as a whole, but only as individual
pages.
When you ask for available free space in the tablespace by issuing
a SHOW TABLE STATUS
statement,
InnoDB
reports the extents that are definitely
free in the tablespace. InnoDB
always reserves
some extents for cleanup and other internal purposes; these
reserved extents are not included in the free space.
When you delete data from a table, InnoDB
contracts the corresponding B-tree indexes. Whether the freed
space becomes available for other users depends on whether the
pattern of deletes frees individual pages or extents to the
tablespace. Dropping a table or deleting all rows from it is
guaranteed to release the space to other users, but remember that
deleted rows are physically removed only by the
purge operation, which happens
automatically some time after they are no longer needed for
transaction rollbacks or consistent reads. (See
Section 15.3, “InnoDB Multi-Versioning”.)
The maximum row length is slightly less than half a database page
for 4KB, 8KB, 16KB, and 32KB
innodb_page_size
settings. For
example, the maximum row length is slightly less than 8KB for the
default 16KB InnoDB
page size. For 64KB pages,
the maximum row length is slightly less than 16KB.
If a row does not exceed the maximum row length, all of it is stored locally within the page. If a row exceeds the maximum row length, variable-length columns are chosen for external off-page storage until the row fits within the maximum row length limit. External off-page storage for variable-length columns differs by row format:
COMPACT and REDUNDANT Row Formats
When a variable-length column is chosen for external off-page
storage, InnoDB
stores the first 768 bytes
locally in the row, and the rest externally into overflow
pages. Each such column has its own list of overflow pages.
The 768-byte prefix is accompanied by a 20-byte value that
stores the true length of the column and points into the
overflow list where the rest of the value is stored. See
Section 15.10, “InnoDB Row Formats”.
DYNAMIC and COMPRESSED Row Formats
When a variable-length column is chosen for external off-page
storage, InnoDB
stores a 20-byte pointer
locally in the row, and the rest externally into overflow
pages. See Section 15.10, “InnoDB Row Formats”.
LONGBLOB
and
LONGTEXT
columns
must be less than 4GB, and the total row length, including
BLOB
and
TEXT
columns, must be less than
4GB.