怎樣玩轉千萬級別的資料

　　大資料處理是一個頭疼的問題，特別當達不到專業DBA的技術水準時，對一些資料庫方面的問題感到無賴。所以還是有必要了解一些資料庫方面的技巧，當然，每個人都有自己的資料庫方面的技巧，只是八仙過海，所用的武功不同而已。我把我最常用的幾種方式總結來與大家分享，大家還有更多的資料庫設計和優化的技巧，儘量的追加到評論中，有時一篇完整的部落格評論比主題更為精彩。

　方法1：採用表分割槽技術。

 　　第一次聽說表分割槽，是以前的一個oracle培訓。oracle既然有表分割槽，就想到mssql是否有表的分割槽，當時我回家就google了一把，資料還是有的，在這我兒只是再作一次推廣，讓更多的人瞭解和運用這些技術。

　　表分割槽，就是將一個資料量比較大的表，用某種方法把資料從物理上分成若干個小表來儲存，從邏輯來看還是一個大表。首先來個結構圖：

　　

　　上圖雖然不能很清晰的表達表分割槽的執行過程，但是可以看出表分割槽要用到那些物件，比如資料檔案，檔案組，分割槽方案，分割槽函式等。

　　我們以一個使用者表(TestUser)為例，假設這個表準備用來儲存中國部分公民的資料，每條資料記錄著每個人所屬的省份(Area)，以及每個人的姓名(UserName)，如下圖所示。當資料量達到1千萬的時候，查詢就比較慢了，這時候的資料優化就迫在眉睫。

　　在優化之前，根據資料的結構，讀寫操作等，肯定會提出若干個解決方案。在這兒就以分割槽表的方案來優化資料庫的查詢，這兒以區域來分別儲存資料，比如廣東的公民存放在AreaFile01.MDF檔案中，湖南的公民存放在AreaFile02.MDF的檔案中，四川的公民存放在AreaFile03.MDF的檔案中，以此類推其它省份，為了實現這個功能我們就得做分割槽方案。在做分割槽方案時，首先要搞清楚分割槽方案要涉及到的四個物件：檔案組，檔案，分割槽函式，分割槽方案。

　　a：檔案組，用來組織資料檔案(.MDF)的一個虛擬名稱，一個檔案組可以新增多個資料檔案(.MDF)。開啟SQL管理器，找到具體的資料庫，然後右鍵【屬性】，進入到【檔案組】選項卡，新增Area01,Area02,Area03,Area04四個檔案組。如圖：

　　b：然後選擇中【檔案】選項卡，新增AreaFile01，AreaFile02，AreaFile03，AreaFile04，AreaFile05，AreaFile06六個資料檔案（.MDF）,然後指定每個檔案屬於那個檔案組(一個檔案組可以儲存多個資料檔案)，以及這個檔案的物理路徑。在這兒大家已經看明白了，這些資料檔案，就是物理上來分割一個資料表的資料的。也就是說一個表的資料有可能儲存在AreaFile01中，也有可能儲存在AreaFile02中，只要用某種方法來指定他們的儲存規則就行了。

　c：分割槽函式，就是指定資料的儲存規則。就是告訴SQL，把新增的資料如何分割槽。建立一個分割槽函式，可以用下邊的SQL語句來實現。

CREATE PARTITION FUNCTION partitionFunArea (nvarchar(50))
AS RANGE Left  FOR VALUES ('廣東','湖南','四川')

　　d：辛苦的建立了檔案，又為其指定檔案組，還建一個分割槽函式，目的只有一個，就是為了建立一個分割槽方案。分割槽方案可以用以下程式碼來建立。

CREATE PARTITION SCHEME partitionSchemeArea
AS PARTITION partitionFunArea
TO (
    Area01,
    Area02,
    Area03,
    Area04)

　　經過緊張的四步操作，一個分割槽方案就呈現在我們的眼前了。接下來的事，就是我們要怎樣來消費這個分割槽方案。

　　首先我們建立一人普通的表，然後給這個表指定一個分割槽方案。如下程式碼。

CREATE TABLE TestUser(
    [Id] [int] IDENTITY(1,1) NOT NULL,
    [Area] nvarchar(50),
    [UserName] nvarchar(50)
) ON partitionSchemeArea([Area])

　　為了能看到效果，再插入一些資料。

INSERT TestUser ([Area],[UserName]) Values('四川','肖一');
INSERT TestUser ([Area],[UserName]) Values('四川','肖二');
INSERT TestUser ([Area],[UserName]) Values('四川','肖三');
INSERT TestUser ([Area],[UserName]) Values('四川','肖四');

INSERT TestUser ([Area],[UserName]) Values('廣東','張一');
INSERT TestUser ([Area],[UserName]) Values('廣東','張二');
INSERT TestUser ([Area],[UserName]) Values('廣東','張三');

INSERT TestUser ([Area],[UserName]) Values('湖南','楊一');
INSERT TestUser ([Area],[UserName]) Values('湖南','楊二');

　　查詢所有的資料，可以用select * from TestUser; 按分割槽查詢：就用如下方法：

select $PARTITION.partitionFunArea([Area]) as 分割槽編號,count(id) as 記錄數 
from TestUser group by $PARTITION.partitionFunArea([Area])
select * from TestUser where $PARTITION.partitionFunArea([Area])=1
select * from TestUser where $PARTITION.partitionFunArea([Area])=2
select * from TestUser where $PARTITION.partitionFunArea([Area])=3
select * from TestUser where $PARTITION.partitionFunArea([Area])=4

　　效果圖：

　　你們看我一個簡單的表的分割槽是不是就已經完成了。呵呵，當然在實際應用中，僅僅掌握這點是不夠的，比如在原分割槽方案上新增一個分割槽，刪除一個分割槽。

　方法2：用xml型別代替主從表設計，從而達到提高查詢效能。

　　優化和提高資料庫的效能，是從一個良好的資料庫設計開始的。以一個會議預訂系統為例，一個預訂會議系統包括了會議時間，會議地點，主持人，參與人，知會人，記錄者等相關資訊。在的TDD，DDD模型主導的時代，在這兒為了更好的想表達我要闡述的問題，還是以表驅動模型來進行開發。

　　使用者需求：

　　a：一個會議可能有多個主持人，雖然這種情況比較少，但是也有可能有。

　　b：一個會議有多個參與人，這個不難理解。

　　c：一個會議有可能要讓某人知曉，這人可以參與或不參與會議，一般為高層。

　　d：一個會議有可能有零個或者多個記錄者。

　　e：一個會議需要遠端視訊，投影儀，電腦，麥克風等會議裝置中的某些裝置。

　　f：會議預訂成功，或者會議時間，會議地點等重要資訊修改後，郵件通知與會人員。

　　常規資料庫設計：

　　a：建一個Meeting的主表，用於存放會議名稱，會議地點，會議時間等的相關資訊。

　　b：再建一個MeetingUser的表儲存主持人，參與人，知會人，記錄者。

　　c：同樣，會議所需要的裝置用MeetingDevice表來儲存相關的資訊。如圖：

　　這樣的表結構，是比較常規的設計方法，但是在實際應用中，你會發現一些待改進的問題。比如：

　　a：在提取一個會議的相關資訊時，會連線多個表進行查詢。這種查詢在很大的程式上影響了資料庫效能。

　　b：在做修改操作時也夠嗆的，先修改主表的相關資訊，再把主表關聯的子表資訊全部刪除重新插入一次，這樣的操作是否夠吐血了。當然有人精益求精，會比較修改前和修改後的資料，再用增加，刪除，修改的手段達到子表資料的更新。這樣的操作在有些ORM操作中已經實現了，但當自己code程式碼來實現的時候，特別是在多次code的時候，感覺總是那麼煩心。

　　吐槽了這麼多，是否有更好的解決方案呢？當然，在SQL裡，我們可以XML資料型別來消除主從表的設計。如圖：

　　上面的表結構設計，是不是有一個小清新的感覺呢？很明顯，可以把第一種表的設計缺陷給消除了。一個會議的相關資訊都儲存在了一個表的一條記錄中，這樣的資料看起來是不是更直觀呢？

　　a：獲取一個預訂會議的詳細資訊，我不需要進行多個表的連線查詢，我要做的是隻需用C#的Linq.Xml來解析查詢出來的XML字串即可。

　　b：修改操作時，我只需要重新組合XML資料，一個Update就更新了與會議相關的資訊，操作是不是簡單多了。

　　表面上看這種設計已經完美了，但是使用者的需求是無止境的，有一天，你收到了一個需求，查詢某個使用者參與過的所有會議（就是隻要主持人，參與人，或者記錄者中包括了這個使用者，就把這些記錄都給查詢出來），Oh！My God  這種表結構設計應該怎麼解決這個問題呢？其實可以用XQuery解決這個問題，還沒接觸過XQuery的那得趕快充一下電了。XQuery中最常用的有exist(),value()這些函式，這兒就不詳細的介紹了，網上搜尋一下有很多相關資料，如果有必要，我會把以前專案中用的XQuery技巧與大家分享。