Materialized Query Tables

Materialized Query Tables (MQT) — это объекты базы данных, представляющие собой предварительно вычисленные и сохранённые на диске результаты выполнения запроса (обычно содержащего операции соединения, агрегации и фильтрации). MQT относятся к классу материализованных представлений (materialized views) и используются в системах управления базами данных (СУБД) для ускорения выполнения сложных аналитических запросов за счёт замены повторного вычисления дорогостоящих операций чтением готовых данных.

Определение и сущность

MQT представляют собой физические таблицы, содержимое которых определяется запросом, указанным при их создании. В отличие от обычных представлений (views), которые являются виртуальными и вычисляются каждый раз при обращении, MQT хранят данные на диске. Это позволяет значительно сократить время выполнения запросов, особенно в системах поддержки принятия решений (DSS) и хранилищах данных, где типичны запросы с агрегацией по большим объёмам данных.

Ключевая особенность MQT — способность СУБД автоматически (прозрачно для пользователя) перенаправлять запросы на чтение данных из MQT, если оптимизатор определяет, что это эквивалентно исходному запросу и выгоднее по производительности. Этот механизм называется «сопоставлением запросов» (query matching) или «замещением запросов» (query rewrite).

История

Концепция материализованных представлений была впервые предложена в академических исследованиях в 1980-х годах, однако практическая реализация в коммерческих СУБД началась в 1990-х. Одним из первых крупных внедрений стала СУБД DB2 Universal Database компании IBM, где в версии 7 (1999 год) была представлена технология под названием Materialized Query Tables.

В дальнейшем аналогичные механизмы были реализованы в других СУБД, таких как Oracle (с 9i, 2001 год — материализованные представления с возможностью перезаписи запросов), Microsoft SQL Server (с 2008 года — индексированные представления, indexed views), PostgreSQL (с версии 9.3, 2013 год — материализованные представления). В современных СУБД, таких как Amazon Redshift, Google BigQuery, Snowflake, также существуют аналогичные сущности (например, материализованные представления в Snowflake).

Классификация

MQT можно классифицировать по нескольким признакам:

По способу обновления (refresh)

• Немедленное обновление (immediate refresh): MQT обновляется синхронно с транзакцией, изменяющей базовые таблицы. Обеспечивает высокую согласованность данных, но может снижать производительность операций записи.
• Отложенное обновление (deferred refresh): MQT обновляется по расписанию или по команде пользователя (например, REFRESH TABLE). Данные могут быть устаревшими, но нагрузка на запись минимальна.
• Инкрементальное обновление (incremental refresh): Обновляются только те строки MQT, которые были затронуты изменениями в базовых таблицах. Требует поддержки журналов изменений и более сложен в реализации, но эффективен при больших объёмах данных.
• Полное перестроение (full rebuild): MQT удаляется и создаётся заново. Простой, но ресурсоёмкий метод.

По типу запроса

• Агрегирующие MQT: Содержат результат выполнения запроса с группировкой (GROUP BY) и агрегатными функциями (SUM, COUNT, AVG и др.). Наиболее распространённый тип.
• Соединяющие MQT: Содержат результат соединения нескольких таблиц (JOIN). Ускоряют запросы с множественными соединениями.
• Фильтрующие MQT: Содержат результат запроса с условием WHERE. Позволяют быстро получать данные по определённому сегменту.

По прозрачности для пользователя

• Прозрачные (transparent): СУБД автоматически перенаправляет запросы на MQT. Пользователь не управляет этим процессом.
• Непрозрачные (non-transparent): Пользователь должен явно обращаться к MQT в запросе.

Устройство и характеристики

MQT создаются с помощью оператора CREATE TABLE (или его аналога) с указанием запроса-источника. В разных СУБД синтаксис различается.

Пример для DB2 (упрощённо): ``sql CREATE TABLE sales_summary AS (SELECT product_id, region, SUM(amount) AS total_sales FROM sales GROUP BY product_id, region) DATA INITIALLY DEFERRED REFRESH DEFERRED; ``

Основные характеристики MQT:

• Размер: MQT может занимать значительное дисковое пространство, особенно при большом количестве уникальных комбинаций в GROUP BY или при полной денормализации.
• Время создания: Построение MQT требует времени, так как выполняется запрос-источник.
• Время обновления: Зависит от метода обновления и объёма изменений.
• Влияние на производительность записи: При немедленном обновлении каждая операция INSERT/UPDATE/DELETE в базовых таблицах приводит к обновлению MQT, что может замедлять запись.

Применение

MQT широко применяются в следующих областях:

• Хранилища данных и системы бизнес-аналитики (BI): Ускорение отчётов с агрегацией по времени, региону, продукту. Например, отчёт о продажах по месяцам может быть заменён чтением из MQT, где суммы уже предварительно вычислены.
• Системы поддержки принятия решений (DSS): Обработка сложных аналитических запросов, включающих множество соединений и фильтров.
• Информационные панели (dashboards): Обеспечение быстрого отклика для визуализации данных.
• Обработка данных в реальном времени (с осторожностью): При использовании немедленного обновления MQT могут применяться в системах, где требуется актуальность данных, но допустима некоторая задержка на запись.

Примеры

Пример 1: Ускорение отчёта по продажам

Базовый запрос: ``sql SELECT region, SUM(amount) AS total_sales FROM sales GROUP BY region; ``

MQT для этого запроса будет содержать одну строку на каждый регион. При выполнении исходного запроса оптимизатор может перенаправить его на MQT, что ускорит выполнение в десятки и сотни раз при большом объёме таблицы sales.

Пример 2: Ускорение соединения таблиц

Базовый запрос: ``sql SELECT c.customer_name, o.order_date, o.total FROM customers c JOIN orders o ON c.customer_id = o.customer_id WHERE c.country = 'Россия'; ``

MQT, содержащий результат этого соединения, позволит быстро получать данные о заказах российских клиентов без повторного соединения таблиц.

Критика и ограничения

• Устаревание данных (stale data): При отложенном обновлении данные в MQT могут не соответствовать текущему состоянию базовых таблиц. Это неприемлемо для систем, требующих строгой актуальности.
• Избыточное использование дискового пространства: Хранение нескольких MQT может привести к значительному расходу диска.
• Сложность управления: Необходимо отслеживать актуальность MQT, выбирать стратегию обновления, анализировать, какие MQT действительно используются.
• Нагрузка на запись: Немедленное обновление может снизить производительность операций DML (INSERT, UPDATE, DELETE) на базовых таблицах.
• Ограничения оптимизатора: Не все запросы могут быть автоматически перенаправлены на MQT. Оптимизатор может не найти эквивалентного MQT или принять неверное решение о его использовании.

Источники

1. Elmasri, R., Navathe, S. B. Fundamentals of Database Systems. 7th ed. — Pearson, 2016. — Глава 25: Materialized Views.
2. IBM Knowledge Center. Materialized Query Tables (MQTs). — IBM Corporation, 2023.
3. Oracle Database Online Documentation. Materialized Views. — Oracle Corporation, 2023.
4. Microsoft SQL Server Documentation. Indexed Views. — Microsoft Corporation, 2023.
5. PostgreSQL Documentation. Materialized Views. — The PostgreSQL Global Development Group, 2023.