Key MySQL Interview Questions and Answers: Joins, Indexes, Transactions, and Optimization

1. Difference between inner join and outer join

Inner join returns only rows that match between the left and right tables, while outer join includes all rows from one side (left or right) and fills non‑matching columns with NULL.

Left outer join: Returns all rows from the left table and matches rows from the right table; unmatched rows appear as NULL.

Right outer join: Returns all rows from the right table and matches rows from the left table; unmatched rows appear as NULL.

2. Difference between DROP, DELETE, and TRUNCATE

DELETE removes rows one by one and records each deletion in the transaction log, allowing rollback.

DROP deletes whole objects such as tables, columns, or indexes.

TRUNCATE drops the table data and recreates the table structure.

In terms of performance, DROP is fastest, followed by TRUNCATE, while DELETE is the slowest; DROP and TRUNCATE do not generate logs and cannot be rolled back.

3. Difference between UNION and UNION ALL

Both combine results of multiple SELECT statements, but UNION removes duplicate rows whereas UNION ALL returns all rows, including duplicates.

4. Difference between CHAR and VARCHAR

Maximum length: CHAR up to 255 characters; VARCHAR up to 65,535 bytes.

Storage: CHAR is fixed‑length and pads with spaces; VARCHAR is variable‑length.

Space usage: CHAR can waste space; VARCHAR is more space‑efficient.

Search efficiency: CHAR is faster to search; VARCHAR is slower.

Use CHAR for fixed‑length data such as ID numbers or phone numbers; use VARCHAR for variable‑length data and keep the declared length close to the actual length.

Note: In VARCHAR(50) the number 50 indicates the maximum number of characters; VARCHAR(50) and VARCHAR(200) occupy the same space when storing the word "hello".

5. The four properties of a transaction

Atomicity: A transaction is an indivisible unit; either all its statements succeed or none do.

Consistency: Data remains consistent before and after the transaction.

Isolation: Concurrent transactions do not interfere with each other.

Durability: Once committed, changes survive system failures.

6. Problems caused by concurrent transactions

Dirty read – reading uncommitted data from another transaction.

Non‑repeatable read – reading data that another transaction has modified.

Phantom read – reading rows that another transaction has inserted.

7. Transaction isolation levels

Read uncommitted: Allows dirty reads, phantom reads, and non‑repeatable reads.

Read committed: Prevents dirty reads but may still have phantom or non‑repeatable reads.

Repeatable read: Guarantees consistent reads of the same rows; prevents dirty and non‑repeatable reads but may allow phantom reads. This is MySQL's default.

Serializable: Executes transactions one after another, eliminating all three phenomena.

8. The three normal forms

First Normal Form (1NF): Each column holds atomic values.

Second Normal Form (2NF): A table should represent a single subject; no mixed‑level data.

Third Normal Form (3NF): No transitive dependencies; derived data should not be stored.

In practice, denormalization (adding redundant fields) may be used to reduce joins.

9. Types of indexes

Physical classification: Clustered index (data and index stored together, used by InnoDB) and non‑clustered index (separate files, used by MyISAM).

Logical classification: Ordinary, unique, primary key, and composite indexes. Unique indexes enforce uniqueness; primary keys are unique and NOT NULL; composite indexes cover multiple columns.

10. Principles for creating indexes

Index primary key and foreign key columns.

Index columns frequently used in WHERE, ORDER BY, or GROUP BY.

Index columns often used in aggregate functions.

Prefer composite indexes over many single‑column indexes when queries filter on multiple columns.

Do not create indexes for:

Very small tables.

Columns of type TEXT, BLOB, BIT, etc.

Low‑cardinality columns such as gender or age.

11. Situations where indexes become ineffective

LIKE patterns that start with %.

Join conditions that involve non‑indexed columns.

Functions, calculations, or type conversions on indexed columns.

Using !=, NOT IN, IS NULL, IS NOT NULL.

Violating the left‑most prefix rule on composite indexes.

12. How to detect index inefficiency

Use the EXPLAIN statement before a query and examine the type and extra columns. Preferred type values are range, ref, eq_ref, const, or system. Avoid using filesort, using temporary, and aim for using index.

13. Differences between MyISAM and InnoDB

MyISAM does not support transactions or foreign keys; InnoDB does.

MyISAM uses table‑level locking; InnoDB uses row‑level locking.

MyISAM stores indexes separately from data (non‑clustered); InnoDB stores data and primary key index together (clustered).

In MyISAM, both primary and secondary indexes store file pointers; In InnoDB, primary indexes store the row data, secondary indexes store the primary key value.

14. Query execution flow in MySQL

Connection layer establishes the session and checks permissions.

Query cache is consulted; if a cached result exists, it is returned.

The parser checks lexical and syntactic correctness.

The optimizer chooses indexes, join order, and execution plan.

The executor invokes the appropriate storage engine.

The storage engine performs the actual data read/write.

15. Index data structure

MySQL indexes are implemented with B+Tree structures, which are balanced, have lower height than B‑Tree, and provide efficient range queries via linked leaf nodes.

16. Types of locks in MySQL

Granularity: Table locks (coarse, low overhead) and row locks (fine‑grained, higher overhead).

Exclusivity: Shared locks allow concurrent reads; exclusive locks block other reads and writes.

Concurrency models: Pessimistic locking assumes conflicts and locks data; optimistic locking assumes no conflict and uses version columns to detect concurrent updates.

17. MySQL log types

Binary log (binlog) – records write operations for replication.

Redo log – ensures transaction durability and recovery.

Undo log – stores previous row versions for rollback.

18. MySQL master‑slave replication process

Master writes changes to the binary log.

Slave reads the binary log and writes to its relay log.

Slave replays the relay log to apply changes.

19. Practical SQL optimization experience

Enable the slow‑query log to locate problematic statements.

Analyze root causes such as inappropriate data types, missing indexes, or inefficient queries.

Apply solutions: choose the right storage engine, design tables with reasonable column counts, use appropriate data types, avoid SELECT *, limit IN list size, prefer joins over subqueries, use UNION instead of temporary tables, add necessary indexes, and verify effectiveness with EXPLAIN.

If the workload is still heavy, consider read‑write splitting with a master‑slave cluster.

Source: blog.csdn.net/qq_17462303/article/details/135560797

Backend Technical Community Invitation

Build a high‑quality technical exchange community; developers, recruiters, and anyone interested in sharing job referrals are welcome.

Maintain civil discussion focusing on technical exchange , job referrals , and industry topics .

Advertisements are prohibited; do not trust private messages to avoid scams.