數(shù)據(jù)庫(kù)實(shí)驗(yàn)心得-心得體會(huì)模板

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

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

3、即分成數(shù)個(gè) 64k的操作。因?yàn)?4 kb傳輸基本上與8 kb傳輸一樣快，而每次傳輸?shù)膕ql server 數(shù)據(jù) 是8 kb傳輸?shù)?倍。我們通過(guò)一個(gè)實(shí)例來(lái)看有and操作符時(shí)候的最常見(jiàn)的一種情況。我們有下 面一個(gè)表，create table dbo.member( member_ no dbo. nu meric_ididen tity(1,1) not n ull, last name dbo.shortstri ng not n ull,firstnamedbo.shortstring not null,middleinitialdbo.letternuII, streetdbo.shorts

4、tringnot nuII,city dbo.shortstringnot nu II, state_ prov dbo.statecode not n ull, co un trydbo.co un trycode not nu II, mail_code dbo.mailcode not nu II,phone_no dbo. phonenu mber nu II, p hotogra ph image n ull,issue_dt datetime not null default (getdate(),expr_dt datetimenot null default (dateadd(

5、year,1,getdate(), regi on_nodbo. nu meric_id not nu II, cor p_no dbo. nu meric_id nu II,p rev_bala nee mon ey n ull default (0), curr_bala nee mon ey nulldefault (0), member_code dbo.status_code not null default ( )這個(gè)表具備下面的四個(gè)索引:索引名細(xì)節(jié)索引的列member_corporati on _li nk non clustered located on p rimary co

6、rp_nomember_ident clustered,unique, primary key located on primary member_nomember_regio n_li nk non clustered located on p rimary regi on_nomemberfirst name non clustered located on p rimary first name k% and _no > 6 and _no當(dāng)我們執(zhí)行下面的sql查詢時(shí)候,select _no, , _nofrom as mwhere like6) b-這個(gè)查詢可以直接使用 memb

7、er_region_link 非聚集索引，而且這個(gè)非 聚集索引覆蓋了所有查詢列-實(shí)際執(zhí)行時(shí)，只需要 邏輯讀取10次where _no = _no不信，你可以看這兩個(gè)sql的執(zhí)行計(jì)劃，以及邏輯讀信息，都是一樣的。其實(shí)上面的sql，如果優(yōu)化成下面的方式，實(shí)際的邏輯讀消耗也是一樣的。為何 sql server 不會(huì)優(yōu)化成下面的方式。是因?yàn)?and操作符優(yōu)化的另外一個(gè)原則。1/26的數(shù)據(jù)和1/6的數(shù)據(jù)找交集的速度要比1/52的數(shù)據(jù)和1/3的數(shù) 據(jù)找交集速度要慢。'k%select _n o,_ no from(select _no, from as mwhere like 1/26 數(shù)據(jù))a,

8、(select _no, _no from as mwhere_no 6 and _no 6 and _no < 5000go_no > 6 and _no 6 and _no 6 and指定的一個(gè)或者多個(gè)索引名字。對(duì)于 0 ,這里index 計(jì)算符可以是0，1,1的意義如下：強(qiáng)制執(zhí)行聚集索引掃描，in dex(1)強(qiáng)制如果存在聚集索引，則in dex(0)執(zhí)行聚集索引掃描或查找(使用性能最高的一種)如果不存在聚集索引，則index(O) 強(qiáng)制執(zhí)行表掃描，index(1)被解釋為錯(cuò)誤?？偨Y(jié)知識(shí)點(diǎn):簡(jiǎn)單來(lái)說(shuō)，我們可以這么理解：sql server對(duì)于每一條查詢語(yǔ)句。會(huì)根據(jù) 實(shí)際索引情

9、況(sysindexes系統(tǒng)表中存儲(chǔ)這些信息)，分析每種組合可能的成本。 然后選擇它認(rèn)為成本最小的一種。作為它實(shí)際執(zhí)行的計(jì)劃。成本代價(jià)計(jì)算的一個(gè)主要組成部分是邏輯 i/o的數(shù)量，特別是對(duì)于單表的查詢。and操作要滿足所有條件，這樣，經(jīng)常會(huì)要求對(duì)幾個(gè)數(shù)據(jù)集作交集。數(shù)據(jù)集越 小，數(shù)據(jù)集的交集計(jì)算越節(jié)省成本。的項(xiàng)目中，竟然出現(xiàn)了濫用聚集索引的問(wèn)題。看來(lái)沒(méi)有培訓(xùn)最最基礎(chǔ)的索引的 意義，代價(jià)，使用場(chǎng)景，是一個(gè)非常大的失誤。這篇博客就是從這個(gè)角度來(lái)羅列 索引的基礎(chǔ)知識(shí)。使用索引的意義索引在數(shù)據(jù)庫(kù)中的作用類似于目錄在書(shū)籍中的作用，用來(lái)提高查找信息的速度。使用索引查找數(shù)據(jù)，無(wú)需對(duì)整表進(jìn)行掃描，可以快速找到所需

10、數(shù)據(jù)。使用索引的代價(jià)索引需要占用數(shù)據(jù)表以外的物理存儲(chǔ)空間。創(chuàng)建索引和維護(hù)索引要花費(fèi)一定的時(shí)間。當(dāng)對(duì)表進(jìn)行更新操作時(shí)，索引需要被重建，這樣降低了數(shù)據(jù)的維護(hù)速度。創(chuàng)建索引的列主鍵外鍵或在表聯(lián)接操作中經(jīng)常用到的列在經(jīng)常查詢的字段上最好建立索引不創(chuàng)建索引的列很少在查詢中被引用包含較少的惟一值 定義為text、ntext或者image數(shù)據(jù)類型的列heaps是staging data 的很好選擇，當(dāng)它沒(méi)有任何index時(shí)excellent for high performanee data loading (parallel bulk load and p arallel in dex creati on

11、 after load) excelle nt as a p artiti on to a p artiti oned view or a p artiti oned table聚集索引提高性能的方法，在前面幾篇博客中分別提到過(guò)，下面只是一個(gè) 簡(jiǎn)單的大綱，細(xì)節(jié)請(qǐng)參看前面幾篇博客。何時(shí)創(chuàng)建聚集索引?clustered index會(huì)提高大多數(shù)table的性能，尤其是當(dāng)它滿足以下條件時(shí):獨(dú)特，狹窄，靜止：最重要的條件 持續(xù)增長(zhǎng)的，最好是只向上增加。例如:ide ntity date, ide ntity guid (on ly whe n using n ewseque ntialid() fun ctio n)聚集索引唯一性（獨(dú)特型的問(wèn)題）由于聚集索引的b+樹(shù)