Database indexing design and optimization pdf

Download address: Download the network disk

Content Profile ...

The database index design and optimization provides a simple, efficient and universal method for the design of relational database indexes. Through systematic explanation and a large number of cases, the author clearly illustrates the principle of access path selection of relational database, the scanning methods of tables and indexes, explains how to quickly estimate the CPU time and execution time of SQL running, and helps readers understand the SQL, table and index structure, The impact of access methods on relational databases, and the ability to use quantitative methods to judge and optimize the index design of the relational database.

Database indexing design and optimization is useful for people who already have the relevant knowledge of SQL as a relational language, want to understand SQL performance-related content, or who want to benefit from understanding how to effectively design tables and indexes. In addition, "Database Indexing Design and optimization" also applies to senior people who want to make better judgments about the changes that may be made to the introduction of new hardware. Author Profile ...

Tapio Lahdenmaki, Database performance consultant, teaches generic indexing design courses. He has worked at IBM for more than 30 years and is the leading author of the company's global curriculum on DB2 (for z/OS) performance-related courses. Michael Leach, a relational database consultant who has retired from IBM, has over 20 years of experience in application systems and database teaching. The articles of both authors have been translated into many languages and widely disseminated. Their approach to index design has been successfully applied to many core systems.Catalog ...The 1th chapter is an overview of ......... ..... 1
Another book on the performance of SQL .... 1
Inappropriate index ....... ................. 3
Misunderstanding and misunderstanding ...... 4
Myth # 1: Do not exceed the level of the index at 5 levels ....... 5
Myth # 2: The number of indexes of single table not more than 6 .... 6
Myth # 3: You should not index unstable columns ........ ....... 6
Example..... 7
Disk drive usage ...... ..... 7
Systematic indexing design ........ ......... 8
2nd Chapter table and index structure ..... 10
Introduced.......... 10
index pages and table pages ....... ....... 11
Index line ... 11
Index structure ... 12
Table row ..... 12
Buffer pool and disk I/O ......... ..... 12
Read from the DBMS buffer pool ......... .......... 13
Random I/O from the disk drive ....... .......... 13
Read from the disk server cache ........ ......... 14
Read from the disk drive in sequential order ........ ....... 15
Auxiliary type random reading ............ 15
Auxiliary sequential reading ........ ....... 18
Synchronous I/O and asynchronous I/O ..... 18
Hardware features ... 19
DBMS features .......... ............. 20
Page...... 20
The table is clustered ......... .............. 21st
The index line ..... .................. 21st
Table row ... 22
Index organization Table ....... ........... 22
Page adjacency .......... .............. 23
Alternatives to the B-tree Index ...... 24
A lot of the meaning of clustering ....... 25
3rd. SQL process .... 27
Brief introduction.......... 27
Predicate.......... 27
Commentary... 28
Optimizer and Access path ........ ....... 28
index slices and matching columns ...... ..... 29
Index filtering and filtering columns ...... 29
Access to path terminology ......... ..... 31
Monitor optimizer ........ ........... 32
Help Optimizer (statistical information). 32
Help Optimizer (number of FETCH calls) ..... ..... 32
When to determine the access path .... 33
Filter factor ... 34
Filter factor of the combined predicate ... 35
The effect of filter factors on the design of the index ........ ....... 37
Materialized result set ......... ............... 39
Cursor review ......... ............ 39
Mode 1: One fetch call materialized a record ... 40
Mode 2: Pre-materialized ...... 41
Database designers must keep in mind. 41
Practice.......... 41
The 4th chapter creates the ideal index for the Selete statement .... 43
Brief introduction.......... 43
Basic assumptions about disk and CPU time ... 44
Inappropriate index ........ ............... 44
Samsung index--the ideal index of the query statement ....... ....... 45
How the star rating is given ..... 46
RANGE predicate and Samsung Index ..... 48
The algorithm to design the best index for the query statement ........ ........ 49
The candidate a ..... .................. 50
Candidate B ..... ................... 50
Now the sort is fast-why do we need the candidate B .............. 51
Do you want to design an ideal index for all query statements ...?.. 52
Completely redundant index ..... ........ 52
Near-superfluous index ....... ..... 53
An index that may be superfluous ..... ..... 53
The cost of adding a new index ....... 54
Response time ......... ............ 54
Disk load ........ ............. 55
Disk space ......... ............ 56
Some suggestions ... 57
Practice.......... 58
The 5th chapter is forward-looking index design. 59
Find an inappropriate index ........ ....... 59
The basic Problem Law (BQ) ..... ....... 59
Attention... 60
Fast upper Limit Estimation method (QUBE) ... 61
The time of service ........ ........... 62
Queue time ....... .............. 62
Basic concept: Access to ...... ..... 63
Count the number of visits ........ ..... 65
FETCH processing ........ ......... 66
The Qube example of the primary access path ......... ........ 67
Using the lowest-cost index to meet your needs is still the best index you can achieve: Example 1 ..... 72
The basic issue of the transaction ..... 73
A quick estimate of the upper limit of the transaction. 73
Using the lowest-cost index to meet demand is still the best index you can reach .... 74
The best index of the transaction ..... 75
Half-width index (maximize index filtering) ....... ........ 75
Wide index (just access the index). 76
Using the lowest-cost index to meet your needs is the best index you can achieve: Example 2 .... 77
The scope of the transaction of BQ and Qube. 78
The best index of the transaction ..... 79
Half-width index (maximize index filtering) ....... ........ 80
Wide index (just access the index). 81
When to use Qube ........ ......... 82
The 6th chapter influences the factors of the index design process ...... ..... 83
Verification of I/O time estimation ...... ..... 83
Multiple narrow index slices ......... ............ 84
Simplicity is beauty (and safety) ..... 86
Difficulty predicate ... 87
The like verb ......... .......... 87
The OR operator and the Boolean predicate ... 88
In verb ....... ................ 89
Filter factor hidden danger ........ ............ 90
An example of a hidden danger of filter factor ..... 92
The best index ....... ............. 95
Half-width index (maximize index filtering) ....... ........ 96
Wide index (just access the index). 97
Summarize... 97
Practice.......... 99
The 7th Chapter Passive index design. 100
Brief introduction........ 100
EXPLAIN describes the access path that you have chosen ....... ..... 101
Full-table scan or full-index scan ... 101
Sort the result set .....---..... 101
Cost estimation ........ ............ 102
Database management system-specific explain options and limitations ....... ............. 102
The surveillance reveals the reality ......... ........ 103
The evolution of Performance Monitor .... 104
LRT level of exception monitoring ..... ..... 106
The mean value of the program granularity is not enough ......... ........... 106
Examples of exception reports: Each sharp line ............ 106
Problem-makers and victims .... 108
Problem makers with optimized space and problem makers with no space for optimization ..... ...... 108
The problem maker who has the space to optimize ......... .......... 109
The potential space for tuning ..... ..... 111
The problem maker with no optimization space ......... ............ 114
Victims.............................. 115
Find a slow SQL call .... 117
Call-level exception monitoring ..... ..... 118
Oracle for example ....... ........ 121
SQL Server for example ....... 123
Conclusion. 125
Database management system-specific monitoring issues .......... ........ 126
The sting report ......... .......... 127
Practice........ 127
The 8th chapter is the design index of the table connection ......... ........ 129
Brief introduction........ 129
Two simple table connections ....... ....... 131
Example 8.1:cust table as the outer table ......... ....... 131
Example 8.2:invoice table as the outer table ...... ..... 132
The effect of table access order on the design of the index ....... .......... 133
Case studies ......... ........... 133
Existing index ........ ............ 136
The ideal index ....... ............ 142
Ideal index, materialized one-screen result set per transaction ..... 146
Ideal index, materialized one screen result set per transaction and encountering FF defects ........... 149
Basic connectivity issues (BJQ) ... 151
Conclusion: Nested loops connected .... 153
The order in which the tables are accessed ..... ..... 153
Merge scan connections and hash connections .... 155
Merge Scan connection ........ ..... 155
Example 8.3: Merge Scan connection ..... 155
Hash connection ......... ........... 157
Program C: Selected by the optimizer ms/hj (under existing index conditions) ..... ..... 158
The ideal index ....... ............ 159
Nested loops connect vs. MS/HJ and the ideal index ...... ..... 161
Nested loops connect vs. Ms/hj ...... 161
Nested loops connect vs. Ideal index ........ ........... 162
Connect to more than two tables ....... ..... 163
Why connectivity performance is poor ... 166
Fuzzy index design ...... ..... 166
The optimizer may choose the wrong table access path ....... 166
Optimistic table design ....... ....... 166
Design an index for a subquery ..... ....... 167
Design the index for the Union statement ... 167
Thinking about the design of the table .....-..... 167
Redundant data .......... .......... 167
The unconscious design of the table ...... 171
Practice........ 173
The 9th Chapter Star Connection ....... 175
Introduced........ 175
The index design of dimension tables ....... ..... 177
The effects of table access Order ..... ....... 178
The index of the fact table ......... ........ 179
Summary table .... 182
Chapter 10th Multi-index access .... 184
Brief introduction........ 184
Index with .... 184
Use the index with the query table ........ ............. 186
Multi-index access and fact table ........ ............. 187
Multi-index access with a bitmap index ........ ......... 187
Index or .... 188
The index connection. 189
Practice........ 190
Chapter 11th Index and index reorganization 191
The physical structure of the B-tree index ..... 191
How the DBMS looks for index rows .... 192
What happens when you insert a line .... 193
Is the division of the leaf page serious? ..... 194
When should the index be reorganized ........ .............. 196
Insert mode ......... ........... 196
The stability of the indexed columns ...... ........ 205
Long index Line ...... 207
Example: A batch task that is sensitive to order ....... ......... 208
Table Disorderly Order (existence of clustered index) ....... .............. 211
Table Chaos (no clustered index starting with CNO) .... 212
A table row stored in a leaf page .... 212
SQL Server ......... ........ 212
Oracle ......... ............... 213
The cost of the index reorganization ......... ..... 214
The monitoring of division ........ ............. 215
Summarize........ 216
12th. Database management System related index restrictions ..... 219
Brief introduction........ 219
The number of indexed columns ......... ......... 219
The total length of the indexed column ......... ..... 220
Variable length column .... 220
Limit on the number of single-table indexes ....... 220
Index size upper limit ......... ......... 220
Index lock. 221
Index row compression ......... ............. 221
Database management System index creation example ......... ............ 222
13th Database Indexing Options 224
Brief introduction........ 224
Index row compression ......... ............. 224
Index columns other than the index key .... 225
The only constraint ..... 227
Scan a database index from a different direction ....... ............. 227
Index key truncation ......... ............. 228
The function-based index .......... ..... 228
Indexed skip scan ....... .......... 229
Block index .... 230
The two-level index of the data partition ..... 230
Practice........ 231
The 14th Chapter Optimizer is not perfect .......... ......... 232
Brief introduction........ 232
The optimizer does not always see the best solution ......... .......... 234
Matching and filtering issues ..... ..... 234
Non-BT verb ...... .......... 234
An unavoidable sort of ..... ..... 237
Unnecessary table access .....---No. 238
The optimizer's cost estimate may be wrong .......... ........ 239
Use the bounding verb of the bound variable ................. 239
Skewed distribution ........ ............ 241
Related columns ......... .............. 242
Part of the index key of the cautionary tale ... 243
Cost estimating formula ......... .......... 246
Estimation of I/O time ......... 247
Estimating CPU time ........ ..... 248
Assist optimizer to deal with estimation related issues ......... 249
Does the optimizer's problem affect the indexing design ...? .......... 252
Practice........ 253
15th. Other assessment matters .... 254
QUBE the assumptions behind the formula ... 254
Non-leaf index pages in memory .... 255
Example. 255
Disk server read cache impact ......... ............. 256
Buffer sub-pool ....... ........... 258
The long record ....... ............... 259
Slow sequential reading ......... ........ 259
The actual response time may be much shorter than the Qube valuation ..... 259
Leaf pages and table pages are cached in the buffer pool ........ ..... 260
Identify low-cost random access ... 262
Auxiliary random reading ...... ..... 262
Auxiliary sequential reading ......... ..... 265
Evaluate CPU time (cqube) .... 265
Single-order access to CPU time ..... .............. 265
CPU time for a single random visit ....... ............ 267
The CPU time of a single fetch call ....... ....... 269
The average CPU time for each line of the sort ......... ..... 270
Examples of CPU evaluations ........ .......... 270
A wide index or an ideal index .... 270
Nested loops (and anti-normalization) or ms/hj ..... 271
Merge Scan vs. hash connection ........ ........... 274
Skip-order scan ..... ..... 275
CPU time is still not negligible. 276
The 16th chapter organizes the index design process ......... ........ 277
Brief introduction........ 277
Computer-aided indexing design .... 278
9 Steps to design an excellent index .... 280
References ......... ............ 282
Glossary of terms ...... ..... 283
Index................ 291

Download address: Download the network disk