線上交易記憶體內資料庫

作者：楊先民  
精誠資訊/恆逸教育訓練中心資深講師

※網路引用請註明完整出處

前言

SQL Server 2014之後開始支援記憶體內資料庫，本期的重點就擺在如何設定以支援記憶體內資料庫。

線上交易記憶體內資料庫

SQL Server 一直以來，所採用的資料存取方式，就是「最佳化讀寫硬碟」的模式，簡單來說，就是要讀的資料如果在 buffer 中可以找到，則從 buffer中取出，若資料在 buffer中不存在，則利用磁碟 I/O，將資料從磁碟中放置到 buffer，然後再將資料從 buffer中取回。

當然，這樣的招式對於資料庫的讀取效能隨著資料大小愈來愈大或是記憶體空間愈來愈不足，將會導致效能愈來愈慢，因為當記憶體不足時，一些原本在 buffer中的資料將會轉存回磁碟以保留空間容納新的資料暫存。

以上所提的是資料查詢時的狀況，若是資料異動的話，則動作基本上來說就有點不一樣了：

1. 會先存入 log
2. 將要異動的資料從磁碟載入 buffer，並且修改
3. 交易完成
4. 將異動完的資料寫回磁碟

是的，你沒有看錯，是將資料載入到 buffer後，將交易先完成再將資料寫回到磁碟，因為 buffer寫入磁碟的速度較慢，所以要先把交易完成再將 buffer資料寫回。由於我們有 log，所以如果動作發生問題，皆可以利用 log回復。

而記憶體內資料庫，和 SQL Server 2012所謂的「資料行存放區索引」這個功能不同，它仍以資料列為主，而且適用於交易型資料操作，SQL Server 2012的資料行存放區索引因為結構的改變，資料表只能唯讀，但記憶體內資料庫則是不然，和資料行存放區採用完全不同的做法，就一般的測試可以大概提升3到50倍左右的效能。


它有幾項特點，描述如下：

1. 以資料表為單位，存取頻繁的資料表放入完全獨立的記憶體區塊，和原來資料表所用的記憶體區塊(buffer)不同。不過不支援像是大型資料型別(MAX、XML或是 SQL CLR)…等。
   
   
2. 一旦設定資料表為記憶體最佳化(memory_optimized=on)，則不再存放於傳統的 mdf，ndf檔案中，完整記錄型式將存在於記憶體中。
   
   
3. 可以設定是否不需要交易記錄，一般異動最大的效能瓶頸出現在交易記錄的寫入，很多人會問一個資料庫設定多個 log檔案是否能加速資料的新增、修改與刪除，答案是不會，因為 log檔變多，其內容還是循序的存入到 log檔中，並不會因為你增加了多個 log檔案就使得資料表的新增、修改、刪除的速度變快。
   
   你可以設定持久性屬性 durability，可以不經過交易記錄(durability=schema_only)，交易記錄所寫的資訊會比之前版本來的更少，也可以使用非同步的延遲交易持久性，批次將記憶體內的交易記錄暫存區寫入硬碟以換來效能，但就有可能會遺失記錄。(速度快自然會有風險)
   
   
4. 採用記憶體為主，存取記憶體不使用8K分頁(page)，而是使用指標(pointer)串列。資料寫回磁碟也是使用循序的串流(streaming)的方式，所以磁碟存取使用循序不是隨機。
   
   
5. 鎖定機制完全改變，以 highly scalable concurrency control mechanism取代 lock manager，會發揮大量 CPU的平行運算能力。
   
   
6. 每個資料表都需要有至少一個以上的索引，但索引並不是放在磁碟，而是資料載入記憶體時當下建立索引。
   
   
7. 可以將預存程序編譯成機械碼(而不是 .NET managed code)，不過有些語法並不支援，需要測試，這樣執行的速度會更快。
   
   

記憶體資料庫，資料在查詢時，T-SQL可同時查詢記憶體最佳化資料表以及存放在硬碟上的舊格式資料表，此種查詢方式為「Interop查詢」，若是用編譯成機械碼的預存程序，稱為「原生預存程序」查詢，此時僅能存取記憶體最佳化資料表。

建立資料庫與記憶體最佳化物件

記憶體最佳化資料表的資料並未存在於硬碟上，不過還是有一組資料檔和差異檔案組(checkpoint檔案)，以循序的方式寫到硬碟，當 SQL Server正常或異常啟動時，這組檔案可用來搭配交易記錄檔回復資料庫資料，而資料檔與差異檔只能用附加的方式在檔尾新增資料，若資料檔放滿就會變成唯讀資料，不像 mdf、ndf檔案是採用隨機讀寫。

我們為了要放資料檔和差異檔，需要為資料庫指定「記憶體最佳化資料群組(Memory Optimized Data File Group)」，如下圖：

目前的管理工具，都可以使用圖型介面來設定檔案群組，你也可以使用指令的方式來設定，指令如下：

CREATE DATABASE [InMemberyTest]
 CONTAINMENT = NONE
 ON  PRIMARY 
( NAME = N'InMemberyTest', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\DATA\InMemberyTest.mdf' , SIZE = 5120KB , MAXSIZE = UNLIMITED, FILEGROWTH = 1024KB ), 
 FILEGROUP [INMemory] CONTAINS MEMORY_OPTIMIZED_DATA  DEFAULT
( NAME = N'InMemoryDir', FILENAME = N'c:\data\inmemorydir\InMemoryDir' , MAXSIZE = UNLIMITED)
 LOG ON 
( NAME = N'InMemberyTest_log', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\DATA\InMemberyTest_log.ldf' , SIZE = 1024KB , MAXSIZE = 2048GB , FILEGROWTH = 10%)
GO

總之，就是給一個檔案群組，設定它是記憶體最佳化資料群組，然後再建立資料表，可以指定是不是「記憶體最佳化資料表」，這個就需要下 T-SQL指令，目前還是沒有管理工具提供記憶體資料表的產生，如下指令：

CREATE TABLE [dbo].[Destination]
(
  [ID] [int] IDENTITY(1,1) NOT NULL,
  [Name] [nvarchar](10) COLLATE Chinese_Taiwan_Stroke_BIN2 NOT NULL,
  [age] [int] NOT NULL,
  INDEX [ixName] NONCLUSTERED HASH ([Name]) WITH ( BUCKET_COUNT = 131072),
  INDEX [ixNameAge] NONCLUSTERED ([Name] ASC, [age] ASC),
  PRIMARY KEY NONCLUSTERED HASH ([ID]) WITH ( BUCKET_COUNT = 131072)
)
WITH ( MEMORY_OPTIMIZED = ON , DURABILITY = SCHEMA_AND_DATA )

這個資料表，是透過 memory_optimized = ON的方式設定為記憶體資料表，而 durability的屬性設定為 schema_and_data則代表需要有交易記錄，若為 schema_only的話則代表資料變更內容不寫入交易記錄。

建立好的資料表，可用管理工具看到其屬性為 true：

這些屬性都是沒有辦法改的，只能唯讀。

存取的方式主要有兩種，一種是 T-SQL指令，而 T-SQL指令可以查詢到硬碟中的資料，也可以查詢記憶體中的資料表，若是撰寫預存程序，設定為「原生編譯」時，這時所取得的是記憶體中的資料表內容。

記憶體資料表的索引，可以設定為 B-Tree 類型的索引，也就是原本 SQL Server 所支援的索引，利用 B-TREE 的結構樹來找尋資料，或者是使用 hash 索引，利用雜湊演算法的方式來建立，只是建立的時候需要設定 BUCKET_COUNT 屬性，用大概研究過雜湊演算法的朋友應該知道，雜湊的複雜度是1，但前提建立在好的雜湊函數，如果雜湊函數會產生同個位置，則必須使用串列的方式將值放在後面，所以 BUCKET_COUNT 的值設定大，則浪費記憶體，線上說明是建議各位把值設定為預期該欄位的唯一值記錄筆數的1～2倍的數量。

下期再繼續介紹後續的內容。