Master MySQL Internals: Query Cache, Indexes, Optimizer & Transaction Logs

Query Cache

MySQL can cache the result of a SELECT statement. Use SHOW VARIABLES LIKE '%query_cache%' to view parameters such as have_query_cache (whether the feature is supported), query_cache_limit (maximum result size that can be cached, default 1 MB), query_cache_min_res_unit (minimum allocation unit, default 4 KB), query_cache_size (total memory for the cache), query_cache_type (ON/OFF, default ON), and query_cache_wlock_invalidate (whether a locked table returns cached data). The cache stores the SQL hash and its result set; if any row in a table changes, the cache for that table is invalidated, which makes the feature unsuitable for frequently‑updated tables.

Indexes

MySQL uses B+‑tree indexes. A clustered index (the primary key) stores full rows in leaf nodes and orders pages by the primary‑key value. A non‑clustered (secondary) index stores the indexed column(s) plus the primary‑key value in leaf nodes, allowing fast lookup without storing the whole row. A composite index follows the left‑most prefix rule: only the leftmost columns can be used for index lookups.

Best practices include using unique indexes where possible, avoiding over‑indexing, not indexing columns that are frequently used in functions, and preferring an auto‑increment integer primary key over a UUID to reduce page splits.

CREATE TABLE `user` (
  `id`   VARCHAR(10) NOT NULL DEFAULT '',
  `name` VARCHAR(10) DEFAULT NULL,
  `age`  INT(11) DEFAULT NULL,
  `sex`  VARCHAR(10) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `idx_name` (`name`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

INSERT INTO `user` VALUES
('1','andy','20','女'),
('2','kat','12','女'),
('3','lili','20','男');

When a query searches by name, MySQL first uses the secondary index to locate matching primary‑key values, then fetches the full rows from the clustered index (the “back‑track” step).

Optimizer

The optimizer converts the parse tree into an execution plan and chooses the plan with the lowest estimated cost. Cost is a sum of I/O cost (default 1 per page read) and CPU cost (default 0.2 per row operation). Example: for table dev_user with 158 9248 bytes data length, page size 16 KB, the estimated I/O cost for a full scan is 97, CPU cost 1228, total 1325. Using a unique index on username yields I/O 2 + CPU 0.4 = 2.4, so the optimizer selects the index.

EXPLAIN Output

Key fields:

id : identifier of the SELECT (subqueries share the same id after being rewritten as joins).

select_type : SIMPLE, PRIMARY, UNION, etc.

table : the table referenced in this row.

type : access method (ALL, index, range, ref, const, etc.).

possible_keys : indexes that could be used.

key : the index actually chosen.

rows : estimated rows examined.

filtered : estimated percentage of rows that satisfy the condition.

Redo Log (Physical Log)

InnoDB writes modifications to a log buffer in memory first. The redo log records the changes in a durable form so that, after a crash, committed transactions can be replayed. The buffer is flushed to disk when it is half full, when a transaction commits, by a background thread, or during a checkpoint.

Redo log files are ib_logfile0 and ib_logfile1 in the data directory. They are written in a circular fashion; a checkpoint determines up to which LSN (Log Sequence Number) the dirty pages have been persisted, allowing older log segments to be overwritten.

Undo Log (Logical Log)

Undo logs support rollback and MVCC. For each change, an opposite operation is recorded: an INSERT generates a DELETE entry, an UPDATE generates an inverse UPDATE, and a DELETE generates an INSERT. During a rollback, the undo log is applied to restore the previous state.

INSERT INTO a(id) VALUES(1);   -- redo
DELETE FROM a WHERE id=1;   -- undo

Undo entries are linked into a version chain for each row; a read view selects the appropriate version based on the transaction’s isolation level.

Transaction Isolation

MySQL supports four isolation levels:

READ UNCOMMITTED : allows dirty reads, non‑repeatable reads, and phantom reads.

READ COMMITTED : prevents dirty reads but allows non‑repeatable and phantom reads.

REPEATABLE READ (default): prevents dirty and non‑repeatable reads; phantom reads are mitigated by next‑key locking.

SERIALIZABLE : eliminates all three phenomena by acquiring locks on the full range of rows.

Phenomena such as dirty writes, dirty reads, non‑repeatable reads, and phantom reads are illustrated with session examples.