SQL SERVER 数据库之内存优化表

小渣渣

内存优化表简介

内存优化表是使用 CREATE TABLE (Transact-SQL) 创建而成的表。

默认情况下，内存优化表具有完全持久性。与（传统）基于磁盘的表上的事务一样，内存优化表上的事务具有完全原子性、一致性、隔离性和持久性 (ACID)。 内存优化表和本机编译的存储过程仅支持一部分 Transact-SQL 功能。

自 SQL Server 2016 起以及在 Azure SQL 数据库中，内存中 OLTP 特定的 排序规则或代码页 没有任何限制。

内存优化表的主存储器是主要内存。 从内存读取表中的行和将这些行写入内存。 表数据的另一个副本维护在磁盘上，但仅用于持续性目的。 有关持久表的详细信息，请参阅 创建和管理用于内存优化的对象的存储 。 内存优化表中的数据仅在数据库恢复期间从磁盘读取（例如，在服务器重启后）。

为了获得更大的性能提升，内存中 OLTP 支持事务持续性延迟的持久表。 延迟的持久事务在提交事务并将控制权归还客户端后不久即保存到磁盘。 为了换取性能的提高，未保存到磁盘的已提交事务会在服务器崩溃或故障转移时丢失。

除了默认持久内存优化表外，SQL Server 还支持未记录的非持久内存优化表，并且其数据不会保留在磁盘上。 这意味着，这些表上的事务不需要任何磁盘 IO，但如果发生服务器故障或故障转移，则数据会丢失。

内存中 OLTP 与 SQL Server 集成，以便在所有方面（如开发、部署、可管理性和可支持性）提供无缝体验。 数据库可包含内存中对象以及基于磁盘的对象。

内存优化表中的行是版本化的。 这意味着表中的每行都可能有多个版本。 所有行版本均维护在同一个表数据结构中。 行版本控制用于实现对同一行的并发读取和写入。 有关对同一行的并发读取和写入的更多信息，请参阅 内存优化表中的事物。

使用大概步骤与限制

步骤：

（1）创建数据库和MEMORY_OPTIMIZED_DATA文件组（alter database add filegroup，每个数据库只能有一个MEMORY_OPTIMIZED_DATA文件组！！）
（2）添加一个存放数据文件的文件夹到文件组（请不要预先创建好这个文件夹，用alter database add file（） to filegroup）
（3）创建持久化/非持久化 的内存优化表（sql server2014默认是持久化的），同时你还必须定义一个基于非聚集哈希索引的主键。哈希索引是在内存优化表中唯一支持的索引类型。
（4）使用本地编译存储过程：内存优化表支持本地编译存储过程，只要那些存储过程只引用内存优化表。

限制：

（1）内存优化表必须放在有 CONTAINS MEMORY_OPTIMIZED_DATA 文件组的数据库下。
（2）内存优化表必须定义一个基于非聚集哈希索引的主键，且该表不支持外键或约束检查
（3）内存优化表必须要有足够的内存，否则插入更新等操作会失败。
（4）SQL Server 2014对这些表有着很多限制。例如，它们不支持外键或约束检查（感觉类似于MySQL的memory存储引擎），它们也不支持IDENTITY 字段或DML触发器。

使用教程

数据库版本如下：

Microsoft SQL Server 2022 (RTM-CU15-GDR) (KB5046059) - 16.0.4150.1 (X64)
        Sep 25 2024 17:34:41
        Copyright (C) 2022 Microsoft Corporation
        Enterprise Edition: Core-based Licensing (64-bit) on Linux (Ubuntu 22.04.4 LTS) <X64>

首先，创建数据库和文件组，命令如下：

登录可见。

如果未创建内存优化组，在创建内存优化表的时候，会报错：Cannot create memory optimized tables. To create memory optimized tables, the database must have a MEMORY_OPTIMIZED_FILEGROUP that is online and has at least one container.

创建两张内存优化表进行测试，一张是持久化的，一张是非持久化的，命令如下：

登录可见。

内存优化表有两个选项（DURABILITY = SCHEMA_AND_DATA），（DURABILITY = SCHEMA_ONLY）
持久表：  DURABILITY = SCHEMA_AND_DATA
非持久表： DURABILITY = SCHEMA_ONLY

测试向内存优化表分别插入 10 万条（实际是 100002 条）数据，命令如下：

登录可见。

登录可见。

开启持久化的耗时 28 秒，未开启持久化耗时 1 秒。

参考：

https://learn.microsoft.com/zh-cn/sql/relational-databases/in-memory-oltp/introduction-to-memory-optimized-tables?view=sql-server-ver16
https://www.cnblogs.com/seriawei/p/sql-server-in-memory-oltp.html
https://www.cnblogs.com/Leo_wl/p/16303428.html
https://mostafaelmasry.com/2015/03/25/durable-vs-non-durable-tables-in-memory-oltp/
https://www.cnblogs.com/poepoe/p/7196284.html

小渣渣

普通表插入 10 万条数据，执行耗时 18 秒。如下图：



脚本：

登录可见。