Giới Thiệu SQL Server In-Memory OLTP

In-Memory Online Transaction Processing (OLTP), còn được gọi là Hekaton hay In-Memory Optimization, là phiên bản mới nhất của công nghệ xử lý dữ liệu trên bộ nhớ của Microsoft nhằm tối ưu hóa tốc độ truy xuất, được tích hợp trong SQL Server’s Database Engine và được sử dụng hoàn toàn giống như các thành phần của Database truyền thống.

In-Memory OLTP được xuất bản trong SQL Server 2014 nhưng còn rất nhiều thiếu sót như không thể dùng được lệnh ALTER TABLE và được hoàn thiện trong SQL Server 2016. Kiến trúc của nó bao gồm:

• Các bảng tối ưu hóa bằng bộ nhớ Memory-Optimized Tables
• Các thủ tục biên dịch tự nhiên Natively-Compiled Stored Procedures
• Hash index và Range index

Memory-Optimized Tables là các bảng được tạo ra bằng lệnh CREATE TABLE, hoàn toàn ổn định và bền vững như các table trên ổ cứng truyền thống.

Dữ liệu của Memory-Optimized tables được lưu trữ bộ nhớ RAM. Các record trong bảng được đọc và ghi vào bộ nhớ, một bản copy dữ liệu của bảng sẽ được bảo quản trong ổ cứng thật để an toàn dữ liệu. Dữ liệu trong bảng memory chỉ được đọc lại từ bảng trên ổ cứng trong quá trình recovery, ví dụ như khi restart server.

Memory-Optimized Tables sao lưu dữ liệu vào bản trên ổ cứng bằng cơ chế transaction delayed. Các thay đổi dữ liệu sẽ được lưu vào ổ cứng ngay khi giao dịch được xử lý hoàn tất và đã trả kết quả lại cho người dùng. Cho nên đổi lại cho việc tăng đáng kể performance, các transaction đã commit mà không kịp lưu vào đĩa sẽ bị mất nếu server bị down hay treo ngay lúc đó.

Memory-Optimized Tables có 2 dạng:

• Durable (DURABILITY=SCHEMA_AND_DATA): ở dạng mặc định, vừa lưu data vào bảng trên bộ nhớ, vừa lưu data vào bảng trên ổ cứng.
• Non-durable (DURABILITY=SCHEMA_ONLY): chỉ lưu dữ liệu trên bộ nhớ mà không lưu lại vào ổ cứng. Các table dạng này thường chỉ dùng lưu dữ liệu tạm thời như các bảng làm trung gian. Những bảng này không cần sử dụng ổ cứng, và dữ liệu không được phục hồi nếu server down.

Memory-Optimized Tables được sử dụng giống như bảng truyền thống cho cùng các hoạt động develop, triển khai, bảo trì. Một database có thể chứa cả bảng ảo và bảng thật chung với nhau.

Dữ liệu trong các bảng MO được truy xuất theo 2 cách:

• Qua các lệnh Transact-SQL truyền thống.
• Bằng các Natively-Compiled Stored Procedures

Để tạo một database sử dụng In-Memory OLTP, cần thực hiện các bước:

• Tạo một memory-optimized data filegroup và gán đường dẫn vào file group.
• Tạo các bảng memory-optimized tables và các indexes.

CREATE DATABASE imoltp;
GO 
ALTER DATABASE imoltp ADD FILEGROUP [imoltp_mod] CONTAINS MEMORY_OPTIMIZED_DATA;  
ALTER DATABASE imoltp ADD FILE (name = [imoltp_dir], filename='c:\data\imoltp_dir') TO FILEGROUP imoltp_mod;  
GO 
USE imoltp  
GO  
CREATE TABLE dbo.ShoppingCart (   
 ShoppingCartId INT IDENTITY(1,1) PRIMARY KEY NONCLUSTERED,  
 UserId INT NOT NULL INDEX ix_UserId NONCLUSTERED HASH WITH   (BUCKET_COUNT=1000000),   
 CreatedDate DATETIME2 NOT NULL,   
 TotalPrice MONEY  
) WITH (MEMORY_OPTIMIZED=ON, DURABILITY=SCHEMA_AND_DATA)

HASH INDEXES TRONG IN-MEMORY OLTP TABLES

Với sự ra đời của In-Memory OLTP (mã nguồn Hekaton), cách index theo cây B-Tree cũ không còn là giải pháp tối ưu để tăng tốc độc truy xuất. Mục tiêu của Hekathon là tăng tốc độ xử lý lên gấp 100 lần, nên một phương pháp index mới được đưa ra là hash index.

Hash index là một giải pháp lý tưởng để tăng tốc truy vấn, sử dụng tốt nhất cho các truy vấn có mệnh đề WHERE chỉ tới một giá trị chính xác của cột được index.

SELECT * FROM EmpInformation

WHERE LastName = Bell

Chúng ta cùng tìm hiểu về cấu trúc của hash index, cách nó làm việc trong các truy vấn và thay đổi dữ liệu.

1. Cấu trúc Hash Index

Giải thích một cách đơn giản, hash indexes là một tập hợp các ngăn chứa (buckets) được sắp xếp thành một mảng. Mỗi bucket chứa một con trỏ tới một dòng dữ liệu trong bộ nhớ.

Cách thức các in-memory table lưu trữ dữ liệu không giống các table truyền thống, mỗi record bao gồm hai phần:

• Dữ liệu hệ thống gồm có row timestamp, các con trỏ chỉ mục (index pointer).
• Dữ liệu của người dùng.

Timestamp gồm có BeginTs cho ta biết thời gian khi một dòng được insert, và EndTs cho biết thời gian khi một dòng không còn giá trị sử dụng (bị xóa hoặc update). Một dòng chỉ còn được sử dụng khi EndTs của nó bằng vô cùng.

2. Cơ chế hoạt động

Khi một dòng mới được insert vào một bảng, một hàm băm map khóa index trên dòng dữ liệu với một bucket tương ứng trên cây hash index. Ví dụ hiện tại chúng ta đang tạo hash index trên cột FirstName và giá trị Beatrix sẽ được map với một bucket B (bucket tên B chỉ là một ví dụ minh họa), một con trỏ được tạo ra link bucket với dòng dữ liệu này. (Figure 1)

Hàm băm luôn cho ra cùng một giá trị đầu ra khi cho cùng một giá trị đầu vào. Các record được insert mà các giá trị của cột index qua hàm băm cho ra giá trị giống nhau sẽ được liên kết vào cùng một bucket.

Figure 1

Không lâu sau hai dòng nữa được insert vào tại cùng thơi điểm BeginTs 20. (Figure 2)

Dòng thứ nhất có giá trị Bill được đẩy vào cùng bucket B do hàm băm, một con trỏ mới được chỉ tới dòng mới này từ dòng trên Beatrix.

Dòng thứ hai được insert có giá trị Zoe trên cột Firstname, sẽ được hàm băm tạo liên kết tới bucket Z.

Figure 2

Tiếp theo, một lệnh update với thời gian BeginTs time là 30 update 2 record: Bill chuyển địa chỉ thành thành phố Basin, Zoe đổi thành thành phố Connell. (Figure 3)

Hai record hiện tại chỉ update EndTs time thành 30 cho biết các dòng này không còn sử dụng nữa.

Hai record mới sẽ được insert vào với BeginTs là 30 và EndTs là vô cùng. Đồng thời hai con trỏ mới được trỏ từ dòng cũ sang dòng mới.

Các dòng cũ sau một thơi gian sẽ bị hủy liên kết và xóa bởi trình thu gom rác.

Lệnh DELETE cũng hoạt động tương tự, nhưng chỉ cập nhật EndTs cho dòng cũ mà không insert thêm dòng mới.

Nếu một lệnh SELECT truy vấn khi thời gian lớn hơn 30 thì các dòng cũ có EndTs time <= 30 sẽ bị bỏ qua hết.

Figure 3

Các bảng OLTP hỗ trợ tạo nhiều hash index, mỗi hash index dùng các mảng bucket và con trỏ riêng biệt.

Trong ví dụ dưới đây, index thứ hai được tạo trên cột LastName. Các record được liên kết với các bucket theo thứ tự khác, nhưng thứ tự theo version vẫn giữ nguyên. (Figure 4)

Mỗi bảng OLTP nên có ít nhất một và tối đa 8 index, và có một khóa chính, khóa chính nếu tạo hash index thì được tính vào trong 8 index được tối đa.

Figure 4

3. Cú pháp tạo Hash Indexes

Hash indexes có thể được tạo trong lệnh CREATE TABLE hoặc thêm vào sau bằng lệnh ALTER TABLE.

CREATE TABLE EmpInformation  
(  
EmpID int NOT NULL IDENTITY(1,1)  
PRIMARY KEY NONCLUSTERED,  
FirstName nvarchar(25) NOT NULL,
LastName nvarchar(25) NOT NULL,
City nvarchar(25) NULL,
INDEX IX_Hash_FirstName HASH (FirstName) WITH (BUCKET_COUNT = 100000)  
)  
WITH ( MEMORY_OPTIMIZED = ON,  DURABILITY = SCHEMA_AND_DATA);

Khai báo Bucket_Count: mỗi bucket có dung lượng 8 byte chứa một con trỏ tới đúng khóa được index của record. Mỗi record là một phần của chuỗi index, được link tới một record cha và một record con.

Nếu số lượng record lớn và số lượng bucket nhỏ thì chuỗi index sẽ dài làm cho tiến trình quét lấy ra đúng record trong chuỗi sẽ mất nhiều thời gian ảnh hưởng tới performance. Tiến trình scan này bao gồm cả tìm ra đúng dòng để update EndTs khi thực hiện lệnh UPDATE hay DELETE. Nếu số lượng bucket lớn và số lượng data ít thì gây dư thừa và lãng phí bộ nhớ.

Không có con số lý tưởng hay chính xác để set cho bucket count. Microsoft khuyên nên chọn một số nằm giữa số lượng các record mà có cột index là duy nhất. Ví dụ nếu có table dbo.Orders có 100.000 distinct order ID thì khi index trên cột này bucket count nên set trong khoảng 100.000 đến 200.000.

Hash indexes không hiệu quả cao khi được đánh trên cột có quá nhiều trường hợp trùng (duplicates). Các giá trị trùng tạo ra cùng giá trị hash (băm) giống nhau sẽ tạo thành một chuỗi liên kết index dài. Nói chung, nếu tỉ lệ tổng số record trên tổng số record duy nhất lớn hơn 10 thì nên sử dụng range index để thay thế. Ví dụ nếu có 100.000 employees trong một bảng mà có tổng số job titles duy nhất là 300 thì không nên dùng hash index trên cột job titles mà phải dùng range index, vì tỉ lệ 100.000 / 300 ~ 333.