The idea: pages in extents, extents in segments

With innodb_file_per_table = ON (the default), each table lives in its own file, for example orders.ibd. InnoDB sees that file as an array of pages of 16 KB (innodb_page_size), numbered from 0. Page n sits at byte offset n × 16 KB. 64 pages in a row make an extent of 1 MB. Each index of the table owns segments: one for its leaf pages and one for its non-leaf pages. A segment collects the pages and extents that index uses. The canvas shows the leaf segment of the PRIMARY index (the rows themselves) growing inside the file.

orders.ibd drawn as four 1 MB extents. Extent 0 starts with pages 0 FSP_HDR, 1 IBUF_BITMAP, 2 INODE, 3 SDI and 4, the PRIMARY root, followed by fragment pages. Extents 1 and 2 belong to the leaf segment of PRIMARY, extent 3 is on the FREE list. The INODE page points to the leaf segment, which holds 32 fragment page slots and a list of whole extents.
A page is 16 KB, an extent is 64 pages, and a segment is the set of pages and extents one index uses.

The first pages of an .ibd file

The first pages of every file-per-table tablespace are bookkeeping:

PageTypeWhat it holds
0FSP_HDRthe space header (size in pages, the extent lists FREE, FREE_FRAG, FULL_FRAG) and one extent descriptor (XDES) per extent: its state and a 2-bit-per-page free bitmap
1IBUF_BITMAPfree space hints for the change buffer
2INODEthe segment inodes: per segment, 32 fragment page slots and its own extent lists
3SDIthe table definition (serialized dictionary information, MySQL 8.0)
4INDEXthe root page of the PRIMARY index; more index roots follow

The extent lists are linked lists through the descriptors. FREE holds whole unused extents. FREE_FRAG and FULL_FRAG hold fragment extents, whose pages are handed out one at a time to any segment. An extent that a segment owns is in state FSEG and sits on that segment's own lists instead.

Fragment pages first, then whole extents

A new index would waste space if it reserved 1 MB for its first few pages. So a segment's first 32 pages are single fragment pages taken from a FREE_FRAG extent and recorded in the 32 slots of its inode. Only when the segment already uses 32 pages does it take whole extents from the FREE list. From then on new pages come from its own extents, usually the page right after the previous one (the hint), so an index filled in key order is laid out in page order too. Demo: a new table grows shows pages 5–36 as fragment pages and then extent 1 going to the leaf segment.

A small table therefore costs only a few pages. That is why a schema with thousands of tiny tables still works: each one starts at 112 KB on disk (7 pages) and grows page by page until it reaches one extent.

How the file grows

When a segment needs a whole extent and the FREE list is empty, InnoDB extends the file. A file-per-table tablespace smaller than 32 MB grows by one extent (1 MB); a bigger one by 4 extents (4 MB) at a time. In MySQL 8.0.23 and later the table option AUTOEXTEND_SIZE sets a larger step, for example ALTER TABLE orders AUTOEXTEND_SIZE = 64M, which means fewer, bigger file extensions and less filesystem fragmentation. innodb_autoextend_increment applies only to the system tablespace ibdata1. Demo: loading more rows grows the file extent by extent.

DELETE frees pages, but the file keeps its size

A DELETE only delete-marks the rows. Purge removes them later (see InnoDB undo logs and purge), and a leaf page that becomes empty is freed back to its segment. A freed fragment page becomes a free page of its fragment extent. A freed page in a segment's extent stays reserved for that segment, until the extent has no used page left: then the whole extent goes back to the space's FREE list. The file itself never shrinks. In Demo: DELETE frees pages, the file keeps its size, the 10,000 oldest rows go away, extent 1 returns to the FREE list, and orders.ibd is still 4 MB.

Two numbers in information_schema.TABLES describe this. DATA_LENGTH is the pages reserved by the clustered index × 16 KB. DATA_FREE counts only the free space in whole free extents (InnoDB rounds it to extents and keeps a small reserve), so free pages scattered inside used extents don't show up at all. The freed space is not lost: Demo: new rows reuse the freed pages inserts 4,000 rows and the file does not grow, because the segment fills its own free pages and then takes extent 1 back.

Three rows of extents. After loading 20,000 rows: extents 0 to 3 used, file 4 MB. After DELETE of the 10,000 oldest rows: extent 0 has free fragment pages, extent 1 is free, extent 2 is nearly full and extent 3 partly full; the file is still 4 MB and DATA_FREE is 1 MB. After OPTIMIZE TABLE: the 100 leaf pages sit in extents 0, 1 and 2 in key order; extent 3 is gone and the file is 3 MB.
DELETE leaves holes inside the file; only a rebuild gives the space back to the operating system.

OPTIMIZE TABLE rebuilds the file

For InnoDB, OPTIMIZE TABLE is mapped to ALTER TABLE … FORCE, and the server says so: Table does not support optimize, doing recreate + analyze instead. It creates a new tablespace (#sql-ib…ibd), copies the rows in primary key order with a sorted index build, renames the new file to orders.ibd and deletes the old one. The pages are contiguous again and the file is only as big as the data. The rebuild is online (other sessions can read and write; their changes are kept in an online log and applied at the end) but it rewrites the whole table and needs free disk space for a second copy. Demo: OPTIMIZE TABLE rebuilds into a smaller file.

After DELETEAfter OPTIMIZE TABLE
File sizeunchangedabout the size of the data
Free pagesscattered inside extents, reused by new rowsnone, except the last partly used extent
Key order vs page ordernew rows go to old holesthe same again
Costnonerewrites every row and index, needs a second copy on disk

Fragmentation

Two kinds of fragmentation grow in a table that changes a lot. Free space inside pages: random keys (UUIDs) split pages in the middle, and splits leave both halves about half full (see inside an InnoDB index page); deletes leave holes until a page falls below MERGE_THRESHOLD (50% by default) and is merged with a neighbour. Scattered page order: when new rows land in freed pages, neighbouring keys are no longer on neighbouring pages, so a range scan reads pages all over the file and read-ahead helps less. Both show up as more pages than the data needs. A rebuild fixes both, but on SSDs the cost of scattered order is small; rebuild when DATA_FREE is a large share of the file and the disk space is wanted back, not by habit.

See also the InnoDB buffer pool for how these pages are cached in memory, the doublewrite buffer for how they are written back safely, and the PostgreSQL heap page for how PostgreSQL lays out a table file.

What the page leaves out

The canvas draws one index and only its leaf segment's growth: secondary indexes, the SDI segments and the non-leaf segment's growth are left out (with hundreds of pointers per page, the root is enough here). Segments bigger than 32 extents reserve up to 4 extents at a time, and InnoDB keeps a few extents in reserve for B-tree splits. The page also leaves out the XDES pages every 16,384 pages, the extent size for other page sizes (64 pages only for 16 KB), compressed and encrypted tablespaces, general tablespaces and ibdata1, undo tablespaces, page split details, innodb_fill_factor (a sorted build leaves 1/16 of each page free), and the exact rules of the allocation hint.