一般來說一個系統(tǒng)最先出現(xiàn)瓶頸的點很可能是數(shù)據(jù)庫。比如我們的生產(chǎn)系統(tǒng)并發(fā)量很高在跑一段時間后，數(shù)據(jù)庫中某些表的數(shù)據(jù)量會越來越大。海量的數(shù)據(jù)會嚴(yán)重影響數(shù)據(jù)庫的讀寫性能。

這個時候我們會開始優(yōu)化系統(tǒng)，一般會經(jīng)過這么幾個過程：

找出SQL慢查詢，針對該SQL進行優(yōu)化，比如改進SQL的寫法，查看執(zhí)行計劃對全表掃描的字段建立索引

引入緩存，把一部分讀壓力加載到內(nèi)存中

讀寫分離

引入隊列，把并發(fā)的請求使其串行化，來減輕系統(tǒng)瞬時壓力

分表/分庫

對于第五點優(yōu)化方案我們來細說一下。分表分庫通常有兩種拆分維度：1.垂直切分，垂直切分往往跟業(yè)務(wù)有強相關(guān)關(guān)系，比如把某個表的某些不常用的字段遷移出去，比如訂單的明細數(shù)據(jù)可以獨立成一張表，需要使用的時候才讀取 2.水平切分，比如按年份來拆分，把數(shù)據(jù)庫按年或者按某些規(guī)則按時間段分成多個表。

拆分表之后每個表的數(shù)據(jù)量將會變小，帶來的好處是不言而喻的。不管是全表掃描，還是索引查詢都會有比較高的提升。如果把不同的表文件落在多個磁盤上那數(shù)據(jù)庫的IO性能還能進一步提高。

如果純手工拆分，比如按年份拆分成多個表，那么上層業(yè)務(wù)代碼也得進行調(diào)整。每次讀寫都得判斷該使用哪張表。如果是跨多個年份的分頁查詢更加難搞。人肉分表基本上不可能實現(xiàn)的，對于上層編碼簡直是個噩夢。所以針對分表分庫我們通常會使用某些中間件，比如Mycat，Sharding-JDBC等中間件。使用這些組件確實能實現(xiàn)分表分庫，并且對業(yè)務(wù)層代碼屏蔽了數(shù)據(jù)庫架構(gòu)的改動，但是配置略顯麻煩。如果你使用的是SQL Server數(shù)據(jù)庫，并且目前還不需要分庫，只需要分表，那么其實使用內(nèi)置的分區(qū)表功能是最簡單的方案。只需要打開SQL Server Management Studio簡單設(shè)置幾下就可以了，對于你上層應(yīng)用完全是無感的，你的代碼、數(shù)據(jù)庫連接串都不需要改動。

以下我們通過2個簡單的測試，來簡單的演示下如何進行表分區(qū)操作，以及測試下分區(qū)前后性能變化。

測試寫性能

我們的測試方案：新建一張logs表，按年份寫入數(shù)據(jù)。2019年寫入1000000數(shù)據(jù)，2020年也寫入100000數(shù)據(jù)。為了加快寫入的速度，每個年份并行10個線程同時寫，每個線程寫100000數(shù)據(jù)，一共1000000數(shù)據(jù)。然后把logs表改成分區(qū)表再用同樣的方式寫入2000000數(shù)據(jù)。記錄耗時 比較兩次的耗時。
硬件為一臺14年產(chǎn)的筆記本，OS為win10。掛載2塊硬盤，1塊為5400轉(zhuǎn)的機械硬盤，1塊為15年加的SSD。磁盤性能可以說極為垃圾。未分區(qū)時表文件會落在機械硬盤上。

未分區(qū)情況下測試

使用腳本建表：

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);
        }
    }

寫完2000000數(shù)據(jù)耗時1369454毫秒。

分區(qū)情況下進行測試 開始分區(qū)

把一個表設(shè)置為分區(qū)表大概有5個步驟：

添加文件組

在文件組添加文件

新建分區(qū)函數(shù)

新建分區(qū)方案

開始分區(qū)

以下演示下如何使用SQL SERVER Management Studio管理器進行表分區(qū)：

選中數(shù)據(jù)庫=>屬性=>文件組，添加group1，group2兩個文件組。

選中數(shù)據(jù)庫=>屬性=>文件。添加file1，文件組選group1，路徑選擇一個文件目錄。這里選擇E盤data目錄。添加file2，文件組選擇group2，路徑選擇一個文件目錄。這里選擇X盤的data目錄。這樣當(dāng)分區(qū)的時候數(shù)據(jù)就會落在這2個目錄下。這里的路徑可以選擇在同一個硬盤，但是為了更高的讀寫性能，如果有條件建議直接指定在不同的硬盤下。

選中l(wèi)ogs表=>存儲=>創(chuàng)建分區(qū)，啟動分區(qū)向?qū)Чぞ摺?/p>

新建一個分區(qū)函數(shù)，點擊下一步。

新建一個分區(qū)方案，點擊下一步。

選擇一個分區(qū)列，數(shù)據(jù)會根據(jù)該列進行水平拆分。

這里選擇logtime，因為時間是比較適合水平切分的一個維度。

值得數(shù)據(jù)拆分的范圍。

范圍選擇“右邊界”。

右邊界跟左邊界的差異在于對邊界值的處理。

右邊界是<，左邊界是<=，也就是包含邊界值。

我們這里設(shè)置group1存儲2019的數(shù)據(jù)，group2存儲2020的數(shù)據(jù)。

所以group1的邊界值設(shè)置為2020-01-01，group2的邊界值設(shè)置為2021-01-01 。

設(shè)置完是這個樣子，需要3個文件組。

當(dāng)出現(xiàn)不在group1，group2范圍內(nèi)的數(shù)據(jù)就會存儲在第三個文件組內(nèi)。

建好分區(qū)函數(shù)、分區(qū)方案后，可以選擇生成腳本或者立即執(zhí)行。

這里選擇“立即執(zhí)行”。

當(dāng)執(zhí)行完成后，表里的數(shù)據(jù)會按照分區(qū)方案設(shè)置的邊界分散到多個文件上。

在分區(qū)情況下進行測試

先清空logs表所有的數(shù)據(jù)，然后使用同樣的代碼進行測試。

測試結(jié)果顯示寫完2000000數(shù)據(jù)耗時：

568903毫秒。

可以看到數(shù)據(jù)庫寫性能大副提高，大概提高了1倍不止的性能。

這也比較符合兩塊磁盤同時IO的預(yù)期。

測試讀性能

我們的測試方案：新建一張log2表，使用上面的代碼按年份寫入2000000數(shù)據(jù)。然后使用select語句同時讀取2019,2020年的數(shù)據(jù)。把log表轉(zhuǎn)換成分區(qū)表，重新測試select的時間。比較兩次讀取數(shù)據(jù)的時間。

sql語句：

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

首先在未分區(qū)的表上測試查詢性能，花費時間為3s。

把表按前面的方法進行分區(qū)拆分，查詢花費時間為1s。

讀性能大概為未分區(qū)時的3倍。

總結(jié)

經(jīng)過簡單的測試，SQL Server的分區(qū)表功能能大副提高數(shù)據(jù)庫的讀寫性能。通過SQL Server Management Stduio的簡單設(shè)置就可以對數(shù)據(jù)庫表進行分區(qū)操作，并且對應(yīng)用層的代碼完全是無感的，比用分表分庫中間件來說簡單多了。

到此這篇關(guān)于使用SQL Server分區(qū)表功能提高數(shù)據(jù)庫的讀寫性能的文章就介紹到這了,更多相關(guān)SQL Server分區(qū)表內(nèi)容請搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持腳本之家！

最新評論