1 / 17

Index tuning--

Index tuning--. B+tree. overview. Overview of tree-structured index Indexed sequential access method (ISAM) B+tree. Data Structures. Most index data structures can be viewed as trees.

april
Download Presentation

Index tuning--

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. Index tuning-- B+tree

  2. overview • Overview of tree-structured index • Indexed sequential access method (ISAM) • B+tree

  3. Data Structures Most index data structures can be viewed as trees. In general, the root of this tree will always be in main memory, while the leaves will be located on disk. The performance of a data structure depends on the number of nodes in the average path from the root to the leaf. Data structure with high fan-out (maximum number of children of an internal node) are thus preferred.

  4. Tree-Structured Indexing Tree-structured indexing techniques support both range searches and equality searches index file may still be quite large. But we can apply the idea repeatedly! ISAM: static structure; B+ tree: dynamic, adjusts gracefully under inserts and deletes. Data pages

  5. Indexed sequential access method (ISAM) • Data entries vs index entries • Both belong to index file • Data entries: <key, records or pointer to records/record list> • Index entries:<key, pointer to index entries or data entries> • Both in ISAM and B-tree, leaf pages are data entries

  6. Example ISAM Tree • Each node can hold 2 entries; no need for `next-leaf-page’ pointers. (Why?)

  7. Comments on ISAM • File creation: Leaf (data) pages allocated sequentially, sorted by search key; then index pages allocated, then space for overflow pages. • Index entries: <search key value, page id>; they `direct’ search for data entries, which are in leaf pages. • Search: Start at root; use key comparisons to go to leaf. • Cost=logFN ; F = # entries/index pg, N = # leaf pgs • Insert: Find leaf data entry belongs to, and put it there. • Delete: Find and remove from leaf; if empty overflow page, de-allocate. • * Static tree structure: inserts/deletes affect only primary leaf pages.

  8. After Inserting 23*, 48*, 41*, 42*

  9. ... Then Deleting 42*, 51*, 97** Note If primary leaf page is empty, just leave it empty so that the number of allocation primary pages does not change!

  10. Features of ISAM • The primary leaf pages are assumed to be allocated sequentially • Because the number of such pages is known when the tree is created and does not change subsequently under inserts and deletes • So next-page-pointers are not needed

  11. Prons and cons of ISAM • Cons:losing sequentiality and balance • Due to long overflow chains • Leading to poor retrieving performance • One solution • Keep 20% of each page free when tree was created • Prons • No need to lock non-leaf index pages since we never modify them, which is one of important advantages of ISAM over B+tree • Another is: scans over a large range is more efficient thant B+tree

  12. B+-Tree A B+-Tree is a balanced tree whose leaves contain a sequence of key-pointer pairs. Dynamic structure that adjust gracefully for delete and insert

  13. B+ Tree: The Most Widely Used Index Operation (insertion, deletion) keep it Height-balanced. Searching for a record requires a traversal from root to the leaf Equality query, Insert/delete at log F N cost (F = fanout, N = # leaf pages); Grow and shrink dynamically. Need `next-leaf-pointer’ to chain up the leaf nodes To handle cases such as leaf node merging Minimum 50% occupancy (except for root). Each node contains d <= m <= 2d entries. The parameter d is called the order of the tree. Data entries at leaf are sorted.

  14. Example B+ Tree Each node can hold 4 entries (order = 2) Root 17 24 5 13 30 39 2 3 5 7 8 24 27 38 19 20 22 29 33 34 14 16

  15. Node structure Non-leaf nodes index entry P K P K P P K m 0 1 2 1 m 2 • Leaf nodes P K P K P Next leaf node P K m 0 1 2 1 m 2

  16. Searching in B+ Tree Search begins at root, and key comparisons direct it to a leaf (as in ISAM). Search for 5, 15, all data entries >= 24 ... Root 30 13 17 24 39 3 5 19 20 22 24 27 38 2 14 16 29 33 34 • Based on the search for 15*, we know it is not in the tree!

  17. summarize

More Related