为什么现在我最终推荐内存OLTP

在今年的8月份，我写了篇文章，介绍了我还不推荐用户使用内存OLTP的各个理由。近日很多人告诉我，他们有一些性能的问题，并考虑使用内存OLTP来解决它们。

众所皆知，在SQL Server里内存OLTP是个非常特别的技术，在很多情况下并不适用，但这是在SQL Server 2014里的首次实现，它有很多限制，我在这篇文章里已经介绍。

感谢上帝——现在事情已经改变了！几个星期前，SQL Server 2016的CTP 3版本已经可以公开下载了。在内存OLTP领域，微软做出了巨大的改进。我们来详细看下。

首先我们创建测试的数据库：

USE master
GO

-- Create new database
CREATE DATABASE InMemoryOLTP
GO

--Add MEMORY_OPTIMIZED_DATA filegroup to the database.
ALTER DATABASE InMemoryOLTP
ADD FILEGROUP InMemoryOLTPFileGroup CONTAINS MEMORY_OPTIMIZED_DATA
GO

USE InMemoryOLTP
GO

-- Add a new file to the previous created file group
ALTER DATABASE InMemoryOLTP ADD FILE
(
    NAME = N'InMemoryOLTPContainer', 
    FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL13.MSSQLSERVER\MSSQL\DATA\InMemoryOLTPContainer'
)
TO FILEGROUP [InMemoryOLTPFileGroup]
GO

 外键约束(Foreign-Key Constraints)

一个最重要的改变，或者我应该说是提升——是支持外键约束！是的，你没有听错：内存OLTP现在支持外键约束。在最初的实现里通常你会期望是支持外键约束，因为这是涉及OLTP情景，但在SQL Server 2014上微软并不支持。我们来看下面的代码：

-- Create a parent table
CREATE TABLE Parent
(
    ParentID INT IDENTITY(1, 1) NOT NULL,
    Col1 CHAR(100) NOT NULL,
    Col2 CHAR(100) NOT NULL,
    Col3 CHAR(100) NOT NULL,
    CONSTRAINT chk_PrimaryKey_Parent PRIMARY KEY NONCLUSTERED HASH (ParentID) WITH (BUCKET_COUNT = 1024)
)
WITH (MEMORY_OPTIMIZED = ON)
GO

-- Create a child table
CREATE TABLE Child
(
    ChildID INT IDENTITY(1, 1) NOT NULL,
    ParentID INT NOT NULL,
    Col1 CHAR(100) NOT NULL,
    Col2 CHAR(100) NOT NULL,
    Col3 CHAR(100) NOT NULL,

    -- Create a FK constraint between both tables
    CONSTRAINT FK_Child_Parent FOREIGN KEY (ParentID) 
    REFERENCES Parent (ParentID),
    
    CONSTRAINT chk_PrimaryKey_Child PRIMARY KEY NONCLUSTERED HASH (ChildID) WITH (BUCKET_COUNT = 1024)
)
WITH (MEMORY_OPTIMIZED = ON)
GO

-- Insert some records into both tables
INSERT INTO Parent VALUES ('a', 'a', 'a'), ('b', 'b', 'b'), ('c', 'c', 'c')
INSERT INTO Child VALUES (1, 'a', 'a', 'a'), (1, 'b', 'b', 'b'), (1, 'c', 'c', 'c')
GO

这段代码在2个表（parent和child表）之间创建了一个简单的外键约束。另外我也在2个表里也插入了些测试数据。现在我们对这2个表进行简单的查询：

-- The unnecessary join is removed in the execution plan.
SELECT c.* FROM Parent p
JOIN Child c ON c.ParentID = p.ParentID
GO

当你看查询本身时，你会看见我只想返回child表的内容。基于外键约束，查询优化器知道在parent表里肯定有记录存在。因此查询优化器通过移除不需要的表连接来简化查询。当你看执行计划时，你会看到这个简化真的发生了——非常棒：

在SQL Server的内存OLTP里，这是其中一个最大的改进——支持外键约束。对于外键约束的支持同样支持本地编译的存储过程，如下代码所示：

-- Create a natively compiled Stored Procedure
CREATE PROCEDURE InMemoryOLTPProcedure
WITH NATIVE_COMPILATION, SCHEMABINDING, EXECUTE AS OWNER
AS
BEGIN
   ATOMIC WITH
   (
      TRANSACTION ISOLATION LEVEL = SNAPSHOT, LANGUAGE = 'us_english'
   )

   SELECT c.ChildID, c.ParentID, c.Col1, c.Col2, c.Col3 FROM dbo.Parent p
   JOIN dbo.Child c ON c.ParentID = p.ParentID
END
GO

当你执行本地编译存储过程时，同样你没有可用的实际执行计划。你能查看的只有估计执行计划……

检查约束（Check Constraints）

另外非常棒的提升是现在我们支持检查约束。检查约束非常重要，因为它告诉查询优化器你数据长相的更多信息。基于这些信息，查询优化器可以给你更好性能的执行计划。下面这段代码给你展示了一个使用内存OLTP如何定义检查约束的简单例子。

-- You can't create a CHECK constraint on a Memory Optimized Table
CREATE TABLE CheckConstraint
(
    ID INT IDENTITY(1, 1) PRIMARY KEY NONCLUSTERED HASH WITH (BUCKET_COUNT = 1024),
    Value INT NOT NULL DEFAULT 1 CONSTRAINT ck_Value CHECK (Value = 1)
)
WITH
(
    MEMORY_OPTIMIZED = ON, 
    DURABILITY = SCHEMA_AND_DATA
)
GO

从这个表定义你可以看到，这里我创建了一个简单的检查约束，告诉SQL Server在列值里只保存1的值。甚至否定检查在与约束检查组合也是支持的。下面这个查询会导致在执行计划里有常数扫描运算符。

-- Contradiction detection works with In-Memory OLTP.
SELECT * FROM CheckConstraint
WHERE Value = 0
GO

在字符列上的索引

回到使用SQL Server 2014的旧时光里，内存OLTP里不支持字符列上的索引，因为你必须使用BIN2排序。对于大多数人来说这是个项目障碍，因为当你在字符列上进行比较或排序时，使用另一个排序会影响结果。

使用SQL Server 2016，微软现在已经最终移除了这个限制，现在你可以在字符列上直接创建哈希或范围索引，不需要使用BIN2排序。我们来看下面的例子，在SQL Server 2016里现在是正常运行的。

-- Creates a table with an index on a character column.
-- This works now without any problems in SQL Server 2016.
CREATE TABLE TestTable1
(
    Col1 CHAR(10) NOT NULL PRIMARY KEY NONCLUSTERED HASH WITH (BUCKET_COUNT = 1024),
    Col2 VARCHAR(100) NOT NULL,
    Col3 VARCHAR(100) NOT NULL
) WITH
(
    MEMORY_OPTIMIZED = ON, 
    DURABILITY = SCHEMA_AND_DATA
)
GO

 非常炫——太棒了！

架构和桶数改变

在SQL Server 2014里这个真的是很糟也很烂：在内存优化表上架构修改是不完全不支持的。你需要删掉并重建你的表，即使你只想增加一个索引或修改现存的索引。抱歉，这是我绝不推荐用户使用内存OLTP的一个最主要原因。即使对于哈希索引修改桶数，你也要删除并重建你的表。

使用SQL Server 2016，生活现在好多了，一切变得简单了。首先你可以使用简单的ALTER INDEX REBUILD语句来修改现存索引的桶数。要留意的是你需要有表大小的2倍内存。下列代码显示这个提升：

-- We can change now the bucket count without dropping the table
ALTER TABLE Parent
ALTER INDEX chk_PrimaryKey_Parent
REBUILD WITH (BUCKET_COUNT = 1048576)
GO

 另外在你创建内存优化表后，现在你可以修改你的表，甚至创建索引。我还没尝试所有的可能修改，但下面的代码可以给你一个你期望SQL Server 2016的一个大致想法。

-- Creates a table with an index on a character column.
-- This works now without any problems in SQL Server 2016.
CREATE TABLE TestTable1
(
    Col1 CHAR(10) NOT NULL PRIMARY KEY NONCLUSTERED HASH WITH (BUCKET_COUNT = 1024),
    Col2 VARCHAR(100) NOT NULL,
    Col3 VARCHAR(100) NOT NULL
) WITH
(
    MEMORY_OPTIMIZED = ON, 
    DURABILITY = SCHEMA_AND_DATA
)
GO

-- Create a new system-versioned table
CREATE TABLE Persons
(
    ID INT IDENTITY(1, 1) PRIMARY KEY NONCLUSTERED HASH WITH (BUCKET_COUNT = 1024),
    FirstName VARCHAR(100) NOT NULL,
    LastName VARCHAR(100) NOT NULL,
    City VARCHAR(100) NOT NULL,

    -- Needed for System-Versioned Tables
    StartDate DATETIME2 GENERATED ALWAYS AS ROW START NOT NULL,
    EndDate DATETIME2 GENERATED ALWAYS AS ROW END NOT NULL,
    PERIOD FOR SYSTEM_TIME (StartDate, EndDate)
)
WITH
(
    -- Needed for System-Versioned Tables
    SYSTEM_VERSIONING = ON
    (
        -- Name of the history table (optional)
        HISTORY_TABLE = dbo.PersonHistory
    ),
    MEMORY_OPTIMIZED = ON, 
    DURABILITY = SCHEMA_AND_DATA
)
GO

-- The current table is a Memory-Optimized Table, the history table
-- is a traditional Disk-Based Table.
SELECT is_memory_optimized, * FROM sys.tables
WHERE object_id IN(OBJECT_ID('Persons'), OBJECT_ID('PersonHistory'))
GO

-- Create a parent table
CREATE TABLE LetsTrySchemaChanges
(
    ID INT IDENTITY(1, 1) NOT NULL PRIMARY KEY NONCLUSTERED HASH WITH (BUCKET_COUNT = 1024),
    Col1 CHAR(100) NOT NULL,
    Col2 CHAR(100) NOT NULL,
    Col3 CHAR(100) NOT NULL
)
WITH (MEMORY_OPTIMIZED = ON)
GO

-- Schema Changes are now also supported on Memory-Optimized tables.
ALTER TABLE LetsTrySchemaChanges ADD Test CHAR(100) NULL
GO

-- The creation of an index after the table creation is not supported
CREATE NONCLUSTERED HASH INDEX idx_Test ON LetsTrySchemaChanges(Col3)
WITH (BUCKET_COUNT = 1024)
GO
    

小结

从这个文章里，你可以看到在SQL Server 2016里，内存OLTP已经彻底翻新了。现在如果有人问我它们是否应该使用内存中OLTP，我会说是的——如要你有对应的问题，而且这些问题使用SQL Server的传统关系引擎不能解决的。

除了我在这篇文章里提到的提升外，SQL Server 2016里的内存OLTP可以给你的其他的提升，我会在接下来的文章里谈到。

对这些提升你有啥想法？请尽情留言。

参考文章：

http://www.sqlpassion.at/archive/2015/11/16/why-i-now-eventually-recommend-in-memory-oltp/