آموزش Column Store Index در SQL Server

مقدمه

ماهنامه دانش و کامپیوتر‌: Column Stored Index یکی از قابلیت‌های جالبی است که در SQL Server 2012 ارائه شده است. به وسیله این قابلیت می‌توان سرعت اجرایی کوئری‌های را تا چندین برابر بهبود بخشید.
مایکروسافت مهم‌ترین هدف خود را از ارائه این قابلیت افزایش سرعت اجرای کوئری‌های بر روی Data Warehouseهای بزرگ ذکر کرده است.
اما می‌توان از آن در شرایطی خاص بر روی OLTP نیز استفاده نمود.
قبل از اینکه به معرفی این قابلیت جدید SQL Server بپردازیم باید شما با مفاهیم اولیه درباره Performance Tuning آشنا شوید.

آشنایی با مفهوم Data File و Log File

همانطور که می‌دانید یک بانک اطلاعاتی در SQL Server حداقل از دو فایل (Data File و Log File) تشکیل شده است. در Data File کلیه اشیائی (مانند فیلد، جدول، ایندکس و…) که شما ایجاد می‌کنید ذخیره می‌شود و به طور ساده در Log File هر چیزی که منجر به تغییر این داده‌ها شود ذخیره می‌شود. برای مثال چنانچه شما مقدار رکوردی را تغییر دهید Log عملیات مربوط به تغییر در Log File ثبت می‌گردد. این Log می‌تواند شامل تصویر داده قبل و بعد از تغییر داده و همچنین زمان انجام کار و… باشد.

آشنایی با مفهوم Page

همانطور که قبلاً هم اشاره کردیم داده‌هایی که شما ایجاد کرده‌اید در درون Data File ثبت می‌شود. درون Data File بلوک‌های ۸KB قرار دارد که به این بلوک‌ها Page می‌گویند. در SQL Server بیش از ۱۰ نوع Page وجود دارد. که SQL Server بر حسب نیاز از آنها استفاده می‌کند.

آشنایی با مفهوم Index در SQL Server

به طور خیلی ساده، ایندکس لیست مرتب شده‌ای از داده‌ها است که بر اساس منطقی خاص مانند عدد، رشته، تاریخ و … ایجاد شده است. هدف از ایجاد ایندکس افزایش سرعت جستجو برای بازیابی داده‌ها می‌باشد.

آشنایی با انواع ایندکس‌ها

در SQL Server پنج نوع ایندکس به شرح زیر وجود دارد
• Clustered Index: این نوع ایندکس‌ها بر روی داده‌های عادی مانند عدد، رشته، تاریخ و… ایجاد می‌شوند. زمانی که یک Clustered Index بر روی یک جدول ایجاد می‌کنید ترتیب و چینش فیزیکی رکوردها بر اساس کلید ایندکس خواهد بود. بنابراین یک جدول صرفاً می‌تواند یک ایندکس از نوع Clustered  داشته باشد.
• NonClustered Index: این نوع ایندکس‌ها مانند Clustered Indexها بر روی داده‌های عادی مانند عدد، رشته، تاریخ و… ایجاد می‌شوند. زمانیکه یک ایندکس از نوع NonClustered بر روی جدول ایجاد می‌کنید کلید ایندکس در فضایی دیگری جدای از فضای جدول مرتب شده و مورد استفاده قرار می‌گیرد. به طور کلی در SQL Server می‌توان تا ۹۹۹ ایندکس از نوع NonClustered را ایجاد کرد.
• XML Index: این نوع ایندکس‌ها بر روی داده‌هایی  که در قالب ساختار XML هستند ایجاد می‌شود.
• Spatial Index: این نوع ایندکس‌ها بر روی داده‌هایی که از نوع جغرافیایی هستند ایجاد می‌شود.
• Column Stored Index: نوع جدیدی از ایندکس‌ها هستند که در SQL Server 2012 معرفی شده‌اند که سرعت دسترسی به داده‌ها در آن بیش از سایر ایندکس‌ها می‌باشد.

آشنایی با مفهوم Plan

Execution Plan  اجرایی یا نقشه اجرایی کوئری ترتیب اجرای فیزیکی دستورات را مشخص می‌نماید. زمانیکه شما کوئری را اجرا می‌کنید SQL Server آن را به همان ترتیبی که نوشته شده است اجرا نمی‌کند. بلکه ترتیب دسترسی به داده‌های موجود در جداول با توجه به پارامترهای مختلف مانند وضعیت ایندکس‌ها، وضعیت منابع سرور و… خواهد بود.
پس از مشخص شدن ترتیب اجرای فیزیکی SQL Server نقشه آن را با عنوان Execution Plan ذخیره می‌کند تا در آینده بتواند از آن استفاده کند. چنانچه بخواهید نقشه اجرایی با Execution Plan یک کوئری را مشاهده نمایید کافی است که کوئری مورد نظر خود را Highlight نموده و کلید Ctrl+L را فشاره دهید تا Estimate Plan یا نقشه اجرایی تخمینی برای شما نمایش داده شود.
در تصویر زیر نمونه‌ای از Execution Plan یک کوئری نمایش داده شده است.

SELECT * FROM Orders O
INNER JOIN [Order Details] OD ON O.OrderID=OD.OrderID
INNER JOIN Employees E ON O.EmployeeID=E.EmployeeID

نکته: Execution Plan از سمت راست به چپ و از بالا به پایین خوانده می‌شود.

آشنایی با انواع ساختارهای رایج ذخیره‌سازی داده‌ها در SQL Server

زمانیکه شما جدولی در SQL Server ایجاد می‌کنید. داده‌های موجود در این جدول در قالب Pageهایی ذخیره می‌شوند. ساختار قرار گیری این Pageها می‌تواند به دو حالت زیر باشد.
۱- Row Store: در این ساختار داده‌ها به شکل سطری یا Row Based در Pageها ذخیره می‌شوند. در SQL Server ساختار ذخیره‌سازی Row Store به دو حالت زیر می‌باشد.
۱.۱. Heap: به طور خیلی ساده جدولی که فاقد Clustered Index باشد به آن Heap می‌گویند. زمانیکه یک جدول به صورت Heap باشد رکوردهای موجود در آن دارای نظم و ترتیب نبوده و اگر حجم رکوردهای این نوع جداول زیاد باشد کارایی بانک اطلاعاتی به شدت پایین می‌آید.
فرض کنید جدولی به شکل زیر دارید.

CREATE TABLE Employees
)
,EmployeeID INT
,FirstName NVARCHAR(50)
LastName NVARCHAR(50)
(

این جدول به شکل Heap بوده و زمانیکه شما تعدادی رکورد در جدول وارد کنید رکوردهای موجود در آن به شکل Row Based (سطری) در Pageهای مربوط به جدول ذخیره می‌شوند.
لازم به ذکر است رکوردهای موجود در جدول دارای نظم و ترتیب خاصی نمی‌باشند و به ترتیب درج به انتهای Pageهای مربوط به جدول اضافه می‌شوند. همچنین مطابق شکل فیلدهای مربوط به هر رکورد در یک Page یکسان ذخیره شده‌اند.
۱.۲. Clustered: در صورتیکه یک جدول به شکل Clustered باشد رکوردهای موجود در آن دارای نظم و ترتیب است. رکوردهای موجود در جدولی که به شکل Clustered می‌باشد بر اساس Clustered Key و یا کلید ایندکس می‌باشد.
جدول زیر را در نظر بگیرید.

CREATE TABLE Employees
)
,EmployeeID INT PRIMARY KEY CLUSTERED
,FirstName NVARCHAR(50)
LastName NVARCHAR(50)
(

این جدول به شکل Clustered بوده و زمانیکه شما تعدادی رکورد در جدول وارد کنید رکوردهای موجود در آن به شکل Row Based (سطری) در Pageهای مربوط به جدول ذخیره می‌شوند.
لازم به ذکر است رکوردهای موجود در جدول دارای نظم و ترتیب بوده و ترتیب آن‌ها بر اساس Clustered Key یا کلید ایندکس می‌باشد. در این مثال کلید ایندکس فیلد EmployeeID بوده که ترتیب فیزیکی رکوردها در داخل Pageهای مربوط به جدول بر اساس آن می‌باشد. همچنین مطابق شکل فیلدهای مربوط به هر رکورد در یک Page یکسان ذخیره شده‌اند.
۲- Column Store : در این ساختار داده‌ها به شکل ستونی یا Column Based در Pageها ذخیره می‌شوند. با استفاده از این روش ذخیره‌سازی فیلدهای مربوط به هر رکورد در Pageهایی یکسان ذخیره می‌شوند.

محاسن نگهداری داده‌ها با استفاده از روش Column Based

۱- به حداقل رساندن Logical Read و Physical Read‌

با توجه به اینکه در روش Column Based هر کدام از فیلدها در Pageهایی جداگانه ذخیره می‌شوند زمانیکه کوئری شما نیاز به خواندن داده‌‌های فیلدهای مورد نظر خود داشته باشد عملیات IO صرفاً به ازای Pageهای مربوط به آن فیلدها خواهد بود. این موضوع باعث خواهد شد که کوئری‌های شما کمترین عملیات IO را برای واکشی رکوردها انجام دهد.
کوئری زیر را در نظر بگیرید این کوئری به ازای حالت Column Based و حالت Row Based اجرا شده است.
کوئری زیر در حالتی است که اطلاعات جدول به صورت Row Based ذخیره شده است.

SELECT Code,FirstName,LastName FROM Employees_RowBased

Execution Plan کوئری فوق به شکل زیر می‌باشد.
کوئری زیر در حالتی است که اطلاعات جدول به صورت Column Based ذخیره شده است.

SELECT Code,FirstName,LastName FROM Employees_ColumnBased

Execution Plan کوئری فوق به شکل زیر می‌باشد.
با بررسی Cost (هزینه) دو کوئری بالا متوجه خواهید می‌شوید که هزینه کوئری دوم (ColumnBased) به مراتب پایین‌تر از کوئری اول (RowBased) است. دلیل این موضوع این است که در کوئری اول با توجه به معماری ذخیره‌سازی اطلاعات باید کلیه Pageهای مربوط به جدول برای پیدا کردن اطلاعات اسکن شود.
ولی در کوئری دوم با توجه به معماری ذخیره‌سازی اطلاعات صرفاً Pageهای مورد نیاز برای واکشی اطلاعات اسکن شود. بنابراین کوئری دوم عملیات IO کمتری نسبت به کوئری اول خواهد داشت.
لازم به ذکر است مطابق Plan اجرایی هزینه کوئری دوم (ColumnBased) نسبت کوئری اول(RowBased) برابر با ۷۹ به ۲۱ است. بنابراین هزینه کوئری دوم به مراتب کمتر از کوئری اول می‌باشد.

۲- ذخیره‌سازی داده‌ها به شکل فشرده‌

با توجه به اینکه در معماری Column Based مقادیر موجود در هر فیلد در Pageهایی جداگانه ذخیره می‌شوند. احتمال تکراری بودن مقادیر فیلدها به شدت بالا است. برای مثال ممکن است در فیلد City چندین مقدار تهران را داشته باشیم در این صورت الگورتیم فشرده‌سازی مورد استفاده در SQL Server (Hoffman) به راحتی می‌تواند مقادیر تکراری را شناسایی و حجم بیشتری از آنها را در Pageهای کمتری ذخیره کند.
این مو ضوع باعث خواهد شد که حجم ایندکس کمتر شده و SQL Server برای بدست آوردن رکوردها عملیات IO کمتری را متحمل شود.

نحوه ایجاد Column Stored Index در SQL Server 2012

برای ایجاد Column Stored Index ابتدا باید فیلدهای شرکت کننده در ایندکس را تعیین و پس از آن ایندکس را ایجاد نمایید. مثال زیر نحوه انجام اینکار را نمایش می‌دهد.
۱- برای شروع کار یک جدول جدید ایجاد می‌کنیم. این جدول دارای یک Primary Key است که با استفاده از Clustered Index ایجاد شده است. بدلیل اینکه جداول شما از قبل وجود دارند ممکن است این مرحله جزء کار شما نباشد.

USE tempdb
GO
IF OBJECT_ID('Employees', 'U') IS NOT NULL
DROP TABLE Employees
GO
CREATE TABLE Employees_ColumnBased
(
Code INT IDENTITY CONSTRAINT PK_Code PRIMARY KEY,
FirstName NVARCHAR(50),
LastName NVARCHAR(80),
HireDate SMALLDATETIME,
City NVARCHAR(20)
)
GO

۲- در این مرحله تعدادی داده تستی در جدول درج می‌کنیم.

INSERT INTO Employees_ColumnBased (FirstName,LastName,HireDate,City) VALUES
(N'مسعود',N'طاهری','۲۰۰۰-۰۱-۰۱',N'میانه'),
(N'فرید',N'طاهری','۲۰۰۳-۰۱-۰۱',N'میانه'),
(N'احمد',N'غفاری','۲۰۰۳-۰۱-۰۱',N'میانه'),
(N'خدیجه',N'افروزنیا','۲۰۰۰-۰۱-۰۱',N'تهران'),
(N'مجید',N'طاهری','۲۰۰۵-۰۱-۰۱',N'تهران')
GO
INSERT INTO Employees_ColumnBased (FirstName,LastName,HireDate,City)
SELECT FirstName,LastName,HireDate,City FROM Employees_ColumnBased
GO 10

۳- در این مرحله یک Column Stored Index به ازای فیلدهای مورد نیاز ایجاد می‌کنیم. لازم به ذکر است در SQL Server 2012 این نوع ایندکس به شکل Non Clustered Column Stored Index می‌باشد.

CREATE NONCLUSTERED COLUMNSTORE INDEX IX_ColumnStore
ON Employees_ColumnBased(FirstName,LastName,HireDate)
GO

نکته مهمی که باید در ایجاد این نوع از ایندکس‌ها در نظر گرفت این است که به دلیل افزایش Performance معمولاً در بیشتر مواقع این ایندکس‌ها با توجه به کوئری‌های شما ایجاد می‌گردند. بدین صورت که:

۱- لیست تمامی کوئری‌های پر استفاده به ازای جدول مورد نظر را استخراج نمایید.
۲- کلیه فیلدهای مربوط به مرحله اول را استخراج نمایید.
۳- در صورتیکه فیلدهای استخراج شده در مرحله دوم جزء فیلدهای قابل شرکت در Column Stored Index باشند در ایندکس مورد نظر شرکت نمایند.
۴- در صورتیکه بخواهید Column Stored Index را به صورت ویژوالی در Management Studio ایجاد کافی است مراحل زیر را انجام دهید.

• همانند تصویر زیر پس از انتخاب جدول بر روی قسمت ایندکس کلیک راست کرده و پس از انتخاب گزینه New Index گزینه Non Clustered Column Stored Index را انتخاب کنید.
• همانطور که در پنجره New Index مشاهده می‌کنید نوع ایندکس شما Non Clustered Column Store بوده و با استفاده از دکمه Add به راحتی می‌توانید فیلدهای مورد نظر را به ایندکس اضافه نمایید.

محدودیت‌های مربوط به Column Stored Index

با توجه به استفاده از این نوع ایندکس بر روی جدول شما محدودیت ایجاد می‌کند. تعدادی از این محدودیت‌ها در لیست زیر بررسی شده است.
۱- ایجاد Column Stored Index به صورت یک ایندکس NonClustered
۲- امکان استفاده از Column Stored Index به صورت Filter شده (Filtered Index) وجود ندارد.
۳- زمانیکه یک جدول دارای Column Stored Index باشد. کلیه داده‌های موجو.د در آن به شکل Readonly در دسترس خواهد بود. در صورتیکه بخواهید اطلاعات موجود در جدول را تغییر دهید باید مراحل زیر را انجام دهید.
۳.۱. Column Stored Index مربوط به جدول مورد نظر را غیر فعال کنید

ALTER INDEX IX_ColumnStore ON Employees_ColumnBased DISABLE
GO

۳.۲. داده‌های خود را تغییر دهید. این تغییرات می‌تواند شامل انجام عملیات Insert، Update، Delete و… بر روی جدول باشد.
۳.۳. Column Stored Index را مجدداً Rebuild نمایید.

ALTER INDEX IX_ColumnStore ON Employees_ColumnBased REBUILD
GO

۴- فیلدهایی که Data Typeها آنها از نوع (CLR, UniqueIdentifier, Binary, VarBinary, VarChar(max), NvarChar(max نمی‌توانند در این نوع ایندکس شرکت کنند.
۵- جداولی که دارای این نوع ایندکس هستند نمی‌توانند در Replication شرکت کنند.
۶- امکان استفاده از Computed Column و همچنین Sparse Column در این نوع ایندکس‌ها وجود ندارد.
۷- امکان استفاده از Column Stored Index بر روی Index View وجود ندارد.
۸- تعداد فیلدهای شرکت کننده در این نوع ایندکس بیش از ۱۰۲۴ فیلد نمی‌تواند باشد.

نکته بسیار مهم

در SQL Server 2014 تغییراتی در Column Stored Index ایجاد شده یکی از مهمترین آنها ایجاد Clustered Index به شکل Column Stored می‌باشد. در این حالت دیگر جدول شما از نوع Read-Only نخواهد بود.