نمای کلی از شاخص های ستون در سرور SQL

همانطور که اخیراً Aaron Bertrand در مورد آن نوشت ، SQL Server 2016 SP1 بسیاری از امکانات جدید را برای نسخه های استاندارد ، وب و اکسپرس SQL Server باز می کند ، از جمله گزینه های جدید برای فناوری های حافظه مانند فهرست های ستونی و جداول بهینه سازی شده حافظه (البتهبا محدودیت عملکرد). قابلیت های ذکر شده در این پست ، که در نسخه های اخیر به طور قابل توجهی تکامل یافته است ، برای SQL Server 2016 SP1 کاربرد دارد.

مقایسه گزینه های حافظه موجود در SQL Server

قبل از اینکه به عنوان اصلی ترین تمرکز این پست وارد فهرست های ستونی شویم ، ابتدا می توانیم به انواع ویژگی های حافظه موجود در SQL Server نگاهی بیندازیم:

• شاخص های ستونی
• میزهای بهینه شده حافظه

تشخیص تفاوت بین فن آوری های حافظه در سرور SQL می تواند کاملاً گیج کننده باشد. هر ویژگی را می توان با نام های مختلف ارجاع داد ، و این نام ها را می توان به راحتی درک کرد (به عنوان مثال ، تفاوت بین ستونهای خوشه ای و غیر خوشه ای هیچ ارتباطی با سفارش داده ها ندارد). بعضی اوقات از ویژگی ها به طور همزمان استفاده می شود ، بنابراین دریافت وضوح در هر مؤلفه می تواند یک چالش باشد.

هر یک از این ویژگی های حافظه ، اساساً انواع مختلفی از بارهای کاری را هدف قرار می دهند. در زیر خلاصه ای از هر ویژگی وجود دارد:

• شاخص ستونی غیر خوشه ای: نمایش داده های تحلیلی را ارائه می دهد.
• جدول Rowstore: در خدمت نمایش داده های OLTP.

شاخص های ستونی غیر خوشه ای یک شاخص ثانویه هستند ، بنابراین یک NCCI فقط شامل ستون های انتخابی در یک جدول مورد نیاز برای پشتیبانی از نمایش داده های تحلیلی است. از آنجا که آنها در بالای جدول RowStore ساخته شده اند ، NCCIS نیازهای کلی ذخیره داده ها را کاهش نمی دهد (نحوه انجام CCIS).

• سناریوهای HTAP (پردازش تحلیلی معاملاتی ترکیبی) که در آن یک سیستم معامله ای نیز نیاز به پشتیبانی از نمایش داده های تحلیلی / BI عملیاتی دارد. HTAP مناسب است که نیازهای تحلیلی بدون نیاز به ادغام داده ها با سایر سیستم ها از یک سیستم معامله ای واحد برآورده شود.

اولین بار معرفی شد: SQL Server 2012

یک شاخص ستونی خوشه ای ذخیره سازی از RowStore به Columnstore را تغییر می دهد ، بنابراین تمام ستون های یک جدول در CCI گنجانده شده است. با توجه به ساختار ستونی ، برای داده های کم کاردینالیت می توان ذخیره داده ها را به میزان قابل توجهی کاهش داد.

CCIS به طور معمول در یک جدول مبتنی بر دیسک اجرا می شود ، اما اگر بار کار آن را توجیه کند ، می تواند در یک جدول بهینه سازی شده حافظه اجرا شود (یعنی اگر تفاوت زیادی بین داده های "داغ" وجود ندارد که هنوز به روزرسانی ها را دریافت می کنند و داده های "سرماخوردگی"تغییرات طولانی تر)

• بارهای کاری ذخیره سازی داده ها که به قالب طرح واره ستاره ای غیرعادی سازی شده اند (به عنوان مثال، غیرعادی سازی از فرمت ستون گرا بهره کامل می برد).

سیستم های انتخابی غیر DW

• بارهای کاری درج گرا (مثلاً: IoT)، که دارای حداقل به روز رسانی و حذف هستند و نیاز به پشتیبانی از پرس و جوهای تحلیلی یا BI عملیاتی دارند.

اولین بار معرفی شد: SQL Server 2014

OLTP درون حافظه به خودی خود برای بارهای کاری تحلیلی مناسب نیست. با این حال، اگر پرس و جوهای تحلیلی/تجمعی نیز الزامی باشد، می توان آن را همراه با فهرست های فروشگاه ستون استفاده کرد.

دو نوع جداول بهینه سازی شده برای حافظه وجود دارد: بادوام (بر روی دیسک پس از راه اندازی مجدد) و غیر بادوام (فرار، مانند جدول دمای جهانی).

جداول بهینه شده برای حافظه (و متغیرهای جدول بهینه شده برای حافظه) در کدهای بومی کامپایل می شوند. این کار عملکرد پرس و جو را بهبود می بخشد زیرا کامپایل قبل از اجرای پرس و جو تکمیل می شود.

• حجم زیادی از تراکنش های نوشتن با تاخیر کم.
• داده های داغ در مقابل داده های سرد به ندرت قابل دسترسی هستند.
• بهینه سازی استفاده از جدول #temp.

اولین بار معرفی شد: SQL Server 2014

برای اختصار، سایر فناوری های ذخیره سازی داده های درون حافظه موجود در پلتفرم مایکروسافت خارج از محدوده این پست هستند:

• خدمات تجزیه و تحلیل سرور SQL
• خدمات تجزیه و تحلیل Azure
• Power BI Desktop، Power BI Service و Power BI Embedded
• Power Pivot برای Excel و Power Pivot برای SharePoint
• جرقه در HDInsight

بسیاری از ویژگی های حافظه در پلتفرم مایکروسافت به موتور xVelocity (که قبلا VertiPaq نامیده می شد) متکی هستند. پیاده سازی ها بین محصولات تا حدودی متفاوت است، مانند الزامات برای نگهداری واقعی داده ها در حافظه.

بقیه این پست بر روی دو فناوری ذخیره ستونی در SQL Server تمرکز خواهد کرد: خوشه ای و غیر خوشه ای.

Primer در Rowstore در مقابل Columnstore

به طور سنتی، داده ها در SQL Server در قالب rowstore ذخیره می شوند (نباید با عبارت rowgroup که مفهومی متفاوت است اشتباه گرفته شود). فرمت Rowstore به عنوان b-tree در صورتی که فهرستی روی جدول وجود داشته باشد، یا اگر جدول فهرست نشده باشد به heap گفته می شود.

در زیر یک مثال ساده و مفهومی از یک جدول در قالب rowstore است که در آن کل ردیف داده ها، برای همه ستون ها، در یک صفحه در SQL Server ذخیره می شود:

برعکس، قالب ستون ستونی (به طور مفهومی) مقادیر متمایز هر ستون را در صفحات جداگانه در SQL Server ذخیره می کند:

به عنوان یک قاعده کلی ، فرمت RowStore برای بارهای کاری OLTP که در آن عملیات DML در معاملات فردی متمرکز است ، مناسب تر است. در مقابل ، فرمت ستون متناسب با بارهای تحلیلی است که با اسکن تعداد زیادی از ردیف ها نتایج پرس و جو کل را تولید می کند و فقط تعداد کمی از ستون ها را از جدول بازیابی می کند. با این حال ، این قانون انگشت شست می تواند برای شرایط بار کاری مخلوط شود.

مزایای فن آوری های ستونی

یکپارچه در سرور SQL. از آنجا که فن آوری های حافظه در موتور SQL Server یکپارچه شده اند ، یک پلتفرم جداگانه برای اجرای تخصصی لازم نیست. این امر باعث می شود که بار کار مختلط مقرون به صرفه تر باشد. ما می توانیم از ابزارها و مهارتهای آشنا برای مدیریت اشیاء ستون استفاده کنیم و معمولاً هیچ تغییر برنامه یا طراحی مجدد بانک اطلاعاتی لازم نیست. ما می توانیم برای مدیریت و تنظیم اشیاء ستونی ، از نمودارهای کاتالوگ سرور SQL استفاده کنیم.

ذخیره سازی کاهش یافتههمانطور که در تصاویر فوق نشان داده شده است ، ستون استور قادر به استفاده از مقادیر داده های مکرر در یک ستون است و آن مقادیر ستون مکرر را بطور اضافی ذخیره نمی کند. کاهش ذخیره مقادیر اضافی منجر به افزایش سطح فشرده سازی داده با استفاده از الگوریتم های Xvelocity می شود ، که به نیازهای ذخیره کمتری ، کاهش دیسک I/O ، بازدیدهای بیشتر حافظه پنهان و امید به زندگی بهتر می رسد. الگوریتم های فشرده سازی ستون برای ستون های کم کاردینالیت (مثال: نام دیسک از مثال فوق ، که تعداد کمی از مقادیر مجزا دارد) مؤثر است ، بر خلاف ستون های کاردینالیت بالا (مثال: ستون اندازه گیری از مثال فوق ، که در آن تعداد تعدادمقادیر منحصر به فرد زیاد است).

استفاده از حافظه منجر به فعالیت دیسک کمتر می شود. نتایج بازگشت از حافظه سریعتر از بازگشت نتایج از دیسک است. کاهش دیسک I/O ، که یک تنگنا رایج است ، منجر به بهبود IOP و توان می شود.

عملکرد پرس و جو بهینه شده برای نمایش داده های تحلیلی. شاخص های ستونی قادر به بهبود عملکرد پرس و جو تحلیلی با تکنیک هایی مانند:

• حذف ستون. از آنجا که داده ها از نظر جسمی در یک ساختار ستونی ذخیره می شوند ، فقط ستون هایی که در یک پرس و جو مورد ارجاع قرار می گیرند ، باید به آنها دسترسی پیدا کنند. این توانایی برای پرش از ستون هایی که در پرس و جو ذکر نشده اند با یک جدول سنتی Rowstore که همیشه مجبور به بازیابی یک ردیف کامل است ، تفاوت قابل توجهی دارد ، حتی اگر فقط یک یا دو ستون در یک پرس و جو درخواست شود. حذف ستون باعث می شود شاخص های ستون به ویژه برای نمایش داده های تحلیلی مناسب باشد که اغلب فقط چند ستون را از یک جدول بازیابی می کنند.
• حذف بخش. ستونی مقادیر حداقل/حداکثر را برای هر بخش مرتبط با یک ستون محاسبه و ذخیره می کند. این اجازه می دهد تا کل بخش ها پرش شوند که با محمول پرس و جو مطابقت نداشته باشد (یعنی عبارت WHERE). حذف بخش از نظر مفهومی شبیه به نحوه کار حذف پارتیشن است. با شروع SQL Server 2016 ، می توانیم یک شاخص استاندارد غیر خوشه ای (B-Tree) در کنار یک شاخص ستونی خوشه ای داشته باشیم که همچنین می تواند بر حذف بخش تأثیر بگذارد. اگر تکنیک ها برای بارگیری عمداً به یک ترتیب مرتب شده استفاده شوند ، حذف بخش می تواند حتی مؤثرتر باشد زیرا دامنه های حداقل/حداکثر با همپوشانی کمتری کوچکتر هستند.
• حالت دسته ای. برخی از اپراتورهای پرس و جو در یک حالت دسته ای ، به طور معمول در دسته های 900 ردیف ، انجام می دهند که باعث افزایش کارایی پرس و جو می شود. برای به دست آوردن حداکثر بهره از ستون در حالت دسته ای ، در مقابل سهواً به حالت ردیف (که در آن جمع ها یک ردیف در یک زمان انجام می شود) ،اپراتورهای حالت دسته ای پشتیبانی شده در هر نسخه از سرور SQL در MSDN ذکر شده است.
• فشار از مصالح و پیش بینی ها. بهینه ساز پرس و جو جمع آوری را پایین می آورد و به پایین ترین سطح ممکن پیش بینی می کند. این هدف از این امر به حداقل رساندن تعداد ردیف هایی است که از طریق عملیات پرس و جو منتقل می شوند.

مضرات فن آوری های ستون

از آنجایی که ویژگی های ستون از زمان معرفی در SQL Server 2012 در ابتدا تکامل یافته است ، این مضرات در حال کاهش است. تجارت برای دستیابی به مزایای ذکر شده در بخش قبلی شامل موارد زیر است:

نیازهای حافظه سرور. الزامات حافظه سرور مبتنی بر حجم داده ها ، اثربخشی فشرده سازی (به عنوان مثال ، کاردینالیت داده ، استفاده از حداقل انواع داده ها و غیره) است ، و اگر ستون در رابطه با جداول بهینه شده حافظه استفاده می شود. شاخص ستونی برای داشتن کاملاً حافظه در یک جدول مبتنی بر دیسک لازم نیست ، اما با یک جدول بهینه سازی شده حافظه قرار دارد.

سربار به روزرسانی ها و حذف ها. انعطاف پذیری با گزینه های بارگیری داده ها از زمان انتشار اولیه به طور قابل توجهی بهبود یافته است ، و تکنیک های (مانند Deltastore) برای بهینه سازی بارهای داده وجود دارد. با این حال ، هنوز هم صحیح است که ستون های ستون برای داده هایی که اغلب به روز نمی شوند (داده های "سرد" به جای داده های "داغ" از دیدگاه به روزرسانی) مناسب است.

محدودیت در داده ها ، پشتیبانی T-SQL و ویژگی ها. محدودیت های ستونی به طور قابل توجهی کمتر از SQL Server 2016 وجود دارد (به عنوان مثال ، در نسخه های اولیه یک فهرست ستون به روز نمی شود). هنوز هم محدودیت هایی در مورد انواع داده های مجاز و پشتیبانی شده (مانند هیچ ستون محاسبه شده) وجود دارد.

پیچیدگی تا حدودی اضافی. ذاتاً ، فن آوری های ستونی پیچیدگی بیشتری را به یک راه حل معرفی می کنند (هرچند که مطمئناً پیچیده تر از یک راه حل ذخیره سازی داده کاملاً جداگانه) است. برای استفاده کامل از رفتار ستونی (مثال: اندازه دسته ای بهینه و سفارش داده ها) ممکن است فرآیندهای بارگذاری داده ها نیاز به تنظیم داشته باشند. ممکن است برای دستیابی به عملکرد بهینه (مثال: حالت دسته ای) نیاز به تنظیمات داشته باشد.

مرجع اصطلاحات

ما این پست را با یک مرجع سریع اصطلاحات مربوط به فن آوری های ستون در SQL Server نتیجه خواهیم گرفت. به ترتیب حروف الفبا:

حالت دسته ای: اجرای مبتنی بر بردار برای پردازش پرس و جو از چند ردیف در دسته های 900. اجرای دسته ای به طور قابل توجهی بهتر از اجرای حالت ردیف است.

شاخص ستونی خوشه ای (CCI): نشان دهنده ذخیره فیزیکی برای کل جدول است که به عنوان ستون به جای فرمت RowStore ساخته شده است. قالب ستون گرا خود را به فشرده سازی قابل توجهی وام می دهد ، که به نوبه خود نیاز به ذخیره سازی را کاهش می دهد و عملکرد پرس و جو را به دلیل کاهش I/O بهبود می بخشد. مانند یک شاخص سنتی خوشه ای ، CCI کپی اصلی داده های داده است (به همین دلیل کلمه "خوشه دار" بخشی از نام آن است). با این حال ، اصطلاح CCI در واقع یک نادرست است زیرا هیچ مرتب سازی ذاتی از داده ها وجود ندارد (بسیار برخلاف یک شاخص خوشه ای سنتی). CCIS برای بارهای کاری انبارداری داده ، به ویژه برای جداول با بیش از 1 میلیون ردیف توصیه می شود.

ستون: داده هایی که از نظر جسمی در قالب ستونی ذخیره می شوند ، اما هنوز هم در ردیف ها و ستون ها به کاربر ارائه می شوند. یک صفحه داده داده ها را از یک ستون واحد ذخیره می کند ، که به مقادیر منحصر به فرد فشرده می شود ، که اساساً با ذخیره سنتی Rowstore متفاوت است.

Delete Buffer: ردیف هایی را که به طور منطقی از یک ستون حذف شده اند ، نگه می دارد. مطالب حاصل از بافر حذف از نتایج پرس و جو حذف می شود (اگرچه ردیف ها هنوز در ستون وجود دارند).

جدول مبتنی بر دیسک: ساختار جدول سنتی در سرور SQL که در آن ردیف داده ها در صفحات ذخیره می شوند.

پردازش تحلیلی تراکنش ترکیبی (HTAP): بستری که از دو نوع بار کار پشتیبانی می کند: پردازش معامله ای ، و همچنین نوع نمایش داده شد. هدف این است که "BI عملیاتی" در زمان واقعی را بر روی داده های منبع انجام دهید ، ضمن اینکه از حرکت داده ها و تکثیر داده ها در یک راه حل ذخیره سازی داده های ثانویه جلوگیری می کنید. HTAP هنگامی که می توان تجزیه و تحلیل را بر روی یک سیستم واحد انجام داد ، بدون نیاز به ادغام سیستم های متعدد ، مناسب است.

جدول بهینه سازی شده حافظه: یک فناوری مبتنی بر ردیف که در آن داده ها به عنوان ردیف های جداگانه و بدون ساختار صفحه سنتی ذخیره می شوند. فقدان صفحات به عدم قفل یا مسدود کردن تبدیل می شود ، اما هنوز هم از اسید کامل برخوردار است. جدول در حافظه قرار دارد و از روشهای ذخیره شده بومی برای دسترسی به داده های کارآمد قابل دسترسی است. برای جداول مشخص شده به عنوان بادوام ، یک نسخه دوم از داده های جدول از طریق پرونده های بازرسی روی دیسک نگهداری می شود. تفاوت های بی شماری در کنترل شاخص و پشتیبانی T-SQL برای جداول بهینه سازی شده حافظه در مقابل یک جدول سنتی مبتنی بر دیسک وجود دارد.

شاخص ستونی بدون خوشه ای (NCCI): یک شاخص ثانویه ستونی گرا در بالای یک جدول RowStore به منظور نمایش داده های تحلیلی که مستقیماً بر روی یک سیستم معامله (HTAP) اجرا می شود. بهینه ساز پرس و جو می تواند جدول RowStore را برای رضایت از نمایش داده های OLTP (مانند یک فرد جستجو) یا NCCI برای ارائه نمایش داده های تحلیلی (مانند اسکن دامنه) انتخاب کند. از آنجا که آنها در بالای جدول RowStore با استفاده از ستون های انتخابی مورد نیاز برای پشتیبانی از نمایش داده های تحلیلی ساخته شده اند ، NCCI ها نیازهای ذخیره سازی کلی را کاهش نمی دهند (نحوه انجام CCIS). کل هدف برای NCCI پیرامون بهبود عملکرد پرس و جو تحلیلی در یک سیستم OLTP است.

حالت ردیف: پردازش پرس و جو از هر ردیف یک بار. حالت ردیف نسبت به حالت دسته ای کندتر عمل می کند.

RowGroup: گروه هایی از ردیف های مرتبط با یک شاخص ستونی که به شکل ستونی فشرده شده اند. حداقل اندازه یک گروه ردیف 102،400 ردیف است. حداکثر اندازه یک گروه ردیف 1،048،576 ردیف است.

RowStore: داده هایی که در قالب سنتی ردیف گرا در یک صفحه داده ذخیره می شوند. یک Rowstore می تواند یک پشته از داده های بدون هماهنگ ، یک شاخص درخت B یا حتی یک جدول بهینه سازی شده حافظه باشد.

بخش: ستونی از داده ها در گروه Rowgroup که در کنار هم فشرده شده و در کنار هم ذخیره می شوند. نزدیک به مفهوم یک گروه RowGroup ، یک بخش می تواند تا 1،048،576 مقادیر متمایز را در خود جای دهد.

Tuple-Mover: یک فرایند پس زمینه که مدیریت فروشگاه Delta و Rowgroups را مدیریت می کند. این داده ها را از deltastore فشرده می کند ، آن را به شاخص ستون منتقل می کند و هنگام رسیدن به حداکثر اندازه ، RowGroup را می بندد.

ملیسا کیتس

ملیسا Coates یک معمار اطلاعات تجاری با Sentryone است. وی که در شارلوت ، کارولینای شمالی مستقر است ، در ارائه تجزیه و تحلیل ، انبارداری داده و راه حل های اطلاعاتی تجاری با استفاده از فن آوری های داخلی ، ابر و ترکیبی تخصص دارد. قبلاً یک CPA ، ملیسا به طرز مضحکی مفتخر است که یک گیک IT و کاملاً خوب است که یک MVP Platform Microsoft Data MVP باشد. هنگامی که ملیسا از صفحه کلید دور می شود ، احتمالاً می توانید او را با کولی مرزی خود ، شبانه روزی دست و پنجه نرم یا بازی در باغ پیدا کنید. ملیسا همچنین در sqlchick. com وبلاگ می نویسد.

این پست را به اشتراک بگذارید: فیس بوک LinkedIn Twitter