Comprehensive MySQL Interview Questions and Answers on Indexes, Transactions, Replication, and More

The article presents a collection of twenty MySQL interview questions sourced from a knowledge community, each accompanied by thorough explanations.

1. Redo Log and Binlog

Redo log is a physical log used by the InnoDB engine, while binlog is a logical log at the server layer that records SQL statements.

2. Index Advantages, Disadvantages, and Principles

Indexes improve query speed, enforce uniqueness, accelerate joins, and reduce sorting time, but they increase write overhead, consume storage, and require maintenance.

Principles include indexing frequently used columns for filtering or sorting, and avoiding indexes on low‑selectivity or large text columns.

3. Types of MySQL Indexes

Supported index types are BTREE (default for most engines), HASH (MEMORY, NDB), RTREE (spatial), and FULLTEXT (text search, supported by InnoDB from 5.6).

4. Recommended Index Usage Principles

Use indexes on WHERE or join columns, prefer unique indexes, avoid over‑indexing, index columns used for sorting/grouping, remove unused indexes, and follow the left‑most prefix rule for composite indexes.

5. Covering Index

A covering index contains all columns needed by a query, allowing the engine to satisfy the query using only the index (Extra shows “Using index”).

6. Joint Index Matching Rules

Rules include equality matching, left‑most column matching, left‑prefix matching, range queries, and combined equality‑range matching. Example diagram omitted.

7. Back‑Table (Row Lookup)

When a non‑covering index is used, MySQL first locates the primary key via the secondary index, then fetches the full row from the clustered index.

8. Left‑Most Matching Principle

MySQL can use a composite index only for continuous left‑most columns; a range condition stops further column usage.

9. Common Index Issues

Misconceptions include assuming all indexes speed up queries and overlooking cases where indexes are not used.

10. Index Condition Pushdown (ICP)

ICP allows filtering conditions to be evaluated within the storage engine, reducing row lookups. Example:

select * FROM person WHERE name like 'ab%' and age = 2;

11. MyISAM vs. InnoDB

MyISAM is non‑transactional, lacks row‑level locking, and stores data and indexes separately; InnoDB supports transactions, row‑level locking, MVCC, and stores data with the primary key index.

12. Buffer Pool Mechanics

Buffer pool uses three linked lists (free, flush, LRU) to manage pages, loading data from disk, handling modifications in memory, and flushing dirty pages back to disk.

13. InnoDB Lock Types

Row‑level locks (S, X) and intention locks (IS, IX) coordinate concurrency; intention locks allow quick checks for existing row locks.

14. MVCC Mechanism

Multi‑Version Concurrency Control uses undo logs and a ReadView to provide consistent snapshots, preventing dirty reads, non‑repeatable reads, and phantom reads across isolation levels.

15. Execution Plan Overview

EXPLAIN output fields (type, possible_keys, key, key_len, ref, Extra) help diagnose query performance; “Using index” indicates a covering index, “Using index condition” indicates ICP.

16. Master‑Slave Replication

The master writes changes to binlog; the slave’s I/O thread fetches binlog entries, writes them to a relay log, and the SQL thread replays them. Replication can be asynchronous, semi‑synchronous, or fully synchronous.

17. Transaction Isolation Levels

Four standard levels: Read Uncommitted, Read Committed, Repeatable Read, and Serializable, each providing increasing guarantees against dirty reads, non‑repeatable reads, and phantom reads.

18. Snowflake ID Algorithm

Twitter’s 64‑bit distributed ID generator splits bits into timestamp, datacenter ID, worker ID, and sequence number, producing sortable, unique IDs.

19. Internal XA Transactions

MySQL uses XA to atomically commit both binlog and InnoDB redo log, ensuring consistency between storage engine and binary log.

20. External XA Transactions

For distributed transactions across multiple MySQL instances, the client acts as the transaction manager, coordinating two‑phase commit via XASTART/END/PREPARE/COMMIT.

Each question is followed by concise explanations, diagrams, and occasional SQL examples such as:

select id, name, age from person where name like '赵%';

The content serves as a practical study guide for candidates preparing for database‑related technical interviews.