بررسی انواع شاخص ها (Index)  در پایگاه داده PostgreSQL

مقدمه :

در این آموزش با انواع مختلف شاخص (index) PostgreSQL و نحوه استفاده مناسب از آنها آشنا خواهید شد.

PostgreSQL چندین نوع شاخص دارد: B-tree، Hash، GiST، SP-GiST، GIN و BRIN. هر نوع شاخص از ساختار و الگوریتم ذخیره سازی متفاوتی برای مقابله با انواع مختلف پرس و جو استفاده می کند.

معرفی معماری پایگاه داده PostgreSQL

هنگامی که از دستور CREATE INDEX بدون تعیین نوع شاخص استفاده می کنید، PostgreSQL به طور پیش فرض از نوع شاخص B-tree استفاده می کند زیرا بهترین کار این است که با رایج ترین پرس و جوها مطابقت داشته باشد.

شاخص های B-tree:

B-tree یک درخت خود متعادل کننده است که داده های مرتب شده را حفظ می کند و امکان جستجو، درج، حذف و دسترسی متوالی را در زمان لگاریتمی فراهم می کند.

Query planner PostgreSQL هر زمان که ستون های شاخص در مقایسه ای که از یکی از عملگرهای زیر استفاده می کند درگیر شوند، از یک شاخص B-tree استفاده می کند:

<

<=

=

>=

BETWEEN

IN

IS NULL

IS NOT NULL

علاوه بر این، Query planner می تواند از یک شاخص B-tree برای جستارهایی استفاده کند که شامل یک عملگر مطابق با الگوی LIKE و ~ است، اگر الگو ثابت باشد و در ابتدای الگو لنگر باشد، به عنوان مثال:

column_name LIKE ‘foo%’

column_name LKE ‘bar%’

column_name  ~ ‘^foo’

علاوه بر این، اگر الگو با یک کاراکتر غیرالفبایی شروع می شود، برنامه ریز پرس و جو استفاده از شاخص های درخت B را برای ILIKE و ~* در نظر می گیرد که نویسه هایی هستند که تحت تأثیر تبدیل حروف بزرگ/کوچک قرار نمی گیرند.

اگر استفاده از ایندکس را برای بهینه سازی پایگاه داده PostgreSQL خود شروع کرده اید، احتمالاً B-tree همان چیزی است که می خواهید.

شاخص های Hash:

شاخص‌های Hash فقط می‌توانند از مقایسه برابری ساده (=) استفاده کنند. این بدان معنی است که هرگاه یک ستون شاخص شده در مقایسه با عملگر برابر (=) درگیر شود، Query planner استفاده از شاخص Hash را در نظر می گیرد.

برای ایجاد یک شاخص Hash، از عبارت CREATE INDEX با نوع شاخص HASH در عبارت USING به صورت زیر استفاده می کنید:

CREATE INDEX index_name

ON table_name USING HASH (indexed_column);

شاخص های GIN:

GIN مخفف generalized inverted indexes است. معمولاً به آن GIN می گویند.

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

شاخص های BRIN:

BRIN مخفف block range indexes است. نگهداری BRIN در مقایسه با شاخص B-tree بسیار کوچکتر و هزینه کمتری دارد.

BRIN اجازه می دهد تا از یک شاخص در یک جدول بسیار بزرگ استفاده شود که قبلاً با استفاده از B-tree بدون پارتیشن بندی افقی غیر عملی بود.

BRIN اغلب در ستونی استفاده می شود که دارای ترتیب مرتب سازی خطی است، به عنوان مثال، ستون تاریخ ایجاد جدول سفارش فروش.

شاخص های GiST:

GiST مخفف  Generalized Search Tree است. شاخص های GiST امکان ساخت ساختارهای درختی عمومی را فراهم می کند. شاخص های GiST در شاخص سازی انواع داده های هندسی و جستجوهای متن کامل مفید هستند.

شاخص های SP-GiST:

SP-GiST مخفف Space-partitioned GiST است. SP-GiST از درخت های جستجوی پارتیشن بندی شده پشتیبانی می کند که توسعه طیف وسیعی از ساختارهای داده غیرمتعادل مختلف را تسهیل می کند.

شاخص‌های SP-GiST برای داده‌هایی که دارای یک عنصر خوشه‌بندی طبیعی هستند و همچنین درختی به همان اندازه متعادل نیستند، برای مثال GIS، چند رسانه‌ای، مسیریابی تلفن و مسیریابی IP بسیار مفید هستند.

در این آموزش انواع مختلف ایندکس PostgreSQL از جمله B-tree، Hash، BRIN، GiST و SP-GiST را آموختید.

مقالات مرتبط:

SYNCHRONOUS و ASYNCHRONOUS REPLICATION در پایگاه داده POSTGRESQL بررسی view کاربردی pg_stat_activity در Postgresql بررسی کامل ابزار PG_BASEBACKUP جهت پشتیبان گیری از پایگاه داده Postgresql معرفی ابزار pgbench در حوزه آزمون عملکرد پایگاه داده Postgresql