數(shù)據(jù)庫實驗心得

1、數(shù)據(jù)庫實驗心得我在 sqlserver 索引基礎(chǔ)知識系列中 ,第一篇就講了記錄數(shù)據(jù)的基本 格式。那里主要講解的是，數(shù)據(jù)庫的最小讀存單元：數(shù)據(jù)頁。 一個數(shù)據(jù)頁是 8k 大小。對于數(shù)據(jù)庫來說， 它不會每次有一個數(shù)據(jù)頁變化后， 就存到硬盤。 而是變化達(dá)到一定數(shù)量級后才會作這個操作。這時候，數(shù)據(jù)庫并不是以數(shù)據(jù)頁來作為操作單元，而是以 64k 的 數(shù)據(jù)（8 個數(shù)據(jù)頁，一個區(qū)）作為操作單元。區(qū)是管理空間的基本單位。 一個區(qū)是八個物理上連續(xù)的頁（即 64kb）。這意味著sqlserver數(shù)據(jù)庫中每mb有16個區(qū)。 為了使空間分配更有效， sqlserver 不會將所有區(qū)分配給包含少量 數(shù)據(jù)的表。sqlser

2、ver有兩種類型的區(qū)：統(tǒng)一區(qū)，由單個對象所有。區(qū)中的所有 8頁只能由所屬對象使用。 混合區(qū)，最多可由八個對象共享。區(qū)中八頁的每頁可由不同的對象所有。 通常從混合區(qū)向新表或索引分配頁。當(dāng)表或索引增長到 8 頁時，將變成使用統(tǒng)一區(qū)進(jìn)行后續(xù)分配。如果對現(xiàn)有表創(chuàng)建索引，并且該表包含的行足以在索引中生成頁，則對該索引的所有分配都使用統(tǒng)一區(qū)進(jìn)行為何會這樣呢？其實很簡單：讀或?qū)?8kb 的時間與讀或?qū)?64kb 的 時間幾乎相同。在 8kb 到 64kb 范圍之內(nèi)，單個磁盤 i/o 傳輸操作所花的時間主要 是磁盤取數(shù)臂和讀 / 寫磁頭運動的時間。因此，從數(shù)學(xué)上來講，當(dāng)需要傳輸 64kb 以上的 sql 數(shù)據(jù)

3、時，盡可 能地執(zhí)行 64kb 磁盤傳輸是有益的，即分成數(shù)個 64k 的操作。因為 64kb 傳輸基本上與 8kb 傳輸一樣快，而每次傳輸?shù)?sqlserver 數(shù)據(jù)是8kb傳輸?shù)?倍。我們通過一個實例來看有 and 操作符時候的最常見的一種情況。 我們有下面一個表， createtabledbo.member(member_nodbo.numeric_ididentity(1,1)n otnull,lastnamedbo.shortstringnotnull,firstnamedbo.shortstringn otnull,middleinitialdbo.letternull,streetd

4、bo.shortstringnotnull,cit ydbo.shortstringnotnull,state_provdbo.statecodenotnull,country dbo.countrycodenotnull,mail_codedbo.mailcodenotnull,phone_n odbo.phonenumbernull,photographimagenull,issue_dtdatetime notnulldefault(getdate(),expr_dtdatetimenotnulldefault(dateadd(year, 1,getdate(),region_nodbo

5、.numeric_idnotnull,corp_nodbo.numer ic_idnull,prev_balancemoneynulldefault(0),curr_balancemoneynull default(0),member_codedbo.status_codenotnulldefault('') 這 個表 具 備下面的四個索引：索引名細(xì)節(jié)索引的列 member_corporation_linknonclusteredlocatedonprimarycorp_nomember _identclustered,unique,primarykeylocatedonpri

6、marymember_nomember _region_linknonclusteredlocatedonprimaryregion_nomemberfirstnameno nclusteredlocatedonprimaryfirstname 當(dāng)我們執(zhí)行下面的 sql 查詢時候， selectm.member_no,m.firstname,m.region_nofromdbo.memberasmwher em.firstnamelike'k%'andm.region_no>6andm.member_no sqlserver 會根據(jù)索引方式，優(yōu)化成下面方式來執(zhí)行。sele

7、cta.member_no,a.firstname,b.region_nofrom(selectm.member_no,m. firstnamefromdbo.memberasmwherem.firstnamelike'k%'andm.member_n o6)b-這個查詢可以直接使用 member_region_link 非聚集索引，而且這 個非聚集索引覆蓋了所有查詢列 -實際執(zhí)行時，只需要邏輯讀取10 次wherea.member_no=b.member_no 不信，你可以看這兩個 sql 的執(zhí)行計 劃，以及邏輯讀信息，都是一樣的。其實上面的sql,如果優(yōu)化成下面的方式，實際

8、的邏輯讀消耗也是 一樣的。為何sqlserver不會優(yōu)化成下面的方式。是因為 and 操作符優(yōu)化的另外一個原則。1/26的數(shù)據(jù)和 1/6的數(shù)據(jù)找交集的速度要比 1/52 的數(shù)據(jù)和 1/3 的 數(shù)據(jù)找交集速度要慢。 selecta.member_no,a.firstname,b.region_nofrom(selectm.member_no,m.firstnamefromdbo.memberasmwherem.firstnamelike'k%'-1/26數(shù)據(jù) )a,(selectm.member_no,m.region_nofromdbo.memberasmwherem.regi

9、 on_no>6andm.member_no6andm.member_no6andm.member_no6andm .member_no6andm.member_no 對于 0，1 的意義如下：如果存在 聚集索引，則index(O)強制執(zhí)行聚集索引掃描，index(1)強制執(zhí)行聚集索 引掃描或查找(使用性能最高的一種) 。如果不存在聚集索引，則index(0)強制執(zhí)行表掃描，index(1)被解釋 為錯誤?？偨Y(jié)知識點：簡單來說，我們可以這么理解： sqlserver 對于每一 條查詢語句。會根據(jù)實際索引情況(sysindexes系統(tǒng)表中存儲這些信息)，分析 每種組合可能的成本。然后選擇它

10、認(rèn)為成本最小的一種。作為它實際執(zhí)行的計劃。 成本代價計算的一個主要組成部分是邏輯 i/o 的數(shù)量，特別是對于 單表的查詢。and 操作要滿足所有條件， 這樣，經(jīng)常會要求對幾個數(shù)據(jù)集作交集。 數(shù)據(jù)集越小，數(shù)據(jù)集的交集計算越節(jié)省成本。的項目中，竟然出現(xiàn)了濫用聚集索引的問題?？磥頉]有培訓(xùn)最最基礎(chǔ)的索引的意義，代價，使用場景，是一個 非常大的失誤。這篇博客就是從這個角度來羅列索引的基礎(chǔ)知識。 使用索引的意義索引在數(shù)據(jù)庫中的作用類似于目錄在書籍中的作 用，用來提高查找信息的速度。使用索引查找數(shù)據(jù)，無需對整表進(jìn)行掃描，可以快速找到所需數(shù) 據(jù)。使用索引的代價索引需要占用數(shù)據(jù)表以外的物理存儲空間。 創(chuàng)建索引和

11、維護(hù)索引要花費一定的時間。當(dāng)對表進(jìn)行更新操作時，索引需要被重建，這樣降低了數(shù)據(jù)的維 護(hù)速度。創(chuàng)建索引的列主鍵外鍵或在表聯(lián)接操作中經(jīng)常用到的列在經(jīng)常查 詢的字段上最好建立索引不創(chuàng)建索引的列很少在查詢中被引用包含較 少的惟一值定義為 text 、ntext 或者 image 數(shù)據(jù)類型的列 heaps 是 stagingdata 的 很 好 選 擇 ， 當(dāng) 它 沒 有 任 何 index 時 excellentforhighperformancedataloading(parallelbulkloadandparallelindex creationafterload)excellentasapartitiontoapartitionedvieworapartitionedta ble 聚集索引提高性能的方法，在前面幾篇博客中分別提到過，下面只 是一個簡單的大綱，細(xì)節(jié)請參看前面幾篇博客。何時創(chuàng)建聚集索引？ clusteredindex會提高大多數(shù)table的性能，尤 其是當(dāng)它滿足以下條件時： 獨特,狹窄 ,靜止：最重要的條件持續(xù)增長的，最好是只向上增加identitydate,identityguid(onlywhenusingnewsequentialid()function) 聚 集 索引唯一性(獨特型的問題)由于聚集