Синхронное материализованное представление
Эта тема описывает, как создавать, использовать и управлять синхронным материализованным представлением (Rollup).
Для синхронного материализованного представления все изменения в базовой таблице одновременно обновляются в соответствующих синхронных материализованных представлениях. Обновление синхронного материализованного представления запускается автоматически. Синхронные материализованные представления значительно недороги в обслуживании и обновлении, что делает их подходящими для прозрачного ускорения запросов агрегации в реальном времени по одной таблице.
Синхронные материализованные представления в Selena могут быть созданы только на одной базовой таблице из default catalog. По сути, они являются специальным индексом для ускорения запросов, а не физической таблицей, как асинхронные материализованные представления.
Начиная с версии 1.5.0, Selena предоставляет асинхронные материализованные представления, которые поддерживают создание на нескольких таблицах и больше операторов агрегации. Для использования асинхронных материализованных представлений см. Асинхронное материализованное представление.
- Синхронные материализованные представления поддерживают WHERE-условия начиная с версии 1.5.0.
- В настоящее время синхронные материализованные представления еще не поддерживаются в кластерах с разделяемыми данными.
Следующая таблица сравнивает асинхронные материализованные представления (ASYNC MVs) в Selena v1.5.2 и синхронные материализованные представления (SYNC MV) с точки зрения функций, которые они поддерживают:
| Агрегация одной таблицы | Соединение нескольких таблиц | Перезапись запросов | Стратегия обновления | Базовая таблица | |
|---|---|---|---|---|---|
| ASYNC MV | Да | Да | Да |
| Несколько таблиц из:
|
| SYNC MV (Rollup) | Ограниченный выбор агрегатных функций | Нет | Да | Синхронное обновление во время загрузки данных | Одна таблица в default catalog |
Основные понятия
-
Базовая таблица
Базовые таблицы являются основными таблицами материализованного представления.
Для синхронных материализованных представлений Selena базовые таблицы должны быть одной нативной таблицей из default catalog. Selena поддерживает создание синхронных материализованных представлений на таблицах Duplicate Key и Aggregate.
-
Обновление
Синхронное материализованное представление обновляется каждый раз, когда изменяются данные в базовой таблице. Вам не нужно запускать обновление вручную.
-
Перезапись запросов
Перезапись запросов означает, что при выполнении запроса к базовым таблицам с построенными на них материализованными представлениями система автоматически определяет, можно ли повторно использовать предварительно вычисленные результаты в материализованном представлении для запроса. Если их можно использовать повторно, система загрузит данные напрямую из соответствующего материализованного представления, чтобы избежать затратных по времени и ресурсам вычислений или соединений.
Синхронные материализованные представления поддерживают перезапись запросов на основе некоторых агрегатных операторов. Для получения дополнительной информации см. Соответствие агрегатных функций.
Подготовка
Перед созданием синхронного материализованного представления проверьте, подходит ли ваше хранилище данных для ускорения запросов через синхронные материализованные представления. Например, проверьте, используют ли запросы повторно определенные подзапросы.
Следующий пример основан на таблице sales_records, которая содержит ID транзакции record_id, ID продавца seller_id, 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 не поддерживает созд ание нескольких синхронных материализованных представлений одновременно. Новое синхронное материализованное представление может быть создано только после завершения предыдущего.
Проверка статуса построения синхронного материализованного представления
Создание синхронного материализованного представления является асинхронной операцией. Успешное выполнение 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)
Удаление синхронного материализованного представления
В следующих обстоятельствах вам нужно удалить синхронное материализованное представление:
- Вы создали неправильное материализованное представление и вам нужно удалить его до завершения построения.
- Вы создали слишком много материализованных представлений, что привело к огромному снижению производительности загрузки, и некоторые из материализованных представлений дублируются.
- Частота соответствующих запросов низкая, и вы можете терпеть относительно высокую задержку запросов.
Удаление незавершенного синхронного материализованного представления
Вы можете удалить синхронное материализованное представление, которое создается, отменив выполняющуюся задачу создания. Сначала вам нужно получить ID задания JobID задачи создания материализованного представления, проверив статус построения материализованного представления. После получения ID задания вам нужно отменить задачу создания с помощью команды 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 и ID пользователя, который просмотрел рекламу 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 |