Оптимизация запросов: статистика, JOIN и агрегации
Оптимизация запросов в аналитических системах — это не только красивый график EXPLAIN и хитрое приложение индексов. Это про понимание того, как работает база данных, какие данные у нее есть, как она хранит их и как на практике получить результат быстрее и с меньшими затратами ресурсов. В рамках курса по Apache Doris мы посвятим главу Оптимизация запросов: статистика, JOIN и агрегации тому, как Doris использует статистику для выбора планов выполнения, как выбирать и настраивать операторы соединения (JOIN) и как эффективно выполнять агрегации над большими объемами данных. Мы затронем теорию, но обязательно добавим практику: как собирать и использовать статистику, как проектировать схемы и представления для ускорения запросов, какие открытые инструменты можно применить, а также что происходит в российских реалиях data-инфраструктур, где часто важны требования к локализации данных и интеграции с локальными решениями.
Цель главы — дать новичку понятное и системное представление о трех взаимосвязанных направлениях оптимизации в Doris:
- статистика и ее роль в планировании исполнения;
- выбор стратегий соединений и порядок объединения таблиц (JOIN);
- агрегации и техники ускорения больших вычислений, включая использование материализованных представлений и специфических функций агрегации.
Статистика и карточность планирования
Статистика — это собранные сведения об распределении значений в столбцах таблиц: минимальные и максимальные значения, число NULL-значений, количество разных значений (NDV — number of distinct values), а также гистограммы распределения некоторых столбцов. В Doris статистика служит опорой для оценки затрат на выполнение запроса: сколько будет прочитано данных, сколько строк пройдет через операторы фильтрации, какой будет кардинальность на выходе и насколько эффективны будут соединения.
Зачем нужна статистика:
- Кардинальность: планировщик оценивает, сколько уникальных значений будет на выходе агрегаций, сколько строк пройдет через фильтры, и на основе этого выбирает планы соединения и метод выполнения.
- Выбор расстояний для обхода данных: например, если для столбца с ролью регионов статистика показывает узкий NDV или сильную корреляцию между датой и регионом, планировщик может применить фильтрацию на уровне разделов (partition pruning) и уменьшить объём сканируемых данных.
- Выбор методов агрегации и сортировок: в зависимости от распределения данных планировщик может предпочесть потоковую агрегацию или вложенную агрегацию, а также определить, какие ключи лучше отсортировать и закэшировать.
Кардинальность и NDV особенно важны. Если NDV очень велико по сравнению с общим количеством значений, приблизительная оценка может лучше подходить к реальности и не перегружать планировщик вычислениями. Doris поддерживает функции, которые помогают разработчику контролировать точность оценок в рамках допустимой погрешности.
Гистограммы и выборки
Гистограммы представляют распределение значений по диапазонам диапазонов (bucket-ов). Они позволяют планировщику оценивать likelihood того, что значение попадет в заданный диапазон. В условиях больших таблиц с неоднородным распределением гистограммы существенно снижают риск неверной оценки селективности фильтров и, как следствие, неверного выбора плана.
Иногда практикуется использование выборок. Выборка может ускорить сбор статистики, но нужно помнить, что она может вводить погрешности. В Doris статистика собирается через команды ANALYZE TABLE; частота обновления статистики должна соответствовать характеру нагрузки: если данные обновляются часто, статистика должна обновляться регулярно.
Оптимизация соединений (JOIN)
JOIN — один из самых дорогих по ресурсам операторов в аналитических системах. Эффективность соединения во многом определяется тем, как данные распределены между узлами и как выстроен план соединения. В Doris основная идея такова:
- co-located join (соединение на уровне локализации данных): когда обе таблицы разделены по тем же ключам распределения, Doris может выполнить соединение локально на каждом узле без межузлового перемещения большего объема данных. Это значительно уменьшает сетевые затраты и ускоряет исполнение.
- стратегическая последовательность соединений: порядок join-операций влияет на итоговую стоимость выполнения. Оптимизатор строит множество вариантов плана и выбирает самый экономичный на основе статистики.
- методы выполнения соединений: в системах типа Doris применяются различные варианты соединений, такие как хеш-соединение (hash join) и, при подходящих условиях, другие техники. Выбор зависит от размера таблиц, доступной памяти и распределения. Важно понимать, что не всегда возможно избежать передач данных между узлами, и здесь роль статистики и архитектуры Doris особенно важна.
Агрегации и ускорение
Агрегации — это часто самый большой бюджет по времени на аналитическую выборку. Doris поддерживает стандартные агрегатные функции (SUM, AVG, COUNT и т.д.), а для больших наборов данных полезно применять приблизительные агрегаты, когда строгая точность может быть заменена эффективностью:
- approximate_count_distinct (приближённое подсчитывание количества уникальных значений) — позволяет быстро оценивать NDV без точного подсчета. Это полезно в больших датасетах, особенно на этапе предварительной фильтрации и анализа.
- rollup и материализованные представления (MV): материализованные представления позволяют заранее вычислять и хранить агрегаты по заданным ключам, что dramatically ускоряет часто исполняемые запросы, связанные с агрегациями по одним и тем же столбцам.
- групповая агрегация с оптимизацией памяти: Doris может использовать стратегию по частям, чтобы не держать все промежуточные результаты в памяти.
Размещение данных, партиционирование и Pruning
Эффективная стратегия хранения данных поддерживает быстрый доступ к нужному подмножеству. Партиционирование по дате или по другим признакам, распределение по ключам и колокационные группы позволяют планировщику исключать ненужные участки данных (partition pruning) и тем самым резко уменьшать объем сканирования, особенно в рамках диапазонных запросов и фильтров по ключам разделов.
Материализованные представления и автоматическое обслуживание MV
Материализованные представления позволяют заранее рассчитывать результаты для типичных запросов и хранить их как отдельные таблицы. Doris поддерживает MV, которые автоматически обновляются при изменении базовых таблиц, что позволяет значительно снизить задержку на повторные запросы с теми же агрегациями. Важно выстраивать MV таким образом, чтобы они покрывали наиболее часто выполняемые сценарии, иначе затраты на обслуживание MV могут оказаться неоправданными.
Практические примеры
Пример 1. Базовая оптимизация на примере таблицы продаж
Допустим, у нас есть две таблицы: sales (фактная таблица) и region_dim (измерение регионов). Таблица sales содержит колонки sale_id, order_date, region_id, product_id, customer_id, amount, quantity. region_dim содержит region_id, region_name.
1) Подготовка схемы и загрузка данных
Создаем таблицу продаж в Doris с распределением по ключу region_id и партиционированием по дате order_date для эффективной фильтрации по диапазонам иpartition pruning:
CREATE TABLE sales (
sale_id BIGINT,
order_date DATE,
region_id INT,
product_id INT,
customer_id BIGINT,
amount DECIMAL(18,2),
quantity INT
) ENGINE=OLAP
DISTRIBUTED BY HASH(region_id) BUCKETS 16
PARTITION BY RANGE (order_date) (
PARTITION p2023_01 VALUES LESS THAN ('2023-02-01'),
PARTITION p2023_02 VALUES LESS THAN ('2023-03-01'),
PARTITION p2023_03 VALUES LESS THAN ('2023-04-01')
);
Создаем таблицу измерений:
CREATE TABLE region_dim (
region_id INT,
region_name VARCHAR(100)
) ENGINE=OLAP
DISTRIBUTED BY HASH(region_id) BUCKETS 8;
2) Сбор статистики
После загрузки данных запускаем сбор статистики:
ANALYZE TABLE sales COMPUTE STATISTICS; ANALYZE TABLE region_dim COMPUTE STATISTICS;
3) Типичный запрос и пояснение плана
Пример запроса:
SELECT r.region_name, SUM(s.amount) AS total_sales FROM sales s JOIN region_dim r ON s.region_id = r.region_id WHERE s.order_date >= '2023-01-01' AND s.order_date < '2023-02-01' GROUP BY r.region_name;
Включение EXPLAIN PLAN поможет увидеть план: Doris обычно предложит ко-локационное соединение по region_id, что сведет межузловую передачу к минимуму. Если региональные данные распределены одинаково по обоим таблицам, Doris выполнит локальное соединение на узлах и не будет обмениваться большими блоками.
4) Оптимизация соединения и использование статистики
Если таблицы разделены одинаково по region_id, соединение может быть выполнено локально, что экономит сетевые ресурсы.
Для ускорения повторных запросов по тем же агрегациям можно создать MV, например:
CREATE MATERIALIZED VIEW mv_sales_by_region AS
SELECT region_id, DATE_TRUNC('month', order_date) AS month, SUM(amount) AS total_amount
FROM sales
GROUP BY region_id, month;
-MV будет автоматически поддерживаться Doris.
Пример 2. Агрегации и приблизительные подсчеты
1) Пример с приблизительным подсчетом уникальных клиентов
SELECT approx_count_distinct(customer_id) AS unique_customers FROM sales WHERE order_date BETWEEN '2023-01-01' AND '2023-12-31';
2) Применение MV для ускорения регулярной агрегации
MV позволяет серверу обслуживать частые запросы типа: SELECT region_id, SUM(amount) FROM sales WHERE order_date >= '2023-01-01' AND order_date < '2024-01-01' GROUP BY region_id;
При наличии MV по region_id и месячным агрегациям эти запросы могут обслуживаться из MV, минуя дорогостоящие вычисления на базе таблиц.
Пример 3. Русские и открытые аналоги: сравнение и практические выводы
- Doris (open-source) — крепкий выбор для больших OLAP-нагруженных систем, где требуется явная поддержка MV, статистики и ко-локализованных соединений. В рамках российского рынка часто применяется ClickHouse — российское открытое решение для OLAP, которое имеет свою модель оптимизации и статистики. Хотя архитектуры различаются, принципы схожи: использование столбцового формата хранения, агрегации на лету и возможность внедрения MV/материализованных результатов. В реальных проектах можно рассмотреть параллелизм и совместную работу Doris для отдельных задач и ClickHouse для быстрых интерактивных запросов по другим данным направлениям.
- Пример интеграции: в ходе миграции или гибридной архитектуры можно держать широкие дампы фактов в Doris для сложных агрегаций и при этом использовать ClickHouse для оперативного анализа некоторых суб-модулей, где нужна ещё более агрессивная компрессия и быстрые фильтры.
- Российские решения и локализация: в рамках требований к локализации данных и соответствию регуляторным требованиям часто обсуждают размещение данных в дата-центрах под управлением российских операторов и использование инструментов, поддерживающих соответствие нормативам. В этом контексте Doris, как гибкая и масштабируемая платформа, может быть частью гибридной архитектуры, где критичные по локализации данные держатся в локальном облаке, а прочие данные реплицируются или обрабатываются в приватных кластерах.
Технические детали
Сбор статистики и настройка
- Сбор статистики выполняется командой ANALYZE TABLE. Рекомендуется собирать статистику после больших загрузок или очевидных изменений в распределении данных. Регулярность зависит от частоты обновления данных и скорости изменений в столбцах.
- Статистические данные хранятся внутри Doris и используются планировщиком запросов для оценки селективности. Включение и настройка статистики на уровне таблиц и баз данных позволяет адаптировать поведение планировщика.
Оптимизация выполнения JOIN
- Колокационное соединение (colocate join) по общему ключу распределения — один из главных способов снизить сетевой трафик. Чтобы достичь колокации, таблицы должны быть распределены по одним и тем же ключам и находиться в одной колокационной группе (colocation). Это тем более важно, если в запросе задействованы несколько таблиц, а объем данных велик.
- Планирование порядка соединений: оптимизатор Doris учитывает статистику по входам и выбирает порядок соединений. В некоторых случаях двойной или тройной вложенный план может оказаться выгоднее, чем «первый пройти по большему набору, потом сужать». Важно анализировать планы через EXPLAIN для понимания того, как оптимизатор приходит к конкретному плану.
Агрегации и материализованные представления
- Materialized View (MV) — заранее рассчитанные агрегаты, которые обслуживают повторные запросы. MV должен быть подобран под частые сценарии анализа и обновляться при изменении базовых таблиц. В Doris MV эффективны при повторяющихся агрегациях, особенно по ключам группировки.
- Приближенные функции: approx_count_distinct применяются там, где точность может быть заменена скоростью — это особенно полезно при подсчете уникальных клиентов или уникальных идентификаторов в больших датасетах.
Работа с партициями и фильтрацией
- Разграничение по partition pruning: queries, которые содержат фильтры по partition keys, дают Doris возможность пропустить сканирование ненужных partition. Это особенно ощутимо на запросах с диапазонной фильтрацией по дате.
- Правильная стратегия партиционирования и распределения помогает минимизировать чтение данных и ускорить выполнение запросов. Но следует помнить, что слишком мелкие partition могут увеличить затраты на управление метаданными, а слишком крупные — снизить эффективность prune.
План тестирования и мониторинга
- Применяйте EXPLAIN PLAN или аналогичные инструменты, чтобы увидеть план выполнения и понять, какие части плана являются узкими местами.
- Регулярно мониторьте планы и статистику по основным часто выполняемым запросам, чтобы понять, какие MV или какие изменения в схемах принесли наилучшее улучшение.
Риски и ограничения
- Точность статистики. Если данные часто обновляются, устаревшая статистика может приводить к неоптимальным планам. В таких случаях критично регулярно обновлять статистику ANALYZE TABLE и рассмотреть настройку частоты обновления.
- Широкие диапазоны данных и skews: сильное смещение данных по одним разделам может привести к перегрузке конкретных узлов и неравномерному распределению нагрузки. Необходимо внимательно отслеживать распределение данных и корректировать стратегию разделения и распределения.
- MV — поддержка и обслуживание. MV требует дополнительных ресурсов на хранение и обновление. Необходимо планировать, какие сценарии будут обслуживаться MV, и какие запросы действительно будут выигрывать от MV. Иногда MV может не покрывать все запросы и тогда планировщик должен возвращаться к вычислению из базовых таблиц.
- Локализация данных и регулятивные требования. В условиях российского рынка важно предусмотреть требования к размещению данных в рамках локальных инфраструктур и соответствие регуляторным нормам. В гибридной архитектуре это следует учитывать при моделировании потоков данных и распределении нагрузки между кластерами в разных регионах.
- Ограничения JOIN. Для очень больших таблиц с высокойCardinality, особенно если колокация данных не достигается на всем пути, могут быть затраты на межузловую передачу. В таких случаях стоит пересмотреть ключи распределения, использовать дополнительные индексы или MV, чтобы снизить стоимость сканирования и соединения.
- Совместимость версий и функциональности. Doris развивается, и новые функции могут появляться, иногда меняя синтаксис или поведение. В проектах важно поддерживать согласованность версий и тестировать новые патчи на тестовой среде перед переносом в продакшн.
Оптимизация запросов в Doris строится вокруг трех столпов: статистика и разумная картина о данных, эффективное соединение таблиц и их планирование, а также грамотно построенные агрегации и использование материалов, которые позволяют ускорить повторные запросы. Практически такая оптимизация начинается с сбора статистики после значительных загрузок, анализа плана исполнения через EXPLAIN, настройки распределения и партиционирования, применения MV и рассуждений о приоритетах агрегаций. Важно помнить: чем точнее статистика и чем более «локализованы» данные, тем меньше приходится перетаскивать данные между узлами и тем быстрее выполняются запросы.
FAQ — Вопрос–Ответ
1) Что такое статистика в Doris и зачем она нужна?
Статистика — это данные о распределении значений в столбцах (min, max, NDV, nulls, гистограммы), которые используются планировщиком запросов для оценки селективности и затрат на выполнение. Она помогает выбрать наилучший план выполнения, порядок соединений, методы агрегаций и возможность prune partitions. Без актуальной статистики планы могут быть неэффективными и приводить к перерасходу ресурсов.
2) Как собрать статистику и как часто её обновлять?
Статистику собирают командой ANALYZE TABLE. Рекомендуется выполнять её после загрузки больших партий данных или когда изменения в данных существенно влияют на распределение значений. Частота обновления зависит от характера нагрузки: чем чаще данные обновляются, тем чаще стоит проводить анализ статистики. В условиях активной линки изменений можно рассмотреть автоматизированные задачи анализа статистики.
3) Что такое co-located join и как обеспечить его?
Co-located join — это соединение, выполненное без передачи больших объемов данных между узлами благодаря размещению таблиц по тем же ключам распределения. Чтобы обеспечить co-located join, таблицы должны использовать одинаковые ключи DISTRIBUTED BY HASH и принадлежать к одной колокационной группе. Это существенно уменьшает сетевой трафик и ускоряет выполнение сложных JOIN-запросов.
4) Как агрегации ускоряются в Doris?
Агрегации ускоряются за счет использования MV (материализованных представлений) для часто повторяемых запросов, а также через приближенные функции, такие как approx_count_distinct, когда допустима погрешность и нужна скорость. MV позволяют держать предвычисленные результаты, что существенно снижает время отклика на типичные запросы. При этом MV требует обслуживания и соответствующего плана использования.
5) Какие риски существуют при использовании статистики и MV?
Основные риски: устаревшая статистика приводит к неоптимальным планам; слишком частое обновление MV может увеличить нагрузку на хранение и управление; MV покрывает не все запросы, и в некоторых случаях планировщик вернется к вычислениям на базовых таблицах. Важно балансировать между скоростью запросов и затратами на обслуживание MV.
6) Как использовать PARITION PRUNING в Doris?
Партиционирование позволяет Doris пропускать чтение partition, если в запросах используются фильтры по partition keys (например, по дате). Это значительно сокращает количество сканируемых данных и ускоряет запросы, особенно на больших датасетах. Для этого следует реализовать разумное партиционирование по часто используемым диапазонам и фильтрам.
7) Какие сценарии лучше всего подходят под использование MV?
MV особенно полезны для frequently executed агрегационных запросов с одними и теми же наборами группировок и диапазонами дат. Если запросы повторяются часто и их параметры ограничены, MV может дать наибольший выигрыш. В противном случае MV может оказаться избыточной, а её стоимость обслуживания — неоправданной.
8) Как сравнить Doris с российскими решениями, например ClickHouse?
Doris и ClickHouse — оба ориентированы на OLAP и агрегации. ClickHouse часто применяется в российских проектах благодаря зрелой экосистеме и гибким настройкам. В Doris сильна интеграция с MV, управление статистикой и колокационные соединения. В реальных задачах может использоваться гибридная архитектура: Doris для сложных, тяжелых агрегатов, ClickHouse — для интерактивной аналитики и быстрого доступа к данным. В любом случае важно подбирать подход под конкретную задачу, включая требования к локализации и регуляторике.
9) Какие практические шаги можно сделать на следующей неделе для повышения скорости запросов?
- Собрать статистику после загрузки крупных партий данных.
- Проверить планы исполнения часто выполняемых запросов через EXPLAIN и убедиться в ко-локализованных соединениях.
- Рассмотреть создание MV для самых востребованных агрегаций.
- Перепроверить ключи распределения и партиционирование в случаях сильной дисперсии данных.
- Протестировать приблизительные агрегаты там, где допустима погрешность, чтобы снизить стоимость.
10) Какие риски связаны с локализацией данных и регулятивными требованиями?
Российские регуляторы часто требуют локализации данных. В гибридной архитектуре это значит грамотное размещение данных в локальном облаке и продуманное распределение нагрузки между кластерами, чтобы соблюсти требования к хранению и обработке. Внедрение таких решений требует согласования с безопасностью и соответствием регламенту, а также планирования резервирования и отказоустойчивости в рамках конкретного региона.
