Databases
Intermediate 12 min read Databases

MySQL and MariaDB

The web's default engine, made fast and survivable

MySQL and its fork MariaDB power the LAMP stack, replicas, and much of the web. InnoDB's clustered index, adaptive hash index, and binlog/log-based replication define how they behave.

InnoDBclustered indexbinlogreplicationMySQLMariaDB
Prerequisites: Relational Database Internals
Before we start

What you will learn

  • Contrast InnoDB clustering with Postgres heap tables
  • Explain binary-log replication and its lag
  • Choose between MySQL and MariaDB for a project
Introduction

What is this?

MySQL stores rows in the InnoDB engine: the primary key index IS the table (a clustered index), secondary indexes point at PK values, the buffer pool caches hot index pages, and binary logs replicate committed changes to replicas and power point-in-time recovery.

Motivation

Why do you need it?

  • MySQL is still the most deployed relational database on the web, especially for read-heavy replicas.
  • Understanding the clustered index explains why PRIMARY KEY design decides everything downstream.
  • Binlog-based replication is the backbone of most read scaling and a gateway to CDC.
Think about it

Mental model

Imagine

A warehouse where the shelf order IS the catalog

Think of

Clustered index in InnoDB

Rows are physically sorted by primary key, so point lookups by PK are single seeks while secondary-index lookups cost an extra hop back to the cluster.

Mechanics

How it works

  • InnoDB keeps rows in B+tree leaves ordered by the clustered index (usually the PRIMARY KEY).
  • Secondary indexes store the PK value and need a second seek into the clustered index.
  • The buffer pool caches index pages; the adaptive hash index accelerates hot key lookups.
  • Writes go to the redo log (InnoDB) first, then pages; commits flush to the binlog ordered by commit timestamps across the cluster.
  • Replicas apply the binlog stream asynchronously, so replica lag is a first-class operational concern.

Clustered vs secondary index traversal.

Query
Secondary B+tree
Clustered B+tree
InnoDB buffer pool
Row set
Hands-on

Examples

Cluster-safe key design

Cluster-safe key design
-- Natural key as PK sorts rows by e-mail (insertion cost, fragmentation).
CREATE TABLE users(email VARCHAR(255) PRIMARY KEY, name TEXT);
 
-- Surrogate sequential PK keeps inserts append-only and clusters well.
CREATE TABLE users(id BIGINT AUTO_INCREMENT PRIMARY KEY, email VARCHAR(255) UNIQUE, name TEXT);

Ordered inserts skip random page splits, so a surrogate PK usually out-performs a natural key in InnoDB.

Real world

In production

  • A read-heavy service runs one primary and three replicas; the app routes reads by shard hash.
  • A nightly ETL reads a replica, keeping the primary's binlog and quarterly reports from colliding.
  • A CDC pipeline tails the binlog (via Debezium) instead of polling, keeping an event log of every row change.
Guidelines

Best practices

  • Design PRIMARY KEY first; every secondary index inherits its size and clustering order.
  • Keep transactions short; long ones delay binlog application and purge.
  • Monitor replica lag with SHOW REPLICA STATUS resetting SLA alerts.
Watch out

Common mistakes

  • Using UUID strings as primary keys, fragmenting pages and bloating every secondary index.
  • Running writes directly against a replica expecting them to propagate back.
  • Relying on MyISAM (table locks, no crash safety) for anything where InnoDB is the obvious default.
When things break

Debugging tips

  • Use EXPLAIN to see which index the optimizer chose and whether you are causing a filesort.
  • Check buffer pool hit ratio via SHOW GLOBAL STATUS LIKE 'Innodb_buffer_pool_read%'.
  • When replica lag mounts, look for a long-running primary transaction or a replica running wrong indexes.
Interviews

Interview checkpoint

Test yourself

Quick quiz

0/2
1

In InnoDB, a row without an explicit PRIMARY KEY is stored in…

2

MySQL binary logs are used both for replication and point-in-time recovery.

Go deeper

Official documentation

MySQL reference manual
docs
MySQL replication
docs
MariaDB knowledge base
docs