Comprehensive MySQL Interview Guide: Key Concepts and Practices

1. Three Normal Forms

1NF requires each column to hold atomic values; 2NF adds a primary key ensuring each row is uniquely identifiable; 3NF builds on 2NF by eliminating transitive dependencies. In practice, denormalization may be used to improve query performance.

2. Difference Between DML and DDL

DML (Data Manipulation Language) covers INSERT, UPDATE, DELETE, and SELECT operations on table data, while DDL (Data Definition Language) includes CREATE, ALTER, and DROP statements that modify database objects.

3. Primary Key vs Foreign Key

A primary key uniquely identifies a row and cannot be null; a table can have only one primary key. A foreign key references the primary key of another table, can be null, and a table may have multiple foreign keys.

4. DROP, DELETE, TRUNCATE

DROP removes the table definition and data; TRUNCATE quickly empties a table while resetting auto‑increment counters; DELETE removes rows based on a WHERE clause and generates binlog entries.

5. MySQL Architecture

The architecture consists of a connector (authentication), optional query cache, parser, optimizer, executor, and pluggable storage engines (e.g., InnoDB, MyISAM).

6. MyISAM vs InnoDB

MyISAM uses table‑level locks, lacks transactions and foreign‑key support, and does not provide crash‑safe recovery. InnoDB supports row‑level locking, ACID transactions, foreign keys, MVCC, and crash recovery via redo logs.

7. Auto‑Increment Primary Key

Using an auto‑increment integer as the primary key yields sequential inserts, reduces page splits, and keeps secondary index size smaller.

8. Non‑Sequential Auto‑Increment IDs

Gaps occur because the auto‑increment counter is stored in memory, is not rolled back on transaction failure, and may be incremented before uniqueness checks.

9. Redo Log

Redo logs are InnoDB‑specific, enabling crash recovery by recording physical changes before they are flushed to disk.

10. Redo Log Flush Timing

The innodb_flush_log_at_trx_commit setting controls when redo logs are flushed: 0 (no flush), 1 (flush on each commit), or 2 (flush to OS cache, fsync later).

11. Redo Log Recording

Redo logs are stored in a circular set of files, written in a ring buffer fashion, and may cause “memory thrashing” if the buffer fills before flushing.

12. Binlog

Binary logs record logical statements for replication and backup; they exist at the server layer and are written after transaction commit.

13. Binlog Formats

Three formats exist: STATEMENT (stores SQL), ROW (stores row changes), and MIXED (chooses based on safety).

14. Binlog Write Mechanism

During a transaction, changes are first placed in the binlog cache and flushed to the binlog file upon commit.

15. Redo Log vs Binlog

Redo logs are engine‑specific, record physical changes, and are crash‑safe; binlogs are server‑wide, record logical changes, and are used for replication.

16. Two‑Phase Commit

InnoDB splits redo log writes into PREPARE and COMMIT phases, ensuring consistency even if binlog writing fails.

17. Undo Log

Undo logs store before‑image records to enable transaction rollback and support MVCC version chains.

18. Relay Log

Relay logs are temporary files on a replica that store the master’s binlog events before they are applied.

19. Index Basics

Indexes accelerate data retrieval; they act like a book’s table of contents.

20. Hash Index

Hash indexes provide O(1) lookups but cannot support range queries, which is why MySQL does not use them for general indexing.

21. B‑Tree vs B+‑Tree

B‑Tree stores keys and data in all nodes; B+‑Tree stores only keys in internal nodes and data in leaf nodes, offering better range scans.

22‑24. Primary, Secondary, Clustered & Non‑Clustered Indexes

Primary indexes are unique and clustered in InnoDB; secondary indexes store the primary key in leaf nodes and may require “back‑lookup” (回表).

25‑28. Index Operations

Cover topics such as back‑lookup, covering indexes, composite indexes, left‑most prefix rule, and index condition push‑down.

29. Implicit Type Conversion

MySQL automatically converts operand types during comparisons, following a defined precedence.

30‑32. Index Selection & Rules

Guidelines for choosing between unique and regular indexes, and best practices for creating effective indexes.

33‑36. Transactions and Concurrency

Explain ACID properties, common concurrency anomalies (dirty read, non‑repeatable read, phantom read), isolation levels (READ UNCOMMITTED, READ COMMITTED, REPEATABLE READ, SERIALIZABLE), and MVCC implementation using hidden columns, undo logs, and read views.

37‑43. Locks, Execution Flow, Optimization, Replication

Discuss lock types, query and update execution steps, SQL optimization tips, master‑slave replication mechanisms, delay mitigation, and why long transactions should be avoided.