數(shù)據(jù)庫(kù)性能優(yōu)化

1、技術(shù)支持中心目的目的 保證系統(tǒng)的高效運(yùn)行 日常工作符合高效要求（數(shù)據(jù)庫(kù)維護(hù)、程序開(kāi)發(fā)） 如何初步解決性能問(wèn)題對(duì)象對(duì)象熟悉Win Server及SQL Server，有操作經(jīng)驗(yàn)時(shí)間時(shí)間1.5小時(shí)產(chǎn)生系統(tǒng)性能的原因產(chǎn)生系統(tǒng)性能的原因系統(tǒng)優(yōu)化辦法系統(tǒng)優(yōu)化辦法數(shù)據(jù)庫(kù)系統(tǒng)性能調(diào)優(yōu)數(shù)據(jù)庫(kù)系統(tǒng)性能調(diào)優(yōu)數(shù)據(jù)庫(kù)性能調(diào)優(yōu)（數(shù)據(jù)庫(kù)設(shè)計(jì)）數(shù)據(jù)庫(kù)性能調(diào)優(yōu)（數(shù)據(jù)庫(kù)設(shè)計(jì)）程序調(diào)優(yōu)（程序調(diào)優(yōu)（SQL語(yǔ)言）語(yǔ)言）如何解決性能問(wèn)題如何解決性能問(wèn)題網(wǎng)絡(luò)問(wèn)題：網(wǎng)絡(luò)速度慢硬件問(wèn)題 Cpu 內(nèi)存：內(nèi)存不足內(nèi)存：內(nèi)存不足 硬盤：硬盤： I/O吞吐量小，形成了瓶頸效應(yīng)吞吐量小，形成了瓶頸效應(yīng)操作系統(tǒng)&數(shù)據(jù)庫(kù)系統(tǒng)應(yīng)用程序（程序設(shè)計(jì)、數(shù)據(jù)庫(kù)設(shè)計(jì)

2、、SQL語(yǔ)句） 沒(méi)有索引或者沒(méi)有用到索引沒(méi)有索引或者沒(méi)有用到索引(這是查詢慢最常見(jiàn)的問(wèn)題，是程序設(shè)計(jì)的缺這是查詢慢最常見(jiàn)的問(wèn)題，是程序設(shè)計(jì)的缺陷陷) 查詢出的數(shù)據(jù)量過(guò)大（可以采用多次查詢，其他的方法降低數(shù)據(jù)量）查詢出的數(shù)據(jù)量過(guò)大（可以采用多次查詢，其他的方法降低數(shù)據(jù)量） 鎖或者死鎖鎖或者死鎖(這也是查詢慢最常見(jiàn)的問(wèn)題，是程序設(shè)計(jì)的缺這也是查詢慢最常見(jiàn)的問(wèn)題，是程序設(shè)計(jì)的缺陷陷)sp_lock,sp_who,活動(dòng)的用戶查看活動(dòng)的用戶查看,原因是讀寫競(jìng)爭(zhēng)資源。原因是讀寫競(jìng)爭(zhēng)資源。 返回了不必要的行和列返回了不必要的行和列 查詢語(yǔ)句不好，沒(méi)有優(yōu)化查詢語(yǔ)句不好，沒(méi)有優(yōu)化 沒(méi)有創(chuàng)建計(jì)算列導(dǎo)致查詢不優(yōu)化沒(méi)

3、有創(chuàng)建計(jì)算列導(dǎo)致查詢不優(yōu)化 可調(diào)整的部分可調(diào)整的部分:網(wǎng)絡(luò)硬件操作系統(tǒng)數(shù)據(jù)庫(kù)應(yīng)用程序應(yīng)用程序應(yīng)用程序數(shù)據(jù)庫(kù)數(shù)據(jù)庫(kù)操作系統(tǒng)操作系統(tǒng)硬件硬件網(wǎng)絡(luò)網(wǎng)絡(luò) 為什么需要講系統(tǒng)的設(shè)置？為什么需要講系統(tǒng)的設(shè)置？ 微小的差異可能照成系統(tǒng)性能的巨大差別 內(nèi)容內(nèi)容 操作系統(tǒng)安裝設(shè)置 數(shù)據(jù)庫(kù)參數(shù)設(shè)置回想回想10秒鐘秒鐘 啟用網(wǎng)絡(luò)應(yīng)用模式為啟用網(wǎng)絡(luò)應(yīng)用模式為“最大化網(wǎng)絡(luò)應(yīng)用程最大化網(wǎng)絡(luò)應(yīng)用程序數(shù)據(jù)量吞吐量序數(shù)據(jù)量吞吐量” 避免講系統(tǒng)避免講系統(tǒng)pagefile.sys與與ms sql server檔案放在同一硬盤上檔案放在同一硬盤上 文件系統(tǒng)選擇文件系統(tǒng)選擇 安全與性能考慮：NTFS 絕對(duì)不要使用壓縮 開(kāi)關(guān)不必要的服務(wù)開(kāi)

4、關(guān)不必要的服務(wù) IIS Web Server,SMTP Virtual Server 不要與大量網(wǎng)絡(luò)流量應(yīng)用服務(wù)期安裝在同一機(jī)器中 主域控制器、Exchange Server、MSMQ、FTP Server、Print Server、MTS 通訊協(xié)議設(shè)定通訊協(xié)議設(shè)定 僅安裝需要用到的協(xié)議，多余的會(huì)拖跨系統(tǒng) 內(nèi)存設(shè)置內(nèi)存設(shè)置:由由SQL Server自動(dòng)調(diào)整自動(dòng)調(diào)整 考慮使用考慮使用“為為SQL Server保留物理內(nèi)保留物理內(nèi)存存” 考慮使用考慮使用“在在Windows上提升上提升SQL Server的優(yōu)先級(jí)的優(yōu)先級(jí)” 預(yù)設(shè)值是 0，也就是優(yōu)先權(quán)7。 啟動(dòng)后 SQL Server 將在 Mic

5、rosoft Windows NT 4.0 或 Windows 2000 排程器中以優(yōu)先權(quán)13 執(zhí)行。 專為執(zhí)行 SQL Server 的 Server 適用 考慮使用考慮使用 “使用查詢使用查詢調(diào)控防止超出成本的調(diào)控防止超出成本的查詢查詢” 使用成本查詢管理者，做為防止查詢超過(guò)指定成本的選項(xiàng)。End 數(shù)據(jù)庫(kù)設(shè)計(jì)目標(biāo)：數(shù)據(jù)庫(kù)設(shè)計(jì)目標(biāo)： 以最有效率的方法將資料存放在硬盤上：成本上的考慮 最短回應(yīng)時(shí)間來(lái)存儲(chǔ)與取得資料：高效率的考率 如何達(dá)到這兩個(gè)要求呢？如何達(dá)到這兩個(gè)要求呢？ 辦法一：正規(guī)化辦法一：正規(guī)化 特性 去除重復(fù)不必要的資料（成本最低） 資料的完整性易易于維護(hù) 缺點(diǎn)： 資料分散到數(shù)個(gè)表格上

6、，存取時(shí)必須進(jìn)行關(guān)聯(lián)（Join） 關(guān)聯(lián)（Join）會(huì)造成效能的下降，必要時(shí)進(jìn)行適當(dāng)?shù)娜萦?辦法二：數(shù)據(jù)分割辦法二：數(shù)據(jù)分割 水平分割：每個(gè)子表的結(jié)構(gòu)皆相同，但存放不同的資料內(nèi)容 例如：有一個(gè)包含一億條記錄的資料表，可以濾依其特性分成數(shù)個(gè)小的表231ABCDEF水平分割水平分割2345671ABCDEF記錄過(guò)多時(shí)記錄過(guò)多時(shí)564ABCDEF 垂直分割：以表垂直分割：以表數(shù)據(jù)列數(shù)據(jù)列做切做切割割成成為數(shù)個(gè)為數(shù)個(gè)小表格，以減少表小表格，以減少表格格寬寬度，降低度，降低I/O存取次存取次數(shù)數(shù) 故當(dāng)表越寬時(shí)，其所占用的頁(yè)（Data Page）會(huì)更多，將使I/O存取次數(shù)大增 例如：包含有過(guò)多列的表，可以考

7、慮將不常用的列存到第二張表中。2345671ABE垂直分割垂直分割2345671CDF2345671ABCDEF過(guò)多的列過(guò)多的列End 數(shù)據(jù)的存儲(chǔ)方式數(shù)據(jù)的存儲(chǔ)方式 數(shù)據(jù)頁(yè) 數(shù)據(jù)盤區(qū):1 Extent = 8 Pages 數(shù)據(jù)的訪問(wèn)方式數(shù)據(jù)的訪問(wèn)方式 表掃描(Table Scan)：速度很慢，和文件差別不大 Indexes are B-Trees Structures Which Allow Fast Access to Data 建立適當(dāng)?shù)乃饕梢詼p少I/O存取的次數(shù)AAA E IJ K LMJMM N Q 聚集聚集（Clustered）Index 資料已經(jīng)做過(guò)物理排序，適合大量資料的處理

8、每一個(gè)資料表只能有一個(gè)聚集索引 非非聚集聚集（Non-Clustered）Index 不做物理排序，所以在一張表中可以有250個(gè) 適用與資料密度低的列，也就是 WHERE 條件時(shí)具有高選擇性（SELECTIVITY） 復(fù)復(fù)合（合（Composited） Index 所要用到的列都已經(jīng)包含在索引頁(yè)（Index Page）中，故不需要去到物理的資料頁(yè)（Data Page）一筆一筆去找 為特定查詢命令量身定作用 特性：特性： 索引可以迅速的找到需要的記錄 建立適當(dāng)?shù)乃饕梢詼p少I/O存取的次數(shù) 正確的索引設(shè)計(jì)對(duì)性能的影響至管重要正確的索引設(shè)計(jì)對(duì)性能的影響至管重要 索引需要另外的管理開(kāi)銷索引需要另外的

9、管理開(kāi)銷 管理的成本 存儲(chǔ)的成本 增加了數(shù)據(jù)插入/修改的成本 沒(méi)有沒(méi)有適當(dāng)適當(dāng)可用的索引時(shí)可用的索引時(shí) 如：在具有大量重復(fù)數(shù)據(jù)的列上創(chuàng)建索引沒(méi)有價(jià)值 采用表掃描方式（Table Scan） 索引存在，SQL Server也不一定使用 結(jié)論結(jié)論 索引不是越多越好，一般一張表不要超過(guò)5個(gè) 不要在不合適的列上建立索引 一句話：在合適的列上建立合適的索引 建立索引的原則建立索引的原則 PK/FK可以建立索引 有大量非重復(fù)值、且經(jīng)常有范圍查詢（between, ,=, =）和order by、group by發(fā)生的列，可考慮建立群集索引 經(jīng)常進(jìn)行范圍查找，排序的列適合建立索引 基數(shù)較大的列很適合用來(lái)做索

10、引 避免添加與已有的索引相似的索引，考慮建立復(fù)合索引 復(fù)合索引要盡量使關(guān)鍵查詢形成索引覆蓋，其前導(dǎo)列一定是使用最頻繁的列 不需要建立索引的列不需要建立索引的列 不常使用的列 包含大量重復(fù)數(shù)據(jù)的列 bit, text, image列上不能被建立索引 具有null值的列End DBCC CheckDB 在系統(tǒng)使用率較低時(shí)運(yùn)行 CHECKDB 一時(shí)檢查一張表 確保未同時(shí)執(zhí)行其它磁盤 I/O 操作，例如磁盤備份 避免運(yùn)行占用大量 CPU 的查詢或批處理作業(yè) 詳細(xì)使用方法可參考SQL Server聯(lián)機(jī)文檔 DBCC CHECKTABLE DROP_EXISTING option saves work A

11、LTER TABLE isnt needed to remove constraints Nonclustered indexes dont need to be rebuilt twice DBCC DBREINDEX 索引重建 例：DBCC DBREINDEX (zy_fymx,0) DBCC INDEXDEFRAG 碎片整理 例：DBCC INDEXDEFRAG (0,zy_fymx,1)1,25,63,41,2,34,5,6 UPDATE STATISTICS對(duì)一個(gè)或多個(gè)統(tǒng)計(jì)組（集合）有關(guān)鍵值分發(fā)的信息進(jìn)行更新 減少網(wǎng)絡(luò)使用，避免商業(yè)邏輯的運(yùn)作需要多次的網(wǎng)路來(lái)回往返呼叫，以及大量的資料

12、在網(wǎng)路上的傳輸 事物事物（Transaction）保持短小，避免）保持短小，避免Long Transaction 或是或是與與使用者互使用者互動(dòng)，造動(dòng)，造成死鎖成死鎖End 簡(jiǎn)單監(jiān)視簡(jiǎn)單監(jiān)視:任務(wù)管理器任務(wù)管理器 系統(tǒng)監(jiān)視系統(tǒng)監(jiān)視器（器（System Monitor） 監(jiān)控Server I/O 內(nèi)存使用 使用者連接 鎖定（Lock） 復(fù)寫（Replication）的活動(dòng) SQL Profiler 取得最詳細(xì)的資料，查知每位使用者的活動(dòng) 請(qǐng)利用不同機(jī)器來(lái)監(jiān)控 SQL Server物件物件計(jì)數(shù)器計(jì)數(shù)器指標(biāo)指標(biāo)Memory： Available KByres這個(gè)計(jì)數(shù)器應(yīng)該要大於這個(gè)計(jì)數(shù)器應(yīng)該要大於

13、5,000 KB，當(dāng)這個(gè)值，當(dāng)這個(gè)值小於小於 5,000 KB 時(shí)，沒(méi)有其他的記憶體可以讓時(shí)，沒(méi)有其他的記憶體可以讓 SQL Server 使用來(lái)作最佳化執(zhí)行的動(dòng)作。使用來(lái)作最佳化執(zhí)行的動(dòng)作。Memory： Page/sec這個(gè)數(shù)值不應(yīng)該持續(xù)地大於零，若持續(xù)地大這個(gè)數(shù)值不應(yīng)該持續(xù)地大於零，若持續(xù)地大於零表示於零表示 Windows 作業(yè)系統(tǒng)一直在用作業(yè)系統(tǒng)一直在用 paging file 來(lái)完成記憶體的需求來(lái)完成記憶體的需求Page Faults/sec 可確認(rèn)磁碟活動(dòng)並非分頁(yè)可確認(rèn)磁碟活動(dòng)並非分頁(yè) (Page) 所造成。所造成。1. 處理序處理序 (Process) 設(shè)定使用太多記憶體。設(shè)定

14、使用太多記憶體。2. 檔案系統(tǒng)檔案系統(tǒng) (File System) 活動(dòng)?；顒?dòng)。 性能對(duì)象性能對(duì)象計(jì)數(shù)計(jì)數(shù)器器指指標(biāo)標(biāo)Processor：% Processor Time(指處理器執(zhí)行非閑置線程指處理器執(zhí)行非閑置線程時(shí)間的百分比時(shí)間的百分比)這個(gè)計(jì)數(shù)器應(yīng)該小於百分之九這個(gè)計(jì)數(shù)器應(yīng)該小於百分之九十。如果計(jì)數(shù)器等於百分之百十。如果計(jì)數(shù)器等於百分之百就必須減低工作量，以增加工就必須減低工作量，以增加工作的執(zhí)行效率，或是增加處理作的執(zhí)行效率，或是增加處理器的數(shù)目或能力器的數(shù)目或能力System： Processor Queue Length(指處理列隊(duì)中的線程數(shù)指處理列隊(duì)中的線程數(shù))如果處理器列隊(duì)中總

15、是有兩個(gè)如果處理器列隊(duì)中總是有兩個(gè)以上的線程以上的線程 通常表示處理器通常表示處理器堵塞。堵塞。若果若果持續(xù)持續(xù)地大地大于于 2 表示表示要減少工作量，以增加工作的要減少工作量，以增加工作的執(zhí)執(zhí)行效率，或是增加行效率，或是增加處處理器的理器的數(shù)數(shù)目或能力。目或能力。性能對(duì)象性能對(duì)象計(jì)數(shù)計(jì)數(shù)器器指指標(biāo)標(biāo)PhysicalDisk： % Disk Time(所選磁盤驅(qū)動(dòng)器忙于為讀所選磁盤驅(qū)動(dòng)器忙于為讀或?qū)懭胝?qǐng)求提供服務(wù)所用的時(shí)間或?qū)懭胝?qǐng)求提供服務(wù)所用的時(shí)間的百分比的百分比)這這個(gè)計(jì)數(shù)個(gè)計(jì)數(shù)器器應(yīng)該要持續(xù)地應(yīng)該要持續(xù)地小小于于百分之九百分之九十十 PhysicalDisk: Avg. Disk Que

16、ue Length （讀取和寫入請(qǐng)讀取和寫入請(qǐng)求求(為所選磁盤在實(shí)例間隔中列隊(duì)為所選磁盤在實(shí)例間隔中列隊(duì)的的)的平均數(shù)）的平均數(shù)）等候的等候的 I/O 要求要求數(shù)數(shù)不不應(yīng)持續(xù)應(yīng)持續(xù)超超過(guò)實(shí)際硬過(guò)實(shí)際硬盤盤所包含所包含讀寫頭數(shù)讀寫頭數(shù)的的 1.5 到到 2 倍。大多倍。大多數(shù)數(shù)的磁的磁盤盤都有一都有一個(gè)讀寫頭個(gè)讀寫頭，而通常，而通常磁盤磁盤陣列陣列(RAID) 裝置裝置則則有多有多個(gè)讀寫頭個(gè)讀寫頭。 PhysicalDisk: Disk Reads/sec 取決取決與制造商提供的規(guī)格與制造商提供的規(guī)格 PhysicalDisk: Disk Writes/sec 取決取決與制造商提供的規(guī)格與制造商提供的規(guī)格允許系統(tǒng)管理員監(jiān)視允許系統(tǒng)管理員監(jiān)視SQL Server執(zhí)行個(gè)體執(zhí)行個(gè)體中的事件的中的事件的圖圖形化工