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

После оптимизации хранилища следующим шагом является улучшение производительности запросов. В этом разделе мы рассмотрим два ключевых подхода: оптимизацию ключей ORDER BY и использование материализованных представлений. Мы увидим, как эти подходы позволяют сократить время выполнения запросов с секунд до миллисекунд.

Оптимизация ключей ORDER BY

Прежде чем переходить к другим оптимизациям, следует оптимизировать ключи ORDER BY, чтобы ClickHouse выдавал максимально быстрые результаты. Выбор правильного ключа во многом зависит от запросов, которые вы планируете выполнять. Предположим, что большинство наших запросов фильтруют данные по столбцам project и subproject. В этом случае имеет смысл добавить их в ключ сортировки, а также столбец time, так как мы также выполняем запросы по времени.

Создадим ещё одну версию таблицы с теми же типами столбцов, что и в wikistat, но с сортировкой по (project, subproject, time).

CREATE TABLE wikistat_project_subproject
(
    `time` DateTime,
    `project` String,
    `subproject` String,
    `path` String,
    `hits` UInt64
)
ENGINE = MergeTree
ORDER BY (project, subproject, time);

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

Запрос	(time)	(project, subproject, time)
SELECT project, sum(hits) AS h FROM wikistat GROUP BY project ORDER BY h DESC LIMIT 10;	2.381 с	1.660 с
SELECT subproject, sum(hits) AS h FROM wikistat WHERE project = 'it' GROUP BY subproject ORDER BY h DESC LIMIT 10;	2.148 с	0.058 с
SELECT toStartOfMonth(time) AS m, sum(hits) AS h FROM wikistat WHERE (project = 'it') AND (subproject = 'zero') GROUP BY m ORDER BY m DESC LIMIT 10;	2.192 с	0.012 с
SELECT path, sum(hits) AS h FROM wikistat WHERE (project = 'it') AND (subproject = 'zero') GROUP BY path ORDER BY h DESC LIMIT 10;	2.968 с	0.010 с

Материализованные представления

Другой вариант — использовать материализованные представления для агрегирования и хранения результатов популярных запросов. Эти результаты можно запрашивать вместо исходной таблицы. Предположим, что следующий запрос в нашем случае выполняется довольно часто:

SELECT path, SUM(hits) AS v
FROM wikistat
WHERE toStartOfMonth(time) = '2015-05-01'
GROUP BY path
ORDER BY v DESC
LIMIT 10

┌─path──────────────────┬────────v─┐
│ -                     │ 89650862 │
│ Angelsberg            │ 19165753 │
│ Ana_Sayfa             │  6368793 │
│ Academy_Awards        │  4901276 │
│ Accueil_(homonymie)   │  3805097 │
│ Adolf_Hitler          │  2549835 │
│ 2015_in_spaceflight   │  2077164 │
│ Albert_Einstein       │  1619320 │
│ 19_Kids_and_Counting  │  1430968 │
│ 2015_Nepal_earthquake │  1406422 │
└───────────────────────┴──────────┘

10 строк в наборе. Затрачено: 2.285 сек. Обработано 231.41 млн строк, 9.22 ГБ (101.26 млн строк/с., 4.03 ГБ/с.)
Пиковое использование памяти: 1.50 ГиБ.

Создание материализованного представления

Мы можем создать следующее материализованное представление:

CREATE TABLE wikistat_top
(
    `path` String,
    `month` Date,
    hits UInt64
)
ENGINE = SummingMergeTree
ORDER BY (month, hits);

CREATE MATERIALIZED VIEW wikistat_top_mv 
TO wikistat_top
AS
SELECT
    path,
    toStartOfMonth(time) AS month,
    sum(hits) AS hits
FROM wikistat
GROUP BY path, month;

Заполнение целевой таблицы задним числом

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

Самый простой способ сделать это — использовать оператор INSERT INTO SELECT для непосредственной вставки данных в целевую таблицу материализованного представления с использованием SELECT‑запроса (трансформации) этого представления:

INSERT INTO wikistat_top
SELECT
    path,
    toStartOfMonth(time) AS month,
    sum(hits) AS hits
FROM wikistat
GROUP BY path, month;

В зависимости от кардинальности исходного набора данных (у нас 1 миллиард строк!), этот подход может быть очень требовательным к памяти. В качестве альтернативы можно использовать вариант, который требует минимального объёма памяти:

• Создание временной таблицы с движком Null
• Подключение копии обычно используемого материализованного представления к этой временной таблице
• Использование запроса INSERT INTO SELECT для копирования всех данных из исходного набора данных во временную таблицу
• Удаление временной таблицы и временного материализованного представления.

При таком подходе строки из исходного набора данных копируются блоками во временную таблицу (которая при этом не сохраняет сами строки), и для каждого блока строк вычисляется промежуточное состояние и записывается в целевую таблицу, где эти состояния постепенно объединяются в фоновом режиме.

CREATE TABLE wikistat_backfill
(
    `time` DateTime,
    `project` String,
    `subproject` String,
    `path` String,
    `hits` UInt64
)
ENGINE = Null;

Далее мы создадим материализованное представление, которое будет читать из wikistat_backfill и записывать в wikistat_top

CREATE MATERIALIZED VIEW wikistat_backfill_top_mv 
TO wikistat_top
AS
SELECT
    path,
    toStartOfMonth(time) AS month,
    sum(hits) AS hits
FROM wikistat_backfill
GROUP BY path, month;

И наконец, мы заполним wikistat_backfill из исходной таблицы wikistat:

INSERT INTO wikistat_backfill
SELECT * 
FROM wikistat;

Когда запрос завершится, мы можем удалить таблицу бэкфилла и материализованное представление:

DROP VIEW wikistat_backfill_top_mv;
DROP TABLE wikistat_backfill;

Теперь вместо исходной таблицы мы можем выполнять запросы к материализованному представлению:

SELECT path, sum(hits) AS hits
FROM wikistat_top
WHERE month = '2015-05-01'
GROUP BY ALL
ORDER BY hits DESC
LIMIT 10;

┌─path──────────────────┬─────hits─┐
│ -                     │ 89543168 │
│ Angelsberg            │  7047863 │
│ Ana_Sayfa             │  5923985 │
│ Academy_Awards        │  4497264 │
│ Accueil_(homonymie)   │  2522074 │
│ 2015_in_spaceflight   │  2050098 │
│ Adolf_Hitler          │  1559520 │
│ 19_Kids_and_Counting  │   813275 │
│ Andrzej_Duda          │   796156 │
│ 2015_Nepal_earthquake │   726327 │
└───────────────────────┴──────────┘

Получено 10 строк. Прошло: 0.004 сек.

Улучшение производительности здесь колоссальное. Раньше на вычисление результата этого запроса уходило чуть больше 2 секунд, а теперь требуется всего 4 миллисекунды.