Skip to content
MySQL Interview Panorama: Architecture, Indexes, Transactions, Locks & Sharding Guide
Databases 76 min read

MySQL Interview Panorama: Architecture, Indexes, Transactions, Locks & Sharding Guide

This comprehensive guide covers MySQL core internals for interview preparation: four-layer architecture, SQL execution flow, InnoDB vs MyISAM, B+Tree/Hash/Fulltext indexes, clustered vs non-clustered indexes, leftmost prefix principle, covering indexes, index condition pushdown, ACID with redo/undo logs, MVCC and ReadView, isolation levels, row/gap/next-key locks, two-phase commit, SQL optimization with EXPLAIN and slow query logs, sharding strategies with ShardingSphere, master-slave replication and GTID, and deep pagination solutions.

Rain's Path to Java Mastery
Rain's Path to Java Mastery
Rain's Path to Java Mastery

MySQL Architecture: Four-Layer Design

MySQL uses a classic four-layer architecture: Connection Layer → Service Layer → Engine Layer → Storage Layer. This decoupled, pluggable design is key to its popularity.

1. Connection Layer

Handles client connections, authentication, and permission checks. Each connection gets a dedicated thread. Connection pooling (e.g., Druid, HikariCP) reuses threads to avoid creation/destruction overhead. Key parameters: max_connections=151 (default), wait_timeout (8 hours), interactive_timeout.

2. Service Layer

The "brain": SQL interface, parser (generates AST), optimizer (cost-based plan selection), query cache (removed in 8.0 due to low hit rate), and built-in functions/procedures/triggers/views. The optimizer picks the plan it deems lowest cost, not necessarily optimal. Use EXPLAIN to inspect plans.

3. Engine Layer

Pluggable storage engines. Main comparison:

InnoDB (default) : ACID transactions, row-level locks, foreign keys, crash recovery, clustered index, Change Buffer, Doublewrite, MVCC + undo log for snapshot reads. General OLTP, high concurrency.

MyISAM : No transactions, table-level locks only, no foreign keys, no crash recovery, non-clustered index, fast COUNT(*) via stored row count. Read-heavy, temporary tables.

Memory : Row-level locks, no transactions, no foreign keys, data loss on crash. Temporary data, cache.

4. Storage Layer

Persists data and logs to disk. Key files:

/data/mysql/
├── ibdata1          # Shared tablespace (undo, doublewrite, system dict)
├── db_name/
│   ├── table.ibd    # Independent tablespace (data + index)
│   └── table.frm    # Table definition (removed in 8.0)
├── ib_logfile0      # Redo log
├── ib_logfile1
└── *.binlog         # Server-layer binary log

Redo log is circular fixed-size; binlog is append-only. Two-phase commit ensures redo/binlog consistency. Independent tablespace ( innodb_file_per_table=ON) prevents ibdata1 bloat. Redo log size affects write throughput and crash recovery time. Binlog ROW format is most reliable but heavier.

SQL Execution Flow

A SQL passes through: Connector (auth, permissions) → Analyzer (lexical/syntax, AST) → Optimizer (cost model, index selection, join order) → Executor (calls engine, row-by-row processing) → InnoDB Engine Core.

InnoDB Update Execution (Critical)

Find row: check Buffer Pool, else load from disk.

Lock: exclusive (X) lock on target rows.

Write undo log: record old version for rollback/MVCC.

Update in-memory page → becomes dirty page.

Write redo log buffer: record physical change for crash recovery.

Two-phase commit:

Prepare: flush redo log to disk, mark prepare.

Write binlog, flush to disk.

Commit: mark redo log commit.

Async flush dirty pages later by background threads.

Why two-phase commit? Guarantees redo/binlog consistency. On crash: if redo has commit, redo applies; if only prepare but binlog written, rollback redo — ensures master-replica consistency.

Log Summary

undo log : Rollback, MVCC. Flush timing: async after commit. Durable: No.

redo log : Crash recovery, durability. Flush timing: at commit (default). Durable: Yes.

binlog : Replication, recovery. Flush timing: at commit. Durable: Yes.

InnoDB vs MyISAM

InnoDB = "all-rounder" (transactions, row locks, clustered index, crash recovery, FK, MVCC). MyISAM = "niche sprinter" (table locks, non-clustered, no transactions, no crash recovery, fast COUNT(*) via stored row count). InnoDB is default since 5.5. Key interview points: InnoDB row locks rely on indexes (no index → table lock); clustered index stores full row, secondary index stores PK value (hence small PK recommended); COUNT(*) full scan in InnoDB vs instant in MyISAM; DELETE row-by-row vs TRUNCATE; deadlocks only in InnoDB.

Index Types

B+Tree Index

Multi-way balanced tree; only leaf nodes store data; leaves linked via doubly-linked list for range scans. Non-leaf nodes store keys + pointers → high fan-out, low height, stable O(log n) I/O. Supports range, sort, prefix fuzzy match. Default for most OLTP.

Hash Index

Hash table; O(1) equality lookups. No range/sort/prefix. Hash collisions possible. InnoDB cannot create explicitly; Adaptive Hash Index (AHI) auto-built on hot pages. Workaround: store CRC32 of long string, index that integer.

Fulltext Index

Inverted index (term → doc mapping). Supports natural/boolean modes. MySQL 5.6+ InnoDB supports it; Chinese needs ngram parser. For complex search, use Elasticsearch.

Clustered vs Non-Clustered Index

Clustered: leaf = full row; data physically ordered by index; one per table (PK = clustered, else first unique not null, else hidden 6-byte row_id). Non-clustered: leaf = PK value (InnoDB) or row pointer (MyISAM); multiple allowed. "Back-table" lookup: secondary index finds PK, then clustered index fetches full row.

Covering index avoids back-table: query columns ⊆ index columns. Example:

CREATE INDEX idx_name_age ON user(name, age); SELECT id, name, age FROM user WHERE name='张三';

→ Using index in EXPLAIN Extra.

Design principles: auto-increment integer PK (sequential inserts, no page splits); avoid long PK (bloats all secondary indexes); avoid frequent PK updates (causes page splits/fragmentation).

Composite Index & Leftmost Prefix

Composite index (a,b,c) sorts by a, then b, then c. Leftmost prefix: matching must start from leftmost column continuously; range query ( >, <, BETWEEN, LIKE) stops further matching. WHERE a=1 → Full hit (a) WHERE a=1 AND b=10 → Full hit (a,b) WHERE a=1 AND b=10 AND c=100 → Full hit (a,b,c) WHERE b=10 AND c=100 → None WHERE a=1 AND c=100 → Partial (a only, c cannot) WHERE a>1 AND b=10 → Partial (a only, range stops b) WHERE a=1 AND b>10 AND c=100 → Partial (a,b; range on b stops c)

Optimizer reorders WHERE clauses automatically. Common invalidation causes: violating leftmost prefix, functions/operations on indexed columns, implicit type conversion, leading % in LIKE, range column stops subsequent columns, OR with non-indexed column, != / NOT IN, IS NULL (sometimes), optimizer chooses full scan (>20-30% rows).

Covering Index & Index Condition Pushdown (ICP)

Covering index: all queried columns in index → zero back-table. SELECT id, name, age FROM user WHERE name='Jack'; with idx_name_age(name, age) → Using index. ICP (MySQL 5.6+): pushes filter to storage engine during index scan. For WHERE name LIKE '张%' AND age=20 on idx_name_age, engine filters age=20 before back-table → Using index condition. Comparison: covering avoids back-table entirely; ICP reduces back-table count. Both need composite index. Verify via EXPLAIN Extra.

Transactions: ACID & Implementation

Atomicity → undo log rollback. Durability → redo log crash recovery. Isolation → MVCC + locks. Consistency → outcome of A+I+D + constraints.

Undo Log (Atomicity + MVCC Foundation)

Records old version before modification (logical log: reverse operations). On rollback, restores old values. Also forms version chain via DB_ROLL_PTR for MVCC.

Redo Log (Durability + WAL)

Physical log: "page X offset Y changed to Z". Write-Ahead Logging: write redo first, modify Buffer Pool later. Circular fixed-size (default 48MB). Commit forces redo flush ( innodb_flush_log_at_trx_commit=1). Crash recovery replays committed redo records.

MVCC (Isolation)

Three hidden columns: DB_TRX_ID (last modifier TX ID), DB_ROLL_PTR (pointer to undo log version chain), DB_ROW_ID (hidden PK). ReadView snapshot generated at first SELECT (RR) or each SELECT (RC). Visibility rules: version TX ID == creator → visible; < min_active_id → visible; ≥ max_trx_id → invisible; in active list → invisible, traverse version chain via DB_ROLL_PTR.

RR: first SELECT creates ReadView, reused → repeatable read. RC: new ReadView per SELECT → sees latest committed. MVCC only for snapshot reads (plain SELECT); current reads ( FOR UPDATE, DML) use locks. Next-key lock (row + gap) solves phantom reads for current reads in RR.

Isolation Levels

Read Uncommitted : Dirty read ✅, Non-repeatable read ✅, Phantom read ✅, Performance highest.

Read Committed : Dirty read ❌, Non-repeatable read ✅, Phantom read ✅, Performance high.

Repeatable Read (default) : Dirty read ❌, Non-repeatable read ❌, Phantom read ❌ (MySQL solves via MVCC+gap lock), Performance medium.

Serializable : Dirty read ❌, Non-repeatable read ❌, Phantom read ❌, Performance lowest.

MySQL RR uses MVCC for snapshot reads (no phantom) and next-key locks for current reads (prevents inserts in gaps). Oracle defaults to RC.

Lock Mechanism

Granularity: row lock < gap lock < next-key lock < table lock. Next-key lock = row lock + gap lock (left-open, right-closed interval). Default in RR.

Table lock: MyISAM default; InnoDB for DDL or full-scan UPDATE/DELETE.

Row lock: index-based; no index → table lock.

Gap lock: locks gaps between index records; blocks inserts only; RR only.

Next-key lock: locks (prev, current]; solves phantom reads.

Example: IDs 10,20,30. SELECT ... WHERE id=25 FOR UPDATE → next-key lock (20,30] blocks inserts 21-29. WHERE id=20 FOR UPDATE (unique index, exists) → degrades to row lock on 20 only.

Avoid long transactions (hold gap locks), use SHOW ENGINE INNODB STATUS for deadlock analysis, consider RC to disable gap locks if phantom reads acceptable.

SQL Optimization: Slow Query Log & EXPLAIN

Process: locate via slow query log → diagnose via EXPLAIN → optimize.

Slow Query Log

SET GLOBAL slow_query_log = ON;
SET GLOBAL long_query_time = 1;
SET GLOBAL log_queries_not_using_indexes = ON;

Analyze with mysqldumpslow or pt-query-digest.

EXPLAIN Key Fields

type :

system > const > eq_ref > ref > range > index > ALL

. Must reach range +, ideally ref / eq_ref.

key : Actual index used; NULL = none. Check index existence/trigger.

rows : Estimated scanned rows. Minimize via better index.

Extra : Using index (covering), Using filesort, Using temporary, Using where. Eliminate filesort/temporary first.

id/select_type : Execution order, subquery types. Small table drives large; convert subqueries to JOIN.

Common fixes: avoid SELECT * (use covering index), no functions on indexed columns, avoid != / NOT IN / LIKE '%...', optimize pagination with WHERE id > last_id LIMIT N, small table drives large in JOINs, UNION ALL over OR, EXISTS over IN for large tables.

Sharding: Vertical vs Horizontal & ShardingSphere

Trigger: single table >10M rows or >1M daily inserts → query slowdown, write contention, storage limits, connection exhaustion.

Vertical Split

By business module/columns. Different table structures. Solves coupling, wide tables. Low complexity; cross-table transactions need distributed TX (TCC/Saga).

Horizontal Split

By rows (sharding key). Identical structures. Solves data volume, write pressure. High complexity: sharding algorithm, routing, global unique ID, cross-shard JOIN/transactions.

Strategy: vertical first, then horizontal. Sharding key must appear in most queries to avoid fan-out.

ShardingSphere

Apache top-level project: data sharding (split + read-write split), distributed transactions (Seata AT/TCC/Saga), data governance (encryption, shadow DB, SQL audit). Two modes: ShardingSphere-JDBC (embedded jar, high perf, Java-only) and ShardingSphere-Proxy (standalone proxy, multi-language, slight overhead). Core concepts: logical table, actual tables, sharding key, sharding algorithm (mod, range, hash, custom), sharding strategy (key + algorithm). Solves routing, result merge, distributed TX, read-write split.

Pitfalls: wrong sharding key, ignored cross-shard queries, neglected distributed TX, no migration plan, no resharding strategy. Bonus: snowflake ID for global PK, broadcast tables (dict), binding tables (co-sharded for JOIN), forced routing, DistSQL in 5.x.

Master-Slave Replication & Lag

Async replication via binlog. Three threads: Master Binlog Dump → Slave I/O (writes relay log) → Slave SQL (replays). Binlog formats: STATEMENT (SQL text, unsafe), ROW (row changes, safe, default), MIXED.

Lag Causes

High master write throughput > slave SQL replay speed.

Large transactions (slave waits for commit then replays serially).

Slave hardware inferior.

Cross-datacenter network latency.

Slave overloaded by heavy analytical queries.

Solutions

Upgrade to 5.7+; enable parallel replication: slave_parallel_type=LOGICAL_CLOCK; slave_parallel_workers=4.

Read-write separation: dedicate slaves for reports.

Sharding to reduce single-table pressure.

Multi-slave to distribute read load.

Split large transactions into small batches.

Offload complex queries from slave.

Schedule bulk writes off-peak.

SSD, more RAM, same datacenter, dedicated network.

Semi-sync replication: master waits for at least one slave to acknowledge binlog receipt (I/O thread) before commit — prevents data loss, not replay lag.

GTID: CHANGE MASTER TO MASTER_AUTO_POSITION=1 simplifies failover.

Monitor with pt-heartbeat (accurate), not just Seconds_Behind_Master.

Deep Pagination Solutions

Problem: LIMIT 1000000, 10 scans and discards 1M rows → massive I/O/CPU.

Cursor/Keyset Pagination (best for infinite scroll): WHERE id > last_id ORDER BY id LIMIT 10. O(1), stable, no jump-to-page.

Subquery Optimization :

SELECT * FROM t WHERE id >= (SELECT id FROM t ORDER BY id LIMIT 1000000,1) ORDER BY id LIMIT 10

. Reduces back-table via covering index on PK; still scans offset IDs but lighter.

Covering Index + Delayed Join :

SELECT a.* FROM t a INNER JOIN (SELECT id FROM t ORDER BY id LIMIT 1000000,10) b ON a.id=b.id

. Better than subquery; supports jump-page.

Business Restriction : limit UI to first N pages; add filters/default date ranges. 80% of deep pagination is fake demand.

Search Engine (ES) : use search_after (cursor) for unlimited scroll; from+size capped at 10k.

Choose by scenario: cursor for mobile feeds, subquery/delayed join for moderate offset with jump-page, business restriction as first line, ES for search-heavy workloads.

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.

OptimizationTransactionShardingInnoDBMySQLReplicationPaginationIndexLockMVCC
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.