In-Memory：記憶體資料庫

標籤：gif   整合   ima   binding   最好   mit   執行   bin   copy   

SQL Server 2016的In-Memory OLTP，通俗地講，是記憶體資料庫，使用記憶體最佳化表（Memory-Optimized Table，簡稱MOT）來實現，MOT駐留在記憶體中，使用 Hekaton 記憶體資料庫引擎訪問。在查詢MOT時，只從記憶體中讀取資料行，不會產生Disk IO消耗；在更新MOT時，資料的更新直接寫入到記憶體中。記憶體最佳化表能夠在Disk上維護一個資料副本，該副本只用於持久化資料，不用於資料讀寫操作。

在記憶體資料庫中，不是所有的資料都需要儲存在記憶體中，有些資料仍然能夠儲存在Disk上，硬碟表（Disk-Based Table，簡稱DBT）是傳統的表格儲存體結構，每個Page是8KB，在查詢和更新DBT時，產生Disk IO操作，將資料從Disk讀取到記憶體，或者將資料更新非同步寫入到Disk中。

記憶體資料庫將原本儲存在Disk上的資料，儲存在記憶體中，利用記憶體的高速訪問優勢實現資料的快速查詢和更新，但是，記憶體資料庫，不僅僅是儲存空間的變化，Hekaton 記憶體資料庫訪問引擎實現本地編譯模組（Natively compiled），交叉事務（Cross-Container Transaction）和查詢互操作（Query Interop）：

• 本地編譯模組：如果代碼模組只訪問MOT，那麼可以將該模組定義為本地編譯模組，SQL Server直接將TSQL指令碼編譯成機器代碼；SQL Server 2016支援本地編譯的模式有：預存程序（SP），觸發器（Trigger），純量值函式（Scalar Function）或內嵌多語句函數（Inline Multi-Statement Function）。相比於解釋性（Interpreted）TSQL 模組，機器代碼直接使用記憶體位址，效能更高。
• 交叉事務：在解釋性TSQL模組中，一個事務既能訪問硬碟表，也能訪問記憶體最佳化表；實際上，SQL Server建立了兩個事務，一個事務用於訪問硬碟表，一個事務用於訪問記憶體最佳化表，在DMV中，分別使用transaction_id 和 xtp_transaction_id 來標識。
• 查詢互操作：解釋性TSQL指令碼能夠訪問記憶體最佳化表和硬碟表，本地編譯模組只能訪問記憶體最佳化表。

記憶體資料被整合到SQL Server關聯式引擎中，使用記憶體資料庫時，用戶端應用程式甚至感受不到任何變化，DAL介面也不需要做任何修改。由於Query Interop的存在，任何解釋性TSQL指令碼都能透明地訪問MOT，只是效能沒有本地編譯TSQL指令碼效能高。在使用分散式交易訪問MOT時，必須設定合適的交易隔離等級，推薦使用Read Committed，如果發生MSSQLSERVER_41333 錯誤，說明產生交叉事務隔離錯誤（CROSS_CONTAINER_ISOLATION_FAILURE），原因是當前事務的隔離等級太高。

一，建立記憶體資料庫

記憶體最佳化表的資料必須儲存在包含Memory_Optimized_Data的File Group中，該FileGroup可以有多個File，每個File實際上是Folder，一個DB只能建立一個包含Memory_Optimized_Data的File Group。

step1，建立一個資料庫，建立的Data File的數量最好和CPU核心數量保持一致，存放在不同的物理磁碟上；

 View Code

step2，為資料庫建立一個包含記憶體最佳化資料的FileGroup，向該FileGroup中添加“File”，實際上是目錄（Directory），用於儲存記憶體最佳化資料檔案，主要是CheckPoint檔案，用於還原持久化的記憶體最佳化表。

-- Add File Group from memory-optimized dataalter database [Test_MemboryDB]add filegroup fg_MemoryOptimizedDatacontains MEMORY_OPTIMIZED_DATA;alter database [Test_MemboryDB]add file (name=Test_MemboryDBDirectory,filename=‘D:\Program Files\Microsoft SQL Server\Test_MemboryDBDirectory‘)to FILEGROUP fg_MemoryOptimizedData;

檔案組屬性：CONTAINS MEMORY_OPTIMIZED_DATA 子句，指定File Group用於儲存記憶體最佳化表資料，每個資料庫只能指定一個儲存記憶體最佳化資料的File Group，可以在該File Group下建立多個Directory，分布在不同的物理Disk上，加快記憶體最佳化表資料還原的速度。

二，建立記憶體最佳化表

記憶體最佳化表用於儲存使用者資料，可以持久化儲存，資料存放區在記憶體中，同時，在Disk上維護資料的一個副本，通過選項 DURABILITY= SCHEMA_AND_DATA 指定持久化儲存記憶體最佳化表；也可以只儲存在記憶體中，通過選項DURABILITY= SCHEMA_ONLY指定。在記憶體最佳化表上，可以建立nonclustered index 或nonclustered hash index，每個記憶體最佳化表中至少建立一個Index。

--create memory optimized tablecreate table [dbo].[products](    [ProductID] [bigint] not null,    [Name] [varchar](64) not null,    [Price] decimal(10,2) not null,    [Unit] varchar(16) not null,    [Description] [varchar](max) null,    constraint [PK__Products_ProductID] primary key nonclustered hash ([ProductID])with (bucket_count=2000000)    ,index idx_Products_Price  nonclustered([Price] desc)    ,index idx_Products_Unit nonclustered hash(Unit) with(bucket_count=40000))with(memory_optimized=on,durability= schema_and_data)go

1，記憶體最佳化：MEMORY_OPTIMIZED

[MEMORY_OPTIMIZED = {ON | OFF}]

預設值是OFF，指定建立的表是硬碟表；設定選項MEMORY_OPTIMIZED為ON，指定建立的表是記憶體最佳化表；

2，持久性：Durability 

DURABILITY = {SCHEMA_ONLY | SCHEMA_AND_DATA}

預設值是SCHEMA_AND_DATA，指定建立的記憶體最佳化表是持久化的，這意味著，資料更新會持久化儲存到Disk上，在SQL Server重啟之後，記憶體最佳化表的資料能跟根據儲存在Disk上的副本還原。選項 SCHEMA_ONLY 指定建立的記憶體最佳化表是非持久化的，這意味著Table Schema是持久化儲存到Disk上，但是，任何資料更新都不會持久化到Disk上，在SQL Server重啟之後，記憶體最佳化表的資料會丟失。

3，雜湊索引和範圍索引

記憶體最佳化表支援Hash Index，屬性 BUCKET_COUNT 指定為Hash Index建立的bucket的數量，一般hash bucket的數量是資料行的1-2倍，如果無法估計bucket的數量，請建立範圍索引（NonClustered Index），索引結構是Bw-Tree。

Hash 索引由一個數組和多個資料行鏈組成，每一個數組元素叫做一個Hash Bucket，通過內建的Hash函數，將Hash索引的Key映射到Hash Bucket上，例如，如果Hash Index的Key是(Col1,Col2)，根據HashFunction(Col1,Col2)返回的Hash Value，將資料行映射到指定的Hash Bucket上；如果多個Key映射到同一個Hash Bucket上，那麼這些Key組成一個鏈。例如：資料表結構是(Name,City)，在Name欄位上建立Hash Index，Hash值相同的資料行連結成一個單向鏈。

三，建立Natively Compiled SP

本地編譯SP在建立時編譯成機器代碼，整個SP以原子方式執行，這意味著，以SP為單位，整個SP中的所有操作是一個原子操作，要麼執行成功，要麼執行失敗。

create procedure dbo.usp_GetProduct    @ProductID bigint not nullwith native_compilation, schemabinding, execute as ownerasbegin atomic with (transaction isolation level = snapshot, language = N‘US_English‘)  select  [ProductID]      ,[Name]      ,[Price]      ,[Unit]      ,[Description]from [dbo].[Products]where [email protected]endgo 

1，在本地編譯SP中，能夠為參數，變數指定Nullability屬性，預設值是NULL

NOT NULL 屬性：不能為參數或變數指定NULL值，

• 在本便編譯SP中，為參數指定NOT NULL屬性，不能為參數指定NULL值；
• 在本便編譯SP中，為變數定義NOT NULL屬性，必須在Declare時初始設定變數；

2，本地編譯SP必須包含兩個選項：SCHEMABINDING 和 ATOMIC Block

• SCHEMABINDING：綁定引用的記憶體最佳化表
• ATOMIC Block：在原子塊中的所有語句，以單個事務運行；在事務成功時，所有語句都提交成功；在事務失敗時，所有語句都復原。Atomic Bloc保證原子地執行SP，如果SP在其他事務的上下文中被調用，那麼該SP開始一個新的事務。
  • Atomic blocks guarantee atomic execution of the stored procedure. If the procedure is invoked outside the context of an active transaction, it will start a new transaction, which commits at the end of the atomic block.

使用Atomic Block必須設定兩個選項：

• TRANSACTION ISOLATION LEVEL：指定Atomic Block開啟事務的隔離等級，通常指定Snapshot隔離等級；
• LANGUAGE：指定SP內容相關的語言；

3，解釋型SP和本地編譯SP的區別

解釋性SP能夠訪問硬碟表（Disk-Based Table）和記憶體最佳化表（Memory-Optimized Table），其真正的區別是解釋性（Interpreted）SP在第一次執行時編譯，而本地編譯（Natively Compiled）SP是在建立時編譯，並且直接編譯成機器代碼，綁定的是記憶體位址。

4，延遲持久化

在本地編譯SP中，設定Atoic Block的選項：DELAYED_DURABILITY = ON ，使SP對記憶體最佳化表的更新操作，以非同步寫交易記錄方式，延遲持久化到Disk，這意味著，如果記憶體最佳化表維護了一個Disk-Based 的副本，資料在記憶體中修改之後，不會立即更新到Disk-Based 的副本中，這有遺失資料的可能性，但是能夠減少Disk IO，提高資料更新的效能。

四，使用記憶體最佳化的表變數和暫存資料表

傳統的表變數和暫存資料表，都使用tempdb儲存臨時資料，而tempdb不是記憶體資料庫，使用Disk儲存暫存資料表和表變數的資料，會產生Disk IO和競爭，SQL Server提供了記憶體最佳化的表變數，將臨時資料存放區在記憶體中，詳細資料，請參考我的部落格：《In-Memory：在記憶體中建立暫存資料表和表變數》。

五，在記憶體資料庫中使用JSON

自從使用JSON之後，我的第一感概是：資料庫豈能沒有JSON，不管是資料庫將值傳遞前端，還是前端將資料傳遞到資料庫，使用JSON方便很多，相比XML，JSON的使用簡單很多，詳細資料，請參考我的部落格：《使用TSQL查詢和更新 JSON 資料》

六，記憶體資料庫的交易處理

交叉事務是指在一個事務中，解釋性TSQL語句同時訪問記憶體最佳化表（Memory-Optimized Table，簡稱MOT）和硬碟表（Disk-Based Table，簡稱DBT）。在交叉事務中，訪問MOT的操作和訪問DBT的操作都擁有自己獨立的事務序號，就像在一個大的交叉事務下，存在兩個單獨的子事務，分別用於訪問MOT和DBT；

In-Memory：記憶體資料庫