如何應付表資料過大的查詢問題?(如何盡量避免大表關聯)

     一般來說,對于做B/S架構的朋友來說,更有機會遇到高并發的資料庫通路情況,因為現在WEB的普及速度就像火箭升空,同時就會因為高通路量帶來一系列性能問題,而資料庫一直是使用者與商人之間交流的重要平台.使用者是沒有耐心忍受一個查詢需要用上10秒以上的，或者更少些，如果經常出現伺服器當機或者是報查詢逾時，我想那将是失敗的項目。做了幾年的WEB工作,不才,一直沒有遇到過大通路量或者是海量資料的情況.這裡并不是說沒有海量資料的項目就不是好項目,要看項目的應用場合.

     最近做項目時,偶然得到了這個機會,在我工作過程中,本人發現的單表最大記錄數高達9位數.像訂單表什麼的也有8位數.在查詢訂單的時候往往不能通過單表查詢就能解決,還要和其它相關表進行關聯查詢.如此關聯的表資料不大還好,一旦發生大表關聯大表,在查詢時就有可能出現慢長的等待。

    主旨: 如何避免這種情況的發生呢?既然有了這樣的資料,需求還是要實作,這裡就我最近針對資料庫的優化過程,我分兩篇文章來說明下.

    第一篇:如何盡量避免大表關聯.

    第二篇:對大表進行分區.

    背景:有兩張表:

       1:訂單表：記錄使用者訂單的詳細資訊.order,其中有一個會員卡号字段cardNo,訂單産生時間.

       2:會員表：記錄會員相關資訊.member,一個會員有一個代理号:proxyID,代理下面有許多的會員卡:cardNo,它們共用一個代理号.

       兩表通過cardNo來相關聯.

    需求:查詢一個使用者或者某些使用者某一時間段所有會員卡産生的訂單情況.

    實作SQL:

        select 字段 from order

            inner join member on

              order.cardNo=member.cardNo

                and member.proxyID in('a-01',代理号二)

                 and 時間 between '20080101' and '20080131'

   本人見解:我想一般的朋友看到這樣的需求大多會寫出這樣的查詢SQL,如果不喜歡用in或者認為in的性能不好的朋友可用union all 代替.SQL語句可以說簡單的不能再簡單了,本身并無問題,隻是如果兩表的資料都在百萬以上,而且字段都特别多.此時如果隻有索引的幫忙下并不一定能達到預期的效果.

    解決方案一:利用表變量來替換大表關聯,表變量的作用域為一個批處理,批處理完了,表變量也會随之失效,比起臨時表有它獨特的優點：不用手動去删除表變量以釋放記憶體。

   可行性:因為需求中的輸出字段大多來自訂單表,member表隻起到資料限制的作用,和查詢使用者會員卡号的作用,所有可以先把代理的會員卡号先取到表變量中,然後利用帶有卡号的表變量和訂單表相關聯查詢.

   declare @t table

   (cardNo int)

   insert @t

     select cardNo from member where in('a-01',代理号二)

   select 字段 from order

            inner join @t on

              [email protected] and 時間 between '20080101' and '20080131'

    這裡我就不貼性能比較圖了,有興趣的朋友可以自己嘗試下.這種方法在查詢人員比較多的時候特别有幫助.它要開發員根據實際情況詳細比較,結果并不是統一的,不同的環境結果可能不一樣.希望大家了解.

   解決方案二:利用索引視圖來提高大表關聯的性能.

   可行性:一般在大表關聯時,我們的輸出列都遠小于兩表的字段合,像上面的member表隻用到了其中的兩個字段(cardNo,proxyID).設想一下,此時的member表如果隻有這兩個字段情況會不會好些呢?答案不言而喻.

   視圖這個名詞在我以前對它的印象中,從來沒有認為視圖能優化查詢,因為我認為視圖對于資料庫來說就是一個虛假表,在資料庫中并無實際實體位置來存儲資料.對于使用者來說無非就是通過不同的視角來觀看結果.視圖資料

的産生都是實時的,即當調用視圖時,自動擴充視圖,去運作裡面相應的select語句.後來才知道在2000後的版本中視圖分一般視圖和索引視圖,一般視圖就是沒有建立索引的我印象中的視圖.而建立了視圖後就稱為索引視圖.索引視圖是實體存在的,可在視圖上首先建立一個唯一的聚集索引,其它字段上也可建立非聚集索引.在不改變基礎表的情況下,起到了優化的效果.

   CREATE VIEW memberView

WITH SCHEMABINDING

AS

    SELECT cardNo,proxyID from member

GO

--以會員卡号建立一個唯一聚集索引

CREATE UNIQUE CLUSTERED INDEX ix_member_cardNo

    ON member (cardNo);

     注意：建立索引視圖要點：

            1： CREATE VIEW memberView後面要跟上WITH SCHEMABINDING

                  理由：• 使用 schemaname.objectname 明确識别視圖所引用的所有對象，而不管是哪個使用者通路該視圖。

                           • 不會以導緻視圖定義非法或強制 SQL Server 在該視圖上重新建立索引的方式，更改視圖定義中所引用的對象。

            2：視圖上的第一個索引必須為 CLUSTERED 和 UNIQUE。

                  理由：必須為 UNIQUE 以便在維護索引視圖期間，輕松地按鍵值查找視圖中的記錄，并阻止建立帶有重複項目的視圖（要求維護特殊的邏輯）。必須為 CLUSTERED，因為隻有聚集索引才能在強制唯一性的同時存儲行。

            3:以下情況可考慮建立索引視圖：

               • 可預先計算聚合并将其儲存在索引中，進而在查詢執行時，最小化高成本的計算。

               • 可預先聯接各個表并儲存最終獲得的資料集。

               • 可儲存聯接或聚合的組合。

             4：基礎表的更新會引發索引視力的更新。

             5：索引視圖的建立同時會帶來維護上的開銷。

                  理由：1:因為索引視圖是實體存在的。

                           2:要額外的維護索引.

     實作:SQL:select 字段 from order

            inner join memberView on

                and member.proxyID=in('a-01',代理号二)

      總結：兩種解決方案來看，各有所長，一般可以優先考慮使用索引視圖來優化大表關聯。以上是本人對于如何盡量避免發生大表關聯所采取的措施，望大家指教。

注：

   本文參考：http://www.51cto.com/html/2005/1115/11396.htm