MySQL Architecture
IntermediateMySQL's architecture layers: connection handling (thread pool), parser/optimiser, storage engine API, and pluggable engines (InnoDB, MyISAM) — understanding each layer aids query tuning.
Overview
MySQL is structured as a layered architecture. The top layer handles client connections and authentication. The server layer contains the SQL parser, query optimiser, and query cache. The storage engine API is a pluggable interface that decouples the server layer from physical data storage — InnoDB is the default and only ACID-compliant engine for production use. Understanding this layering helps diagnose whether a performance problem is in query parsing, optimisation, or I/O. InnoDB features: MVCC, row-level locking, ACID transactions, foreign keys, and the buffer pool (in-memory page cache).
Architecture Layers
Three main layers: (1) Connection layer — thread pool, authentication, SSL; (2) Server layer — parser, query optimiser, query rewriter, execution engine; (3) Storage engine layer — InnoDB, MyISAM, MEMORY, etc. Each layer can be a performance bottleneck.
# Architecture overview:
#
# ┌─────────────────────────────────────────────────┐
# │ Client (JDBC, mysql CLI, Workbench) │
# └────────────────┬────────────────────────────────┘
# │ TCP / Unix socket
# ┌────────────────▼────────────────────────────────┐
# │ Connection Layer │
# │ • Thread pool / one thread per connection │
# │ • Authentication (caching_sha2_password) │
# │ • SSL/TLS termination │
# └────────────────┬────────────────────────────────┘
# ┌────────────────▼────────────────────────────────┐
# │ Server Layer │
# │ • SQL Parser (validates syntax) │
# │ • Query Rewriter (transforms e.g. views) │
# │ • Query Optimiser (chooses indexes, join order) │
# │ • Execution Engine (iterates rows) │
# └────────────────┬────────────────────────────────┘
# │ Storage Engine API (handler interface)
# ┌────────────────▼────────────────────────────────┐
# │ Storage Engines │
# │ InnoDB (default) │ MyISAM │ MEMORY │ CSV │ ... │
# └─────────────────────────────────────────────────┘
SHOW ENGINES; -- list available storage engines
SHOW ENGINE INNODB STATUSG -- detailed InnoDB runtime infoInnoDB Internals
InnoDB organises data in 16KB pages stored in the buffer pool (in-memory cache). Dirty pages are flushed to the tablespace files by the cleaner thread. The redo log (iblog) ensures durability; the undo log enables MVCC and rollback.
# InnoDB key components:
# Buffer Pool — in-memory cache of data + index pages
# innodb_buffer_pool_size = 70-80% of available RAM (most important tuning knob)
# Redo Log — write-ahead log for crash recovery (ibdata1 or redo log files)
# innodb_log_file_size — larger = better write throughput, longer recovery
# Undo Log — stores old row versions for MVCC read consistency and ROLLBACK
# Change Buffer — caches secondary index changes for non-present pages
# Doublewrite Buffer — prevents torn pages on crash (write page twice)
# Check buffer pool hit ratio (should be > 99%)
SELECT
FORMAT((1 - Innodb_buffer_pool_reads / Innodb_buffer_pool_read_requests) * 100, 2)
AS buffer_pool_hit_pct
FROM information_schema.GLOBAL_STATUS
WHERE Variable_name IN ('Innodb_buffer_pool_reads', 'Innodb_buffer_pool_read_requests');
# Alternatively, single query:
SHOW STATUS LIKE 'Innodb_buffer_pool%';Query Execution Flow
Understanding the query path from client to storage engine helps pinpoint where time is spent. Use EXPLAIN to see the optimiser's plan and SHOW PROFILE (or Performance Schema) for timing per stage.
-- Query execution path:
-- 1. Client sends SQL text over TCP
-- 2. Parser validates syntax → parse tree
-- 3. Query rewriter applies rewrites (view expansion, subquery transformation)
-- 4. Optimiser estimates costs → picks indexes, join order, join algorithm
-- 5. Execution engine opens tables via handler API
-- 6. Storage engine reads/writes pages from/to buffer pool
-- 7. Result rows streamed back to client
-- EXPLAIN — see optimiser choices
EXPLAIN SELECT o.id, c.email
FROM orders o
JOIN customers c ON o.customer_id = c.id
WHERE o.status = 'PENDING';
-- Key columns: type (ALL=full scan, ref=index), key (index used), rows (estimate)
-- EXPLAIN ANALYZE (MySQL 8.0.18+) — execute and show actual timings
EXPLAIN ANALYZE SELECT * FROM orders WHERE status = 'PENDING'GKey Points to Remember
- 1MySQL has three layers: connection, server (parser + optimiser), and pluggable storage engines.
- 2InnoDB is the only production-grade engine: ACID, row-level locks, MVCC, foreign keys.
- 3The buffer pool is InnoDB's in-memory page cache — size it to 70-80% of available RAM.
- 4Redo log provides crash durability (write-ahead log); undo log enables MVCC + rollback.
- 5The query optimiser selects indexes and join order — use EXPLAIN to inspect its decisions.
- 6EXPLAIN ANALYZE (8.0.18+) executes the query and shows actual vs estimated row counts.
Interview Questions
Sign in to ask AriaWhat are the main layers of MySQL's architecture?
What is the InnoDB buffer pool and why is its size so important?
What is the difference between the redo log and the undo log in InnoDB?
How does MySQL's pluggable storage engine architecture differ from PostgreSQL?
What does the type column in EXPLAIN output tell you about a query?
Ask Aria about MySQL Architecture
Your personal AI tutor — ask anything about this concept
Revision Status
Personal Notes
Sign in to save personal notes for this topic.
Discussion
Sign in to join the discussion.