MySQL Internals: SQL Execution Flow [closed]

Question

Explain the sequence an UPDATE query goes through from memory to disk. And considering a crash at every stage how 'durability' is maintained.

1. innodb_log_buffer_size
2. log_buffer
3. Double write buffer
4. ib_logfile0, ib_logfile1 (redolog)
5. ibdata (Single tablespace or multiple tablespace,innodb_file_per_table=1)
6. binary log (WAL)
7. general query log (assuming enabled)

Reference:

MySQL Internals

XtraDb-InnoDB Internals

For Example:

1. First data will be written in buffer pool which contains the page cache of tables that are used recently.
2. If it is a READ request it is done in foreground like first it checks if the result is available in buffer pool , otherwise it hits the disk
3. If it is a WRITE request, the change will be written in buffer pool of table.ibd file and then to ib_logfiles .
4. After innodb_flush_log_at_trx_commit the changes are flushed to disk (table.ibd).

At what stage it will be written in binary log (contains both committed and uncommitted transaction) or general log (contains everything including SELECT and unsuccessful queries).

Note1: Not interested in Optimizer, Parser, Other Storage Engines

Note2: Kindly do not mark this as too broad, A short precise answer covering the 7 components mentioned will be enough

Answer 1

There isn't a clear-cut flow chart online for this, but if you want to understand how it all comes together for InnoDB, one of the best explanations I have seen are at M18 Deep Dive InnoDB Transactions and Write Paths. The slideshow is available at https://mariadb.org/wp-content/uploads/2018/07/Deep-Dive_-InnoDB-Transactions-and-Write-Paths.pdf

That presentation is for MariaDB, but MySQL should be quite similar.

The presentation of overall architecture of MySQL 8.0 at https://www.percona.com/resources/webinars/mysql-80-architecture-and-enhancement is also quite clear.