試試SQLSERVER2014的內(nèi)存優(yōu)化表

2024-07-21 02:50:17

字體：大中小

來(lái)源：轉(zhuǎn)載

供稿：網(wǎng)友

試試SQLSERVER2014的內(nèi)存優(yōu)化表試試SQLSERVER2014的內(nèi)存優(yōu)化表

SQL Server 2014中的內(nèi)存引擎(代號(hào)為Hekaton)將OLTP提升到了新的高度。

現(xiàn)在，存儲(chǔ)引擎已整合進(jìn)當(dāng)前的數(shù)據(jù)庫(kù)管理系統(tǒng)，而使用先進(jìn)內(nèi)存技術(shù)來(lái)支持大規(guī)模OLTP工作負(fù)載。

就算如此，要利用此新功能，數(shù)據(jù)庫(kù)必須包含“內(nèi)存優(yōu)化”文件組和表

即所配置的文件組和表使用Hekaton技術(shù)。

幸運(yùn)的是，SQL Server 2014使這一過(guò)程變得非常簡(jiǎn)單直接。

要說(shuō)明其工作原理，我們來(lái)創(chuàng)建一個(gè)名為TestHekaton的數(shù)據(jù)庫(kù)，然后添加一個(gè)內(nèi)存優(yōu)化文件組到此數(shù)據(jù)庫(kù)

測(cè)試環(huán)境：Microsoft Azure 大陸版虛擬機(jī)

4核，7G內(nèi)存，Windows2012R2

SQLSERVER2014企業(yè)版

實(shí)驗(yàn)

第一個(gè)實(shí)驗(yàn)：內(nèi)存表的簡(jiǎn)單使用

步驟1：創(chuàng)建數(shù)據(jù)庫(kù)和MEMORY_OPTIMIZED_DATA文件組

USE master;GOCREATE DATABASE TestHekaton;GOALTER DATABASE TestHekatonADD FILEGROUP HekatonFG CONTAINS MEMORY_OPTIMIZED_DATA;GO

注意ALTER DATABASE語(yǔ)句中的ADD FILEGROUP 語(yǔ)句包含文件組的名稱(HekatonFG)和關(guān)鍵字CONTAINS MEMORY_OPTIMIZED_DATA

它會(huì)指導(dǎo)SQL Server去創(chuàng)建支持內(nèi)存OLTP引擎所必需的文件組類型。

注意：每個(gè)數(shù)據(jù)庫(kù)只能有一個(gè)MEMORY_OPTIMIZED_DATA文件組！！

要確認(rèn)此文件組已經(jīng)創(chuàng)建，可以訪問(wèn)SSMS中數(shù)據(jù)庫(kù)屬性的Filegroups 界面，如下圖所示。

步驟2：

添加一個(gè)數(shù)據(jù)文件到文件組，可以通過(guò)ALTER DATABASE語(yǔ)句來(lái)實(shí)現(xiàn)。

添加一個(gè)新數(shù)據(jù)文件到HekatonFG文件組：

ALTER DATABASE TestHekatonADD FILE(   NAME = 'HekatonFile',   FILENAME ='C:/PRogram Files/Microsoft SQL Server/MSSQL12.MSSQLSERVER/MSSQL/DATA/HekatonFile')TO FILEGROUP [HekatonFG];GO

注意：在ADD FILE 語(yǔ)句中，我們只為文件路徑和文件名提供了一個(gè)友好的名稱。

并且，在TO FILEGROUP 語(yǔ)句中，為新文件組指定名稱。

然后可以去往數(shù)據(jù)庫(kù)屬性的 Files 界面來(lái)查看剛剛添加的文件，如圖所示。

步驟3：

在為數(shù)據(jù)庫(kù)設(shè)置了必需的文件組和文件之后，就可以創(chuàng)建自己的內(nèi)存優(yōu)化表了。

當(dāng)在定義表的時(shí)候，會(huì)指定其“持久性”。

一個(gè)內(nèi)存優(yōu)化表可以是持久的或非持久的。

（1）對(duì)于一個(gè)持久表是將數(shù)據(jù)存儲(chǔ)在內(nèi)存中，而且也保存在內(nèi)存優(yōu)化文件組中。

（2）對(duì)于一個(gè)非持久表，數(shù)據(jù)是僅存儲(chǔ)在內(nèi)存中的，所以，如果系統(tǒng)崩潰或重啟，數(shù)據(jù)就會(huì)丟失。

在SQL Server 2014中默認(rèn)用的是持久表，接下來(lái)我們來(lái)深入了解一下。

當(dāng)定義一個(gè)持久內(nèi)存優(yōu)化表的時(shí)候，你還必須定義一個(gè)基于非聚集哈希索引的主鍵。

在一個(gè)哈希索引中，數(shù)據(jù)是通過(guò)一個(gè)內(nèi)存散列表進(jìn)行訪問(wèn)的，而非固定大小頁(yè)。

哈希索引是在內(nèi)存優(yōu)化表中唯一支持的索引類型。

除了在表定義中定義主鍵外，還必須將表配置為內(nèi)存優(yōu)化的，如下CREATE TABLE 語(yǔ)句所示：

USE TestHekaton;GOCREATE TABLE Reseller    (      [ResellerID] INT NOT NULL                       PRIMARY KEY NONCLUSTERED HASH WITH (BUCKET_COUNT = 1024),      [ResellerName] NVARCHAR(50) NOT NULL ,      [ResellerType] NVARCHAR(20) NOT NULL    )WITH (MEMORY_OPTIMIZED = ON, DURABILITY = SCHEMA_AND_DATA);INSERT  INTO ResellerVALUES  ( 1, 'A Bike Store', 'Value Added Reseller' );

ResellerID 字段定義包含了定義為非聚集哈希的主鍵。

注意，必須包含一個(gè)WITH 語(yǔ)句來(lái)指定BUCKET_COUNT 的設(shè)置，它表明了在哈希索引中應(yīng)該創(chuàng)建的bucket數(shù)量。

(每個(gè)bucket是一個(gè)槽，可以用來(lái)存放一組鍵值對(duì)。)

微軟建議bucket的數(shù)量應(yīng)是一到兩倍于你所期望的表所要包含的唯一索引鍵的數(shù)量。

此表定義以第二個(gè)WITH 語(yǔ)句結(jié)束。

這里你指定MEMORY_OPTIMIZED 選項(xiàng)為ON 以及DURABILITY 選項(xiàng)為SCHEMA_AND_DATA，此選項(xiàng)是針對(duì)持久表的。

接著在表中插入一條記錄，這樣就可以進(jìn)行測(cè)試了。

數(shù)據(jù)已經(jīng)插入到表中

這就是創(chuàng)建一個(gè)內(nèi)存優(yōu)化表的全部步驟，其他的一切都會(huì)發(fā)生在幕后。

但是，要記住，SQL Server 2014對(duì)這些表有著很多限制。例如，它們不支持外鍵或約束檢查（感覺類似于MySQL的memory存儲(chǔ)引擎），

它們也不支持IDENTITY 字段或DML觸發(fā)器。最為重要的是，內(nèi)存耗盡會(huì)導(dǎo)致寫活動(dòng)停止。

步驟4：

另一方面，內(nèi)存優(yōu)化表支持本地編譯存儲(chǔ)過(guò)程，只要那些存儲(chǔ)過(guò)程只引用內(nèi)存優(yōu)化表。

在這種情況下，存儲(chǔ)過(guò)程可以轉(zhuǎn)化為本地代碼，這樣會(huì)執(zhí)行更快且要比典型存儲(chǔ)過(guò)程需要更少的內(nèi)存。

除了只引用內(nèi)存優(yōu)化表，一個(gè)本地編譯存儲(chǔ)過(guò)程必須是模式綁定的并運(yùn)行在一個(gè)特定執(zhí)行內(nèi)容內(nèi)。

另外，每個(gè)本地編譯存儲(chǔ)過(guò)程必須完全由一個(gè)原子塊組成。

下面的CREATE PROCEDURE 語(yǔ)句定義了一個(gè)本地編譯存儲(chǔ)過(guò)程，它從前例中所創(chuàng)建的Reseller表中檢索數(shù)據(jù)

CREATE PROCEDURE GetResellerType ( @id INT )    WITH NATIVE_COMPILATION,         SCHEMABINDING,         EXECUTE AS OWNERAS    BEGIN  ATOMIC WITH(TRANSACTION ISOLATION LEVEL = SNAPSHOT, LANGUAGE = 'us_english')        SELECT  ResellerName ,                ResellerType        FROM    dbo.Reseller        WHERE   ResellerID = @id    END;GO

在定義了參數(shù)之后，包含一個(gè)WITH 語(yǔ)句來(lái)指定NATIVE_COMPILATION 選項(xiàng)。

注意：此語(yǔ)句還包含SCHEMABINDING 選項(xiàng)和EXECUTE AS 選項(xiàng)，以及指定了OWNER 作為執(zhí)行環(huán)境。

而WITH 語(yǔ)句負(fù)責(zé)實(shí)現(xiàn)本地編譯存儲(chǔ)過(guò)程的三大需求。

要解決原子塊需求，可以在BEGIN 關(guān)鍵字后指定ATOMIC ，之后是另一個(gè)包含有事務(wù)隔離級(jí)別和語(yǔ)言的WITH 語(yǔ)句。

對(duì)于訪問(wèn)內(nèi)存優(yōu)化表的事務(wù)，可以使用SNAPSHOT，REPEATABLEREAD 或SERIALIZABLE 作為隔離級(jí)。

而且，對(duì)于此語(yǔ)言必須使用一個(gè)可用的語(yǔ)言或語(yǔ)言別名。

這是在定義存儲(chǔ)過(guò)程時(shí)所需要包含的全部?jī)?nèi)容。一旦創(chuàng)建，就可以通過(guò)執(zhí)行EXECUTE 語(yǔ)句來(lái)對(duì)其加以測(cè)試，如下例中所示：

EXEC GetResellerType 1;

此語(yǔ)句會(huì)返回經(jīng)銷商的姓名和類型，在本例中分別是ABike Store和Value Added Reseller。

第一個(gè)實(shí)驗(yàn)：內(nèi)存表的數(shù)據(jù)查詢速度比較

聚集索引表和內(nèi)存優(yōu)化表的比較

建表語(yǔ)句

USE TestHekaton;GO--內(nèi)存優(yōu)化表CREATE TABLE testmemory1    (      [ID] FLOAT NOT NULL                       PRIMARY KEY NONCLUSTERED HASH WITH (BUCKET_COUNT = 1024),      [Name] NVARCHAR(50) NOT NULL     )WITH (MEMORY_OPTIMIZED = ON, DURABILITY = SCHEMA_AND_DATA);

USE TestHekaton;GO--聚集索引表CREATE TABLE testmemory2    (      [ID] FLOAT NOT NULL PRIMARY KEY,      [Name] NVARCHAR(50) NOT NULL     )

---------------------------------------------------------------

插入性能比較

內(nèi)存優(yōu)化表

SET STATISTICS IO ON SET STATISTICS TIME ONINSERT into testmemory1([id],[name])  SELECT [id] ,[name] from sysobjectsSET STATISTICS IO OFFSET STATISTICS TIME OFF

Table 'sysschobjs'. Scan count 1, logical reads 33, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0. SQL Server Execution Times:   CPU time = 0 ms,  elapsed time = 20 ms.(90 row(s) affected) SQL Server Execution Times:   CPU time = 0 ms,  elapsed time = 0 ms. SQL Server Execution Times:   CPU time = 0 ms,  elapsed time = 0 ms.

聚集索引表

SET STATISTICS IO ON SET STATISTICS TIME ONINSERT into testmemory2([id],[name])  SELECT [id] ,[name] from sysobjectsSET STATISTICS IO OFFSET STATISTICS TIME OFF

Table 'testmemory2'. Scan count 0, logical reads 183, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.Table 'sysschobjs'. Scan count 1, logical reads 33, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0. SQL Server Execution Times:   CPU time = 0 ms,  elapsed time = 10 ms.(90 row(s) affected) SQL Server Execution Times:   CPU time = 0 ms,  elapsed time = 0 ms.

-------------------------------------------------------------------------------

查詢性能比較

內(nèi)存優(yōu)化表

SET STATISTICS IO ON SET STATISTICS TIME ONSELECT * FROM  testmemory1  ORDER BY [ID] DESCSET STATISTICS IO ONSET STATISTICS TIME ON

SQL Server parse and compile time:    CPU time = 0 ms, elapsed time = 1 ms. SQL Server Execution Times:   CPU time = 0 ms,  elapsed time = 0 ms. SQL Server Execution Times:   CPU time = 0 ms,  elapsed time = 0 ms.(90 row(s) affected) SQL Server Execution Times:   CPU time = 0 ms,  elapsed time = 0 ms. SQL Server Execution Times:   CPU time = 0 ms,  elapsed time = 0 ms.

聚集索引表

SET STATISTICS IO ON SET STATISTICS TIME ONSELECT * FROM  testmemory2  ORDER BY [ID] DESCSET STATISTICS IO ONSET STATISTICS TIME ON

(91 row(s) affected)Table 'testmemory2'. Scan count 1, logical reads 2, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0. SQL Server Execution Times:   CPU time = 0 ms,  elapsed time = 0 ms. SQL Server Execution Times:   CPU time = 0 ms,  elapsed time = 0 ms.