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.
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 pointerReadView 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 firstCore 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.
Signed-in readers can open the original source through BestHub's protected redirect.
This article has been distilled and summarized from source material, then republished for learning and reference. If you believe it infringes your rights, please contactand we will review it promptly.
How this landed with the community
Was this worth your time?
0 Comments
Thoughtful readers leave field notes, pushback, and hard-won operational detail here.
