آموزش پیشرفته SQL Server (جلسه پنجم) - Column Store Index

Column Store Index، قسمتی از آموزش پیشرفته SQL Server است، پیشنهاد می شود قبل از مطالعه ی Column Store Index، قسمت های قبلی را مطالعه فرمایید.

آشنایی با ColumnStore Index

قابلیت Column Store Index در SQL Server 2012 ارائه شده است.

Column Store Index برای کار کردن با بانک های اطلاعاتی حجیم که اصطلاحاً Data Warehouse یا انبار داده نامیده می شوند، مناسب است.

Column Store Index یک انقلاب بزرگ در ایندکس ها ایجاد کرده است و به شدت سرعت کوئری ها را افزایش می دهد.

مهمترین مزیت Column Store Index به حداقل رساندن Logical Read و Physical Read است.

در Column Store Index، داده ها بصورت فشرده ذخیره می شوند.

روش های ذخیره داده در ایندکس:

1. Row Based
2. Column Based

در ادامه به طور اختصار در مورد هر کدام از موارد بالا توضیح خواهیم داد.

1- ذخیره اطلاعات بصورت Row Based

 داده های هر رکورد در یک یا چند Page ذخیره می شوند. لطفاً به مثال زیر توجه کنید، ردیف 1 و 2 و 3 در Page 1 و ردیف 4 و 5 و 6 در Page 2 قرار دارند:

<------

2- ذخیره اطلاعات بصورت Column Based

به ازای هر کدام از فیلدهای جدول یک Page مجزا در نظر گرفته می شود.

اگر داده های یک فیلد در یک Page جا نشد، یک Page دیگر اختصاص داده می شود.

<------

<------

 اگر به قسمت های متمایز شده در شکل بالا دقت کنید، متوجه خواهید شد که در فیلد Name مقدار "مهدی" و در فیلد Family مقدار "پهلوان" و "طاهری" تکراری است، در اینجا حتماً این فکر به سرتان خطور کرده که می توان بسادگی مقادیر "مهدی"، "پهلوان" و "طاهری" را بنحوی یکبار درج کرد و از این طریق حجم داده ها را کاهش داد.

بله همین طور است در اینجا به یکی از مهمترین مزیت های Column Store Index پی بردید و آن چیزی نیست جز، کاهش حجم داده های ایندکس و این کاهش حجم باعث می شود که سرعت جستجو در ایندکس ها به شدت افزایش یابد.

مثال: مقایسه جستجو در ایندکس از نوع Column Store Index در مقابل ایندکس معمولی

Select ID,Name,Family
from Employee Where Family="پهلوان"

اگر کوئری بالا از ایندکس عادی که ساختارش بصورت B-Tree است استفاده کند، با توجه به اینکه فامیلی "پهلوان" در 1 Page و Page 2 قرار دارد، باید هر دو Page را وارد حافظه کرده و شروع به Scan و آنالیز آنها نماید در حالی که در ایندکس Column Store Index فقط Page 3 به حافظه منتقل شده و به این ترتیب هم در میزان استفاده از حافظه صرفه جویی می شود و هم Scan داده ها سریع تر انجام می شود. تمام این موارد باعث کاهش چشم گیر I/O خواهد شد.

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

در SQL Server 2012 فقط ایندکس از نوع Non-clustered می توان ایجاد کرد.

با ایجاد ایندکس از نوع Column Store Index جدول مذکور بصورت اتوماتیک در حالت Read-only قرار می گیرد. و اگر قصد Update / Insert / Delete دارید، ابتدا باید ایندکس Column Store Index را غیر فعال (Disable) کنید و بعد از انجام عملیات های مذکور، مجدداً آنرا Rebuild نمایید. (برای Disable یا Rebuild کردن ایندکس های Column Store Index در SQL Server 2012 وارد پنل Object Explorer شده و روی ایندکس مورد نظرتان راست کلیک کنید. در منوی ظاهر شده گزینه های Disable و Rebuild را مشاهده خواهید کرد)

مثال 1: اسکریبت ساخت یک ایندکس Non-clustered از نوع Column Store Index

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

در مثال بالا یک ایندکس با نام "IX_ColumnStore" روی سه فیلد FirstName,LastName,HireDate از جدول Employees البته از نوع Column Store Index ایجاد شده است.

نحوه ی ایجاد Column Store Index در SQL Server 2014

مشکل ساخت ایندکس از انواع دیگر که در SQL Server 2012 وجود داشت، در اینجا رفع شده است. همچنین مشکل Read-only نیز رفع شده و داده های جدول را می توان Update / Insert / Delete نمود.

نکته مهم: اگر در SQL Server 2014 قصد ساخت ایندکس Clustered از نوع Column Store Index دارید، قبل از آن باید از خیر تمام ایندکس های دیگر و حتی کلید جدولتان بگزرید...!  اما این حالت در مورد ایندکس Non-Clustered صادق نیست.

در ادامه آموزش Column Store Index خواهید دید...!

با خرید جلسه 5 از بسته آموزشی "افزایش کارایی پایگاه داده" موارد زیر را خواهید دید:

1. Data Typeها قابل استفاده در Column Store Index کدامند؟
2. چه محدودیت های برای ساخت Column Store Index وجود دارد؟
3. مثال عملی استفاده از Column Store Index

دسترسی به موارد آموزشی بالا در بسته خریداری شده

• شماره جلسه: 5
• نام فایل ویدئو: 02
• فرمت فایل: mp4.

نقطه شروع بحث بالا (گزینه DATA_COMPRESSION) در ویدئو:  21:56

منابع کمکی Column Store Index

1. Column Store Index Description

برای خرید و دانلود کاملآموزش پیشرفته SQL Server کلیک کنید.