MySQL 100 Interview Questions: Indexes, Transactions, Table Design, and Storage Engines

This article is aimed at developers and presents a collection of 100 MySQL interview questions, focusing on the most frequently asked topics such as indexes, transactions, optimization, and table design, while providing concise answers to deepen understanding.

Index related : An index is a data structure that speeds up data lookup. Common index structures in MySQL include hash indexes and B‑tree indexes, with InnoDB’s default being the B‑tree. Hash indexes are faster for equality queries but cannot support range queries, sorting, or prefix matching, while B‑trees support range scans and can serve as clustered or covering indexes. The article explains clustered vs. non‑clustered indexes, composite index ordering, and how to verify index usage with EXPLAIN (examining fields like possible_key , key , key_len ). It also lists scenarios where indexes are ignored, such as inequality conditions, functions on columns, leading‑wildcard LIKE , optimizer‑chosen full scans, and range queries preceding a composite index.

Transaction related : A transaction is a series of operations that must satisfy ACID properties. The article details each component—Atomicity, Consistency, Isolation, Durability—and describes common concurrency problems (dirty read, non‑repeatable read, phantom read). It outlines MySQL’s four isolation levels (READ UNCOMMITTED, READ COMMITTED, REPEATABLE READ, SERIALIZABLE), noting that InnoDB uses REPEATABLE READ by default. Lock types (shared/read lock and exclusive/write lock) and lock granularity (row‑level, page‑level, table‑level) are also discussed.

Table structure design : Emphasizes defining a primary key, preferably an auto‑increment integer rather than a UUID, because InnoDB stores the primary key as a clustered index. It advises using NOT NULL to save space and avoid unexpected behavior, and recommends CHAR for fixed‑length fields such as password hashes, while VARCHAR should be used for variable‑length data.

Storage engine related : MySQL supports multiple engines; InnoDB is the default and generally the best choice. Differences between InnoDB and MyISAM are highlighted: transaction support, row‑level vs. table‑level locking, MVCC, foreign key support, and full‑text indexing.

Miscellaneous topics : The article compares CHAR vs. VARCHAR , explains the meaning of display width in INT(10) , describes the three binlog formats (STATEMENT, ROW, MIXED), and offers strategies for handling huge pagination queries (e.g., using indexed subqueries or ID‑based limits). It covers slow‑query analysis, horizontal and vertical sharding examples, pros and cons of stored procedures, the three normal forms of database normalization, and the difference between # and $ in MyBatis parameter binding.