Essential MySQL Interview Q&A: Locks, Indexes, Replication, and Optimization

This article compiles common MySQL interview questions and answers.

What are the differences between MyISAM and InnoDB?

InnoDB supports transactions, foreign keys, and clustered indexes, uses MVCC for high concurrency, and stores indexes and data together. It does not store the exact row count, so select count(*) from table requires a full table scan, whereas MyISAM keeps a row count variable. InnoDB’s smallest lock granularity is a row lock, while MyISAM uses table locks; InnoDB is the default storage engine.

What lock types does MySQL have?

MySQL provides shared (S) locks and exclusive (X) locks, also known as read and write locks. Based on granularity, there are table locks, page locks, and row locks.

What is a gap lock?

A gap lock appears under the REPEATABLE READ isolation level; MySQL creates left‑open, right‑closed intervals that, together with MVCC, prevent phantom reads.

How to avoid deadlocks?

Design indexes with high selectivity and place high‑cardinality columns first.

Adjust SQL execution order to avoid long‑running updates/deletes holding locks early in a transaction.

Split large transactions into smaller ones.

Access tables and rows in a consistent order across transactions.

Avoid explicit locking inside high‑concurrency transactions (e.g., SELECT … FOR UPDATE).

Prefer primary‑key or indexed lookups.

Optimize SQL and schema to reduce resource contention, such as avoiding unnecessary joins.

What are the MySQL isolation levels?

Read Uncommitted, Read Committed, Repeatable Read (default, may cause phantom reads), and Serializable.

What index types does MySQL support?

Normal index – single‑column, multiple per table.

Unique index – column values must be unique, nulls allowed.

Composite index – multi‑column, used for combined searches.

Clustered index – primary key index, data stored in leaf nodes; only one per table.

Non‑clustered index – leaf nodes store index columns and primary key; may require a “covering index” to avoid back‑table lookups.

What are covering indexes and back‑table lookups?

1) A covering index contains all fields needed for a query, eliminating the need for a back‑table lookup. select buyer_id from order where money>100 If a composite index on (money, buyer_id) exists, the leaf nodes store buyer_id, so no back‑table lookup is required.

2) A back‑table lookup occurs when some required fields are not in the index, forcing a second read of the primary‑key B‑tree.

What is MySQL’s left‑most prefix rule?

When using a composite index, MySQL can use the index from the leftmost column until it encounters a range condition (e.g., >, <, BETWEEN, LIKE). Example: with index (a,b,c,d), a query on a=1 AND b=2 AND c>3 AND d=4 can use columns a, b, c but not d.

What are your online SQL tuning experiences?

Analyze slow queries from slow_query_log with EXPLAIN to check index usage.

Reduce rows scanned by indexes and target slow SQL for optimization.

Create composite indexes following the left‑most prefix rule to filter early and reduce back‑table reads.

Use virtual columns and composite indexes to improve complex query performance.

Why does MySQL recommend an auto‑increment ID as the primary key?

Auto‑increment IDs are sequential, causing inserts to append at the end, reducing page splits and data movement; avoid using string keys like UUIDs.

Why does MySQL use B+ trees for indexes instead of B‑trees or red‑black trees?

B‑trees store one key per node, leading to deep trees and high I/O for large tables.

B‑trees store data in non‑leaf nodes, wasting space.

B+ trees store all data in leaf nodes, internal nodes hold only key+pointer, resulting in a flatter tree (often three levels) that can handle millions of rows efficiently, with leaf nodes linked for fast range scans.

What are the properties of a transaction?

Atomicity – all operations succeed or all fail.

Durability – changes are permanently stored.

Consistency – transitions from one consistent state to another.

Isolation – uncommitted changes are invisible to other transactions.

How to implement distributed transactions?

Asynchronous tasks with eventual consistency (idempotent APIs).

Transactional messages.

Two‑phase commit.

Three‑phase commit.

TCC (Try‑Confirm‑Cancel).

Seata framework.

How does MySQL master‑slave replication work?

The master writes data changes to its binary log (binlog).

A log‑dump thread on the master notifies slaves of new events.

The slave requests the binlog from the master and stores it in a relay log.

The slave’s SQL thread reads the relay log and replays the events (redo) locally.

What is master‑slave lag and how to detect it?

Lag is the time difference between a write completing on the master and the data being fully applied on the slave. It can be measured as t2‑t1, where t1 is the timestamp in the binlog and t2 is the execution time on the slave. Use SHOW SLAVE STATUS and check Seconds_Behind_Master.

How to resolve master‑slave lag?

Force reads from the master if latency is unacceptable.

Introduce caching and update the cache after writing to the master.

Upgrade slave hardware to improve binlog sync speed.

Reduce network latency between master and slave.

Use a canal component for incremental subscription to alleviate master load.

Break large transactions into smaller ones.

Enable multi‑threaded replication (set slave_parallel_workers > 0 and slave_parallel_type=LOGICAL_CLOCK in MySQL 5.7+).

Deploy a floating IP on the slave and switch to the master when lag exceeds a threshold, then switch back after catch‑up.

What to do when data volume is huge?

For tables exceeding tens of millions of rows, consider sharding (horizontal partitioning) and other scaling techniques such as caching, read/write splitting, vertical partitioning, cold‑hot data separation, Elasticsearch for complex search, NoSQL, or NewSQL solutions.

How to guarantee globally unique IDs after sharding?

UUID.

Database auto‑increment IDs.

Database segment (range) allocation per service.

Redis INCR command for atomic increments.

Snowflake algorithm.

Open‑source generators (e.g., Baidu uid‑generator, Meituan Leaf, Didi Tinyid).

What problems may arise after sharding?

Sharding introduces a sharding_key for routing. For example, using buyer_id as the key works for buyer‑centric queries but not for seller‑centric ones. Solutions include:

Maintain separate buyer and seller databases; replicate necessary fields (e.g., seller_id, order_id, buyer_id) to the seller DB and use seller_id as the sharding key.

Perform multi‑threaded scans across shards and aggregate results.

Synchronize data to Elasticsearch for multi‑dimensional search.