Скачать презентацию Module 6 Planning Indexes Overview n Introduction
6aee8f999a96dd28e5cb6b44b830cca5.ppt
- Количество слайдов: 31
Module 6: Planning Indexes
Overview n Introduction to Indexes n Index Architecture n How SQL Server Retrieves Stored Data n How SQL Server Maintains Index and Heap Structures n Deciding Which Columns to Index
u. Introduction to Indexes n How SQL Server Stores and Accesses Data n Whether to Create Indexes
How SQL Server Stores and Accesses Data n How Data Is Stored l l n Rows are stored in data pages Heaps are a collection of data pages for a table How Data Is Accessed l Scanning all data pages in a table l Using an index that points to data on a page Data Pages Page 4 Con Funk White. . . Page 5. . . . Rudd White Barr. . . Page 6. . . . Akhtar Funk Smith Martin. . . Page 8 Page 7. . . . Smith Ota Jones. . . . . Martin Phua Jones Smith. . . Page 9. . . . Ganio Jones Hall. . . . .
Whether to Create Indexes n Why to Create an Index l l n Speeds up data access Enforces uniqueness of rows Why Not to Create an Index l Consumes disk space l Incurs overhead
u Index Architecture n SQL Server Index Architecture n Using Heaps n Using Clustered Indexes n Using Nonclustered Indexes
Multimedia Presentation: SQL Server Index Architecture
Using Heaps SQL Server: n Uses Index Allocation Map Pages That: l l Navigate through the heap and find available space for new rows being inserted l n Contain information on where the extents of a heap are stored Connect data pages Reclaims Space for New Rows in the Heap When a Row Is Deleted
Using Clustered Indexes n Each Table Can Have Only One Clustered Index n The Physical Row Order of the Table and the Order of Rows in the Index Are the Same n Key Value Uniqueness Is Maintained Explicitly or Implicitly
Using Nonclustered Indexes n Nonclustered Indexes Are the SQL Server Default n Existing Nonclustered Indexes Are Automatically Rebuilt When: l An existing clustered index is dropped l A new clustered index is created l The DROP_EXISTING option is used to change which columns define the clustered index
u How SQL Server Retrieves Stored Data n How SQL Server Uses the sysindexes Table n Finding Rows Without Indexes n Finding Rows in a Heap with a Nonclustered Index n Finding Rows in a Clustered Index with a Nonclustered Index
How SQL Server Uses the sysindexes Table n Describes the Indexes indid Object Type 0 Heap 1 Clustered Index 2 to 250 Nonclustered Index 255 text, ntext, or image n Location of IAM, First, and Root of Indexes n Number of Pages and Rows n Distribution of Data
Finding Rows Without Indexes sysindexes id indid = 0 IAM Extent … 127 128 129 130 … Heap Extent 127 01 Con 01 Rudd …… … 020101 Akhtar… … Funk 01 White … … Smith 02 01 Con … Funk … Rudd 03020201 Funk ………… White Akhtar Ota 03020201 White……… Barr Smith … Smith 04030302 White. . . … … Durkin. Funk … 030302 Barr. . . … …. . . Jones. . . … Ota Martin 0504… 03. . . Smith. . . … Lang. . . …. . . 03 Jones. . . … …. . … 04 Martin. . . …. . . . … …. . . Extent 128 01 Dunn … 01 Rudd … 020101 Akhtar……… Randall 020101 Smith ……… White Con 02 Ota. Akhtar … Funk … 03 02 Barr. Rudd…… … 01 Funk …… 03 02 Smith … 01 Smith 04030302 White. . . ……… Slichter. Ota 030202 White …… Funk … 03 Jones. . . …. . . Barr 04 La. Brie. Smith. . . … Martin. . . 05 …. . . 03 Jones. . . … … 03. . … …. . . . Martin … 04. . . …. . . First IAM Bit Map 1 1 0 1 Extent 129 01 Seattle … 01 Rudd … 020101 Akhtar……… Paris 020101 Smith ……… White Con 02 Tokyo. Akhtar … Funk … 03 02 Barr. Rudd…… … Ota Smith 01 Funk …… 03 02 Smith … 01 04030302 White. . . ……… 030202 White …… Atlanta Ota … 03 Jones. . . …. . . Funk 04. . . 03 Barr. . . … Martin. . . … …. . . 03 Smith. . . … …. . . Jones. . . … …. . . . … 04. . . Martin. . . … …. . . Extent 130 01 Graff … 01 Rudd … 020101 Akhtar……… Bacon 020101 Smith ……… White Con 02 Koch. Rudd… … Funk … 03 02 Barr Akhtar… … Ota Smith 01 Funk …… 03 02 Smith … 01 … 030302 White. . . ………. . . Jones. . . 030202 White …… … 03 Barr. . . …. . . Funk Ota 04. . . 03 Smith. . . … Martin. . . … …. . . 03 Jones. . . … …. . . … 04. . . Martin. . . … …. . .
Finding Rows in a Heap with a Nonclustered Index sysindexes id indid = 2 root Akhtar. . . Martin SELECT lastname, Page 28 firstname Page 37 Page 12 - Root Akhtar Martin FROM member Ganio WHERE lastname Smith. . . Page 41 Akhtar 4: 706: 01 Barr 4: 705: 03 Con 4: 704: 01 Funk 4: 706: 02 Funk 4: 704: 02 Non-Leaf Level Non Clustered clustered Index BETWEEN 'Masters' AND 'Rudd' Page 51 Ganio 4: 709: 01 Hall 4: 709: 04 Jones 4: 709: 02 Jones 4: 708: 03 Jones 4: 707: 03 Page 61 Martin 4: 708: 01 Matey 4: 706: 04 Ota 4: 707: 02 Phua 4: 708: 02 Rudd 4: 705: 01 Page 71 Leaf Level Smith 4: 706: 03 (Key Value) Smith 4: 708: 04 Smith 4: 707: 01 White 4: 704: 03 White 4: 705: 02 Heap Page 704 01. . . Conn 02. . . Funk 03. . . White. . . . File ID #4 Page 705 01. . . Rudd 02. . . White 03. . . Barr. . . . Page 706 01. . . Akhtar 02. . . Funk 03. . . Smith 04. . . Matey. . Page 707 01. . . Smith 02 02. . . Ota 03. . . Jones. . . . Page 708 01. . . Martin 02. . . Phua 02 03. . . Jones 04. . . Smith. . Page 709 01. . . Ganio 02. . . Jones 03. . . Hall. . . .
Finding Rows in a Clustered Index sysindexes id indid = 1 root Clustered Index Akhtar … Martin Page 140 - Root Akhtar SELECT lastname, firstname Martin Ganio Smith FROM member … … WHERE lastname = 'Ota' Page 141 Page 145 Akhtar 2334 Barr 5678 Con 2534 Funk 1334 Funk 1534. . . Page 100 . . . . Ganio 7678 Hall 8078 Jones 2434 Jones 5978 Jones 2634. . . Page 110 . . . . Martin 1234 Martin 7778 Ota 5878 Phua 7878 Rudd 6078. . . Page 120 . . . . Smith 1434 Smith 5778 Smith 7978 White 2234 White 1634. . . Page 130 . . . .
Finding Rows in a Clustered Index with a Nonclustered Index sysindexes Nonclustered Index on First Name id indid = 2 root Non-Leaf Level Aaron. . . Jose Aaron Jose SELECT lastname, firstname, phone Deanna Nina FROM … member … WHERE firstname = 'Mike' Aaron Adam Amie … Con Barr Baldwin … Daum Hall Hampton … Jose Judy Mike … Leaf Level Lugo (Clustered Kaethler Key Value) Nash … Barr Kim Nagata O’Melia Clustered Index On Last Name Barr Cox Daum … Deanna Don Doug … Adam Arlette Deanna … … … Kim Kobara La. Brie … Shane Linda Ryan … … … Nagata Nash Nixon … Susanne Mike Toby … … …
u How SQL Server Maintains Index and Heap Structures n Page Splits in an Index n Forwarding Pointer in a Heap n How SQL Server Updates Rows n How SQL Server Deletes Rows
Page Splits in an Index INSERT member (last name) VALUES lastname = ‘Jackson' Index Pages Non-Leaf Level Akhtar … Martin Akhtar Ganio Jackson … Lang Smith … Leaf Level (Key Value) … Akhtar Barr Borm Buhl … … … Ganio Akhtar Hall Barr Hart Barr Borm Buhl … … … … Jackson Ganio Jones Hall Jones Hart Jones … … … … … Lang Martin Moris … … … Smith Smith … … …
Forwarding Pointer in a Heap sysindexes Page 37 Akhtar Ganio UPDATE. . . id indid = 2 Akhtar. . . Martin Page 12 - Root root Non-Leaf Level Page 28 Martin Smith. . . Non Clustered clustered Index member SET Page 51 Address =Page 61
How SQL Server Updates Rows n An Update Generally Does Not Cause a Row to Move n An Update Can Be a Delete Followed by an Insert n Batch Updates Touch Each Index Only Once
How SQL Server Deletes Rows n How Deletes Cause Ghost Records n How SQL Server Reclaims Space n How Files Can Shrink Clustered index pages move as a unit Heap records move individually
u Deciding Which Columns to Index n Understanding the Data n Indexing Guidelines n Choosing the Appropriate Clustered Index n Indexing to Support Queries n Determining Selectivity n Determining Density n Determining Distribution of Data
Understanding the Data n Logical and Physical Design n Data Characteristics n How Data Is Used l The types of queries performed l The frequency of queries that are typically performed
Indexing Guidelines n Columns to Index l l Those frequently searched in ranges l Those frequently accessed in sorted order l n Primary and foreign keys Those frequently grouped together during aggregation Columns Not to Index l Those seldom referenced in queries l Those that contain few unique values l Those defined with text, ntext, or image data types
Choosing the Appropriate Clustered Index n Heavily Updated Tables l n Sorting l n A clustered index with an identity column keeps updated pages in memory A clustered index keeps the data pre-sorted Column Length and Data Type l Limit the number of columns l Reduce the number of characters l Use the smallest data type possible
Indexing to Support Queries n Using Search Arguments n Writing Good Search Arguments l Specify a WHERE clause in the query l Verify that the WHERE clause limits the number of rows l Verify that an expression exists for every table referenced in the query l Avoid using leading wildcards
Determining Selectivity High selectivity member_no last_name first_name 1 Randall Joshua 2. Flood Kathie SELECT * FROM member WHERE member_no > 8999 . . 10000 Number of rows meeting criteria Total number of rows in table Anderson Bill Good candidate for Index? Low selectivity member_no last_name first_name 1 2. Randall Flood Number of rows meeting criteria Total number of rows in table SELECT * FROM member WHERE member_no < 9001 . . 10000 Joshua Kathie Anderson Bill 1000 = 10% 9000 = 10000 = 90% Good candidate for Index?
Determining Density last_name first_name Randall Joshua . . . Randall. . . Ota. . . Cynthia Tristan High Density SELECT * FROM member WHERE last_name = ‘Randall’ Low Density Lani SELECT * FROM member WHERE last_name = ‘Ota’
Determining Distribution of Data Standard Distribution of Values Number of Last Names A-E F-J K-O P-U Last Name V-Z Even Distribution of Values Number of Last Names A-B C-F G-K L-N Last Name O-Z
Recommended Practices Create Indexes on Columns That Join Tables Use Indexes to Enforce Uniqueness Drop Unused Indexes Avoid Long Clustering Keys Consider Using a Clustered Index to Support Sorting and Range Searches Create Indexes That Support Search Arguments
Review n Introduction to Indexes n Index Architecture n How SQL Server Retrieves Stored Data n How SQL Server Maintains Index and Heap Structures n Deciding Which Columns to Index