Master MySQL: Deep Dive into Execution Flow, Logs, Indexes, Transactions, and Optimization

1. SQL Statement Execution Process

MySQL can be roughly divided into the Server layer and the Storage Engine layer.

Server Layer

Connector

: After the TCP handshake the server validates the login user. Changes to user permissions after a connection is created do not affect the existing connection; a re‑login is required. Query Cache: Stores the result of a query. Removed in MySQL 8.0 because the cache invalidates too frequently. Parser: Checks whether the input SQL conforms to MySQL syntax. Optimizer: Generates multiple execution strategies and automatically selects the optimal one. Executor: Checks permissions and finally submits the task to the storage engine.

Storage Engine Layer

Responsible for data storage and retrieval. It follows a plug‑in architecture and supports engines such as InnoDB, MyISAM, and Memory. Since MySQL 5.5.5, InnoDB is the default storage engine.

SQL Execution Order

2. BinLog, RedoLog, UndoLog

BinLog

BinLog

records all table definition changes (e.g., CREATE, ALTER) and data modifications (INSERT, UPDATE, DELETE). It is used for master‑slave replication and has three logging modes.

STATEMENT Mode

Content : BinLog records only the SQL statements that may cause data changes. Advantage : Very low log volume and I/O consumption, giving the best performance. Disadvantage : Non‑deterministic functions (e.g., uuid() ) produce different results on replay, which may cause unexpected behavior.

ROW Mode

Content : BinLog records the before‑image and after‑image of each row operation. Advantage : Guarantees exact data restoration, ensuring safety and allowing concurrent replication and recovery. Disadvantage : Log size becomes very large; high‑cost for updates on wide rows and requires special commands to read.

MIXED Mode

Content : A hybrid of STATEMENT and ROW modes. Detail : Mostly uses STATEMENT; switches to ROW for NDB storage engine, non‑deterministic functions, INSERT DELAY , and temporary tables.

Master‑Slave Synchronization Process

1. The master must enable binary logging to record any data‑changing events. 2. The slave starts an I/O thread that pretends to be a MySQL client and requests the master’s binlog events. 3. The master starts a dump thread, compares the requested position, and sends events from the first binlog file if no position is specified. 4. The slave writes received events to its relay log and records the exact binlog file and position. 5. The slave’s SQL thread reads the relay log and re‑executes the events locally.

MySQL’s default replication is asynchronous with parallel replication. If the master crashes after the slave has processed events, the logs may be lost, leading to two concepts:

Full synchronous replication: After the master writes to binlog, it forces synchronization to the slave; the client returns only after all slaves finish, which hurts performance.

Semi‑synchronous replication: The slave sends an ACK after writing the log; the master proceeds after receiving at least one ACK.

RedoLog

RedoLog implements Write‑Ahead Logging: first write the log, then write to disk. In the analogy, the account book = BinLog, the scratch pad = RedoLog.

1. InnoDB writes updates to RedoLog (scratch pad) and updates memory; later it flushes to disk. 2. If RedoLog cannot handle too many updates, part of it is flushed to disk and the used portion is cleared.

RedoLog contains write pos and checkpoint pointers.

write pos : Current write position; wraps around after reaching the end of file 3. checkpoint : Position to be cleared after flushing data to the data file.

When write pos catches up with checkpoint, the redo log is full and must pause updates until space is reclaimed.

RedoLog guarantees crash‑safe recovery; after a crash, all committed records are preserved.

Two‑phase commit between BinLog and RedoLog:

1. Prepare phase → 2. Write BinLog → 3. Commit. If a crash occurs before step 2, the transaction rolls back; if before step 3, the system automatically commits.

Differences between BinLog and RedoLog:

RedoLog is InnoDB‑specific; BinLog is a server‑level feature usable by all engines.

RedoLog is a physical log (records page‑level changes); BinLog is a logical log (records the original SQL).

RedoLog is circular and has fixed space; BinLog is append‑only and creates new files when full.

UndoLog

UndoLog is generally a logical log with two types:

Insert undo log – created when a new row is inserted; discarded after commit.

Update undo log – created for UPDATE/DELETE; needed for rollback and snapshot reads, purged only when no longer required.

3. Indexes in MySQL

Common index models are hash tables, ordered arrays, and search trees (B+ trees).

Hash table : KV structure suitable only for equality queries, not range queries. Ordered array : Works for static storage engines; similar to Java’s ArrayList . Search tree : Implemented as an N‑ary (B+) tree and widely used in the storage‑engine layer.

B+ tree non‑leaf nodes store only indexes, allowing more entries and fewer I/O operations. Leaf nodes are linked, making range queries efficient and results stable. The structure reduces back‑tracking scans compared with a B tree.

Index advantages:

1. Unique indexes guarantee row uniqueness. 2. Speed up query execution. 3. Accelerate table joins. 4. Significantly reduce grouping and sorting time. 5. Enable optimizer hints to improve performance.

Index disadvantages:

1. Creation and maintenance consume time. 2. Creating an index locks the table, potentially affecting other operations. 3. Indexes occupy disk space. 4. DML operations trigger index maintenance, which may degrade performance.

Index design principles – avoid:

1. Over‑indexing; too many indexes increase maintenance cost. 2. Indexing frequently updated columns. 3. Indexing small tables.

Prefer:

1. Columns with low duplication rate (high cardinality). 2. Columns with unique constraints. 3. Columns used often in GROUP BY or ORDER BY. 4. Columns frequently used in query predicates.

Index failure scenarios include wildcard searches, implicit type conversion, OR conditions, and missing left‑most prefix in composite indexes.

Key index concepts:

Primary key index : Leaf nodes store the full row; InnoDB calls it a clustered index. Unique index : Enforces uniqueness but allows NULL. Secondary index : Leaf nodes store the primary key value; InnoDB calls it a secondary index. Covering index : Contains all columns needed by a query, eliminating the need to read the table. Composite index : Up to 16 columns combined; order matters. Left‑most prefix rule : For a composite index (a,b,c), queries must use a or a,b as the leftmost columns to be effective.

4. SQL Transaction Isolation Levels

ACID’s four properties: Atomicity: All operations in a transaction succeed or all fail. Consistency: Transactions leave the database in a valid state. Isolation: Transactions do not interfere with each other; MySQL supports four isolation levels. Durability: Once committed, changes persist.

ACID’s C focuses on single‑database consistency, while CAP’s C refers to cross‑replica consistency.

Possible anomalies:

Dirty read : Transaction B modifies data without committing; Transaction A reads it and may act on uncommitted data. Non‑repeatable read : Same query returns different results because another transaction modified data. Phantom read : New rows appear in a range query after another transaction inserts them.

Isolation levels (from low to high):

Read Uncommitted : Can read uncommitted changes. Read Committed (Oracle default): Can read only committed changes. Repeatable Read (MySQL default): Guarantees consistent reads for the duration of the transaction. Serializable : Transactions are fully isolated; others cannot modify data until commit.

5. Locks in MySQL

MySQL uses both pessimistic and optimistic locking concepts.

Pessimistic Lock

Advantage : Suitable for write‑heavy workloads; ensures data safety. Disadvantage : Increases overhead and reduces throughput for read‑heavy scenarios.

Optimistic Lock

Advantage : Ideal for read‑heavy workloads; avoids lock overhead. Disadvantage : In write‑heavy contention, retries may be costly. Implementation : Uses a version number or timestamp (CAS‑like).

MySQL lock granularity:

Table‑level lock – coarse, supported by both MyISAM and InnoDB.

Page‑level lock – intermediate granularity between row and table.

Row‑level lock – finest granularity; reduces conflicts but adds overhead.

6. MVCC (Multi‑Version Concurrency Control)

MVCC maintains multiple versions of a row to allow non‑blocking reads.

Current Read

Operations such as SELECT ... LOCK IN SHARE MODE , SELECT ... FOR UPDATE , UPDATE , INSERT , DELETE read the latest version and acquire locks, behaving like pessimistic locking.

Snapshot Read

Plain SELECT without locking reads a consistent snapshot based on the transaction’s read view, avoiding locks and providing higher concurrency.

Combining MVCC with locks:

MVCC + pessimistic lock – MVCC solves read/write conflicts; pessimistic lock solves write/write conflicts. MVCC + optimistic lock – MVCC solves read/write conflicts; optimistic lock solves write/write conflicts.

MVCC Implementation Details

Each record stores four hidden fields, an undo log, and a consistent read view. DB_TRX_ID: 6‑byte ID of the transaction that last inserted or modified the row. DB_ROLL_PTR: 7‑byte pointer to the previous version in the rollback segment. DB_ROW_ID: 6‑byte hidden auto‑increment ID; InnoDB creates a clustered index if no primary key exists. FLAG: Deletion flag indicating logical deletion.

7. Buffer Pool

The buffer pool caches frequently accessed pages (default 16 KB) to reduce disk I/O. InnoDB uses an LRU‑based algorithm, dividing pages into “old generation” and “new generation” to mitigate pre‑read failures and buffer‑pool pollution.

8. Table Slimming

DELETE marks rows as reusable but does not shrink the physical file, leaving “holes”. Rebuilding a table removes these holes.

1. Create a new table B with the same structure as A. 2. Copy data from A to B ordered by primary key. 3. Replace A with B.

Commands: ALTER TABLE A ENGINE=InnoDB (use with caution) or use tools like gh‑ost .

9. SQL Joins, Statistics, Random Queries

Seven join types are illustrated (image omitted for brevity).

Statistics: MyISAM stores total row count on disk; InnoDB must count rows, making COUNT(*) the most efficient.

Random row selection: SELECT word FROM words ORDER BY RAND() LIMIT 3; Using ORDER BY RAND() creates a temporary table and filesort, which is costly. An alternative method avoids sorting:

SELECT COUNT(*) INTO @C FROM t;
SET @Y1 = FLOOR(@C * RAND());
SET @Y2 = FLOOR(@C * RAND());
SET @Y3 = FLOOR(@C * RAND());
SELECT * FROM t LIMIT @Y1,1;
SELECT * FROM t LIMIT @Y2,1;
SELECT * FROM t LIMIT @Y3,1;

EXISTS vs IN:

IN builds a Cartesian product; EXISTS evaluates the subquery per outer row. Use EXISTS for large inner tables and IN for small ones. NOT EXISTS is generally faster than NOT IN.

10. MySQL Optimization

Optimization directions: SQL & indexes, table structure, system configuration, hardware.

1. Reduce data access: appropriate column types, compression, index usage. 2. Return less data: select needed columns, paginate. 3. Reduce round‑trips: batch DML, stored procedures. 4. Reduce CPU load: avoid unnecessary sorting and full‑table scans. 5. Partition tables to enable parallelism.

SQL statement tuning examples:

1. Build covering indexes to avoid back‑table lookups. 2. Prefer IN over OR; avoid NOT IN, NOT LIKE, and functions on indexed columns. 3. Beware implicit type conversion that can disable indexes. 4. Place small tables first in joins; order WHERE conditions from most selective to least.

SQL tuning workflow:

Identify slow queries via the slow‑query log.

Run EXPLAIN to inspect index usage.

Focus on key, key_len, type, and Extra columns.

Optimize based on findings and repeat.

Table‑structure recommendations:

1. Use tinyint, smallint, mediumint instead of int; add UNSIGNED when possible. 2. Keep VARCHAR length minimal. 3. Prefer TIMESTAMP over DATETIME. 4. Limit columns per table (ideally ≤20). 5. Avoid NULL columns; use empty strings or default values.

Read‑write separation: writes go to the master, reads to slaves. Implementation can be via code wrappers or database middleware.

Sharding (vertical then horizontal): split by business modules (orders, users, etc.) and then by a sharding key such as user_id, using tools like Mycat or Sharding‑Sphere.

TiDB is a distributed MySQL‑compatible database offering strong consistency, horizontal scalability, and automatic failover. It is suitable for large‑scale data, high‑concurrency workloads, and real‑time analytics, but unnecessary for small datasets (<50 M rows) or simple single‑node use cases.

Suitable: MySQL performance bottlenecks, massive data, high‑concurrency reads/writes, distributed transactions. Not suitable: Small‑scale workloads, low‑availability requirements, or when a single MySQL instance suffices.

End

Additional MySQL resources:

1. SQL basics: https://juejin.im/post/6844903790571700231 2. SQL interview guide: https://sowhat.blog.csdn.net/article/details/71158104 3. MySQL deep dive: https://www.jianshu.com/nb/22933318