Checkpoints[05]

It uses both Fuzzy and Sharp checkpoint. It takes a sharp checkpoint when the database is performing shut down and uses fuzzy checkpoint during normal database operations.

Compression[06][07]

InnoDB supports multiple compression algorithms which include Zlib and LZ4. When writing a page to disk, data were first compressed and then written to disk. Then empty blocks will be released by hole punching mechanism.

Concurrency Control[08][09]

MySQL uses a combination of Multiversion Concurrency Control (MVCC) and row-level locking to achieve better concurrency. Old versions of rows are stored in a rollback segment which could later be used to rollback changes. Rollback segments are partitioned into the insert and update undo logs for different types of rollbacks.

Data Model

Foreign Keys[10]

The build-in MySQL natively supports foreign key constraints.

Indexes[11]

MySQL support multiple types of indexes. Most MySQL tables stored on disks use B-trees. Spatial data uses R-tree for indexes. MySQL also uses hash indexes for memory tables.

Joins[12][13][14]

MySQL employs both nested loop join as well as hash join algorithms. Nested loop joins are used by default and hash join was used when the join has an equi-join condition and no indexes were used. Block nested loop, as well as Batched Key Access Joins, are used to improve join performance.

Query Interface

Storage Architecture

System Architecture