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

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

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

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

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

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

Запрос	(time)	(project, subproject, time)
	2.381 сек	1.660 сек
	2.148 сек	0.058 сек
	2.192 сек	0.012 сек
	2.968 сек	0.010 сек

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

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

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

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

Заполнение целевой таблицы

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

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

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

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

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

Следующим шагом мы создадим материализованное представление для чтения из wikistat_backfill и записи в wikistat_top

А затем, наконец, мы заполним wikistat_backfill из первоначальной таблицы wikistat:

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

Теперь мы можем запрашивать материализованное представление вместо оригинальной таблицы:

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