0

I want to know if I fire query like insert into emp ... What are the steps involved in query ?

It will go through InnoDB buffer pool?

Thanks for your help.

  • This question is way to broad to answer here.. if you really want to know it you could download and study the MySQL sourcecode.. – Raymond Nijland Jan 21 '19 at 16:13
1

Here's a broad answer to your broad question.

All DMLs and SELECTs always goes through the buffer_pool in InnoDB. It is a cache of the blocks that compose the BTrees that compose the data (organized by the necessary PRIMARY KEY) and each secondary INDEX.

For DML (broadly):

  1. Fetch a block, if not already in the buffer pool.
  2. Modify whatever.
  3. Mark the block "dirty".
  4. Rinse and repeat.
  5. Eventually (as a background task), the block will be written back to disk. Meanwhile, it is in the cache so that other operations can avoid having to fetch it from disk.

Modifying a non-unique index involves the "Change buffer". That is sort of a "delayed write" for such changes.

SELECTs:

  1. Fetch a block (if not already in buffer pool).
  2. Fetch row(s) out of that block.
  3. Rinse and repeat.

Note: in both DML and SELECT, the discovery of where a row usually involves drilling down a BTree, which involves fetching nodes (blocks) of the BTree. They, too, are cached (and tend to live a long time in the buffer pool).

If you want more details, start another Question with more specifics.

| improve this answer | |

Not the answer you're looking for? Browse other questions tagged or ask your own question.