MySQL 100 Soul Questions: Indexes, Transactions, Schema Design, Storage Engines, and Miscellaneous Topics

Preface

This article is aimed at developers and does not cover MySQL service deployment; it focuses on interview‑style questions to deepen understanding of key MySQL concepts such as indexes, transactions, optimization, and schema design.

Index Related

Indexes are data structures that accelerate data lookup. In MySQL the most common index types are hash indexes and B‑tree indexes, with InnoDB’s default being the B‑tree.

Hash indexes use a hash table and are fast for equality queries but cannot handle range queries, sorting, or prefix matching, and they always require a table lookup for the actual row data.

B‑tree indexes support range queries, sorting, and multi‑column prefix matching, offering stable performance across different query patterns.

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.

When creating indexes consider field usage frequency, order of columns in composite indexes, and the impact on write performance.

Transaction Related

A transaction is a sequence of operations that must satisfy the ACID properties.

A – Atomicity: all operations succeed or all fail.

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

I – Isolation: uncommitted changes are invisible to other transactions (with exceptions at lower isolation levels).

D – Durability: once committed, changes survive crashes.

Concurrent transactions can cause dirty reads, non‑repeatable reads, and phantom reads. MySQL provides four isolation levels: READ UNCOMMITTED, READ COMMITTED, REPEATABLE READ (default for InnoDB), and SERIALIZABLE.

InnoDB uses REPEATABLE READ by default.

Locks ensure data consistency. Shared (read) locks allow concurrent reads; exclusive (write) locks block other reads and writes. InnoDB implements row‑level, page‑level, and table‑level locks.

Table Structure Design

Define a primary key (preferably an auto‑increment integer) to guarantee row uniqueness and improve performance.

Use char for fixed‑length fields such as password hashes; use varchar for variable‑length data.

Define columns as NOT NULL to avoid extra storage overhead for NULL flags.

Storage Engine Related

MySQL supports multiple storage engines; InnoDB is the default and recommended for most cases.

Key differences between InnoDB and MyISAM: InnoDB supports transactions, row‑level locking, MVCC, foreign keys, and crash recovery; MyISAM lacks these features but supports full‑text indexes.

Miscellaneous Issues

char vs varchar: char(10) always occupies 10 bytes; varchar(10) uses actual length + 1 byte.

int(10) only affects display width, not storage.

MySQL binlog formats: STATEMENT, ROW, and MIXED, each with trade‑offs between size and fidelity.

Large pagination can be optimized by avoiding deep OFFSETs, using indexed subqueries or ID‑based ranges, and caching results.

Slow query analysis involves checking whether queries load unnecessary rows or columns, examining the execution plan for index usage, and considering sharding (horizontal) or column‑splitting (vertical) when data volume is high.

Stored procedures are pre‑compiled SQL blocks that can improve performance but are discouraged in fast‑moving internet projects due to maintenance overhead.

Database normalization: 1NF (no repeating groups), 2NF (non‑key attributes fully depend on the whole primary key), 3NF (no transitive dependencies).

In MyBatis, the # placeholder treats the value as a literal string, helping prevent SQL injection.

END