From Client to Disk: The Complete Life Cycle of a MySQL Query

1. MySQL Two‑Layer Architecture

MySQL is split into a Server layer and a pluggable storage‑engine layer. The Server layer handles connection management, SQL parsing, optimization, execution, and cross‑engine features such as stored procedures, triggers, views, functions, and binlog. The storage‑engine layer actually stores and reads data; InnoDB, MyISAM, Memory, etc., can be chosen per table, with InnoDB the default since MySQL 5.5.

客户端 │
┌─┴────────────── Server 层 ──────────────┐
│  连接器 → 分析器 → 优化器 → 执行器   │
└─────────────────┬───────────────────────┘
                │  调用引擎接口
┌─────────────────┴──── 存储引擎层 ────────┐
│  InnoDB / MyISAM / Memory ...          │
└────────────────────────────────────────┘
                │
                磁盘

2. Life of a SELECT Query

SELECT * FROM orders WHERE id = 1;

Step 1 – Connector : establishes the client connection, authenticates the user, and determines privileges. Privileges are fixed for the session; changes require a new connection. Long‑lived connections save connection overhead but must be closed by wait_timeout to avoid memory leaks.

Step 2 – Query Cache (removed in MySQL 8.0) : early versions cached "SQL text → result" pairs, but any data change invalidated the whole table cache, making it more harmful than helpful. It was deprecated in 5.7.20 and removed in 8.0.

Step 3 – Parser : performs lexical analysis (identifying keywords, table names, column names, literals) and syntactic analysis (checking SQL grammar). Errors such as "You have an error in your SQL syntax" are raised here.

Step 4 – Optimizer : based on the parser’s output, the optimizer evaluates possible execution plans (different indexes, join orders) using cost estimation and selects the cheapest plan, which can be inspected with EXPLAIN.

Step 5 – Executor : re‑checks privileges for the target table, then calls the storage‑engine interface to fetch rows, apply filters, and assemble the result set. The rows field in EXPLAIN and the rows_examined metric in the slow‑query log reflect how many rows the executor asked the engine to read.

Step 6 – Storage Engine (InnoDB example) : the executor asks InnoDB for the record with id = 1. InnoDB walks the clustered B+‑tree from the root page down to the leaf, using a page‑directory binary search. Before reading a page, it is loaded into the Buffer Pool. The executor then assembles the final result and returns it to the client.

连接器(建连+鉴权) → [查询缓存,8.0删] → 分析器(词法+语法) → 优化器(选执行计划) → 执行器(调引擎接口) → 存储引擎(B+树取数) → 返回

3. What an UPDATE/INSERT/DELETE Does Differently

Write an undo log before modifying data (enables rollback and MVCC).

Write a redo log while changing the in‑memory page (WAL, crash‑safe).

After the statement finishes, write to the binlog (used for replication and recovery).

During transaction commit, perform a two‑phase commit: redo prepare → binlog → redo commit, ensuring redo and binlog stay consistent.

Thus a read‑only SELECT only fetches data, whereas a data‑modifying statement also generates a chain of logs to guarantee durability and consistency.

4. Storage Engine Comparison: InnoDB vs MyISAM

Transactions : InnoDB supports them; MyISAM does not.

Lock granularity : InnoDB uses row‑level locks (high concurrency); MyISAM uses table‑level locks (low concurrency).

Foreign keys : supported by InnoDB, not by MyISAM.

Crash recovery : InnoDB relies on redo logs; MyISAM has none.

Index structure : InnoDB uses clustered indexes (data stored with the primary key); MyISAM uses non‑clustered indexes (index leaf stores a pointer to the data file).

COUNT(*) : InnoDB must scan rows; MyISAM stores the row count and returns it in O(1) time.

MVCC : supported by InnoDB, not by MyISAM.

Typical use case : InnoDB for the vast majority of OLTP workloads (default); MyISAM only for read‑heavy, write‑light scenarios that do not need transactions.

InnoDB is the default because modern applications need transactions, high‑concurrency writes, and crash‑safe persistence—exactly the strengths of InnoDB. MyISAM’s only advantage is a fast COUNT(*) and simpler storage, useful only in pure‑read workloads.

5. Choosing Data Types When Creating Tables

Primary key : use BIGINT AUTO_INCREMENT; avoid UUIDs because they cause random inserts, page splits, and larger secondary indexes.

CHAR vs VARCHAR : CHAR(n) is fixed‑length and faster for truly fixed‑size data (e.g., MD5, phone numbers). VARCHAR(n) is variable‑length, saving space for columns with varying length but incurs a length byte.

Datetime vs Timestamp : TIMESTAMP occupies 4 bytes, stores timezone, and is valid until 2038; DATETIME occupies 8 bytes, no timezone, larger range. Use TIMESTAMP for timezone‑aware values before 2038, otherwise DATETIME.

Money values : always use DECIMAL; avoid FLOAT / DOUBLE because binary floating‑point loses precision (e.g., 0.1 + 0.2 ≠ 0.3).

NULL columns : avoid them when possible; they add a hidden flag and complicate indexing and aggregation. Prefer NOT NULL with a sensible default.

Table size : keep a single table under roughly 20 million rows; beyond that the B+‑tree may grow an extra level, adding an extra disk I/O per query. Consider sharding or archiving when this threshold is reached.

6. Full Series Map

一条 SQL 进来
  │
  ├─ 连接器鉴权 → 分析器解析 → 优化器选执行计划   ← 《EXPLAIN》《SQL 优化实战》
  │
  ├─ 执行器调 InnoDB 接口
  │     │
  │     ├─ 在 B+ 树里定位记录                 ← 《B+ 树索引》《InnoDB 存储结构》
  │     ├─ 页加载进 Buffer Pool               ← 《Buffer Pool》
  │     ├─ 加锁（当前读，防脏写）               ← 《锁》
  │     ├─ 写 undo（可回滚 + MVCC）            ← 《事务与 MVCC》
  │     ├─ 改页 + 写 redo（WAL，崩溃不丢）    ← 《事务与 MVCC》
  │     └─ 快照读靠 ReadView 找版本           ← 《事务与 MVCC》
  │
  └─ 提交：redo prepare → binlog → redo commit   ← 《binlog 与两阶段提交》
        （两阶段提交保证主从一致，binlog 供复制/恢复）

Together these nine articles answer a single overarching question: how does MySQL achieve fast, reliable, and concurrent data access on disk? The answer is threefold: speed comes from indexes, Buffer Pool, and cost‑based optimization; reliability from redo logs, undo logs, and two‑phase commit; concurrency from MVCC and row‑level locking.