مقدمه
بدون شک مفهوم ایندکس index در SQL Server مهمترین و گسترده ترین موضوع در زمینه بهبود کارایی و افزایش Performance در دیتابیس است. در این مقاله سعی کردیم 10 پرسش مهم در این زمینه رو مطرح و به اونها جواب بدیم. با مطرح کردن این پرسش که ایندکس چیست و چه اهمیت در جداول پایگاه داده دارد، عمیق تر به کاربرد اون بپردازیم.
روزگاری، متداول ترین مفهوم ایندکس به کتاب های راهنما و دیکشنری ها مربوط می شد. امروزه با وجود منابع آنلاین و امکان جستجو در موتورهای جستجوگر، مفهوم ایندکس کاربرد عمومی خود را از دست داده. به طور مثال در دیکشنری‌های جیبی که سابقا پرکاربرد بود، برای یافتن یک کلمه، نخست به فهرست دیکشنری مراجعه میکردیم و بر اساس حروف الفبا کلمه مورد نظر را یافته و در نهایت آدرس محل قرار گیری کلمه در کتاب رو که در واقع همان شماره صفحه (Page number) بود استخراج می کردیم.
درواقع بدون اینکه نیاز باشه از صفحه اول دیکشنری شروع کنیم و ورق یه ورق جستجو کنیم مستقیما سراغ صفحه مورد نظر می رفتیم و دنبال کلمه می گشتیم. تازه همیشه هم غر میزدیم که پیدا کردن یک کلمه در فهرست و سپس یافتن آن در صفحه مورد نظر سخت و زمانبر است !
حالا فرض کنید دیکشنری ها به نحوه دیگری نوشته شده بودند، فهرستی وجود نداشت و کلمات بر اساس حروف الفبا مرتب نشده بود، چند صفحه باید جستجو می شد ؟ چه تعداد کلمه باید استخراج و چک می شد ؟ هزینه اینکار چقدر بود (زمان و انرژی که شما صرف یافتن یک کلمه می کردید )
براحتی تمامی مفاهیم گفته شده در بالا رو می‌توان برای جستجو دیتا موجود در جداول پایگاه داده در نظر گرفت و مفاهیمی مثل ایندکس index در SQL Server ، هزینه استخراج دیتا IO، هزینه پردازش CPU Time و Performance رو به وجود آورد.
پایگاه داده تمامی دیتاها در تمامی فایل های خود را در صفحه هایی ( page ) با اندازه 8KB ذخیره می کند. حداقل دو فایل برای هر دیتابیس وجود دارد، یکی برای ذخیره سازی دیتا، با فرمت پیش فرض mdf. و یکی هم برای نگهداری لاگ ( log ) با فرمت پیش فرض ldf.
هر جدول در دیتابیس حداقل یک page برای خود دارد، برای مدیریت این page ها SQL Server از مجموعه خاصی از page ها استفاده می کند، با نام IAM pages) Index Allocation Map) . البته این دسته page ها برای جداول بدون ایندکس (heap table) هم بکار برده می شوند .
مفهوم heap چیزی شبیه برگه های چک نویسی خواهد بود که داده ها با همان ترتیبی که منتقل شده اند درج می گردند (ترتیب زمانی) ، در ساختار heap هیچ چیز مرتب شده نیست نه ترتیب درج دیتا در page ها و نه ترتیب قرار گیری پیج ها.
در جدول heap، تنها راه یافتن دیتا خواندن تمام page ها و جستجو از ابتدا تا انتهای جدول است، برای جداول بزرگ این می تونه یک فاجعه باشه و هزینه بالایی رو به سرور تحمیل کنه.
جدولی با ساختار کوئری ایجاد شده حالا با کمک ابزار ApexSQL دیتا نمونه ای برای آن تولید می کنیم.
کوئری Select را اجرا می کنیم :
SELECT * FROM Customers where CustomerID = 50000
در صورتی که estimated execution plan مربوط به کوئری را چک کنیم :
میبینیم که SQL در این حالت از عملگر table scan استفاده کرده، به این معنی که مجبوره تمام رکوردهای جدول رو بخونه تا به CustomerId با شماره 50000 برسه، به جزئیات زیر توجه کنید :
چرا Estimated rows = 1! حدسی زده شده ؟
این به این معناس که SQL Server انتظار داشته فقط یک مشتری با این شماره وجود داشته باشه، تنها یک راه برای فهمیدن صحت این ادعا وجود دارد ، اجرا کوئری :
میبینیم که تعداد 2506 logical read انجام شده، برای هر page یکی. همچنین به همین تعداد هم read-ahead انجام شده است . اما این عدد چی بخشی از کل page های جدول است ؟
کوئری زیر بما کمک می کنه تا بفهمیم دیتا جدول شامل چند page است :
نتیجه اجرا کوئری :
همانطور که میبینید SQL Server فقط یک page کمتر از تعداد کل page های مربوط به جدول رو خوند. میتونیم فرض کنیم در حالت متوسط SQL نیاز داره نیمی از صفحات رو بخونه تا دیتا رو پیدا کنه.
اما اگر جدولمون بخشی از یک سیستم OLTP با حجم تراکنش بالا و میلیون ها رکورد باشه که همزمان هزاران جستجو روش درحال پیاده شدنه حتی اگر انقدر منابع وجود داشته باشه که کل جدول رو بتونین توی buffer pool نگهدارین، تصور اینکه همچین بلایی سر سرور دیتابیس بیاریم تا فقط یک رکورد رو پیدا کنیم، یک فاجعه است و میشه به اهمیت نقش ایندکس index در SQL Server پی برد.
بهترین دسته بندی ایندکس ها رو میتونیم مطابق با داکومنت مایکروسافت ببینیم :
Clustered
A clustered index sorts and stores the data rows of the table or view in order based on the index key. This type of index is implemented as a B-tree structure that supports fast retrieval of the rows, based on their key values.
Nonclustered
A nonclustered index can be defined on a table or view with a clustered index or on a heap. Each row in the index contains the key value and a row locator. This locator points to the data row in the clustered index or heap having the key value. The rows in the index are stored in the order of the key values, but the data rows are not guaranteed to be in any particular order unless they are in a clustered index.
Unique
A unique index ensures that the key contains no duplicate values and therefore every row in the table or view is in some way unique.
Index with included columns
A nonclustered index that is extended to include nonkey columns in addition to the key columns.
Full-text
A special type of token-based functional index that is built and maintained by the Microsoft Full-Text Engine for SQL Server. It provides efficient support for sophisticated word searches in character string data.
Spatial
A spatial index provides the ability to perform certain operations more efficiently on spatial objects (spatial data) in a column of the geometry data type. The spatial index reduces the number of objects on which relatively costly spatial operations need to be applied.
Filtered
An optimized nonclustered index, especially suited to cover queries that select from a well-defined subset of data. It uses a filter predicate to index a portion of rows in the table. A well-designed filtered index can improve query performance, reduce index maintenance costs, and reduce index storage costs compared with full-table indexes.
XML
A shredded, and persisted, representation of the XML binary large objects (BLOBs) in the xml data type column.
مفاهیم Full-text و Spatial و همچنین XML خارج از بحث این مقاله هستند، بیشتر مفهوم cluster و non-cluster رو مورد بررسی قرار خواهیم داد.
نکته : این مطلب شامل نسخه SQL Sever 2016 هست و به نوع Columnstore index به طور کلی اشاره نکرده است، در مقاله های مربوط به قابلیت های SQl Server 2019 به این موضوع اشاره کرده ایم .
در جدول شرح داده شده که کلاستر ایندکس در واقع در نحوه ذخیره سازی دیتا اثرگذار است، در ساختار heap دیتا بدون هیچ ساختار مرتب شده ای ذخیره میشه، این ساختار کمترین هزینه رو هنگام ذخیره سازی دیتا داره اما در ساختار کلاستر، هنگامی که یک ایندکس کلاستر بر روی جدول heap ایجاد میشه، ساختار ذخیره سازی دیتا کاملا تغییر میکنه و حالا دیگه بر اساس کلید ( key ) مرتب شده است.
ایندکس بر اساس مفهوم B-tree ( به مفهوم درخت بالانس ) با این مدل که نود های برگ شامل page های دیتا هستند و سطوح بالاتر جهت نگهداری کلید‌ها، به کار می روند.
در ایندکس های non-cluster ساختار ذخیره سازی دیتا تغییر نمی‌کنه اما یک B-tree جدید برای این ایندکس ایجاد میشه.
همانطور که گفتیم وقتی ایندکس کلاستر ایجاد میشه ترتیب ذخیره سازی page ها تغییر میکنه، این موضوع رو با یه مثال بررسی میکنیم:
CREATE CLUSTERED INDEX CIX_Customers_CustomerID
ON dbo.Customers (CustomerID)
خب برای ساخت ایندکس index در SQL Server چه عملیات هایی انجام میشه :
1- خواندن تمام page های جدول
2- مرتب سازی تمام رکوردها بر اساس کلید اعلام شده
3- پرکردن page های جدید با رکوردهای مرتب شده
4- ذخیره سازی page های جدید در دیسک
5- آزاد سازی page های قبلی
در کوئری پلن ساخت ایندکس مشاهده میکنیم :
اگه تصویر بالا رو از راست به چپ بخونیم شامل، یک Table scan هست که تمام جدول رو میخونه، 100.000 رکورد میرن به مرحله مرتب سازی ، بعد از مرتب شدن وارد مرحله درج دیتا ایندکس میشه ، که در واقع node های ایندکس رو می سازه، خب حالا ما ایندکس کلاستر در جدولمون داریم و نتیجه رو میتونیم چک کنیم :
SELECT * FROM Customers where CustomerID = 50000
با چنین کوئری پلنی :
همانطور که میبینیم اینبار Table scan انجام نمیشه و با عملیات clusterde index seek جایگزین شده که باعث کاهش هزینه I/O میشه.
یک اصلاح بزرگ اتفاق افتاد، تعداد 2500 read به فقط سه مورد کاهش یافت، با توجه به اینکه می‌دونیم دیتا مورد نظر فقط در یک page ذخیره شده میشه فهمید که دو page دیگه مربوط به درخت ایندکس و جستجو در اون برای پیدا کردن node page هست.
SELECT INDEXPROPERTY(OBJECT_ID(‘dbo.Customers’)
,‘CIX_Customers_CustomerID’,‘IndexDepth’) AS [Index Depth]
نتیجه حاصل از اجرا کوئری بالا :
پس به صورت گرافیکی ساختار ایندکس ما به این صورت خواهد بود :
تا اینجا ما بدنبال مشتری با ID آن بودیم، اگر مشتری را بر اساس نام سرچ کنیم چه اتفاقی می افتد ؟
چیزی شبیه به این کوئری :
SELECT * FROM Customers WHERE LastName = ‘Myers’ AND FirstName = ‘Kaitlyn’
پلن کوئری :
باز برگشتیم به Scan (البته کلاستر ایندکس اسکن و نه table scan) همونطور که قبلا اشاره کردیم یک جدول یا میتونه heap باشه یا clustered ، که خب ما ساختار جدول رو به کلاستر تغییر داده بودیم.
نگاهی به I/O این کوئری بندازیم :
دلیل اینکه logical read زیاد شده و برابر با 2550 هست اینکه SQL Server برای اینکه مشتری رو بر اساس اسم پیدا کنه ، همانطور که میدونم کلید ایندکس id بود و نه name لذا تمام Node های برگ ایندکس رو باید اسکن کنه .
اجازه بدین با کمک این DDL یه ایندکس جدید درست کنیم :
CREATE NONCLUSTERED INDEX IX_Customers_LastName_FirstNameON dbo.Customers (LastName, FirstName)
خب حالا کوئری رو اجرا میکنیم تا ببینیم چه اتفاقی افتاد :
میبینیم که داره از یک index seek به همراه یه lookup استفاده میکنه ، دلیل وجود lookup اینکه ما Select * نوشتیم .
SELECT * FROM Customers …
و SQL Server ناچاره برگرده سراغ دیتا page ها تا تمام ستون ها رو استخراج کنه. ساختار ایندکس noncluster اینجوریه که تو سطح page های برگ هیچ دیتا page ی نداره و توسط یک سری pointer داره به دیتا page های ایندکس کلاستر اشاره میکنه ( ساختار اصلی دیتا )
و این هم هزینه کوئری پس از ایجاد ایندکس noncluster :
دیدیم که کوئری از ایندکس nonclustered استفاده کرد و lookup زد به ایندکس کلاستر تا مابقی ستون ها رو استخراج کنه. در صورتی که نیاز باشه به صورت متناوب، بخش خاصی از دیگر ستون ها رو توی کوئریمون استخراج کنیم ، مثلا هربار که دنبال مشتری با نام مشتری هستیم شماره تلفن مشتری رو هم لازم داشته باشیم، اینجاست که included column ها معنی پیدا میکنه.
این ایندکس رو می سازیم :
CREATE NONCLUSTERED INDEX IX_Customers_LastName_FirstName ON dbo.Customers (LastName, FirstName) INCLUDE(HomePhone)
وبعد کوئری زیر رو امتحان میکنیم :
SELECT HomePhone FROM Customers
WHERE LastName = ‘Myers’ AND FirstName = ‘Kaitlyn’
مدل execution plan میشه :
بدون هیچ key lookup و با کاهش I/O :
سوالی که به صورت طبیعی مطرح میشه اینکه چرا ستون شماره تلفن رو به بخش اول ایندکس اضافه نکردیم ؟
دلیلش اینکه ما یک nonclustered ایندکس ساختیم که شامل کلید های نام و نام خانوادگیه و نیازی به این که شماره تلفن هم در کلید های ایندکس باشه نداشتیم، چون جستجو ای که انجام دادیم ( بخش where کوئری ) کاری با شماره تلفن نداشت. فقط نیاز بود تا بعد از پیدا کردن کلیدمون بتونیم شماره تلفن رو هم مستقیما از روی ایندکس استخراج کنیم و دیگه سراغ کلاستر ایندکس یا همون جدولمون نریم. به این حالت که ایندکس تمام ستون های مورد نیاز کوئری رو پوشش میده، covering index می‌گیم.
این مورد رو هم یادمون باشه که میشه nonclustered ایندکس ها رو بدون کلاستر ایندکس برروی جدول ساخت ، در واقع یک ساختار heap خواهیم داشت به همراه nonclustered ایندکس که هر بار ایندکس نیاز داره RID lookup بزنه به data page های جدول heap.
معمولا سعی کردیم از این سوال فرار کنیم، خیلی وقت ها هم مفهوم کلاستر ایندکس و Primary key رو یکی در نظر گرفتیم، اما اینطور نیست!
در جداول رابطه ای – relational database – RDBMS باید کلید وجود داشته باشه، گاهی شامل یک ستون و گاهی هم شامل مجموعه ای از ستون ها که یک کلید یکتا رو تشکلیل میدن -unique key که معمولا به کمک ایندکس index در SQL Server پشتیبانی میشن اما لزوما همیشه در کنار هم قرار نمیگیرن.
به مثال زیر توجه کنین :
ALTER TABLE dbo.Customers ADD CONSTRAINT PK_Customers_CustomerID PRIMARY KEY (CustomerID)
ما در جدولمون هم کلاستر ایندکس داشتم و هم nonclustered ایندکس و حالا pk رو هم اضافه کردیم :
همونطور که میبینیم ساختن PK باعث ایجاد یک ایندکس شد، اون هم از نوع nonclustered .
اگه تاحالا فکر میکردین مفهموم PK و کلاستر ایندکس یکی هست حالا وقتشه که تو تصورات خودتون تجدید نظر کنین. وجود Primary key برای جداول رابطه ای ضروریه و باعث میشه ارتباط بین جداول و مفاهیم PK , FK در دیتابیس شکل بگیره.
به طور پیش فرض SQL Server در هنگام ساخت جدول با primary key یک کلاستر ایندکس با همون کلید در نظر گرفته شده برای PK میسازه.
CREATE TABLE PrimaryKey (id INT IDENTITY PRIMARY KEY,name VARCHAR(50) )
این دستور جدول با یک PK و یک ایندکس کلاستر ایجاد کرد :
فقط به این دلیل که این عملکرد پیش فرض SQL Server هست و معنیش این نیست که همیشه این روش بهترین کاره، چون PK نیاز داره تا توسط یک ایندکس پشتیبانی بشه لزوما اون ایندکس نباید از نوع کلاستر باشه. گاهی پیش میاد که کاربرد PK با ایندکس کلاستر هم راستا نباشه.
primary key این امکان رو به جدول میده تا از duplicate در ستون کلید اصلی جدول جلوگیری کنه .
برگردیم به مثال جدولمون و اینبار کوئری های زیر رو روش اجرا کنیم :
CREATE CLUSTERED INDEX IX_Customers_LastName_FirstName
ON dbo.Customers(LastName, FirstName)
ALTER TABLE dbo.Customers ADD CONSTRAINT PK_Customers_CustomerID
PRIMARY KEY ONCLUSTERED(CustomerID)
میبینیم که یک ایندکس کلاستر برای جدولمون برروی نام و نام خانوادگی ساختیم، اما همزمان نیاز داشتیم که CustomerID یکتا باشه پس برروی اون PK ساختیم.
این حالت وقتی مفیده که ما بدونیم بیشترین جستجو در این جدول با نام و نام خانوادگی مشتری هست و نه با ID اون، پس در یک تصمیم هوشمندانه ساختار جدول رو بر اساس مهمترین فاکتور های جستجو ایجاد کردیم و در ضمن با ساختن PK از Duplicate روی ستون CustomerID هم جلوگیری کردیم.
گاهی پیش میاد که در قسمت بالای کوئری پلن پیغامی از طرف SQL Server دریافت کنیم :
این متن سبز رنگ بیان میکنه که اگر ایندکس nonclustered بر روی CustomerID ایجاد بشه میتونه تا 99.814 درصد، هزینه اجرای کوئری رو کاهش بده، چون بجای Table scan به سراغ index seek خواهد رفت.
اگر شما این کوئری را سالی یکبار اجرا میکنین و 10 دقیقه هم زمان میبره ، باید بگیم این ایندکس رو نسازین، اما اگر روزی چندبار اجرا میشه، حتما این ایندکس رو بسازین.
توصیه ای وجود داره که میگه برای هر column در جداول دیتابیس مون باید ایندکس داشته باشیم و هیچ کوئری وجود نداشته باشه که براش missing index داشته باشیم.
همون جدول مشتری مثالمون رو در نظر بگیرید اگر ما برای تمام ستون ها ایندکس ساخته باشیم و کوئری زیر رو اجرا کنیم :
INSERT INTO
[dbo].[Customers] ([FirstName],[LastName],[Street],[StreetNumber],[Unit],[City],[StateProvince],[ISO3_Country],[EmailAddress],[HomePhone],[MobilePhone])
VALUES(‘Ford’,‘Prefect’,‘The Resraurant’,’42’,‘N/a’,‘End of the Universe’,‘Improbable’,‘FPP’,‘Ford@HeartOfGold.com’,2125551212,3141592653)
GO
چنین کوئری پلنی خواهیم داشت :
هزینه درج دیتا – insert بخاطر ساخت تعداد زیادی ایندکس افزایش پیدا کرد، پس در صورتی که دارین با جداول با تراکنش های بالای درج و بروزرسانی دیتا کار می کنید، کاملا در ساخت ایندکس احتیاط کنید، اما اگه جدول از نوع static table هست مثلا جدول تقویم یا هر نوع دیتا پایه ای که قرار نیست تغییر متناوبی توش اتفاق بیوفته ، تا جای که فکر میکنید نیازه ایندکس بسازین.
ساخت و نگهداری ایندکس index در SQL Server ، مهمترین عامل داشتن دیتابیس با کارایی -performance مناسب هست. اما همیشه فاکتور های زیادی در تصمیم گیری برای مدیریت ایندکس ها موثره و بدون شناخت کامل از business سازمان و رفتار کاربران اون سیستم نمیشه تصمیم قطعی و درستی گرفت. این تصمیم گیری ها فقط با مشاهدات دوره ای جداول و در نظر داشتن تغییر ایجاد شده که معمولا توسط برنامه نویس ها اتفاق میوفته امکان پذیره.
در این مقاله سعی کردیم به سوالات مهم ایندکس index در SQL Server جواب بدیم، مسلما هر یک از سوالات مطرح شده میتونه عنوان مقاله مستقلی باشه و خیلی بیشتر درمورد اونها بحث بشه. همچنین موارد زیادی از جمله Columnstore ایندکس ها باقی موند که بهشون نپرداختیم.
با ما همراه باشید تا به کمک هم عمیق تر به مسائل مربوط به Performance در SQL Server بپردازیم.
منبع :
