Common MySQL Interview Questions and Answers

Preface

MySQL is frequently asked in interviews; this article summarizes classic interview questions.

1. What are the three normal forms?

First Normal Form (1NF): each column cannot be further divided.

Second Normal Form (2NF): based on 1NF, non‑key columns must depend fully on the whole primary key, not on part of it.

Third Normal Form (3NF): based on 2NF, non‑key columns depend only on the primary key and not on other non‑key columns.

When designing a database, try to follow the three normal forms; if you deviate, you must have a sufficient reason, such as performance considerations.

2. Which tables store MySQL privileges?

MySQL controls access through privilege tables stored in the mysql database, initialized by mysql_install_db .

The tables are user , db , table_priv , columns_priv , and host .

user table: records accounts that may connect to the server; privileges are global.

db table: records privileges for each account on each database.

table_priv table: records table‑level privileges.

columns_priv table: records column‑level privileges.

host table: works with the db table to provide finer‑grained host‑level control; it is not affected by GRANT/REVOKE.

3. Describe the four ACID properties of transactions

Atomicity: a transaction is the smallest unit of execution and cannot be divided.

Atomicity ensures that actions either complete entirely or have no effect.

Consistency: data remains consistent before and after a transaction; multiple transactions see the same data.

Isolation: concurrent transactions do not interfere with each other; each transaction operates as if it were alone.

Durability: once a transaction is committed, its changes persist even if the database crashes.

4. What categories do SQL statements belong to?

Data Definition Language (DDL): CREATE, DROP, ALTER – operations on logical structures such as tables, views, indexes.

Data Query Language (DQL): SELECT – query operations, including simple and join queries.

Data Manipulation Language (DML): INSERT, UPDATE, DELETE – operations that modify data.

Data Control Language (DCL): GRANT, REVOKE, COMMIT, ROLLBACK – operations related to security and transaction control.

5. What is the purpose of MySQL sharding (database and table partitioning)?

Sharding splits a large database or table into multiple smaller databases or tables to reduce data volume per instance, thereby improving performance.

Common middleware for sharding includes the following diagrams:

6. What is a deadlock and how to resolve it?

A deadlock occurs when two or more transactions hold resources the other needs, forming a cycle.

Common solutions:

Access tables in a consistent order across programs.

Lock all required resources at once within a transaction.

Use coarser‑grained locks (e.g., table‑level) for highly contended sections.

If needed, use distributed transaction locks or optimistic locking.

7. What are dirty read, non‑repeatable read, and phantom read?

Dirty read: a transaction reads data that has been modified by another transaction that later rolls back.

Non‑repeatable read: the same query within a transaction returns different results because another transaction modified the data in between.

Phantom read: the number of rows returned by a query changes because another transaction inserted or deleted rows.

8. What are the characteristics of a view?

Views can combine columns from multiple tables, act as logical abstractions, and do not store data physically.

Views are derived from base tables; creating or dropping a view does not affect the base tables.

Modifying data through a view directly affects the underlying tables.

If a view is based on multiple base tables, inserting or deleting rows is not allowed.

Operations on views include creating, querying, dropping, and altering.

9. What is the lifecycle of an SQL statement?

Application server establishes a connection to the database server.

The database process receives the SQL request.

The statement is parsed and an execution plan is generated.

Data is read into memory and logical processing occurs.

The result is sent back to the client via the established connection.

The connection is closed and resources are released.

10. Should primary keys use auto‑increment IDs or UUIDs?

Auto‑increment IDs are recommended over UUIDs.

In InnoDB, the primary key is the clustered index; using sequential IDs keeps inserts at the end of the index, while UUIDs cause random inserts, leading to page splits and fragmentation, reducing insert performance.

For large data volumes, auto‑increment keys generally perform better.

If a table lacks a primary key, InnoDB chooses a unique key as the clustered index, or creates an implicit hidden primary key.

11. How to handle MySQL CPU usage spiking to 100%?

First, use OS tools (e.g., top ) to verify whether mysqld is the culprit.

If not, identify the high‑CPU process and address it.

If mysqld is responsible, run SHOW PROCESSLIST to find resource‑intensive sessions, examine their execution plans, missing indexes, or large data volumes.

Kill offending threads and observe CPU changes, then adjust (add indexes, rewrite SQL, tune memory parameters).

Sometimes many concurrent sessions cause the spike; coordinate with the application to limit connections.

12. What problems does MySQL master‑slave replication solve?

Replication enables failover to a slave when the master fails, supports read/write splitting, and allows backups on the slave.

It provides data distribution across geographic locations, load balancing, high availability, and facilitates upgrade testing.

13. What is MySQL GTID?

GTID (Global Transaction ID) uniquely identifies a committed transaction across the cluster.

Introduced in MySQL 5.6, a GTID consists of UUID+TID, where UUID identifies the server instance.

GTID increments monotonically with each committed transaction.

Benefits:

Identifies the originating server of a transaction.

Simplifies failover because replication does not rely on binary log file/position.

Ensures each transaction is executed only once during replication.

14. Common MySQL backup tools

Replication (MySQL native).

Logical backup: mysqldump , mydumper .

Physical backup: copy, xtrabackup .

Differences:

Replication provides near‑real‑time backup.

Logical backup: easy to split per table; mysqldump creates a single file, mydumper creates one file per table.

Physical backup: simple file copy, fast.

Copying files directly can cause corruption; xtrabackup does not lock InnoDB tables but still requires locks for MyISAM.

15. How to design a MySQL backup plan

For databases under 100 GB, mysqldump is lightweight; schedule full backups during low‑traffic periods, possibly daily.

For databases over 100 GB, use xtrabackup for faster backups.

Typically perform a full backup weekly and incremental backups daily, all during off‑peak hours.

If you notice any errors in the interview questions, please comment for correction; if the article helped you, give the author a free like. Thank you!