Clustred and none clustred index in postgres 1

ایندکس های خوشه ای و غیر خوشه ای دو تکنیک مهم ایندکس کردن در پایگاه داده هستند که در PostgreSQL برای بهبود عملکرد کوئری های SQL استفاده می شوند. در اینجا ما پیاده سازی ایندکس های خوشه ای و غیر خوشه ای در PostgreSQL را توضیح خواهیم داد، نمونه ها و موارد استفاده واقعی ارائه خواهیم داد و نحوه عیب یابی عملکرد ایندکس در PostgreSQL را بحث خواهیم کرد.

ایندکس های خوشه ای در PostgreSQL:

ایندکس خوشه ای یک ساختار ایندکس است که ترتیب فیزیکی داده ها را بر روی دیسک تعیین می کند. در PostgreSQL، ایندکس خوشه ای با استفاده از مفهوم جدول خوشه ای یا جدول سازماندهی شده توسط ایندکس پیاده سازی می شود. هنگامی که یک ایندکس خوشه ای روی یک جدول ایجاد می شود، داده ها به صورت فیزیکی بر اساس مقادیر ستون(های) ایندکس شده مرتب می شوند. این می تواند عملکرد کوئری را بهبود بخشد، زیرا به سیستم پایگاه داده اجازه می دهد تا داده های مورد نیاز را کارآمدتر بازیابی کند.

مثال:

فرض کنید جدولی به نام sales حاوی اطلاعات مربوط به معاملات فروش است، از جمله ستونی به نام transaction_date که تاریخ هر معامله را ذخیره می کند. یک ایندکس خوشه ای روی این ستون می تواند به بهبود عملکرد کوئری هنگام جستجو برای معاملات در بازه زمانی خاص کمک کند.

ایندکس های غیر خوشه ای در PostgreSQL:

ایندکس غیر خوشه ای یک ساختار ایندکس است که ترتیب فیزیکی داده ها را روی دیسک تعیین نمی کند. در عوض، ایندکس غیر خوشه ای یک درخت جستجو است که حاوی یک لیست از مراجع به داده های جدول اصلی است. ایندکس های غیر خوشه ای اغلب برای بهبود عملکرد کوئری هایی استفاده می شوند که بر روی چندین ستون یا برای جدول هایی که به طور مکرر به روز می شوند، کوئری می شوند.

مثال:

فرض کنید جدول فروش شامل ستونی به نام customer_name نیز باشد. یک ایندکس غیر خوشه ای در این ستون می تواند به بهبود عملکرد کوئری هنگام جستجوی معاملات فروش برای یک مشتری خاص کمک کند.

CREATE INDEX sales_customer_name_idx ON sales (customer_name);

موارد استفاده از ایندکس های خوشه ای و غیر خوشه ای

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

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

به عنوان مثال، یک ایندکس خوشه ای روی ستون date جدول sales می تواند به بهبود عملکرد کوئری هایی که داده های فروش را برای یک تاریخ یا بازه زمانی خاص بازیابی می کنند کمک کند. یک ایندکس غیر خوشه ای روی ستون customer_name همان جدول می تواند به بهبود عملکرد کوئری هایی که داده های فروش را برای یک مشتری خاص بازیابی می کنند کمک کند.

عیب یابی عملکرد ایندکس

وقتی پایگاه داده PostgreSQL شما کند عمل می کند، یکی از اولین مکان هایی که باید بررسی کنید ایندکس های شما هستند. ایندکس ها به پایگاه داده کمک می کنند تا داده ها را سریع تر پیدا کند، اما اگر به درستی نگهداری نشوند، می توانند کند شوند.

چند راه برای عیب یابی عملکرد ایندکس وجود دارد:

از دستور EXPLAIN استفاده کنید تا ببینید پایگاه داده چگونه کوئری های شما را اجرا می کند. اگر EXPLAIN نشان می دهد که پایگاه داده در حال اسکن کامل جدول برای یافتن داده ها است، این می تواند نشانه آن باشد که ایندکس های شما کار نمی کنند.

از نمای (view ) pg_stat_statements استفاده کنید تا ببینید کدام کوئری ها کندترین هستند. اگر یک کوئری کند را شناسایی کردید، می‌توانید از دستور EXPLAIN برای عیب‌یابی عملکرد ایندکس آن کوئری استفاده کنید.

عملیات نگهداری ایندکس را به طور مرتب انجام دهید. این به پایگاه داده کمک می کند تا ایندکس های خود را بهینه نگه دارد و سریع عمل کند.

اگر هنوز مشکل دارید، ممکن است لازم باشد عملکرد پایگاه داده خود را بهینه کنید. این شامل بررسی پیکربندی سرور، استفاده از حافظه و I/O دیسک است.

اگر در عیب یابی عملکرد ایندکس یا بهینه سازی عملکرد پایگاه داده خود مشکل دارید، می توانید از کمک یک DBA PostgreSQL استفاده کنید.

به صورت کلی :

ایندکس های خوشه ای و غیر خوشه ای دو تکنیک مهم ایندکس گذاری پایگاه داده هستند که می توانند به بهبود عملکرد کوئری های SQL در PostgreSQL کمک کنند. با ایجاد و بهینه سازی ایندکس ها بر اساس مورد استفاده و بار کاری خاص، DBA های PostgreSQL می توانند اطمینان حاصل کنند که سیستم پایگاه داده عملکرد مطلوبی دارد و نیازهای عملکرد و مقیاس پذیری کسب و کار را برآورده می کند.

توضیحات پستگرس : خوشه - یک جدول را بر اساس یک ایندکس خوشه بندی کنید

خلاصه ماجرا :

CLUSTER [VERBOSE] table_name [ USING index_name ]

CLUSTER ( option [, ...] ) table_name [ USING index_name ]

CLUSTER [VERBOSE]

where option can be one of:

VERBOSE [ boolean ]

توضیحات :

فرمان CLUSTER به PostgreSQL دستور می دهد تا جدول مشخص شده توسط table_name را بر اساس ایندکس مشخص شده توسط index_name خوشه بندی کند. ایندکس باید قبلاً روی table_name تعریف شده باشد.

هنگامی که یک جدول خوشه بندی می شود، بر اساس اطلاعات ایندکس به صورت فیزیکی بازسازی می شود. خوشه بندی یک عملیات یکباره است: هنگامی که جدول بعداً به روز می شود، تغییرات خوشه بندی نمی شوند. یعنی تلاشی برای ذخیره سطرهای جدید یا به روز شده بر اساس ترتیب ایندکس آنها انجام نمی شود. (اگر مایل باشید، می توانید با صدور مجدد فرمان، به طور دوره ای خوشه بندی کنید. همچنین، تنظیم پارامتر ذخیره سازی fillfactor جدول به کمتر از 100% می تواند به حفظ ترتیب خوشه بندی در طول به روز رسانی ها کمک کند، زیرا سطرهای به روز شده در صورت وجود فضای کافی در همان صفحه نگه داشته می شوند.)

هنگامی که یک جدول خوشه بندی می شود، PostgreSQL به یاد می آورد که بر اساس کدام ایندکس خوشه بندی شده است. فرم CLUSTER table_name جدول را با استفاده از همان ایندکس قبلی خوشه بندی می کند. همچنین می توانید از فرم های CLUSTER یا SET WITHOUT CLUSTER از ALTER TABLE برای تنظیم ایندکس استفاده کنید که برای عملیات خوشه بندی آینده استفاده شود، یا برای پاک کردن هر تنظیم قبلی.

فرمان CLUSTER بدون table_name همه جداول خوشه بندی شده قبلی را در پایگاه داده فعلی که کاربر فراخواننده مالک آن است، یا همه جداول مشابه را در صورت فراخوانی توسط یک کاربر فوق العاده، خوشه بندی می کند. این فرم از CLUSTER نمی تواند در داخل یک بلوک تراکنش اجرا شود.

هنگامی که یک جدول در حال خوشه بندی است، یک قفل ACCESS EXCLUSIVE روی آن اعمال می شود. این از اجرای سایر عملیات پایگاه داده (هم خواندن و هم نوشتن) روی جدول تا زمانی که CLUSTER به پایان برسد، جلوگیری می کند.

@code_crafters