B-trees and database indexes (2024)
12 hours ago
- #b-tree
- #database-indexes
- #mysql-performance
- B-trees are foundational data structures in DBMS like MySQL and MongoDB, used for efficient data lookups via indexes.
- B-trees store ordered key/value pairs in nodes, with each node sized to match disk blocks for optimized persistent storage.
- B+trees, a variant used in MySQL, store values only in leaf nodes, have linked lists for in-order traversal, and allow more keys in inner nodes.
- MySQL's InnoDB engine stores table data in a B+tree with the primary key as the tree key; secondary indexes use additional B+trees.
- Choosing a sequential primary key (e.g., auto-incrementing integer) over random keys like UUIDs reduces I/O and improves insert/query performance.
- Primary key size matters: smaller keys (e.g., BIGINT vs. UUID) allow more keys per node, leading to shallower trees and faster lookups.
- InnoDB uses a buffer pool to cache pages in memory, reducing disk I/O, but minimizing page visits remains crucial for performance.