SQL2014发布后，最令我感兴趣的是列索引的改进和新增的内存优化表。前者让2012中鸡肋的新功能具备了真正用于实际环境的可能，后者则很可能是又一个激动人心的新鸡肋。
本文介绍内存优化表的简单基础、示例代码及初体验。  计划再用三篇文章介绍内存优化表的索引、架构和备份还原。
       总体上来说，内存优化表除了本身存在于SQL SERVER数据库中，混身上下的全部零件都与之前版本的磁盘表（即2014前的所有SQL SERVER表）没有半毛钱关系，完全是新概念新架构。以下几点能证明我的观点：
1.它不存放在数据页中
2.它的表物理结构和磁盘表不同
3.它的索引结构和使用方法跟磁盘表不同
4.它没有锁
5.它的事务处理方式与磁盘表不同
6.它比磁盘表快的多
7.它霸占内存

       首先我们从了解内存优化表（以下简称内存表）的结构开始。
       与传统的磁盘表不同，内存表的数据不保存在mdf文件中(其实我个人非常喜欢用.avi替代.mdf当后缀)，而是由一堆数据流文件和内存中的一群钉子户组成。当你创建数据库时，如果要在库中使用内存表，你就需要先为数据库指定一个目录存放内存表的数据流文件。当创建内存表时，你又需要为它指定占用的内存空间大小（存储桶数）。在满足了这两个条件后，数据库启动后会在内存中为所有内存表分配同一个固定的空间，并从数据文件中加载所有表数据到这个空间里。所以，内存表的读取与磁盘表的加载数据页读取根本就是两个概念，它没有加载数据页的概念，所有数据都在内存里，它甚至没有页的概念，同一个数据文件中可能存在N个表的数据。数据页头的标识只有用于查找数据更新时间的两个时间戳。
       可以说，内存表的主体实际是内存空间中的数据。磁盘上的数据流文件则可以认为是它的备份文件，由单独的线程定期更新，并在数据库启动或恢复时用于还原内存表。

       然后我们来看一下内存表的DML底层实现。先说磁盘表，SELECT是去数据页里读数据，INSERT是往数据页中增加一行数据，DELETE是从数据页中擦除，UPDATE是DELETE+INSERT。而内存表是没有数据页的概念的，那它是如何实现这些操作的呢？
       当你向内存表中INSERT时，会向内存空间中为这个表分配的存储桶中加入一行，并记录插入的时间戳。
       当你从内存表中DELETE时，会向内存空间中为这个表分配的存储桶中加入一行，并记录插入的时间戳。。。。不过不是上面那种行，而是一个专门记录删除操作的行。在某一个相同的时间戳段里，存在两个数据文件，一个记录这段时间内插入的行，一个记录这段时间内删除的行，查询时把这个表之前的所有文件读出来，第一个文件减掉第二个文件，就是真实的表数据了。
       当你对内存表UPDATE时，会做什么操作呢？其实还是DELETE+INSERT。它会先记录一个删除行，再记录一个插入行。由于新行的标识与原行相同，版本更新，UPDATE结束后读取这行就都是从新行读了。
       看起来是不是有点眼熟？没错，就是快照隔离级别，只不过由于内存空间很金贵，所以还有一个擦屁股线程不断的合并回收无效行当一个行被同标识的新行取代，且没有活动事务时，就会被回收合并，作为可用空间使用。
       由于内存表采用行版本的机制，因此读取和更新并不冲突，本来在内存中的读取就够快了，无非是读到的版本不够新，不会出现更新被大量读取阻塞的现象。又由于内存预留机制和数据流的IO写入方式，使磁盘IO几乎不会出现瓶颈，因此，内存表非常适合大量的并行操作（相对磁盘表，没有和NOSQL数据库叫板的意思哥）。事实上，由于空间的限制，也只适合这种业务场景。。。。

       说了一大堆，我们还是看一下实际的例子，理解起来更直观。
       首先你需要装上2014（废话），然后新建一个数据库，指定一个数据流文件存储目录

SQL code
?
1
2
3
4
5
6
7
8
9
10
11
USE [master]
GO
CREATE DATABASE [DB_TEST_MEMTB]
ON PRIMARY
( NAME = N 'DB_TEST_MEMTB_DATA' , FILENAME = N 'D:\DB_TEST_MEMTB_DATA.mdf' , SIZE = 512000KB , MAXSIZE = UNLIMITED, FILEGROWTH = 1024KB ),
--下面的文件就是数据流文件了
FILEGROUP [MEM_DIR] CONTAINS MEMORY_OPTIMIZED_DATA   DEFAULT
( NAME = N 'DB_TEST_MEMTB_DIR' , FILENAME = N 'D:\DB_TEST_MEMTB_DIR' , MAXSIZE = UNLIMITED)
LOG ON
( NAME = N 'DB_TEST_MEMTB_LOG' , FILENAME = N 'D:\DB_TEST_MEMTB_LOG.' , SIZE = 512000KB , MAXSIZE = 2048GB , FILEGROWTH = 1024KB)
GO

然后，我是用MS的示例代码建的表
SQL code
?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
USE DB_TEST_MEMTB
GO
IF EXISTS ( SELECT name FROM sysobjects WHERE name = 'xx' )
DROP PROCEDURE xx
GO
IF EXISTS ( SELECT name FROM sysobjects WHERE name = 'sql' )
DROP TABLE sql
GO
IF EXISTS ( SELECT name FROM sysobjects WHERE name = 'hash' )
DROP TABLE hash
GO
IF EXISTS ( SELECT name FROM sysobjects WHERE name = 'hash1' )
DROP TABLE hash1
GO
create table [sql]
(
c1 int not null primary key ,
c2 nchar (48) not null
)
go
create table [hash]
(
c1 int not null primary key nonclustered hash with (bucket_count=1000000),
c2 nchar (48) not null
) with (memory_optimized= on , durability = schema_and_data)
go
create table [hash1]
(
c1 int not null primary key nonclustered hash with (bucket_count=1000000),
c2 nchar (48) not null
) with (memory_optimized= on , durability = schema_and_data)
go
CREATE PROCEDURE xx
@rowcount int ,
@c nchar (48)
WITH NATIVE_COMPILATION, SCHEMABINDING, EXECUTE AS OWNER
AS
BEGIN ATOMIC
WITH ( TRANSACTION ISOLATION LEVEL = SNAPSHOT, LANGUAGE = N 'us_english' )
declare @i int = 1
while @i <= @rowcount
begin
INSERT INTO [dbo].[hash1] values (@i, @c)
set @i += 1
end
END
GO


建表语法就和以往不同，需要指定好多东西。关注这两句：
c1 int not null primary key nonclustered hash with (bucket_count=1000000)

with (memory_optimized=on, durability = schema_and_data)

memory_optimized=on指定表为内存优化表，durability = schema_and_data指定内存表同时存储于数据流文件中，如果是schema_only，就不存到磁盘中，可以理解成更快的、能建索引的全局表变量。

列里的hash关键字指定为c1列建立hash索引，关于内存表的索引在后面的文章中介绍。

跳过存储过程，我们直接看性能测试代码：
SQL code
?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
set statistics time off
set nocount on
-- inserts - 1 at a time
declare @starttime datetime2 = sysdatetime(),
@timems int
declare @i int = 1
declare @rowcount int = 100000
declare @c nchar (48) = N '12345678901234567890123456789012345678'
-----------------------------
--- disk-based table and interpreted Transact-SQL
-----------------------------
begin tran
while @i <= @rowcount
begin
insert into [sql] values (@i, @c)
set @i += 1
end
commit
set @timems = datediff(ms, @starttime, sysdatetime())
select 'Disk-based table and interpreted Transact-SQL: ' + cast (@timems as varchar (10)) + ' ms'
-----------------------------
--- Interop Hash
-----------------------------
set @i = 1
set @starttime = sysdatetime()
begin tran
while @i <= @rowcount
begin
insert into [hash] values (@i, @c)
set @i += 1
end
commit
set @timems = datediff(ms, @starttime, sysdatetime())
select ' memory-optimized table w/ hash index and interpreted Transact-SQL: ' + cast (@timems as varchar (10)) + ' ms'
-----------------------------
--- Compiled Hash
-----------------------------
set @starttime = sysdatetime()
exec xx @rowcount, @c
set @timems = datediff(ms, @starttime, sysdatetime())
select 'memory-optimized table w/hash index and native SP:' + cast (@timems as varchar (10)) + ' ms'


上面代码的功能简单来说就是往磁盘表中插入1W条数据、往内存表中插入1W条数据、往内存表中通过存储过程插入1W条数据。跳过第一次运行，在我机器上跑的结果是：
Disk-based table and interpreted Transact-SQL: 1022 ms
memory-optimized table w/ hash index and interpreted Transact-SQL: 784 ms
memory-optimized table w/hash index and native SP:248 ms

可以看到，在跳过了编译后，单条数据的操作性能几乎翻了5倍，提升非常大。