The physical structure of an InnoDB index

The physical structure of an InnoDB index

In addition to spatial indexes, InnoDB indexes are B-tree data structures. Spatial indexes use R trees, which are specialized data structures for indexing multidimensional data. Index records are stored in leaf pages of their B-tree or R-tree data structures. The default size of the index page is 16KB.

When inserting new records into InnoDB’s clustered index, InnoDB tries to leave 1/16 of the page free for future insertion and update of index records. If the index records are inserted sequentially (ascending or descending), the resulting index page is about 15/16. If records are inserted in random order, the size of the page is 1/2 to 15/16.

InnoDB performs bulk loading when creating or rebuilding b-tree indexes. This method of index creation is called sorted index building. The innodb_fill_factor configuration option defines the percentage of space per B-tree page that is reserved for future exponential growth by filling the space as a sort indicator during the build process. Spatial indexes do not support sorted index building. For more information, see “Sorted Index Building.” An Innodb_FILL_factor of 100 leaves in the space of the clustered index page is set to 1/16 free for future exponential growth.

If InnoDB index page padding factor drops below MERGE_THRESHOLD (default is 50% if not specified), InnoDB tries to shrink the index tree to free the page. This MERGE_THRESHOLD setting applies to both B-tree indexes and R-tree indexes. For more information, see configuring merge Thresholds for Index Pages.

You can define the page size of all table Spaces in a MySQL instance by setting configuration options before initializing the MySQL instance. Once an instance’s page size is defined, it cannot be changed without reinitializing the instance. Supported sizes are 64KB, 32KB, 16KB (default), 8KB, and 4KB. InnoDBinnodb_page_size

Support for 32KB and 64KB page sizes was added in MySQL 5.7. See the Innodb_page_size documentation for more information.

MySQL instances that use a particular InnoDB page size cannot use data files or log files from instances that use different page sizes.

More content welcome to pay attention to my personal public number “Han Elder brother has words”, 100G artificial intelligence learning materials, a large number of back-end learning materials waiting for you to take.