MySQL

Theory

MySQL is the world's most deployed open-source relational database. InnoDB is the default storage engine — ACID-compliant with row-level locking, MVCC, foreign keys, and crash recovery via redo logs. MyISAM is legacy (no transactions) — avoid for new work.

Transaction isolation: READ COMMITTED (default in many DBs) sees committed changes from other transactions mid-query. REPEATABLE READ is MySQL InnoDB default — consistent snapshot for transaction duration, prevents non-repeatable reads but can have phantom reads (gap locks address this).

EXPLAIN output columns: type (ALL=full scan, ref=index lookup, range=index range scan), possible_keys, key (index used), key_len (bytes used — composite index prefix), rows (estimated), Extra (Using index = covering index, Using filesort = extra sort pass, Using temporary = temp table).

Covering index includes all columns needed by a query — InnoDB secondary indexes leaf nodes store primary key; if all SELECT columns are in index, no table lookup (Using index in Extra). Design indexes for your actual query patterns, not every column.

Binlog (binary log) records all data changes for replication and point-in-time recovery. Row-based format (binlog_format=ROW) is safest for consistency. Primary replicates to replicas asynchronously — replicas may lag (replication lag). Semi-sync replication waits for one replica ack before commit.

Slow query analysis: enable slow_query_log with long_query_time threshold. pt-query-digest (Percona Toolkit) aggregates slow.log into normalized patterns ranked by total time. Focus on queries with highest Query_time × count product.

Stored procedures run logic inside the DB — reduce round trips but harder to version/deploy than app code. Use for simple batch operations; avoid complex business logic in procedures for microservice architectures.

Query optimization workflow: EXPLAIN the slow query, check indexes on WHERE/JOIN/ORDER BY columns, verify cardinality statistics (ANALYZE TABLE), avoid SELECT *, rewrite OR conditions as UNION for index use, and consider read replicas for analytics load.

Architecture Diagram

Users / clients
         |
  MySQL
         |
  Core services
         |
  Data + observability

Examples

EXPLAIN SELECT id, email, status
FROM users
WHERE status = 'active' AND created_at > '2024-01-01'
ORDER BY created_at DESC
LIMIT 50;

CREATE INDEX idx_users_status_created
  ON users (status, created_at, email, id);
-- Covering index: all SELECT columns included → "Using index" in Extra

START TRANSACTION;
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
UPDATE accounts SET balance = balance + 100 WHERE id = 2;
COMMIT;

-- Isolation demo: REPEATABLE READ
SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ;
START TRANSACTION;
SELECT balance FROM accounts WHERE id = 1;  -- 500
-- (another session updates balance to 400 and commits)
SELECT balance FROM accounts WHERE id = 1;  -- still 500

# Enable slow log
SET GLOBAL slow_query_log = ON;
SET GLOBAL long_query_time = 1;

# Analyze with Percona Toolkit
pt-query-digest /var/log/mysql/slow.log | head -40

# Check replication lag
SHOW REPLICA STATUS\G
# Seconds_Behind_Source (or Seconds_Behind_Master)

Key Concepts

Interview Questions

Best Practices

• Enable slow query log in staging with long_query_time=0.5 — find real offenders before production tuning.
• Analyze EXPLAIN rows and Extra — "Using filesort" or "Using temporary" on large tables need indexes or query rewrites.
• Use ROW binlog format for replication — STATEMENT mode breaks with non-deterministic functions.
• Prefer ALGORITHM=INPLACE, LOCK=NONE for online DDL when InnoDB supports it — avoid full table copies.
• Size InnoDB buffer pool to ~70% of RAM on dedicated DB servers — the dominant read performance lever.
• Use connection pooling (ProxySQL or app-side pool) — MySQL thread-per-connection does not scale like PgBouncer.

Common Mistakes

• Adding indexes without checking cardinality — low-cardinality columns (boolean, status) rarely help B-tree lookups.
• Using SELECT * with JOINs — multiplies row width and prevents covering indexes.
• Running schema migrations without pt-online-schema-change on huge tables — hours-long locks block writes.
• Ignoring deadlock logs — recurring deadlocks indicate transaction ordering bugs, not random noise.
• Mixing MyISAM and InnoDB — MyISAM has no crash recovery or foreign keys; legacy tables cause data loss.

Cheat Sheet

Practical Exercises