Вспомогательная таблица для ускорения выборки

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

Контекст

Имеется агрегат, с которым может быть связано много данных, которые накапливаются с течением длительного времени. Например, пациент и его документы; датчик и его показания.

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

Вполне вероятно, что в целях нормального распределения данных, их партиционирование будет выполнено по времени создания элементов данных (например, времени создания документа; времени снятия показаний). Гранулярность партиционирования определяется выбранной БД, объемом данных и интенсивностью их поступления.

Пример таблицы с показаниями датчиков в ClickHouse:

CREATE TABLE history (
  tag String,
  date Date DEFAULT toDate(time),
  time DateTime64(3, 'UTC'),
  value Float64
)
ENGINE = MergeTree()
PARTITION BY toYYYYMM(date)
ORDER BY (tag, date, time)

Проблемы

В системе очень часто (или всегда) запрашивают данные без указания временного диапазона, но, возможно, с указанием дополнительных фильтров. Например, получить документы по пациенту у терапевта; получить показания датчика со значениями выше нормы.

SELECT tag, time, value
FROM history
WHERE tag = :tag AND value > :value

Без указания временных границ приходится сканировать все партиции за всё время. В результате запрос выполняется очень долго и создает большую нагрузку на I/O.

Решение

Создать производную таблицу с “подсказками”, по которым можно будет существенно ограничить количество партиций при выборке данных. По такой таблице можно определять наличие данных у агрегата за весь период его существования. Например, дни, за которые у пациента/датчика есть документы/показания.

CREATE MATERIALIZED VIEW history_info
ENGINE = ReplacingMergeTree()
PARTITION BY toYYYYMM(date)
ORDER BY (tag, date)
POPULATE
AS
SELECT tag, date
FROM history
GROUP BY tag, date

По такой таблице, например, можно очень быстро найти левую границу данных:

SELECT min(date)
FROM history_info
WHERE tag = :tag

Эту информацию можно использовать как подсказку в основном запросе:

SELECT tag, time, value
FROM history
WHERE tag = :tag AND value > :value
  AND date >= (
    SELECT min(date)
    FROM history_info
    WHERE tag = :tag
  )

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

Плюсы

• Существенное ускорение времени выполнения запроса.
• Существенное снижение нагрузки на I/O.

Минусы

• Усложнение кода приложения для создания производной таблицы, наполнения её данными и поддержания их в актуальном состоянии. Если данная проблема решается средствами СУБД, как, например, в ClickHouse, то данный минус несущественный.
• Увеличение размера хранимых данных.