使用SQL-Server分割槽表功能提高資料庫的讀寫效能

Agile.Zhou發表於2021-02-18
SQLServer資料庫

首先祝大家新年快樂，身體健康，萬事如意。
一般來說一個系統最先出現瓶頸的點很可能是資料庫。比如我們的生產系統併發量很高在跑一段時間後，資料庫中某些表的資料量會越來越大。海量的資料會嚴重影響資料庫的讀寫效能。
這個時候我們會開始優化系統，一般會經過這麼幾個過程：

  1. 找出SQL慢查詢，針對該SQL進行優化，比如改進SQL的寫法，檢視執行計劃對全表掃描的欄位建立索引
  2. 引入快取，把一部分讀壓力載入到記憶體中
  3. 讀寫分離
  4. 引入佇列，把併發的請求使其序列化，來減輕系統瞬時壓力
  5. 分表/分庫

對於第五點優化方案我們來細說一下。分表分庫通常有兩種拆分維度：1.垂直切分，垂直切分往往跟業務有強相關關係，比如把某個表的某些不常用的欄位遷移出去，比如訂單的明細資料可以獨立成一張表，需要使用的時候才讀取 2.水平切分，比如按年份來拆分，把資料庫按年或者按某些規則按時間段分成多個表。
拆分表之後每個表的資料量將會變小，帶來的好處是不言而喻的。不管是全表掃描，還是索引查詢都會有比較高的提升。如果把不同的表檔案落在多個磁碟上那資料庫的IO效能還能進一步提高。
如果純手工拆分，比如按年份拆分成多個表，那麼上層業務程式碼也得進行調整。每次讀寫都得判斷該使用哪張表。如果是跨多個年份的分頁查詢更加難搞。人肉分表基本上不可能實現的，對於上層編碼簡直是個噩夢。所以針對分表分庫我們通常會使用某些中介軟體，比如Mycat，Sharding-JDBC等中介軟體。使用這些元件確實能實現分表分庫，並且對業務層程式碼遮蔽了資料庫架構的改動，但是配置略顯麻煩。如果你使用的是SQL Server資料庫，並且目前還不需要分庫，只需要分表，那麼其實使用內建的分割槽表功能是最簡單的方案。只需要開啟SQL Server Management Studio簡單設定幾下就可以了，對於你上層應用完全是無感的，你的程式碼、資料庫連線串都不需要改動。
以下我們通過2個簡單的測試，來簡單的演示下如何進行表分割槽操作，以及測試下分割槽前後效能變化。

測試寫效能

我們的測試方案：新建一張logs表，按年份寫入資料。2019年寫入1000000資料，2020年也寫入100000資料。為了加快寫入的速度，每個年份並行10個執行緒同時寫，每個執行緒寫100000資料，一共1000000資料。然後把logs表改成分割槽表再用同樣的方式寫入2000000資料。記錄耗時 比較兩次的耗時。
硬體為一臺14年產的筆記本，OS為win10。掛載2塊硬碟，1塊為5400轉的機械硬碟，1塊為15年加的SSD。磁碟效能可以說極為垃圾。未分割槽時表檔案會落在機械硬碟上。

未分割槽情況下測試

使用指令碼建表：

CREATE TABLE [dbo].[logs](
	[id] [uniqueidentifier] NOT NULL,
	[log_txt] [varchar](200) NULL,
	[log_time] [datetime] NULL,
 CONSTRAINT [PK_logs] PRIMARY KEY CLUSTERED 
(
	[id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
)

新建一個控制檯程式編寫程式碼：

class Program
    {
        static void Main(string[] args)
        {
            Console.WriteLine("Hello World!");
            Task.Run(() =>
            {
                InsertData(2019);
            });
            Task.Run(() =>
            {
                InsertData(2020);
            });
            Console.ReadLine();
        }

        static void InsertData(int year)
        {
            var tasks = new List<Task>();
            Stopwatch sw = new Stopwatch();
            sw.Start();
            for (int i = 0; i < 10; i++)
            {
                tasks.Add(Task.Run(()=> {
                    using (var conn = new SqlConnection())
                    {
                        conn.ConnectionString = "Persist Security Info = False; User ID =sa; Password =dev@123; Initial Catalog =fq_test; Server =.\\mssql2016";
                        conn.Open();
                        int index = 0;
                        for (int j = 0; j < 100000; j++)
                        {
                            var logtime = new DateTime(year, new Random().Next(1, 12), new Random().Next(1, 28));
                            conn.Execute("insert into logs2 values (newid(),'下訂單',@logtime)", new
                            {
                                logtime
                            });
                            Console.WriteLine("logtime:{0} index {1}", logtime, index++);
                        }
                    }
                }));
            }
            Task.WaitAll(tasks.ToArray());
            sw.Stop();
            Console.WriteLine("Year {0} complete , total time: {1}.", year, sw.ElapsedMilliseconds);
        }
    }

y0cke1.png
寫完2000000資料耗時1369454毫秒。

分割槽情況下進行測試

開始分割槽

把一個表設定為分割槽表大概有5個步驟：

  1. 新增檔案組
  2. 在檔案組新增檔案
  3. 新建分割槽函式
  4. 新建分割槽方案
  5. 開始分割槽

以下演示下如何使用SQL SERVER Management Studio管理器進行表分割槽：
y02dZn.png
選中資料庫=>屬性=>檔案組，新增group1，group2兩個檔案組。
y02waq.png
選中資料庫=>屬性=>檔案。新增file1，檔案組選group1，路徑選擇一個檔案目錄。這裡選擇E盤data目錄。新增file2，檔案組選擇group2，路徑選擇一個檔案目錄。這裡選擇X盤的data目錄。這樣當分割槽的時候資料就會落在這2個目錄下。這裡的路徑可以選擇在同一個硬碟，但是為了更高的讀寫效能，如果有條件建議直接指定在不同的硬碟下。
y0ciLR.png
選中logs表=>儲存=>建立分割槽，啟動分割槽嚮導工具。
y0cCQJ.png
新建一個分割槽函式，點選下一步。
yyYBZT.png
新建一個分割槽方案，點選下一步。
y0cPy9.png
選擇一個分割槽列，資料會根據該列進行水平拆分。這裡選擇logtime，因為時間是比較適合水平切分的一個維度。
y0cpz4.png
值得資料拆分的範圍。範圍選擇“右邊界”。右邊界跟左邊界的差異在於對邊界值的處理。右邊界是<，左邊界是<=，也就是包含邊界值。
我們這裡設定group1儲存2019的資料，group2儲存2020的資料。所以group1的邊界值設定為2020-01-01，group2的邊界值設定為2021-01-01 。
y06zJU.png
設定完是這個樣子，需要3個檔案組。當出現不在group1，group2範圍內的資料就會儲存在第三個檔案組內。
y06joV.png
y06Xd0.png
建好分割槽函式、分割槽方案後，可以選擇生成指令碼或者立即執行。這裡選擇“立即執行”。當執行完成後，表裡的資料會按照分割槽方案設定的邊界分散到多個檔案上。

在分割槽情況下進行測試

y0cSWF.png
先清空logs表所有的資料，然後使用同樣的程式碼進行測試。測試結果顯示寫完2000000資料耗時：568903毫秒。可以看到資料庫寫效能大副提高，大概提高了1倍不止的效能。這也比較符合兩塊磁碟同時IO的預期。

測試讀效能

我們的測試方案：新建一張log2表，使用上面的程式碼按年份寫入2000000資料。然後使用select語句同時讀取2019,2020年的資料。把log錶轉換成分割槽表，重新測試select的時間。比較兩次讀取資料的時間。
sql語句：

select * from log2 where (logtime > '2019-05-01' and logtime < '2019-06-01') or (logtime > '2020-05-01' and logtime < '2020-06-01')

y02E8O.png
首先在未分割槽的表上測試查詢效能，花費時間為3s。
y02APK.png
把表按前面的方法進行分割槽拆分，查詢花費時間為1s。讀效能大概為未分割槽時的3倍。

總結

經過簡單的測試，SQL Server的分割槽表功能能大副提高資料庫的讀寫效能。通過SQL Server Management Stduio的簡單設定就可以對資料庫表進行分割槽操作，並且對應用層的程式碼完全是無感的，比用分表分庫中介軟體來說簡單多了。

關注我的公眾號一起玩轉技術

相關文章