SQL Server 内存中OLTP内部机制概述（一）

----------------------------我是分割线-------------------------------

本文翻译自微软白皮书《SQL Server In-Memory OLTP Internals Overview》：http://technet.microsoft.com/en-us/library/dn720242.aspx

译者水平有限，如有翻译不当之处，欢迎指正。

----------------------------我是分割线-------------------------------

SQL Server 内存中OLTP内部机制概述

摘要：

内存中OLTP（项目名为“Hekaton”）是一个新的完全集成到SQL Server中的数据库引擎组件。它专为访问内存常驻数据的OLTP工作负荷而进行优化。内存中OLTP有助于OLTP工作负荷实现显著的性能改进，并减少了处理时间。可以通过将表声明成“内存中优化”来启用内存中OLTP的功能。内存优化表完全支持事务，并且可以使用Transact-SQL进行访问。 Transact-SQL存储过程可以被编译成机器代码从而进一步提升内存优化表的性能。引擎针对高并发进行设计，并使阻塞最小化。

简介

在最初设计SQL Server的时候假定主内存是非常昂贵的，因此除非当数据确实需要进行处理，否则数据都驻留在磁盘上。由于内存价格在过去的30年中已经大幅下降，这种假设已不再有效。与此同时，多核服务器也已不再昂贵，所以如今人们只需花费不到5万美元就可以购买到拥有32个内核和1TB内存的服务器。由于生产环境的许多（尽管并不是绝大多数）OLTP数据库能够完全装进1TB的内存中，我们需要重新评估将数据存储在磁盘上的好处，以及当读取数据到内存中进行处理时所导致的I/O开销。此外，OLTP数据库在更新数据并且需要将数据写回到磁盘时，也会产生开销。内存优化表的存储方式与基于磁盘的表完全不同，并且这些新的数据结构有助于更加有效地访问和处理数据。

由于更多可用内存和更多内核的这种趋势，微软SQL Server团队开始构建一种针对大量主内存和多核CPU进行优化的数据库引擎。本文给出了这个新的数据库引擎功能：内存中OLTP的技术概述。

有关内存中OLTP的更多信息，请参阅在内存中 OLTP（内存中优化）。

设计注意事项与目的

开发一个真正的内存数据库的举动由三种基本的需求所驱动：1）将工作负荷所需的大部分或全部数据放到内存中，2）对于数据操作更低的延迟时间，3）针对特定类型的工作负荷的专业数据库引擎需要为那些工作负荷进行优化。摩尔定律已经影响了内存的成本，允许主内存足够大以满足需求（1）及部分满足需求（2）。（更大的内存降低了读取的延迟，但并没有影响传统数据库系统所需的写入到磁盘需要的延迟）。内存中OLTP的其他功能大大提高了数据修改操作的延迟。专为特定类型工作负荷设计的系统能够比通用系统表现优异10倍或者10倍以上，正是这样的认知驱动了专业数据库引擎的需求。最专业的系统，包括那些用于复杂事件处理（CEP, Complex Event Processing），DW/BI和OLTP的系统，都通过专注于内存中的结构来优化数据结构和算法。

微软之所以开发了内存中OLTP的功能，主要来源于这样一个事实即主内存大小正以极快的速度增长，并变得更加便宜。此外，由于64位架构和多核处理器的普及，有理由认为大多数（尽管并不是所有）OLTP数据库或者对性能敏感的整个工作数据集可以完全在内存中驻留。最大的金融，在线零售和航空订票系统中的许多系统的大小降低至500GB与5TB之间，工作集也显著变小。截至2012年，即使是一个双插槽服务器也可以通过采用32GB的DIMMs（Dual In-line Memory Module）来容纳2TB 的DRAM（Dynamic Random Access Memory）（比如IBM x3680 X5）。展望未来，在未来几年内，以不到5美元/GB的成本来构建拥有1-10 PB容量的基于DRAM的分布式系统，是完全有可能的。非易失性的RAM变为可行也只是一个时间问题。

如果一个应用程序的大多数或所有数据都能够完全驻留在内存中，那么SQL Server优化器从最初版本就开始使用的成本计算规则将变得几乎完全过时，因为规则假定所有访问的数据页都可能需要从磁盘进行物理读。如果不需要从磁盘进行读取，优化器则可以使用不同的成本计算算法。此外，如果没有磁盘读取所需的等待时间，其他等待的统计信息，比如等待锁被释放，等待闩锁可用或者等待日志写入完成，就会变得无比庞大。内存中OLTP解决了所有的这些问题。内存中OLTP消除了等待锁释放的问题，采用了一种新型的多版本乐观并发控制。内存中OLTP产生比原先少得多的日志数据，并且只需要更少的日志写入，从而减少了等待日志写入的延迟。

术语

SQL Server 2014的内存中OLTP功能涉及到一系列与使用内存优化表相关的技术。与内存优化表相对的表将被称为基于磁盘的表，这正是SQL Server一直所提供的。使用的术语包括：

内存优化表：是指采用了新的数据结构的表，这种数据结构作为内存中OLTP的一部分，将在本文中详细描述。
基于磁盘的表：与内存优化表相对，采用SQL Server此前一直使用的数据结构，以从磁盘读取和写入所需的8K大小的数据页作为一个单元。
本机编译的存储过程：是指内存中OLTP功能支持的一种对象类型，被编译为机器代码，并且比起只使用内存优化表，本机编译的存储过程有进一步增进性能的潜力。与之对应的是SQL Server此前一直使用的解释型的Transact-SQL存储过程。本机编译的存储过程只能引用内存优化表。
交叉容器事务：是指同时引用内存优化表和基于磁盘的表的事务。
互操作：是指引用内存优化表的解释型的Transact-SQL

功能概述

在使用内存中OLTP对数据进行大多数的处理操作时，你可能并不会察觉到正在使用的是内存优化表而不是基于磁盘的表。但是，如果数据存储在内存优化表中，SQL Server处理数据的方式非常不同。在本节中，我们将看到与SQL Server中基于磁盘的操作和数据不同的内存中OLTP运作原理和数据处理方式的概述。我们也将简单介绍来自于竞争对手的一些内存优化数据库的解决方案，并指出SQL Server的内存中OLTP与它们的区别。

内存中OLTP有何特殊之处

虽然内存中OLTP与SQL Server关系引擎集成，并且可以使用相同的接口透明地进行访问，但它的内部行为和功能有很大的不同。图1给出了包含内存中OLTP组件的SQL Server引擎的概述。

图1 包含内存中OLTP组件的SQL Server引擎

请注意，对于内存优化表或者基于磁盘的表，无论是将调用本地编译的存储过程或解释型的Transact-SQL，客户端应用程序连接到TDS处理程序都采用相同的方式。你可以看到，解释型的Transact-SQL可以使用互操作功能来访问内存优化表，但本地编译存储过程只能访问内存优化表。

内存优化表

内存优化表和基于磁盘的表之间最重要的区别是，当访问内存优化表时，数据页不需要从磁盘读入高速缓存。所有的数据都始终被存储在内存中。仅用于恢复的目的的检查点文件组（数据和差异文件对）创建在驻留在内存优化文件组中的文件中，记录了数据更改的跟踪，而检查点文件是只能被附加的。

在内存优化表上的操作与在基于磁盘的表的操作使用同样的事务日志，和往常一样，事务日志被存储在磁盘上。万一系统崩溃或者服务器关机，内存优化表中的数据行可以通过检查点文件和事务日志重建。

通过使用一个名为SCHEMA_ONLY的选项，内存中OLTP能够选择创建一个非持久的和不记录日志的表。如这个选项名所示，即便数据是非持久的，表架构也将是持久的。这些表在事务处理期间不需要任何IO操作，但是只有当SQL Server运行时，数据在内存中可用。一旦SQL Server关机或者AlwaysOn可用性组进行故障转移，这些表中的数据会丢失。当它们所属的数据库进行恢复时，表将会被重建，而表中没有数据。这些表可能会是有用的，例如，ETL场景里的临时表或者用于存储Web服务器会话状态的临时表。虽然数据是非持久的，但这些表上的操作符合事务其他所有的要求：原子性，隔离性和一致性。我们将会在创建表的章节看到创建一个非持久表的语法。

内存优化表上的索引

内存优化表上的索引并不按照传统的B树结构进行存储。内存优化表支持非聚集哈希索引，非聚集哈希索引以哈希表的方式存储，哈希表中拥有将相同哈希值的所有数据行与内存优化的非聚集索引连接起来的链接列表，而内存优化的非聚集索引使用的是特殊的BW树进行存储。非聚集哈希索引针对点查找进行优化，而内存优化非聚集索引则为检索值的范围和行排序提供支持，并优化了使用不等谓词的查询的性能。

每个内存优化表都必须至少有一个索引，因为正是索引将所有数据行组合成一张表。内存优化表永远不会像基于磁盘的堆表那样存储成无组织的数据行集合。

索引永远不会存储在磁盘上，在磁盘上的检查点文件中也不会体现，并且在索引中的操作永远不会被日志记录。与基于磁盘的表上的B树索引相同，在内存优化表上的所有修改操作发生时，索引是自动进行维护的，但一旦SQL Server重新启动，由于数据会加载到内存中，则内存优化表上的索引会被重建。

并发性的改进

当访问内存优化表时，SQL Server实现了一个乐观的多版本并发控制。尽管SQL Server以前通过在SQL Server 2005中引入的基于快照的隔离级别，从而号称支持乐观并发控制，但这些所谓的乐观方式在数据修改操作的过程中的确需要获取锁。对于内存优化表，不需要获取锁，从而没有因为阻塞而产生的等待。

注意，这并不意味着在使用内存优化表时，不可能产生等待。仍会存在其他等待类型，比如在事务结束时等待日志写入完成。不过，在对内存优化表进行更改时，内存优化表的日志记录比起基于磁盘的表更有效率的多，因此等待时间会更短。而且从磁盘读取数据永远不会有等待，也没有因为数据行上的锁而产生的等待。

本地编译的存储过程

当使用拥有内存优化表的本机编译的存储过程时，能获得最好的执行性能。不过，相对于解释型的代码可供使用的丰富的功能集，本地编译存储过程内部允许的Transact-SQL语言结构有一些限制。此外，本机编译存储过程只能访问内存优化表，并不能引用基于磁盘的表。

内存中OLTP仅仅只是DBCC PINTABLE的改进？

DBCC PINTABLE是旧版本的SQL Server提供的功能，一旦数据页从磁盘中读取，这张被“固定”的表里的任何数据页就不会从内存中移除。这些数据页需要被初始化读取，所以这样的表被访问总是会有第一次读取数据页的成本。这些固定的表与任何其他基于磁盘的表并无任何不同。它们需要相同数量的锁、闩锁和日志记录，也使用相同的索引结构，这些索引同样也需要锁和日志记录。内存中OLTP的内存优化表与SQL Server的基于磁盘的表则完全不同，它们使用不同的数据和索引结构，不使用锁，并且日志记录这些内存优化表的更改比起基于磁盘的表效率更高。

竞争对手的产品

对于处理OLTP数据，有两种类型的专业引擎。第一类是主内存数据库。 Oracle有TimesTen，IBM有solidDB，还有许多其它产品主要是针对嵌入式数据库空间。第二类是应用程序高速缓存或者键值存储（例如，Velocity–App Fabric Cache和GigaSpaces），利用应用程序和中间层的内存来降低数据库系统的工作负荷。这些缓存逐渐变得更为复杂，并拥有类似事务、范围索引和查询这样的数据库功能（例如GigaSpaces已经拥有了这些功能）。同时，数据库系统拥有缓存功能，比如高性能哈希索引和跨多服务器集群的扩展（比如VoltDB）。内存中OLTP引擎意在提供这两种类型引擎中各自的优点。可以认为内存中OLTP拥有缓存的性能和数据库的功能。它支持在内存中存储表和索引，这样你就可以将整个数据库建成一个完整的内存中的系统。它也提供了高性能索引和日志记录，以及其它特性以显著提高查询的执行性能。

SQL Server的内存中OLTP提供了极少竞争对手产品能够提供的以下功能：

内存优化表和基于磁盘的表之间的集成，迁移到内存驻留数据库可以逐步进行，只将最关键的表和存储过程创建成内存优化的对象。
本机编译的存储过程为基本的数据处理操作的执行时间提供了数量级的改进
内存优化的非聚集哈希索引和内存优化的非聚集索引都专门为主内存访问进行了优化
不在数据页上存储数据，不需要数据页闩锁。
对任何操作都没有锁或者闩锁的真正多版本乐观并发控制

SQL Server内存中OLTP与竞争对手产品设计最显著的区别是“互操作”的集成。在一个典型的高端OLTP工作负荷中，性能瓶颈主要集中在一些特定的区域，比如一小部分的表和存储过程。迫使将整个数据库驻留在内存中将是昂贵和低效的。但到目前为止，其他主要的竞争产品都采用这种方法。对于SQL Server，高性能和高竞争区域可以迁移到内存中OLTP，那么在这些内存优化表上的操作（存储过程）可以在本地进行编译从而达到最大的业务处理性能。

内存中OLTP改进的另一个关键是移除了内存优化表的数据页结构。这从根本上将数据操作算法从基于磁盘优化改变成基于内存和缓存优化。正如前面提到的，关于内存中OLTP的困惑之一是，它只像“DBCC PINTABLE”那样简单的将表锁定在缓冲池中。然而，即使数据页被强制驻留内存中时，许多竞争产品仍然采用数据页结构。例如SAP的HANA仍使用16KB大小的数据页来处理内存中数据行的存储，在高性能环境下，这从本质上仍需要忍受数据页闩锁争用的影响。

使用内存中OLTP

自从2013年6月发布的CTP版本以来，内存中OLTP引擎已经作为SQL Server 2014的一部分可供使用。内存中OLTP的安装是SQL Server安装应用程序的一部分。内存中OLTP组件只能在SQL Server 2014的64位版本中安装，在所有32位版本中都不可用。

创建数据库

包含内存优化表的任何数据库都必须有一个MEMORY_OPTIMIZED_DATA文件组。这个文件组用于存储SQL Server恢复内存优化表所需的检查点文件，虽然创建这个文件组的语法与创建一个普通的FILESTREAM文件组几乎是一样的，但还必须指定CONTAINS MEMORY_OPTIMIZED_DATA选项。下面是创建可支持内存优化表的数据库的一条CREATE DATABASE语句的示例：

CREATE DATABASE HKDB

    ON

    PRIMARY(NAME = [HKDB_data],

           FILENAME = 'Q:\data\HKDB_data.mdf', size=500MB),

    FILEGROUP [SampleDB_mod_fg] CONTAINS MEMORY_OPTIMIZED_DATA

           (NAME = [HKDB_mod_dir],

           FILENAME = 'R:\data\HKDB_mod_dir'),

           (NAME = [HKDB_mod_dir],

           FILENAME = 'S:\data\HKDB_mod_dir')

LOG ON (name = [SampleDB_log], Filename='L:\log\HKDB_log.ldf', size=500MB)

COLLATE Latin1_General_100_BIN2;

请注意，上面的示例代码在三个不同的驱动器（Q:，R:和S:)上创建了数据库文件，因此如果想运行这段代码，可能需要编辑路径名称来与系统相匹配。在S:和R:上的文件名是相同的，所以如果在相同的驱动器上创建所有文件，这两个文件需要使用不同的文件名。

还要注意的是指定的二进制排序规则。这时，内存优化表上的所有索引只能位于采用Windows（非SQL）BIN2排序规则的列上，并且本地编译存储过程只支持在这些相同排序规则上的比较，排序和分组。它可以为整个数据库指定（如上面的CREATE DATABASE语句所做的）使用默认的二进制排序规则，也可以在CREATE TABLE语句中为任何字符数据指定排序规则。（也可以为任何比较，排序或分组操作，在一个查询中指定排序规则。）

另外，也可以添加一个MEMORY_OPTIMIZED_DATA文件组到现有的数据库，然后添加文件到该文件组中。例如：

ALTER DATABASE AdventureWorks2012 ADD FILEGROUP hk_mod CONTAINS MEMORY_OPTIMIZED_DATA;

GO

ALTER DATABASE AdventureWorks2012 ADD FILE (NAME='hk_mod', FILENAME='c:\data\hk_mod')

TO FILEGROUP hk_mod;

GO

创建表

创建内存优化表的语法与创建基于磁盘的表的语法几乎相同，只有一些限制以及一些所需的扩展。通过使用MEMORY_OPTIMIZED= ON子句来将表指定为内存优化表。内存优化表只能拥有这些支持数据类型的列：

bit
所有整数类型: tinyint, smallint, int, bigint
所有货币类型: money, smallmoney
所有浮点类型: float, real
日期/时间类型: datetime, smalldatetime, datetime2, date, time
numeric 和decimal
所有非LOB字符类型: char(n), varchar(n), nchar(n), nvarchar(n), sysname
非LOB二进制类型: binary(n), varbinary(n)
Uniqueidentifier

需要注意的是不允许有LOB数据类型;不能有XML类型，CLR或max数据类型的列，并且所有行的长度被限制在8060字节，且没有行外数据。事实上，8060字节限制在表创建时就已强制执行，因此与基于磁盘的表不同，拥有两个VARCHAR(5000)列的内存优化表是不能被创建的。

内存优化表可以用两个DURABILITY值中的一个来进行定义，SCHEMA_AND_DATA或SCHEMA_ONLY，前者是默认值。采用DURABILITY = SCHEMA_ONLY定义的内存优化表，意味着表数据的修改不进行日志记录，并且表中的数据不保留在磁盘上。但是，架构会被持久化成数据库元数据的一部分，所以SQL Server重新启动数据库恢复后，空表将可供使用。

正如前面提到的，内存优化表必须至少拥有一个索引，这一要求可以通过自动创建支持主键约束的索引的方式来实现。除了那些采用SCHEMA_ONLY选项创建的表外，其他所有表都必须声明一个主键。至少必须声明一个索引来支持PRIMARY KEY约束。下面的示例演示了一个创建成哈希索引的PRIMARY KEY索引，必须为其指定存储桶的数量。在讨论哈希索引存储的细节时，将提到为存储桶选择一个值的几个准则。

单列索引可以与在CREATE TABLE语句中的列定义行被创建，如下图所示。BUCKET_COUNT属性将在哈希索引的章节进行讨论。

CREATE TABLE T1

(

[Name] varchar(32) not null PRIMARY KEY NONCLUSTERED HASH WITH (BUCKET_COUNT = 100000),

[City] varchar(32) null,

[State_Province] varchar(32) null,

[LastModified] datetime not null,

) WITH (MEMORY_OPTIMIZED = ON, DURABILITY = SCHEMA_AND_DATA);

或者，如下面的例子所示，在所有的列已被定义之后，可以创建复合索引。下面的示例将一个非聚集索引添加到表定义中。需要注意的是对于这两种类型的索引在定义中的区别在于，一个使用了关键字HASH，而另一个没有。这两种类型的索引都被指定为NONCLUSTERED，但Hash和存储桶数量表明了两个索引定义之间的不同之处。

CREATE TABLE T2

(

[Name] varchar(32) not null PRIMARY KEY NONCLUSTERED HASH WITH (BUCKET_COUNT = 100000),

[City] varchar(32) null,

[State_Province] varchar(32) null,

[LastModified] datetime not null,

INDEX T1_ndx_c2c3 NONCLUSTERED ([City],[State_Province])

) WITH (MEMORY_OPTIMIZED = ON, DURABILITY = SCHEMA_AND_DATA);