- All user data in InnoDB tables is stored in pages comprising a B-tree index (the clustered index). In some other database systems, this type of index is called an “index-organized table”. Each row in the index node contains the values of the (user-specified or system-generated) primary key and all the other columns of the table.
- Updates to rows usually rewrite the data within the same page
- by default,
innodb_file_per_table is on. This means each table will have an ibd file
- each ibd file has multiple segments, each of which is associated with an index
- each segment consists of multiple 1MB extent
- Each extend has multiple 16KB pages
- On delete, the space of the deleted record is marked reusable, if it reaches MERGE_THRESHOLD (default 50%), innodb will try merging it with neighboring pages, and leave the original page blank.
- Check
index_page_merge_successful in INFORMATION_SCHEMA.INNODB_METRICS
Optimize table
- If you do sequential deletes instead of random deletes, most likely you don’t need to run
optimize table, because
- Purpose of
optimize table is to
- reduce the
data_free value in information_schema.tables.
- defrag index pages.
data_free marks the reusable space, and is not accrurate if the table has variable-length column > 768 bytes,e.g., varchar, text- Innodb implements
opitmize table by alter table force to use the temp table. This also means additional spaces needed during the operation