خانه / SQL / ایندکس گذاری آری یا خیر.

ایندکس گذاری آری یا خیر.

ایندکس گذاری آری یا خیر.

ایندکس گذاری در دیتابیس همیشه به عنوان جذابترین و جالب ترین بخش های کار محسوب شده؛ و اینطوری نمایان شده که با انجام عملی معجزه گر به Performance و velocity بسیار بالا و معجزه آسایی خواهیم رسید. اما نیک که بنگریم می بینیم که این پدیده معچزه گر گاهی اوقات بلای جان خودمان می شود و یا حداقل بر خلاف انتظارمان تاثیری دارد بسیار ناچیز. پس بد نیست به این پدیده و مفهوم پنهان و جذاب در سیستم های RDBMS نگاهی عمیق تر بندازیم.

ایندکس نیز مانند بسیار از مفاهیم دیگر دیتابیس یک ساختار داده!! است که به هدف تعیین محل سریع Fast Locating عنصر مورد نظر در کوئریهای مد نظر کاربر بدون پیمایش کامل جدول مورد نظر می باشد. در نبود ایندکس بهنگام کوئری گرفتن سیستم مدیریت دیتابیس مجبور می باشد که تمام row های جدول مورد نظر را سطر به سطر تا به آخر پیمایش کند به روش bubble sort معروف. در اینجا می توان یک یا چند سطر رو به عنوان مبنای ایندکس گذاری تعریف کرد. نکته ی مهم در اینجا ترتیب این سطر ها می باشد.(بر می گردیم به این نکته)

ایندکس شامل کپی از سطر(سطرهای) مورد نظر هست که عموما شامل آدرس بلاک دیسکی که row اصلی در جدول مورد نظر بر روی disk ذخیره شده است نیز می باشد. عموما برخی از RDBMS ها امکان بکارگیری یکسری expression ها رو بر روی ایندکس گذاری نیز می گذارند از جمله ایندکسی رو مثلا بر روی سطری از نوع رشته قرار دهید که بصورت lower case و یا uppercase تعریف شده باشد.

بدون حضور ایندکس عمل lockup بر روی دیتابیس با سرعت خطی o(n) می باشد که با تعداد بسیار زیاد دیتا این مورد می تونه بسیار بد باشد ولی با حضور ایندکس این عمل می توند بصورت sub linear  انجام شود. O(log(n)) البته در این مورد ساختار داده ای که برای ایندکس ها اتخاب می شود در این زمان نیمه خطی موثر است. B-tree معمولترین ساختاری ایست که در بسیاری از RDBMS های از جمله MS SQL Server و Oracle استفاده میشه و همونطور که می دونیم عملیات lockup در ساختار b-tree بصورت o(log(n)) می باشد.

 

از ایندکس همچنین می توان برای چک کردن موثر یکسری policyهای دیتابیسی از جمله Unique و Primary key و Foreign key و مواردی از این دست نیز استفاده نمود. به عنوان نمونه اگر ایندکسی policy ,unique رو نقض کند پس بی شک خود جدول اصلی نیز با این دستور افزودن یا ویرایش نیز نقض خواهد شد. دیتابیس ها بصورت Implicit ایندکس هایی رو بر روی Primary key ها اعمال می کنند که از آن هم برای Fast lockup که در بالا اشاره شد و هم برای Policy Checking استفاده می کنند.

اما بصورت کلی ما دونوع ایندکس داریم.

ایندکس های کلاستر و غیر کلاستر.

#None_Cluster_Index

در اینجا دیتا جدول اصلی بصورت غیر مرتب قرار می گیرد اما در ساختار داده ی ایندکس بصورت مرتب شده ذخیره خواهد شد. درخت ایندکس در اینجا بصورت مرتب شده می باشد و شامل دیتای مورد نظر از جدول اصلی و پوینتری به محل اصلی ذخیره سازی row مورد نظر بر روی دیسک می باشد. شامل شماره صفحه و شماره دیسک. در اینجا ترتیب داده ها در جدول اصلی و ساختار ایندکس مشابه نیست و از این ایندکس معمولا بر روی فیلدهای غیر اصلی که در کوئری های مهم استفاده خواهد شد استفاده می شود.

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

#Clustered_Index

در اینجا ترتیب داده ها در جدول اصلی نیز پس از هر عملیات ذخیره یا ویرایش عوض شده تا جدول اصلی نیز مرتب باشد.

همانطور که می بینیم در حضور ایندکس ها بخصوص ایندکس های غیر کلاستر دیتا همواره علاوه بر اینکه در جدول اصلی ذخیره خواهند شد بر روی index نیز باید ذخیره شوند؛ پس می بینیم که با حضور ایندکس های زیاد عملیات نوشتن با سرعت کندتری انجام خواهد شد. در غیر اینصورت می توان با تمام ترتیب های ممکن فیلدهای یک جدول ایندکس تعریف نمود و در نتیجه تمامی کوئری های ان جدول ب سرعت بسیار زیادی انجام می شدند!!! ولی زهی خیال باطل…

خوب همونطور که گفته شد می توان یک ایندکس رو بر روی چندین فیلد یک جدول تعریف نمود مثلا ایندکسی به ترتیب بر روی نام و شهر و نام خانوادگی یک جدول. در نتیجه ساختار داده این ایندکس بصورت نام-شهر-نام خانوادگی-پوینتر خواهد بود. این ایندکس کوئری هایی که به تریتب در خواست لیستی از نام؛ لیستی از نام و شهر و لیستی از نام و شهر و نام خانوادگی را داشته باشند بسیار خوب عمل می کنند؛ اما نکته ی بسیار مهم و ریز در اینجا وجود دارد که اهمیت بسیار زیاد ترتیب فیلدهای ایندکس رو مشخص می کنند که می توانند این ایندکس ایجاد شده را بسیار ناکارآمد کند.

فرض نمائید اکثر کوئری های ما شامل لیستی از نام-نام خانوادگی-شهر باشد؛ در اینجا این ایندکس تعریف شده نمی تواند کارایی بالا و موثری داشته باشد. چون بر روی این ایندکس ابتدا به پیدا کردن نام مورد نظر می گردد و بعد از ان چون در کوئری ما نام خانوادگی آمده است و نه شهر RDBMS به ناچار باید کل ایندکس رو به روش Bubble sort پیمایش نمائید که می بینیم در سناریو هایی که داده های ما بسیار زیاد هستند و دارای ارتباطات پیچیده ای می باشند این ایندکس تعریف شده عملا کارایی مناسبی را نمی تواند داشته باشد.

پس تمامی ایندکس ها باید متناسب با کوئری هایی باشد که application نیاز دارد.

اکثر RDBMS ها به کاربر این امکان را می دهند که برای کوئری های خود بصورت دستی استفاده از index مناسب و مورد نظر خود را داشته باشند از جمله SQL Server و Oracle. پس می توان دو ایندکس در اینجا تعریف کرد یک ایندکس برای کوئری هایی که بیشتر استفاده می شود می باشد و برای کوئری خاص خود می توان از آن ایندکسی که متناسب تر و مناسب تر است استفاده نمود.

استفاده از ایندکس ها در viewها نیز از موارد بسیار مهم و اساسی است که بسیاری از RDBMS ها از آن پشتیبانی می کنند که می تواند در موارد یکه view ما ساختار پیچیده ای دارد و شامل relation ها ی زیادی بوده و سربار زیادی دارد بسیار مفید واقع شود.

 

Trying to be Agile…

Masoud Bahrami

درباره ی masoud@admin

پاسخ دهید

نشانی ایمیل شما منتشر نخواهد شد. بخش‌های موردنیاز علامت‌گذاری شده‌اند *

در تلگرام هم همراه شما هستم

اگر علاقمند به معماری نرم افزار و مبحث محبوب مایکروسرویس هستید؛ در کانال با ما همراه باشید. اطلاعات مفید زیادی در این کانال انتظار شما را می کشند. فقط کافیست دکمه ی پیوستن را بفشارید.

پیوستن بستن