介紹索引訪問方法及索引優(yōu)化
索引是一個(gè)單獨(dú)的、物理的數(shù)據(jù)庫結(jié)構(gòu)，它是某個(gè)表中一列或若干列值的集合和相應(yīng)的指向表中物理標(biāo)識(shí)這些值的數(shù)據(jù)頁的邏輯指針清單。索引提供指向存儲(chǔ)在表的指定列中的數(shù)據(jù)值的指針，然后根據(jù)您指定的排序順序?qū)@些指針排序。數(shù)據(jù)庫使用索引的方式與您使用書籍中的索引的方式很相似：它搜索索引以找到特定值，然后順指針找到包含該值的行。
要了解索引訪問方法，首先要知道索引的結(jié)構(gòu)。
1.表和索引的結(jié)構(gòu)
頁
頁是sql server存儲(chǔ)數(shù)據(jù)的基本單位，大小為8kb，可以存儲(chǔ)表數(shù)據(jù)、索引數(shù)據(jù)、執(zhí)行計(jì)劃數(shù)據(jù)、分配位圖、可用空間信息。頁是sql server可以讀寫的最小I/O單位。即便是讀取一行數(shù)據(jù)，它也要把整個(gè)頁加載到緩存并從緩存中讀取數(shù)據(jù)。
區(qū)
區(qū)是由8個(gè)連續(xù)頁組成的分配單元。
堆
堆是指不含聚集索引的表，它的數(shù)據(jù)不按任何順序進(jìn)行存儲(chǔ)。
聯(lián)系一個(gè)堆中的數(shù)據(jù)的唯一結(jié)構(gòu)是被稱為索引分配映射（IAM）的一個(gè)位圖頁，當(dāng)掃描對(duì)象時(shí)，SQl server使用IAM頁來遍歷該對(duì)象的數(shù)據(jù)。
聚集索引：
它的葉級(jí)表中維護(hù)所有數(shù)據(jù)，按照索引鍵列的順序存儲(chǔ)在索引的葉級(jí)。在索引頁級(jí)別的上層，索引還維護(hù)著其他級(jí)別，每個(gè)級(jí)別都概況了它下面的級(jí)別，非葉級(jí)索引上的每一行指向它下一級(jí)別的整個(gè)頁。
堆上的非聚集索引：
與聚集索引的唯一區(qū)別是非聚集索引的葉級(jí)頁只包含索引鍵列和指向特定數(shù)據(jù)行的行定位符，稱為RID。當(dāng)通過索引查找到特定的數(shù)據(jù)行后，Sqlserver必須在seek操作之后執(zhí)行RID lookup操作，該操作用于讀取包含數(shù)據(jù)行的頁。
聚集表上的非聚集索引：
指向特定數(shù)據(jù)行的行定位符是聚集鍵的值，不是RID。
2.索引訪問方法
表掃描/無序聚集索引掃描
當(dāng)表中沒有索引時(shí)，連續(xù)的掃描表中的所有數(shù)據(jù)頁。SQl server將根據(jù)該表的IAM頁指示磁盤取數(shù)臂按物理順序掃描屬于該表的區(qū)。
當(dāng)表包含聚集索引時(shí)，所采取的方法將是無序聚集索引掃描。
示例sql：
- select orderid,custid,empid,shipperid,orderdate,filler from dbo.Orders
索引：
- CREATE CLUSTERED INDEX idx_cl_od ON dbo.Orders(orderdate);
表Orders結(jié)構(gòu):
- orderid,custid,empid,shipperid,orderdate,filler
覆蓋非聚集索引掃描
Sql server 只訪問索引數(shù)據(jù)就可以找到滿足查詢所需的全部數(shù)據(jù)，不需要訪問完整的數(shù)據(jù)行。
示例sql：
- select orderid from dbo.Orders
索引：
- ALTER TABLE [dbo].[Orders] ADD CONSTRAINT [PK_Orders] PRIMARY KEY NONCLUSTERED
- (
- [orderid] ASC
- )
有序聚集索引掃描
按照鏈接列表對(duì)聚集索引葉級(jí)執(zhí)行的完整掃描 操作。
示例sql：
- select orderid,custid,empid,shipperid,orderdate,filler from dbo.Orders order by orderdate
索引：
- CREATE CLUSTERED INDEX idx_cl_od ON dbo.Orders(orderdate);
不同于無序索引掃描，有序掃描的性能取決于索引的碎片級(jí)別。
有序覆蓋非聚集索引掃描
與有序聚集索引掃描類似，但是覆蓋非聚集索引掃描時(shí)，因?yàn)樗婕案俚捻摚某杀究隙ū染奂饕饕龗呙枰汀?/p>
示例sql：
- select orderid, orderdate from dbo.Orders order by orderid
非聚集索引索引查找+有序局部掃描+lookups
通常用于小范圍查詢，且用到的非聚集索引沒有覆蓋該查詢。
示例sql：
- select orderid,custid,empid,shipperid,orderdate,filler
- from dbo.Orders where orderid between 101 and 200
無序非聚集索引掃描 + lookups
通常符合以下情況時(shí)，優(yōu)化器會(huì)選擇此種訪問方法：
- 該查詢的選擇性足夠高
- 最適合某查詢的索引并不覆蓋該查詢
- 索引沒有按順序維護(hù)被查找鍵
示例sql：
- select orderid,custid,empid,shipperid,orderdate,filler
- from dbo.Orders where custid = ‘’
聚集索引查找+有序局部掃描
對(duì)于按聚集索引的***個(gè)鍵列進(jìn)行篩選的范圍查詢，優(yōu)化器通常使用這種方法。
示例sql：
- select orderid,custid,empid,shipperid,orderdate,filler
- from dbo.Orders where orderdate = ‘20060212’
這種方法的好處是不涉及l(fā)ookups.
覆蓋非聚集索引查找+有序局部掃描
訪問方法與上一個(gè)類似，唯一的區(qū)別是非聚集索引。相對(duì)于上一個(gè)訪問方法，這個(gè)方法的好處在于非聚集索引的的葉級(jí)頁比聚集索引的葉級(jí)頁能夠容納更多的行。
示例sql:
- select shipperid,orderdate, custid from dbo.Orders
- Where shipperid='C' and orderdate >='20060101' and orderdate <'20070101'
- CREATE NONCLUSTERED INDEX idx_nc_sid_od_cid
- ON dbo.Orders(shipperid, orderdate, custid);
3.索引優(yōu)化等級(jí)
需要優(yōu)化的sql：select orderid,custid,empid,shipperid,orderdate,filler from dbo.Orders where orderid > 999001
1.這個(gè)表沒有任何索引：該計(jì)劃將使用表掃描
2.接下來優(yōu)化，創(chuàng)建一個(gè)非聚集覆蓋索引，且不把篩選列（orderid）作為***個(gè)篩選列：
- CREATE INDEX idx_nc_od_i_oid_cid_eid_sid
- ON performance.dbo.Orders(orderdate)
- include(orderid,custid,empid,shipperid);
優(yōu)化器將采用覆蓋非聚集索引掃描
3.下一步優(yōu)化：創(chuàng)建一個(gè)不覆蓋該查詢的非聚集索引
- CREATE NONCLUSTERED INDEX idx_nc_od_i_oid
- ON dbo.Orders(orderdate)
- INCLUDE(orderid);
優(yōu)化器將采用非聚集索引掃描+lookup，這個(gè)查詢依賴于選擇性。選擇性越高，性能越高。
4.繼續(xù)優(yōu)化：在orderid上創(chuàng)建非聚集非覆蓋索引，
- CREATE UNIQUE NONCLUSTERED INDEX idx_unc_oid
- ON dbo.Orders(orderid);
優(yōu)化器將采用非聚集索引查找+lookup
5.繼續(xù)優(yōu)化：在orderid上創(chuàng)建聚集索引
- CREATE UNIQUE CLUSTERED INDEX idx_cl_oid ON dbo.Orders(orderid);
這個(gè)計(jì)劃主要不涉及l(fā)ookup，
6.繼續(xù)優(yōu)化：
***優(yōu)化應(yīng)該是把orderid作為鍵列，并把其他列定義為包含性非鍵列的非聚集覆蓋索引。
- CREATE UNIQUE NONCLUSTERED INDEX idx_unc_oid_i_od_cid_eid_sid
- ON dbo.Orders(orderid)
- INCLUDE(orderdate, custid, empid, shipperid);
這個(gè)計(jì)劃的邏輯與上一個(gè)類似，只是非聚集覆蓋索引有序局部掃描讀取的頁更少。
【編輯推薦】
