跳到主要內容

資料結構

資料怎麼排,決定了哪些操作便宜

主題 · B-tree

B-tree

每個節點放很多個鍵,樹因此又矮又胖:存在磁碟上時,每往下一層就是一次讀取,幾十億筆資料也只要三、四層。

每個節點最多幾個鍵
6102035712172530
正在讀的節點讀過的節點分裂/往上推的鍵插入或找到

每個節點放好幾個排好序的鍵,鍵之間的空隙各指向一個子節點。節點滿了就從中間分裂、把中間的鍵往上推;只有根分裂時樹才長高。試試把「每個節點最多幾個鍵」調大,樹會變得更矮。

鍵
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
3357759152436912151821242730394551333639424548515463695760636669728187757881848790
正在讀的節點讀過的節點已輸出的鍵→ 葉子之間的鏈結

讀內部節點 [33, 57, 75]:裡面只有導航用的鍵。往 20 所在的子節點走下去。

同樣的鍵、同樣的範圍 20–60
樹輸出幾個鍵讀幾個節點上下層移動
B-tree141120
B+ tree14102
亮起來的是這一步執行的程式碼
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 On = 500n = 5,000n = 50,000成長倍數:實測(理論)
讀幾個節點O(log n)4.66.67.6×1.6 (×1.7)
比較幾個鍵O(log n)8.912.716.9×1.9 (×1.7)

n 變成一百倍,樹高從 5 層變 8 層。真實資料庫每個節點放幾百個鍵,層數更少。

和其他做法比

1,000,000 個鍵:最少幾層1,000,000,000 個鍵:最少幾層
平衡的二元樹(每個節點 2 個子節點)2030
B-tree,每個節點 100 個子節點45
B-tree,每個節點 500 個子節點34

節點全滿時的最少層數,算法是 ⌈log(n+1) ÷ log(子節點數)⌉。存在磁碟上時每一層是一次讀取:十億個鍵,二元樹要讀 30 次,B-tree 只要 4 次,而且上面幾層通常已經在記憶體裡。一個 4 KB 或 16 KB 的磁碟頁剛好放得下幾百個鍵,這就是 B-tree 節點的大小。

範圍內 10 個鍵範圍內 100 個鍵範圍內 1,000 個鍵
B-tree:讀幾個節點8.941.8373.1
B+ tree:讀幾個節點9.239.6348.1
B-tree:上下層移動15.881.6744.1
B+ tree:上下層移動555

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 的好處不明顯;它是為「讀一次很貴」的儲存而設計的。