MySQL Interview Questions and Answers: Normal Forms, Auto‑Increment IDs, ACID, Data Types, Joins, Indexes, Transactions, Engines, Locks, and Optimization

164. What are the three normal forms?

First Normal Form (1NF): each column contains atomic, indivisible values.

Second Normal Form (2NF): every non‑key attribute is fully dependent on the whole primary key.

Third Normal Form (3NF): no non‑key attribute depends on another non‑key attribute.

165. In an auto‑increment table with 7 rows, after deleting the last two rows and restarting MySQL, what ID will the next inserted row have?

If the table uses MyISAM, the ID will be 18.

If the table uses InnoDB, the ID will be 15 because InnoDB stores the maximum auto‑increment value only in memory, which is lost after a restart.

166. How to obtain the current MySQL version?

Execute SELECT VERSION() to retrieve the database version.

167. What does ACID stand for?

Atomicity: All operations in a transaction are completed entirely or not at all; failures trigger a rollback.

Consistency: Database integrity constraints are preserved before and after a transaction.

Isolation: Concurrent transactions do not interfere with each other; isolation levels include Read Uncommitted, Read Committed, Repeatable Read, and Serializable.

Durability: Once a transaction commits, its changes persist even after system failures.

168. Difference between CHAR and VARCHAR?

CHAR(n) is a fixed‑length type; values are padded to n bytes. VARCHAR(n) is variable‑length, storing the actual length plus a length byte. CHAR offers higher efficiency but wastes space; VARCHAR saves space but may be slightly slower.

169. Difference between FLOAT and DOUBLE?

FLOAT stores up to 8 decimal digits and occupies 4 bytes.

DOUBLE stores up to 16 decimal digits and occupies 8 bytes.

170. Differences among INNER JOIN, LEFT JOIN, and RIGHT JOIN?

INNER JOIN returns only rows with matching keys in both tables. LEFT JOIN returns all rows from the left table and matching rows from the right table (nulls for non‑matches). RIGHT JOIN does the opposite, returning all rows from the right table.

171. How are MySQL indexes implemented?

Indexes are data structures that enable fast lookups; most storage engines implement them using B+ trees, providing logarithmic search performance.

172. How to verify whether an index meets the query needs?

Use EXPLAIN SELECT * FROM table WHERE type=1 to view the execution plan and assess index usage.

173. What are MySQL transaction isolation levels?

Set in the MySQL configuration (e.g., transaction-isolation = REPEATABLE-READ). Available levels: READ‑UNCOMMITTED, READ‑COMMITTED, REPEATABLE‑READ (default), and SERIALIZABLE, each providing increasing protection against dirty reads, non‑repeatable reads, and phantom reads.

174. Common MySQL storage engines?

InnoDB: Supports ACID transactions, row‑level locking, and foreign keys; suitable for large, concurrent workloads but slower to start and does not store row counts.

MyISAM: Default engine without transaction support or row‑level locks; stores row counts for fast SELECT COUNT(*) and is preferable when reads dominate writes.

175. Difference between row locks and table locks?

Table‑level lock: low overhead, fast acquisition, but coarse granularity leading to higher contention.

Row‑level lock: higher overhead, slower acquisition, but fine granularity reduces contention and improves concurrency.

176. Optimistic lock vs pessimistic lock?

Optimistic lock: No lock is taken when reading; on update, the version field is checked to ensure no concurrent modification.

Pessimistic lock: A lock is acquired when reading, preventing other transactions from modifying the data until the lock is released.

Implementation typically adds a version column that increments on each successful update.

177. Common MySQL troubleshooting methods?

Use SHOW PROCESSLIST to view active connections.

Run EXPLAIN to analyze query execution plans.

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

178. MySQL performance optimization tips?

Create indexes on columns used in search conditions.

Avoid SELECT *; specify only needed columns.

Apply vertical partitioning or sharding to split large tables.

Choose the appropriate storage engine for the workload.

(End)