Master MySQL Interview Essentials: 43 Key Topics Explained

1. Three Normal Forms

1NF requires each column to hold atomic values. 2NF adds a primary key and ensures each non‑key attribute depends on it. 3NF demands that every column depends directly on the primary key, not indirectly through other columns. In practice, denormalization may be used for performance.

2. DML vs DDL

DML (Data Manipulation Language) handles INSERT, UPDATE, DELETE, SELECT.

DDL (Data Definition Language) creates, alters, drops database objects and does not affect row data.

3. Primary Key vs Foreign Key

Primary key uniquely identifies a row, cannot be NULL, and a table has only one.

Foreign key references a primary key in another table, can be NULL, and a table may have many.

4. DROP, DELETE, TRUNCATE

DROP TABLE tbl

removes the table definition and data. TRUNCATE TABLE tbl quickly removes all rows and resets auto‑increment counters. DELETE FROM tbl WHERE col=val removes rows matching a condition; without WHERE it behaves like TRUNCATE but logs each row.

DROP and TRUNCATE are DDL statements (non‑transactional), while DELETE is DML (transactional and logged).

5. Basic MySQL Architecture

The client sends a SQL statement that passes through the connector, query cache (removed after MySQL 8.0), parser, optimizer, executor, and finally the storage engine.

6. MyISAM vs InnoDB

MyISAM uses table‑level locks, no transactions, no foreign keys, and no crash‑safe recovery.

InnoDB supports row‑level locks, ACID transactions, foreign keys, MVCC, and crash‑safe recovery via redo logs.

InnoDB stores data and indexes together (clustered), while MyISAM stores them separately.

InnoDB generally offers better performance, especially under concurrent workloads.

7. Auto‑Increment Primary Key

Using an auto‑increment integer as the primary key keeps index size small and inserts sequential, reducing page splits.

8. Why Auto‑Increment IDs May Not Be Continuous

Values are kept in memory and not persisted.

Unique‑key conflicts or transaction rollbacks increment the counter without inserting a row.

9. Redo Log

Redo log is an InnoDB‑specific log that enables crash recovery by recording changes before they are flushed to disk.

10. Redo Log Flush Timing

InnoDB’s innodb_flush_log_at_trx_commit controls when the redo log buffer is flushed: 0 (no flush), 1 (flush on each commit, default), 2 (flush to page cache, fsync later).

11. How Redo Log Records Changes

Redo logs are stored as a set of equal‑sized files forming a circular buffer; when the buffer is half full, a background thread forces a flush.

12. Binlog

Binary log records logical changes (SQL statements) for backup and replication. It is written at transaction commit.

13. Binlog Formats

STATEMENT – stores the original SQL.

ROW – stores the actual row changes.

MIXED – chooses between STATEMENT and ROW per statement.

14. Binlog Write Mechanism

During a transaction, changes are first written to the binlog cache; on commit the cache is flushed to the binlog file.

15. Redolog vs Binlog

Redolog is InnoDB‑only, crash‑safe, and records physical changes.

Binlog is server‑level, logical, used for replication and point‑in‑time recovery.

16. Two‑Phase Commit

InnoDB writes redo log in a PREPARE phase; if binlog fails, the transaction can be rolled back safely because the redo log indicates the prepare state.

17. Undo Log

Undo log records before‑image of each row change, enabling transaction rollback and MVCC versioning.

18. Relaylog

Relaylog stores binlog events fetched from the master on a slave; the slave SQL thread reads it to apply changes.

19. Index Basics

Indexes are data structures that speed up data retrieval, similar to a book’s table of contents.

20. Hash Index

Hash tables provide O(1) lookups but cannot support range queries, so 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; leaf nodes contain keys and data and are linked for sequential access.

22. Primary Key Index

In InnoDB, the primary key is a clustered index; if none is defined, InnoDB creates a hidden 6‑byte key.

23. Secondary Index

Secondary (auxiliary) indexes store the primary key in leaf nodes, allowing lookup of the row via the primary key.

24. Clustered vs Non‑Clustered Index

Clustered indexes store data together with the index (InnoDB primary key). Non‑clustered indexes store only the index and a pointer to the row.

25. Backward Lookup (回表)

After finding a secondary index entry, the engine reads the primary key and then fetches the full row.

26. Covering and Composite Indexes

A covering index contains all columns needed by a query, eliminating the need for a backward lookup. Composite indexes combine multiple columns.

27. Left‑most Prefix Rule

MySQL can use a composite index only for the leftmost contiguous columns that appear in the query conditions.

28. Index Condition Pushdown

ICPD filters rows using index values during index traversal, reducing the number of rows that need to be read from the table.

29. Implicit Type Conversion

MySQL automatically converts operand types to a common type during expression evaluation, following a defined precedence.

30. Choosing Normal vs Unique Index

Unique indexes are ideal for lookup queries because they stop after the first match.

Normal indexes are better for high‑frequency inserts.

31. Avoiding Index Loss

Do not use SELECT *.

Follow the left‑most prefix rule.

Avoid functions or calculations on indexed columns.

Do not start LIKE patterns with %.

Avoid OR conditions that reference non‑indexed columns.

32. Index Creation Rules

Prefer non‑NULL columns with high selectivity.

Index columns used frequently in WHERE, ORDER BY, JOIN, or GROUP BY.

Avoid indexing columns that are updated often.

Prefer composite indexes over many single‑column indexes.

Use prefix indexes for long string columns.

Remove unused indexes.

33. Transaction Properties (ACID)

Atomicity – all statements succeed or none.

Consistency – data remains valid.

Isolation – concurrent transactions do not interfere.

Durability – committed changes survive crashes.

34. Concurrency Issues

Dirty read

Lost update

Non‑repeatable read

Phantom read

35. Isolation Levels

READ UNCOMMITTED – allows dirty reads.

READ COMMITTED – prevents dirty reads.

REPEATABLE READ – prevents dirty and non‑repeatable reads (MySQL default).

SERIALIZABLE – highest isolation, but reduces concurrency.

36. MVCC

Multi‑Version Concurrency Control uses hidden columns, undo logs, and read views to provide snapshot reads without locking.

37. Locks

Refer to the detailed lock classification article for InnoDB and MyISAM lock types.

38. Query Execution Process

select * from tb_student s where s.age='18' and s.name='张三';

Permission check.

Cache lookup (pre‑MySQL 8.0).

Parser tokenizes and validates syntax.

Optimizer chooses the best execution plan.

Executor runs the plan via the storage engine.

39. Update Execution Process

update tb_student A set A.age='19' where A.name='张三';

Find the target row.

Modify the row in memory, record redo log (prepare state).

Executor writes binlog, then commits redo log.

40. SQL Optimization Tips

Avoid full table scans; create indexes on WHERE, ORDER BY, JOIN columns.

Do not use SELECT *; list needed columns.

Limit the number of indexes per table (≈6).

Prefer EXISTS over IN when possible.

Reduce complex joins and use pagination wisely.

41. Master‑Slave Replication

Master writes changes to its binlog.

Slave fetches binlog entries into relaylog.

Slave SQL thread replays relaylog to apply changes.

42. Reducing Replication Lag

Enable parallel replication (MySQL 5.6+).

Upgrade hardware.

Use appropriate sharding and avoid oversized tables.

Avoid long‑running transactions.

Offload heavy computations.

Read from the master for latency‑sensitive queries.

43. Why Avoid Long Transactions

They exhaust connection pools.

They increase lock contention and can cause deadlocks.

They amplify replication lag.

Rollback time grows with transaction length.

Undo logs become large, consuming storage.