Concurrency Control

Two-Phase Locking (Deadlock Detection)

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.

Indexes

B+Tree Hash Table R-Tree

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

Nested Loop Join Hash Join

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.

Data Model

Relational

Storage Architecture

Disk-oriented

Compression

Naïve (Page-Level)

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.

Checkpoints

Fuzzy

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.

Query Interface

SQL Stored Procedures

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

System Architecture

Shared-Nothing

Foreign Keys

Supported

The build-in MySQL natively supports foreign key constraints.

Query Execution

Tuple-at-a-Time Model

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