Databases & Storage Mind Map

Every databases & storage visualization on this site on one map. The branches are the main ideas. Each leaf is a page; the grey line under it is the one thing to remember about it. Click a branch to focus on it, click a page to open it.

Recall mode hides the page names. Read the hint, say the name, then click the "?" to check.

1. How the map is organized

Overview of the databases & storage mind map: Schema and storage, Indexes, Running a query, Cost-based optimizer, Query rewrites, Transactions, Apps and connections, MongoDB. — The map at a glance: 8 branches, 41 pages.

Follow a piece of data from the table design to the client, and ask where a page sits:

Where is the data kept? Table layout and file format: schema and storage. Extra structures to find rows fast: indexes.
What happens to one SQL statement? The steps it runs through are running a query. How the plan is chosen is the optimizer, and changing the query itself first is a rewrite.
What if many users or a crash are involved? That is transactions. Opening and using connections from the app is apps and connections.
Is it MongoDB? The document database has its own branch, from one command to shards.

2. The branches side by side

Branch	Question it answers	Main cost
Schema and storage	How are rows laid out?	pages read and written
Indexes	How do I find a row fast?	extra writes, page splits
Running a query	What steps does a statement take?	rows joined, sorted, grouped
Cost-based optimizer	Which plan is cheapest?	bad estimates, bad plans
Query rewrites	Can the query be simpler?	planning time only
Transactions	Is it correct with many users?	locks, waits, log writes
Apps and connections	How does the app talk to it?	connection setup, round trips
MongoDB	Same ideas in a document store?	scatter-gather across shards

3. Five facts to remember

A B+ tree index finds a row in about 3–4 page reads, even for millions of rows.
In InnoDB a secondary index stores the primary key, so a non-covering lookup searches two trees.
The optimizer picks a plan from row estimates; a wrong estimate is the usual cause of a slow plan.
MVCC lets readers see a snapshot, so readers do not block writers.
Write-ahead logging writes the log record to disk before the data page, so a crash can be redone.

See all databases & storage pages in the index, or the other mind maps.