ADM INISTRIVIA Project #1 is due Fri Sept 27 th @ 11:59pm Homework #2 - PowerPoint PPT Presentation

07 Tree Indexes Part I Intro to Database Systems Andy Pavlo AP AP 15-445/15-645 Computer Science Fall 2019 Carnegie Mellon University

2 ADM INISTRIVIA Project #1 is due Fri Sept 27 th @ 11:59pm Homework #2 is due Mon Sept 30 th @ 11:59pm CMU 15-445/645 (Fall 2019)

3 DATA STRUCTURES Internal Meta-data Core Data Storage Temporary Data Structures Table Indexes CMU 15-445/645 (Fall 2019)

4 TABLE INDEXES A table index is a replica of a subset of a table's attributes that are organized and/or sorted for efficient access using a subset of those attributes. The DBMS ensures that the contents of the table and the index are logically in sync. CMU 15-445/645 (Fall 2019)

5 TABLE INDEXES It is the DBMS's job to figure out the best index(es) to use to execute each query. There is a trade-off on the number of indexes to create per database. → Storage Overhead → Maintenance Overhead CMU 15-445/645 (Fall 2019)

6 TODAY'S AGENDA B+Tree Overview Design Decisions Optimizations CMU 15-445/645 (Fall 2019)

7 B- TREE FAM ILY There is a specific data structure called a B-Tree . People also use the term to generally refer to a class of balanced tree data structures: → B-Tree (1971) → B+Tree (1973) → B*Tree (1977?) → B link -Tree (1981) CMU 15-445/645 (Fall 2019)

7 B- TREE FAM ILY There is a specific data structure called a B-Tree . People also use the term to generally refer to a class of balanced tree data structures: → B-Tree (1971) → B+Tree (1973) → B*Tree (1977?) → B link -Tree (1981) CMU 15-445/645 (Fall 2019)

8 B+ TREE A B+Tree is a self-balancing tree data structure that keeps data sorted and allows searches, sequential access, insertions, and deletions in O(log n) . → Generalization of a binary search tree in that a node can have more than two children. → Optimized for systems that read and write large blocks of data. CMU 15-445/645 (Fall 2019)

9 B+ TREE PROPERTIES A B+Tree is an M -way search tree with the following properties: → It is perfectly balanced (i.e., every leaf node is at the same depth). → Every node other than the root, is at least half-full M/2- 1 ≤ #keys ≤ M -1 → Every inner node with k keys has k+1 non-null children CMU 15-445/645 (Fall 2019)

10 B+ TREE EXAM PLE Inner Node 5 9 1 3 6 7 9 13 Leaf Nodes Sibling Pointers CMU 15-445/645 (Fall 2019)

10 B+ TREE EXAM PLE Inner Node <node*>|<key> 5 9 <5 <9 ≥9 1 3 6 7 9 13 Leaf Nodes Sibling Pointers <value>|<key> CMU 15-445/645 (Fall 2019)

11 NODES Every B+Tree node is comprised of an array of key/value pairs. → The keys are derived from the attributes(s) that the index is based on. → The values will differ based on whether the node is classified as inner nodes or leaf nodes. The arrays are (usually) kept in sorted key order. CMU 15-445/645 (Fall 2019)

12 B+ TREE LEAF NODES B+Tree Leaf Node Prev Next ¤ ¤ V1 • • • Kn K1 Vn CMU 15-445/645 (Fall 2019)

12 B+ TREE LEAF NODES B+Tree Leaf Node Prev Next PageID PageID ¤ ¤ V1 • • • Kn K1 Vn CMU 15-445/645 (Fall 2019)

12 B+ TREE LEAF NODES B+Tree Leaf Node Prev Next PageID PageID ¤ ¤ ¤ ¤ V1 • • • Kn K1 Vn Key+ Value CMU 15-445/645 (Fall 2019)

12 B+ TREE LEAF NODES B+Tree Leaf Node Level Slots Prev Next ¤ ¤ # # Sorted Keys K5 • • • Kn K1 K2 K3 K4 Values • • • ¤ ¤ ¤ ¤ ¤ ¤ CMU 15-445/645 (Fall 2019)

12 B+ TREE LEAF NODES B+Tree Leaf Node Level Slots Prev Next ¤ ¤ # # Sorted Keys K5 • • • Kn K1 K2 K3 K4 Values • • • ¤ ¤ ¤ ¤ ¤ ¤ CMU 15-445/645 (Fall 2019)

13 LEAF NODE VALUES Approach #1: Record Ids → A pointer to the location of the tuple that the index entry corresponds to. Approach #2: Tuple Data → The actual contents of the tuple is stored in the leaf node. → Secondary indexes have to store the record id as their values. CMU 15-445/645 (Fall 2019)

14 B- TREE VS. B+ TREE The original B-Tree from 1972 stored keys + values in all nodes in the tree. → More space efficient since each key only appears once in the tree. A B+Tree only stores values in leaf nodes. Inner nodes only guide the search process. CMU 15-445/645 (Fall 2019)

15 B+ TREE INSERT Find correct leaf node L . Put data entry into L in sorted order. If L has enough space, done! Otherwise, split L keys into L and a new node L2 → Redistribute entries evenly, copy up middle key. → Insert index entry pointing to L2 into parent of L . To split inner node, redistribute entries evenly, but push up middle key. Source: Chris Re CMU 15-445/645 (Fall 2019)

16 B+ TREE VISUALIZATIO N https://cmudb.io/btree Source: David Gales (Univ. of San Francisco) CMU 15-445/645 (Fall 2019)

17 B+ TREE DELETE Start at root, find leaf L where entry belongs. Remove the entry. If L is at least half-full, done! If L has only M/2-1 entries, → Try to re-distribute, borrowing from sibling (adjacent node with same parent as L ). → If re-distribution fails, merge L and sibling. If merge occurred, must delete entry (pointing to L or sibling) from parent of L . Source: Chris Re CMU 15-445/645 (Fall 2019)

18 B+ TREES IN PRACTICE Typical Fill-Factor: 67%. Typical Capacities: → Height 4: 1334 = 312,900,721 entries → Height 3: 1333 = 2,406,104 entries Pages per level: → Level 1 = 1 page = 8 KB → Level 2 = 134 pages = 1 MB → Level 3 = 17,956 pages = 140 MB CMU 15-445/645 (Fall 2019)

19 CLUSTERED INDEXES The table is stored in the sort order specified by the primary key. → Can be either heap- or index-organized storage. Some DBMSs always use a clustered index. → If a table doesn’t contain a primary key, the DBMS will automatically make a hidden row id primary key. Other DBMSs cannot use them at all. CMU 15-445/645 (Fall 2019)

20 SELECTIO N CONDITIO NS The DBMS can use a B+Tree index if the query provides any of the attributes of the search key. Example: Index on <a,b,c> → Supported: (a=5 AND b=3) → Supported: (b=3) . Not all DBMSs support this. For hash index, we must have all attributes in search key. CMU 15-445/645 (Fall 2019)

21 SELECTIO N CONDITIO NS Find Key=(A,B) A ≤ A B ≤ C A,C B,B C,C A,A A,B A,C B,A B,B B,C C,C C,D CMU 15-445/645 (Fall 2019)

21 SELECTIO N CONDITIO NS Find Key=(A,B) Find Key=(A,*) A ≤ A A,C B,B C,C A,A A,B A,C B,A B,B B,C C,C C,D CMU 15-445/645 (Fall 2019)

21 SELECTIO N CONDITIO NS Find Key=(A,B) Find Key=(A,*) A ≤ A A,C B,B C,C A,A A,B A,C B,A B,B B,C C,C C,D A ≤ B CMU 15-445/645 (Fall 2019)

21 SELECTIO N CONDITIO NS Find Key=(A,B) Find Key=(A,*) Find Key=(*,B) A,C B,B C,C A,A A,B A,C B,A B,B B,C C,C C,D CMU 15-445/645 (Fall 2019)

21 SELECTIO N CONDITIO NS Find Key=(A,B) Find Key=(A,*) *,B < C,C Find Key=(*,B) A,C B,B C,C A,A A,B A,C B,A B,B B,C C,C C,D CMU 15-445/645 (Fall 2019)

21 SELECTIO N CONDITIO NS Find Key=(A,B) Find Key=(A,*) *,B < C,C Find Key=(*,B) A,C B,B C,C A,A A,B A,C B,A B,B B,C C,C C,D * = A * = B B = B B = B CMU 15-445/645 (Fall 2019)

22 B+ TREE DESIGN CHOICES Node Size Merge Threshold Variable Length Keys Non-Unique Indexes Intra-Node Search CMU 15-445/645 (Fall 2019)

23 NODE SIZE The slower the storage device, the larger the optimal node size for a B+Tree. → HDD ~1MB → SSD: ~10KB → In-Memory: ~512B Optimal sizes can vary depending on the workload → Leaf Node Scans vs. Root-to-Leaf Traversals CMU 15-445/645 (Fall 2019)

24 M ERGE THRESH OLD Some DBMSs do not always merge nodes when it is half full. Delaying a merge operation may reduce the amount of reorganization. It may also be better to just let underflows to exist and then periodically rebuild entire tree. CMU 15-445/645 (Fall 2019)

25 VARIABLE LENGTH KEYS Approach #1: Pointers → Store the keys as pointers to the tuple’s attribute. Approach #2: Variable Length Nodes → The size of each node in the index can vary. → Requires careful memory management. Approach #3: Padding → Always pad the key to be max length of the key type. Approach #4: Key Map / Indirection → Embed an array of pointers that map to the key + value list within the node. CMU 15-445/645 (Fall 2019)

26 KEY M AP / INDIRECTIO N B+Tree Leaf Node Level Slots Prev Next ¤ ¤ # # Sorted Key Map ¤ ¤ ¤ ¤ Key+Values Prashanth V4 Lin V2 Andy V1 Obama V3 CMU 15-445/645 (Fall 2019)

26 KEY M AP / INDIRECTIO N B+Tree Leaf Node Level Slots Prev Next ¤ ¤ # # Sorted Key Map ¤ ¤ ¤ ¤ Key+Values Prashanth V4 Lin V2 Andy V1 Obama V3 CMU 15-445/645 (Fall 2019)

26 KEY M AP / INDIRECTIO N B+Tree Leaf Node Level Slots Prev Next ¤ ¤ # # Sorted Key Map A·¤ L·¤ O·¤ P·¤ ¤ ¤ ¤ ¤ Key+Values Prashanth V4 Lin V2 Andy V1 Obama V3 CMU 15-445/645 (Fall 2019)

27 NON- UN IQ UE INDEXES Approach #1: Duplicate Keys → Use the same leaf node layout but store duplicate keys multiple times. Approach #2: Value Lists → Store each key only once and maintain a linked list of unique values. CMU 15-445/645 (Fall 2019)