【正文】 sex,cage From customerinfo Where cid in (select cid from customer Where crank=39。 圖 53 SQL 語句的執(zhí)行計(jì)劃 根據(jù)第四章所介紹的 SQL 語句優(yōu)化技術(shù)將“ SELECT * FROM orderinfo WHERE ordernumber/2=100”改為“ SELECT * FROM orderinfo WHERE ordernumber=100*2”后的執(zhí)行成本為 ,執(zhí)行計(jì)劃如圖 54。 圖 52 創(chuàng)建索引后 SQL 語句一的執(zhí)行計(jì)劃 將圖 51 和圖 52 中的實(shí)驗(yàn)統(tǒng)計(jì)數(shù)據(jù)進(jìn)行比較，可以發(fā)現(xiàn)在使用索引技術(shù)后， 查詢語句一的執(zhí)行效率得到了應(yīng)有的提升，總的執(zhí)行成本比未使用索引技術(shù)前少 了 。根據(jù)該分析結(jié)果和得到的所有預(yù)索引字段的選擇性數(shù)據(jù)，可以考慮在 ORDERINFO 表的 PID 與 ORDERNUMBER 字段上建復(fù)合索引；同時(shí)，根據(jù)索引創(chuàng)建規(guī)則可知，為了提高系統(tǒng)性能，表的連接字段及外鍵上應(yīng)該建索引。首先確定用戶常用的字段，列出全部預(yù)索引字段，然后根據(jù)預(yù)索引字段的選 擇 性以及前面介紹的索引創(chuàng)建相關(guān)規(guī)則，在適當(dāng)?shù)淖侄紊显O(shè)置必要的索引。))))) Order by cid 此查詢的執(zhí)行成本為 ，其執(zhí)行計(jì)劃如圖 51。 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。and cid in (select cid from orders Where ordertime=39。 在商品訂單查詢業(yè)務(wù)中，有一個(gè)查詢需要執(zhí)行 6 張表的連接，在系 統(tǒng)開始設(shè) 計(jì)時(shí)，沒有充分考慮到該查詢的執(zhí)行效率。因?yàn)閷?shí)驗(yàn)條件不具備的原因，在這里無法對(duì)該優(yōu)化的方法給出一個(gè)具體的實(shí)驗(yàn)數(shù)據(jù)。例如在本文所 用的商品訂單管理數(shù)據(jù)庫中，因?yàn)樗锌蛻糁挥袃蓚€(gè)類型 ,即會(huì)員和貴賓 ,那么就 可以將 CUSTOMER 表水平分區(qū)成 2 個(gè)表，每個(gè)小表表示一種類型客戶的數(shù)據(jù)。每個(gè)表包含的列數(shù)相同，但是行更少。如果系統(tǒng)有多個(gè) CPU或是多個(gè)磁盤子系統(tǒng)，可以通過并行操作獲得更好的性能。如果在實(shí)際情況中每天商品的交易量很大 ,那么該數(shù)據(jù)庫的數(shù)據(jù)就有可以增長(zhǎng)到幾個(gè) G,這不但影響著數(shù)據(jù)庫的運(yùn)行效率，也增大數(shù)據(jù)庫的維護(hù)難度。 訂單詳細(xì)信息表：存放每一筆訂單所包含的商品號(hào)、所訂購的商品數(shù)量等信 息。 商品詳細(xì)信息表：存放各商品的詳細(xì)信息，包括商品種類、有效期、商品生 產(chǎn)商等信息。 客戶詳細(xì)信息表：存放客戶的詳細(xì)信息，包括客戶名，性別、年齡、電話、 地址及其 EMAIL。 4 商品訂單管理系統(tǒng)數(shù)據(jù)庫設(shè)計(jì) 數(shù)據(jù)庫的設(shè)計(jì)是否合理將會(huì)極大地影響系統(tǒng)運(yùn)行的性能，因此我們結(jié)合大型 數(shù)據(jù)庫的設(shè)計(jì)原則，有效利用大型數(shù)據(jù)庫的優(yōu)勢(shì)，對(duì)數(shù)據(jù)庫進(jìn)行設(shè)計(jì) ,在數(shù)據(jù)庫應(yīng)用系統(tǒng)的生命周期中 ,在設(shè)計(jì)階段做的性能優(yōu)化所帶來的效益最大 ,所以我所設(shè)計(jì)這個(gè)數(shù)據(jù)庫的數(shù)據(jù)庫模式至少達(dá)到了 3NF 的標(biāo)準(zhǔn) ,并結(jié)合反規(guī)范化設(shè)計(jì)的相關(guān)原則 ,也允許部份冗余數(shù)據(jù)的存在。最好的方法當(dāng)然是測(cè)試， 看實(shí)現(xiàn)相同功能的 SQL 語句哪個(gè)執(zhí)行時(shí)間最少，但是數(shù)據(jù)庫中如果數(shù)據(jù)量很少， 是比較不出來的，這時(shí)可以用查看執(zhí)行計(jì)劃，即：把實(shí)現(xiàn)相同功能的多條 SQL語句拷到查詢分析器，按 CTRL+L 看查所利用的索引，表掃描次數(shù)（這兩個(gè)對(duì)性能影響最大），總體上看詢成本百分比即可。 (4)避免 UPDATE 將要復(fù)制到其他數(shù)據(jù)庫的列。 SELECT * FROM T1 WHERE nextprocess=1 AND processid IN(8， 32， 45) 改成 : SELECT * FROM T1(INDEX=IX_ProcessID)WHERE nextprocess=1 AND processid IN(8， 32， 45) 則查詢優(yōu)化器將會(huì)強(qiáng)行利用索引 IX_ProcessID 執(zhí)行查詢。 UNION ALL 就不要用 UNION,UNION ALL 不執(zhí)行 SELECT DISTINCT 函數(shù)，這樣就會(huì)減少很多不必要的資源。Orlando39。Orlando39。 BETWEEN 的就不要用 IN SELECT*FROM T1 WHERE ID IN(10， 11， 12， 13， 14) 改成： SELECT*FROM T1 WHERE ID BETWEEN 10 AND 14 因?yàn)?IN 會(huì)使系統(tǒng)無法使用索引，而只能直接搜索表中的數(shù)據(jù)。如果把語句改為 SELECT * FROM customer WHERE zipcode“ 98000” ，在執(zhí)行查詢時(shí)就會(huì)利用索引來查詢，顯然會(huì)大大提高速度。如： SELECT * FROM orders WHERE(customer_num=104 AND order_num1001) OR order_num=1008 解決辦法可以使用并集來避免順序存取： SELECT*FROM ORDERS WHERE CUSTOMER_NUM=104 AND ORDER_NUM1001 UNION SELECT＊ FROM orders WHERE order_num=1008 這樣就能利用索引路徑處理查詢。而第三個(gè)查詢能夠使用索引來加快操作。) T_SQL 語句比較一個(gè)父結(jié)果集和子結(jié)果集，從而找到是否 存在在父結(jié)果集中有而在子結(jié)果集中沒有的記錄，如： (1).SELECT FROM HDR_TBL A WHERE NOT EXISTS(SELECT * FROM DTL_TBL B WHERE =) (2).SELECT FROM HDR_TBL A LEFT JOIN DTL_DTL B ON = WHERE IS NULL (3).SELECT HDR_KEY FROM HDR_TBL WHERE HDR_KEY NOT IN(SELECT HDR_KEY FROM DTL_TBL) 三種寫法都可以得到同樣正確的結(jié)果，但是效率依次降低。如： IF(SELECT COUNT(*)FROM table_name WHERE column_name=39。如下所示： (1).SELECT SUM()FROM T1 WHERE(SELECT COUNT(*)FROM T2 WHERE =)0 (2).SELECT SUM()FROM T1 WHERE EXISTS(SELECT*FROM T2 WHERE =) 兩者產(chǎn)生相同的結(jié)果，但是后者的效率顯然要高于前者。 優(yōu)化器將無法通過索引來確定將要命中的行數(shù)，因此需要搜索該表的所有行。如： (1)SELECT * FROM T1 WHERE F1/2=100 應(yīng)改為 : SELECT * FROM T1 WHERE F1=100*2 (2)SELECT * FROM RECORD WHERE SUBSTRING(CARD_NO， 1， 4)=’5378’ 應(yīng)改為： SELECT * FROM RECORD WHERE CARD_NO LIKE?5378%? (3)SELECT member_number， first_name， last_name FROM members WHERE DATEDIFF(yy， datofbirth， GETDATE())21 應(yīng)改為： SELECT member_number， first_name， last_name FROM members WHERE dateofbirthDATEADD(yy， 21， GETDATE()) 即：任何對(duì)列的操作都將導(dǎo)致表掃描，它包括數(shù)據(jù)庫函數(shù)、計(jì)算表達(dá)式等等， 查詢時(shí)要盡可能將操作移至等號(hào)右邊。數(shù)據(jù)類型的不兼容可能使優(yōu)化器無法執(zhí)行一些本來可 以進(jìn)行的優(yōu)化操作。 ；在查詢時(shí)，不要過多地使用通配符如 SELECT * FROM T1 語句，要用到幾列就選擇幾列，如： SELECT COL1， COL2 FROM T1；在可能的情況下盡量限制結(jié)果集行數(shù)，如： SELECT TOP 300 COL1， COL2， COL3 FROM T1，因?yàn)槟承┣闆r下用戶是不需要那么多的數(shù)據(jù)的。另外，如 果數(shù)據(jù)庫上已經(jīng)有索引了，并且索引優(yōu)化向?qū)Оl(fā)現(xiàn)這些索引不是最佳的，那么會(huì) 建議你移除。這樣，索引優(yōu)化向?qū)Ь涂梢曰谶@些實(shí)際運(yùn)行的統(tǒng)計(jì)行為來提出有用的建議。 使用索引優(yōu)化向?qū)埃紫纫獎(jiǎng)?chuàng)建工作負(fù)載。經(jīng)過一段時(shí)間后， 數(shù)據(jù)會(huì)變得支離破碎，要根據(jù)組織環(huán)境對(duì)數(shù)據(jù)碎片進(jìn)行整理。 (7)考慮使用聚集索引來支持排序和范圍查詢，在為數(shù)據(jù)檢索而優(yōu)化表的時(shí) 候，聚集索引應(yīng)該支持對(duì)記錄組的檢索。 (4)如果經(jīng)常同時(shí)使用多個(gè)字段排序，可以在這些 字段上建立復(fù)合索引。為經(jīng)常進(jìn)行連接，但是沒有指定為外鍵的字 段建立索引，而不經(jīng)常連接的字段由數(shù)據(jù)庫優(yōu)化器自動(dòng)建立索引。索引是數(shù)據(jù)庫的重要的數(shù)據(jù)結(jié)構(gòu) ,它的根本目的就是為了提高查詢速度 。每一個(gè)索引條目都由一個(gè)鍵值和 ROWID 組成。總得來講，程序員寫 SQL 語句需要滿足考慮如下規(guī)則： 合理使用索引 索引是一種供服務(wù)器在表中快速查找一個(gè)行的數(shù)據(jù)庫結(jié)構(gòu)。由于涉及到對(duì)程序邏輯的改變，源代碼的優(yōu)化在時(shí)間成本和風(fēng)險(xiǎn)上代價(jià)很高，而對(duì)數(shù)據(jù)庫系統(tǒng)性能的提升收效有限。最常見的優(yōu)化手段就是對(duì)硬件的升級(jí)。我們可以 通過 $ 來查看，代碼如下： 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)換。分區(qū)表是通過定義分 區(qū)鍵值和分區(qū)方案相聯(lián)系的。就是定義實(shí)際存放數(shù)據(jù)的媒體與各數(shù)據(jù)塊的對(duì)應(yīng)關(guān)系。2021070139。例如存在四個(gè)分區(qū)，則定義三個(gè)邊界點(diǎn)值，并指定每個(gè) 值是第一個(gè)分區(qū)的上邊界 (LEFT)還是第二個(gè)分區(qū)的下邊界 (RIGHT)。 SIZE=3072KB， FILEGROWTH=1024KB)TO FILEGROUP [FG1] 類 似的建立四個(gè)文件和文件組，并把每一個(gè)存儲(chǔ)數(shù)據(jù)的文件放在不同的磁盤 驅(qū)動(dòng)器里。 ATER DATABASE[DeanDB]ADD FILE(NAME=39。一個(gè)文件組可以由多個(gè)分區(qū)使用。下面具體 介紹一下使用分區(qū)表的方法。水平分區(qū)是最常用分區(qū)方式，本文以水 平分區(qū)來介紹具體實(shí)現(xiàn)方法。每個(gè)表包含的列數(shù)相同，但是行更少。所以對(duì)大表進(jìn)行分區(qū)是處理海量數(shù)據(jù)的一種十分高效的方法。除了表的數(shù)據(jù)量外，對(duì)表不同的訪問模式也可能會(huì)影響性能和可用性。 1 個(gè)專用的智能型的控制器能進(jìn)一步提高性能。比如能使用 smallint 類型就不要用 int 類型，這樣索引 字段可以被更快地讀取，而且可以在 1 個(gè)數(shù)據(jù)頁上放置更多的數(shù)據(jù)行，因而也就 減少了 I/O 操作。某種程度上的非規(guī)范化可以改善系統(tǒng)的性能，非規(guī)范化過程可以根據(jù)性能方面不同的考慮用多種不同的方法進(jìn)行，所在在進(jìn)行數(shù)據(jù)庫邏輯結(jié)構(gòu)設(shè)計(jì)時(shí)應(yīng)綜合考慮數(shù)據(jù)冗余和基于連接的查詢帶來的性能問題。 第 2 規(guī)范：每個(gè)非關(guān)鍵字段必須依賴于主關(guān)鍵字，不能依賴于 1 個(gè)組合式主 關(guān)鍵字的某些組成部分。在實(shí)際工作中，許多 SQL Server 方案往往是 由于數(shù)據(jù)庫設(shè)計(jì)得不好導(dǎo)致性能很差。 3 性能優(yōu)化技術(shù)介紹 數(shù)據(jù)庫結(jié)構(gòu)優(yōu)化 一個(gè)數(shù)據(jù)庫系統(tǒng)的生命周期可以分成：設(shè)計(jì)、開發(fā)和成品三個(gè)階段。 優(yōu)化數(shù)據(jù)庫的邏輯結(jié)構(gòu) 這一階段就是通過增加、減少或調(diào)整邏輯結(jié)構(gòu)，如考慮在主鍵和外鍵上建立 索引之外增加其它索引以提高系統(tǒng)性能。這樣設(shè)計(jì)者將性能要求同具體的業(yè)務(wù)需求直接 聯(lián)系起來。在數(shù)據(jù)庫的性能調(diào)整過程中需 要應(yīng)用程序設(shè)計(jì)人員、應(yīng)用程序開發(fā)人員、數(shù)據(jù)庫管理員以及系統(tǒng)管理員共同完 成。為了縮短系統(tǒng)響應(yīng)時(shí)間，可以使系統(tǒng)以遠(yuǎn)低于其實(shí)際吞吐能力的吞吐量運(yùn)行 或是將批理作業(yè)放在非營業(yè)時(shí)間內(nèi)處理。 盡管某些數(shù)據(jù)庫系統(tǒng)需要實(shí)現(xiàn)系統(tǒng)可能達(dá)到的最高吞吐量，但另一些系統(tǒng)可 能有嚴(yán)格的系統(tǒng)響應(yīng)時(shí)間要求。響應(yīng)時(shí)間通常也稱為用戶收到查詢正被處理的直觀確認(rèn)時(shí)刻所感知的時(shí)間長(zhǎng)度。這是設(shè)計(jì)數(shù)據(jù) 庫系統(tǒng)的一條著名規(guī)律：為最糟糕的情況設(shè)計(jì)系統(tǒng)的軟硬件規(guī)模。如果該公司的 營業(yè)時(shí)間為每 天 8 小時(shí)，并且在銷售市場(chǎng)體育比賽門票的第一天必須賣出 25000 張門票，那么 該公司的門票預(yù)定系統(tǒng)的吞吐量必須等于或大于每小時(shí) 3125 筆事務(wù)。如果吞吐量低 (壞 )，反應(yīng)時(shí)間或許短 (好 )。提高系統(tǒng)的吞吐量也有兩種方法： ? 在同樣的資源環(huán)境下做更多的工作 (通過減少服務(wù)時(shí)間 )； ? 通過減少總的響應(yīng)時(shí)間使工作做得更快。首先，討論將涉及系統(tǒng)調(diào)整的不同組件