Search code examples
mysqlinnodb

MySQL innodb b-tree rebalance async in background or finish on per write operation if rebalance is needed

I just curious about when mysql innodb execute rebalance operation when it is needed.

Thanks.

Solution

  • InnoDB must update a B-tree synchronously (at the time of your INSERT/UPDATE/DELETE) if the index is a UNIQUE KEY or PRIMARY KEY.

    Otherwise, for non-unique indexes, the changes are written to the InnoDB change buffer. These supplement the B-tree for the index.

    The changes are merged into the B-tree either the next time someone's query reads those index entries, or else they are merged eventually by a "merge thread." Also InnoDB has a "slow shutdown" option which fully merges all pending change buffer entries at the time you shut down MySQL Server.