PostgreSQL / Materialized View

Materialized View

Ердийн view нь дуудах болгонд асуулгыг дахин гүйцэтгэдэг. Хэрэв тухайн асуулга нарийн төвөгтэй, олон сая мөрд хамаарах бол дуудах болгонд хэдэн секунд хүлээх шаардлага гарна. Materialized view бол үр дүнг дискэнд бодитоор хадгалдаг тусгай view — асуулгыг дахин гүйцэтгэхгүй, хадгалсан өгөгдлийг шууд буцаадаг тул маш хурдан байдаг.

Ердийн view vs Materialized view

| | View | Materialized View | |---|---|---| | Өгөгдөл хадгалах | ❌ үгүй | ✅ тийм | | Хурд | асуулгаас хамаарна | маш хурдан | | Шинэ өгөгдөл | шууд | REFRESH хийх хүртэл хуучин | | Индекс тавих | ❌ боломжгүй | ✅ боломжтой |

Materialized view нь тайлан, dashboard, статистик зэрэг байнга уншигддаг ч ховор шинэчлэгддэг өгөгдөлд хамгийн тохиромжтой.

Materialized view үүсгэх

sql
-- Хэлтэс тус бүрийн цалингийн статистик
-- (нарийн төвөгтэй тооцоо гэж үзье)
CREATE MATERIALIZED VIEW department_salary_report AS
SELECT
    d.name                              AS хэлтэс,
    COUNT(e.id)                         AS ажилчдын_тоо,
    ROUND(AVG(e.salary), 0)             AS дундаж_цалин,
    SUM(e.salary)                       AS нийт_цалингийн_зардал,
    MAX(e.salary)                       AS дээд_цалин,
    MIN(e.salary)                       AS доод_цалин
FROM departments d
LEFT JOIN employees e ON e.department_id = d.id
GROUP BY d.id, d.name
ORDER BY нийт_цалингийн_зардал DESC
WITH DATA;  -- Одоохондоо өгөгдлийг хадгал

WITH DATA гэдэг нь үүсгэх үед тооцоог гүйцэтгэж дискэнд хадгална. WITH NO DATA гэвэл хоосон materialized view үүсгэж, дараа нь refresh хийнэ.

sql
-- Хурдан дуудах
SELECT * FROM department_salary_report;
-- Асуулга гүйцэтгэлгүй, дискнээс шууд уншина

REFRESH — өгөгдлийг шинэчлэх

Эх хүснэгтийн өгөгдөл өөрчлөгдсөн үед materialized view-г гараар шинэчлэх шаардлагатай:

sql
-- Шинэ ажилтан нэмэх
INSERT INTO employees (full_name, email, department_id, salary, hired_at)
VALUES ('Энхбаяр Содном', 'enkh@co.mn', 2, 2100000, '2024-06-01');

-- Одоохондоо materialized view хуучин өгөгдөлтэй байна
SELECT * FROM department_salary_report;
-- Санхүүгийн мөр шинэ ажилтныг тусгаагүй байна

-- Refresh хийх
REFRESH MATERIALIZED VIEW department_salary_report;

-- Одоо шинэ өгөгдөл харагдана
SELECT * FROM department_salary_report;

REFRESH CONCURRENTLY — хаалтгүй шинэчлэлт

Ердийн REFRESH нь шинэчлэлтийн үед хүснэгтийг түгжиж, уншилтыг хориглодог. CONCURRENTLY сонголт нь шинэчлэлтийн явцад ч уншилтыг зогсоохгүй:

sql
-- Unique индекс байгаа тохиолдолд л CONCURRENTLY ажиллана
CREATE UNIQUE INDEX idx_dept_report_name
    ON department_salary_report (хэлтэс);

-- Хэрэглэгчийг хориглохгүйгээр шинэчлэх
REFRESH MATERIALIZED VIEW CONCURRENTLY department_salary_report;

CONCURRENTLY ашиглахад unique индекс заавал байх ёстой. Үйлдвэрлэлийн системд ихэвчлэн CONCURRENTLY ашигладаг.

Materialized view-д индекс тавих

Ердийн view-ээс ялгаатай нь materialized view нь бодитой хадгалагдсан тул индекс тавьж болно:

sql
-- Хэлтсийн нэрт индекс
CREATE INDEX idx_dept_report_хэлтэс
    ON department_salary_report (хэлтэс);

-- Дундаж цалингаар эрэмбэлэн хайхад зориулсан индекс
CREATE INDEX idx_dept_report_дундаж
    ON department_salary_report (дундаж_цалин DESC);

Materialized view дээрх индекс нь ердийн хүснэгтийн индекстэй яг адил ажилладаг — хайлтыг улам хурдасгана.

Materialized view устгах

sql
DROP MATERIALIZED VIEW department_salary_report;

DROP MATERIALIZED VIEW IF EXISTS department_salary_report;

Дараагийн хичээлд:

SQL-г зөвхөн асуулт биш функц хэлбэрт оруулж, параметртэй, давтан ашиглагдах логик бичиж сурна.

← Өмнөх хичээлView үүсгэх ба ашиглахДараах хичээл →PostgreSQL функц бичих