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

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

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

3、有創(chuàng)建計算列導(dǎo)致查詢不優(yōu)化 可調(diào)整的部分可調(diào)整的部分:網(wǎng)絡(luò)硬件操作系統(tǒng)數(shù)據(jù)庫應(yīng)用程序應(yīng)用程序應(yīng)用程序數(shù)據(jù)庫數(shù)據(jù)庫操作系統(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ù)庫參數(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 絕對不要使用壓縮 開關(guān)不必要的服務(wù)開

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é)議，多余的會拖跨系統(tǒng) 內(nèi)存設(shè)置內(nèi)存設(shè)置:由由SQL Server自動調(diào)整自動調(diào)整 考慮使用考慮使用“為為SQL Server保留物理內(nèi)保留物理內(nèi)存存” 考慮使用考慮使用“在在Windows上提升上提升SQL Server的優(yōu)先級的優(yōu)先級” 預(yù)設(shè)值是 0，也就是優(yōu)先權(quán)7。 啟動后 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)控防止超出成本的查詢查詢” 使用成本查詢管理者，做為防止查詢超過指定成本的選項。End 數(shù)據(jù)庫設(shè)計目標(biāo)：數(shù)據(jù)庫設(shè)計目標(biāo)： 以最有效率的方法將資料存放在硬盤上：成本上的考慮 最短回應(yīng)時間來存儲與取得資料：高效率的考率 如何達(dá)到這兩個要求呢？如何達(dá)到這兩個要求呢？ 辦法一：正規(guī)化辦法一：正規(guī)化 特性 去除重復(fù)不必要的資料（成本最低） 資料的完整性易易于維護(hù) 缺點(diǎn)： 資料分散到數(shù)個表格上

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

7、慮將不常用的列存到第二張表中。2345671ABE垂直分割垂直分割2345671CDF2345671ABCDEF過多的列過多的列End 數(shù)據(jù)的存儲方式數(shù)據(jù)的存儲方式 數(shù)據(jù)頁 數(shù)據(jù)盤區(qū):1 Extent = 8 Pages 數(shù)據(jù)的訪問方式數(shù)據(jù)的訪問方式 表掃描(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)做過物理排序，適合大量資料的處理

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

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

10、引 避免添加與已有的索引相似的索引，考慮建立復(fù)合索引 復(fù)合索引要盡量使關(guān)鍵查詢形成索引覆蓋，其前導(dǎo)列一定是使用最頻繁的列 不需要建立索引的列不需要建立索引的列 不常使用的列 包含大量重復(fù)數(shù)據(jù)的列 bit, text, image列上不能被建立索引 具有null值的列End DBCC CheckDB 在系統(tǒng)使用率較低時運(yùn)行 CHECKDB 一時檢查一張表 確保未同時執(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對一個或多個統(tǒng)計組（集合）有關(guān)鍵值分發(fā)的信息進(jìn)行更新 減少網(wǎng)絡(luò)使用，避免商業(yè)邏輯的運(yùn)作需要多次的網(wǎng)路來回往返呼叫，以及大量的資料

12、在網(wǎng)路上的傳輸 事物事物（Transaction）保持短小，避免）保持短小，避免Long Transaction 或是或是與與使用者互使用者互動，造動，造成死鎖成死鎖End 簡單監(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）的活動 SQL Profiler 取得最詳細(xì)的資料，查知每位使用者的活動 請利用不同機(jī)器來監(jiān)控 SQL Server物件物件計數(shù)器計數(shù)器指標(biāo)指標(biāo)Memory： Available KByres這個計數(shù)器應(yīng)該要大於這個計數(shù)器應(yīng)該要大於

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

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

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

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