SQL2014学习笔记1--初识内存优化表

[guguda2008]

在开始之前，先吐槽一下2012和2014的快捷键设置，几乎把我习惯的快捷键改了个遍，害我要一一设回来。。。
------------------------------------------------------------正文开始------------------------------------------------------------------------------
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（废话），然后新建一个数据库，指定一个数据流文件存储目录

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的示例代码建的表

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索引，关于内存表的索引在后面的文章中介绍。

跳过存储过程，我们直接看性能测试代码：

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倍，提升非常大。

对于内存优化表的介绍就是这样了，感觉如何？我个人感觉在某些特定的业务场景中，内存表还是有很大发挥空间的，当然现在来说它的限制还是太多，对于传统业务的升级支持也很差，但多了一项代表数据库趋势的新功能，还是让我对SQL SERVER产品的未来很看好的，希望它将来能发展成SQL SERVER的主力技能之一。

[smthgdin_020]

这系列都mark一下。

[信仰诠释过往]

学习了。。。

[吉普赛的歌]

declare @t datetime2
set @t=sysdatetime()
select * from sql
print 'sql: ' + convert(varchar(20) , datediff(ms, @t, sysdatetime()))

set @t=sysdatetime()
select * from hash
print  'hash: '  + convert(varchar(20) , datediff(ms, @t, sysdatetime()))

set @t=sysdatetime()
select * from hash1
print 'hash1: '  + convert(varchar(20) ,datediff(ms, @t, sysdatetime()))

--下面是三次测试的结果
--sql: 1434
--hash: 1434
--hash1: 1377

--sql: 1366
--hash: 1443
--hash1: 1405

--sql: 1384
--hash: 1479
--hash1: 1445

为什么查询就得不到改善呢？

[吉普赛的歌]

引用 57 楼 guguda2008 的回复:

数据量不够，差别不明显

你的 Demo ，　我后来把数据量加到了一百万行，　还是没有太大变化 ( 即使用上本地编译的存储过程做查询 )。 真不知道用什么办法可以提高查询速度，　鸭兄能否做一个可以查询方面的对比测试供我们参考？

[hyde100]

学习，这版本更新的也太快了

[guguda2008]

引用 56 楼 yenange 的回复:

declare @t datetime2
set @t=sysdatetime()
select * from sql
print 'sql: ' + convert(varchar(20) , datediff(ms, @t, sysdatetime()))

set @t=sysdatetime()
select * from hash
print  'hash: '  + convert(varchar(20) , datediff(ms, @t, sysdatetime()))

set @t=sysdatetime()
select * from hash1
print 'hash1: '  + convert(varchar(20) ,datediff(ms, @t, sysdatetime()))

--下面是三次测试的结果
--sql: 1434
--hash: 1434
--hash1: 1377

--sql: 1366
--hash: 1443
--hash1: 1405

--sql: 1384
--hash: 1479
--hash1: 1445

为什么查询就得不到改善呢？

数据量不够，差别不明显

[Steven-Xu]

好不容易换到2008，你们就2014了，看样纸。。。

[山寨DBA]

最近也在研究内存优化表，楼主：bucket_count=1000000 这个是什么意思呀？

[guguda2008]

引用 58 楼 yenange 的回复:

[quote=引用 57 楼 guguda2008 的回复:] 数据量不够，差别不明显

你的 Demo ，　我后来把数据量加到了一百万行，　还是没有太大变化 ( 即使用上本地编译的存储过程做查询 )。 真不知道用什么办法可以提高查询速度，　鸭兄能否做一个可以查询方面的对比测试供我们参考？[/quote] 最近在忙工作的事，自己的测试环境也不如意，后面是打算做一个性能上的测试的。 话说内存表其实对单次查询的效率提升不大，但对很大量的零星查询性能提升较大，典型的场景是一个对某表每分钟几百次查询以上的系统，你如果用单实例的话很难模拟这种场景。

[guguda2008]

引用 61 楼 hwhmh2010 的回复:

最近也在研究内存优化表，楼主：bucket_count=1000000 这个是什么意思呀？

可以理解为给内存表分配空间的大小

[吉普赛的歌]

引用 63 楼 guguda2008 的回复:

[quote=引用 58 楼 yenange 的回复:] [quote=引用 57 楼 guguda2008 的回复:] 数据量不够，差别不明显

你的 Demo ，　我后来把数据量加到了一百万行，　还是没有太大变化 ( 即使用上本地编译的存储过程做查询 )。 真不知道用什么办法可以提高查询速度，　鸭兄能否做一个可以查询方面的对比测试供我们参考？[/quote] 最近在忙工作的事，自己的测试环境也不如意，后面是打算做一个性能上的测试的。 话说内存表其实对单次查询的效率提升不大，但对很大量的零星查询性能提升较大，典型的场景是一个对某表每分钟几百次查询以上的系统，你如果用单实例的话很难模拟这种场景。[/quote] 你的意思是要用SQL Query Stress 之类的多线程测试工具来测？

[guguda2008]

引用 65 楼 yenange 的回复:

[quote=引用 63 楼 guguda2008 的回复:] [quote=引用 58 楼 yenange 的回复:] [quote=引用 57 楼 guguda2008 的回复:] 数据量不够，差别不明显

你的 Demo ，　我后来把数据量加到了一百万行，　还是没有太大变化 ( 即使用上本地编译的存储过程做查询 )。 真不知道用什么办法可以提高查询速度，　鸭兄能否做一个可以查询方面的对比测试供我们参考？[/quote] 最近在忙工作的事，自己的测试环境也不如意，后面是打算做一个性能上的测试的。 话说内存表其实对单次查询的效率提升不大，但对很大量的零星查询性能提升较大，典型的场景是一个对某表每分钟几百次查询以上的系统，你如果用单实例的话很难模拟这种场景。[/quote] 你的意思是要用SQL Query Stress 之类的多线程测试工具来测？[/quote] 差不多吧，最好的环境是你们产品的测试环境，然后模拟多线程访问，当负载大幅上长时内内存表的表现会比正常表优秀。 另外我也说了，2014在这方面只是个探路者，如果这版本的功能不尽如人意，没关系，先学好这方面的应用知识，等下版本用同样的方法测试，如果觉得OK了就可以试着用到实际环境中了。

[释怀355_H]

感谢楼主分享

[BlueStorm]

我的测试结果是: Disk-based table and interpreted Transact-SQL: 657 ms memory-optimized table w/ hash index and interpreted Transact-SQL: 441 ms memory-optimized table w/hash index and native SP:79 ms 性能是原来的8.32倍，也就是性能提升了732%，相当可观！

[guguda2008]

引用 68 楼 BlueStorm 的回复:

我的测试结果是: Disk-based table and interpreted Transact-SQL: 657 ms memory-optimized table w/ hash index and interpreted Transact-SQL: 441 ms memory-optimized table w/hash index and native SP:79 ms 性能是原来的8.32倍，也就是性能提升了732%，相当可观！

这版学习一下使用方法即可，实际生产环境里的性能提升需要再进行测试的。

[王蒙]

nibi

[人潮拥挤我踩死你]

感谢LZ分享，非常不错的体验，虽然新技能不够好，但期待更好的出现~~

[leiming7792]

学习了

[Pxl_____]

14的数据库分离出来 版本低可以兼容么

[楠小颜]

最近要用这个，一头雾水