Database Storage and Indices

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 alice@aut.ac.ir Female 30-40 London 1260 2 Bob bob@gmail.com Male 20-30 Paris 907 3 Carol carol@gmail.com Female 20-30 Shanghai 1400 4 Dave dave@yahoo.com Male 10-20 Sydney 2577 5 Eve eve@hotmail.com Female 50-60 Toronto 3790 6 Mallory mallory@me.com Female 30-40 Shanghai 1968 7 Peggy peggy@aut.ac.ir Female 40-50 Cairo 3731 438532 Steve steve@me.com 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,alice@aut.ac.ir,Female,30-40,London,1260\n 2,Bob,bob@gmail.com,Male,20-30,Paris,907\n 3,Carol,carol@gmail.com,Female,20-30,Shanghai,1400\n 4,Dave,dave@yahoo.com,Male,10-20,Sydney,2577\n 5,Eve,eve@hotmail.com,Female,50-60,Toronto,3790\n 6,Mallory,mallory@me.com,Female,30-40,Shanghai,1968\n 7,Peggy,peggy@aut.ac.ir,Female,40-50,Cairo,3731\n ...... 438532,Steve,steve@me.com,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,alice@aut.ac.ir,Female,30-40, London,1260\n 2, Bob, bob@gmail.com, Male,20-30, Paris, 907\n 3, Carol,carol@gmail.com,Female,20-30, Shanghai,1400\n 4, Dave, dave@yahoo.com, Male,10-20, Sydney,2577\n 5, Eve,eve@hotmail.com,Female,50-60, Toronto,3790\n 6,Mallory, mallory@me.com,Female,30-40, Shanghai,1968\n 7, Peggy,peggy@aut.ac.ir,Female,40-50, Cairo,3731\n ...... 438532, Steve, steve@me.com, Male,50-60,Cupertino,7110\n /home/pooria/db/users.table
19. 11 CSV (Fixed-length Fields) /home/pooria/db/users.table 1 Alicealice@aut.ac.irFemale30-40 London1260 2 Bob bob@gmail.com Male20-30 Paris 907 3 Carolcarol@gmail.comFemale20-30 Shanghai1400 4 Dave dave@yahoo.com Male10-20 Sydney2577 5 Eveeve@hotmail.comFemale50-60 Toronto3790 6Mallory mallory@me.comFemale30-40 Shanghai1968 7 Peggypeggy@aut.ac.irFemale40-50 Cairo3731 ...... 438532 Steve steve@me.com 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 | bob@gmail.com 14 | Jane | 20 | clark@gmail.com … 5 | Smith | 14 | smith@me.com 13 | Adams | 35 | adams@yahoo.com … 6 | Jones | 19 | jones@gmail.com 7 | Jane | 48 | jane@hotmail.com … 1 | Blake | 23 | blake@gmail.com … … Hash Map 14 UID Hash function
24. 2 | Bob | 26 | bob@gmail.com 14 | Jane | 20 | clark@gmail.com … 5 | Smith | 14 | smith@me.com 13 | Adams | 35 | adams@yahoo.com … 6 | Jones | 19 | jones@gmail.com 7 | Jane | 48 | jane@hotmail.com … 1 | Blake | 23 | blake@gmail.com … … UID=7 Hash Map 14 UID Hash function
25. 2 | Bob | 26 | bob@gmail.com 14 | Jane | 20 | clark@gmail.com … 5 | Smith | 14 | smith@me.com 13 | Adams | 35 | adams@yahoo.com … 6 | Jones | 19 | jones@gmail.com 7 | Jane | 48 | jane@hotmail.com … 1 | Blake | 23 | blake@gmail.com … … UID=7 Hash Map 14 UID Hash function
26. 2 | Bob | 26 | bob@gmail.com 14 | Jane | 20 | clark@gmail.com … 5 | Smith | 14 | smith@me.com 13 | Adams | 35 | adams@yahoo.com … 6 | Jones | 19 | jones@gmail.com 7 | Jane | 48 | jane@hotmail.com … 1 | Blake | 23 | blake@gmail.com … … UID=7 Hash Map 14 City=Cupertino UID Hash function
27. 2 | Bob | 26 | bob@gmail.com 14 | Jane | 20 | clark@gmail.com … 5 | Smith | 14 | smith@me.com 13 | Adams | 35 | adams@yahoo.com … 6 | Jones | 19 | jones@gmail.com 7 | Jane | 48 | jane@hotmail.com … 1 | Blake | 23 | blake@gmail.com … … 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 alice@aut.ac.ir Female 30-40 London 1260 2 Bob bob@gmail.com Male 20-30 Paris 907 3 Carol carol@gmail.com Female 20-30 Shanghai 1400 4 Dave dave@yahoo.com Male 10-20 Sydney 2577 5 Eve eve@hotmail.com Female 50-60 Toronto 3790 6 Mallory mallory@me.com Female 30-40 Shanghai 1968 7 Peggy peggy@aut.ac.ir Female 40-50 Cairo 3731 438532 Steve steve@me.com 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:
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
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