Re: InnoDB File Size

[Rick James]

Probably somewhere in between.

InnoDB stores everything in BTree structures. Deletions tend leave empty space in blocks; these empty spaces will be used for inserts only if the inserted rows belong in those blocks of the BTree. Often the inserts will go elsewhere, possibly causing block splits that will cause the file to grow.

The BTree blocks are allocated inside ibdata1 or a .ibd file. If a block becomes completely empty, then it is available for reallocation, but it is not given back to the OS. Hence, ibdata1 and .ibd files never shrink.

Three things do shrink .ibd files (but not ibdata1):
TRUNCATE
OPTIMIZE
ALTER
But they are costly and/or time consuming.

InnoDB blocks are 16KB. Each secondary index is kept in its own BTree, similar to the data. The PRIMARY KEY is kept with the data.

BLOBs are stored in a different way, with a 1MB allocation unit; I have not covered this wrinkle above.