資源描述：
《sql server 2005_索引》由會員上傳分享，免費在線閱讀，更多相關(guān)內(nèi)容在教育資源-天天文庫。
1、索引學(xué)習(xí)目標(biāo)索引的概念索引的類型索引的創(chuàng)建和維護(hù)SQLServer數(shù)據(jù)存儲結(jié)構(gòu)2-1數(shù)據(jù)文件結(jié)構(gòu)組成頁SQLServer最小存儲數(shù)據(jù)單位一個頁大小是8K區(qū)管理空間的基本單位8個物理上連續(xù)的頁（64KB）表被存儲在區(qū)SQLServer數(shù)據(jù)存儲結(jié)構(gòu)2-2頁：8KB區(qū)：64KB數(shù)據(jù)數(shù)據(jù)庫數(shù)據(jù)文件.mdf或.ndf事務(wù)日志文件.ldf表數(shù)據(jù)的查找數(shù)據(jù)如何查找表掃描使用索引數(shù)據(jù)頁Page7Page8Page9AkhtarFunkSmithMartin...Page4Page5Page6...............ConFunkWhite
2、......RuddWhiteBarr......SmithOtaJones......MartinPhuaJonesSmith...GanioJonesHall.................................................................................決定是否使用索引為什么創(chuàng)建索引加快存取速度強制數(shù)據(jù)行的唯一性索引的副作用占用空間降低了修改數(shù)據(jù)的速度索引的類型和特點在MicrosoftSQLServer2005系統(tǒng)中有兩種基本的索引類型：聚集索引聚集索引是一
3、種數(shù)據(jù)表的物理順序與索引順序相同的索引，非聚集索引則是一種數(shù)據(jù)表的物理順序與索引順序不相同的索引。非聚集索引在非聚集索引中，基礎(chǔ)表的數(shù)據(jù)行不是按照非聚集鍵的順序排序和存儲，且非聚集索引的葉級是由索引頁而不是由數(shù)據(jù)頁組成。堆堆是不含聚集索引的表，表中的數(shù)據(jù)沒有任何的順序。聚集索引的結(jié)構(gòu)示意圖在聚集索引上查找數(shù)據(jù)行ClusteredIndexPage140-RootPage100Page120Page130Page141Page145AkhtarBarrConFunkFunk...23345678253413341534......
4、...............MartinMartinOtaPhuaRudd...12347778587878786078.....................SmithSmithSmithWhiteWhite...14345778797822341634.....................AkhtarGanio…Akhtar…MartinMartinSmith…Page110GanioHallJonesJonesJones...76788078243459782634.....................SELE
5、CTlastname,firstnameFROMmemberWHERElastname='Ota'ClusteredIndexPage140-RootPage100Page120Page130Page141Page145AkhtarBarrConFunkFunk...23345678253413341534.....................MartinMartinOtaPhuaRudd...12347778587878786078.....................SmithSmithSmithWhiteWhite
6、...14345778797822341634.....................AkhtarGanio…Akhtar…MartinMartinSmith…Page110GanioHallJonesJonesJones...76788078243459782634.....................MartinOta5878...Martinidindid=1rootsysindexes非聚集索引的結(jié)構(gòu)示意圖在非聚集索引上查找數(shù)據(jù)行Non-LeafLevelPage12-RootPage37Page28LeafLev
7、el(KeyValue)Page41Page51Page61Page71Akhtar...MartinAkhtarBarrConFunkFunk4:706:014:705:034:704:014:706:024:704:02MartinSmith...SmithSmithSmithWhiteWhite4:706:034:708:044:707:014:704:034:705:02AkhtarGanio...GanioHallJonesJonesJones4:709:014:709:044:709:024:708:034:707:
8、03HeapPage707Page808Page70901020304..................AkhtarFunkSmithMatey...Page704Page705Page706010203.....................ConnFun
