Postgresql

ایندکس کردن در PostgreSQL یک فرایند است که شامل ایجاد ساختارهای داده ای است که برای جستجو و بازیابی کارآمد داده ها از جداول بهینه شده است.

یک index یک کپی از یک قسمت از جدول است که به گونه ای مرتب شده است که PostgreSQL می تواند به سرعت ردیف هایی را که با یک شرط query خاص مطابقت دارند پیدا و بازیابی کند.

هنگامی که یک query اجرا می شود، PostgreSQL به indexes موجود نگاه می کند تا تعیین کند که آیا می توان از آنها برای برآورده کردن شرط query استفاده کرد. اگر PostgreSQL یک index مرتبط پیدا کند، از آن برای شناسایی سریع ردیف های مربوطه در جدول استفاده می کند. این باعث می شود که queries به طور قابل توجهی سریعتر شوند، به خصوص در شرایطی که جداول بزرگ هستند یا شرایط پیچیده هستند.

پستگرس از چندین نوع index پشتیبانی می کند، از جمله B-tree، hash، GiST، SP-GiST و BRIN. هر نوع index برای پاسخگویی به انواع مختلف query ها و الگوهای دسترسی به داده ها طراحی شده است.

علاوه بر انواع index استاندارد، PostgreSQL به کاربران اجازه می دهد تا index های سفارشی را با استفاده از توابع تعریف شده توسط کاربر تعریف کنند.

ایندکس کردن با درخت B یکی از رایج ترین انواع index برای ذخیره و بازیابی کارآمد داده ها در PostgreSQL است. این نوع index پیش فرض است. هر زمان که از دستور CREATE INDEX بدون مشخص کردن نوع index مورد نظر استفاده کنیم، PostgreSQL یک index B-tree برای جدول یا ستون ایجاد می کند.

CREATE INDEX index_name ON table_name;

ایندکس تک ستونی Single-column

برای ایجاد یک ایندکس B-tree تک ستونی بر اساس یک ستون جدول، به جای ایجاد یک ایندکس بر روی کل جدول، از دستور زیر استفاده می شود.

CREATE INDEX index_name ON table_name (column_name);

مقدار index_name نامی است که می خواهید به ایندکس بدهید.

مقدار table_name نام جدولی است که می خواهید ایندکس را روی آن ایجاد کنید.

مقدار column_name نام ستون(هایی) است که می خواهید ایندکس را روی آن ایجاد کنید.

مثال :

بیایید جدولی به نام "sales_info" ایجاد کنیم و برخی از داده های ساختگی را درونش وارد کنیم.

CREATE TABLE sales_info (

sales_id integer NOT NULL, email VARCHAR,

location VARCHAR, item_purchased VARCHAR,

price VARCHAR

);

با استفاده از عبارت INSERT مقادیر را در جدول وارد کنید:

INSERT INTO sales_info (

sales_id, email, location, item_purchased,

price

)

VALUES

(

1, 'halie46@gmail.com', 'London',

'Headphone', '$50'

),

(

2, 'romaine21@gmail.com', 'Australia',

'Webcam', '$50'

),

(

3, 'frederique19@gmail.com', 'Canada',

'iPhone 14 pro', '$1259'

),

(

4, 'kenton_macejkovic80@hotmail.com',

'London', 'Wireless Mouse', '$20'

),

(

5, 'alexis62@hotmail.com', 'Switzerland',

'Dell Charger', '$15'

),

(

6, 'concepcion_kiehn@hotmail.com',

'Canada', 'Longitech Keyboard',

'$499'

);

با اجرای این دستور یک ایندکس از نوع B-tree بر روی ستون sales_id ایجاد میکنیم:

CREATE INDEX idx_sales_id ON sales_info (sales_id);

وقتی دستور SELECT را اجرا می کنیم، کل زمان اجرای query را در بالا دریافت می کنیم.

زمان نمایش داده شده ممکن است ناچیز به نظر برسد، زیرا جدولی که با آن کار می کنیم کوچک است. اما هنگام کار با حجم زیادی از داده ها، این کار به طور قابل توجهی عملکرد query شما را بهبود می بخشد.

Hash Indexe ها

ایندکس‌های هش برای جست‌وجوهای سریع کلید-مقدار طراحی شده‌اند. زمانی که شرط query بر روی ستون‌هایی که ایندکس‌ شان هش است، نیازمند برابری باشد، ایندکس‌های هش می‌توانند بازیابی بسیار سریعی ارائه دهند، زیرا تابع هش به طور مستقیم مکان داده مورد نظر را تعیین می‌کند. ایندکس‌های هش بیشتر برای مقایسه‌های برابری مانند عملگرهای "=" یا "IN" مناسب هستند.

مانند سایر انواع ایندکس‌ها، برای حفظ سازگاری داده‌ها در هنگام تغییرات داده (درج، به‌روزرسانی و حذف)، نیاز به نگهداری ایندکس‌های هش وجود دارد. اما نگهداری ایندکس‌های هش ممکن است گرانتر از ایندکس‌های B-Tree باشد به دلیل نیاز به حل تصادفات و باز هش داده‌ها.

برای ایجاد یک ایندکس‌ هش در PostgreSQL، می‌توانید از دستور CREATE INDEX با عبارت USING HASH استفاده کنید. به عنوان مثال:

CREATE INDEX hash_name ON table_name USING HASH (column_name);

این دستور یک ایندکس‌ هش با نام "hash_name" روی ستون مشخص شده در جدول ایجاد می‌کند.

نکته‌ای که باید به آن توجه کنید این است که در حالی که ایندکس‌های هش در PostgreSQL وجود دارند، برای query های محدوده‌ای یا مرتب‌سازی مناسب نیستند. ایندکس‌های B-Tree معمولاً برای چنین سناریوهایی ترجیح داده می‌شوند. همچنین، ایندکس‌های B-Tree نوع پیش‌فرض و رایج برای انواع ایندکس‌ها در PostgreSQL هستند.

ایندکس‌های هش مورد استفاده و محدودیت‌های خاص خود را دارند و قبل از تصمیم‌گیری درباره نوع مناسب ایندکس‌ برای پایگاه داده PostgreSQL خود، باید نیازها و الگوهای query خود را ارزیابی کنید.

مثال :

ایجاد ایندکس هش روی جدول sales_info و استفاده از هش برای ستون sales_id به این صورت است .

CREATE INDEX idx_sales_id ON sales_info USING HASH(sales_id);

انتخاب و فیلتر اطلاعات با استفاده از کلمه کلیدی WHERE :

EXPLAIN (ANALYZE)

Select

*

from

sales_info

WHERE

sales_id = 5;

ایندکس های GiST و SP-GiST

ایندکس های GiST (Generalized Search Tree) و SP-GiST (Space-Partitioned Generalized Search Tree) نوعی ایندکس پیشرفته در PostgreSQL هستند که پشتیبانی از گونه‌های متنوعی از داده و عملیات جستجو را فراهم می‌کنند.

این ایندکس ها به خصوص برای مدیریت ساختارهای پیچیده داده و داده‌های فضایی مفید هستند. ایندکس های GiST برای سرعت بخشیدن به جستجوهای متنی کاربرد دارند.

نحوه ایجاد ایندکس های GiST وSP-GiST:

برای ایجاد یک ایندکس GiST یا SP-GiST در PostgreSQL، می‌توانید از دستور CREATE INDEX با عبارت USING GIST یا USING SPGIST به ترتیب استفاده کنید.

اینجا مثالی از ایجاد یک ایندکس GiST بر روی یک ستون هندسی آمده است:

CREATE INDEX index_geometry ON table_name USING GIST (geometry_column);

و در اینجا مثالی از ایجاد یک اندیس SP-GiST بر روی یک ستون tsvector آمده است:

CREATE INDEX index_text_search ON table_name USING SPGIST (tsvector_column);

مروری بر اندیس‌های GiST و SP-GiST در PostgreSQL:

ایندکس GiST :

ایندکسهای Generalized Search Tree (GiST) ساختارهای ایندکس چندمنظوره‌ای هستند که به جز مقادیر علمی ساده، انواع داده‌های متنوعی را نیز پشتیبانی می‌کنند.

ایندکس های GiST به جستجوی کارآمد و بازیابی برای ساختارهای پیچیده داده مانند اشیاء هندسی، اسناد متنی، آرایه‌ها و موارد دیگر امکان می‌دهند.

این ایندکس ها بر اساس مفهوم درخت‌های چندبُعدی عمل می‌کنند و امکان عملیات جستجوی انعطاف‌پذیر را فراهم می‌کنند.

ایندکسهای GiST قادرند با پیشوندهای جستجوی مختلف، از جمله برابری، بازه‌ای و عملیات‌های فضایی مانند همپوشانی، و جستجوهای مبتنی بر فاصله، مقابله کنند.

ایندکس SP-GiST:

ایندکسهای Space-Partitioned Generalized Search Tree (SP-GiST) یک گسترش از ایندکسهای GiST هستند که قابلیت‌های ایندکس گذاری را بهبود می‌بخشند.

ایندکسهای SP-GiST برای انواع داده‌هایی با ویژگی‌های پرکننده فضا طراحی شده‌اند، مانند داده‌های چندبُعدی، داده‌های سری زمانی و داده‌های شبکه.

آنها فضای ایندکس را به مناطق غیر همپوشانی تقسیم می‌کنند و عملکرد جستجو را برای الگوهای دسترسی خاص بهینه می‌کنند.

ایندکسهای SP-GiST پشتیبانی از انواع داده‌های متنوعی را ارائه می‌دهند، از جمله اشیاء هندسی، جستجوی متنی و موارد دیگر.

آنها به خصوص برای ایندکس گذاری فضایی بهینه هستند و قادر به مدیریتqueryهای فضایی پیچیده، از جمله تقاطع، نزدیک‌ترین همسایه و عملیات خوشه‌بندی هستند.

نتیجه‌گیری:

در این راهنمای سریع، نوع‌های دیگری از ایندکس هایی که توسط PostgreSQL پشتیبانی می‌شوند به جز ایندکس B-Tree را مشاهده کردیم.

توصیه نمی‌شود که قبل از اجرای یک کوئری یک‌باره، ایندکس را به صورت فوری ایجاد کنید. ایجاد یک ایندکس با طراحی خوب نیاز به برنامه‌ریزی و آزمایش دقیق دارد.

مهم است که در نظر بگیرید که ایندکس ها فضای دیسک را مصرف می‌کنند. همچنین، هرگاه ردیف‌های داده جدیدی وارد شوند یا ردیف‌های موجود به‌روزرسانی شوند، پایگاه داده به‌طور خودکار ورودی‌های ایندکس مربوطه را به‌روز می‌کند.

@code_crafters