Essential MySQL Interview Guide: Core Concepts, Queries, and Best Practices

Introduction

Senior ByteDance interviewer shares MySQL interview knowledge from the interviewer's viewpoint, aiming to help candidates understand what interviewers look for.

Basic Concepts

MySQL is a traditional relational database used mainly in OLTP scenarios. OLTP handles daily transaction processing, while OLAP is used for analytical workloads such as reporting.

Normalization

First normal form (1NF) requires atomic columns. Second normal form (2NF) requires full functional dependency on the primary key. Third normal form (3NF) eliminates transitive dependencies among non‑key attributes.

SQL Language Categories

DML (Data Manipulation Language) includes SELECT, INSERT, UPDATE, DELETE. DDL (Data Definition Language) handles schema changes like CREATE and ALTER. DCL (Data Control Language) manages permissions such as GRANT and REVOKE.

Data Types: VARCHAR vs CHAR

CHAR stores fixed‑length strings, always occupying the declared size. VARCHAR stores variable‑length strings up to the defined limit, saving space but adding a length byte and potential fragmentation.

Storage Engines

InnoDB provides ACID transactions, row‑level locking, and foreign‑key support. MyISAM (formerly MyIASM) lacks transaction support. MEMORY keeps data in RAM for fast access but is not durable.

ACID Properties

Atomicity, Consistency, Isolation, and Durability ensure reliable transaction processing; consistency is the goal, while the other three are mechanisms.

Keys

A primary key uniquely identifies each row. A foreign key references a primary key in another table to maintain referential integrity.

Monitoring Queries

Use SHOW PROCESSLIST to view currently executing threads; regular users see only their own sessions unless granted higher privileges.

Next Topics

The upcoming session will explore MySQL internals in depth.