高并發(fā)數(shù)據(jù)庫解決方案.docx

高并發(fā)高負(fù)載數(shù)據(jù)庫架構(gòu)策略在WEB網(wǎng)站的規(guī)模從小到大不斷擴(kuò)展的過程中，數(shù)據(jù)庫的訪問壓力也不斷的增加，數(shù)據(jù)庫的架構(gòu)也需要動態(tài)擴(kuò)展，在數(shù)據(jù)庫的擴(kuò)展過程基本上包含如下幾步，每一個擴(kuò)展都可以比上一步驟的部署方式的性能得到數(shù)量級的提升。 1. WEB應(yīng)用和數(shù)據(jù)庫部署在同一臺服務(wù)器上 一般的小規(guī)模的網(wǎng)站采用這種方式，用戶量、數(shù)據(jù)量、并發(fā)訪問量都比較小，否則單臺服務(wù)器無法承受，并且在遇到性能瓶頸的時候升級硬件所需要的費用非常高 昂，在訪問量增加的時候，應(yīng)用程序和數(shù)據(jù)庫都來搶占有限的系統(tǒng)資源，很快就又會遇到性能問題。 2. WEB應(yīng)用和數(shù)據(jù)庫部署在各自獨立的服務(wù)器上 web應(yīng)用和數(shù)據(jù)庫分開部署，WEB應(yīng)用服務(wù)器和數(shù)據(jù)庫服務(wù)器各司其職，在系統(tǒng)訪問量增加的時候可以分別升級應(yīng)用服務(wù)器和數(shù)據(jù)庫服務(wù)器，這種部署方式是一般小規(guī)模網(wǎng)站的典型部署方式。在將應(yīng)用程序進(jìn)行性能優(yōu)化并且使用數(shù)據(jù)庫對象緩存策略的情況下，可以承載較大的訪問量，比如2000用戶，200個并發(fā)，百萬級別的數(shù)據(jù)量。 3. 數(shù)據(jù)庫服務(wù)器采用集群方式部署（比如Oracle的一個數(shù)據(jù)庫多個實例的情況） 數(shù)據(jù)庫集群方式能承擔(dān)的負(fù)載是比較大的，數(shù)據(jù)庫物理介質(zhì)為一個磁盤陣列，多個數(shù)據(jù)庫實例以虛擬IP方式向外部應(yīng)用服務(wù)器提供數(shù)據(jù)庫連接服務(wù)。這種部署方式 基本上可以滿足絕大多數(shù)的常見WEB應(yīng)用，但是還是不能滿足大用戶量、高負(fù)載、數(shù)據(jù)庫讀寫訪問非常頻繁的應(yīng)用。4. 數(shù)據(jù)庫采用主從部署方式在面向大眾用戶的博客、論談、交友、CMS等系統(tǒng)中，有上百萬的用戶，有上千萬的數(shù)據(jù)量，存在眾多的數(shù)據(jù)庫查詢操作，也有較多的數(shù)據(jù)庫寫操作，并且在多數(shù)情況下都是讀操作遠(yuǎn)大于寫操作的。在這個時候，假如能將數(shù)據(jù)庫的讀寫操作分離的話，對于系統(tǒng)來講是一個很大的提高啦。數(shù)據(jù)庫的主從部署方式就走到我們面前啦。 主從復(fù)制： 幾乎所有的主流數(shù)據(jù)庫都支持復(fù)制，這是進(jìn)行數(shù)據(jù)庫簡單擴(kuò)展的基本手段。下面以Mysql為例來說明，它支持主從復(fù)制，配置也并不復(fù)雜，只需要開啟主服務(wù)器上的二進(jìn)制日志以及在主服務(wù)器和從服務(wù)器上分別進(jìn)行簡單的配置和授權(quán)。Mysql的主從復(fù)制是一句主服務(wù)器的二進(jìn)制日志文件進(jìn)行的，主服務(wù)器日志中記錄的操作會在從服務(wù)器上重放，從而實現(xiàn)復(fù)制，所以主服務(wù)器必須開啟二進(jìn)制日志，自動記錄所有對于主數(shù)據(jù)庫的更新操作，從服務(wù)器再定時到主服務(wù)器取得二進(jìn)制日志 文件進(jìn)行重放則完成了數(shù)據(jù)的復(fù)制。主從復(fù)制也用于自動備份。 讀寫分離： 為保證數(shù)據(jù)庫數(shù)據(jù)的一致性，我們要求所有對于數(shù)據(jù)庫的更新操作都是針對主數(shù)據(jù)庫的，但是讀操作是可以針對從數(shù)據(jù)庫來進(jìn)行。大多數(shù)站點的數(shù)據(jù)庫讀操作比寫操作更加密集，而且查詢條件相對復(fù)雜，數(shù)據(jù)庫的大部分性能消耗在查詢操作上了。 主從復(fù)制數(shù)據(jù)是異步完成的，這就導(dǎo)致主從數(shù)據(jù)庫中的數(shù)據(jù)有一定的延遲，在讀寫分離的設(shè)計中必須要考慮這一點。以博客為例，用戶登錄后發(fā)表了一篇文章，他需要馬上看到自己的文章，但是對于其它用戶來講可以允許延遲一段時間（1分鐘/5分鐘/30分鐘），不會造成什么問題。這時對于當(dāng)前用戶就需要讀主數(shù)據(jù)庫，對于其他訪問量更大的外部用戶就可以讀從數(shù)據(jù)庫。 數(shù)據(jù)庫反向代理： 在讀寫分離的方式使用主從部署方式的數(shù)據(jù)庫的時候，會遇到一個問題，一個主數(shù)據(jù)庫對應(yīng)多臺從服務(wù)器，對于寫操作是針對主數(shù)據(jù)庫的，數(shù)據(jù)庫個數(shù)是唯一的，但 是對于從服務(wù)器的讀操作就需要使用適當(dāng)?shù)乃惴▉矸峙湔埱罄?，尤其對于多個從服務(wù)器的配置不一樣的時候甚至需要讀操作按照權(quán)重來分配。 對于上述問題可以使用數(shù)據(jù)庫方向代理來實現(xiàn)。就像WEB方向代理服務(wù)器一樣，MYsql Proxy同樣可以在SQL語句轉(zhuǎn)發(fā)到后端的Mysql服務(wù)器之前對它進(jìn)行修改。 5. 數(shù)據(jù)庫垂直分割 主從部署數(shù)據(jù)庫中，當(dāng)寫操作占了主數(shù)據(jù)庫的CPU消耗的50%以上的時候，我們再增加從服務(wù)器的意義就不是很大了，因為所有的從服務(wù)器的寫操作也將占到 CPU消耗的50%以上，一臺從服務(wù)器提供出來查詢的資源非常有限。數(shù)據(jù)庫就需要重新架構(gòu)了，我們需要采用數(shù)據(jù)庫垂直分區(qū)技術(shù)啦。 最簡單的垂直分區(qū)方式是將原來的數(shù)據(jù)庫中獨立的業(yè)務(wù)進(jìn)行分拆（被分拆出來的部分與其它部分不需要進(jìn)行Join連接查詢操作），比如WEB站點的BLOG和 論壇，是相對獨立的，與其它的數(shù)據(jù)的關(guān)聯(lián)性不是很強(qiáng)，這時可以將原來的數(shù)據(jù)庫拆分為一個BLog庫，一個論壇庫，以及剩余的表所組成的庫。這三個庫再各自進(jìn)行主從數(shù)據(jù)庫方式部署，這樣整個數(shù)據(jù)庫的壓力就分擔(dān)啦。 另外查詢擴(kuò)展性也是采用數(shù)據(jù)庫分區(qū)最主要的原因之一。將一個大的數(shù)據(jù)庫分成多個小的數(shù)據(jù)庫可以提高查詢的性能，因為每個數(shù)據(jù)庫分區(qū)擁有自己的一小部分?jǐn)?shù)據(jù)。假設(shè)您想掃描1億條記錄，對一個單一分區(qū)的數(shù)據(jù)庫來講，該掃描操作需要數(shù)據(jù)庫管理器獨立掃描一億條記錄，如果您將數(shù)據(jù)庫系統(tǒng)做成50個分區(qū)，并將這1 億條記錄平均分配到這50個分區(qū)上，那么每個數(shù)據(jù)庫分區(qū)的數(shù)據(jù)庫管理器將只掃描200萬記錄。 6. 數(shù)據(jù)庫水平分割 在數(shù)據(jù)庫的垂直分區(qū)之后，假如我們的BLOG庫又再次無法承擔(dān)寫操作的時候，我們又該怎么辦呢？數(shù)據(jù)庫垂直分區(qū)這種擴(kuò)展方式又無能為力了，我們需要的是水 平分區(qū)。 水平分區(qū)意味著我們可以將同一個數(shù)據(jù)庫表中的記錄通過特定的算法進(jìn)行分離，分別保存在不同的數(shù)據(jù)庫表中，從而可以部署在不同的數(shù)據(jù)庫服務(wù)器上。很多的大規(guī)模的站點基本上都是主從復(fù)制+垂直分區(qū)+水平分區(qū)這樣的架構(gòu)。水平分區(qū)并不依賴什么特定的技術(shù)，完全是邏輯層面的規(guī)劃，需要的是經(jīng)驗和業(yè)務(wù)的細(xì)分。 如何分區(qū)呢？對于大型的WEB站點來說，必須分區(qū)，并且對于分區(qū)我們沒有選擇的余地，對于那些頻繁訪問導(dǎo)致站點接近崩潰的熱點數(shù)據(jù)，我們必須分區(qū)。 在對數(shù)據(jù)分區(qū)的時候，我們必須要存在一個分區(qū)索引字段，比如USER_ID，它必須和所有的記錄都存在關(guān)系，是分區(qū)數(shù)據(jù)庫中的核心表的主鍵，在其它表中作 為外鍵，并且在使用主鍵的時候，該主鍵不能是自增長的，必須是業(yè)務(wù)主鍵才可以。 余數(shù)分區(qū)： 我們可以將User_ID%10后的值為依據(jù)存入到不同的分區(qū)數(shù)據(jù)庫中，該算法簡單高效，但是在分區(qū)數(shù)據(jù)庫個數(shù)有變動的時候，整個系統(tǒng)的數(shù)據(jù)需要重新分布。 范圍分區(qū)： 我們可以將User_ID的范圍進(jìn)行分區(qū)，比如1-100000范圍為一個分區(qū)數(shù)據(jù)庫，100001-200000范圍為一個分區(qū)數(shù)據(jù)庫，該算法在分區(qū)數(shù) 據(jù)庫個數(shù)有變動的時候，系統(tǒng)非常有利于擴(kuò)展，但是容易導(dǎo)致不同分區(qū)之間的壓力不同，比如老用戶所在的分區(qū)數(shù)據(jù)庫的壓力很大，但是新用戶的分區(qū)數(shù)據(jù)庫的壓力偏小。 映射關(guān)系分區(qū)： 將對分區(qū)索引字段的每個可能的結(jié)果創(chuàng)建一個分區(qū)映射關(guān)系，這個映射關(guān)系非常龐大，需要將它們寫入數(shù)據(jù)庫中。比如當(dāng)應(yīng)用程序需要知道User_id為10的 用戶的BLOG內(nèi)容在那個分區(qū)時，它必須查詢數(shù)據(jù)庫獲取答案，當(dāng)然，我們可以使用緩存來提高性能。 這種方式詳細(xì)保存了每一個記錄的分區(qū)對應(yīng)關(guān)系，所以各個分區(qū)有非常強(qiáng)的可伸縮性，可以靈活的控制，并且將數(shù)據(jù)庫從一個分區(qū)遷移到另一個分區(qū)也很簡單，也可以使各個分區(qū)通過靈活的動態(tài)調(diào)節(jié)來保持壓力的分布平衡。大數(shù)據(jù)量高并發(fā)的數(shù)據(jù)庫優(yōu)化發(fā)表日期：2011-01-19 一、數(shù)據(jù)庫結(jié)構(gòu)的設(shè)計 如果不能設(shè)計一個合理的數(shù)據(jù)庫模型，不僅會增加客戶端和服務(wù)器段程序的編程和維護(hù)的難度，而且將會影響系統(tǒng)實際運行的性能。所以，在一個系統(tǒng)開始實施之前，完備的數(shù)據(jù)庫模型的設(shè)計是必須的。 在一個系統(tǒng)分析、設(shè)計階段，因為數(shù)據(jù)量較小，負(fù)荷較低。我們往往只注意到功能的實現(xiàn)，而很難注意到性能的薄弱之處，等到系統(tǒng)投入實際運行一段時間后，才發(fā)現(xiàn)系統(tǒng)的性能在降低，這時再來考慮提高系統(tǒng)性能則要花費更多的人力物力，而整個系統(tǒng)也不可避免的形成了一個打補(bǔ)丁工程。 所以在考慮整個系統(tǒng)的流程的時候，我們必須要考慮，在高并發(fā)大數(shù)據(jù)量的訪問情況下，我們的系統(tǒng)會不會出現(xiàn)極端的情況。（例如：對外統(tǒng)計系統(tǒng)在7月16日出現(xiàn)的數(shù)據(jù)異常的情況，并發(fā)大數(shù)據(jù)量的的訪問造成，數(shù)據(jù)庫的響應(yīng)時間不能跟上數(shù)據(jù)刷新的速度造成。具體情況是：在日期臨界時（00：00：00），判斷數(shù)據(jù)庫中是否有當(dāng)前日期的記錄，沒有則插入一條當(dāng)前日期的記錄。在低并發(fā)訪問的情況下，不會發(fā)生問題，但是當(dāng)日期臨界時的訪問量相當(dāng)大的時候，在做這一判斷的時候，會出現(xiàn)多次條件成立，則數(shù)據(jù)庫里會被插入多條當(dāng)前日期的記錄，從而造成數(shù)據(jù)錯誤。），數(shù)據(jù)庫的模型確定下來之后，我們有必要做一個系統(tǒng)內(nèi)數(shù)據(jù)流向圖，分析可能出現(xiàn)的瓶頸。 為了保證數(shù)據(jù)庫的一致性和完整性，在邏輯設(shè)計的時候往往會設(shè)計過多的表間關(guān)聯(lián)，盡可能的降低數(shù)據(jù)的冗余。（例如用戶表的地區(qū)，我們可以把地區(qū)另外存放到一個地區(qū)表中）如果數(shù)據(jù)冗余低，數(shù)據(jù)的完整性容易得到保證，提高了數(shù)據(jù)吞吐速度，保證了數(shù)據(jù)的完整性，清楚地表達(dá)數(shù)據(jù)元素之間的關(guān)系。而對于多表之間的關(guān)聯(lián)查詢（尤其是大數(shù)據(jù)表）時，其性能將會降低，同時也提高了客戶端程序的編程難度，因此，物理設(shè)計需折衷考慮，根據(jù)業(yè)務(wù)規(guī)則，確定對關(guān)聯(lián)表的數(shù)據(jù)量大小、數(shù)據(jù)項的訪問頻度，對此類數(shù)據(jù)表頻繁的關(guān)聯(lián)查詢應(yīng)適當(dāng)提高數(shù)據(jù)冗余設(shè)計但增加了表間連接查詢的操作，也使得程序的變得復(fù)雜，為了提高系統(tǒng)的響應(yīng)時間，合理的數(shù)據(jù)冗余也是必要的。設(shè)計人員在設(shè)計階段應(yīng)根據(jù)系統(tǒng)操作的類型、頻度加以均衡考慮。 另外，最好不要用自增屬性字段作為主鍵與子表關(guān)聯(lián)。不便于系統(tǒng)的遷移和數(shù)據(jù)恢復(fù)。對外統(tǒng)計系統(tǒng)映射關(guān)系丟失（*）。 原來的表格必須可以通過由它分離出去的表格重新構(gòu)建。使用這個規(guī)定的好處是，你可以確保不會在分離的表格中引入多余的列，所有你創(chuàng)建的表格結(jié)構(gòu)都與它們的實際需要一樣大。應(yīng)用這條規(guī)定是一個好習(xí)慣，不過除非你要處理一個非常大型的數(shù)據(jù)，否則你將不需要用到它。（例如一個通行證系統(tǒng)，我可以將 USERID，USERNAME，USERPASSWORD，單獨出來作個表，再把USERID作為其他表的外鍵） 表的設(shè)計具體注意的問題： 1、數(shù)據(jù)行的長度不要超過8020字節(jié)，如果超過這個長度的話在物理頁中這條數(shù)據(jù)會占用兩行從而造成存儲碎片，降低查詢效率。 2、能夠用數(shù)字類型的字段盡量選擇數(shù)字類型而不用字符串類型的（電話號碼），這會降低查詢和連接的性能，并會增加存儲開銷。這是因為引擎在處理查詢和連接回逐個比較字符串中每一個字符，而對于數(shù)字型而言只需要比較一次就夠了。 3、對于不可變字符類型char和可變字符類型varchar 都是8000字節(jié),char查詢快，但是耗存儲空間，varchar查詢相對慢一些但是節(jié)省存儲空間。在設(shè)計字段的時候可以靈活選擇，例如用戶名、密碼等長度變化不大的字段可以選擇CHAR，對于評論等長度變化大的字段可以選擇VARCHAR。 4、字段的長度在最大限度的滿足可能的需要的前提下，應(yīng)該盡可能的設(shè)得短一些，這樣可以提高查詢的效率，而且在建立索引的時候也可以減少資源的消耗。二、查詢的優(yōu)化 保證在實現(xiàn)功能的基礎(chǔ)上，盡量減少對數(shù)據(jù)庫的訪問次數(shù)；通過搜索參數(shù)，盡量減少對表的訪問行數(shù),最小化結(jié)果集，從而減輕網(wǎng)絡(luò)負(fù)擔(dān)；能夠分開的操作盡量分開處理，提高每次的響應(yīng)速度；在數(shù)據(jù)窗口使用SQL時，盡量把使用的索引放在選擇的首列；算法的結(jié)構(gòu)盡量簡單；在查詢時，不要過多地使用通配符如SELECT * FROM T1語句，要用到幾列就選擇幾列如：SELECT COL1,COL2 FROM T1；在可能的情況下盡量限制盡量結(jié)果集行數(shù)如：SELECT TOP 300 COL1,COL2,COL3 FROM T1,因為某些情況下用戶是不需要那么多的數(shù)據(jù)的。 在沒有建索引的情況下，數(shù)據(jù)庫查找某一條數(shù)據(jù)，就必須進(jìn)行全表掃描了，對所有數(shù)據(jù)進(jìn)行一次遍歷，查找出符合條件的記錄。在數(shù)據(jù)量比較小的情況下，也許看不出明顯的差別，但是當(dāng)數(shù)據(jù)量大的情況下，這種情況就是極為糟糕的了。SQL語句在SQL SERVER中是如何執(zhí)行的，他們擔(dān)心自己所寫的SQL語句會被SQL SERVER誤解。比如： select * from table1 where name=zhangsan and tID 10000 和執(zhí)行: select * from table1 where tID 10000 and name=zhangsan 一些人不知道以上兩條語句的執(zhí)行效率是否一樣，因為如果簡單的從語句先后上看，這兩個語句的確是不一樣，如果tID是一個聚合索引，那么后一句僅僅從表的 10000條以后的記錄中查找就行了；而前一句則要先從全表中查找看有幾個name=zhangsan的，而后再根據(jù)限制條件條件tID 10000來提出查詢結(jié)果。 事實上，這樣的擔(dān)心是不必要的。SQL SERVER中有一個“查詢分析優(yōu)化器”，它可以計算出where子句中的搜索條件并確定哪個索引能縮小表掃描的搜索空間，也就是說，它能實現(xiàn)自動優(yōu)化。雖然查詢優(yōu)化器可以根據(jù)where子句自動的進(jìn)行查詢優(yōu)化，但有時查詢優(yōu)化器就會不按照您的本意進(jìn)行快速查詢。 在查詢分析階段，查詢優(yōu)化器查看查詢的每個階段并決定限制需要掃描的數(shù)據(jù)量是否有用。如果一個階段可以被用作一個掃描參數(shù)（SARG），那么就稱之為可優(yōu)化的，并且可以利用索引快速獲得所需數(shù)據(jù)。 SARG的定義：用于限制搜索的一個操作，因為它通常是指一個特定的匹配，一個值的范圍內(nèi)的匹配或者兩個以上條件的AND連接。形式如下： 列名 操作符 或 操作符 列名 列名可以出現(xiàn)在操作符的一邊，而常數(shù)或變量出現(xiàn)在操作符的另一邊。如： Name=張三 價格5000 50005000 如果一個表達(dá)式不能滿足SARG的形式，那它就無法限制搜索的范圍了，也就是SQL SERVER必須對每一行都判斷它是否滿足WHERE子句中的所有條件。所以一個索引對于不滿足SARG形式的表達(dá)式來說是無用的。 所以，優(yōu)化查詢最重要的就是，盡量使語句符合查詢優(yōu)化器的規(guī)則避免全表掃描而使用索引查詢。具體要注意的：1.應(yīng)盡量避免在 where 子句中對字段進(jìn)行 null 值判斷，否則將導(dǎo)致引擎放棄使用索引而進(jìn)行全表掃描，如：select id from t where num is null可以在num上設(shè)置默認(rèn)值0，確保表中num列沒有null值，然后這樣查詢：select id from t where num=02.應(yīng)盡量避免在 where 子句中使用!=或操作符，否則將引擎放棄使用索引而進(jìn)行全表掃描。優(yōu)化器將無法通過索引來確定將要命中的行數(shù),因此需要搜索該表的所有行。3.應(yīng)盡量避免在 where 子句中使用 or 來連接條件，否則將導(dǎo)致引擎放棄使用索引而進(jìn)行全表掃描，如：select id from t where num=10 or num=20可以這樣查詢：select id from t where num=10union allselect id from t where num=204.in 和 not in 也要慎用，因為IN會使系統(tǒng)無法使用索引,而只能直接搜索表中的數(shù)據(jù)。如：select id from t where num in(1,2,3)對于連續(xù)的數(shù)值，能用 between 就不要用 in 了：select id from t where num between 1 and 35.盡量避免在索引過的字符數(shù)據(jù)中，使用非打頭字母搜索。這也使得引擎無法利用索引。 見如下例子： SELECT * FROM T1 WHERE NAME LIKE %L% SELECT * FROM T1 WHERE SUBSTING(NAME,2,1)=L SELECT * FROM T1 WHERE NAME LIKE L% 即使NAME字段建有索引，前兩個查詢依然無法利用索引完成加快操作，引擎不得不對全表所有數(shù)據(jù)逐條操作來完成任務(wù)。而第三個查詢能夠使用索引來加快操作。6.必要時強(qiáng)制查詢優(yōu)化器使用某個索引，如在 where 子句中使用參數(shù)，也會導(dǎo)致全表掃描。因為SQL只有在運行時才會解析局部變量，但優(yōu)化程序不能將訪問計劃的選擇推遲到運行時；它必須在編譯時進(jìn)行選擇。然而，如果在編譯時建立訪問計劃，變量的值還是未知的，因而無法作為索引選擇的輸入項。如下面語句將進(jìn)行全表掃描：select id from t where num=num可以改為強(qiáng)制查詢使用索引：select id from t with(index(索引名) where num=num7.應(yīng)盡量避免在 where 子句中對字段進(jìn)行表達(dá)式操作，這將導(dǎo)致引擎放棄使用索引而進(jìn)行全表掃描。如：SELECT * FROM T1 WHERE F1/2=100 應(yīng)改為: SELECT * FROM T1 WHERE F1=100*2SELECT * FROM RECORD WHERE SUBSTRING(CARD_NO,1,4)=5378 應(yīng)改為: SELECT * FROM RECORD WHERE CARD_NO LIKE 5378%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 dateofbirth =2005-11-30 and createdate0) SELECT SUM(T1.C1) FROM T1WHERE EXISTS( SELECT * FROM T2 WHERE T2.C2=T1.C2) 兩者產(chǎn)生相同的結(jié)果，但是后者的效率顯然要高于前者。因為后者不會產(chǎn)生大量鎖定的表掃描或是索引掃描。如果你想校驗表里是否存在某條紀(jì)錄，不要用count(*)那樣效率很低，而且浪費服務(wù)器資源。可以用EXISTS代替。如： IF (SELECT COUNT(*) FROM table_name WHERE column_name = xxx) 可以寫成： IF EXISTS (SELECT * FROM table_name WHERE column_name = xxx)經(jīng)常需要寫一個T_SQL語句比較一個父結(jié)果集和子結(jié)果集，從而找到是否存在在父結(jié)果集中有而在子結(jié)果集中沒有的記錄，如： SELECT a.hdr_key FROM hdr_tbl a- tbl a 表示tbl用別名a代替 WHERE NOT EXISTS (SELECT * FROM dtl_tbl b WHERE a.hdr_key = b.hdr_key) SELECT a.hdr_key FROM hdr_tbl a LEFT JOIN dtl_tbl b ON a.hdr_key = b.hdr_key WHERE b.hdr_key IS NULL SELECT hdr_key FROM hdr_tbl WHERE hdr_key NOT IN (SELECT hdr_key FROM dtl_tbl) 三種寫法都可以得到同樣正確的結(jié)果，但是效率依次降低。12.盡量使用表變量來代替臨時表。如果表變量包含大量數(shù)據(jù)，請注意索引非常有限（只有主鍵索引）。13.避免頻繁創(chuàng)建和刪除臨時表，以減少系統(tǒng)表資源的消耗。14.臨時表并不是不可使用，適當(dāng)?shù)厥褂盟鼈兛梢允鼓承├谈行?，例如，?dāng)需要重復(fù)引用大型表或常用表中的某個數(shù)據(jù)集時。但是，對于一次性事件，最好使用導(dǎo)出表。15.在新建臨時表時，如果一次性插入數(shù)據(jù)量很大，那么可以使用 select into 代替 create table，避免造成大量 log ，以提高速度；如果數(shù)據(jù)量不大，為了緩和系統(tǒng)表的資源，應(yīng)先create table，然后insert。16.如果使用到了臨時表，在存儲過程的最后務(wù)必將所有的臨時表顯式刪除，先 truncate table ，然后 drop table ，這樣可以避免系統(tǒng)表的較長時間鎖定。 17.在所有的存儲過程和觸發(fā)器的開始處設(shè)置 SET NOCOUNT ON ，在結(jié)束時設(shè)置 SET NOCOUNT OFF 。無需在執(zhí)行存儲過程和觸發(fā)器的每個語句后向客戶端發(fā)送 DONE_IN_PROC 消息。18.盡量避免大事務(wù)操作，提高系統(tǒng)并發(fā)能力。19.盡量避免向客戶端返回大數(shù)據(jù)量，若數(shù)據(jù)量過大，應(yīng)該考慮相應(yīng)需求是否合理。 20. 避免使用不兼容的數(shù)據(jù)類型。例如float和int、char和varchar、binary和varbinary是不兼容的。數(shù)據(jù)類型的不兼容可能使優(yōu)化器無法執(zhí)行一些本來可以進(jìn)行的優(yōu)化操作。例如: SELECT name FROM employee WHERE salary 60000 在這條語句中,如salary字段是money型的,則優(yōu)化器很難對其進(jìn)行優(yōu)化,因為60000是個整型數(shù)。我們應(yīng)當(dāng)在編程時將整型轉(zhuǎn)化成為錢幣型,而不要等到運行時轉(zhuǎn)化。21.充分利用連接條件，在某種情況下，兩個表之間可能不只一個的連接條件，這時在 WHERE 子句中將連接條件完整的寫上，有可能大大提高查詢速度。 例： SELECT SUM(A.AMOUNT) FROM ACCOUNT A,CARD B WHERE A.CARD_NO = B.CARD_NO SELECT SUM(A.AMOUNT) FROM ACCOUNT A,CARD B WHERE A.CARD_NO = B.CARD_NO AND A.ACCOUNT_NO=B.ACCOUNT_NO 第二句將比第一句執(zhí)行快得多。22、使用視圖加速查詢 把表的一個子集進(jìn)行排序并創(chuàng)建視圖，有時能加速查詢。它有助于避免多重排序 操作，而且在其他方面還能簡化優(yōu)化器的工作。例如：SELECT ，rcvbles.balance，other columns FROM cust，rcvbles WHERE cust.customer_id = rcvlbes.customer_id AND rcvblls.balance0 AND cust.postcode“98000” ORDER BY 如果這個查詢要被執(zhí)行多次而不止一次，可以把所有未付款的客戶找出來放在一個視圖中，并按客戶的名字進(jìn)行排序： CREATE VIEW DBO.V_CUST_RCVLBES AS SELECT ，rcvbles.balance，other columns FROM cust，rcvbles WHERE cust.customer_id = rcvlbes.customer_id AND rcvblls.balance0 ORDER BY 然后以下面的方式在視圖中查詢： SELECT FROM V_CUST_RCVLBES WHERE postcode“98000” 視圖中的行要比主表中的行少，而且物理順序就是所要求的順序，減少了磁盤I/O，所以查詢工作量可以得到大幅減少。23、能用DISTINCT的就不用GROUP BY SELECT OrderID FROM Details WHERE UnitPrice 10 GROUP BY OrderID 可改為： SELECT DISTINCT OrderID FROM Details WHERE UnitPrice 1024.能用UNION ALL就不要用UNION UNION ALL不執(zhí)行SELECT DISTINCT函數(shù)，這樣就會減少很多不必要的資源 35.盡量不要用SELECT INTO語句。 SELECT INOT 語句會導(dǎo)致表鎖定，阻止其他用戶訪問該表。 上面我們提到的是一些基本的提高查詢速度的注意事項,但是在更多的情況下,往往需要反復(fù)試驗比較不同的語句以得到最佳方案。最好的方法當(dāng)然是測試，看實現(xiàn)相同功能的SQL語句哪個執(zhí)行時間最少，但是數(shù)據(jù)庫中如果數(shù)據(jù)量很少，是比較不出來的，這時可以用查看執(zhí)行計劃，即：把實現(xiàn)相同功能的多條SQL語句考到查詢分析器，按CTRL+L看查所利用的索引，表掃描次數(shù)（這兩個對性能影響最大），總體上看詢成本百分比即可。 三、算法的優(yōu)化盡量避免使用游標(biāo)，因為游標(biāo)的效率較差，如果游標(biāo)操作的數(shù)據(jù)超過1萬行，那么就應(yīng)該考慮改寫。.使用基于游標(biāo)的方法或臨時表方法之前，應(yīng)先尋找基于集的解決方案來解決問題，基于集的方法通常更有效。與臨時表一樣，游標(biāo)并不是不可使用。對小型數(shù)據(jù)集使用 FAST_FORWARD 游標(biāo)通常要優(yōu)于其他逐行處理方法，尤其是在必須引用幾個表才能獲得所需的數(shù)據(jù)時。在結(jié)果集中包括“合計”的例程通常要比使用游標(biāo)執(zhí)行的速度快。如果開發(fā)時間允許，基于游標(biāo)的方法和基于集的方法都可以嘗試一下，看哪一種方法的效果更好。游標(biāo)提供了對特定集合中逐行掃描的手段，一般使用游標(biāo)逐行遍歷數(shù)據(jù)，根據(jù)取出的數(shù)據(jù)不同條件進(jìn)行不同的操作。尤其對多表和大表定義的游標(biāo)（大的數(shù)據(jù)集合）循環(huán)很容易使程序進(jìn)入一個漫長的等特甚至死機(jī)。 在有些場合，有時也非得使用游標(biāo)，此時也可考慮將符合條件的數(shù)據(jù)行轉(zhuǎn)入臨時表中，再對臨時表定義游標(biāo)進(jìn)行操作，可時性能得到明顯提高。（例如：對內(nèi)統(tǒng)計第一版）封裝存儲過程四、建立高效的索引 創(chuàng)建索引一般有以下兩個目的：維護(hù)被索引列的唯一性和提供快速訪問表中數(shù)據(jù)的策略。大型數(shù)據(jù)庫有兩種索引即簇索引和非簇索引，一個沒有簇索引的表是按堆結(jié)構(gòu)存儲數(shù)據(jù)，所有的數(shù)據(jù)均添加在表的尾部，而建立了簇索引的表，其數(shù)據(jù)在物理上會按照簇索引鍵的順序存儲，一個表只允許有一個簇索引，因此，根據(jù)B樹結(jié)構(gòu)，可以理解添加任何一種索引均能提高按索引列查詢的速度，但會降低插入、更新、刪除操作的性能，尤其是當(dāng)填充因子（Fill Factor）較大時。所以對索引較多的表進(jìn)行頻繁的插入、更新、刪除操作，建表和索引時因設(shè)置較小的填充因子，以便在各數(shù)據(jù)頁中留下較多的自由空間，減少頁分割及重新組織的工作。 索引是從數(shù)據(jù)庫中獲取數(shù)據(jù)的最高效方式之一。95% 的數(shù)據(jù)庫性能問題都可以采用索引技術(shù)得到解決。作為一條規(guī)則，我通常對邏輯主鍵使用唯一的成組索引，對系統(tǒng)鍵（作為存儲過程）采用唯一的非成組索引，對任何外鍵列字段采用非成組索引。不過，索引就象是鹽，太多了菜就咸了。你得考慮數(shù)據(jù)庫的空間有多大，表如何進(jìn)行訪問，還有這些訪問是否主要用作讀寫。 實際上，您可以把索引理解為一種特殊的目錄。微軟的SQL SERVER提供了兩種索引：聚集索引（clustered index，也稱聚類索引、簇集索引）和非聚集索引（nonclustered index，也稱非聚類索引、非簇集索引）。下面，我們舉例來說明一下聚集索引和非聚集索引的區(qū)別： 其實，我們的漢語字典的正文本身就是一個聚集索引。比如，我們要查“安”字，就會很自然地翻開字典的前幾頁，因為“安”的拼音是“an”，而按照拼音排序漢字的字典是以英文字母“a”開頭并以 “z”結(jié)尾的，那么“安”字就自然地排在字典的前部。如果您翻完了所有以“a”開頭的部分仍然找不到這個字，那么就說明您的字典中沒有這個字；同樣的，如果查“張”字，那您也會將您的字典翻到最后部分，因為“張”的拼音是“zhang”。也就是說，字典的正文部分本身就是一個目錄，您不需要再去查其他目錄來找到您需要找的內(nèi)容。 我們把這種正文內(nèi)容本身就是一種按照一定規(guī)則排列的目錄稱為“聚集索引”。 如果您認(rèn)識某個字，您可以快速地從自動中查到這個字。但您也可能會遇到您不認(rèn)識的字，不知道它的發(fā)音，這時候，您就不能按照剛才的方法找到您要查的字，而需要去根據(jù)“偏旁部首”查到您要找的字，然后根據(jù)這個字后的頁碼直接翻到某頁來找到您要找的字。但您結(jié)合“部首目錄”和“檢字表”而查到的字的排序并不是真正的正文的排序方法，比如您查 “張”字，我們可以看到在查部首之后的檢字表中“張”的頁碼是672頁，檢字表中“張”的上面是“馳”字，但頁碼卻是63頁，“張”的下面是“弩”字，頁面是390頁。很顯然，這些字并不是真正的分別位于“張”字的上下方，現(xiàn)在您看到的連續(xù)的“馳、張、弩”三字實際上就是他們在非聚集索引中的排序，是字典正文中的字在非聚集索引中的映射。我們可以通過這種方式來找到您所需要的字，但它需要兩個過程，先找到目錄中的結(jié)果，然后再翻到您所需要的頁碼。 我們把這種目錄純粹是目錄，正文純粹是正文的排序方式稱為“非聚集索引”。 進(jìn)一步引申一下，我們可以很容易的理解：每個表只能有一個聚集索引，因為目錄只能按照一種方法進(jìn)行排序。（一）何時使用聚集索引或非聚集索引 下面的表總結(jié)了何時使用聚集索引或非聚集索引（很重要）。 動作描述 使用聚集索引 使用非聚集索引 列經(jīng)常被分組排序 應(yīng) 應(yīng) 返回某范圍內(nèi)的數(shù)據(jù) 應(yīng) 不應(yīng) 一個或極少不同值 不應(yīng) 不應(yīng) 小數(shù)目的不同值 應(yīng) 不應(yīng) 大數(shù)目的不同值 不應(yīng) 應(yīng) 頻繁更新的列 不應(yīng) 應(yīng) 外鍵列 應(yīng) 應(yīng) 主鍵列 應(yīng) 應(yīng) 頻繁修改索引列 不應(yīng) 應(yīng)事實上，我們可以通過前面聚集索引和非聚集索引的定義的例子來理解上表。如：返回某范圍內(nèi)的數(shù)據(jù)一項。比如您的某個表有一個時間列，恰好您把聚合索引建立在了該列，這時您查詢2004年1月1日至2004年10月1日之間的全部數(shù)據(jù)時，這個速度就將是很快的，因為您的這本字典正文是按日期進(jìn)行排序的，聚類索引只需要找到要檢索的所有數(shù)據(jù)中的開頭和結(jié)尾數(shù)據(jù)即可；而不像非聚集索引，必須先查到目錄中查到每一項數(shù)據(jù)對應(yīng)的頁碼，然后再根據(jù)頁碼查到具體內(nèi)容。（二）結(jié)合實際，談索引使用的誤區(qū)理論的目的是應(yīng)用。雖然我們剛才列出了何時應(yīng)使用聚集索引或非聚集索引，但在實踐中以上規(guī)則卻很容易被忽視或不能根據(jù)實際情況進(jìn)行綜合分析。下面我們將根據(jù)在實踐中遇到的實際問題來談一下索引使用的誤區(qū)，以便于大家掌握索引建立的方法。 1、主鍵就是聚集索引 這種想法筆者認(rèn)為是極端錯誤的，是對聚集索引的一種浪費。雖然SQL SERVER默認(rèn)是在主鍵上建立聚集索引的。 通常，我們會在每個表中都建立一個ID列，以區(qū)分每條數(shù)據(jù)，并且這個ID列是自動增大的，步長一般為1。我們的這個辦公自動化的實例中的列Gid就是如此。此時，如果我們將這個列設(shè)為主鍵，SQL SERVER會將此列默認(rèn)為聚集索引。這樣做有好處，就是可以讓您的數(shù)據(jù)在數(shù)據(jù)庫中按照ID進(jìn)行物理排序，但筆者認(rèn)為這樣做意義不大。 顯而易見，聚集索引的優(yōu)勢是很明顯的，而每個表中只能有一個聚集索引的規(guī)則，這使得聚集索引變得更加珍貴。 從我們前面談到的聚集索引的定義我們可以看出，使用聚集索引的最大好處就是能夠根據(jù)查詢要求，迅速縮小查詢范圍，避免全表掃描。在實際應(yīng)用中，因為ID號是自動生成的，我們并不知道每條記錄的ID號，所以我們很難在實踐中用ID號來進(jìn)行查詢。這就使讓ID號這個主鍵作為聚集索引成為一種資源浪費。其次，讓每個ID號都不同的字段作為聚集索引也不符合“大