MySQL Transaction Concepts, Isolation Levels, MVCC, Storage Engines, Indexes, and Optimization Techniques

1. What is a MySQL transaction? What are its four properties? What problems does it bring?

MySQL defines four isolation levels: READ UNCOMMITTED, READ COMMITTED, REPEATABLE READ, SERIALIZABLE . The four ACID properties are Atomicity, Consistency, Isolation, and Durability.

Atomicity : either all modifications succeed or none do, based on Redo/Undo logs.

Consistency : the state before and after a transaction remains consistent.

Isolation : transactions are isolated from each other, related to the chosen isolation level.

Durability : once committed, changes are persisted to disk.

Different isolation levels affect phenomena such as dirty reads, non‑repeatable reads, phantom reads, and locking behavior.

2. Do you understand MVCC? How does it work?

MVCC

(Multi‑Version Concurrency Control) uses a consistency view to support READ COMMITTED and REPEATABLE READ . Each row can have multiple versions; a new version is created after an update, identified by a monotonically increasing transaction ID stored in row_trx_id. Older versions are kept in the undo log and can be reconstructed as needed, enabling fast snapshot reads.

3. Differences between InnoDB and MyISAM

InnoDB supports transactions and row‑level locks; MyISAM does not and uses table‑level locks. Index storage differs: MyISAM stores row pointers in leaf nodes, while InnoDB stores the actual row data in the primary (clustered) index and only the primary key in secondary indexes.

4. Query execution process

Client sends the SQL to the server.

Server authenticates the client.

Cache lookup (MySQL 8 removed the query cache).

Parser, optimizer, and execution plan generation.

Executor runs the plan, invoking the storage engine, and returns results.

5. redo log and binlog

redo log

(WAL) records physical changes before they are flushed to disk, providing crash‑safety. It is a fixed‑size circular log with a write position and checkpoint. binlog is a logical archive of SQL statements (STATEMENT format) or row images (ROW format) used for replication.

6. Adding a column to a hot table in production

Altering a large table acquires an MDL write lock and scans the whole table, which can cause downtime. Strategies include scheduling during low‑traffic windows, using online DDL, or breaking the operation into smaller steps.

7. How MySQL indexes are implemented

MySQL uses B+‑tree indexes. Hash indexes are fast for equality but unsuitable for range queries. Ordered arrays have high update costs, and binary trees cause many random I/O operations. B+‑trees balance read/write performance and support efficient range scans.

8. Checking index effectiveness and common causes of index loss

Use EXPLAIN to see the key column. Indexes may become ineffective when using OR, non‑left‑most LIKE, mismatched data types, != / <>, functions on indexed columns, or null checks.

9. Types of indexes

Based on data structure: B+‑tree, hash, R‑Tree, FULLTEXT. Based on storage: clustered vs. non‑clustered. Based on logic: primary, unique, ordinary, composite, spatial.

10. SQL optimization basics

Key steps: add appropriate indexes on WHERE and ORDER BY columns, avoid index‑invalidating patterns, and use EXPLAIN to analyze execution plans.

11. Clustered vs. non‑clustered indexes

In a clustered index (InnoDB primary key), leaf nodes store the full row. Non‑clustered indexes store only the indexed columns plus a pointer to the primary key.

12. What is a “back‑table” (回表) and why it occurs

When a secondary index is used, MySQL may need to fetch the full row from the primary index—this extra lookup is called a back‑table.

13. Solving back‑table issues

Create covering (composite) indexes that include all columns needed by the query, eliminating the need for a second lookup.

14. The left‑most prefix rule

For composite indexes, the optimizer can use the leftmost N columns. For string indexes, it can use the leftmost M characters. Queries that do not follow this rule may cause full‑table scans.

15. Index condition pushdown (索引下推)

MySQL 5.6+ can apply additional predicates (e.g., age=10) directly on the index, reducing the number of rows that need to be fetched from the table.

16. Auto‑increment ID vs. UUID as primary keys

Auto‑increment IDs are sequential, compact (4‑8 bytes), and insert‑friendly. UUIDs are 16 bytes, random, cause page splits, and increase storage and index size, so they are generally discouraged unless required.

17. Concurrency control in MySQL

MyISAM uses table‑level read/write locks. InnoDB uses row‑level locks, gap locks, and next‑key locks, providing higher concurrency for mixed read/write workloads.

18. Deadlocks in MySQL and how to resolve them

Deadlocks occur in InnoDB when two transactions acquire locks in opposite order. Solutions include ordering accesses consistently, using shorter transactions, setting innodb_lock_wait_timeout, and enabling innodb_deadlock_detect to let MySQL roll back one victim.

19. MySQL master‑slave replication

Replication enables read/write separation: writes go to the master, reads are served by slaves. The binary log (binlog) records changes, which are replayed on replicas.

20. Sharding (分库分表) concepts

Vertical sharding splits a table by columns (e.g., moving large text fields to a separate table). Horizontal sharding splits rows across multiple tables or databases based on a key (e.g., user ID modulo). Tools like MyCat or Sharding‑JDBC implement these strategies.

Reference

《Mysql45讲》, 《数据库原理》