Server Layers and Storage Engines

MySQL is split into three logical layers:

1. Connection & Authentication – handles client hand-shake, privilege checks, SSL, thread pooling.
2. SQL Layer – parses, optimizes, rewrites and executes statements. All built-in functions, views, triggers, stored procedures live here. The query cache was removed in 8.0.
3. Storage Engine Layer – pluggable engines (InnoDB, MyISAM, RocksDB, …) that store and retrieve rows. Engines do not parse SQL; they only respond to low-level requests such as "fetch row with PK = 42".

Locking Granularity

InnoDB defaults to row-level locking and supports:

• Record Lock – locks the exact index record.
• Gap Lock – locks the gap between index records to prevent phantom inserts.
• Next-Key Lock – record + gap; used for range scans under REPEATABLE READ.

Transaction & Logging

ACID is enforced via:

• Redo Log (InnoDB) – physical log that guarantees durability after crash.
• Undo Log (InnoDB) – stores before-images for rollback and MVCC snapshots.
• Binary Log (MySQL Server) – logical log for replication and point-in-time recovery.

MVCC under REPEATABLE READ

Each row has two hidden columns: DB_TRX_ID (creation txn) and DB_ROLL_PTR (pointer to undo). A snapshot is created at the first consistent read; latter reads use the same snapshot, eliminating read locks for repeatable read isolation.

B+Tree Indexes

InnoDB uses a clustered B+Tree for the primary key. Secondary indexes store the primary-key value as the logical pointer.

• All data lives in the leaf nodes of the clustered index.
• Secondary index leaves contain (index-columns, PK) pairs; accessing the row requires a second lookup ("double read").
• Covering indexes avoid the second lookup by including all requested columns in the secondary index.

Index Design Tips

• Favor monotonically increasing keys (e.g., BIGINT AUTO_INCREMENT) to reduce page splits.
• Avoid random UUIDs as primary key; inserts become scattered and cause fragmentation.
• Composite indexes obey left-prefix matching; put the most sleective column first.
• Cardinality shown by SHOW INDEX helps the optimizer choose the correct index.

Query Life Cycle

1. Client connects via TCP / Unix socket.
2. Parser checks syntax and builds a parse tree.
3. Preprocessor resolves names, checks privileges.
4. Optimizer chooses join order, indexes, access methods.
5. Executor opens tables, applies WHERE, GROUP BY, HAVING, ORDER BY, LIMIT.
6. Rows are returned to the cliant.

Execution Order in a Single SELECT

FROM & JOIN  →  WHERE  →  GROUP BY  →  HAVING  →  SELECT  →  ORDER BY  →  LIMIT

Join Types

INNER JOIN       -- intersection
LEFT JOIN        -- left + matched right
RIGHT JOIN       -- right + matched left
FULL OUTER JOIN  -- MySQL emulates with UNION of LEFT & RIGHT

Typical Slow-Query Fixes

1. Check EXPLAIN FORMAT=JSON for type=ALL (full scan).
2. Ensure predicates are sargable (no WHERE YEAR(col) = 2023).
3. Add or extend composite indexes to match filter order.
4. Use covering indexes to eliminate "Using where; Using filesort".
5. Rewrite OR-chained predicates as UNION ALL.
6. Avoid OFFSET N for large N; use "seek method" (keyset pagination) instead.

Deadlock Example

-- session 1
START TRANSACTION;
UPDATE account SET balance = balance - 100 WHERE id = 1;

-- session 2
START TRANSACTION;
UPDATE account SET balance = balance - 100 WHERE id = 2;

-- session 1
UPDATE account SET balance = balance + 100 WHERE id = 2;  -- waits

-- session 2
UPDATE account SET balance = balance + 100 WHERE id = 1;  -- deadlock detected

MyBatis Quick Notes

• #{} – prepared statement parameter, SQL-injection safe.
• ${} – literal substitution, useful for dynamic column/table names.
• First-level cache is per-SqlSession; second-level cache is global and pluggable (Redis, Ehcache).

Common SQL Recipes

-- Pagination (keyset style, faster than OFFSET)
SELECT * FROM posts
WHERE (created_at, id) < (?, ?)
ORDER BY created_at DESC, id DESC
LIMIT 20;

-- Time diff
SELECT TIMESTAMPDIFF(SECOND, start_ts, end_ts) AS duration_sec
FROM events;

-- Conditional aggregation
SELECT
  SUM(CASE WHEN status = 'PAID' THEN amount ELSE 0 END) AS paid_amount,
  SUM(CASE WHEN status = 'REFUND' THEN amount ELSE 0 END) AS refund_amount
FROM orders;

-- Distinct count
SELECT COUNT(DISTINCT user_id) FROM logins WHERE login_date = CURDATE();