【導(dǎo)讀】數(shù)據(jù)庫系統(tǒng)的性能最終決定數(shù)據(jù)庫的可用性和生命力。數(shù)據(jù)庫內(nèi)存、應(yīng)用程序、操作系統(tǒng)、數(shù)據(jù)庫參數(shù)等方面。的性能調(diào)整與優(yōu)化對于整個系統(tǒng)的正常運行起著至關(guān)重要的作用。數(shù)據(jù)庫的穩(wěn)定性和可用性，保障系統(tǒng)高效地運行，解決系統(tǒng)瓶頸，節(jié)約系統(tǒng)開銷，具有良好的應(yīng)用價值。同時也對理論研究提供了一定的方法指導(dǎo)。其次,對常用的幾種優(yōu)化技術(shù)進行了詳細地介紹與說明。建的相關(guān)規(guī)則，SQLSERVER分區(qū)技術(shù)等。最后提出一個針對SQLSERVER數(shù)。最后，對SQLSERVER數(shù)據(jù)庫性能調(diào)整與優(yōu)化問題進行了總結(jié)。Keywords：database；SQLSERVER；performance；optimization

　　

【正文】 knumber) customerinfo(cid， ame， csex， cage， c， cphone， caddress) products(pid， pintime， puniprice， pquantity， pstorage， buyerid) productsinfo(pid， pkind， plife， psupplyer， pstock， pmanufactory) orders(orderid， cid， operatorid， ordertime) orderinfo(orderid， pid， ordernumber， leftnumber) customer、 customerinfo 表 包含 5000 條記錄，共占用空間約 550K； produets、 productsinfo 表包含 600 條記錄，共占用空間約 120K； orders 表包含 3000 萬條 。 5 優(yōu)化實例 數(shù)據(jù)庫物理結(jié)構(gòu)的優(yōu)化 該商品訂單管理數(shù)據(jù)庫因為主要是用于測試 ,所以它的數(shù)據(jù)量并不是很大 ,但 是若將該數(shù)據(jù)庫應(yīng)用系統(tǒng)應(yīng)用到實際的環(huán)境中 ,那么記錄數(shù)會隨著時間而增長。如果在實際情況中每天商品的交易量很大 ,那么該數(shù)據(jù)庫的數(shù)據(jù)就有可以增長到幾個 G,這不但影響著數(shù)據(jù)庫的運行效率，也增大數(shù)據(jù)庫的維護難度。除了表的數(shù)據(jù)量外，對表 不同的訪問模式也可能會影響性能和可用性。這些問題都可以通過對大表進行合理分區(qū)得到很大的改善。當(dāng)表和索引變得非常大時，分區(qū)可以將數(shù)據(jù)分為更小、更容易管理的部分來提高系統(tǒng)的運行效率。如果系統(tǒng)有多個 CPU或是多個磁盤子系統(tǒng)，可以通過并行操作獲得更好的性能。所以對大表進行分區(qū)是處理海量數(shù)據(jù)的一種十分高效的方法。 表分區(qū)功能是 SQL SERVER 2021 新增的一個功能，表分區(qū)分為水平分區(qū)和垂直分區(qū)。水平分區(qū)將表分為多個表。每個表包含的列數(shù)相同，但是行更少。而垂直分區(qū)則是將原始表分成多個只包含較少列的表。水平分區(qū)是 最常用分區(qū)方式，本文以水平分區(qū)來介紹具體實現(xiàn)方法。 水平分區(qū)常用的方法是根據(jù)時期和使用對數(shù)據(jù)進行水平分區(qū)。例如在本文所 用的商品訂單管理數(shù)據(jù)庫中，因為所有客戶只有兩個類型 ,即會員和貴賓 ,那么就 可以將 CUSTOMER 表水平分區(qū)成 2 個表，每個小表表示一種類型客戶的數(shù)據(jù)。任何需要特定客戶類型數(shù)據(jù)的查詢只需引用相應(yīng)客戶類型的表。又如該數(shù)據(jù)庫只存儲了三個廠家四種類型的商品信息 ,在這種情況下，可考慮將數(shù)據(jù)分成三個區(qū)或四個區(qū)，每個區(qū)只包含一個廠家的商品信息或一種類型的商品信息。任何需要特定廠家商品信息的查詢只需引用相 應(yīng)廠家的表就行了。因為實驗條件不具備的原因，在這里無法對該優(yōu)化的方法給出一個具體的實驗數(shù)據(jù)。 添加必要的索引 為了對使用索引技術(shù)對數(shù)據(jù)庫性能的影響進行測試 ,先構(gòu)建原始數(shù)據(jù)庫商品 訂 單 數(shù)據(jù) 庫 . 其 中 包括 CUSTOMER 、 CUSTOMERINFO 、 PRODUCTS 、PRODUCTSINFO、 ORDERS、 ORDERINFO 這六張表。將全部表存儲在同一個磁盤上，所有表均沒有設(shè)置主鍵，并且不使用任何優(yōu)化技術(shù)。該實驗方案方要作為其他優(yōu)化方案的參照物。 在商品訂單查詢業(yè)務(wù)中，有一個查詢需要執(zhí)行 6 張表的連接，在系 統(tǒng)開始設(shè) 計時，沒有充分考慮到該查詢的執(zhí)行效率。其中某條語句用來查詢 2021 年 1 月訂購了江中制藥生產(chǎn)的 A 類產(chǎn)品的、訂購數(shù)據(jù)在 20 到 200 件之間的訂單記錄。具體 查詢語句如下： Select distinct cid,ame,csex,cage From customerinfo Where cid in (select cid from customer Where crank=39。會員 39。and cid in (select cid from orders Where ordertime=39。20211139。and ordertime=39。202113139。 and orderid in (select orderid from orderinfo Where ordernumber between 20 and 200 and pid in (select pid from products Where pid in (select pid from productsinfo Where pmanufactory=39。江中制藥 39。and pkind=39。A39。))))) Order by cid 此查詢的執(zhí)行成本為 ，其執(zhí)行計劃如圖 51。 圖 51 SQL 語句一的執(zhí)行計劃 該語句是一個多層嵌套的非相關(guān)子查詢，它從最內(nèi)層的子查詢開始執(zhí)行，最 內(nèi)層子查詢的執(zhí)行結(jié)果被傳遞給其外層子查詢，逐次循環(huán)，直到最外層的外部查 詢結(jié)束。在該查詢中，由于各子查詢返回的記錄比較大，因此對這些結(jié)果集以及 臨時數(shù)據(jù)段進行排序會增加大量的系統(tǒng)開銷。 通過對系統(tǒng)進行分析，發(fā)現(xiàn)可以利用索引技術(shù)來加快檢索速度，提高系統(tǒng)性 能。首先確定用戶常用的字段，列出全部預(yù)索引字段，然后根據(jù)預(yù)索引字段的選 擇 性以及前面介紹的索引創(chuàng)建相關(guān)規(guī)則，在適當(dāng)?shù)淖侄紊显O(shè)置必要的索引。 確定預(yù)索引字段 根據(jù)商品訂單管理系統(tǒng)完成各種查詢業(yè)務(wù)所涉及到的常用表、常用字段以及查詢執(zhí)行的具體情況，現(xiàn)將 CUSTOMER 表的 CID、 CRANK 字段， PRODUCTS表的 PID 字段 PRODUCTSINFO 表的 PID、 PMANUFACTORY、 PKIND 字段，ORDERS 表的 ORDERID、 CID、 ORDERTIME 字段， ORDERINFO 表的ORDERID、 PID、 ORDRENUMBER 字段作為預(yù)索引字段。 創(chuàng)建適當(dāng)?shù)乃?引 通過對商品訂單查詢所訪問到的數(shù)據(jù)進行分析后，然后根據(jù)索引創(chuàng)建細則，在 CUSTOMER 表的 CRANK 字段上、 PRODUCTSNIFO 表的 PMANUFACTORY和 PKIND 字段上不應(yīng)該創(chuàng)建索引。即使在上述表和字段上創(chuàng)建了索引，系統(tǒng)使用這些索引的機率也非常小。根據(jù)該分析結(jié)果和得到的所有預(yù)索引字段的選擇性數(shù)據(jù)，可以考慮在 ORDERINFO 表的 PID 與 ORDERNUMBER 字段上建復(fù)合索引；同時，根據(jù)索引創(chuàng)建規(guī)則可知，為了提高系統(tǒng)性能，表的連接字段及外鍵上應(yīng)該建索引。而且在 OLTP 系統(tǒng)中，每張表超過 5 個索引就 會降低系統(tǒng)性能。 對以上情況進行綜合考慮后，最終決定建立數(shù)據(jù)庫索引商品訂單系統(tǒng)數(shù)據(jù)庫，該數(shù)據(jù)庫在商品訂單系統(tǒng)數(shù)據(jù)庫的基礎(chǔ)上創(chuàng)建以下索引：在 ORDERINFO表的 (PID， ORDERNUMBER)字段上建復(fù)合索引 OO_PID_ORDERNUMBER，在各表的連接字段上創(chuàng)建唯一索引 C_PRIMARY、 CC_PRIMARY、 P_PRIMARY、PP_PRIMARY、 O_PRIMARY、 OO_PRIMARY，并分別在 ORDERS 表的 CID 字段、 ORDERINFO 表的 PID 字 段 上 建 外 鍵 約 束 O_CID_FOREIGN 和OO_PID_FOREIGN。 實驗結(jié)果分析 在創(chuàng)建適當(dāng)?shù)乃饕?，查詢語句一的執(zhí)行計劃如圖 52 所示 ,該查詢的執(zhí)行成 本為 。 圖 52 創(chuàng)建索引后 SQL 語句一的執(zhí)行計劃 將圖 51 和圖 52 中的實驗統(tǒng)計數(shù)據(jù)進行比較，可以發(fā)現(xiàn)在使用索引技術(shù)后， 查詢語句一的執(zhí)行效率得到了應(yīng)有的提升，總的執(zhí)行成本比未使用索引技術(shù)前少 了 。根據(jù)此實驗數(shù)據(jù)可以初步得出一個結(jié)論 :在數(shù)據(jù)庫中創(chuàng)建適當(dāng)?shù)?、能夠? 優(yōu)化器使用到的索引，可以極大的提高數(shù)據(jù)庫系統(tǒng)的性能。 SQL 語句的優(yōu)化 本小節(jié)需 要用的測試環(huán)境是上面所創(chuàng)建的索引商品訂單系統(tǒng)數(shù)據(jù)庫，該數(shù)據(jù)庫在商品訂單管理數(shù)據(jù)庫的基礎(chǔ)上創(chuàng)建以下索引：在 ORDERINFO 表的 (PID， ORDERNUMBER)字段上建復(fù)合索引 OO_PID_ORDERNUMBER，在各表的連接字 段 上 創(chuàng) 建 唯 一 索 引 C_PRIMARY 、 CC_PRIMARY 、 P_PRIMARY 、PP_PRIMARY、 O_PRIMARY、 OO_PRIMARY，并分別在 ORDERS 表的 CID 字段、 ORDERINFO 表的 PID 字 段 上 建 外 鍵 約 束 O_CID_FOREIGN 和OO_PID_FOREIGN。 *FROM orderinfo WHERE ordernumber/2=100 該語句的執(zhí)行成本為 ,執(zhí)行計劃如圖 53。 圖 53 SQL 語句的執(zhí)行計劃 根據(jù)第四章所介紹的 SQL 語句優(yōu)化技術(shù)將“ SELECT * FROM orderinfo WHERE ordernumber/2=100”改為“ SELECT * FROM orderinfo WHERE ordernumber=100*2”后的執(zhí)行成本為 ,執(zhí)行計劃如圖 54。 圖 54 改進后 SQL 語句 的執(zhí)行計劃 因為該 SQL 語句在 WHERE 子句中對字段進行表達式操作，所以導(dǎo)致引擎放棄使用索引而進行全表掃描 ,導(dǎo)致查詢成本的增加 . ，我在商品訂單管理數(shù)據(jù)庫對所要用到的表進行了很大的 擴充，增加了上萬條記錄。下面的第一個 SQL 語句和第二個 SQL 語句都是用于查詢 2021 年 1 月訂購了江中制藥生產(chǎn)的 A 類產(chǎn)品的、訂購數(shù)量在 20 到 200 件之間的客戶的相關(guān)信息，兩條查詢語句的結(jié)果集相同，但是第一個 SQL 語句使用子查詢完成，第二個 SQL 語句使用聯(lián)接查詢完成。下面我們就來測試一下這兩條語句的執(zhí)行成本。 （ 1） Select distinct cid,ame,csex,cage From customerinfo Where cid in (select cid from customer Where crank=39。會員 39。and cid in (select cid from orders Where ordertime=39。20211139。and ordertime=39。202113139。 and orderid in (select orderid from orderinfo Where ordernumber between 20 and 200 and pid in (select pid from products Where pid in (select pid from productsinfo Where pmanufactory=39。江中制藥 39。 and pkind=39。A39。))))) order by cid 該語句的執(zhí)行成本為 ,執(zhí)行計劃如圖 55。 圖 55 使用子查詢的 SQL 語句的執(zhí)行計劃 (2)select distinct ,ame,csex,cage from Products P,Productsinfo PP,orderinfo oo,orders o,customerinfo ee,customer e where = and = and = and = and = and crank=39。會員 39。and pmanufactory=39。江中制藥 39。and pkind=39。A39。and ordertime=39。20211139。and ordertime=39。2021123139。and ordernumber between 20 and 200 order by 該語句的執(zhí)行成本為 ,執(zhí)行計劃如圖 56。 圖 56 使用連接的 SQL 語句的執(zhí)行計劃 通過這兩條 SQL 語句的執(zhí)行計劃進行比較分析可以得出如下結(jié)論：我們在寫查詢語句時盡量用連接代替所有的子查詢。雖然優(yōu)化器有時可以自動將子查詢“扁平化”，并且用常規(guī)連接或外連接代替。但那樣也不總是有效。明確的連接對選擇表的 順序和找到最好的計劃給出了更多的選項。 6 總 結(jié) 隨著現(xiàn)代計算機應(yīng)用技術(shù)的發(fā)展和企業(yè)信息系統(tǒng)的廣泛應(yīng)用，數(shù)據(jù)庫己逐步 成為計算機信息系統(tǒng)和計算機應(yīng)用系統(tǒng)的基礎(chǔ)和核心，而數(shù)據(jù)庫性能的重要性也 越來越引起人們的關(guān)注。 SQL SERVER 數(shù)據(jù)庫在國內(nèi)外獲得了廣泛應(yīng)用。因此，對 SQL SERVER 數(shù)據(jù)庫系統(tǒng)的性能問題進行深入研究，提出針對 SQL SERVER數(shù)據(jù)庫性能調(diào)整與優(yōu)化的具體方法和措施，對于保證數(shù)據(jù)庫穩(wěn)定高效運行具有關(guān)鍵作用， 很好的理論與應(yīng)用價值。論文根據(jù)本人自己平時在工作 中遇到的問題和對 SQL SERVER 數(shù)據(jù)庫的使用經(jīng)驗，對 SQL SERVER 數(shù)據(jù)庫性能調(diào)整和優(yōu)化進行了較為全面、系統(tǒng)的分析和研究，對各種優(yōu)化技術(shù)進行了深入的探討，將 SQL語句優(yōu)化、 SQL SERVER 內(nèi)存分配調(diào)整以及磁盤 I/O 優(yōu)化方法作為論文的主要研究內(nèi)容，總結(jié)歸納出 SQL SERVER 數(shù)據(jù)庫性能調(diào)整與優(yōu)化的一般性原則、方法和技巧?？偨Y(jié)如下 : 。包括對性能調(diào)整與優(yōu)化的概念、性能優(yōu)化的主要方面、常用的 SQL SERVER