Essential MySQL Interview Guide: Normal Forms, Permissions, ACID, Indexes & More

Database Normalization

First Normal Form (1NF) requires each column to be atomic (no further division). Second Normal Form (2NF) requires every non‑key column to be fully dependent on the whole primary key, not on a part of it. Third Normal Form (3NF) requires non‑key columns to depend only on the primary key and not on other non‑key columns. Deviations should be justified by performance needs.

MySQL Permission Tables

Permission information is stored in the mysql system database and initialized by mysql_install_db. Key tables: user – global privileges db – database‑level privileges table_priv – table‑level privileges columns_priv – column‑level privileges host – host‑level filtering (not affected by GRANT / REVOKE)

ACID Transaction Properties

Atomicity : a transaction is indivisible; it either fully commits or has no effect.

Consistency : data remains valid according to all constraints before and after the transaction.

Isolation : concurrent transactions do not interfere with each other.

Durability : once committed, changes survive crashes.

Index Design Principles

Index columns used in WHERE clauses or join conditions.

Avoid indexing low‑cardinality columns.

Prefer short indexes; for long strings use prefix lengths.

Do not over‑index – each index adds storage and write overhead.

SQL Statement Categories

DDL (Data Definition Language): CREATE, DROP, ALTER – modify schema, views, indexes.

DQL (Data Query Language): SELECT – retrieve data.

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

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

Purpose of MySQL Sharding

Sharding splits a large database into multiple smaller databases and large tables into smaller tables, reducing query latency and improving manageability when data volume causes performance degradation.

Deadlock Definition and Mitigation

A deadlock occurs when two or more transactions hold locks needed by each other, forming a cycle.

Use a consistent lock acquisition order across applications.

Acquire all required locks within a single transaction.

For hot sections, consider higher‑level locks (e.g., table‑level) or distributed transaction locks.

Optimistic locking can also reduce deadlock likelihood.

Isolation Anomalies

Dirty Read : reading uncommitted changes from another transaction that later rolls back.

Non‑repeatable Read : the same query returns different rows because another transaction modified data.

Phantom Read : a re‑executed query sees a different number of rows due to inserts or deletes by another transaction.

View Characteristics

Columns may originate from multiple base tables; a view is a logical abstraction.

Views are virtual tables; creating or dropping a view does not affect underlying tables.

Updates to a view propagate to base tables, but updates are restricted when the view spans multiple tables.

Supported statements: CREATE VIEW, SHOW VIEW, DROP VIEW, ALTER VIEW.

SQL Execution Lifecycle

Application server opens a connection to the MySQL server.

The server receives the SQL request.

The SQL is parsed and an execution plan is generated.

Required data pages are read into memory and processed.

Results are sent back to the client over the same connection.

The connection is closed and resources are released.

Primary Key Choice: Auto‑Increment vs. UUID

In InnoDB the primary key is the clustered index. Sequential auto‑increment IDs keep inserts at the end of the B+‑tree, minimizing page splits and fragmentation. UUIDs are random, causing frequent page splits, higher write overhead, and reduced performance on large tables.

Handling MySQL CPU at 100%

Use top to confirm mysqld is the culprit.

Run SHOW PROCESSLIST to locate heavy queries.

Examine execution plans; add missing indexes or rewrite inefficient SQL.

If many connections cause the spike, limit the max connections.

Kill offending threads and monitor CPU after adjustments.

MySQL Master‑Slave Replication Benefits

Geographic data distribution and redundancy.

Read load balancing by directing reads to slaves.

High availability and failover capability.

Ability to test upgrades on a slave before promotion.

GTID (Global Transaction ID)

Unique identifier for each committed transaction, composed of UUID (server ID) + TID (transaction ID).

Introduced in MySQL 5.6 to simplify replication failover.

Allows easy identification of the originating server and ensures each transaction is applied only once.

Common MySQL Backup Tools

Logical backup : mysqldump, mydumper – produce SQL files per table.

Physical backup : file copy, xtrabackup – hot backup for InnoDB (requires table locks for MyISAM).

Replication can provide near‑real‑time backup.

Backup Planning Guidelines

Databases < 100 GB: use mysqldump during low‑traffic windows; perform daily full backups.

Databases ≥ 100 GB: prefer xtrabackup for faster, hot backups.

Typical schedule: weekly full backup plus daily incremental backups, all executed during off‑peak periods.