How mysql data is stored internally?

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:

1. Fetch the entire block from disk. Often, the block is already cached in the “buffer_pool”, so this step is fast.
2. Locate the row in the block. Or the place in the block where the PRIMARY KEY says it should be.
3. 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.
4. 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.

Taken from this answer

Related article: InnodBD internal structure diagram