Skip to content
MySQL Internals Deep Dive: Architecture, Source Code & Performance Tuning
Databases 42 min read

MySQL Internals Deep Dive: Architecture, Source Code & Performance Tuning

This comprehensive guide covers MySQL 8.0 architecture, InnoDB storage engine, B+ tree indexing, MVCC, transaction logs, replication, source code execution paths, and a four-stage learning roadmap with best practices for performance tuning and troubleshooting.

Architecture Pony
Architecture Pony
Architecture Pony

Introduction

This article provides a deep technical analysis of MySQL 8.0 (with 5.7 comparisons), covering the full journey from writing SQL to understanding internal execution and source code. It is structured in seven parts: positioning and version evolution, technical architecture, core SQL execution flows, key source code paths, a four-stage learning roadmap, best practices, and a one-page cheat sheet.

1. MySQL Positioning and Version Evolution

MySQL is an open-source RDBMS with a two-layer decoupled architecture: the Server layer handles connection, parsing, optimization, execution, and permissions; the storage engine layer handles data storage and retrieval via a handler interface. InnoDB is the default storage engine since 8.0, providing transactions, row-level locks, foreign keys, MVCC, and crash recovery.

Key Version Milestones

1995: MySQL 1.0 released by Michael Widenius.

2000: GPL open source, LAMP stack adoption.

2008/2010: Sun and Oracle acquisitions; MariaDB fork created.

2013/2015: 5.6 and 5.7 become long-term releases; 5.7 adds JSON, generated columns, multi-source replication, group commit improvements.

2018: MySQL 8.0 released with major kernel changes; 8.0.30+ redo log redesign; 8.4 LTS; 9.x innovation track.

Critical 5.7 vs 8.0 Differences

Data dictionary transactional: .frm files removed, metadata stored in InnoDB system tables, atomic DDL support.

Redo log redesign (8.0.30+): 32 files under #innodb_redo directory replace ib_logfile0/1, supporting online resizing and concurrent writes.

Default charset utf8mb4 (true emoji support), default authentication plugin caching_sha2_password.

SQL enhancements: window functions, CTEs, descending indexes, hash join, EXPLAIN ANALYZE, histogram statistics, functional indexes, invisible indexes, resource groups, roles.

Removed: query cache, \N syntax sugar; added SET PERSIST for persistent configuration in mysqld-auto.cnf.

2. Technical Architecture

Four-Layer View

Connection layer: TCP handshake, authentication plugin negotiation, TLS; one-thread-per-connection model.

Server layer: parser (lexical/syntax), preprocessor, cost-based optimizer, executor, plus binlog, replication, audit.

Storage engine layer: InnoDB (default), MyISAM, Memory, etc., communicating via handler interface.

File system layer: .ibd (per-table tablespace), ibdata1, redo log, undo tablespace, binlog, #innodb_temp.

Storage Engine Comparison

InnoDB: transactions, row locks, MVCC, clustered index (data = primary key leaf), crash recovery.

MyISAM: no transactions, table locks only, separate index/data, crash repair needed.

Memory: all data in RAM, hash index by default, lost on restart.

Transaction, locking, undo, redo are InnoDB engine features, not Server layer.

Indexing: B+ Tree Internals

B+ tree chosen over B-tree (non-leaf nodes store only keys, more keys per page, shallower tree), hash (no range scans, no ordering), LSM (read amplification, compaction overhead). InnoDB uses clustered index (primary key leaf stores full row) and secondary indexes (leaf stores index columns + primary key). Covering index avoids lookup.

Index Access Rules

Leftmost prefix: composite index (a,b,c) usable for a, (a,b), (a,b,c).

Range column stops ordering for subsequent columns, but Index Condition Pushdown (ICP) can still filter.

Column order (equality first, range last) determines whether filesort is avoided.

Page Structure (16KB)

File Header (38B), Page Header (56B), Infimum/Supremum, User Records (singly linked), Free Space, Page Directory (slots for binary search), File Trailer (8B). Page Directory enables binary search within page.

Transactions and Locks: ACID Implementation

Atomicity → undo log (reverse operations, also MVCC snapshot source).

Consistency → constraints + atomicity + isolation.

Isolation → locks + MVCC (write-write via locks, read-write via MVCC).

Durability → redo log (WAL: sequential write, fsync on commit).

Isolation Levels

Read Uncommitted: dirty reads.

Read Committed: each statement creates new ReadView; non-repeatable reads possible.

Repeatable Read (default): first read creates ReadView reused; next-key locks largely eliminate phantom reads.

Serializable: reads also lock, fully serial.

MVCC Mechanism

Hidden columns: DB_TRX_ID (last modifying transaction), DB_ROLL_PTR (pointer to undo log).

Version chain: each update writes old value to undo, forming a linked list.

ReadView: snapshot of active transaction IDs (m_ids), min/max active IDs; visibility check walks version chain.

RC vs RR difference: RR fixes ReadView at first read; RC recreates per statement.

Lock Types

Shared (S) / Exclusive (X) locks.

Row locks: record lock (single row), gap lock (range, prevent inserts), next-key lock (record + preceding gap, default in RR).

Table-level: intention locks (IS/IX), AUTO-INC lock, MDL (metadata lock, blocks DDL).

Deadlock detection via wait-for graph; victim rolled back (error 1213). Lock wait timeout (1205) more common.

Log System: Three Logs

redo log (engine, physio-logical): records page modifications, circular write, crash recovery, durability. innodb_flush_log_at_trx_commit=1 fsyncs each commit.

undo log (engine, logical): reverse operations, rollback + MVCC history. Long transactions prevent purge, cause tablespace bloat.

binlog (Server, logical): statement/row/mixed format (8.0 default row), replication and point-in-time recovery.

Two-phase commit (2PC) ensures atomicity between redo and binlog: ① redo prepare, ② write binlog, ③ redo commit. Crash recovery rules: redo commit → apply; redo prepare + binlog complete → commit; redo prepare + binlog missing → rollback. Group commit merges multiple fsyncs; 8.0 lock-free optimization. Doublewrite buffer prevents partial page writes (page fracture).

Replication and High Availability

Three threads: master dump thread, slave IO thread (writes relay log), slave SQL thread (replays, parallel since 5.7). Modes: asynchronous (default, possible data loss), semi-synchronous (at least one slave ACK), Group Replication (MGR/InnoDB Cluster, Paxos variant, auto failover). GTID simplifies failover. Tools: MHA, Orchestrator, MySQL Router for failover; ProxySQL, ShardingSphere, MyCat for read-write splitting and sharding. Master-slave lag causes stale reads; solutions: force primary reads, semi-sync/MGR to reduce lag window.

3. Core SQL Execution Flows

Connection Establishment

TCP three-way handshake (or Unix socket).

Server sends handshake packet: protocol version, server version, capability flags, auth plugin (caching_sha2_password default).

Client responds with username + digest; TLS/RSA public key encryption if needed.

Privilege check against mysql.user; allocate thread (one-thread-per-connection).

Session context caches current DB, charset, timezone, sql_mode, autocommit; each execution rechecks privileges.

Connection failures often due to auth plugin/charset mismatch; max_connections limit due to per-thread model.

SELECT Execution Pipeline

Connector: authenticate, get thread.

Query Cache (5.7 only): text-based key, removed in 8.0 due to high invalidation cost.

Parser: lexical + syntax analysis → parse tree; syntax errors (1064).

Preprocessor: check table/column existence, expand views, resolve aliases.

Optimizer: cost-based plan selection (index choice, join order, ICP, index merge, MRR, hash join).

Executor: check table privileges, loop calling storage engine handler interface (ha_index_read_map / ha_rnd_next).

InnoDB Engine: buffer pool lookup, index navigation, MVCC visibility check or locking read, possible lookup.

Return results: row-by-row, disk read if not cached (read-ahead), sent via protocol.

Performance issues 90% from optimizer plan mistakes and excessive engine row scans.

UPDATE Execution Pipeline

Connector → Parser → Optimizer (same as SELECT), executor calls engine.

InnoDB locates target page in buffer pool; if absent, reads from disk.

Acquire exclusive lock (record/next-key), write undo log with old value.

Modify in-memory page (becomes dirty page), write redo log as prepare.

Server layer writes binlog at commit.

Redo log marked commit, transaction returns success.

Dirty pages flushed asynchronously by page cleaner at checkpoint.

WAL decouples commit return from disk flush: random writes turned sequential via redo. Trade-off: crash recovery time and dirty page accumulation controlled by checkpoint.

Buffer Pool and LRU

Buffer Pool: page-level cache for tables/indexes; 8.0 supports online resize. Improved LRU: new pages inserted at midpoint (old sublist ~5/8) to prevent full scans evicting hot data.

Change Buffer: buffers writes to non-unique secondary indexes when page not in memory; merged on read. Unique indexes require page read for conflict check, so not eligible.

Adaptive Hash Index: auto-builds hash for frequent equality lookups, reducing B+ tree traversal.

Log Buffer: redo staged in memory, flushed per innodb_flush_log_at_trx_commit; group commit merges fsyncs.

Target buffer pool hit rate >99%; size typically 60-70% of physical memory.

Index Lookup Page-Level Walkthrough

Example: SELECT * FROM t WHERE id = 100.

Locate root page (fixed location in data dictionary), usually memory-resident.

Page binary search via Page Directory slots, then linear traverse singly linked list.

Descend to next level via page number, repeat; tree height 2-4 levels.

At leaf page, locate record; if secondary index, extract primary key and lookup clustered index (lookup).

Range scan follows leaf page doubly linked list for natural ordering.

Page splits occur when insert doesn't fit; random primary keys (UUID) cause frequent splits and low fill factor (<50%). Auto-increment primary key appends to last page, minimizing splits.

MVCC Visibility Check Algorithm

ReadView = { m_ids, min_trx_id, max_trx_id, creator_trx_id }
Traverse version chain from newest:
1) trx_id == creator_trx_id → visible (own change)
2) trx_id < min_trx_id → visible (committed before snapshot)
3) trx_id >= max_trx_id → not visible (started after snapshot)
4) trx_id in m_ids → not visible (active at snapshot)
If not visible, follow DB_ROLL_PTR to previous version, repeat.

Snapshot read (plain SELECT) uses MVCC, no locks. Current read (SELECT ... FOR UPDATE, LOCK IN SHARE MODE, UPDATE/DELETE/INSERT) reads latest version and acquires locks.

Crash Recovery Process

Redo roll forward: from last checkpoint, scan redo, reapply page modifications.

Undo roll back: find uncommitted transactions (redo prepare without binlog), undo via undo log.

Binlog alignment: Server checks binlog completeness, decides commit/rollback for prepare transactions (2PC completion).

Redo ensures no loss, undo ensures no leftovers, binlog ensures replica consistency. Backup must include binlog (xtrabackup --slave-info) for recoverable state.

4. Core Source Code Analysis

Reading Strategy

MySQL is C/C++, ~10M lines. Strategy: follow a single SQL call stack. Key directories: mysql-server/sql/ (Server layer), storage/innobase/ (InnoDB). Read sql/ skeleton first, then dive into InnoDB implementation.

Server Layer Execution Backbone

do_command()
└─ dispatch_command()
   ├─ mysql_parse() → parse_sql() → parse tree
   └─ mysql_execute_command()
      ├─ check_access()
      └─ Sql_cmd_dml::execute()
         ├─ prepare() – semantic checks, MDL
         ├─ Query_expression::optimize()
         │  └─ JOIN::optimize() – cost optimization
         └─ JOIN::exec()
            └─ sub_select() → handler::ha_index_read_map()

Key functions: mysql_execute_command() (entry, privilege, transaction control), JOIN::optimize() (plan generation), handler::ha_index_read_map() (Server-engine boundary, InnoDB implements ha_innobase::index_read()).

InnoDB Read Path: row_search_mvcc()

Core function for consistent read and locking. Call chain:

ha_innobase::index_read()
└─ row_search_for_mysql()
   └─ row_search_mvcc() [core]
      ├─ btr_pcur_open_with_no_init()
      │  └─ btr_cur_search_to_nth_level()
      │     └─ page_cur_search_with_match() – page binary search
      ├─ lock_clust_rec_read_check_and_lock() – locking decision
      ├─ lock_sec_rec_read_check_and_lock() – secondary index locking
      ├─ row_sel_build_prev_vers_for_mysql()
      │  └─ row_vers_build_for_consistent_read()
      └─ visibility check changes_visible()

Lock decision branches on isolation level, unique vs non-unique index, equality hit – explains why RC omits gap locks, RR uses next-key locks, unique index equality can degrade to record lock.

Transaction Commit and MVCC

trx_commit() / trx_commit_for_mysql()
├─ trx_commit_in_memory() – release locks, ReadView, write transaction state
└─ log flush (via prior mtr_commit)

MVCC snapshot:
trx_assign_read_view() → MVCC::view_open()
ReadView stored in trx->read_view (m_ids, low_limit_no, etc.)

Undo write:
trx_undo_report_row_operation()
└─ trx_undo_page_report_modify() – record old value and roll pointer

ReadView created lazily at first snapshot read, not at BEGIN. Early transaction start with late read sees snapshot at read time.

Redo and Binlog Write Paths

Redo (8.0.30+):
mtr_commit()
└─ log_write_up_to() – write log buffer, optional flush
   └─ log_files_write_buffer() – flush to #innodb_redo

Binlog + Group Commit:
binlog_cache_data::write_event() – session cache
MYSQL_BIN_LOG::commit()
└─ ordered_commit() – three phases: FLUSH → SYNC → COMMIT
   (serialized by LOCK_log / LOCK_sync / LOCK_commit)

Group commit batches multiple transactions' binlog fsyncs into one. sync_binlog=1 with group commit does not kill throughput.

Key File/Class Index

sql/sql_parse.cc: dispatch_command, mysql_parse, mysql_execute_command

sql/sql_optimizer.cc: Optimize_table_order, Cost_model_table, index dive

storage/innobase/row/row0sel.cc: row_search_mvcc (must-read), row_vers_build_for_consistent_read

storage/innobase/btr/btr0cur.cc: btr_cur_search_to_nth_level, btr_cur_optimistic_insert, btr_page_split_and_insert

storage/innobase/lock/lock0lock.cc: lock_rec_lock, lock_rec_other_has_conflicting, DeadlockChecker

storage/innobase/buf/buf0buf.cc: buf_page_get_gen, buf_LRU_get_free_block

sql/binlog.cc: MYSQL_BIN_LOG::commit, ordered_commit

Practical tip: combine source reading with EXPLAIN ANALYZE (actual execution times/rows) and performance_schema (wait events, lock waits). Observe phenomenon first, then locate logic in source.

5. Learning Roadmap

Four Stages

Stage 1 – Write SQL (1-2 weeks): master SELECT execution order, joins, subqueries, functions. Graduation: write 5-table join and explain each step.

Stage 2 – Understand Internals (3-6 weeks): InnoDB four mechanisms (B+ tree, transactions/locks, MVCC, logs). Graduation: draw B+ tree search path, explain RC vs RR difference and reason, explain 2PC necessity.

Stage 3 – Performance Tuning (1-2 months): EXPLAIN/ANALYZE, slow log, performance_schema, SHOW ENGINE INNODB STATUS; index design, SQL rewrite, parameter tuning, lock analysis, large DDL. Graduation: given slow SQL, diagnose and propose verifiable optimization with expected effect.

Stage 4 – Source Code / Architecture (3+ months): read sql/ skeleton + InnoDB core functions; master replication, MGR, sharding, backup/recovery, capacity planning. Graduation: design read-write separation + HA + backup solution, explain each component's problem solved.

Source Code Reading Sequence (10 Steps)

dispatch_command – command entry

mysql_parse – parse tree construction

Sql_cmd_dml::execute – prepare/optimize/exec

JOIN::optimize – plan selection

ha_index_read_map – Server-engine interface

row_search_mvcc – consistent read & locking (critical)

btr_cur_search_to_nth_level – B+ tree navigation

trx_undo_report_row_operation – undo & version chain

trx_commit / log_write_up_to – commit & redo flush

MYSQL_BIN_LOG::ordered_commit – group commit

Methodology: (1) use gdb/logs to trace real query call stack; (2) understand data structures first (trx_t, dict_table_t, btr_pcur_t); (3) build minimal repro with concurrent sessions to observe lock behavior and match source.

High-Frequency Interview Questions (12)

Why B+ tree over B-tree/hash/red-black?

Clustered vs secondary index differences? Lookup vs covering index?

Why auto-increment PK? UUID problems?

Composite index leftmost prefix and ICP?

What is MVCC? RC vs RR code-level difference?

Record/gap/next-key locks: what problems solved? Does RR truly eliminate phantoms?

Why redo log? How responsibilities split with binlog?

2PC process and crash recovery criteria?

What is doublewrite? Consequences if disabled?

Change buffer purpose and limitations? Why only non-unique secondary indexes?

What is group commit? Interaction with sync_binlog and innodb_flush_log_at_trx_commit?

Master-slave lag causes? Root fixes and workarounds?

6. Best Practices and Common Pitfalls

Reading EXPLAIN

Focus on four columns:

type: system > const > eq_ref > ref > range > index > ALL. ALL = full table scan; index = full index scan.

key: actual index used; NULL means no index. Discrepancy with possible_keys suggests optimizer thinks full scan cheaper or stats stale.

rows: estimated rows scanned; multiplied by type cost factor = cost estimate. Rows >> returned rows indicates low filter efficiency.

Extra: Using filesort (extra sort), Using temporary (temp table for GROUP BY/DISTINCT), Using index (covering index, good), Using index condition (ICP active).

Common Index Failure Scenarios

Function/operation on indexed column: WHERE DATE(created_at) = '...' → rewrite as range.

Implicit type conversion: string column compared to number triggers conversion = function on column.

Leading wildcard LIKE: '%abc' cannot use index; 'abc%' can.

Range column kills subsequent column ordering (ICP may still filter).

OR with non-indexed column: a=1 OR b=2 (b no index) → full scan; consider UNION.

Optimizer chooses full scan when estimated selectivity high (>20-30%) – rational choice, not bug.

Three Frequent Production Issues

Deep pagination LIMIT 100000,20: scans and discards 100k rows. Solutions: (a) deferred join – covering index to fetch PKs then lookup; (b) cursor pagination – WHERE id > last_id ORDER BY id LIMIT 20 (constant time).

Large table ALTER: 8.0 instant ADD COLUMN, Online DDL (ALGORITHM=INPLACE, LOCK=NONE). Safer: gh-ost or pt-online-schema-change – shadow table, batch copy, trigger/binlog sync, atomic cutover, zero lock.

Long transactions: cause undo bloat (purge blocked), long lock holding (waits/deadlocks), MDL blocking DDL. Mitigation: split large transactions, commit promptly, avoid RPC/bulk loops inside transaction.

Key Parameter Cheat Sheet

innodb_buffer_pool_size: most critical, 60-70% RAM, target hit rate >99%.

innodb_flush_log_at_trx_commit: 1 = fsync per commit (safest, financial); 2 = per second; 0 = OS decides. Lower improves throughput, risks seconds of data loss.

sync_binlog: 1 = fsync per commit (with above forms "dual 1"); group commit makes performance acceptable.

innodb_io_capacity / max_connections / transaction_isolation: set per disk capability; connection pool sizing not just max_connections; isolation level per business (RC/RR). 8.0 SET PERSIST persists changes.

Error Code Quick Reference

1040 Too many connections: check pool limits, leaked connections, slow query pileup.

1205 Lock wait timeout: use information_schema.innodb_trx + performance_schema.data_locks to find holder; usually long transaction or uncommitted session.

1213 Deadlock found: InnoDB auto-rolls back victim. Fix: unify lock order, shorten transactions, add indexes to reduce lock scope.

1064 syntax error / 1054 unknown column / 1062 duplicate key: parse failure, column/alias typo, PK/unique violation (use idempotent writes or INSERT ... ON DUPLICATE KEY UPDATE).

1114 table full / 2006 server gone away: tablespace/temp space full; latter often max_allowed_packet exceeded or wait_timeout disconnect.

7. One-Page Cheat Sheet

[Layers] Connection → Server → Engine → File
[Engine] InnoDB default: transactions / row locks / FK / MVCC
[Index] B+ tree; clustered leaf = full row, secondary leaf = PK
[Lookup] Secondary → PK → clustered; covering index avoids
[Leftmost] Composite index sequential match; range kills later ordering
[Tx] A=undo  C=constraints  I=locks+MVCC  D=redo
[Isolation] RR default, first read fixes ReadView; RC rebuilds per statement
[Locks] Record / Gap / Next-key; Intention locks; MDL
[Logs] redo no-loss, undo no-leftover, binlog replica consistency
[2PC] redo prepare → binlog → redo commit
[Memory] Buffer Pool (midpoint insert) + change buffer
[Tuning] Watch type/key/rows/Extra; EXPLAIN first

Core insight: MySQL performance stems from "memory for I/O, logs for random writes, MVCC for read-write concurrency"; every SQL you write negotiates with this system.

Original Source

Signed-in readers can open the original source through BestHub's protected redirect.

Sign in to view source
Republication Notice

This article has been distilled and summarized from source material, then republished for learning and reference. If you believe it infringes your rights, please contactadmin@besthub.devand we will review it promptly.

Performance TuningInnoDBMySQLreplicationtransactionsB+ treeMVCCsource code
Reader feedback

How this landed with the community

Sign in to like

Rate this article

Was this worth your time?

Sign in to rate
Discussion

0 Comments

Thoughtful readers leave field notes, pushback, and hard-won operational detail here.