Сегодня поделюсь методом оптимизации выборки больших данных, который кажется очевидным, но не всегда приходит в голову. Этот подход я использовал в связке с ClickHouse, однако он подходит для большинства хранилищ данных.
Контекст
Имеется агрегат, с которым может быть связано много данных, которые накапливаются с течением длительного времени. Например, пациент и его документы; датчик и его показания.
Обычно такие данные хранят в табличном виде, и в рамках такой таблицы есть связка между идентификатором агрегата, ассоциированным элементом и временем создания элемента. Например, таблица документов хранит ссылку на пациента и время создания документа; таблица показаний хранит ссылку на датчик и время снятия показаний.
Вполне вероятно, что в целях нормального распределения данных, их партиционирование будет выполнено по времени создания элементов данных (например, времени создания документа; времени снятия показаний). Гранулярность партиционирования определяется выбранной БД, объемом данных и интенсивностью их поступления.
Пример таблицы с показаниями датчиков в ClickHouse:
Проблемы
В системе очень часто (или всегда) запрашивают данные без указания временного диапазона, но, возможно, с указанием дополнительных фильтров. Например, получить документы по пациенту у терапевта; получить показания датчика со значениями выше нормы.
Без указания временных границ приходится сканировать все партиции за всё время. В результате запрос выполняется очень долго и создает большую нагрузку на I/O.
Решение
Создать производную таблицу с “подсказками”, по которым можно будет существенно ограничить количество партиций при выборке данных. По такой таблице можно определять наличие данных у агрегата за весь период его существования. Например, дни, за которые у пациента/датчика есть документы/показания.
По такой таблице, например, можно очень быстро найти левую границу данных:
Эту информацию можно использовать как подсказку в основном запросе:
Подобное решение можно адаптировать и под другие варианты партиционирования данных, а производная таблица “подсказок” может быть более или менее информативной. Основная её цель — это существенно сократить объем выборки без потери качества результата.
Плюсы
- Существенное ускорение времени выполнения запроса.
- Существенное снижение нагрузки на I/O.
Минусы
- Усложнение кода приложения для создания производной таблицы, наполнения её данными и поддержания их в актуальном состоянии. Если данная проблема решается средствами СУБД, как, например, в ClickHouse, то данный минус несущественный.
- Увеличение размера хранимых данных.
***
P.s. Напомню, что у меня есть Telegram-канал "Архитектоника в ИТ", где я публикую материал на похожие темы примерно раз в неделю. Подписчики меня мотивируют, но ещё больше мотивируют живые дискуссии, ведь именно в них рождается истина. Поэтому подписывайтесь на канал или на мой блог здесь. Будем обмениваться опытом и мнениями. ;-)