Lesson 147
Database Indexing & Storage
Pages, B+-Trees & the Query Planner
1:00How databases store rows in pages, build B+-tree and hash indexes, choose between clustered and secondary indexes, and use a query optimizer to pick the cheapest execution plan.
By the end, you can
- Explain why databases read and write data a whole page at a time.
- Describe how the buffer pool reduces disk I/O for hot pages.
- Calculate the number of page reads for a full table scan versus a B+-tree descent.
- Explain why a B+-tree (page-sized nodes, high fan-out) is shallower than a binary search tree for the same data set.
- Trace a B+-tree point lookup and range scan, including the linked-leaf traversal.
- Distinguish hash indexes from B+-tree indexes and state which query types each supports.
- Distinguish clustered and secondary indexes, and explain the "double hop" cost of a secondary index lookup.
- Identify the write overhead that indexes add to INSERT, UPDATE, and DELETE.
- Explain what the query optimizer does and why stale statistics can lead to poor plans.
- Choose the appropriate join algorithm (nested-loop, hash, merge) for a given data shape.
Up next in Networking, Distributed Systems & Databases
