ایندکس دیتابیس از صفر: چرا کوئریها سریع میشن؟
خلاصهٔ کاملتر
نویسنده برای اینکه ایندکس رو عملی نشون بده، دیتاست Postgres Air رو روی PostgreSQL بالا آورده که جدول passenger اون حدود ۱۶ میلیون ردیف داره. یه کوئری ساده که دنبال first_name = 'JASON' و یه account_id مشخص میگرده، بدون ایندکس حدود ۵ ثانیه روی MacBook Air M1 طول کشیده. بعد از ساختن یه ایندکس روی همین دو ستون، همون کوئری به حدود ۱ تا ۱۰ میلیثانیه رسیده.
CREATE INDEX "passenger_name_account_id" ON "postgres_air"."passenger"("first_name","account_id");با دستور EXPLAIN (یعنی نشون دادن نقشهای که دیتابیس برای اجرای کوئری میکشه) فرقش معلوم میشه. بدون ایندکس، Postgres یه Seq Scan انجام میده، یعنی تکتک ردیفها رو میخونه و فیلتر میکنه، با cost حدود 274633. با ایندکس، کار به Index Scan با cost حدود 8.46 تبدیل میشه که به گفتهی نویسنده نزدیک ۳۲ هزار برابر کمتره.
ایندکس در واقع یه ساختار دادهی جداست که معمولاً با B+ tree ساخته میشه. B+ tree یعنی درختی که گرههای بالاش فقط مسیر رو نشون میدن و اشارهگر به دادهی واقعی فقط تو برگهاشه. اینطوری دیتابیس بهجای گشتن کل جدول، مستقیم از درخت پایین میره و به ردیف میرسه. نویسنده سه نوع ایندکس رو معرفی میکنه: clustered که ترتیب فیزیکی دادهها رو روی دیسک عوض میکنه و هر جدول فقط یکی ازش داره، non-clustered که یه ساختار جدا با اشارهگر میسازه، و unique که جلوی مقدار تکراری تو یه ستون رو میگیره.
چرا روی همهی ستونها ایندکس نذاریم؟ چون هر بار که ردیفی اضافه یا آپدیت میشه، دیتابیس باید علاوه بر نوشتن داده، همهی ایندکسها رو هم مرتب کنه. یعنی ایندکس بیشتر، خوندن سریعتر ولی نوشتن کندتر. نویسنده میگه باید بین این دو تعادل پیدا کرد.
جالبترین بخش مقاله جاییه که ایندکس کمکی نمیکنه. کوئریای که فقط دنبال first_name = 'JASON' میگرده حدود 115895 ردیف برمیگردونه. با ایندکس روی first_name زمانش فقط از ۳.۵ به ۳.۲ ثانیه رسیده و cost فقط حدود ۱۶ درصد کم شده. دلیلش اینه که ایندکس فقط جای ردیفها رو میگه؛ دیتابیس هنوز باید به هزاران جای پراکنده روی دیسک سر بزنه، و SELECT * هم با خوندن همهی ستونها این I/O رو بدتر میکنه.
راهحلهایی که نویسنده پیشنهاد میده: ایندکس ترکیبی روی چند ستون، فقط ستونهایی که لازم داری رو انتخاب کن، و اگه همهی ردیفها رو یهجا لازم نداری، صفحهبندی کن. اگه واقعاً همهی ۱۰۰ هزار ردیف رو یهجا بخوای، کار زیادی از دستت برنمیاد.
نکات کلیدی:
- ایندکس ترکیبی روی first_name و account_id زمان کوئری رو از حدود ۵ ثانیه به زیر ۱۰ میلیثانیه رسوند.
- EXPLAIN نشون میده دیتابیس از Seq Scan استفاده میکنه یا Index Scan.
- ایندکسها تو PostgreSQL معمولاً با B+ tree ساخته میشن و اشارهگر داده فقط تو برگهاست.
- هر جدول فقط یه clustered index میتونه داشته باشه.
- هر ایندکس اضافه، نوشتن (INSERT و UPDATE) رو کندتر میکنه.
- وقتی کوئری دهها هزار ردیف برمیگردونه، ایندکس تکستونی تقریباً بیاثره؛ ستونهای کمتر و صفحهبندی کمک میکنن.




