Step 1: Read about BTrees. Better yet, read about the B+Tree variant, since that is what InnoDB uses. (I recommend Wikipedia.)
The data is ordered by the PRIMARY KEY in one B+Tree.
Each secondary key is ordered by that key in another B+Tree, with the leaf nodes containing the Primary Key column(s) so that it can reach over into the data B+Tree to get the whole record.
Yes, there are blocks, but no they are not necessarily consecutive. Instead, the B+Tree has a mechanism to provide a "tree" to find any particular block and for finding the 'next' and 'previous' blocks.
Within a 16KB block is several "rows", plus block overhead, row overhead, and column overhead. A block contains info for only one table (or one secondary index).
When any operation is performed on a column in a row, the following steps occur:
- Fetch the entire block from disk. Often, the block is already cached in the "buffer_pool", so this step is fast.
- Locate the row in the block. Or the place in the block where the
PRIMARY KEY says it should be.
- Depending on the operation:
- For
SELECT, fetch the desired column(s).
- For
INSERT, the row is inserted. If this makes the block too big, then a "block split" occurs.
- For
DELETE, the row is deleted. This could lead to merging two blocks together.
- For
UPDATE, a new copy of the row is made. If it is bigger or smaller, then the above steps may happen.
- The block is marked as "dirty". Eventually, it will be written back to disk.
I left out transactional 'ACID', index details, etc.
Caveat: What I have said applies to MySQL's InnoDB. And it does not apply to FULLTEXT or SPATIAL indexes. Other vendors do other things.
