隨著企業(yè)收集和存儲的數(shù)據(jù)信息越來越多，企業(yè)的數(shù)據(jù)需要通過不斷優(yōu)化來適應(yīng)數(shù)據(jù)存儲的需要。中培偉業(yè)《SQL Server2014數(shù)據(jù)庫高級管理與性能調(diào)優(yōu)》培訓(xùn)專家姜老師對大數(shù)據(jù)量下的SQL Server數(shù)據(jù)庫自身優(yōu)化進(jìn)行了介紹。

1.1：增加次數(shù)據(jù)文件

從SQL SERVER 2005開始，數(shù)據(jù)庫不默認(rèn)生成NDF數(shù)據(jù)文件，一般情況下有一個主數(shù)據(jù)文件(MDF)就夠了，但是有些大型的數(shù)據(jù)庫，由于信息很多，而且查詢頻繁，所以為了提高查詢速度，可以把一些表或者一些表中的部分記錄分開存儲在不同的數(shù)據(jù)文件里

由于CPU和內(nèi)存的速度遠(yuǎn)大于硬盤的讀寫速度，所以可以把不同的數(shù)據(jù)文件放在不同的物理硬盤里，這樣執(zhí)行查詢的時候，就可以讓多個硬盤同時進(jìn)行查詢，以充分利用CPU和內(nèi)存的性能，提高查詢速度。 在這里詳細(xì)介紹一下其寫入的原理，數(shù)據(jù)文件(MDF、NDF)和日志文件(LDF)的寫入方式是不一樣的：

數(shù)據(jù)文件：SQL Server按照同一個文件組里面的所有文件現(xiàn)有空閑空間的大小，按這個比例把新的數(shù)據(jù)分布到所有有空間的數(shù)據(jù)文件里，如果有三個數(shù)據(jù)文件A.MDF，B.NDF，C.NDF，空閑大小分別為200mb，100mb，和50mb，那么寫入一個70mb的東西，他就會向ABC三個文件中一次寫入40、20、10的數(shù)據(jù)，如果某個日志文件已滿，就不會向其寫入

日志文件：日志文件是按照順序?qū)懭氲模粋€寫滿，才會寫入另外一個。由上可見，如果能增加其數(shù)據(jù)文件NDF,有利于大數(shù)據(jù)量的查詢速度，但是增加日志文件卻沒什么用處。

1.2：設(shè)置文件自動增長(大數(shù)據(jù)量，小數(shù)據(jù)量無需設(shè)置)

在SQL Server 2005中，默認(rèn)MDF文件初始大小為5MB，自增為1MB，不限增長，LDF初始為1MB，增長為10%，限制文件增長到一定的數(shù)目，一般設(shè)計中，使用SQL自帶的設(shè)計即可，但是大型數(shù)據(jù)庫設(shè)計中，最好親自去設(shè)計其增長和初始大小，如果初始值太小，那么很快數(shù)據(jù)庫就會寫滿，如果寫滿，在進(jìn)行插入會是什么情況呢？當(dāng)數(shù)據(jù)文件寫滿，進(jìn)行某些操作時，SQL Server會讓操作等待，直到文件自動增長結(jié)束了，原先的那個操作才能繼續(xù)進(jìn)行。如果自增長用了很長時間，原先的操作會等不及就超時取消了(一般默認(rèn)的閾值是15秒)，不但這個操作會回滾，文件自動增長也會被取消。

1.3 數(shù)據(jù)和日志文件分開存放在不同磁盤上

數(shù)據(jù)文件和日志文件的操作會產(chǎn)生大量的I/O。在可能的條件下，日志文件應(yīng)該存放在一個與數(shù)據(jù)和索引所在的數(shù)據(jù)文件不同的硬盤上以分散I/O，同時還有利于數(shù)據(jù)庫的災(zāi)難恢復(fù)。

當(dāng)一個表的數(shù)據(jù)量太大的時候，我們最想做的一件事是什么？將這個表一分為二或者更多分，但是表還是這個表，只是將其內(nèi)容存儲分開，這樣讀取就快了N倍了

原理：表數(shù)據(jù)是無法放在文件中的，但是文件組可以放在文件中，表可以放在文件組中，這樣就間接實現(xiàn)了表數(shù)據(jù)存放在不同的文件中。能分區(qū)存儲的還有：表、索引和大型對象數(shù)據(jù)

SQL SERVER 2005中，引入了表分區(qū)的概念， 當(dāng)表中的數(shù)據(jù)量不斷增大，查詢數(shù)據(jù)的速度就會變慢，應(yīng)用程序的性能就會下降，這時就應(yīng)該考慮對表進(jìn)行分區(qū),當(dāng)一個表里的數(shù)據(jù)很多時，可以將其分拆到多個的表里,因為要掃描的數(shù)據(jù)變得更少 ,查詢可以更快地運行,這樣操作大大提高了性能,表進(jìn)行分區(qū)后，邏輯上表仍然是一張完整的表，只是將表中的數(shù)據(jù)在物理上存放到多個表空間(物理文件上)，這樣查詢數(shù)據(jù)時，不至于每次都掃描整張表。

優(yōu)化③：分布式數(shù)據(jù)庫設(shè)計

分布式數(shù)據(jù)庫系統(tǒng)是在集中式數(shù)據(jù)庫系統(tǒng)的基礎(chǔ)上發(fā)展起來的，理解起來也很簡單，就是將整體的數(shù)據(jù)庫分開，分布到各個地方，就其本質(zhì)而言，分布式數(shù)據(jù)庫系統(tǒng)分為兩種：1.數(shù)據(jù)在邏輯上是統(tǒng)一的，而在物理上卻是分散的，一個分布式數(shù)據(jù)庫在邏輯上是一個統(tǒng)一的整體，在物理上則是分別存儲在不同的物理節(jié)點上，我們通常說的分布式數(shù)據(jù)庫都是這種2.邏輯是分布的，物理上也是分布的，這種也成聯(lián)邦式分布數(shù)據(jù)庫，由于組成聯(lián)邦的各個子數(shù)據(jù)庫系統(tǒng)是相對“自治”的，這種系統(tǒng)可以容納多種不同用途的、差異較大的數(shù)據(jù)庫，比較適宜于大范圍內(nèi)數(shù)據(jù)庫的集成。

分布式數(shù)據(jù)庫較為復(fù)雜，在此不作詳細(xì)的使用和說明，只是舉例說明一下，現(xiàn)在分布式數(shù)據(jù)庫多用于用戶分區(qū)性較強的系統(tǒng)中，如果一個全國連鎖店，一般設(shè)計為每個分店都有自己的銷售和庫存等信息，總部則需要有員工，供應(yīng)商，分店信息等數(shù)據(jù)庫，這類型的分店數(shù)據(jù)庫可以完全一致，很多系統(tǒng)也可能導(dǎo)致不一致，這樣，各個連鎖店數(shù)據(jù)存儲在本地，從而提高了影響速度，降低了通信費用，而且數(shù)據(jù)分布在不同場地，且存有多個副本，即使個別場地發(fā)生故障，不致引起整個系統(tǒng)的癱瘓。 但是他也帶來很多問題，如：數(shù)據(jù)一致性問題、數(shù)據(jù)遠(yuǎn)程傳遞的實現(xiàn)、通信開銷的降低等，這使得分布式數(shù)據(jù)庫系統(tǒng)的開發(fā)變得較為復(fù)雜，只是讓大家明白其原理，具體的使用方式就不做詳細(xì)的介紹了。

優(yōu)化④：整理數(shù)據(jù)庫碎片

如果你的表已經(jīng)創(chuàng)建好了索引，但性能卻仍然不好，那很可能是產(chǎn)生了索引碎片，你需要進(jìn)行索引碎片整理。

什么是索引碎片?

由于表上有過度地插入、修改和刪除操作，索引頁被分成多塊就形成了索引碎片，如果索引碎片嚴(yán)重，那掃描索引的時間就會變長，甚至導(dǎo)致索引不可用，因此數(shù)據(jù)檢索操作就慢下來了。

如何知道是否發(fā)生了索引碎片?

在SQLServer數(shù)據(jù)庫，通過DBCC ShowContig或DBCC ShowContig(表名)檢查索引碎片情況，指導(dǎo)我們對其進(jìn)行定時重建整理。

通過對掃描密度(過低)，掃描碎片(過高)的結(jié)果分析，判定是否需要索引重建，主要看如下兩個：

Scan Density [Best Count:Actual Count]-掃描密度[最佳值:實際值]：DBCC SHOWCONTIG返回最有用的一個百分比。這是擴展盤區(qū)的最佳值和實際值的比率。該百分比應(yīng)該盡可能靠近100%。低了則說明有外部碎片。

Logical Scan Fragmentation-邏輯掃描碎片：無序頁的百分比。該百分比應(yīng)該在0%到10%之間，高了則說明有外部碎片。

解決方式：

一是利用DBCC INDEXDEFRAG整理索引碎片

二是利用DBCC DBREINDEX重建索引。

兩者區(qū)別調(diào)用微軟的原話如下：

DBCC INDEXDEFRAG 命令是聯(lián)機操作，所以索引只有在該命令正在運行時才可用，而且可以在不丟失已完成工作的情況下中斷該操作。這種方法的缺點是在重新組織數(shù)據(jù)方面沒有聚集索引的除去/重新創(chuàng)建操作有效。

重新創(chuàng)建聚集索引將對數(shù)據(jù)進(jìn)行重新組織，其結(jié)果是使數(shù)據(jù)頁填滿。填滿程度可以使用 FILLFACTOR 選項進(jìn)行配置。這種方法的缺點是索引在除去/重新創(chuàng)建周期內(nèi)為脫機狀態(tài)，并且操作屬原子級。如果中斷索引創(chuàng)建，則不會重新創(chuàng)建該索引。也就是說，要想獲得好的效果，還是得用重建索引，所以決定重建索引。