B-tree
每個節點放很多個鍵,樹因此又矮又胖:存在磁碟上時,每往下一層就是一次讀取,幾十億筆資料也只要三、四層。
每個節點最多幾個鍵
正在讀的節點讀過的節點分裂/往上推的鍵插入或找到
每個節點放好幾個排好序的鍵,鍵之間的空隙各指向一個子節點。節點滿了就從中間分裂、把中間的鍵往上推;只有根分裂時樹才長高。試試把「每個節點最多幾個鍵」調大,樹會變得更矮。
鍵
10
節點
5
樹高(最多讀幾個節點)
2
這次讀了幾個節點
–
亮起來的是這一步執行的程式碼
class BNode { keys: number[] = []; children: BNode[] = [];} type Split = { median: number; right: BNode }; class BTree { root = new BNode(); constructor(private maxKeys: number) {} search(key: number): boolean { let node = this.root; while (true) { let i = 0; while (i < node.keys.length && key > node.keys[i]) i++; if (node.keys[i] === key) return true; if (node.children.length === 0) return false; node = node.children[i]; } } insert(key: number): void { const split = this.insertAt(this.root, key); if (split) { const root = new BNode(); root.keys = [split.median]; root.children = [this.root, split.right]; this.root = root; } } private insertAt(node: BNode, key: number): Split | null { let i = 0; while (i < node.keys.length && key > node.keys[i]) i++; if (node.keys[i] === key) return null; if (node.children.length === 0) { node.keys.splice(i, 0, key); } else { const split = this.insertAt(node.children[i], key); if (!split) return null; node.keys.splice(i, 0, split.median); node.children.splice(i + 1, 0, split.right); } if (node.keys.length <= this.maxKeys) return null; const mid = Math.floor(node.keys.length / 2); const right = new BNode(); right.keys = node.keys.splice(mid + 1); if (node.children.length) right.children = node.children.splice(mid + 1); return { median: node.keys.pop()!, right }; } // In-order walk of only the subtrees that can hold keys in [lo, hi]. rangeScan(lo: number, hi: number): number[] { const out: number[] = []; const visit = (node: BNode): boolean => { // false once past hi const k = node.keys.length; for (let i = 0; i <= k; i++) { const above = i === 0 ? -Infinity : node.keys[i - 1]; const below = i === k ? Infinity : node.keys[i]; if (node.children.length && lo < below && above < hi) { if (!visit(node.children[i])) return false; } if (i === k) break; if (node.keys[i] > hi) return false; if (node.keys[i] >= lo) out.push(node.keys[i]); } return true; }; visit(this.root); return out; }} // B+ tree: every key lives in a leaf, and the leaves are linked.class BPNode { keys: number[] = []; children: BPNode[] = []; next: BPNode | null = null;} class BPlusTree { root = new BPNode(); constructor(private maxKeys: number) {} insert(key: number): void { const path: Array<[BPNode, number]> = []; let node = this.root; while (node.children.length) { let i = 0; while (i < node.keys.length && key >= node.keys[i]) i++; path.push([node, i]); node = node.children[i]; } let at = 0; while (at < node.keys.length && node.keys[at] < key) at++; if (node.keys[at] === key) return; node.keys.splice(at, 0, key); if (node.keys.length <= this.maxKeys) return; const right = new BPNode(); right.keys = node.keys.splice(Math.floor(node.keys.length / 2)); right.next = node.next; node.next = right; let separator = right.keys[0]; // copied up; it stays in the leaf let left = node; let newChild = right; while (path.length) { const [parent, i] = path.pop()!; parent.keys.splice(i, 0, separator); parent.children.splice(i + 1, 0, newChild); if (parent.keys.length <= this.maxKeys) return; const mid = Math.floor(parent.keys.length / 2); const sibling = new BPNode(); sibling.keys = parent.keys.splice(mid + 1); sibling.children = parent.children.splice(mid + 1); separator = parent.keys.pop()!; // moved up, as in a B-tree left = parent; newChild = sibling; } const root = new BPNode(); root.keys = [separator]; root.children = [left, newChild]; this.root = root; } // Down once to the leaf holding lo, then along the leaf chain. rangeScan(lo: number, hi: number): number[] { let node = this.root; while (node.children.length) { let i = 0; while (i < node.keys.length && lo >= node.keys[i]) i++; node = node.children[i]; } const out: number[] = []; for (let leaf: BPNode | null = node; leaf; leaf = leaf.next) { for (const key of leaf.keys) { if (key > hi) return out; if (key >= lo) out.push(key); } } return out; }}B+ tree:範圍查詢
資料庫的索引幾乎都用 B+ tree:所有的鍵(和資料)只放在葉子,內部節點只放導航用的複本,葉子由左到右串成一條鏈。下面把同一組鍵放進兩種樹,用同一個範圍各掃一次。
樹
同一組 30 個鍵,插入順序
–
1/25
正在讀的節點讀過的節點已輸出的鍵→ 葉子之間的鏈結
讀內部節點 [33, 57, 75]:裡面只有導航用的鍵。往 20 所在的子節點走下去。
| 樹 | 輸出幾個鍵 | 讀幾個節點 | 上下層移動 |
|---|---|---|---|
| B-tree | 14 | 11 | 20 |
| B+ tree | 14 | 10 | 2 |
亮起來的是這一步執行的程式碼
class BNode { keys: number[] = []; children: BNode[] = [];} type Split = { median: number; right: BNode }; class BTree { root = new BNode(); constructor(private maxKeys: number) {} search(key: number): boolean { let node = this.root; while (true) { let i = 0; while (i < node.keys.length && key > node.keys[i]) i++; if (node.keys[i] === key) return true; if (node.children.length === 0) return false; node = node.children[i]; } } insert(key: number): void { const split = this.insertAt(this.root, key); if (split) { const root = new BNode(); root.keys = [split.median]; root.children = [this.root, split.right]; this.root = root; } } private insertAt(node: BNode, key: number): Split | null { let i = 0; while (i < node.keys.length && key > node.keys[i]) i++; if (node.keys[i] === key) return null; if (node.children.length === 0) { node.keys.splice(i, 0, key); } else { const split = this.insertAt(node.children[i], key); if (!split) return null; node.keys.splice(i, 0, split.median); node.children.splice(i + 1, 0, split.right); } if (node.keys.length <= this.maxKeys) return null; const mid = Math.floor(node.keys.length / 2); const right = new BNode(); right.keys = node.keys.splice(mid + 1); if (node.children.length) right.children = node.children.splice(mid + 1); return { median: node.keys.pop()!, right }; } // In-order walk of only the subtrees that can hold keys in [lo, hi]. rangeScan(lo: number, hi: number): number[] { const out: number[] = []; const visit = (node: BNode): boolean => { // false once past hi const k = node.keys.length; for (let i = 0; i <= k; i++) { const above = i === 0 ? -Infinity : node.keys[i - 1]; const below = i === k ? Infinity : node.keys[i]; if (node.children.length && lo < below && above < hi) { if (!visit(node.children[i])) return false; } if (i === k) break; if (node.keys[i] > hi) return false; if (node.keys[i] >= lo) out.push(node.keys[i]); } return true; }; visit(this.root); return out; }} // B+ tree: every key lives in a leaf, and the leaves are linked.class BPNode { keys: number[] = []; children: BPNode[] = []; next: BPNode | null = null;} class BPlusTree { root = new BPNode(); constructor(private maxKeys: number) {} insert(key: number): void { const path: Array<[BPNode, number]> = []; let node = this.root; while (node.children.length) { let i = 0; while (i < node.keys.length && key >= node.keys[i]) i++; path.push([node, i]); node = node.children[i]; } let at = 0; while (at < node.keys.length && node.keys[at] < key) at++; if (node.keys[at] === key) return; node.keys.splice(at, 0, key); if (node.keys.length <= this.maxKeys) return; const right = new BPNode(); right.keys = node.keys.splice(Math.floor(node.keys.length / 2)); right.next = node.next; node.next = right; let separator = right.keys[0]; // copied up; it stays in the leaf let left = node; let newChild = right; while (path.length) { const [parent, i] = path.pop()!; parent.keys.splice(i, 0, separator); parent.children.splice(i + 1, 0, newChild); if (parent.keys.length <= this.maxKeys) return; const mid = Math.floor(parent.keys.length / 2); const sibling = new BPNode(); sibling.keys = parent.keys.splice(mid + 1); sibling.children = parent.children.splice(mid + 1); separator = parent.keys.pop()!; // moved up, as in a B-tree left = parent; newChild = sibling; } const root = new BPNode(); root.keys = [separator]; root.children = [left, newChild]; this.root = root; } // Down once to the leaf holding lo, then along the leaf chain. rangeScan(lo: number, hi: number): number[] { let node = this.root; while (node.children.length) { let i = 0; while (i < node.keys.length && lo >= node.keys[i]) i++; node = node.children[i]; } const out: number[] = []; for (let leaf: BPNode | null = node; leaf; leaf = leaf.next) { for (const key of leaf.keys) { if (key > hi) return out; if (key >= lo) out.push(key); } } return out; }}模型假設與範圍
- Big O 與實測步數以頁面列出的基本操作為單位;視覺化的快照、畫圖、程式高亮和輸出成本另計,不是執行時間 benchmark。
什麼時候用
- 資料放在磁碟或 SSD 上,每讀一次都很貴:把一整個磁碟頁塞滿鍵,讓樹只有三、四層。
- 需要排序和範圍查詢(「這個月所有訂單」):B+ tree 的葉子串在一起,找到起點就能一路往右讀。
和其他主題的關係
時間與空間複雜度(Big O)
| 操作 | 平均 | 最差 |
|---|---|---|
| 查找 讀 log_m n 個節點;節點內用二分搜尋時總比較次數是 O(log n) | O(log n) | O(log n) |
| 插入 分裂只會沿著一條路徑往上 | O(log n) | O(log n) |
| 刪除 節點太空時向兄弟借或合併(這裡沒有示範) | O(log n) | O(log n) |
| 範圍查詢(B+ tree) 找到起點後沿著串起來的葉子往右讀 k 筆 | O(log n + k) | O(log n + k) |
空間:O(n),節點通常半滿到全滿
Big O 實測:n 變大時步數怎麼長
數的是:每個節點最多 4 個鍵、隨機順序插入;每次查找讀的節點數和比較次數(取 2,000 個鍵平均)
| Big O | n = 500 | n = 5,000 | n = 50,000 | 成長倍數:實測(理論) | |
|---|---|---|---|---|---|
| 讀幾個節點 | O(log n) | 4.6 | 6.6 | 7.6 | ×1.6 (×1.7) |
| 比較幾個鍵 | O(log n) | 8.9 | 12.7 | 16.9 | ×1.9 (×1.7) |
n 變成一百倍,樹高從 5 層變 8 層。真實資料庫每個節點放幾百個鍵,層數更少。
和其他做法比
| 1,000,000 個鍵:最少幾層 | 1,000,000,000 個鍵:最少幾層 | |
|---|---|---|
| 平衡的二元樹(每個節點 2 個子節點) | 20 | 30 |
| B-tree,每個節點 100 個子節點 | 4 | 5 |
| B-tree,每個節點 500 個子節點 | 3 | 4 |
節點全滿時的最少層數,算法是 ⌈log(n+1) ÷ log(子節點數)⌉。存在磁碟上時每一層是一次讀取:十億個鍵,二元樹要讀 30 次,B-tree 只要 4 次,而且上面幾層通常已經在記憶體裡。一個 4 KB 或 16 KB 的磁碟頁剛好放得下幾百個鍵,這就是 B-tree 節點的大小。
| 範圍內 10 個鍵 | 範圍內 100 個鍵 | 範圍內 1,000 個鍵 | |
|---|---|---|---|
| B-tree:讀幾個節點 | 8.9 | 41.8 | 373.1 |
| B+ tree:讀幾個節點 | 9.2 | 39.6 | 348.1 |
| B-tree:上下層移動 | 15.8 | 81.6 | 744.1 |
| B+ tree:上下層移動 | 5 | 5 | 5 |
2,000 個鍵隨機插入、每個節點最多 4 個鍵,從 20 個平均分散的起點各掃一次取平均。兩種樹讀的節點數差不多,差別在路線:B-tree 每讀完一棵子樹就要回到父節點,上下移動跟著範圍變大;B+ tree 只往下走一次(5 步),之後沿著葉子鏈結一路往右,在磁碟上幾乎是循序讀取。
真實世界裡的它
- InnoDB 與 PostgreSQL 的 B-tree 索引採 B+ tree 類型的設計;PostgreSQL 另有 Hash、GIN 等索引。SQLite 則要區分 table B-tree 與 index B-tree:後者的內部節點也會存索引鍵的 payload,不能一概說資料只在葉子。
- NTFS、Btrfs、APFS 用 B-tree 類的結構管理檔案或中繼資料。ext4 的索引目錄使用以雜湊鍵導航的 HTree,和這裡按原始鍵排序的 B-tree 不同。
取捨與陷阱
- 用隨機的 UUID(Universally Unique Identifier)當主鍵,新資料會插進樹的各處:頁一直分裂、快取命中率低。遞增的鍵只會寫最右邊那頁,快很多。
- 每次寫入都可能改動一整頁、還要寫日誌,寫入量遠大於資料本身;寫入特別多時,LSM tree(Log-Structured Merge tree)常常更合適。
- 記憶體裡資料量小時,B-tree 的好處不明顯;它是為「讀一次很貴」的儲存而設計的。