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.

Logging[03][15]

To ensure data durability, ClearDB leverages durable storage as well as MySQL transaction log.

Query Execution[16]

MySQL follows an iterative processing mode and subqueries are optimized with materialization by storing subquery result in a temporary table

Query Interface[17]

ClearDB MySQL natively supports SQL and stored procedures for query executions.

Storage Architecture[18][03]

On a higher level, ClearDB uses cloud-based durable storage. On a lower level, ClearDB MySQL uses InnoDB as the default storage engine and supports multiple other storage engines: MyISAM, Memory (Heap Engine), Merge, Archive, Federated, and NDB.

System Architecture