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

Сегодня поделюсь методом оптимизации выборки больших данных, который кажется очевидным, но не всегда приходит в голову. Этот подход я использовал в связке с 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, то данный минус несущественный.
Увеличение размера хранимых данных.

P.s. Напомню, что у меня есть Telegram-канал "Архитектоника в ИТ", где я публикую материал на похожие темы примерно раз в неделю. Подписчики меня мотивируют, но ещё больше мотивируют живые дискуссии, ведь именно в них рождается истина. Поэтому подписывайтесь на канал или на мой блог здесь. Будем обмениваться опытом и мнениями. ;-)

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

Контекст

Проблемы

Решение

Плюсы

Минусы