Top MySQL Interview Questions Explained: Ace Your Database Interview

Basics

1. What is MySQL and its main features?

MySQL is an open‑source relational database management system (RDBMS) that supports multiple users and threads, offering high performance, high reliability and ease of use. It is widely used in web development and is a core component of the LAMP stack.

2. What storage engines does MySQL provide and how do they differ?

MySQL supports several storage engines, the most common being:

InnoDB : supports transactions, row‑level locking, foreign keys; suited for high‑concurrency scenarios.

MyISAM : does not support transactions or row‑level locks but offers fast reads, ideal for read‑heavy workloads.

Memory : stores data in memory for very fast access, but data is lost on restart.

Key differences: InnoDB supports transactions, row‑level locks and foreign keys; MyISAM lacks these and only provides table‑level locks.

SQL Optimization

3. How to optimize SQL queries?

Use indexes : create indexes on frequently queried columns to avoid full table scans.

Avoid SELECT *: select only required columns to reduce data transfer.

Optimize JOINs : ensure join columns are indexed to prevent Cartesian products.

Pagination : for large result sets, use LIMIT and OFFSET with indexed columns.

4. What are indexes and what types exist in MySQL?

An index is a data structure that speeds up query execution. Common index types:

PRIMARY KEY : unique, non‑null identifier for each row.

UNIQUE : enforces uniqueness of column values.

INDEX : basic non‑unique index.

FULLTEXT : supports full‑text search on text columns.

5. When can an index become ineffective?

Using functions or expressions in predicates, e.g., WHERE YEAR(col)=2023.

LIKE patterns that start with a wildcard, e.g., WHERE col LIKE '%abc'.

OR conditions where at least one operand lacks an index.

Data‑type mismatches, such as comparing a string column with a numeric value.

Transactions and Locking

6. What is a transaction and how does MySQL guarantee ACID?

A transaction is a logical unit of work that must be atomic, consistent, isolated, and durable. MySQL uses redo logs, undo logs and lock mechanisms to enforce these properties.

7. What isolation levels does MySQL support?

Read Uncommitted : lowest level, may read uncommitted data.

Read Committed : reads only committed data.

Repeatable Read : default level, guarantees consistent reads within a transaction.

Serializable : highest isolation, eliminates phantom reads but reduces performance.

8. What is a deadlock and how to avoid it?

Access resources in a consistent order across transactions.

Keep transaction length short.

Use a lower isolation level such as Read Committed to reduce lock contention.

High Availability and Architecture

9. How does MySQL master‑slave replication work?

The master writes changes to the binary log (binlog). Slaves read the binlog and replay the events, achieving data synchronization.

Benefits: read/write separation for performance improvement and data backup for higher availability.

10. What are sharding (分库分表) and how to implement it?

Sharding splits data to overcome single‑database size limits.

Vertical sharding : separate databases by business module.

Horizontal sharding : distribute rows across multiple tables based on a rule (e.g., user‑id modulo).

Implementation typically uses middleware such as MyCat or ShardingSphere, or custom routing logic in the application layer.

Practical Topics

11. How to troubleshoot MySQL performance issues?

Enable the slow‑query log to identify long‑running statements.

Use EXPLAIN to examine execution plans.

Deploy monitoring tools (e.g., Prometheus, Zabbix) for real‑time metrics.

12. How to migrate data from large MySQL tables?

Batch migration: move data in small chunks to minimize impact.

Online migration tools such as pt-online-schema-change enable lock‑free schema changes.

Conclusion

Mastering these high‑frequency interview questions not only prepares you for database interviews but also equips you with practical skills for everyday MySQL usage.