Re: mysql online ddl still lock

[Rick James]

Tables and INDEXes in InnoDB are stored in BTree structures.

INSERTs... When a block in a BTree must be split, 1 full block becomes 2 half-full blocks. Those blocks then fill up as you insert more rows. This gradually leads to the BTree being about 69% full.

DELETEs... InnoDB combines adjacent blocks that are less than half full.

A "point query" (SELECT ... WHERE primary_key = constant) is very efficient -- For a million-row table the BTree will be about 3 levels deep. The query will drill down those three blocks in the BTree. For a trillion-row table, it is about 6 levels. The number of levels rarely changes. That is, whether the table is fully defragmented or only 69% full, the number of levels is likely to be the same.

A "range query" (SELECT ... WHERE primary_key BETWEEN 123 AND 456) will do a point query to 123, then scan forward through the block(s) until the 456. True, if it is 100% full, it will need fewer blocks than if it is 69% full. But this has zero impact all the necessary blocks are cached.

MVCC -- "Multi version concurrency control" -- If you have two connections doing transactions that touch the same rows, InnoDB will (in certain circumstances) hang onto old records until a transaction finishes. That is, there can be "multiple versions" of a single row for a brief period of time. That can cause block splits that you don't expect.

But what about "Data_free" (from SHOW TABLE STATUS and `information_schema`)? It shows one of several types of "free" space. It does not show the "69%". There is not good way to measure the free space.

Do you want to defragment after every query? The cost of that is quite unreasonable. Don't bother doing OPTIMIZE -- ever.