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

This article is aimed at developers and compiles a set of 100 essential MySQL interview questions to deepen understanding of core concepts.

Index related

Indexes are data structures that accelerate data lookup. Common index types in MySQL include hash indexes and B+Tree indexes, with InnoDB using B+Tree by default. Hash indexes are faster for equality queries but cannot handle range queries, sorting, or fuzzy matching, and always require a table lookup. B+Tree indexes support range queries, sorting, and can avoid table lookups when using clustered or covering indexes.

Clustered indexes store the full row in leaf nodes; InnoDB uses the primary key as the clustered index, or selects a unique NOT NULL key if no primary key exists.

Non‑clustered indexes may still require a table lookup unless all queried columns are covered by the index.

When creating indexes, consider column usage frequency, order of columns in composite indexes, and avoid excessive indexing that can burden the table.

Composite indexes require queries to follow the defined column order; otherwise the index cannot be used.

Use EXPLAIN to check whether a query uses an index (fields like possible_key, key, key_len). Indexes are ignored in cases such as inequality conditions, functions on columns, leading wildcards in LIKE, full table scans deemed cheaper, or when a preceding range condition blocks later columns in a composite index.

Transaction related

A transaction is a sequence of operations that must satisfy ACID properties: Atomicity, Consistency, Isolation, and Durability.

Isolation levels in MySQL:

READ UNCOMMITTED – allows dirty reads.

READ COMMITTED – prevents dirty reads but can cause non‑repeatable reads.

REPEATABLE READ – prevents non‑repeatable reads; default in InnoDB, but can still produce phantom reads.

SERIALIZABLE – highest isolation, forces serial execution, reducing concurrency.

InnoDB’s default isolation level is REPEATABLE READ.

MySQL uses shared (read) locks and exclusive (write) locks; InnoDB provides row‑level, page‑level, and table‑level locks.

Table structure design

Always define a primary key, preferably an auto‑increment integer rather than a UUID, because InnoDB’s clustered index benefits from sequential inserts.

Define fixed‑length fields (e.g., password hashes, salts) as CHAR instead of VARCHAR for space efficiency and faster lookups.

Use CHAR(10) for fixed‑length strings; VARCHAR stores only actual length plus one byte.

INT(10) only affects display width, not storage size.

Storage engine related

MySQL supports multiple storage engines; InnoDB is the default and recommended for most cases. Compared to MyISAM, InnoDB supports transactions, row‑level locking, MVCC, foreign keys, and better crash recovery, while MyISAM offers full‑text indexing.

Miscellaneous questions

Binary log formats: STATEMENT, ROW, and MIXED, each with trade‑offs between granularity and size.

Large pagination can be optimized by avoiding deep OFFSET scans, using indexed subqueries (e.g., SELECT * FROM table WHERE id > 1000000 LIMIT 10 ) or caching results.

Slow query analysis involves checking unnecessary column loads, ensuring index usage via EXPLAIN, and considering sharding (horizontal) or vertical partitioning when data volume grows.

Horizontal sharding splits rows across multiple tables (e.g., by ID range), while vertical sharding separates infrequently used large columns into separate tables.

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

Database normalization: 1NF (no repeating groups), 2NF (full functional dependency on primary key), 3NF (no transitive dependencies).

In MyBatis, the # placeholder safely injects parameters as strings, helping prevent SQL injection.

Conclusion

The guide consolidates essential MySQL knowledge for interview preparation, covering indexes, transactions, schema design, storage engines, and performance tuning.