قابلیت invisible index برای پیدا کردن بدترین ایندکس

ویژگی Ignored Indexes تو MariaDB یکی از ویژگی‌های جدید و خیلی کاربردیه که این امکان و میده ایندکس‌ها رو موقع عملیات select نادیده بگیره. یعنی ایندکس‌ها همچنان نگهداری و به‌روزرسانی میشن، ولی Optimizer دیگه از اونا تو کوئری‌ها استفاده نمیکنه. این ویژگی شبیه قابلیت Invisible Indexes تو دیتابیس MySQL نسخه ۸ هست. این ویژگی مخصوصا تو تست‌هایی که می‌خواهید ببینید حذف یه ایندکس چه تاثیری روی کارایی و سرعت کوئری‌ها داره به درد میخوره. با این کار میتونید قبل از حذف نهایی یه ایندکس، اونو به حالت ignore دربیارین و اگه با مشکلی مثل کم شدن سرعت کوئری یا کم شدن پرفورمنس روبرو شدید، به راحتی دوباره اونو فعال کنید.

یه مثال ساده تو دیتابیس MariaDB ببینیم

فرض کنید جدولی به اسم users داریم و یه ایندکس روی ستون email درست کردیم. حالا میخوایم بررسی کنیم که اگه این ایندکس ignore بشه، چه تاثیری روی عملکرد کوئری ها داره. با استفاده از دستور زیر میتونیم این کار رو انجام بدیم:

ALTER TABLE users ALTER INDEX email_index IGNORED;

تو این حالت، ایندکس همچنان نگه داشته میشه و داده‌های جدید به آن اضافه میشه، ولی optimizer دیتابیس ازش استفاده نمیکنه. اگه بعد از یه مدتی دیدین که نادیده گرفتن ایندکس باعث شده سرعت کوئری کم بشه یا پرفورمنسش اومده پایین ، میتونید به راحتی با این دستور دوباره فعالش کنید:

ALTER TABLE users ALTER INDEX email_index NOT IGNORED;

عملکرد در MySQL:

این قابلیت تو MySQL 8 با اسم Invisible Indexes معرفی شده. مکانیزمش دقیقا شبیه دیتابیس mariadb فقط کلمه کلیدی هاشون متفاوت هست. اینجا میتونیم ایندکس‌ها رو به حالت invisible در بیاریم و optimizer ازش استفاده نمیکنه ولی همچنان ایندکس ها رو نگه میداره. دستورش هم به این شکل استفاده میشه:

ALTER TABLE users ALTER INDEX email_index INVISIBLE;

برای برگردوندن ایندکس به حالت قابل استفاده:

ALTER TABLE users ALTER INDEX email_index VISIBLE;

قابلیت invisible index توی دیتابیس PostgreSQL چطوریه؟

تو دیتابیس PostgreSQL ویژگی invisible index به صورت پیش‌فرض وجود نداره و به صورت built-in این قابلیت رو ارائه ندادن متاسفانه.

بریم ببینیم Under the Hood چه خبره؟

وقتی یه ایندکس به حالت Ignored یا Invisible تو MariaDB و MySQL درآورده میشه، دیتابیس همچنان ایندکس رو به‌روزرسانی میکنه و برای عملیات INSERT، UPDATE و DELETE همچنان تغییرات رو اعمال میکنه. اما الگوریتم optimizer موقع اجرای کوئری‌ها، ایندکس و نادیده میگیره. این کار باعث میشه بدون تغییر ساختار اصلی ایندکس‌ها، بتوینم تاثیر اونارو روی عملکرد کوئری‌ها تست کنیم.

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

در نهایت ویژگی Ignored Indexes تو MariaDB و Invisible Indexes تو MySQL برنامه نویس ها و دیتابیس ادمین ها کمک میکنه که ایندکس‌های بدون استفاده یا کم‌اثر رو به سادگی تست کنن. این کار به بهینه‌سازی دیتابیس کمک میکنه و ما میتونیم قبل از اعمال تغییرات دائمی، اول تاثیر اونارو رو بررسی کنیم بعد تصمیم بگیریم برای حذف ایندکس یا نگه داشتنش.

تو مصاحبه ها اگر ازتون در مورد ایندکس گذاری روی فیلدهای دیتابیس پرسیدن میتونین این فیچر و به عنوان راه حل نهایی ارائه بدین.

امیدوارم این پست براتون مفید بوده باشه و مرسی که حمایت میکنید و باعث میشین به دست بقیه هم برسه این پست.