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

這個(gè)時(shí)候我們會(huì)開(kāi)始優(yōu)化系統(tǒng)，一般會(huì)經(jīng)過(guò)這么幾個(gè)過(guò)程：

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

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

讀寫(xiě)分離

引入隊(duì)列，把并發(fā)的請(qǐng)求使其串行化，來(lái)減輕系統(tǒng)瞬時(shí)壓力

分表/分庫(kù)

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

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

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

以下我們通過(guò)2個(gè)簡(jiǎn)單的測(cè)試，來(lái)簡(jiǎn)單的演示下如何進(jìn)行表分區(qū)操作，以及測(cè)試下分區(qū)前后性能變化。

測(cè)試寫(xiě)性能

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

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

使用腳本建表：

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]
)

新建一個(gè)控制臺(tái)程序編寫(xiě)代碼：

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

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

分區(qū)情況下進(jìn)行測(cè)試 開(kāi)始分區(qū)

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

添加文件組

在文件組添加文件

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

新建分區(qū)方案

開(kāi)始分區(qū)

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

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

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

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

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

新建一個(gè)分區(qū)方案，點(diǎn)擊下一步。

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

這里選擇logtime，因?yàn)闀r(shí)間是比較適合水平切分的一個(gè)維度。

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

范圍選擇“右邊界”。

右邊界跟左邊界的差異在于對(duì)邊界值的處理。

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

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

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

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

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

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

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

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

在分區(qū)情況下進(jìn)行測(cè)試

先清空l(shuí)ogs表所有的數(shù)據(jù)，然后使用同樣的代碼進(jìn)行測(cè)試。

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

568903毫秒。

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

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

測(cè)試讀性能

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

sql語(yǔ)句：

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

首先在未分區(qū)的表上測(cè)試查詢(xún)性能，花費(fèi)時(shí)間為3s。

把表按前面的方法進(jìn)行分區(qū)拆分，查詢(xún)花費(fèi)時(shí)間為1s。

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

總結(jié)

經(jīng)過(guò)簡(jiǎn)單的測(cè)試，SQL Server的分區(qū)表功能能大副提高數(shù)據(jù)庫(kù)的讀寫(xiě)性能。通過(guò)SQL Server Management Stduio的簡(jiǎn)單設(shè)置就可以對(duì)數(shù)據(jù)庫(kù)表進(jìn)行分區(qū)操作，并且對(duì)應(yīng)用層的代碼完全是無(wú)感的，比用分表分庫(kù)中間件來(lái)說(shuō)簡(jiǎn)單多了。

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

最新評(píng)論