Upgrade to Pro — share decks privately, control downloads, hide ads and more …

Database Storage and Indices

Pooria Azimi
April 12, 2012
Programming
4
99

Database Storage and Indices

Pooria Azimi

April 12, 2012
Tweet

More Decks by Pooria Azimi

See All by Pooria Azimi
MongoDB
pooriaazimi
7
190

Other Decks in Programming

See All in Programming
ゆるやかにgolangci-lintのルールを強くする / Kyoto.go #56
utgwkk
2
400
採用事例の少ないSvelteを選んだ理由と それを正解にするためにやっていること
oekazuma
2
1k
menu基盤チームによるGoogle Cloudの活用事例~Application Integration, Cloud Tasks編~
yoshifumi_ishikura
0
110
「とりあえず動く」コードはよい、「読みやすい」コードはもっとよい / Code that 'just works' is good, but code that is 'readable' is even better.
mkmk884
3
540
「Chatwork」Android版アプリを 支える単体テストの現在
okuzawats
0
180
Асинхронность неизбежна: как мы проектировали сервис уведомлений
lamodatech
0
850
責務を分離するための例外設計 - PHPカンファレンス 2024
kajitack
8
1.6k
Stackless и stackful? Корутины и асинхронность в Go
lamodatech
0
840
見えないメモリを観測する: PHP 8.4 `pg_result_memory_size()` とSQL結果のメモリ管理
kentaroutakeda
0
420
競技プログラミングへのお誘い@阪大BOOSTセミナー
kotamanegi
0
360
クリエイティブコーディングとRuby学習 / Creative Coding and Learning Ruby
chobishiba
0
3.9k
ブラウザ単体でmp4書き出すまで - muddy-web - 2024-12
yue4u
3
490

Featured

See All Featured
Improving Core Web Vitals using Speculation Rules API
sergeychernyshev
0
99
Mobile First: as difficult as doing things right
swwweet
222
9k
Build The Right Thing And Hit Your Dates
maggiecrowley
33
2.4k
Large-scale JavaScript Application Architecture
addyosmani
510
110k
[Rails World 2023 - Day 1 Closing Keynote] - The Magic of Rails
eileencodes
33
2k
Evolution of real-time – Irina Nazarova, EuRuKo, 2024
irinanazarova
5
450
Optimizing for Happiness
mojombo
376
70k
The MySQL Ecosystem @ GitHub 2015
samlambert
250
12k
Exploring the Power of Turbo Streams & Action Cable | RailsConf2023
kevinliebholz
28
4.4k
Imperfection Machines: The Place of Print at Facebook
scottboms
266
13k
What’s in a name? Adding method to the madness
productmarketing
PRO
22
3.2k
A better future with KSS
kneath
238
17k

Transcript

  1. Database Storage and Indices Pooria Azimi 1

  2. Project 1 Questions? 2

  3. Abstraction Layers DBMS Architecture Levels: • External (View) Level •

    Logical (Conceptual) Level • Physical (Internal) Level 3

  4. Outline 4

  5. Outline How to store a table on disk? 4

  6. Outline How to store a table on disk? • Sequential

    file, Hash map, B tree, ... 4

  7. Outline How to store a table on disk? • Sequential

    file, Hash map, B tree, ... Indexing, for faster retrieval 4

  8. Outline How to store a table on disk? • Sequential

    file, Hash map, B tree, ... Indexing, for faster retrieval • Single and multiple index, Bitmap index, Clustered vs. non-clustered, ... 4

  9. Outline How to store a table on disk? • Sequential

    file, Hash map, B tree, ... Indexing, for faster retrieval • Single and multiple index, Bitmap index, Clustered vs. non-clustered, ... • Tradeoffs in using (multiple) indices 4

  10. 5 Users table: UID Name Email Gender Age Group City

    Salary 1 Alice [email protected] Female 30-40 London 1260 2 Bob [email protected] Male 20-30 Paris 907 3 Carol [email protected] Female 20-30 Shanghai 1400 4 Dave [email protected] Male 10-20 Sydney 2577 5 Eve [email protected] Female 50-60 Toronto 3790 6 Mallory [email protected] Female 30-40 Shanghai 1968 7 Peggy [email protected] Female 40-50 Cairo 3731 438532 Steve [email protected] Male 50-60 Cupertino 7110

  11. Sequential Files 6

  12. Sequential Files 6 • CSV (comma separated values) • Fixed-length

    fields • Variable-length fields

  13. Sequential Files 6 • CSV (comma separated values) • Fixed-length

    fields • Variable-length fields • XML

  14. Sequential Files 6 • CSV (comma separated values) • Fixed-length

    fields • Variable-length fields • XML • ...

  15. 7 CSV (Variable-length Fields) 1,Alice,[email protected],Female,30-40,London,1260\n 2,Bob,[email protected],Male,20-30,Paris,907\n 3,Carol,[email protected],Female,20-30,Shanghai,1400\n 4,Dave,[email protected],Male,10-20,Sydney,2577\n 5,Eve,[email protected],Female,50-60,Toronto,3790\n 6,Mallory,[email protected],Female,30-40,Shanghai,1968\n

    7,Peggy,[email protected],Female,40-50,Cairo,3731\n ...... 438532,Steve,[email protected],Male,50-60,Cupertino,7110\n /home/pooria/db/users.table

  16. 8 CSV (Variable-length Fields) • Search: Slow • Update: Slow

    • Delete: Slow • Space: Very efficient

  17. 9 CSV (Variable-length Fields) Suitable for • Small tables and

    short Logs • Tables that don’t change very often • Tables that must be read sequentially (moving data between two application) • Tables with variable-length fields

  18. 10 CSV (Fixed-length Fields) 1, Alice,[email protected],Female,30-40, London,1260\n 2, Bob, [email protected],

    Male,20-30, Paris, 907\n 3, Carol,[email protected],Female,20-30, Shanghai,1400\n 4, Dave, [email protected], Male,10-20, Sydney,2577\n 5, Eve,[email protected],Female,50-60, Toronto,3790\n 6,Mallory, [email protected],Female,30-40, Shanghai,1968\n 7, Peggy,[email protected],Female,40-50, Cairo,3731\n ...... 438532, Steve, [email protected], Male,50-60,Cupertino,7110\n /home/pooria/db/users.table

  19. 11 CSV (Fixed-length Fields) /home/pooria/db/users.table 1 [email protected] London1260 2 Bob

    [email protected] Male20-30 Paris 907 3 [email protected] Shanghai1400 4 Dave [email protected] Male10-20 Sydney2577 5 [email protected] Toronto3790 6Mallory [email protected] Shanghai1968 7 [email protected] Cairo3731 ...... 438532 Steve [email protected] Male50-60Cupertino7110

  20. 12 CSV (Fixed-length Fields) • Search: A bit faster (than

    variable-length) • Update: Very fast (however, we must search the tuple first) • Delete: Slow • Space: Not efficient

  21. 13 CSV (Fixed-length Fields) Suitable for • Logs • Tables

    that can be read sequentially • Tables with short, fixed-length fields

  22. Hash Map 14

  23. 2 | Bob | 26 | [email protected] 14 | Jane

    | 20 | [email protected] … 5 | Smith | 14 | [email protected] 13 | Adams | 35 | [email protected] … 6 | Jones | 19 | [email protected] 7 | Jane | 48 | [email protected] … 1 | Blake | 23 | [email protected] … … Hash Map 14 UID Hash function

  24. 2 | Bob | 26 | [email protected] 14 | Jane

    | 20 | [email protected] … 5 | Smith | 14 | [email protected] 13 | Adams | 35 | [email protected] … 6 | Jones | 19 | [email protected] 7 | Jane | 48 | [email protected] … 1 | Blake | 23 | [email protected] … … UID=7 Hash Map 14 UID Hash function

  25. 2 | Bob | 26 | [email protected] 14 | Jane

    | 20 | [email protected] … 5 | Smith | 14 | [email protected] 13 | Adams | 35 | [email protected] … 6 | Jones | 19 | [email protected] 7 | Jane | 48 | [email protected] … 1 | Blake | 23 | [email protected] … … UID=7 Hash Map 14 UID Hash function

  26. 2 | Bob | 26 | [email protected] 14 | Jane

    | 20 | [email protected] … 5 | Smith | 14 | [email protected] 13 | Adams | 35 | [email protected] … 6 | Jones | 19 | [email protected] 7 | Jane | 48 | [email protected] … 1 | Blake | 23 | [email protected] … … UID=7 Hash Map 14 City=Cupertino UID Hash function

  27. 2 | Bob | 26 | [email protected] 14 | Jane

    | 20 | [email protected] … 5 | Smith | 14 | [email protected] 13 | Adams | 35 | [email protected] … 6 | Jones | 19 | [email protected] 7 | Jane | 48 | [email protected] … 1 | Blake | 23 | [email protected] … … UID=7 Hash Map 14 City=Cupertino UID Hash function

  28. Hash Map 15

  29. Hash Map Good: 15

  30. Hash Map Good: • Fast (for lookup field) 15

  31. Hash Map Good: • Fast (for lookup field) • Easy

    to implement 15

  32. Hash Map Good: • Fast (for lookup field) • Easy

    to implement • Relatively space-efficient (no pointers) 15

  33. Hash Map Good: • Fast (for lookup field) • Easy

    to implement • Relatively space-efficient (no pointers) 15

  34. Hash Map Good: • Fast (for lookup field) • Easy

    to implement • Relatively space-efficient (no pointers) Bad: 15

  35. Hash Map Good: • Fast (for lookup field) • Easy

    to implement • Relatively space-efficient (no pointers) Bad: • Naïve implementations only allow one index (lookup key) per table 15

  36. Hash Map Good: • Fast (for lookup field) • Easy

    to implement • Relatively space-efficient (no pointers) Bad: • Naïve implementations only allow one index (lookup key) per table • Not scalable (for non-memory resident data) 15

  37. Hash Map Good: • Fast (for lookup field) • Easy

    to implement • Relatively space-efficient (no pointers) Bad: • Naïve implementations only allow one index (lookup key) per table • Not scalable (for non-memory resident data) • Not suitable for range queries 15

  38. 16 ... 11 | Smi- 10 | Bob ... 14

    | Cla- 13 | Bla- ... 17 | Ste- 16 | Jan- ... ... ... 9 | Joh- ... ... B tree ... 15 | Ada- 12 | Jon-

  39. 17 B tree

  40. • They were specifically built for database storage 17 B

    tree

  41. • They were specifically built for database storage • They

    are I/O-friendly 17 B tree

  42. • They were specifically built for database storage • They

    are I/O-friendly • B trees are fast and scalable 17 B tree

  43. • They were specifically built for database storage • They

    are I/O-friendly • B trees are fast and scalable • They can be modified to allow more than one index per table 17 B tree

  44. • They were specifically built for database storage • They

    are I/O-friendly • B trees are fast and scalable • They can be modified to allow more than one index per table • They work well with range queries 17 B tree

  45. Demo 18 B tree

  46. 19 B tree

  47. 19 B tree Good:

  48. 19 B tree Good: • Fast (for indexed fields)

  49. 19 B tree Good: • Fast (for indexed fields) •

    Scalable

  50. 19 B tree Good: • Fast (for indexed fields) •

    Scalable • Suitable for range queries (on indexed fields)

  51. 19 B tree Good: • Fast (for indexed fields) •

    Scalable • Suitable for range queries (on indexed fields) Bad:

  52. 19 B tree Good: • Fast (for indexed fields) •

    Scalable • Suitable for range queries (on indexed fields) Bad: • Space overhead

  53. 19 B tree Good: • Fast (for indexed fields) •

    Scalable • Suitable for range queries (on indexed fields) Bad: • Space overhead • Negative impact on performance

  54. Index •Why use indices? • Faster retrieval • Most efficient

    in range queries •Tradeoffs • Slower writes/updates vs. faster retrieval • Disk space overhead 20

  55. 21 Users table: UID Name Email Gender Age Group City

    Salary 1 Alice [email protected] Female 30-40 London 1260 2 Bob [email protected] Male 20-30 Paris 907 3 Carol [email protected] Female 20-30 Shanghai 1400 4 Dave [email protected] Male 10-20 Sydney 2577 5 Eve [email protected] Female 50-60 Toronto 3790 6 Mallory [email protected] Female 30-40 Shanghai 1968 7 Peggy [email protected] Female 40-50 Cairo 3731 438532 Steve [email protected] Male 50-60 Cupertino 7110

  56. Why Use Indices? Without using indices, most queries require a

    full scan! 22

  57. Why Use Indices? 23 Sample queries:

  58. Why Use Indices? • SELECT name, city FROM users WHERE

    city LIKE ‘S%’ 23 Sample queries:

  59. Why Use Indices? • SELECT name, city FROM users WHERE

    city LIKE ‘S%’ • SELECT name, city FROM users WHERE uid BETWEEN 10 AND 20 23 Sample queries:

  60. 24

  61. 24 UID 10 11 12 13 14 15 Index (in

    memory)

  62. 24 UID 10 11 12 13 14 15 Index (in

    memory) 8 Bytes (4 bytes for keys, 4 bytes for pointers)

  63. 24 UID 10 11 12 13 14 15 UID 7

    … … … … … 13 … … … … … 11 … … … … … 9 … … … … … 12 … … … … … 10 … … … … … Index (in memory) Data (on disk) 8 Bytes (4 bytes for keys, 4 bytes for pointers)

  64. 24 UID 10 11 12 13 14 15 UID 7

    … … … … … 13 … … … … … 11 … … … … … 9 … … … … … 12 … … … … … 10 … … … … … Index (in memory) Data (on disk) 8 Bytes (4 bytes for keys, 4 bytes for pointers) 480 Bytes

  65. 24 UID 10 11 12 13 14 15 UID 7

    … … … … … 13 … … … … … 11 … … … … … 9 … … … … … 12 … … … … … 10 … … … … … Index (in memory) Data (on disk) 8 Bytes (4 bytes for keys, 4 bytes for pointers) 480 Bytes

  66. 24 UID 10 11 12 13 14 15 UID 7

    … … … … … 13 … … … … … 11 … … … … … 9 … … … … … 12 … … … … … 10 … … … … … Index (in memory) Data (on disk) 8 Bytes (4 bytes for keys, 4 bytes for pointers) 480 Bytes

  67. 24 UID 10 11 12 13 14 15 UID 7

    … … … … … 13 … … … … … 11 … … … … … 9 … … … … … 12 … … … … … 10 … … … … … Index (in memory) Data (on disk) 8 Bytes (4 bytes for keys, 4 bytes for pointers) 480 Bytes

  68. 24 UID 10 11 12 13 14 15 UID 7

    … … … … … 13 … … … … … 11 … … … … … 9 … … … … … 12 … … … … … 10 … … … … … Index (in memory) Data (on disk) 8 Bytes (4 bytes for keys, 4 bytes for pointers) 480 Bytes

  69. 25

  70. 25 Name Alice Bob Bernard Carole Cindy Dave Index (in

    memory)

  71. 25 Name Alice Bob Bernard Carole Cindy Dave UID Name

    8 Peggy … … … … 3 Carole … … … … 2 Bob … … … … 26 Trudy … … … … 32 Bernard … … … … 1 Alice … … … … Index (in memory) Data (on disk)

  72. 25 Name Alice Bob Bernard Carole Cindy Dave UID Name

    8 Peggy … … … … 3 Carole … … … … 2 Bob … … … … 26 Trudy … … … … 32 Bernard … … … … 1 Alice … … … … Index (in memory) Data (on disk)

  73. 25 Name Alice Bob Bernard Carole Cindy Dave UID Name

    8 Peggy … … … … 3 Carole … … … … 2 Bob … … … … 26 Trudy … … … … 32 Bernard … … … … 1 Alice … … … … Index (in memory) Data (on disk)

  74. 25 Name Alice Bob Bernard Carole Cindy Dave UID Name

    8 Peggy … … … … 3 Carole … … … … 2 Bob … … … … 26 Trudy … … … … 32 Bernard … … … … 1 Alice … … … … Index (in memory) Data (on disk)

  75. 25 Name Alice Bob Bernard Carole Cindy Dave UID Name

    8 Peggy … … … … 3 Carole … … … … 2 Bob … … … … 26 Trudy … … … … 32 Bernard … … … … 1 Alice … … … … Index (in memory) Data (on disk)

  76. 26 B tree Separate trees for index & data

  77. 26 B tree Separate trees for index & data Data

    (UID): ... 11 | Smi- 10 | Bob ... 14 | Cla- 13 | Bla- ... 17 | Ste- 16 | Jan- ... ... ... ... 15 | Ada- 12 | Jon-

  78. 26 B tree Separate trees for index & data Index

    (UID): Data (UID): ... 11 10 ... 14 13 ... 17 16 ... ... ... ... 15 12 ... 11 | Smi- 10 | Bob ... 14 | Cla- 13 | Bla- ... 17 | Ste- 16 | Jan- ... ... ... ... 15 | Ada- 12 | Jon-

  79. 26 B tree Separate trees for index & data Index

    (UID): Data (UID): ... 11 10 ... 14 13 ... 17 16 ... ... ... ... 15 12 ... 11 | Smi- 10 | Bob ... 14 | Cla- 13 | Bla- ... 17 | Ste- 16 | Jan- ... ... ... ... 15 | Ada- 12 | Jon- Memory Disk

  80. 27 B tree Separate trees for index & data ...

    11 | Smi- 10 | Bob ... 14 | Cla- 13 | Bla- ... 17 | Ste- 16 | Jan- ... ... ... ... 11 10 ... 14 13 ... 17 16 ... ... ... ... 15 12 ... 15 | Ada- 12 | Jon- Index (UID): Data (UID): Memory Disk

  81. 28 B tree Multiple Indexes ... 11 | Smi- 10

    | Bob ... 14 | Cla- 13 | Bla- ... 17 | Ste- 16 | Jan- ... ... ... ... 15 | Ada- 12 | Jon- ... 11 10 ... 14 13 ... 17 16 ... ... ... ... 15 12 UID: Memory Disk

  82. 29 B tree Multiple Indexes ... 11 | Smi- 10

    | Bob ... 14 | Cla- 13 | Bla- ... 17 | Ste- 16 | Jan- ... ... ... ... 11 10 ... 14 13 ... 17 16 ... ... ... ... 15 12 ... 15 | Ada- 12 | Jon- UID: Memory Disk

  83. 30 B tree Multiple Indexes ... 11 | Smi- 10

    | Bob ... 14 | Cla- 13 | Bla- ... 17 | Ste- 16 | Jan- ... ... ... ... 11 10 ... 14 13 ... 17 16 ... ... ... ... 15 12 ... 15 | Ada- 12 | Jon- UID: ... Bl Ad ... Jan Cl ... Ste Sm ... ... ... ... Jon Bo Username: Memory Disk

  84. 31 B tree Multiple Indexes ... 11 | Smi- 10

    | Bob ... 14 | Cla- 13 | Bla- ... 17 | Ste- 16 | Jan- ... ... ... ... 11 10 ... 14 13 ... 17 16 ... ... ... ... 15 12 ... 15 | Ada- 12 | Jon- UID: ... Bl Ad ... Jan Cl ... Ste Sm ... ... ... ... Jon Bo Username: Memory Disk

  85. 32 B tree Multiple Indexes ... 11 | Smi- 10

    | Bob ... 14 | Cla- 13 | Bla- ... 17 | Ste- 16 | Jan- ... ... ... ... 11 10 ... 14 13 ... 17 16 ... ... ... ... 15 12 ... 15 | Ada- 12 | Jon- UID: ... Bl Ad ... Jan Cl ... Ste Sm ... ... ... ... Jon Bo Username: Age: ... 19 14 ... 26 23 ... 56 48 ... ... ... ... 35 20 Memory Disk

  86. 33 B tree Multiple Indexes ... 11 | Smi- 10

    | Bob ... 14 | Cla- 13 | Bla- ... 17 | Ste- 16 | Jan- ... ... ... ... 11 10 ... 14 13 ... 17 16 ... ... ... ... 15 12 ... 15 | Ada- 12 | Jon- UID: ... Bl Ad ... Jan Cl ... Ste Sm ... ... ... ... Jon Bo Username: ... 19 14 ... 26 23 ... 56 48 ... ... ... ... 35 20 Age: Memory Disk

  87. Index trees are small. Therefore, we can fit all index

    trees in main memory. 34 B tree Multiple Indexes

  88. Clustered vs. Non-clustered Indexes Non-clustered Index: The data rows may

    be randomly spread throughout the table. A non-clustered index tree contains the index keys in sorted order, with the leaf level of the index containing the pointer to the page and the row number in the data page. Clustered Index: The ordering of the physical data rows is in accordance with the index blocks that point to them. Therefore, only one clustered index can be created on a given table. 35

  89. 36 ... 11 | Smi- 10 | Bob ... 14

    | Cla- 13 | Bla- ... 17 | Ste- 16 | Jan- ... ... ... ... 11 10 ... 14 13 ... 17 16 ... ... ... ... 15 12 ... 15 | Ada- 12 | Jon- UID: ... Bl Ad ... Jan Cl ... Ste Sm ... ... ... ... Jon Bo Username: Age: ... 19 14 ... 26 23 ... 48 35 ... ... ... ... 26 20 Clustered Non-clustered Memory Disk

  90. Clustered vs. Non-clustered Indexes • Each table must have one

    (and only one) clustered index (typically primary key) • Each table can have any number of non-clustered indexes 37

  91. Reverse Index 38

  92. Reverse Index 38 Sample Query: Find all users with ‘aut.ac.ir’

    email account

  93. Reverse Index CREATE INDEX ON email SELECT name FROM users

    WHERE email LIKE ‘%@aut.ac.ir’ 38 Sample Query: Find all users with ‘aut.ac.ir’ email account

  94. Reverse Index 39 CREATE INDEX ON reverse(email) SELECT name FROM

    users WHERE reverse(email) LIKE reverse(‘%@aut.ac.ir’) Sample Query: Find all users with ‘aut.ac.ir’ email account

  95. Bitmap Index Suitable for: • Categorical fields, with a small

    number of possible values (age group, account type) • Boolean fields (gender) 40

  96. Bitmap Index 41 Gender (Female): 011101100010101010…1 Age Group (20-30): 011001000010000010…1

    Age Group (30-40): 000100011100000001…0 … Female, Age between 20 and 40: 011101000010000010…1

  97. Tradeoffs In Using Indices 42

  98. Tradeoffs In Using Indices Good: 42

  99. Tradeoffs In Using Indices Good: • Much faster search (on

    indexed fields) 42

  100. Tradeoffs In Using Indices Good: • Much faster search (on

    indexed fields) • Range queries 42

  101. Tradeoffs In Using Indices Good: • Much faster search (on

    indexed fields) • Range queries Bad: 42

  102. Tradeoffs In Using Indices Good: • Much faster search (on

    indexed fields) • Range queries Bad: • Slower insert/update/delete 42

  103. Tradeoffs In Using Indices Good: • Much faster search (on

    indexed fields) • Range queries Bad: • Slower insert/update/delete • Space overhead 42

  104. Tradeoffs In Using Indices 43 Number of transactions per second:

  105. Tradeoffs In Using Indices 43 No indices 1 Index 2

    indices 3 indices 4 indices Number of transactions per second:

  106. Tradeoffs In Using Indices 43 No indices 1 Index 2

    indices 3 indices 4 indices 100 Number of transactions per second:

  107. Tradeoffs In Using Indices 43 No indices 1 Index 2

    indices 3 indices 4 indices 100 65 Number of transactions per second:

  108. Tradeoffs In Using Indices 43 No indices 1 Index 2

    indices 3 indices 4 indices 100 65 22 Number of transactions per second:

  109. Tradeoffs In Using Indices 43 No indices 1 Index 2

    indices 3 indices 4 indices 100 65 22 15 Number of transactions per second:

  110. Tradeoffs In Using Indices 43 No indices 1 Index 2

    indices 3 indices 4 indices 100 65 22 15 5 Number of transactions per second:

  111. Indices in MySQL CREATE INDEX IX_users_username ON users (username); SELECT

    * FROM users WITH(INDEX(IX_users_username)) WHERE username = ‘Jane’ DROP INDEX users.IX_users_username; 44

  112. Indices in Microsoft SQL Server CREATE [NONCLUSTERED] INDEX IX_users_username ON

    users (username); GO SELECT * FROM users WITH(INDEX(IX_users_username)) WHERE username = ‘Jane’ DROP INDEX users.IX_users_username GO 45

  113. Questions 46

  114. Questions • Storage, B+ tree, ... 46

  115. Questions • Storage, B+ tree, ... • Indices 46

  116. Questions • Storage, B+ tree, ... • Indices • Project

    1: MySQL 46

  117. Questions • Storage, B+ tree, ... • Indices • Project

    1: MySQL • Project 2: MongoDB 46

  118. Questions • Storage, B+ tree, ... • Indices • Project

    1: MySQL • Project 2: MongoDB • Optional Project 46