В чем разница между View и Materialized View?

# Разница между View и Materialized View в БД

Esli раньше я писал SQL в основном через ORM, то теперь часто работаю с сырым SQL и views. Это важно для оптимизации больших систем.

Определения

View (Виртуальное представление)

View — это сохранённый SQL запрос, который выглядит как таблица.

-- Создание View
CREATE VIEW active_users AS
SELECT id, name, email, created_at
FROM users
WHERE is_active = true AND deleted_at IS NULL;

-- Использование (выглядит как таблица)
SELECT * FROM active_users;
SELECT COUNT(*) FROM active_users;

Materialized View (Материализованное представление)

Materialized View — это сохранённый результат запроса, который хранится физически.

-- Создание Materialized View (PostgreSQL)
CREATE MATERIALIZED VIEW active_users_materialized AS
SELECT id, name, email, created_at
FROM users
WHERE is_active = true AND deleted_at IS NULL;

-- Использование (тоже выглядит как таблица)
SELECT * FROM active_users_materialized;

-- Обновление (физически переговаривает данные)
REFRESH MATERIALIZED VIEW active_users_materialized;

Основные различия

View — виртуальное представление

-- Пример 1: Простой View
CREATE VIEW user_order_count AS
SELECT 
    u.id, 
    u.name, 
    COUNT(o.id) as order_count
FROM users u
LEFT JOIN orders o ON u.id = o.user_id
GROUP BY u.id, u.name;

-- При каждом SELECT считается заново
SELECT * FROM user_order_count WHERE order_count > 10;
-- Это эквивалентно:
SELECT 
    u.id, 
    u.name, 
    COUNT(o.id) as order_count
FROM users u
LEFT JOIN orders o ON u.id = o.user_id
GROUP BY u.id, u.name
WHERE COUNT(o.id) > 10;

Плюсы View

-- 1. Всегда актуальные данные
INSERT INTO orders (user_id, amount) VALUES (1, 100);
SELECT order_count FROM user_order_count WHERE id = 1; -- Сразу видно новый заказ

-- 2. Экономия памяти
CREATE VIEW high_value_customers AS
SELECT * FROM users
WHERE total_spent > 10000;
-- Никакой extra память не требуется

-- 3. Безопасность
CREATE VIEW public_users AS
SELECT id, name, bio FROM users; -- Скрываем email и phone
GRANT SELECT ON public_users TO public_role;

-- 4. Простота использования
CREATE VIEW order_summary AS
SELECT 
    DATE(created_at) as order_date,
    COUNT(*) as total_orders,
    SUM(amount) as total_amount
FROM orders
GROUP BY DATE(created_at);

Минусы View

-- 1. Производительность с большим количеством данных
CREATE VIEW expensive_query AS
SELECT 
    u.id, u.name,
    COUNT(DISTINCT o.id) as orders,
    COUNT(DISTINCT r.id) as reviews,
    AVG(r.rating) as avg_rating
FROM users u
LEFT JOIN orders o ON u.id = o.user_id
LEFT JOIN reviews r ON u.id = r.user_id
LEFT JOIN order_items oi ON o.id = oi.order_id
LEFT JOIN products p ON oi.product_id = p.id
WHERE u.created_at > '2020-01-01'
GROUP BY u.id, u.name;

-- ОЧЕНЬ медленно при миллионах пользователей!
-- Считается заново при каждом SELECT

-- 2. Нельзя индексировать
-- SELECT * FROM expensive_query WHERE id = 1 — всё равно полный скан

Materialized View — материализованное представление

-- Создание Materialized View (PostgreSQL)
CREATE MATERIALIZED VIEW user_analytics AS
SELECT 
    u.id,
    u.name,
    COUNT(DISTINCT o.id) as total_orders,
    SUM(o.amount) as total_spent,
    AVG(o.amount) as avg_order_value,
    MAX(o.created_at) as last_order_date
FROM users u
LEFT JOIN orders o ON u.id = o.user_id
GROUP BY u.id, u.name;

-- Создание индекса на materialized view
CREATE INDEX idx_user_analytics_id ON user_analytics(id);
CREATE INDEX idx_user_analytics_spent ON user_analytics(total_spent DESC);

-- Быстрый поиск (использует индекс)
SELECT * FROM user_analytics 
WHERE total_spent > 5000 
ORDER BY total_spent DESC; -- O(log n) с индексом

Обновление Materialized View

-- Полное обновление (переписывает весь MV)
REFRESH MATERIALIZED VIEW user_analytics;
-- Это может занять минуты на больших таблицах!

-- Обновление без блокирования (PostgreSQL 9.5+)
REFRESH MATERIALIZED VIEW CONCURRENTLY user_analytics;
-- Создаёт временный результат, потом заменяет (требует UNIQUE INDEX)

-- В MySQL (есть workaround)
DROP TABLE IF EXISTS user_analytics_temp;
CREATE TABLE user_analytics_temp AS
SELECT ... (запрос здесь);
RENAME TABLE user_analytics TO user_analytics_old;
RENAME TABLE user_analytics_temp TO user_analytics;
DROP TABLE user_analytics_old;

Плюсы Materialized View

-- 1. Супер быстрый поиск
SELECT * FROM user_analytics WHERE id = 123; -- O(1) с индексом

-- 2. Сложные аналитики
-- Может содержать дорогостоящие GROUP BY, JOIN'ы
CREATE MATERIALIZED VIEW daily_revenue_report AS
SELECT 
    DATE(o.created_at) as sale_date,
    p.category,
    SUM(o.amount) as daily_revenue,
    COUNT(*) as num_orders,
    COUNT(DISTINCT u.id) as unique_customers
FROM orders o
JOIN order_items oi ON o.id = oi.order_id
JOIN products p ON oi.product_id = p.id
JOIN users u ON o.user_id = u.id
GROUP BY DATE(o.created_at), p.category;
-- Может занять 5 минут на 100M строк
-- Но SELECT'ы будут мгновенные!

-- 3. Отличается для аналитики (OLAP)
-- Data warehouse часто использует materialized view'ы

Минусы Materialized View

-- 1. Устаревают данные
INSERT INTO orders ... ; -- Новый заказ
SELECT * FROM user_analytics; -- Старые данные!
-- Нужно REFRESH MATERIALIZED VIEW

-- 2. Требует дополнительную память
-- Если MV занимает 100GB, это 100GB доп. памяти

-- 3. Сложность управления refresh'ем
CREATE EVENT refresh_analytics
ON SCHEDULE EVERY 1 HOUR
DO REFRESH MATERIALIZED VIEW user_analytics;
-- Нужно координировать refresh'ем

-- 4. Конфликты при обновлении
-- Во время REFRESH читать нельзя (в некоторых БД)

Практические примеры

Пример 1: использовать VIEW

-- Случай: Нужен быстрый доступ к отфильтрованным данным
-- Фильтр меняется редко или не важна абсолютная свежесть

CREATE VIEW premium_users_view AS
SELECT id, name, email, subscription_level
FROM users
WHERE subscription_level IN ('gold', 'platinum')
AND status = 'active';

-- Используем так
SELECT COUNT(*) FROM premium_users_view;
-- Быстро, но считается каждый раз

Пример 2: использовать MATERIALIZED VIEW

-- Случай: Сложный отчёт, который считается долго
-- Нужен часто, свежесть не критична

CREATE MATERIALIZED VIEW monthly_sales_report AS
SELECT 
    DATE_TRUNC('month', o.created_at)::DATE as month,
    c.country,
    c.region,
    SUM(o.amount) as total_sales,
    COUNT(*) as num_orders,
    AVG(o.amount) as avg_order
FROM orders o
JOIN customers c ON o.customer_id = c.id
JOIN order_items oi ON o.id = oi.order_id
JOIN products p ON oi.product_id = p.id
WHERE o.status = 'completed'
GROUP BY DATE_TRUNC('month', o.created_at), c.country, c.region;

CREATE INDEX idx_msr_country ON monthly_sales_report(country);
CREATE INDEX idx_msr_month ON monthly_sales_report(month);

-- Обновляем раз в день (ночью)
CREATE EVENT refresh_monthly_report
ON SCHEDULE EVERY 1 DAY STARTS '02:00:00'
DO REFRESH MATERIALIZED VIEW monthly_sales_report;

-- Используем в дашборде
SELECT * FROM monthly_sales_report WHERE country = 'USA'; -- Мгновенно!

В Java / ORM контексте

// С JPA можно работать с view'ами как с обычными сущностями
@Entity
@Table(name = "user_analytics") // Это materialized view
public class UserAnalytics {
    @Id
    private Long userId;
    private String userName;
    private Long totalOrders;
    private BigDecimal totalSpent;
    private BigDecimal avgOrderValue;
}

// Использование
@Repository
public interface UserAnalyticsRepository 
        extends JpaRepository<UserAnalytics, Long> {
    List<UserAnalytics> findByTotalSpentGreaterThan(BigDecimal amount);
}

// В сервисе
@Service
public class ReportService {
    
    @Autowired
    private UserAnalyticsRepository analyticsRepo;
    
    public List<UserAnalytics> getTopSpenders() {
        // SELECT быстрый, потому что это materialized view с индексом
        return analyticsRepo.findByTotalSpentGreaterThan(
            BigDecimal.valueOf(5000)
        );
    }
}

Когда что использовать

УСЕ VIEW когда:
✓ Нужны всегда актуальные данные
✓ Данные меняются часто (INSERT/UPDATE)
✓ Хочешь скрыть сложность SQL
✓ Нет проблем с производительностью
✓ Экономишь память

УСЕ MATERIALIZED VIEW когда:
✓ Данные редко обновляются
✓ Запрос считается очень долго
✓ Нужна высокая производительность SELECT
✓ Можешь позволить себе lag в данных
✓ Есть память для хранения результата
✓ Аналитика и отчёты (OLAP)

Итог

• View = живое окно в данные (динамическое, свежее, медленнее)
• Materialized View = кэшированный снимок (статичное, устаревает, быстрое)

В OLTP системах (транзакции) → View. В OLAP системах (аналитика) → Materialized View.