نگاهی عمیق به نمایه سازی پایگاه داده

کوشیک نات

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

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

در اصطلاحات ساده ، یک فهرست کلیدهای جستجو در مورد داده های مربوط به دیسک با استفاده از ساختار داده های مختلف حافظه و روی دیسک. Index با کاهش تعداد سوابق برای جستجوی ، برای سریع تر جستجو استفاده می شود.

بیشتر یک فهرست در ستون های مشخص شده در بند یک پرس و جو ایجاد می شود زیرا بانک اطلاعاتی داده ها را از جداول بر اساس آن ستون ها بازیابی و فیلتر می کند. اگر یک شاخص ایجاد نکنید ، پایگاه داده تمام ردیف ها را اسکن می کند ، ردیف های تطبیق یافته را فیلتر می کند و نتیجه را برمی گرداند. با میلیون ها سوابق ، این عملیات اسکن ممکن است ثانیه ها طول بکشد و این زمان پاسخ بالا باعث می شود API ها و برنامه ها کندتر و غیرقابل استفاده شوند. بیایید مثالی ببینیم -

ما از MySQL با یک موتور پیش فرض DINODB استفاده خواهیم کرد ، اگرچه مفاهیمی که در این مقاله توضیح داده شده اند کم و بیش در سایر سرورهای پایگاه داده و همچنین مانند Oracle ، MSSQL و غیره یکسان هستند.

یک جدول به نام index_demo با طرح زیر ایجاد کنید:

چگونه می توانیم تأیید کنیم که از موتور InnoDB استفاده می کنیم؟

دستور زیر را اجرا کنید:

ستون موتور در شات صفحه نمایش فوقانی را نشان می دهد که برای ایجاد جدول استفاده می شود. در اینجا از InnoDB استفاده می شود.

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

من تاکنون هیچ شاخصی در این جدول ایجاد نکرده ام. بیایید این را با دستور تأیید کنیم: فهرست نمایش. 0 نتیجه را برمی گرداند.

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

توضیح دهید که چگونه موتور پرس و جو قصد دارد پرس و جو را اجرا کند. در تصویر بالا ، می بینید که ستون Rows 5 & act_keys را برمی گرداند. keys inky_keys نشان دهنده آنچه همه شاخص های موجود در آنجا وجود دارد که می تواند در این پرس و جو استفاده شود. ستون کلیدی نشان می دهد که کدام شاخص در واقع از تمام شاخص های ممکن در این پرس و جو استفاده می شود.

کلید اصلی:

پرس و جو فوق بسیار ناکارآمد است. بیایید این پرس و جو را بهینه کنیم. ما ستون phone_no را به یک کلید اصلی تبدیل خواهیم کرد که با فرض اینکه هیچ دو کاربر نمی توانند در سیستم ما با همان شماره تلفن وجود داشته باشند. هنگام ایجاد یک کلید اصلی موارد زیر را در نظر بگیرید:

• یک کلید اصلی باید بخشی از بسیاری از نمایش داده های حیاتی در برنامه شما باشد.
• کلید اصلی محدودیتی است که منحصر به فرد هر ردیف را در یک جدول مشخص می کند. اگر چندین ستون بخشی از کلید اصلی باشد ، این ترکیب باید برای هر سطر بی نظیر باشد.
• کلید اصلی باید غیر تهی باشد. هرگز کلید اصلی خود را NULL FIELDS نکنید. طبق استانداردهای ANSI SQL ، کلیدهای اولیه باید با یکدیگر قابل مقایسه باشند ، و مطمئناً باید بگویید که آیا مقدار ستون اصلی کلید برای یک ردیف خاص بیشتر ، کوچکتر یا برابر با همان از ردیف دیگر است. از آنجا که NULL به معنای یک مقدار تعریف نشده در استانداردهای SQL است ، شما نمی توانید به طور قطعی NULL را با هر مقدار دیگر مقایسه کنید ، بنابراین از نظر منطقی NULL مجاز نیست.
• نوع کلید اصلی ایده آل باید عددی مانند Int یا Bigint باشد زیرا مقایسه عدد صحیح سریعتر است ، بنابراین عبور از این فهرست بسیار سریع خواهد بود.

غالباً ما یک قسمت شناسه را به عنوان افزایش خودکار جداول تعریف می کنیم و از آن به عنوان یک کلید اصلی استفاده می کنیم ، اما انتخاب یک کلید اصلی به توسعه دهندگان بستگی دارد.

اگر خودتان کلید اصلی ایجاد نکنید ، چه می کنید؟

ایجاد یک کلید اصلی خودتان اجباری نیست. اگر هیچ کلید اصلی را تعریف نکرده اید ، InnODB به طور ضمنی یکی را برای شما ایجاد می کند زیرا InnoDB با طراحی باید در هر جدول دارای یک کلید اصلی باشد. بنابراین پس از ایجاد یک کلید اصلی بعداً برای آن جدول ، InnoDB کلید اصلی تعریف شده قبلی را حذف می کند.

از آنجایی که ما هیچ کلید اصلی تعریف نشده از هم اکنون نداریم ، بیایید ببینیم چه InnODB به طور پیش فرض برای ما ایجاد شده است:

گسترش تمام شاخص هایی که توسط کاربر قابل استفاده نیستند اما کاملاً توسط MySQL اداره می شوند.

در اینجا می بینیم که MySQL یک شاخص کامپوزیت را تعریف کرده است (بعداً در مورد شاخص های کامپوزیت بحث خواهیم کرد) در DB_ROW_ID ، DB_TRX_ID ، DB_ROLL_PTR و همه ستون های تعریف شده در جدول. در صورت عدم وجود کلید اصلی تعریف شده توسط کاربر ، از این فهرست برای یافتن سوابق منحصر به فرد استفاده می شود.

تفاوت بین کلید و فهرست چیست؟

اگرچه از اصطلاحات کلید و شاخص به صورت متناوب استفاده می شود ، کلید به معنای محدودیتی است که بر رفتار ستون تحمیل می شود. در این حالت ، محدودیت این است که کلید اصلی زمینه غیر قابل تهی است که به طور منحصر به فرد هر ردیف را مشخص می کند. از طرف دیگر ، ایندکس یک ساختار داده ویژه است که جستجوی داده ها را در سراسر جدول تسهیل می کند.

اکنون بیایید شاخص اصلی را از طریق phone_no ایجاد کنیم و فهرست ایجاد شده را بررسی کنیم:

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

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

جدول: جدول که روی آن شاخص ایجاد شده است.

non_unique: اگر مقدار 1 باشد ، شاخص منحصر به فرد نیست ، اگر مقدار 0 باشد ، شاخص منحصر به فرد است.

key_name: نام فهرست ایجاد شده. نام شاخص اصلی همیشه در MySQL اصلی است ، صرف نظر از این که اگر در هنگام ایجاد شاخص ، نام شاخص ارائه داده اید یا خیر.

SEQ_IN_INDEX: شماره دنباله ستون در فهرست. اگر چندین ستون بخشی از فهرست باشد ، شماره دنباله بر اساس نحوه سفارش ستون ها در زمان ایجاد شاخص اختصاص می یابد. شماره دنباله از 1 شروع می شود.

جمع بندی: نحوه طبقه بندی ستون در فهرست. به معنای صعودی ، D به معنای نزولی ، تهی به معنای مرتب نشده است.

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

sub_part: پیشوند فهرست. اگر کل ستون فهرست بندی شود ، تهی است. در غیر این صورت ، در صورتی که ستون تا حدی فهرست بندی شود ، تعداد بایت های ایندکس شده را نشان می دهد. بعداً شاخص جزئی را تعریف خواهیم کرد.

بسته بندی شده: نحوه بسته بندی کلید را نشان می دهد. اگر اینگونه نباشد تهی.

NULL: بله اگر ستون ممکن است حاوی مقادیر تهی و خالی باشد.

index_type: نشان می دهد که از ساختار داده نمایه سازی برای این فهرست استفاده می شود. برخی از نامزدهای احتمالی عبارتند از: BTREE ، HASH ، RTREE یا FULLTEXT.

نظر: اطلاعات مربوط به شاخصی که در ستون خود توضیح داده نشده است.

index_comment: نظر برای فهرست مشخص شده هنگام ایجاد فهرست با ویژگی Comment.

حال بیایید ببینیم که آیا این شاخص تعداد ردیف هایی را که برای یک phone_no داده شده در بند یک پرس و جو جستجو می شود ، کاهش می دهد.

در این عکس فوری ، توجه داشته باشید که ستون ردیف ها فقط 1 را برگردانده است ، action_keys & key هر دو بازگشت اولیه است. بنابراین اساساً به این معنی است که استفاده از شاخص اصلی به عنوان اولیه (نام به صورت خودکار هنگام ایجاد کلید اصلی اختصاص می یابد) ، بهینه ساز پرس و جو فقط به رکورد می رود و آن را واکشی می کند. بسیار کارآمد استاین دقیقاً همان چیزی است که یک شاخص برای آن است - برای به حداقل رساندن دامنه جستجو با هزینه فضای اضافی.

فهرست خوشه ای:

یک شاخص خوشه ای با داده ها در همان فضای جدول یا همان پرونده دیسک جمع می شود. می توانید در نظر بگیرید که یک شاخص خوشه ای یک شاخص B درخت است که گره های برگ آن بلوک های داده واقعی روی دیسک هستند ، زیرا فهرست و داده ها در کنار هم قرار دارند. این نوع شاخص از نظر فیزیکی داده ها را بر اساس ترتیب منطقی کلید شاخص سازماندهی می کند.

سازمان داده های فیزیکی به چه معنی است؟

از نظر جسمی ، داده ها بر روی دیسک در هزاران یا میلیون ها بلوک دیسک / داده سازماندهی می شوند. برای یک شاخص خوشه ای ، اجباری نیست که تمام بلوک های دیسک به طور مسری ذخیره شوند. بلوک های داده فیزیکی همه زمان ها در اینجا و در آنجا توسط OS در هر زمان لازم است. یک سیستم پایگاه داده هیچ کنترل مطلق بر نحوه مدیریت فضای داده های فیزیکی ندارد ، اما در داخل یک بلوک داده ، سوابق را می توان به ترتیب منطقی کلید فهرست ذخیره یا مدیریت کرد. نمودار ساده شده زیر آن را توضیح می دهد:

• مستطیل بزرگ با رنگ زرد یک بلوک / بلوک داده دیسک را نشان می دهد
• مستطیل های رنگی آبی نشان دهنده داده های ذخیره شده به عنوان ردیف های داخل آن بلوک است
• منطقه پاورقی نمایانگر شاخص بلوک است که مستطیل های کوچک با رنگ قرمز به ترتیب مرتب شده از یک کلید خاص ساکن هستند. این بلوک های کوچک چیزی جز نوع نشانگرهایی نیستند که به جبران سوابق اشاره می کنند.

سوابق به هر ترتیب دلخواه در بلوک دیسک ذخیره می شوند. هر زمان که سوابق جدیدی اضافه شود ، در فضای بعدی موجود اضافه می شوند. هر زمان که یک رکورد موجود به روز شود ، سیستم عامل تصمیم می گیرد که آیا این رکورد هنوز هم می تواند در همان موقعیت قرار بگیرد یا موقعیت جدیدی باید برای آن رکورد اختصاص یابد.

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

به این ترتیب ، شما واقعاً نیازی به اهمیت دادن به سازماندهی سوابق فیزیکی به یک ترتیب خاص ندارید ، بلکه یک بخش کوچک در آن به ترتیب حفظ می شود و واکشی یا حفظ سوابق بسیار آسان می شود.

مزیت شاخص خوشه ای:

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

هنگام اجرای پرس و جو ، یک بلوک داده در حافظه بدست می آید. بگویید بلوک داده شامل PHONE_NO در محدوده 9010000000 تا 9030000000 است. بنابراین هر محدوده ای که در پرس و جو درخواست کرده اید فقط یک زیر مجموعه از داده های موجود در بلوک است. اگر اکنون برای دریافت تمام شماره های تلفن در محدوده ، پرس و جو بعدی را شلیک کنید ، بگویید از 9015000000 به 9019000000 ، دیگر نیازی به گرفتن بلوک دیگر از دیسک ندارید. داده های کامل را می توان در بلوک فعلی داده ها یافت ، بنابراین Clusted_Index با جمع آوری داده های مرتبط تا حد امکان در همان بلوک داده ، تعداد دیسک IO را کاهش می دهد. این کاهش دیسک IO باعث بهبود عملکرد می شود.

بنابراین اگر فکر خوبی به کلید اصلی دارید و نمایش داده شدگان شما بر اساس کلید اصلی است ، عملکرد بسیار سریع خواهد بود.

محدودیت های شاخص خوشه ای:

از آنجا که یک شاخص خوشه ای بر سازمان فیزیکی داده ها تأثیر می گذارد ، در هر جدول فقط یک شاخص خوشه ای وجود دارد.

رابطه بین کلید اصلی و شاخص خوشه ای:

شما نمی توانید با استفاده از InnODB در MySQL ، یک شاخص خوشه ای را به صورت دستی ایجاد کنید. MySQL آن را برای شما انتخاب می کند. اما چگونه انتخاب می کند؟گزیده های زیر از مستندات MySQL است:

هنگامی که یک کلید اصلی را در جدول خود تعریف می کنید ، InnoDB از آن به عنوان شاخص خوشه ای استفاده می کند. یک کلید اصلی را برای هر جدول ایجاد کنید. اگر ستون یا مجموعه ای از ستون های منحصر به فرد و غیر تهی وجود ندارد ، یک ستون جدید در حال افزایش خودکار اضافه کنید که مقادیر آن به صورت خودکار پر می شود.

 

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

اگر جدول دارای کلید اصلی RNO یا شاخص منحصر به فرد مناسب باشد ، InnoDB در داخل یک شاخص خوشه ای پنهان به نام gen_clust_index در ستون مصنوعی حاوی مقادیر ردیف شناسه تولید می کند. ردیف ها توسط شناسه ای که InnoDB در چنین جدول به ردیف ها اختصاص می دهد ، سفارش داده می شود. Row ID یک میدان 6 بایت است که با قرار دادن ردیف های جدید ، یکنواخت را افزایش می دهد. بنابراین ، ردیف های سفارش داده شده توسط Row ID از نظر جسمی به ترتیب درج هستند.

به طور خلاصه ، موتور MySQL InnoDB در واقع شاخص اصلی را به عنوان شاخص خوشه ای برای بهبود عملکرد مدیریت می کند ، بنابراین کلید اصلی و رکورد واقعی روی دیسک با هم خوشه بندی می شوند.

ساختار کلید اصلی (خوشه ای) شاخص:

یک شاخص معمولاً به عنوان یک درخت B+ روی دیسک و حافظه نگهداری می شود و هر شاخصی در بلوک های روی دیسک ذخیره می شود. این بلوک ها بلوک های شاخص نامیده می شوند. ورودی های موجود در بلوک شاخص همیشه بر روی کلید فهرست/جستجو طبقه بندی می شوند. بلوک شاخص برگ این شاخص شامل یک یاب ردیف است. برای شاخص اصلی ، یاب ردیف به آدرس مجازی محل فیزیکی مربوطه بلوک های داده روی دیسک که در آن ردیف ها مطابق با کلید فهرست هستند ، اشاره می کند.

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

آیا می توان یک شاخص اصلی را روی یک کلید غیر پیشرو ایجاد کرد؟

در MySQL ، یک شاخص اصلی به طور خودکار ایجاد می شود ، و ما قبلاً در بالا توضیح داده ایم که چگونه MySQL شاخص اصلی را انتخاب می کند. اما در دنیای پایگاه داده ، در واقع لازم نیست که یک فهرست در ستون کلید اصلی ایجاد شود - شاخص اصلی می تواند در هر ستون کلید غیر اصلی نیز ایجاد شود. اما هنگامی که بر روی کلید اصلی ایجاد شد ، تمام ورودی های کلیدی در شاخص بی نظیر هستند ، در حالی که در مورد دیگر ، شاخص اصلی ممکن است دارای یک کلید تکراری نیز باشد.

آیا می توان یک کلید اصلی را حذف کرد؟

حذف یک کلید اصلی امکان پذیر است. هنگامی که یک کلید اصلی را حذف می کنید ، شاخص خوشه ای مرتبط و همچنین خاصیت منحصر به فرد آن ستون از بین می رود.

مزایای شاخص اولیه:

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

مضرات شاخص اولیه:

• از آنجا که شاخص اصلی حاوی یک مرجع مستقیم به آدرس بلوک داده از طریق آدرس مجازی فضای مجازی و بلوک های دیسک به ترتیب کلید فهرست است ، هر بار که سیستم عامل برخی از صفحه دیسک را به دلیل عملیات DML مانند درج / به روزرسانی تقسیم می کند / تقسیم می شود. حذف ، شاخص اصلی نیز باید به روز شود. بنابراین عملیات DML فشار بر عملکرد شاخص اصلی وارد می کند.

فهرست ثانویه:

هر شاخص دیگری غیر از شاخص خوشه ای ، شاخص ثانویه نامیده می شود. شاخص های ثانویه بر خلاف شاخص های اولیه ، مکانهای ذخیره فیزیکی را تحت تأثیر قرار نمی دهد.

چه زمانی به شاخص ثانویه احتیاج دارید؟

شما ممکن است چندین مورد در برنامه خود داشته باشید که در آن پایگاه داده را با یک کلید اصلی پرس و جو کنید. در مثال ما Phone_no کلید اصلی است اما ممکن است ما نیاز به پرس و جو از پایگاه داده با PAN_NO یا نام داشته باشیم. در چنین مواردی اگر فرکانس چنین نمایش داده شدی بسیار زیاد است ، به شاخص های ثانویه در این ستون ها نیاز دارید.

چگونه می توان یک شاخص ثانویه در MySQL ایجاد کرد؟

دستور زیر یک فهرست ثانویه در ستون Name در جدول index_demo ایجاد می کند.

ساختار شاخص ثانویه:

در نمودار زیر ، مستطیل های رنگی قرمز بلوک های شاخص ثانویه را نشان می دهند. شاخص ثانویه نیز در درخت B+ نگهداری می شود و طبق کلید ایجاد شده در این فهرست طبقه بندی می شود. گره های برگ حاوی یک کپی از کلید داده های مربوطه در شاخص اولیه است.

بنابراین برای درک ، می توانید فرض کنید که شاخص ثانویه به آدرس کلید اصلی اشاره دارد ، اگرچه اینگونه نیست. بازیابی داده ها از طریق شاخص ثانویه به این معنی است که شما باید دو درخت B+ را طی کنید - یکی خود شاخص ثانویه B+ درخت و دیگری شاخص اصلی B+ Tree است.

مزایای شاخص ثانویه:

از نظر منطقی می توانید به همان اندازه که می خواهید شاخص های ثانویه ایجاد کنید. اما در واقعیت ، چند شاخص در واقع به یک فرایند فکر جدی نیاز دارد زیرا هر شاخص مجازات خاص خود را دارد.

مضرات یک فهرست ثانویه:

با استفاده از عملیات DML مانند Delete / Insert ، شاخص ثانویه نیز باید به روز شود تا کپی ستون کلید اصلی حذف و درج شود. در چنین مواردی ، وجود بسیاری از شاخص های ثانویه می تواند مسائلی ایجاد کند.

همچنین ، اگر یک کلید اصلی مانند URL بسیار بزرگ باشد ، زیرا شاخص های ثانویه حاوی یک کپی از مقدار ستون اصلی کلیدی هستند ، از نظر ذخیره می تواند ناکارآمد باشد. کلیدهای ثانویه بیشتر به معنای تعداد بیشتری از نسخه های تکراری از مقدار ستون اصلی اصلی هستند ، بنابراین در صورت وجود یک کلید اصلی بزرگ ، ذخیره سازی بیشتر. همچنین کلید اصلی خود کلیدها را ذخیره می کند ، بنابراین اثر ترکیبی روی ذخیره سازی بسیار زیاد خواهد بود.

توجه قبل از حذف یک فهرست اصلی:

در MySQL می توانید با رها کردن کلید اصلی ، یک شاخص اصلی را حذف کنید. ما قبلاً دیده ایم که یک شاخص ثانویه به یک شاخص اصلی بستگی دارد. بنابراین اگر یک شاخص اصلی را حذف کنید ، تمام شاخص های ثانویه باید به روز شوند تا حاوی یک کپی از کلید جدید شاخص جدید باشد که MySQL Auto تنظیم می کند.

این فرآیند وقتی چندین شاخص ثانویه وجود دارد گران است. همچنین جداول دیگر ممکن است یک کلید اصلی خارجی به کلید اصلی داشته باشد ، بنابراین شما باید قبل از حذف کلید اصلی ، آن منابع کلیدی خارجی را حذف کنید.

هنگامی که یک کلید اصلی حذف می شود ، MySQL به طور خودکار کلید اصلی دیگری را در داخل ایجاد می کند ، و این یک عملیات پرهزینه است.

فهرست کلید منحصر به فرد:

مانند کلیدهای اولیه ، کلیدهای منحصر به فرد همچنین می توانند سوابق منحصر به فرد را با یک تفاوت شناسایی کنند - ستون کلید منحصر به فرد می تواند حاوی مقادیر تهی باشد.

بر خلاف سایر سرورهای پایگاه داده ، در MySQL یک ستون کلید منحصر به فرد می تواند تا حد امکان مقادیر تهی داشته باشد. در استاندارد SQL ، تهی به معنای یک مقدار تعریف نشده است. بنابراین اگر MySQL باید فقط یک مقدار تهی را در یک ستون کلید منحصر به فرد داشته باشد ، باید فرض کند که تمام مقادیر تهی یکسان هستند.

اما از نظر منطقی این صحیح نیست زیرا تهی به معنای نامشخص است - و مقادیر نامشخص را نمی توان با یکدیگر مقایسه کرد ، این ماهیت تهی است. از آنجا که MySQL نمی تواند ادعا کند که آیا همه تهی به معنای یکسان است ، اجازه می دهد تا مقادیر تهی های متعدد در ستون باشد.

دستور زیر نحوه ایجاد یک فهرست کلید منحصر به فرد در MySQL را نشان می دهد:

شاخص کامپوزیت:

MySQL به شما امکان می دهد شاخص ها را در چندین ستون ، حداکثر 16 ستون تعریف کنید. این شاخص یک شاخص چند ستونی / کامپوزیت / مرکب نامیده می شود.

بیایید بگوییم که ما یک شاخص در 4 ستون تعریف کرده ایم - Col1 ، Col2 ، Col3 ، Col4. با یک شاخص کامپوزیت ، ما توانایی جستجو در Col1 ، (Col1 ، Col2) ، (Col1 ، Col2 ، Col3) ، (Col1 ، Col2 ، Col3 ، Col4) داریم. بنابراین ما می توانیم از هر پیشوند سمت چپ ستون های ایندکس شده استفاده کنیم ، اما نمی توانیم ستونی را از وسط حذف کنیم و از آن استفاده کنیم - (Col1 ، Col3) یا (col1 ، col2 ، col4) یا col3 یا col4 و غیره. اینها هستندترکیبات نامعتبر.

دستورات زیر 2 شاخص کامپوزیت در جدول ما ایجاد می کند:

اگر پرس و جوهایی حاوی یک عبارت WHERE در چندین ستون دارید، عبارت را به ترتیب ستون های شاخص ترکیبی بنویسید. ایندکس برای این پرس و جو مفید خواهد بود. در واقع، هنگام تعیین ستون ها برای یک نمایه ترکیبی، می توانید موارد استفاده مختلف سیستم خود را تجزیه و تحلیل کنید و سعی کنید به ترتیب ستون هایی که بیشتر موارد استفاده شما را سودمند می سازد، بیایید.

شاخص های ترکیبی می توانند در جستارهای JOIN & SELECT نیز به شما کمک کنند. مثال: در پرس و جو SELECT * زیر از composite_index_2 استفاده شده است.

هنگامی که چندین نمایه تعریف می شود، بهینه ساز پرس و جو MySQL آن شاخصی را انتخاب می کند که بیشترین تعداد ردیف را حذف می کند یا برای کارایی بهتر تا حد امکان تعداد ردیف های کمتری را اسکن می کند.

چرا از شاخص های ترکیبی استفاده می کنیم؟چرا چند شاخص ثانویه را روی ستون هایی که به آنها علاقه مندیم تعریف نمی کنیم؟

MySQL فقط از یک فهرست در هر جدول در هر پرس و جو استفاده می کند به جز UNION.(در یک UNION، هر کوئری منطقی به طور جداگانه اجرا می شود و نتایج ادغام می شوند.) بنابراین تعریف شاخص های متعدد بر روی ستون های متعدد تضمین نمی کند که آن شاخص ها حتی اگر بخشی از پرس و جو هستند استفاده شوند.

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

شاخص کامپوزیت چگونه کار می کند؟

ستون های مورد استفاده در شاخص های ترکیبی به هم متصل می شوند و آن کلیدهای پیوسته به ترتیب مرتب شده با استفاده از درخت B+ ذخیره می شوند. هنگامی که شما یک جستجو را انجام می دهید، الحاق کلیدهای جستجوی شما با کلیدهای شاخص ترکیبی مطابقت دارد. سپس اگر بین ترتیب کلیدهای جستجوی شما و ترتیب ستون های فهرست ترکیبی ناهماهنگی وجود داشته باشد، این فهرست قابل استفاده نیست.

در مثال ما، برای رکورد زیر، یک کلید فهرست ترکیبی با الحاق pan_no، نام، سن - HJKXS9086Wkousik28 تشکیل می شود.

نحوه تشخیص اینکه آیا به یک شاخص ترکیبی نیاز دارید:

• ابتدا درخواست های خود را با توجه به موارد استفاده خود تجزیه و تحلیل کنید. اگر می بینید که فیلدهای خاصی با هم در بسیاری از کوئری ها ظاهر می شوند، ممکن است ایجاد یک نمایه ترکیبی را در نظر بگیرید.
• اگر یک شاخص در col1 و یک شاخص ترکیبی در (col1, col2) ایجاد می کنید، پس فقط شاخص ترکیبی باید خوب باشد. col1 به تنهایی می تواند توسط خود ایندکس ترکیبی ارائه شود زیرا پیشوند سمت چپ ایندکس است.
• کاردینالیته را در نظر بگیرید. اگر ستون های مورد استفاده در شاخص ترکیبی در نهایت با هم کاردینالیته بالایی داشته باشند، کاندیدای خوبی برای شاخص ترکیبی هستند.

شاخص پوشش:

شاخص پوشش نوع خاصی از شاخص کامپوزیت است که در آن تمام ستون های مشخص شده در پرس و جو در جایی در فهرست وجود دارند. بنابراین بهینه ساز پرس و جو برای بدست آوردن داده ها نیازی به ضربه زدن به پایگاه داده ندارد - بلکه نتیجه آن از خود شاخص است. مثال: ما قبلاً یک شاخص کامپوزیت را در (PAN_NO ، نام ، سن) تعریف کرده ایم ، بنابراین اکنون پرس و جو زیر را در نظر بگیرید:

ستون های ذکر شده در Select & Where Brans بخشی از شاخص کامپوزیت هستند. بنابراین در این حالت ، ما در واقع می توانیم مقدار ستون سن را از خود شاخص کامپوزیت دریافت کنیم. بیایید ببینیم دستور توضیح برای این پرس و جو چیست:

در پاسخ فوق ، توجه داشته باشید که یک کلید وجود دارد - با استفاده از_Index که روی True تنظیم شده است که نشان می دهد از شاخص پوشش برای پاسخ به پرس و جو استفاده شده است.

من نمی دانم که شاخص های پوشش در محیط های تولید چقدر قدردانی می شود ، اما ظاهراً به نظر می رسد در صورت متناسب با این لایحه ، بهینه سازی خوبی باشد.

شاخص جزئی:

ما از قبل می دانیم که شاخص ها با هزینه فضا ، سؤالات ما را سرعت می بخشند. هرچه شاخص های بیشتری داشته باشید ، نیاز به ذخیره سازی بیشتر خواهد بود. ما قبلاً یک شاخص به نام secondary_idx_1 را بر روی نام ستون ایجاد کرده ایم. نام ستون می تواند حاوی مقادیر بزرگی از هر طول باشد. همچنین در این فهرست ، ابرداده های نشانگرهای ردیف یا ردیف اندازه خاص خود را دارند. بنابراین به طور کلی ، یک فهرست می تواند دارای بار ذخیره سازی و حافظه بالایی باشد.

در MySQL ، می توان در چند بایت اول از داده ها نیز یک شاخص ایجاد کرد. مثال: دستور زیر یک فهرست در 4 بایت اول نام ایجاد می کند. اگرچه این روش سربار حافظه را با مقدار مشخصی کاهش می دهد ، این شاخص نمی تواند بسیاری از ردیف ها را از بین ببرد ، زیرا در این مثال 4 بایت اول ممکن است در بسیاری از نام ها رایج باشد. معمولاً این نوع نمایه سازی پیشوند بر روی ستون های کاراکتر ، وارکار ، باینری ، لاکرونی پشتیبانی می شود.

وقتی یک شاخص را تعریف می کنیم چه اتفاقی می افتد؟

بیایید دوباره فرمان گسترده نمایش را اجرا کنیم:

ما Secondary_Index_1 را بر روی نام تعریف کردیم ، اما MySQL یک شاخص کامپوزیت را در (نام ، phone_no) ایجاد کرده است که phone_no ستون اصلی اصلی است. ما Secondary_Index_2 را در Age ایجاد کردیم و MySQL یک شاخص کامپوزیت در (سن ، PHONE_NO) ایجاد کرد. ما Composite_Index_2 را در (PAN_NO ، نام ، سن) ایجاد کردیم و MySQL یک شاخص کامپوزیت در (PAN_NO ، نام ، سن ، PHONE_NO) ایجاد کرده است.composite Index Composite_Index_1 از قبل Phone_no را به عنوان بخشی از آن دارد.

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

بسیاری از شاخص های دیگر نیز وجود دارند مانند نمایه فضایی و فهرست جستجوی متن کامل که توسط MySQL ارائه می شوند. من هنوز آن شاخص ها را آزمایش نکرده ام، بنابراین در این پست درباره آنها بحث نمی کنم.

دستورالعمل های عمومی نمایه سازی:

• از آنجایی که شاخص ها حافظه اضافی مصرف می کنند، با دقت تصمیم بگیرید که چه تعداد و چه نوع شاخصی نیاز شما را کفایت می کند.
• با عملیات DML، شاخص ها به روز می شوند، بنابراین عملیات نوشتن با ایندکس ها بسیار پرهزینه است. هر چه شاخص های بیشتری داشته باشید، هزینه بیشتری خواهید داشت. ایندکس ها برای سریعتر کردن عملیات خواندن استفاده می شوند. بنابراین اگر سیستمی دارید که سنگین می نویسد اما سنگین خوانده نمی شود، خوب فکر کنید که آیا به ایندکس نیاز دارید یا نه.
• کاردینالیته مهم است - کاردینالیته به معنای تعداد مقادیر متمایز در یک ستون است. اگر نمایه ای را در ستونی ایجاد کنید که کاردینالیته پایینی دارد، سودمند نخواهد بود زیرا ایندکس باید فضای جستجو را کاهش دهد. کاردینالیته پایین به طور قابل توجهی فضای جستجو را کاهش نمی دهد. مثال: اگر نمایه ای را روی ستونی از نوع بولی (فقط int 1 یا 0) ایجاد کنید، ایندکس بسیار کج خواهد شد زیرا کاردینالیته کمتر است (در اینجا کاردینالیته 2 است). اما اگر می توان این فیلد بولی را با ستون های دیگر ترکیب کرد تا کاردینالیته بالایی ایجاد کند، در صورت لزوم به سراغ آن شاخص بروید.
• اگر داده های قدیمی همچنان در فهرست باقی بمانند، ممکن است شاخص ها نیاز به نگهداری داشته باشند. آنها باید حذف شوند در غیر این صورت حافظه مختل می شود، بنابراین سعی کنید یک برنامه نظارتی برای شاخص های خود داشته باشید.

در پایان، درک جنبه های مختلف نمایه سازی پایگاه داده بسیار مهم است. در حین انجام طراحی سیستم سطح پایین کمک خواهد کرد. بسیاری از بهینه سازی های واقعی برنامه های ما به دانش چنین جزئیات پیچیده ای بستگی دارد. یک شاخص با دقت انتخاب شده مطمئناً به شما کمک می کند تا عملکرد برنامه خود را افزایش دهید.

لطفاً اگر این مقاله را دوست دارید کف بزنید و با دوستان خود و در رسانه های اجتماعی به اشتراک بگذارید.:)