| انواع ایندکس |
|---|
| درخت متوازن برای تطبیق دقیق، بازهای و اسکن مرتب. پیشفرض بیشتر پایگاههای داده. | برای =، <، >، BETWEEN و ORDER BY به کار ببرید. برای جستوجوی متن کامل نه. |
| ایندکسی که سطح برگ آن خود جدول است. سطرها به ترتیب کلید ذخیره میشوند. یکی برای هر جدول. | InnoDB روی کلید اصلی خوشهبندی میشود. جدولهای SQL Server بدون آن heap هستند. |
| ساختار جداگانهای که کلیدها بههمراه اشارهگری به جدول پایه را نگه میدارد. چندتا برای هر جدول. | روی heap یک شناسهی سطر ذخیره میکند. روی جدول خوشهای، کلید خوشهبندی را ذخیره میکند. |
| جدول درهمسازی فقط برای جستوجوی تطبیق دقیق. تطبیق دقیق با زمان ثابت. | فقط برای عملگرهای = ؛ بازه و مرتبسازی به B-tree نیاز دارند. هش memory-optimized در SQL Server به bucket count نزدیک به تعداد سطرها نیاز دارد. |
| ایندکس معکوس تعمیمیافته. مقادیر را به سطرها نگاشت میکند. برای آرایهها و متن کامل ساخته شده. | برای jsonb، آرایهها، tsvector و جستوجوی trigram. ساختش کندتر از B-tree است. |
| درخت جستوجوی تعمیمیافته. برای انواع غیراسکالر و پرسوجوهای همپوشانی توسعهپذیر است. | برای بازههای هندسی، kNN و قیدهای exclusion به کار ببرید. pg_trgm سرعت LIKE را بالا میبرد. |
| GiST با افراز فضایی. دادهها را با درختهای radix یا quad به ناحیههای بدون همپوشانی تقسیم میکند. | برای نقاط، پیشوندهای IP و تطبیق پیشوند. برای همپوشانی نه؛ از GiST استفاده کنید. |
| ایندکس بازهی بلوکی. کمینه/بیشینه را برای هر بازهی بلوک ذخیره میکند. ردپای بسیار کوچک. | روی جدولهای بزرگِ مرتبشده به صورت فیزیکی به کار ببرید. ساختش ارزان، گزینشپذیریاش ضعیف است. |
| ایندکسی که بهجای سطر، به شکل قطعههای ستونی ذخیره میشود. فشردهسازی بالا، اجرای دستهای. | برای اسکنهای تحلیلی روی سطرهای زیاد و ستونهای کم. خواندن تکسطری کند است. |
| ایندکس معکوس اختصاصی واژه در MySQL و SQL Server. برای هر ستون متنی ساخته میشود. | از MATCH AGAINST یا CONTAINS استفاده کنید. متن کامل در Postgres در عوض GIN روی tsvector را به کار میبرد. |
| ایندکس Oracle که برای هر مقدار متمایز یک bitmap ذخیره میکند. هر بیت یک سطر را نشان میدهد. | در انبارهای داده با خواندن زیاد روی ستونهای کاردینالیتی پایین. نوشتنهای همزمان به ازای هر bitmap سریالی میشوند. |
| ستون ایندکس با تعریف DESC. کلیدهای آن ستون به ترتیب معکوس ذخیره میشوند. | برای مرتبسازیهای ترکیبی مثل (a ASC, b DESC) لازم است. مرتبسازی DESC ساده میتواند ایندکس معمولی را هم وارونه بخواند. |
| ایندکس MySQL 8 که بهینهساز نادیدهاش میگیرد. اما در هر نوشتن همچنان نگهداری میشود. | پیش از حذف، ایندکس را مخفی کنید تا اثرش را بسنجید. بدون بازسازی دوباره نمایانش کنید. |
| طراحی |
|---|
| ایندکس چندستونی. ستونهای آغازین باید با ترتیب فیلتر پرسوجو بخوانند. | اول تساوی، بعد بازه. دو ایندکس تکستونی جای یک ایندکس مرکب را نمیگیرند. |
| ایندکس روی یک شرط WHERE. SQL Server آن را filtered index مینامد. فقط سطرهای منطبق را پوشش میدهد. | فقط سطرهای active='t' را ایندکس کنید. حجم و هزینهی نوشتن نصف میشود. |
| B-tree با ستونهای غیرکلیدی الحاقی. اسکن index-only سر زدن به heap را حذف میکند. | ستونهای پرجستوجو را با INCLUDE بیفزایید. ایندکس مرتبخواه باقی میماند. |
| ایندکسی که مقدار تکراری را ممنوع میکند. درجهای متعارض را رد میکند. | برای کلیدهای طبیعی و قیدهای یکبهیک. UNIQUE NULL چند NULL را مجاز میدارد. |
| ایندکسی که روی تابعی از ستونها ساخته میشود، نه خود ستونها. | lower(email) یا date_trunc('day', created_at) را ایندکس کنید. پرسوجو باید دقیقاً همان عبارت را تکرار کند. |
| کلاس عملگر. تعیین میکند ایندکس با نوع یک ستون چگونه مقایسه و ذخیره میکند. | برای LIKE 'abc%' بدون تغییر حالت حروف، varchar_pattern_ops را بگذارید. پیشفرضها برای تساوی و بازه مناسباند. |
| پلانی که پرسوجو را تنها از صفحات ایندکس جواب میدهد. heap هرگز خوانده نمیشود. | همهی ستونهای خواندهشده باید در ایندکس یا فهرست INCLUDE باشند. Vacuum نقشهی دید را بهروز نگه میدارد. |
| ستونهای کلید خارجی بهطور خودکار در جدول ارجاعدهنده ایندکس نمیشوند. | هر ستون FK که با آن join یا delete میکنید را ایندکس کنید. حذفهای آبشاری بدون آن جدول فرزند را اسکن میکنند. |
| نگهداری |
|---|
| بازرس پلان. اسکنهای ترتیبی، انتخاب ایندکس و برآورد هزینه را نشان میدهد. | از EXPLAIN (ANALYZE, BUFFERS) استفاده کنید. Seq Scan روی جدول ۱۰ میلیونی یعنی ایندکس غایب است. |
| نمونهگیری از ستونهای جدول برای ساخت آمار برنامهریز. هیستوگرامها انتخاب ایندکس را هدایت میکنند. | پس از بارگذاری سنگین اجرا کنید. آمار کهنه باعث seq scan روی ستونهای ایندکسشده میشود. |
| هر ایندکس هزینهی نوشتن را بالا میبرد. درج، بهروزرسانی و حذف به ازای هر ایندکس میپردازند. | ایندکسهای بلااستفاده را حذف کنید. استفاده را در pg_stat_user_indexes ببینید. |
| نمای Postgres برای استفادهی هر ایندکس. تعداد اسکنها و تاپلهای خواندهشده از آخرین بازنشانی را میشمارد. | با idx_scan = 0 ایندکسهای بلااستفاده را بیابید. شمارندهها را با pg_stat_reset() بازنشانی کنید. |
| ایندکس را بدون مسدود کردن خواندن و نوشتن میسازد. به دو اسکن جدول نیاز دارد. | داخل تراکنش اجرا نمیشود. ساخت ناموفق یک ایندکس INVALID بهجا میگذارد که باید حذف شود. |
| ایندکس را حذف و فضایش را آزاد میکند. بهطور پیشفرض قفل انحصاری میگیرد. | روی جدولهای پرترافیک از DROP INDEX CONCURRENTLY استفاده کنید. پیش از حذف، آمار استفاده را ببینید. |
| ایندکس متورم را از نو میسازد. فضای تاپلهای مرده را پس میگیرد. | در محیط عملیاتی از REINDEX CONCURRENTLY استفاده کنید. بدون آن قفلها نوشتن را میبندند. |
| تاپلهای مرده و خلأهای صفحهای که در جدولهای MVCC و ایندکسهایشان انباشته میشوند. | با pgstattuple بسنجید. زیاد vacuum بزنید؛ وقتی فضای مرده غالب شد REINDEX. |
| درصد پرشدن یک صفحه هنگام نوشتن. فضای آزاد بهروزرسانیهای آینده را جذب میکند. | روی جدولهای با بهروزرسانی داغ به ۷۰ تا ۹۰ بریزید. صفحههای پُر باعث page split میشوند. |
| جدول را فیزیکی و مرتب بر اساس یک ایندکس از نو مینویسد. عملی یکباره. | با BRIN برای بلوکهای مرتب جفت میشود. ترتیب در نوشتنهای بعدی حفظ نمیشود. |