Blog

This article provides an in-depth introduction to the logical architecture of MySQL and the execution process of SQL query statements within MySQL. First, the article explains in detail the roles of connectors, query caches, and analyzers in the execution of SQL query statements, as well as the difference between long and short joins. Then, it explains how MySQL determines the execution plan and starts executing the statement through the processing of the optimizer and executor stages. The executor executes the query based on the engine definition of the table, using the interfaces provided by the engine, and the logic of execution on indexed and non-indexed tables is presented. Finally, the logical architecture of MySQL and the stages of the SQL statement execution process are summarized to provide the reader with an initial impression. The entire article helps readers gain a deeper understanding of the inner workings of MySQL by disassembling its underlying architecture and explaining the execution process of SQL query statements.

When executing an UPDATE statement in MySQL, multiple components work together to ensure efficiency, consistency, and durability. The process begins with the connector handling authentication, followed by the parser and optimizer determining the best execution plan. The executor then interacts with the InnoDB storage engine, which utilizes buffer pools, undo logs, redo logs, and binlogs to maintain data integrity and support transaction management. This article delves into how MySQL processes an update query, from parsing and optimization to logging and checkpointing, ensuring data reliability even in the event of a crash.

In MySQL, transaction isolation levels define how and when the changes made by one transaction become visible to other concurrent transactions. The four standard isolation levels are: Read Uncommitted: Transactions can see uncommitted changes made by others, leading to potential "dirty reads." Read Committed: A transaction only sees changes committed before it began, preventing dirty reads but allowing non-repeatable reads. Repeatable Read: Ensures that if a transaction reads a row, it will see the same data throughout its execution, preventing non-repeatable reads but not phantom reads. Serializable: The strictest level, transactions are executed in a way that ensures complete isolation, preventing dirty reads, non-repeatable reads, and phantom reads, but potentially impacting performance due to increased locking.