【文章內(nèi)容簡介】 ，數(shù)據(jù)冗余、更新異 常、插入異常、刪除異常。所以邏輯數(shù)據(jù)庫設(shè)計(jì)至少應(yīng)滿足規(guī)范化的前 3 級標(biāo)準(zhǔn)： 第 1 規(guī)范：沒有重復(fù)的元組或多值的列。 第 2 規(guī)范：每個非關(guān)鍵字段必須依賴于主關(guān)鍵字，不能依賴于 1 個組合式主 關(guān)鍵字的某些組成部分。 第 3 規(guī)范：每個非關(guān)鍵字段必須依賴于主關(guān)鍵字，不能依賴于 1 個組合式主 關(guān)鍵字的某些組成部分，也不能依賴于另 1 個非關(guān)鍵字段。 遵守這些規(guī)則的設(shè)計(jì)會產(chǎn)生較少的列和更多的表，因而也就減少了數(shù)據(jù)冗余，也減少了用于存儲數(shù)據(jù)的頁。但表關(guān)系也許需要通過復(fù)雜的合并來處理，這樣會降低系統(tǒng)的性能 。某種程度上的非規(guī)范化可以改善系統(tǒng)的性能，非規(guī)范化過程可以根據(jù)性能方面不同的考慮用多種不同的方法進(jìn)行，所在在進(jìn)行數(shù)據(jù)庫邏輯結(jié)構(gòu)設(shè)計(jì)時應(yīng)綜合考慮數(shù)據(jù)冗余和基于連接的查詢帶來的性能問題。 數(shù)據(jù)庫物理存儲結(jié)構(gòu)的優(yōu)化 數(shù)據(jù)文件和日志文件的位置和分布對系統(tǒng)的性能非常重要。數(shù)據(jù)庫設(shè)計(jì)的兩 條關(guān)鍵性的指導(dǎo)原則如下：將序列訪問的文件分離到專用的磁盤上，并通過將數(shù) 據(jù)文件分散到各個磁盤上而允許并行 I/O。 ，特別是對于被索引的列更是如此。比如能使用 smallint 類型就不要用 int 類型，這樣索引 字段可以被更快地讀取，而且可以在 1 個數(shù)據(jù)頁上放置更多的數(shù)據(jù)行，因而也就 減少了 I/O 操作。 Server 分區(qū)技術(shù)把一個頻繁使用的大表分割開，并放在多個單獨(dú)的 智能型磁盤控制器的數(shù)據(jù)庫設(shè)備上，這樣也可以提高性能。因?yàn)橛卸鄠€磁頭在查 找，所以數(shù)據(jù)分離也能提高性能 ,SQL SERVER 的分區(qū)技術(shù)將在下面講到。 Server 段把文本或圖像列的數(shù)據(jù)存放在 1 個單獨(dú)的物理設(shè)備上可以 提高性能。 1 個專用的智能型的控制器能進(jìn)一步提高性能。 使用分區(qū) 超大 型數(shù)據(jù)庫的大小常常達(dá)到數(shù)百 GB，有時甚至要用 TB 來計(jì)算。而單表的數(shù)據(jù)量往往會達(dá)到上億的記錄，并且記錄數(shù)會隨著時間而增長。這不但影響著數(shù)據(jù)庫的運(yùn)行效率，也增大數(shù)據(jù)庫的維護(hù)難度。除了表的數(shù)據(jù)量外，對表不同的訪問模式也可能會影響性能和可用性。這些問題都可以通過對大表進(jìn)行合理分區(qū)得到很大的改善。當(dāng)表和索引變得非常大時，分區(qū)可以將數(shù)據(jù)分為更小、更容易管理的部分來提高系統(tǒng)的運(yùn)行效率。如果系統(tǒng)有多個 CPU 或是多個磁盤子系統(tǒng)，可以通過并行操作獲得更好的性能。所以對大表進(jìn)行分區(qū)是處理海量數(shù)據(jù)的一種十分高效的方法。下面通過一個 具體實(shí)例，介紹如何創(chuàng)建和修改分區(qū)表，以及如何查看分區(qū)表。 分區(qū)功能是 SQL SERVER 2021 新增的一個功能，表分區(qū)分為水平分區(qū)和垂 直分區(qū)。水平分區(qū)將表分為多個表。每個表包含的列數(shù)相同，但是行更少。例如， 可以將一個包含十億行的表水平分區(qū)成 12 個表，每個小表表示特定年份內(nèi)一個 月的數(shù)據(jù)。任何需要特定月份數(shù)據(jù)的查詢只需引用相應(yīng)月份的表。而垂直分區(qū)則 是將原始表分成多個只包含較少列的表。水平分區(qū)是最常用分區(qū)方式，本文以水 平分區(qū)來介紹具體實(shí)現(xiàn)方法。 平分區(qū)常用的方法是根據(jù)時期和使用對數(shù)據(jù)進(jìn)行水平分區(qū)。 例如本文例子， 一個短信發(fā)送記錄表包含最近一年的數(shù)據(jù)，但是只定期訪問本季度的數(shù)據(jù)。在這 種情況下，可考慮將數(shù)據(jù)分成四個區(qū)，每個區(qū)只包含一個季度的數(shù)據(jù)。下面具體 介紹一下使用分區(qū)表的方法。 立分區(qū)表先要創(chuàng)建文件組，而創(chuàng)建多個文件組主要是為了獲得好的 I/O 平 衡。一般情況下，文件組數(shù)最好與分區(qū)數(shù)相同，并且這些文件組通常位于不同的 磁盤上。每個文件組可以由一個或多個文件構(gòu)成，而每個分區(qū)必須映射到一個文 件組。一個文件組可以由多個分區(qū)使用。為了更好地管理數(shù)據(jù)（例如，為了獲得 更精確的備份控制），對 分區(qū)表應(yīng)進(jìn)行設(shè)計(jì)，以便只有相關(guān)數(shù)據(jù)或邏輯分組的數(shù)據(jù)位于同一個文件組中。使用 ALTER DATABASE，添加邏輯文件組名： AER DATABASE[DeanDB]ADD FILEGROUP[FG1] DanDB 為數(shù)據(jù)庫名稱， FG1 文件組名。創(chuàng)建文件組后，再使用 ALTER DATABASE 將文件添加到該文件組中。 ATER DATABASE[DeanDB]ADD FILE(NAME=39。FG139。， FILENAME= 39。C:\DeanData\39。， SIZE=3072KB， FILEGROWTH=1024KB)TO FILEGROUP [FG1] 類 似的建立四個文件和文件組，并把每一個存儲數(shù)據(jù)的文件放在不同的磁盤 驅(qū)動器里。 創(chuàng)建分區(qū)表必須先確定分區(qū)的功能機(jī)制，表進(jìn)行分區(qū)的標(biāo)準(zhǔn)是通過分區(qū)函數(shù) 來決定的。創(chuàng)建數(shù)據(jù)分區(qū)函數(shù)有 RANGE“ LEFT|/RIGHT”兩種選擇。代表每個 邊界值在局部的哪一邊。例如存在四個分區(qū)，則定義三個邊界點(diǎn)值，并指定每個 值是第一個分區(qū)的上邊界 (LEFT)還是第二個分區(qū)的下邊界 (RIGHT)。代碼如下： CREATE PARTITION FUNCTION[SendSMSPF](datetime)AS RANGE RIGHT FOR VALUES(39。2021040139。， 39。2021070139。， 39。2021100139。) 創(chuàng)建分區(qū)函數(shù)后，必須將其與分區(qū)方案相關(guān)聯(lián)，以便將分區(qū)指向至特定的文 件組。就是定義實(shí)際存放數(shù)據(jù)的媒體與各數(shù)據(jù)塊的對應(yīng)關(guān)系。多個數(shù)據(jù)表可以共 用相同的數(shù)據(jù)分區(qū)函數(shù)，一般不共用相同的數(shù)據(jù)分區(qū)方案?？梢酝ㄟ^不同的分區(qū) 方案，使用相同的分區(qū)函數(shù)，使不同的數(shù)據(jù)表有相同的分區(qū)條件，但存放在不同 的媒介上。創(chuàng)建分區(qū)方案的代碼如下： CREATE PARTITION SCHEME[SendSMSPS] AS PARTITION[SendSMSPF] TO ([FG1]， [FG2]， [FG3]， [FG4]) 建立好分區(qū)函數(shù)和分區(qū)方案后，就可以創(chuàng)建分區(qū)表了。分區(qū)表是通過定義分 區(qū)鍵值和分區(qū)方案相聯(lián)系的。插入記錄時， SQL SERVER 會根據(jù)分區(qū)鍵值的不同， 通過分區(qū)函數(shù)的定義將數(shù)據(jù)放到相應(yīng)的分區(qū)。從而把分區(qū)函數(shù)、分區(qū)方案和分區(qū) 表三者有機(jī)的結(jié)合起來。創(chuàng)建分區(qū)表的代碼如下： CREATE TABLE SendSMSLog([ID][int]IDENTITY(1， 1)NOT NULL， [IDNum] [nvarchar](50)NULL， [SendContent][text]NULL[SendDate][datetime]NOT NULL， )ON SendSMSPS(SendDate) 系統(tǒng)運(yùn)行一段時間或者把以前的數(shù)據(jù)導(dǎo)入分區(qū)表后，我們需要查看數(shù)據(jù)的具 體存儲情況，即每個分區(qū)存取的記錄數(shù)，那些記錄存取在那個分區(qū)等。我們可以 通過 $ 來查看，代碼如下： SELECT$()AS[Partition Number]， min()AS[Min SendDate]， max()AS[Max SendDate]， count(*)AS[Rows In Partition]FROM AS oGROUP BY $()ORDER BY[Partition Number] 分區(qū)的 維護(hù)主要設(shè)計(jì)分區(qū)的添加、減少、合并和在分區(qū)間轉(zhuǎn)換?？梢酝ㄟ^ ALTER PARTITION FUNCTION 的選項(xiàng) SPLIT， MERGE 和 ALTER TABLE 的選項(xiàng) SWITCH 來實(shí)現(xiàn)。 SPLIT 會多增加一個分區(qū)，而 MEGRE 會合并或者減少分區(qū)， SWITCH 則是邏輯地在組間轉(zhuǎn)換分區(qū)。 SQL 查詢語句的優(yōu)化 數(shù)據(jù)庫的優(yōu)化通?？梢酝ㄟ^對網(wǎng)絡(luò)、硬件、操作系統(tǒng)、數(shù)據(jù)庫參數(shù)和應(yīng)用程 序的優(yōu)化來進(jìn)行。最常見的優(yōu)化手段就是對硬件的升級。根據(jù)統(tǒng)計(jì)，對網(wǎng)絡(luò)、硬 件、操作系統(tǒng)、數(shù)據(jù)庫參數(shù)進(jìn)行優(yōu)化所獲得的性能提升，全部 加起來只占數(shù)據(jù)庫 系統(tǒng)性能提升的 40%左右，其余的 60%系統(tǒng)性能提升來自對應(yīng)用程序的優(yōu)化。許多優(yōu)化專家認(rèn)為，對應(yīng)用程序的優(yōu)化可以得到 80%的系統(tǒng)性能的提升。應(yīng)用程序的優(yōu)化通?？煞譃閮蓚€方面：源代碼和 SQL 語句。由于涉及到對程序邏輯的改變，源代碼的優(yōu)化在時間成本和風(fēng)險上代價很高，而對數(shù)據(jù)庫系統(tǒng)性能的提升收效有限。所以 SQL 語句的優(yōu)化對數(shù)據(jù)庫系統(tǒng)的性能起著決定性的作用。 SQL 語言是一種靈活的語言，相同的功能可以使用不同的語句來實(shí)現(xiàn)，但是語句的執(zhí)行效率是很不相同的。程序員可以通過查看查詢計(jì)劃來比較各種實(shí)現(xiàn)方案 ，并選出最優(yōu)的實(shí)現(xiàn)方案?？偟脕碇v，程序員寫 SQL 語句需要滿足考慮如下規(guī)則： 合理使用索引 索引是一種供服務(wù)器在表中快速查找一個行的數(shù)據(jù)庫結(jié)構(gòu)。 在關(guān)系數(shù)據(jù)庫中，一個行的物理位置無關(guān)緊要，除非數(shù)據(jù)庫需要找到它。為 了能找到數(shù)據(jù)。表中的每一行均用一個 ROWID 來標(biāo)識， ROWID 告訴數(shù)據(jù)庫這一行的準(zhǔn)確位置 (指出行所在的文件、該文件中的塊、該塊中的行地址 )。每一個索引條目都由一個鍵值和 ROWID 組成。可以索引一個列或一組列， SQL SERVER用 B 樹結(jié)構(gòu)存儲索引條目，以保證用最短路徑訪問鍵值，當(dāng)一個查 詢訪問索引時，就能找到與查詢條件相匹配的索引條目。與條目相匹配的 ROWID 值指向SQL SERVER 提供相關(guān)行的物理位置，以減輕定位數(shù)據(jù)所需要的 I/O 負(fù)擔(dān)。 從大多數(shù)系統(tǒng)應(yīng)用實(shí)例來看，查詢操作在各種數(shù)據(jù)庫的操作中，所占的比重 最大。索引是數(shù)據(jù)庫的重要的數(shù)據(jù)結(jié)構(gòu) ,它的根本目的就是為了提高查詢速度 。實(shí)踐證明在數(shù)據(jù)庫編程中，合理使用索引文件，對于改善數(shù)據(jù)查詢速度有著舉 足輕重的作用，可以極大改善數(shù)據(jù)庫應(yīng)用程序的性能。 在應(yīng)用過程中，用戶的查詢條件可能是多種多樣的，如果能根據(jù)用戶查詢建 立和選擇索引，對于保證應(yīng) 用程序的性能很有幫助。 ，一般使用的原則為： (1)為主鍵所在的列創(chuàng)建索引。為經(jīng)常進(jìn)行連接，但是沒有指定為外鍵的字 段建立索引，而不經(jīng)常連接的字段由數(shù)據(jù)庫優(yōu)化器自動建立索引。 (2)在頻繁進(jìn)行排序或分組 (即使用 GROUP BY 或 ORDER BY 操作 )的字段上建立索引。 (3)在條件表達(dá)式中經(jīng)常用到的不同值較多的字段上建立索引，在不同值較少 的字段上不要建立索引，如性別字段就不需要建立索引。不要在在查詢很少引用 的列上創(chuàng)建索引。 (4)如果經(jīng)常同時使用多個字段排序，可以在這些 字段上建立復(fù)合索引。要盡 量使關(guān)鍵查詢形成索引覆蓋，其前導(dǎo)字段一定是使用最頻繁的字段。 (5)查看索引并刪除不使用的索引，維護(hù)索引需要一定的硬盤空間和時間，需 要頻繁進(jìn)行數(shù)據(jù)查入操作的數(shù)據(jù)庫應(yīng)該有較少的索引，需要頻繁進(jìn)行讀取操作的 數(shù)據(jù)庫應(yīng)該有更多的索引。 (6)在聚集索引中，避免包括不必要的列，盡可能使用較小的數(shù)據(jù)類型，例如 用 VARCHAR 代替 CHAR。 (7)考慮使用聚集索引來支持排序和范圍查詢，在為數(shù)據(jù)檢索而優(yōu)化表的時 候，聚集索引應(yīng)該支持對記錄組的檢索。為了聚集鍵選擇列或列組，其中聚集鍵 根 據(jù)需要經(jīng)常排序的排序數(shù)據(jù)或?qū)Ρ仨氁黄鹪L問的記錄進(jìn)行分組。 (8)具有高選擇性的列是索引的好候選列，具有高密度的列是索引最糟糕的候 選列。 在創(chuàng)建索引后，必須維護(hù)索引以確保可以獲得最佳的性能。經(jīng)過一段時間后， 數(shù)據(jù)會變得支離破碎，要根據(jù)組織環(huán)境對數(shù)據(jù)碎片進(jìn)行整理。 SQL Server 2021 索引優(yōu)化向?qū)且粋€你會馬上喜歡上的工具。雖然并不完 美，但是這個工具可以評估查詢運(yùn)行性能，并基于查詢，提出數(shù)據(jù)表上是否該添 加聚集 /非聚集索引的建議。索引優(yōu)化向?qū)г?SQL Server 2021 事 件探查器的“工 具”菜單下可以找到。 使用索引優(yōu)化向?qū)?，首先要?chuàng)建工作負(fù)載。工作負(fù)載表示一個事件跟蹤或 者 TSQL 腳本。在很多情況下，你應(yīng)該選擇使用事件跟蹤，因?yàn)樗磻?yīng)了真實(shí)的數(shù)據(jù)庫行為。工作負(fù)載的創(chuàng)建必須能體現(xiàn)一段時間內(nèi)每天數(shù)據(jù)庫使用的情況。這樣，索引優(yōu)化向?qū)Ь涂梢曰谶@些實(shí)際運(yùn)行的統(tǒng)計(jì)行為來提出有用的建議。 當(dāng)工作負(fù)載創(chuàng)建后，索引優(yōu)化向?qū)Ь秃芨櫵Ｋ饕齼?yōu)化向?qū)龅木褪菑? 工作負(fù)載中提取行為樣本，然后利用查詢優(yōu)化器進(jìn)行分析。 一旦索引優(yōu)化向?qū)Х治鐾旯ぷ髫?fù)載后 (如果工作負(fù)載很大，這會運(yùn)行幾個小時 那么長 )，基于對工作負(fù)載的分析，提出最佳的聚集 /非聚集索引建議。另外，如 果數(shù)據(jù)庫上已經(jīng)有索引了，并且索引優(yōu)化向?qū)Оl(fā)現(xiàn)這些索引不是最佳的，那么會 建議你移除。 3 在數(shù)據(jù)庫中放置索引 用戶可以指定一個表的索引放置的位置。一個表的索引就該放置在與數(shù)據(jù)表 不同的物理磁盤上，這樣可以減少文件之間的磁盤爭用。 優(yōu)化 SQL 語句的一些具體策略 ，保證在實(shí)現(xiàn)功能的基礎(chǔ)上，盡量減少對數(shù)據(jù)庫的訪問次數(shù)； 通過搜索參數(shù)，盡量減少對表的訪問行數(shù)，最小化結(jié)果集，從而減輕網(wǎng)絡(luò)負(fù)擔(dān)； 能夠分開的操作盡量分 開處理，提高每次的響應(yīng)速度；在數(shù)據(jù)窗口使用 SQL 時， 盡量把使用的索引放在選擇的首列。 ；在查詢時，不要過多地使用通配符如 SELECT * FROM T1 語句，要用到幾列就選擇幾列，如： SELECT COL1， COL2 FROM T1；在可能的情況下盡量限制結(jié)果集行數(shù)，如： SELECT TOP 300 COL1， COL2， COL3 FROM T1，因?yàn)槟承┣闆r下用戶是不需要那么多的數(shù)據(jù)的。不要在應(yīng)用中使用數(shù)據(jù)庫游標(biāo)，游標(biāo)是非常有