Master MySQL DDL: Online DDL Algorithms, Version Differences, and Production Indexing Tips

Interview Focus Points

DDL Mechanism Understanding : Interviewers expect you to explain MySQL DDL execution and Online DDL principles, not just answer yes/no.

Version Differences : Know the major changes in DDL handling for MySQL 5.5, 5.6, and 8.0.

Production Practice : Be able to describe the correct way to add indexes online, avoid business impact, and handle emergencies.

Core Answers by MySQL Version

MySQL 5.5 and below : Uses COPY algorithm, which locks the whole table (read/write prohibited) during the operation.

MySQL 5.6+ : Default is Online DDL (INPLACE) – no table lock, supports concurrent reads/writes, only a short MDL lock at start and end.

MySQL 8.0+ : Supports Instant DDL for some operations (seconds‑level, almost no impact). Adding indexes still requires INPLACE.

Deep Analysis

1. Three DDL Algorithms

COPY : Creates a temporary table, copies all data, then swaps tables. The whole process locks the table and can take hours on large tables.

INPLACE (Online DDL, introduced in 5.6): Operates on the original table without copying data. Reads and writes are allowed during index build; only a brief MDL lock is taken at the beginning and end.

INSTANT (MySQL 8.0): Modifies only metadata (.frm or data dictionary). Changes complete in seconds, but currently does not support adding or dropping indexes.

2. Online DDL Execution Process

Phase 1 – Initialization : Acquires an MDL lock, then quickly downgrades to a shared lock. This step lasts milliseconds.

Phase 2 – Execution : The longest phase; data is built while allowing concurrent DML. Incremental changes are recorded in the Online Log.

Phase 3 – Commit : Re‑acquires an exclusive MDL lock, applies the Online Log to the new index, and updates metadata. Usually very short.

Potential Risk : If a long‑running query holds an MDL read lock during Phase 3, the DDL will wait, causing a lock‑wait chain and temporary service jitter.

3. Index Types and Locking

Ordinary secondary index – INPLACE, no lock.

Primary key addition – INPLACE, rebuilds table but still non‑blocking.

Primary key deletion – same as addition.

Full‑text index – INPLACE, no lock, but initial build may be slower.

Spatial index – INPLACE, supported from MySQL 5.7, no lock.

4. Production Best Practices

-- Recommended syntax: explicitly specify Online DDL parameters
ALTER TABLE user
  ADD INDEX idx_create_time (create_time)
  ALGORITHM=INPLACE,
  LOCK=NONE;

-- Parameter explanations:
-- ALGORITHM=INPLACE – use online DDL, no full‑table copy.
-- LOCK=NONE – avoid any table lock, allow concurrent reads/writes.
-- If INPLACE is not supported, the statement will error instead of falling back to COPY.

Operational Recommendations :

Avoid peak traffic periods; Online DDL still consumes CPU and I/O.

For very large tables, consider pt-online-schema-change (Percona Toolkit) which creates a shadow table, copies data in batches, syncs increments via triggers, then swaps tables atomically.

Monitor MDL lock wait status before running DDL (e.g., query performance_schema.metadata_locks for PENDING locks).

Set appropriate timeouts to prevent long waits: LOCK_WAIT_TIMEOUT – limits MDL lock wait time. MAX_EXECUTION_TIME – caps DDL execution duration.

Check for Long Transactions :

-- View running transactions
SELECT * FROM information_schema.INNODB_TRX;

-- View pending MDL locks (MySQL 8.0+)
SELECT * FROM performance_schema.metadata_locks WHERE LOCK_STATUS='PENDING';

Interview Follow‑Up Questions

Impact of heavy writes during Online DDL : Online Log grows in memory, may trigger disk flushes; Phase 3 may take longer as more incremental data must be applied.

Why does adding an index sometimes block? : Usually due to MDL lock wait caused by a long‑running query holding a read lock.

Difference between ALGORITHM=INPLACE and LOCK=NONE : ALGORITHM=INPLACE selects the online algorithm; LOCK=NONE specifies that no table lock should be taken, ensuring full concurrency.

Can Instant DDL be used for adding indexes in 8.0? : No – Instant DDL currently supports only column addition/removal and default value changes; index operations still require INPLACE.

Common Interview Variants

"What are the options for adding an index to a huge MySQL table?"

"Explain the principle of Online DDL and its impact on production."

"How to safely add an index to a tens‑of‑billions‑row table in production?"

"What could cause a DDL operation to cause database jitter?"

Memory Mnemonics

Version Differences : 5.5 – lock table; 5.6 – online; 8.0 – partial instant.

Algorithm Evolution : COPY – full copy & lock; INPLACE – in‑place, non‑blocking; INSTANT – metadata‑only, seconds.

Production Operations : Avoid peak, check long transactions, use INPLACE + NONE as double insurance.

Summary

Since MySQL 5.6, InnoDB adds indexes by default using Online DDL with the INPLACE algorithm, which does not lock the table and allows concurrent reads/writes. However, a short MDL lock is taken at the start and end, so long transactions can still cause blocking. In production, explicitly specify ALGORITHM=INPLACE, LOCK=NONE, run during low‑traffic windows, and consider pt-online-schema-change for very large tables.