Синхронное материализованное представление
Эта тема описывает, как создавать, использовать и управлять синхронным материализованным представлением (Rollup).
Для синхронного материализованного представления все изменения в базовой таблице одновременно обновляются в соответствующих синхронных материализованных представлениях. Обновление синхронного материализованного представления запускается автоматически. Синхронные материализованные представления значительно недороги в обслуживании и обновлении, что делает их подходящими для прозрачного ускорения запросов в реальном времени с агрегацией по одной таблице.
Синхронные материализованные представления в Selena могут быть созданы только на одной базовой таблице из каталога по умолчанию. По сути, они представляют собой специальный индекс для ускорения запросов, а не физическую таблицу, как асинхронные материализованные представления.
Начиная с версии v1.5.2, Selena предоставляет асинхронные материализованные представления, которые поддерживают создание на нескольких таблицах и большее количество операторов агрегации. Для использования асинхронных материализованных представлений см. Асинхронное материализованное представление.
- Синхронные материализованные представления поддерживают условия WHERE начиная с версии v1.5.2.
- Синхронные материализованные представления поддерживаются в shared-data кластерах начиная с версии v1.5.2.
Следующая таблица сравнивает асинхронные материализованные представления (ASYNC MV) в Selena v1.5.2 и синхронное материализованное представление (SYNC MV) с точки зрения поддерживаемых функций:
| Агрегация по одной таблице | Соединение нескольких таблиц | Переписывание запросов | Стратегия обновления | Базовая таблица | |
|---|---|---|---|---|---|
| ASYNC MV | Да | Да | Да |
| Несколько таблиц из:
|
| SYNC MV (Rollup) | Ограниченный выбор агрегатных функц ий | Нет | Да | Синхронное обновление во время загрузки данных | Одна таблица в каталоге по умолчанию |
Базовые концепции
-
Базовая таблица
Базовые таблицы являются исходными таблицами материализованного представления.
Для синхронных материализованных представлений Selena базовые таблицы должны быть одной нативной таблицей из каталога по умолчанию. Selena поддерживает создание синхронных материализованных представлений на таблицах Duplicate Key и Aggregate.
-
Обновление
Синхронное материализованное представление обновляется автоматически при каждом изменении данных в базовой таблице. Вам не нужно запускать обновление вручную.
-
Переписывание запросов
Переписывание запросов означает, что при выполнении запроса к базовым таблицам с построенными на них материализованными представлениями система автоматически определяет, можно ли использовать предварительно вычисленные результаты в материализованном представлении для запроса. Если они могут быть использованы, система загрузит данные непосредственно из соответствующего материализованного представления, чтобы избежать затратных по времени и ресурсам вычислений или соединений.
Синхронные материализованные представления поддерживают переписывание запросов на основе некоторых агрегатных операторов. Для получения дополнительной информации см. Соответствие агрегатных функций.
Подготовка
Перед созданием синхронного материализованного представления проверьте, подходит ли ваше хранилище данных для ускорения запросов с помощью синхронных материализованных представлений. Например, проверьте, используют ли запросы определенные подзапросы повторно.
Следующий пример основан на таблице sales_records, которая содержит идентификатор транзакции record_id, ид ентификатор продавца seller_id, идентификатор магазина store_id, дату sale_date и сумму продажи sale_amt для каждой транзакции. Выполните следующие шаги для создания таблицы и вставки данных в нее:
CREATE TABLE sales_records(
record_id INT,
seller_id INT,
store_id INT,
sale_date DATE,
sale_amt BIGINT
) DISTRIBUTED BY HASH(record_id);
INSERT INTO sales_records
VALUES
(001,01,1,"2022-03-13",8573),
(002,02,2,"2022-03-14",6948),
(003,01,1,"2022-03-14",4319),
(004,03,3,"2022-03-15",8734),
(005,03,3,"2022-03-16",4212),
(006,02,2,"2022-03-17",9515);
Бизнес-сценарий этого примера требует частого анализа сумм продаж различных магазинов. В результате функция sum() используется в каждом запросе, потребляя огромное количество вычислительных ресурсов. Вы можете выполнить запрос, чтобы записать его время, и просмотреть его профиль запроса с помощью команды EXPLAIN.
MySQL > SELECT store_id, SUM(sale_amt)
FROM sales_records
GROUP BY store_id;
+----------+-----------------+
| store_id | sum(`sale_amt`) |
+----------+-----------------+
| 2 | 16463 |
| 3 | 12946 |
| 1 | 12892 |
+----------+-----------------+
3 rows in set (0.02 sec)
MySQL > EXPLAIN SELECT store_id, SUM(sale_amt)
FROM sales_records
GROUP BY store_id;
+-----------------------------------------------------------------------------+
| Explain String |
+-----------------------------------------------------------------------------+
| PLAN FRAGMENT 0 |
| OUTPUT EXPRS:3: store_id | 6: sum |
| PARTITION: UNPARTITIONED |
| |
| RESULT SINK |
| |
| 4:EXCHANGE |
| |
| PLAN FRAGMENT 1 |
| OUTPUT EXPRS: |
| PARTITION: HASH_PARTITIONED: 3: store_id |
| |
| STREAM DATA SINK |
| EXCHANGE ID: 04 |
| UNPARTITIONED |
| |
| 3:AGGREGATE (merge finalize) |
| | output: sum(6: sum) |
| | group by: 3: store_id |
| | |
| 2:EXCHANGE |
| |
| PLAN FRAGMENT 2 |
| OUTPUT EXPRS: |
| PARTITION: RANDOM |
| |
| STREAM DATA SINK |
| EXCHANGE ID: 02 |
| HASH_PARTITIONED: 3: store_id |
| |
| 1:AGGREGATE (update serialize) |
| | STREAMING |
| | output: sum(5: sale_amt) |
| | group by: 3: store_id |
| | |
| 0:OlapScanNode |
| TABLE: sales_records |
| PREAGGREGATION: ON |
| partitions=1/1 |
| rollup: sales_records |
| tabletRatio=10/10 |
| tabletList=12049,12053,12057,12061,12065,12069,12073,12077,12081,12085 |
| cardinality=1 |
| avgRowSize=2.0 |
| numNodes=0 |
+-----------------------------------------------------------------------------+
45 rows in set (0.00 sec)
Можно заметить, что запрос занимает около 0,02 секунд, и синхронное материализованное представление не используется для ускорения запроса, потому что значение поля rollup в пр офиле запроса равно sales_records, что является базовой таблицей.
Создание синхронного материализованного представления
Вы можете создать синхронное материализованное представление на основе конкретного запроса, используя CREATE MATERIALIZED VIEW.
На основе таблицы sales_records и запроса, упомянутого выше, следующий пример создает синхронное материализованное представление store_amt для анализа суммы продаж в каждом магазине.
CREATE MATERIALIZED VIEW store_amt AS
SELECT store_id, SUM(sale_amt)
FROM sales_records
GROUP BY store_id;
ВНИМАНИЕ
- При использовании агрегатных функций в синхронных материализованных представлениях вы должны использовать условие GROUP BY и указать хотя бы один столбец GROUP BY в вашем списке SELECT.
- Синхронные материализованные представления не поддерживают использование одной агрегатной функции для нескольких столбцов. Запросы в форме
sum(a+b)не поддерживаются.- Синхронные мате риализованные представления не поддерживают использование нескольких агрегатных функций для одного столбца. Запросы в форме
select sum(a), min(a) from tableне поддерживаются.- JOIN не поддерживается при создании синхронного материализованного представления.
- При использовании ALTER TABLE DROP COLUMN для удаления определенного столбца в базовой таблице необходимо убедиться, что все синхронные материализованные представления базовой таблицы не содержат удаляемого столбца, в противном случае операция удаления не может быть выполнена. Чтобы удалить столбец, используемый в синхронных материализованных представлениях, сначала необходимо удалить все синхронные материализованные представления, содержащие этот столбец, а затем удалить столбец.
- Создание слишком большого количества синхронных материализованных представлений для таблицы повлияет на эффективность загрузки данных. При загрузке данных в базовую таблицу данные в синхронных материализованных представлениях и базовой таблице обновляются синхронно. Если базовая таблица содержит
nсинхронных материализованных представлений, эффективность загрузки данных в базовую таблицу примерно такая же, как и загрузка данных вnтаблиц.- В настоящее время Selena не поддерживает создание нескольких синхронных материализованных представлений одновременно. Новое синхронное материализованное представление может быть создано только после завершения предыдущего.
- Материализованное представление может быть создано только в default_catalog. Вы можете либо создать его с помощью default_catalog.database.mv, либо переключиться на default_catalog с помощью оператора
set catalog <default_catalog>.
Проверка статуса построения синхронного материализованного представления
Создание синхронного материализованного представления является асинхронно й операцией. Успешное выполнение CREATE MATERIALIZED VIEW указывает на то, что задача по созданию материализованного представления успешно отправлена. Вы можете просмотреть статус построения синхронного материализованного представления в базе данных через SHOW ALTER MATERIALIZED VIEW.
MySQL > SHOW ALTER MATERIALIZED VIEW\G
*************************** 1. row ***************************
JobId: 12090
TableName: sales_records
CreateTime: 2022-08-25 19:41:10
FinishedTime: 2022-08-25 19:41:39
BaseIndexName: sales_records
RollupIndexName: store_amt
RollupId: 12091
TransactionId: 10
State: FINISHED
Msg:
Progress: NULL
Timeout: 86400
1 row in set (0.00 sec)
Раздел RollupIndexName указывает имя синхронного материализованного представления, а раздел State указывает, завершено ли построение.
Прямой запрос синхронного материализованного представления
Поскольку синхронное материализованное представление по сути является индексом базовой таблицы, а не физической таблицей, вы можете запросить синхронное материал изованное представление только с помощью подсказки [_SYNC_MV_]:
-- Не опускайте квадратные скобки [] в подсказке.
MySQL > SELECT * FROM store_amt [_SYNC_MV_];
+----------+----------+
| store_id | sale_amt |
+----------+----------+
| 2 | 6948 |
| 3 | 8734 |
| 1 | 4319 |
| 2 | 9515 |
| 3 | 4212 |
| 1 | 8573 |
+----------+----------+
ВНИМАНИЕ
В настоящее время Selena автоматически генерирует имена для столбцов в синхронном материализованном представлении, даже если вы указали для них псевдонимы.
Переписывание и ускорение запросов с помощью синхронного материализованного представления
Синхронное материализованное представление, которое вы создали, содержит полный набор предварительно вычисленных результатов в соответствии с запросом. Последующие запросы используют данные из него. Вы можете выполнить тот же запрос, чтобы протестировать время запроса, как вы делали при подготовке.
MySQL > SELECT store_id, SUM(sale_amt)
FROM sales_records
GROUP BY store_id;
+----------+-----------------+
| store_id | sum(`sale_amt`) |
+----------+-----------------+
| 2 | 16463 |
| 3 | 12946 |
| 1 | 12892 |
+----------+-----------------+
3 rows in set (0.01 sec)
Можно заметить, что время запроса сократилось до 0,01 секунд.
Проверка использования синхронного материализованного представления запросом
Выполните команду EXPLAIN снова, чтобы проверить, использует ли запрос синхронное материализованное представление.
MySQL > EXPLAIN SELECT store_id, SUM(sale_amt) FROM sales_records GROUP BY store_id;
+-----------------------------------------------------------------------------+
| Explain String |
+-----------------------------------------------------------------------------+
| PLAN FRAGMENT 0 |
| OUTPUT EXPRS:3: store_id | 6: sum |
| PARTITION: UNPARTITIONED |
| |
| RESULT SINK |
| |
| 4:EXCHANGE |
| |
| PLAN FRAGMENT 1 |
| OUTPUT EXPRS: |
| PARTITION: HASH_PARTITIONED: 3: store_id |
| |
| STREAM DATA SINK |
| EXCHANGE ID: 04 |
| UNPARTITIONED |
| |
| 3:AGGREGATE (merge finalize) |
| | output: sum(6: sum) |
| | group by: 3: store_id |
| | |
| 2:EXCHANGE |
| |
| PLAN FRAGMENT 2 |
| OUTPUT EXPRS: |
| PARTITION: RANDOM |
| |
| STREAM DATA SINK |
| EXCHANGE ID: 02 |
| HASH_PARTITIONED: 3: store_id |
| |
| 1:AGGREGATE (update serialize) |
| | STREAMING |
| | output: sum(5: sale_amt) |
| | group by: 3: store_id |
| | |
| 0:OlapScanNode |
| TABLE: sales_records |
| PREAGGREGATION: ON |
| partitions=1/1 |
| rollup: store_amt |
| tabletRatio=10/10 |
| tabletList=12092,12096,12100,12104,12108,12112,12116,12120,12124,12128 |
| cardinality=6 |
| avgRowSize=2.0 |
| numNodes=0 |
+-----------------------------------------------------------------------------+
45 rows in set (0.00 sec)
Можно заметить, что значение раздела rollup в профиле запроса теперь store_amt, что является синхронным материализованным представлением, которое вы создали. Это означает, что этот запрос использовал синхронное материал изованное представление.
Отображение синхронных материализованных представлений
Вы можете выполнить DESC <tbl_name> ALL, чтобы проверить схему таблицы и ее подчиненных синхронных материализованных представлений.
MySQL > DESC sales_records ALL;
+---------------+---------------+-----------+--------+------+-------+---------+-------+
| IndexName | IndexKeysType | Field | Type | Null | Key | Default | Extra |
+---------------+---------------+-----------+--------+------+-------+---------+-------+
| sales_records | DUP_KEYS | record_id | INT | Yes | true | NULL | |
| | | seller_id | INT | Yes | true | NULL | |
| | | store_id | INT | Yes | true | NULL | |
| | | sale_date | DATE | Yes | false | NULL | NONE |
| | | sale_amt | BIGINT | Yes | false | NULL | NONE |
| | | | | | | | |
| store_amt | AGG_KEYS | store_id | INT | Yes | true | NULL | |
| | | sale_amt | BIGINT | Yes | false | NULL | SUM |
+---------------+---------------+-----------+--------+------+-------+---------+-------+
8 rows in set (0.00 sec)
Удаление синхронного материализованного представления
При следующих обстоятельствах вам необходимо удалить синхронное материализованное представление:
- Вы создали неправильное материализованное представление и вам нужно удалить его до завершения построения.
- Вы создали слишком много материализованных представлений, что привело к значительному снижению производительности загрузки, и некоторые из материализованных представлений являются дубликатами.
- Частота использования соответствующих запросов низкая, и вы можете терпеть относительно высокую задержку запроса.
Удаление незавершенного синхронного материализованного представления
Вы можете удалить создаваемое синхронное материализованное представление, отменив выполняющуюся задачу создания. Сначала вам нужно получить идентификатор задачи JobID задачи создания материализованного представления, проверив статус построения материализованного представления. После получения идентификатора задачи вам необходимо отменить задачу создания с помощью команды CANCEL ALTER.
CANCEL ALTER TABLE ROLLUP FROM sales_records (12090);
Удаление существующего синхронного материализованного представления
Вы можете удалить существующее синхронное материализованное представление с помощью команды DROP MATERIALIZED VIEW.
DROP MATERIALIZED VIEW store_amt;
Лучшие практики
Точный подсчет уникальных значений
Следующий пример основан на таблице анализа рекламного бизнеса advertiser_view_record, которая записывает дату просмотра рекламы click_time, название рекламы advertiser, канал рекламы channel и идентификатор пользователя, просмотревшего рекламу user_id.
CREATE TABLE advertiser_view_record(
click_time DATE,
advertiser VARCHAR(10),
channel VARCHAR(10),
user_id INT
) distributed BY hash(click_time);
Анализ в основном сосредоточен на UV рекламы.
SELECT advertiser, channel, count(distinct user_id)
FROM advertiser_view_record
GROUP BY advertiser, channel;
Чтобы ускорить точный подсчет уникальных значений, вы можете создать синхронное материализованное представление на основе этой таблицы и использовать функцию bitmap_union для предварительной агрегации данных.
CREATE MATERIALIZED VIEW advertiser_uv AS
SELECT advertiser, channel, bitmap_union(to_bitmap(user_id))
FROM advertiser_view_record
GROUP BY advertiser, channel;
После создания синхронного материализованного представления подзапрос count(distinct user_id) в последующих запросах будет автоматически переписан как bitmap_union_count (to_bitmap(user_id)), чтобы они могли использовать синхронное материализованное представление.
Приблизительный подсчет уникальных значений
Используя таблицу advertiser_view_record из примера выше. Чтобы ускорить приблизительный подсчет уникальных значений, вы можете создать синхронное материализованное представление на основе этой таблицы и использовать функцию hll_union() для предварительной агрегации данных.
CREATE MATERIALIZED VIEW advertiser_uv2 AS
SELECT advertiser, channel, hll_union(hll_hash(user_id))
FROM advertiser_view_record
GROUP BY advertiser, channel;
Установка дополнительных ключей сортировки
Предположим, что базовая таблица tableA содержит столбцы k1, k2 и k3, где только k1 и k2 являются ключами сортировки. Если запрос, включающий подзапрос where k3=x, должен быть ускорен, вы можете создать синхронное материализованное представление с k3 в качестве первого столбца.
CREATE MATERIALIZED VIEW k3_as_key AS
SELECT k3, k2, k1
FROM tableA
Соответствие агрегатных функций
Когда запрос выполняется с синхронным материализованным представлением, исходный запрос автоматически переписывается и используется для запроса промежуточных результатов, хранящихся в синхронном материализованном представлении. Следующая таблица показывает соответствие между агрегатной функцией в исходном запросе и агрегатной функцией, используемой для построения синхронного материализованного представления. Вы можете выбрать соответствующую агрегатную функцию для построения синхронного материализованного представления в соответствии с вашим бизнес-сценарием.
| агрегатная функция в исходном запросе | агрегатная функция материализованного представления |
|---|---|
| sum | sum |
| min | min |
| max | max |
| count | count |
| bitmap_union, bitmap_union_count, count(distinct) | bitmap_union |
| hll_raw_agg, hll_union_agg, ndv, approx_count_distinct | hll_union |