MySQL (and InnoDB)

MySQL has been in this corpus only as the thing vitess shards — every claim about it arrived through planetscale‘s sharding layer. This page states what is being sharded, from the MySQL 8.4 Reference Manual (T1, first-party).

The engine is where the substance is. InnoDB is the default storage engine: a CREATE TABLE with no ENGINE clause produces an InnoDB table, so “MySQL semantics” in practice means InnoDB semantics.

What InnoDB provides

• Transactions on the ACID model — “Its DML operations follow the ACID model, with transactions featuring commit, rollback, and crash-recovery capabilities to protect user data.”
• Row-level locking and consistent reads — “Row-level locking and Oracle-style consistent reads increase multi-user concurrency and performance.” That is MVCC, arrived at from the same direction as PostgreSQL and implemented differently: InnoDB keeps old row versions in an undo log, so reads reconstruct a version rather than reading a separate row.
• Clustered index on the primary key — “InnoDB tables arrange your data on disk to optimize queries based on primary keys. Each InnoDB table has a primary key index called the clustered index that organizes the data to minimize I/O for primary key lookups.” Table data lives in the primary key index, and every secondary index entry carries the primary key, not a row pointer.
• Foreign keys — checked on insert, update and delete.
• Crash recovery, data encryption, and a 64TB storage limit.

Why the clustered index matters to this spoke

cross-shard-queries describes what happens when a query cannot be routed by the shard key. InnoDB’s clustered index is the reason the shard key and the primary key tend to be the same decision: a lookup by primary key is one index traversal, and anything else is a secondary index lookup followed by a primary key lookup. So the choice that determines a query’s cost on one node is also the choice that determines whether it can be routed on a fleet. vitess‘s vindexes are that same decision made a second time, one level up.

Open here: the WAL/redo log and the double-write buffer, buffer-pool sizing, and the replication model planetscale-768-servers depends on.