<sub id="gqw76"><listing id="gqw76"></listing></sub>
      <sub id="gqw76"><listing id="gqw76"></listing></sub>

    1. <form id="gqw76"><legend id="gqw76"></legend></form>
    2. 使用SQL-Server分區表功能提高數據庫的讀寫性能

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

      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的簡單設置就可以對數據庫表進行分區操作,并且對應用層的代碼完全是無感的,比用分表分庫中間件來說簡單多了。

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

      posted @ 2021-02-18 09:58  Agile.Zhou  閱讀(2442)  評論(25編輯  收藏
      最新chease0ldman老人|无码亚洲人妻下载|大香蕉在线看好吊妞视频这里有精品www|亚洲色情综合网

        <sub id="gqw76"><listing id="gqw76"></listing></sub>
        <sub id="gqw76"><listing id="gqw76"></listing></sub>

      1. <form id="gqw76"><legend id="gqw76"></legend></form>