Comprehensive MySQL Interview Guide: Concepts, Indexes, Transactions, Storage Engines, and Optimization

1. Basic Concepts

Superkey, Candidate key, Primary key, Foreign key: A superkey uniquely identifies a row; a candidate key is a minimal superkey; the primary key uniquely and non‑nullably identifies each row; a foreign key references a primary key in another table.

Why use auto‑increment primary keys: InnoDB uses the primary key as the clustered index, so sequential inserts avoid page splits and fragmentation, improving write performance compared to random keys.

Triggers, Stored Procedures, Functions: Triggers are special stored procedures executed on events; stored procedures are pre‑compiled SQL blocks that can be called from applications; functions differ mainly in return values and usage.

SQL Language Categories: DQL (SELECT), DML (INSERT, UPDATE, DELETE), DDL (CREATE, ALTER, DROP), DCL (GRANT, REVOKE).

2. Indexes

What is an index: A sorted data structure (usually B‑tree or B+‑tree) that allows fast row lookup.

Advantages: Faster queries, uniqueness enforcement, join acceleration, sorting/grouping efficiency.

Disadvantages: Additional storage, slower INSERT/UPDATE/DELETE, maintenance overhead.

Suitable columns for indexing: Frequently searched, primary keys, foreign keys, columns used in WHERE, JOIN, ORDER BY, or range queries; avoid low‑cardinality, rarely used, or large TEXT/BLOB columns.

Index types: B+‑tree (default, supports range queries) vs. Hash (exact match only). MySQL’s InnoDB uses B+‑tree; MEMORY engine can use Hash.

Clustered vs. Non‑clustered indexes: Clustered index stores rows in primary key order; non‑clustered index stores pointers to rows.

3. Transactions

Definition: A group of operations that commit or roll back as a unit, ensuring ACID properties.

ACID: Atomicity, Consistency, Isolation, Durability.

Isolation levels (MySQL default is REPEATABLE‑READ):

READ UNCOMMITTED – allows dirty reads.

READ COMMITTED – prevents dirty reads.

REPEATABLE READ – prevents non‑repeatable reads, may cause phantom reads.

SERIALIZABLE – highest isolation, eliminates phantom reads.

Concurrency problems: Dirty read, non‑repeatable read, phantom read.

Transaction propagation (Spring style):

PROPAGATION_REQUIRED

PROPAGATION_SUPPORTS

PROPAGATION_MANDATORY

PROPAGATION_REQUIRES_NEW

PROPAGATION_NOT_SUPPORTED

PROPAGATION_NEVER

PROPAGATION_NESTED

4. Storage Engines

InnoDB vs. MyISAM vs. MEMORY:

InnoDB – supports transactions, row‑level locking, foreign keys; default engine.

MyISAM – table‑level locking, no transactions, faster for read‑heavy workloads.

MEMORY – stores data in RAM, uses hash indexes, data lost on restart.

Choosing an engine: Use InnoDB for data integrity and write‑intensive workloads; MyISAM for pure read‑heavy scenarios; MEMORY for temporary fast tables.

5. Optimization

SQL execution order: FROM → WHERE → GROUP BY → HAVING → SELECT → ORDER BY (note that JOIN order is right‑to‑left).

Using EXPLAIN: Columns such as type, possible_keys, key, key_len, rows, and Extra help identify full table scans, index usage, and potential bottlenecks.

Slow query handling: Enable slow_query_log, set long_query_time, and analyze the log.

6. Locks

Lock granularity: Table lock (low overhead, no deadlocks), Page lock (moderate), Row lock (high concurrency, possible deadlocks).

Deadlock resolution: Identify blocking sessions via SELECT trx_mysql_thread_id FROM information_schema.INNODB_TRX; , kill offending thread, or set innodb_lock_wait_timeout .

Pessimistic lock: SELECT ... FOR UPDATE within a transaction acquires row locks.

Optimistic lock: Use a version column; update with WHERE version = old_version and check affected rows.

7. Replication and High‑Concurrency

Replication modes: Asynchronous (default), Semi‑synchronous, Synchronous.

Read‑write splitting: Writes go to master; reads are distributed among slaves via a proxy or load balancer.

Scaling strategies: Horizontal sharding (horizontal partitioning), vertical splitting, and adding cache layers (e.g., Memcached) to reduce DB load.

Failure handling: Promote a slave to master, use multiple proxies for high availability.