ایندکس ترکیبی و قانون Leftmost Prefix
متوسط
۱۲ دقیقه مطالعه
گاهی پرسوجوهای پرتکرار، همیشه با هم روی چند ستون فیلتر میکنند — مثلاً «دانشآموزان شهر تهران با پایهی ۱۰». بهجای دو ایندکس جدا، میتوان یک ایندکس ترکیبی (Composite Index) روی چند ستون همزمان ساخت:
sql
CREATE INDEX idx_students_city_grade ON students(city, grade);
این دستور در SQLite و SQL Server کاملاً یکسان است. اما نکتهی مهمی هست که خیلی از برنامهنویسها اشتباه میفهمند: ترتیب ستونها در ایندکس ترکیبی، اهمیت حیاتی دارد.
قانون Leftmost Prefix (پیشوند چپترین ستون)
ایندکس ترکیبی (city, grade) را میتوان مثل یک دفترچه تلفن دوسطحی تصور کرد: اول بر اساس شهر مرتب شده، و داخل هر شهر، بر اساس پایه. این یعنی:
- فیلتر روی
cityبهتنهایی → ایندکس قابلاستفاده است (چون شهر، ستون اول است). - فیلتر روی
cityوgradeبا هم → ایندکس کاملاً قابلاستفاده است. - فیلتر روی
gradeبهتنهایی (بدونcity) → ایندکس قابلاستفاده نیست! چون بدون دانستن شهر، نمیتوان مستقیم به بخش درست از دفترچه پرید — دقیقاً مثل اینکه بخواهی در دفترچهای که بر اساس نامخانوادگی مرتب شده، کسی را فقط با نام کوچکش پیدا کنی.
بیا با EXPLAIN QUERY PLAN خودمان این را ثابت کنیم:
امتحانش کن — فیلتر روی ستون اول ایندکس (city)
امتحانش کن — فیلتر روی ستون دوم بدون ستون اول (grade)
جعبهی اول
SEARCH students USING INDEX idx_students_city_grade (city=? AND grade=?) را نشان میدهد. جعبهی دوم — که فقط grade دارد — SCAN students است، یعنی همان ایندکسی که تازه ساختیم، کاملاً نادیده گرفته شد! این دقیقاً قانون Leftmost Prefix است، و در SQL Server هم عیناً همینطور رفتار میشود.پس چطور ترتیب ستونها را انتخاب کنیم؟
- ستونی که بیشتر بهتنهایی در
WHEREاستفاده میشود را اول بگذار. اگر گاهی فقط باcityفیلتر میکنی و گاهی با هر دو، اما هرگز فقط باgrade، پس(city, grade)انتخاب درستی است. - ستون با گزینشپذیری بالاتر (مقادیر متنوعتر) معمولاً بهتر است اول بیاید — چون سریعتر دامنهی جستوجو را کوچک میکند.
- اگر واقعاً هم به فیلتر تنهای
cityو هم فیلتر تنهایgradeنیاز داری، شاید به دو ایندکس جدا نیاز داشته باشی، نه یک ایندکس ترکیبی.
جمعبندی این درس
- ایندکس ترکیبی روی چند ستون همزمان ساخته میشود و برای فیلترهای ترکیبی پرتکرار مناسب است.
- قانون Leftmost Prefix: ایندکس فقط وقتی قابلاستفاده است که ستونهای چپترین (اول) آن در شرط پرسوجو حضور داشته باشند.
- فیلتر روی ستون دوم بدون ستون اول، باعث نادیدهگرفتن کامل ایندکس میشود — چیزی که با
EXPLAIN QUERY PLANمستقیم دیدیمش. - ترتیب ستونها باید بر اساس الگوی واقعی پرسوجوهای برنامه انتخاب شود، نه بهصورت تصادفی.
در درس بعدی یاد میگیریم چطور نقشهی اجرا (Execution Plan) یک پرسوجوی پیچیدهتر با چند JOIN را بخوانیم و تفسیر کنیم.