【導讀】人們在使用SQL時往往會陷入一個誤區(qū)，即太關注于所得的結果是否正確，不良的SQL往往來自于不恰當?shù)乃饕O計、不充份的連接條件和不可優(yōu)。在對它們進行適當?shù)膬?yōu)化后，其運行速度將有比較明顯的提高。性能可以得到最大程度的改進，并集中分析這些方面。否則，您在這些問題上可。能花費大量的時間和精力，而并不能得到明顯的性能上的提高。在開發(fā)工具、數(shù)據(jù)庫設計、應用程序的結構、查詢設計、接口選擇。等方面有多種選擇，這取決于特定的應用需求以及開發(fā)隊伍的技能。第一章綜述(緒論)------------------------------------------------------------------------4. 數(shù)據(jù)庫優(yōu)化概述---------------------------------------------------------------------8. 規(guī)范化與逆規(guī)范化------------------------------------------------------------------10. 第三章查詢優(yōu)化----------------------------------------------------------------------------15

　　

【正文】 21 第一章 綜述 SQL Server 是一個后臺數(shù)據(jù)庫管理系統(tǒng)，他功能強大、操作簡便，日益為廣大數(shù)據(jù)庫用戶所喜愛，越來越多的開發(fā)工具提供了與 SQL Server 的接口。了解和掌握 SQL Server 的功能，對于一個數(shù)據(jù)庫開發(fā)管理人員來說非常必要。 SQL Server 是一種高性能的大型關系型數(shù)據(jù)庫管理系統(tǒng)，廣泛的應用在 C/S和 B/S 體系結構的數(shù)據(jù)庫系統(tǒng)中。 評價系統(tǒng)性能優(yōu)化的標準有：吞吐量、響應時間、并行能力等。本文主要探討如何優(yōu)化 SQL，取得最快的系統(tǒng)響應速度。 引言 在一個大型的數(shù) 據(jù)庫中，性能成為人們關注的焦點之一，如何讓數(shù)據(jù)庫高效有效的運行成為廣大數(shù)據(jù)庫管理人員和開發(fā)人員必須要考慮的問題。 何謂“好性能” 性能是一個應用或多個應用在相同的環(huán)境下運行時對效率的衡量。性能常用響應時間和工作效率來表示。響應時間是指提交任務到得到返回結果之間的時間開銷，含完成一個任務花費的時間。可以從以下三方面來減少響應時間： 減少競爭和等待的次數(shù)，尤其是磁盤讀寫等待次數(shù) 用更快的部件 減少利用資源所需的時間 絕大多數(shù)性能的獲得來自于優(yōu)秀的數(shù)據(jù)庫設計、精確的查詢分析和適當?shù)乃饕?。最好性能的獲得 能夠通過確立優(yōu)秀的數(shù)據(jù)庫設計，在開發(fā)時使用 SQL Server查詢優(yōu)化器來實現(xiàn)。為了取得更好的數(shù)據(jù)庫性能，我們就需要對數(shù)據(jù)庫進行優(yōu)化，減少系統(tǒng)資源的競爭，如對數(shù)據(jù) cache,過程 cache，系統(tǒng)資源和 CPU的競爭。 數(shù)據(jù)庫優(yōu)化概述 在 SQL Server 中，有如下優(yōu)化層次 ： ? 應用層 —— 大部分性能的獲得來自于對 SQL 應用中查詢的優(yōu)化，這必須是以好的數(shù)據(jù)庫設計為基礎的。 ? 數(shù)據(jù)庫層 —— 應用共享在數(shù)據(jù)庫層中的資源，這些資源包括硬盤，事務日志和數(shù)據(jù) cache。 ? 服務器層 —— 在服務器層有許多共享的資源，包括數(shù)據(jù)高 速緩存，存儲過程高速緩存，鎖， CPU等。 ? 設備層 —— 指的是存儲數(shù)據(jù)的磁盤及其控制器，在這一層，你應尤其關注磁盤的 I／ O。 ? 網(wǎng)絡層 —— 指連接用戶和 SQL Server 的網(wǎng)絡。 ? 硬件層 —— 指可利用的 CPU。 ? 操作系統(tǒng)層 —— 理想地， SQL Server 是一臺機器的唯一主要應用，它必須和操作系統(tǒng)以及其他 sybase 軟件，如 Backup Server 或 SQL Server Monitor 共享處理器、內存以及其他資源。 在大多數(shù)情況下面，我們是對應用層進行優(yōu)化，因為對應用性能的優(yōu)化是設計和編程人員樂于接受的功能，其結果 能被觀測及檢驗。查詢的性能是 SQL 應用的整個性能的一個關鍵。 應用層上的問題包括以下內容： ? 決策支持 (OLTP)需要不同的性能策略 ? 事務設計能夠減少并發(fā)，因為長的事務保持占用鎖，也就減少了其他用戶對相關數(shù)據(jù)的存取 ? 關聯(lián)一致性保證的策略。對數(shù)據(jù)查詢修改時需要考慮 join 操作對性能的影響 ? 索引可以改善查詢性能，但也會增加修改數(shù)據(jù)的時間 ? 為了安全而設立的審計限制了性能 在應用層優(yōu)化的選項包括 ： ? 遠程處理或復制處理能夠把決策支持從 OLTP 機器中分離出來 ? 利用存儲過程來減少編譯時間和網(wǎng)絡的利用 ? 利用最少量的鎖去滿足你的應用需要 數(shù)據(jù)庫層的問題包括： ? 建立備份和恢復方案 ? 在設備上分布存儲數(shù)據(jù) ? 審計操作影響性能；僅審計你所需的 ? 日常的維護活動將導致性能的降低和導致用戶不能操作數(shù)據(jù)庫表 在數(shù)據(jù)庫層上優(yōu)化選擇包括： ? 利用事務日志的閥值來自動轉儲事務日志防止其超出使用空間 ? 在數(shù)據(jù)段中用閥值來監(jiān)視空間的使用 ? 利用分區(qū)來加速數(shù)據(jù)的裝入 ? 對象的定位以避免硬盤的競爭 ? 把重要表和索引放入 cache 中，保證隨時取得 服務器層的問題有： ? 應用的類型 —— 服務器是支持 OLTP 還是 DSS，或者兩者都支持 ? 所支持的用戶數(shù)影響優(yōu)化 決策 —— 隨著用戶數(shù)的增加，對資源的競爭會發(fā)生改變 ? 網(wǎng)絡負荷 ? 當用戶數(shù)和事務數(shù)達到一定的數(shù)量時復制服務器或其他分布式處理是一個解決的方法 服務器層的優(yōu)化的選項包括： ? 優(yōu)化內存 —— 一個關鍵的配置參數(shù)和其他方面的參數(shù) ? 決策是客戶端處理還是服務器端處理 —— 有些處理能在客戶端進行嗎 ? 配置 cache 的大小和 I／ O 的大小 ? 增加多個 CPU ? 為空閑時間排定批處理任務和生成報表 ? 工作負荷發(fā)生改變，重新配置特定參數(shù) ? 決定是否可能把 DSS 移到另一個 SQL 服務器中設備層 設備層的問題包括： ? 主設備、包含用戶數(shù)據(jù)庫的設備，用戶數(shù)據(jù)設備 ，或數(shù)據(jù)庫日志是否要鏡像 ? 怎樣在設備之間分布系統(tǒng)數(shù)據(jù)庫、用戶數(shù)據(jù)庫和數(shù)據(jù)庫日志 ? 為獲得對堆表插入操作的高性能，是否有必要進行分區(qū) 設備層上優(yōu)化的選項包括： ? 用多個中等大小的設備及多個控制器可能比用少量的大設備有更好的 I／ O 性能 ? 分布數(shù)據(jù)庫，表和索引以在不同的設備上進行 I／ O 裝載 網(wǎng)絡層 實際上， SQL Server 的所有用戶都是通過網(wǎng)絡存取他們的數(shù)據(jù)。網(wǎng)絡層上的主要問題有： ? 網(wǎng)絡的流量 ? 網(wǎng)絡的瓶頸 ? 網(wǎng)絡的速度 網(wǎng)絡層上優(yōu)化的選項包括： ? 配置包的大小，以使其與應用的需要相匹配 ? 配置子網(wǎng) ? 分隔出繁忙的網(wǎng)絡運用 ? 創(chuàng)建一個高容量的網(wǎng)絡 ? 配置多個網(wǎng)絡引擎 ? 更好地設計應用，限制所需的網(wǎng)絡傳輸 硬件層 在硬件層上的問題包括 ? CPU的效率 ? 磁盤的存?。嚎刂破骱痛疟P ? 磁盤備份 ? 內存的使用 在硬件層上優(yōu)化的選項包括： ? 增加 CPU以適應工作負荷 ? 配置調度程序以提高 CPU利用率 ? 遵循多處理器應用設計指導以減少競爭 ? 配置多個數(shù)據(jù) cache 操作系統(tǒng)層 操作系統(tǒng)層的主要問題有： ? 文件系統(tǒng) —— 是否被 SQL Server 獨占使用 ? 內存管理 —— 精確估算操作系統(tǒng)和其他程序的內存占用 ? CPU 的利用 —— 整個系統(tǒng)共有多少處理器可用 ?有多少分配給 SQL Server 在操作系統(tǒng)層優(yōu)化的選項包括： ? 網(wǎng)絡接口 ? 在文件和原始分區(qū)之間選擇 ? 增加內存 ? 把客戶操作和批處理移到其他機器上 ? SQL Server 利用多個 CPU 第二章 設計數(shù)據(jù)庫 要在良好的 SQL Server 方案中實現(xiàn)最優(yōu)的性能，最關鍵的是要有一個很好的數(shù)據(jù)庫設計方案。在實際工作中，許多 SQL Server 方案往往是由于數(shù)據(jù)庫設計得不好導致性能很差。所以要設計好數(shù)據(jù)庫就要從以下幾點來討論。 熟悉業(yè)務系統(tǒng) 對業(yè)務系統(tǒng)的熟悉程度對整個數(shù)據(jù)庫系統(tǒng)的性能有很大影響，一個對業(yè)務不熟悉的設計人 員，盡管有豐富的數(shù)據(jù)庫知識，也很難設計出性能最佳的數(shù)據(jù)庫應用系統(tǒng)。所以我們在做“三江學院師資管理系統(tǒng)”這個項目前跟指導老師和用戶收集了充足的資料，通過這些資料和經(jīng)過我們的分析，我在底層數(shù)據(jù)庫建立了13 張數(shù)據(jù)庫表，它包括教師表（ Teacher）、黨派表（ Clan）、用戶表（ DBA）、入黨表（ JoinClan）、類別表（ Kind）、學位學歷表（ konwledgeDegree）、進修情況表（ operation）、聯(lián)系電話表（ Phone）、兼職情況表（ Plurality）、工作簡歷表（ Resume）、任職情況表（ SchoolDuty）、任課情況表（ SchoolTeach）、學科表（ Subject）。 通過這些表我建立了 ER 圖 m m 1 1 m m 1 n 1 m m 1 m 1 m m 1 1 m 1 學歷學位 工作簡歷 黨派 教師 擁有 入黨時間 入黨地點 任課 任職 三江學院任課情況 類別 三江學院任職情況 擁有 兼職 兼職情況 分類 入黨 學科 屬于 進修情況 進修 聯(lián)系電話 擁有 規(guī)范化與逆規(guī)范化 數(shù)據(jù)庫被規(guī)范化后，減少了數(shù)據(jù)冗余，數(shù)據(jù)量變小，數(shù)據(jù)行變窄。這樣 SQL Server 2021 的每一頁可以包括更多行，那么每一區(qū)里的數(shù)據(jù)量更多，從而加速表的掃描，改進了單個表的查詢性能。但是，當查詢涉及多個表的時候，需要用很多連接操作把信息從各個表中組合在一起，導致更高的 CPU 和 I/O 花銷。那么，有很多時候需要在規(guī)范化和逆規(guī)范化之間保持平衡，用適當?shù)娜哂嘈畔頊p少系統(tǒng)開銷，用空間代價來換取時間代價。有查詢教師全部 信息表 TeacherInfo，它里面記錄了教師基礎信息，教師類別信息，教師兼職信息，教師任課信息，教師入黨信息，教師任職信息，教師學科信息，教師進修情況信息 ….. 這些信息分別在教師表、類別表、兼職情況表、任課情況表、入黨表、任職情況表、學科表、進修情況表中存放。如果按照規(guī)范化的要求， TeacherInfo 查詢時就必須要與這么多個表進行連接或者嵌套查詢。如果 TeacherInfo 表中的數(shù)據(jù)量是在百萬級的，那么在一般配置的服務器上一次復雜查詢所需要的時間可能會達到好幾分鐘。事實上，只要在設計時保證數(shù)據(jù)的邏輯有效 性，很多信息都可以直接冗余在TeacherInfo 表中，這些冗余的數(shù)據(jù)能夠極大的提高查詢的效率，從而減少 CPU和 I/O 操作。 選擇數(shù)據(jù)類型 對每一屬性選擇什么樣的數(shù)據(jù)類型很大程度上依據(jù)表的要求，但是在不違背表要求的前提下，選擇適當?shù)臄?shù)據(jù)類型可以提高系統(tǒng)性能。比如有 text 列存放一本書的信息，用 BLOB 而不是 character(1024)， BLOB 存放的是指針或者文件參照變量，真正的文本信息可以放在數(shù)據(jù)庫之外，從而減少數(shù)據(jù)庫緩存空間，使得程序運行的速度提高。用戶可以根據(jù)自己的需要定義自己的數(shù)據(jù)類型 。 選擇索引 在應用系統(tǒng)中 ,尤其在聯(lián)機事務處理系統(tǒng)中 ,對數(shù)據(jù)查詢及處理速度已成為衡量應用系統(tǒng)成敗的標準。而采用索引來加快數(shù)據(jù)處理速度也成為廣大數(shù)據(jù)庫用戶所 接受的優(yōu)化方法。在良好的數(shù)據(jù)庫設計基礎上，能有效地使用索引是 SQL Server 取得高性能的基礎， SQL Server 采用基于代價的優(yōu)化模型，它對每一個提交的有關表的查詢，決定是否使用索引或用哪一個索引。因為查詢執(zhí)行的大部分開銷是磁盤 I/O，使用索引提高性能的一個主要目標是避免全表掃描，因為全表掃描需要從磁盤上讀表的每一個數(shù)據(jù)頁，如果有索引指向數(shù)據(jù) 值，則查詢只需讀幾次磁盤就可以了。所以如果建立了合理的索引，優(yōu)化器就能利用索引加速數(shù)據(jù)的查詢過程。但是，索引并不總是提高系統(tǒng)的性能，在增、刪、改操作中索引的存在會增加一定的工作量，因此，在適當?shù)牡胤皆黾舆m當?shù)乃饕牟缓侠淼牡胤絼h除次優(yōu)的索引，將有助于優(yōu)化那些性能較差的 SQL Server 應用。實踐表明，合理的索引設計是建立在對各種查詢的分析和預測上的，只有正確地使索引與程序結合起來 ,才能產(chǎn)生最佳的優(yōu)化方案。 本文就 SQL Server 索引的性能問題進行了一些分析和實踐。 二、 聚簇索引 (clustered indexes)的使用 聚簇索引是一種對磁盤上實際數(shù)據(jù)重新組織以按指定的一個或多個列的值排序。由于聚簇索引的索引頁面指針指向數(shù)據(jù)頁面，所以使用聚簇索引查找數(shù)據(jù)幾乎總是比使用非聚簇索引快