Скачать презентацию Module 6 Planning Indexes Overview n Introduction
6f1179a79b0a3b881197f709263c1a24.ppt
- Количество слайдов: 32
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 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 … Map 127 128 129 130 … Heap Extent 127 01 Con … 01 Rudd … 01 Funk Akhtar … 0201 White … … Smith 0201 Funk … … Con 02 White … … … 01 Ota … … Rudd 0302 Barr. Akhtar … 01 Funk … 0302 Smith … … 01 White Smith 03 Durkin. . . … 02 Funk 0403. . . Jones… … 02 White … … … 03 Martin. . . 02 04 Lang Ota. . . … 03 Barr. . . 05…. . . … Smith … 03. . . Jones … … …. . . . 03. . . … …. . . Martin. . . … 04. . . …. . . . . … Extent 128 01 Dunn … 01 Rudd … 01 Randall … Akhtar 0201 White … … Smith 0201 Funk … … 02 Ota. Con … … 01 Ota … … … Rudd 0302 Barr. Akhtar … 01 Funk 0302 Smith … … 01 White … Smith 03 Slichter … 02 Funk 0403. . . Jones… …. . . 02 White … … … 03 Martin. . . … 02 Barr Ota 04 La. Brie. . . … 03… Smith 05…. . . Jones … 03 … …. . . Martin. . . … 04. . . … …. . . … First IAM Bit 1 1 0 1 Extent 129 01 Seattle… 01 Rudd … 01 Paris … Akhtar 0201 White … … Smith 0201 Funk … … Con 02 Tokyo … … … 01 Ota … … Rudd 0302 Barr. Akhtar … 01 Funk … 0302 Smith … … 01 White Smith 03 Atlanta. . . … 02 Funk 0403. . . Jones… … 02 White … … … 03 Martin. . . 03 Ota. . . 04. . . 02 Barr. . . … … …. . . … Smith … 03. . . … …. . . Jones … 03 … …. . . . … 04. . . Martin. . . … …. . . . . … Extent 130 01 Graff … 01 Rudd … 01 Bacon … Akhtar 0201 White … … Smith 0201 Funk … … Con 02 Koch. Akhtar … 01 Ota … … Rudd 0302 Barr Smith … 01 Funk… … 0302 Smith … … 01 White … 03. . . Funk 02 … 03. . . Jones… …. . . 02 White … 03 Martin. . . … … Ota 04. . . 02 Barr. . . … 03… Smith … … …. . . Jones… 03 … …. . . . … 03. . . … …. . . Martin. . . … 04. . . …. . . . . …
Finding Rows in a Heap with a Nonclustered Index sysindexes id indid = 2 root Akhtar. . . Martin firstname Page SELECT lastname, Page 28 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 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 : 706: 04 Matey 4: 706: 04 4 Ota 4: 707: 02 Ota Phua 4: 708: 02 Phua Rudd 4: 705: 01 Rudd 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 808 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 145 Page 141 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 … Jose Judy Mike … Leaf Level Lugo Kaethler (Clustered Key Value) Nash … Barr Kim Nagata O’Melia Clustered Index On Last Name Barr Cox Daum … Deanna Daum Don Hall Doug Hampton … … 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 root Akhtar. . . Martin Page 12 - Root Non-Leaf Level Page 28 Martin Smith. . . Non 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 1000 Total number of rows in table = 10000 = 10% Anderson Bill Low selectivity member_no last_name first_name 1 2. Randall Flood Number of rows meeting criteria 9000 Total number of rows in table = 10000 = 90% SELECT * FROM member WHERE member_no < 9001 . . 10000 Joshua Kathie Anderson Bill
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
Lab A: Determining the Indexes of a Table
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