MySQL Interview Essentials: Indexes, Transactions, Storage Engines, and Schema Design

The article is aimed at developers preparing for MySQL interviews and presents a collection of frequently asked questions with concise answers.

Indexes

An index is a data structure that speeds up data lookup. In MySQL the most common implementations are B‑tree (default for InnoDB) and hash indexes. B‑tree supports range queries, sorting, and stable performance, while hash indexes are faster for equality lookups but cannot handle ranges, sorting, or fuzzy matches.

Clustered indexes store the full row in the leaf nodes; InnoDB uses the primary key as the clustered index. If no primary key exists, InnoDB picks a unique NOT NULL key or creates an implicit one.

Non‑clustered indexes may require a back‑table lookup unless the query can be satisfied entirely from the index (covering index).

When creating indexes consider column selectivity, query patterns, and the impact on write performance.

Composite (joint) indexes must follow the left‑most prefix rule; the column order should reflect the most selective or most frequently used columns.

Transactions and ACID

A transaction groups a series of operations that must either all succeed or all fail, adhering to the ACID properties:

Atomicity – all‑or‑nothing execution.

Consistency – the database moves from one consistent state to another.

Isolation – uncommitted changes are invisible to other transactions (with exceptions at READ UNCOMMITTED).

Durability – committed changes survive crashes.

Common concurrency problems include dirty reads, non‑repeatable reads, and phantom reads. MySQL provides four isolation levels: READ UNCOMMITTED, READ COMMITTED, REPEATABLE READ (default for InnoDB), and SERIALIZABLE, each offering progressively stronger guarantees at the cost of performance.

InnoDB uses REPEATABLE READ by default.

Locking

MySQL uses shared (read) locks and exclusive (write) locks. InnoDB implements row‑level, page‑level, and table‑level locks, with row‑level locks offering the highest concurrency.

Storage Engines

MySQL supports multiple storage engines; InnoDB is the default and recommended for most workloads because it supports transactions, row‑level locking, MVCC, and foreign keys. MyISAM lacks transaction support and uses table‑level locks.

Data Types and Schema Design

Use CHAR for fixed‑length fields such as password hashes, and VARCHAR for variable‑length data. Define columns as NOT NULL to avoid extra storage overhead and unexpected NULL handling.

Primary keys should be auto‑increment integers rather than UUIDs to keep the clustered index sequential and avoid fragmentation.

Normalization (1NF, 2NF, 3NF) helps maintain data integrity; denormalization may be justified for performance reasons.

Binary Log Formats

MySQL binlog can be written in three formats: STATEMENT (records the SQL statement), ROW (records each row change), and MIXED (uses STATEMENT when safe, otherwise ROW).

Pagination and Sharding

Large offsets are inefficient because the engine must scan and discard many rows. Optimizations include using indexed subqueries, “id > N” style pagination, or caching results in Redis.

Horizontal sharding splits tables by rows (e.g., by ID range), while vertical sharding separates columns into different tables (e.g., storing large text content separately).

Stored Procedures and MyBatis

Stored procedures are pre‑compiled SQL blocks that can improve performance and reduce network traffic but are often discouraged in fast‑moving web projects due to maintenance challenges.

In MyBatis, # placeholders safely bind parameters (preventing SQL injection), whereas $ performs raw string substitution.

Miscellaneous

Binlog formats, pagination tricks, and the differences between CHAR and VARCHAR are also discussed, providing a well‑rounded view of MySQL internals useful for interview preparation.