Materialized View

مثال عددی برای دید مادی (Materialized View)

فرض کنید یک فروشگاه آنلاین داریم که جدولی به نام sales با ساختار زیر دارد:

می‌خواهیم یک دید مادی ایجاد کنیم که مجموع فروش هر محصول در هر ماه را نشان دهد.

ساخت دید مادی:

CREATE MATERIALIZED VIEW monthly_sales AS
SELECT product_id,
       TO_CHAR(sale_date, 'YYYY-MM') AS sale_month,
       SUM(amount) AS total_sales
FROM sales
GROUP BY product_id, sale_month;

پس از اجرای این دستور، دید مادی monthly_sales ایجاد می‌شود و داده‌های زیر را شامل می‌شود:

مزایای استفاده از این دید مادی:

• بهبود عملکرد پرس و جو: اگر بخواهیم به صورت مکرر مجموع فروش هر محصول در هر ماه را محاسبه کنیم، به جای اجرای پرسجوی GROUP BY پیچیده، می‌توانیم به سادگی از دید مادی monthly_sales استفاده کنیم که بسیار سریع‌تر خواهد بود.
• ساده‌سازی پرس و جو: پرس و جو برای بدست آوردن مجموع فروش یک محصول در ماه خاصی بسیار ساده‌تر می‌شود.
• افزایش خوانایی: دید مادی به عنوان یک جدول مجازی عمل می‌کند و خوانایی پرس و جوها را افزایش می‌دهد.

مثال استفاده از دید مادی:

برای پیدا کردن مجموع فروش محصول با شناسه 101 در ماه نوامبر 2023:

SELECT total_sales
FROM monthly_sales
WHERE product_id = 101 AND sale_month = '2023-11';

توجه:

• برای به‌روزرسانی داده‌های دید مادی، از دستور REFRESH MATERIALIZED VIEW استفاده می‌شود.
• می‌توان شاخص‌هایی روی دید مادی ایجاد کرد تا عملکرد پرس و جوها بیشتر بهبود یابد.
• برای دیدهای مادی پیچیده، بهتر است از ابزارهای تجزیه و تحلیل عملکرد برای ارزیابی عملکرد و تنظیم پارامترهای آن استفاده شود.

مثال دیگری با شرط:

فرض کنید می‌خواهیم دید مادی‌ای داشته باشیم که فقط فروش محصولات با قیمت بالای 100 دلار را نشان دهد:

CREATE MATERIALIZED VIEW high_value_sales AS
SELECT product_id,
       TO_CHAR(sale_date, 'YYYY-MM') AS sale_month,
       SUM(amount) AS total_sales
FROM sales
WHERE amount > 100
GROUP BY product_id, sale_month;

این مثال‌ها نشان می‌دهند که دیدهای مادی چقدر می‌توانند در بهبود عملکرد پایگاه داده و ساده‌سازی پرس و جوها موثر باشند.

CREATE MATERIALIZED VIEW monthly_sales AS
SELECT product_id,
       TO_CHAR(sale_date, 'YYYY-MM') AS sale_month,
       SUM(amount) AS total_sales
FROM sales
GROUP BY product_id, sale_month;

شاخص‌ها در دیدهای مادی (Materialized Views)

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

چرا به شاخص در دیدهای مادی نیاز داریم؟

• بهبود عملکرد پرس و جوها: شاخص‌ها به پایگاه داده کمک می‌کنند تا به سرعت سطرهایی را که با شرط WHERE یک پرس و جو مطابقت دارند، پیدا کند.
• سرعت بخشیدن به عملیات مرتب‌سازی: اگر اغلب روی داده‌های دید مادی عملیات مرتب‌سازی انجام می‌دهید، ایجاد شاخص روی ستون‌هایی که بر اساس آن‌ها مرتب‌سازی انجام می‌شود، می‌تواند بسیار مفید باشد.
• بهبود عملکرد عملیات گروه‌بندی: اگر اغلب از توابع جمع‌شدنی مانند SUM, AVG و COUNT بر روی دید مادی استفاده می‌کنید، ایجاد شاخص روی ستون‌هایی که در عبارت GROUP BY استفاده می‌شوند، می‌تواند عملکرد را بهبود بخشد.

انواع شاخص‌ها در دیدهای مادی

• شاخص‌های B-tree: رایج‌ترین نوع شاخص هستند و برای جستجوهای برابری، بزرگتر از، کوچکتر از و محدوده استفاده می‌شوند.
• شاخص‌های Bitmap: برای ستون‌هایی با تعداد مقادیر منحصر به فرد کم بسیار مناسب هستند.
• شاخص‌های ترکیبی: برای جستجوهای بر اساس چندین ستون به طور همزمان استفاده می‌شوند.
• شاخص‌های فضایی: برای داده‌های مکانی استفاده می‌شوند.

مثال

فرض کنید دید مادی monthly_sales را داریم که مجموع فروش هر محصول در هر ماه را نشان می‌دهد:

CREATE MATERIALIZED VIEW monthly_sales AS
SELECT product_id,
       TO_CHAR(sale_date, 'YYYY-MM') AS sale_month,
       SUM(amount) AS total_sales
FROM sales
GROUP BY product_id, sale_month;

اگر اغلب می‌خواهیم محصولات با فروش بیش از یک مقدار خاص را پیدا کنیم، می‌توانیم یک شاخص روی ستون total_sales ایجاد کنیم:

CREATE INDEX idx_monthly_sales_total_sales ON monthly_sales(total_sales);

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

چه زمانی باید شاخص ایجاد کنیم؟

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

نکات مهم

• زیاده‌روی در ایجاد شاخص‌ها: ایجاد شاخص‌های زیاد می‌تواند عملکرد پایگاه داده را کاهش دهد، زیرا ایجاد و نگهداری شاخص‌ها هزینه دارد.
• انتخاب ستون‌های مناسب: برای ایجاد شاخص، ستون‌هایی را انتخاب کنید که اغلب در شرط‌های WHERE، ORDER BY و GROUP BY استفاده می‌شوند.
• اندازه شاخص‌ها: اندازه شاخص‌ها می‌تواند بر عملکرد پایگاه داده تأثیر بگذارد.
• به‌روزرسانی شاخص‌ها: وقتی داده‌های دید مادی تغییر می‌کنند، شاخص‌ها نیز باید به‌روزرسانی شوند.

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

ابزارها و روش‌های مدیریت دیدهای مادی (Materialized View)

دیدهای مادی ابزاری قدرتمند برای بهبود عملکرد پرس‌و‌جوها هستند، اما مدیریت مؤثر آن‌ها نیز اهمیت زیادی دارد. در این بخش، به برخی از ابزارها و روش‌های مدیریت دیدهای مادی می‌پردازیم.

۱. دستورات SQL برای مدیریت:

• ایجاد دید مادی:
  SQL

  CREATE MATERIALIZED VIEW view_name AS
  SELECT ...;
  

• بروزرسانی دید مادی:
  SQL

  REFRESH MATERIALIZED VIEW view_name;
  

• حذف دید مادی:
  SQL

  DROP MATERIALIZED VIEW view_name;
  

• ایجاد شاخص روی دید مادی:
  SQL

  CREATE INDEX idx_view_name ON view_name (column1, column2);
  

۲. ابزارهای مدیریت پایگاه داده:

• pgAdmin: یک ابزار گرافیکی محبوب برای مدیریت پایگاه داده PostgreSQL است که امکان ایجاد، ویرایش و مدیریت دیدهای مادی را به صورت بصری فراهم می‌کند.
• PostgreSQL command-line tools: ابزارهای خط فرمان مانند psql برای اجرای دستورات SQL و مدیریت دیدهای مادی استفاده می‌شوند.
• ابزارهای ORM (Object-Relational Mapping): فریم‌ورک‌های ORM مانند SQLAlchemy (Python) یا Hibernate (Java) امکان تعامل با پایگاه داده و مدیریت دیدهای مادی را به صورت برنامه‌نویسی فراهم می‌کنند.

۳. روش‌های اتوماسیون:

• تریگرها: برای به‌روزرسانی خودکار دید مادی پس از ایجاد یا تغییر داده‌ها در جداول پایه استفاده می‌شوند.
• Jobs: در برخی پایگاه داده‌ها، می‌توان وظایفی (Jobs) تعریف کرد که به صورت دوره‌ای دیدهای مادی را به‌روزرسانی کنند.
• ابزارهای برنامه‌نویسی: با استفاده از زبان‌های برنامه‌نویسی مانند Python، می‌توان اسکریپت‌هایی نوشت که به صورت خودکار دیدهای مادی را مدیریت کنند.

۴. ملاحظات مهم در مدیریت دیدهای مادی:

• فرکانس به‌روزرسانی: بر اساس میزان تغییرات در داده‌های پایه، فرکانس مناسب برای به‌روزرسانی دید مادی را تعیین کنید.
• زمانبندی: برای به‌روزرسانی در ساعات کم‌بار سیستم برنامه‌ریزی کنید تا تأثیر بر عملکرد سیستم به حداقل برسد.
• شاخص‌ها: ایجاد شاخص‌های مناسب روی دید مادی می‌تواند عملکرد پرس‌و‌جوها را بهبود بخشد.
• فضای دیسک: دیدهای مادی فضای دیسک بیشتری را نسبت به جداول مجازی معمولی اشغال می‌کنند.
• پیچیدگی پرس‌و‌جو: پیچیدگی پرس‌و‌جوی تعریف شده برای دید مادی بر عملکرد به‌روزرسانی و پرس‌و‌جو تأثیر می‌گذارد.

مثال عملی:

فرض کنید یک فروشگاه آنلاین داریم و می‌خواهیم یک دید مادی ایجاد کنیم که مجموع فروش هر محصول در هر ماه را نشان دهد.

CREATE MATERIALIZED VIEW monthly_sales AS
SELECT product_id,
       TO_CHAR(sale_date, 'YYYY-MM') AS sale_month,
       SUM(amount) AS total_sales
FROM sales
GROUP BY product_id, sale_month;

برای به‌روزرسانی خودکار این دید مادی پس از هر تغییر در جدول sales، می‌توانیم یک تریگر تعریف کنیم.

CREATE TRIGGER update_monthly_sales
AFTER INSERT OR UPDATE OR DELETE ON sales
FOR EACH ROW
EXECUTE PROCEDURE refresh_matview('monthly_sales');

در این مثال، refresh_matview یک تابع سفارشی است که برای به‌روزرسانی دید مادی تعریف شده است.

جمع‌بندی

مدیریت مؤثر دیدهای مادی به شما کمک می‌کند تا از مزایای این ویژگی به طور کامل بهره‌مند شوید. با استفاده از ابزارها و روش‌های مناسب، می‌توانید عملکرد پایگاه داده خود را بهبود بخشیده و دسترسی به داده‌های خلاصه‌ شده را تسهیل کنید.