Чтобы сделать наши курсы по Greenplum и аналитике больших данных еще более полезными, сегодня рассмотрим особенности выполнения SQL-запросов в этой MPP-СУБД. Читайте далее, зачем и когда запускать оператор анализа табличной статистики ANALYZE, как он связан с планом выполнения SQL-запроса и какие инструменты помогут дата-инженеру, аналитику или разработчику повысить их производительность.
Как выполняются SQL-запросы в СУБД: краткий ликбез
Прежде всего напомним общий процесс выполнения SQL-запросов в СУБД. Сперва SQL-выражение преобразуется в план выполнения запроса – последовательность операций, нужных для получения результата, включая следующие [1]:
- Выборка результатов через вложенные циклы (итеративные процессы поиска данных в каждой из соединяемых таблиц) и слияние. Если объединяемые таблицы имеют индексы по сравниваемым полям, они могут объединяться с помощью слияния, когда оба индекса сканируются и в них ищутся одинаковые значения. Если колонок в индексах достаточно для получения итогового результата, то чтение таблиц не выполняется. Иначе выполняется прямой доступ к сливаемым таблицам для получения не индексных колонок, нужных для результата. Если слияния недостаточно для получения итогового результата, то для каждой строки, полученной слиянием, выполняется до 2-х серий вложенных циклов (для каждой из сливаемых таблиц). Поскольку в индексах данные отсортированы, слияние дешевле вложенных циклов, но план выполнения не может начинаться со слияния.
- Сортировка и группировка, а также выполнение агрегаций. Это выполняется опционально, если не найдено путей доступа для получения результата в нужном порядке.
Оптимизатор запросов в СУБД использует статистическую информацию, которая хранится хранящуюся в базе данных вместе с таблицами и индексами. На основе этой статистики оптимизатор оценивает альтернативные способы формирования результатов SQL-запроса, стараясь выбрать самый эффективный план выполнения. Просмотр и изучение планов выполнения запросов, созданных оптимизатором, позволяют оптимизировать их, изменив сам запрос или создав дополнительный индекс [1]. Выделяют 2 стратегии поиска оптимального плана [2]:
- стратегия грубой силы на основе оценки полного перебора всех вариантов и типов соединения таблиц, а также способов входа в них;
- стратегия генетического алгоритма на основе эволюционного изменения ограниченного числа вариантов соединения таблиц и оценки полученных перестановок.
В любом случае, оптимизация SQL-запроса сводится к поиску минимума функции стоимости от перестановки таблиц. Независимо от стратегии, оптимизатор анализирует стоимость для произвольной перестановки, предоставляемых другим алгоритмом. В СУБД Greenplum (GP) оптимизатор также оценивает множество стратегий выполнения SQL-запроса и выбирает наименее затратный метод с учетом следующих факторов [3]:
- количество строк в таблицах, которые необходимо объединить;
- доступность индексов и количество данных столбца при вычислении стоимости альтернативных планов выполнения;
- распределение данных по кластеру, чтобы свести к минимуму объем данных, передаваемых между сегментами для выполнения запроса.
Оптимизатор Greenplum создает планы выполнения SQL-запросов на основе статистики таблиц. Таким образом, чтобы составить лучший план выполнения запроса, эта статистическая информация должна быть точной. Для получения таких данных в GP есть специальный оператор ANALYZE, о котором мы поговорим далее.
Greenplum для инженеров данных и аналитиков данных
Код курса
GPDE
Ближайшая дата курса
27 января, 2025
Продолжительность
24 ак.часов
Стоимость обучения
72 000 руб.
Что такое оператор ANALYZE и зачем он нужен в Greenplum
В Greenplum обновление табличной статистики с помощью оператора ANALYZE позволяет планировщику запросов создавать оптимальные планы запросов. Когда таблица анализируется, информация о данных сохраняется в таблицах системного каталога. Если хранимая информация устарела, планировщик может создать неэффективные планы.
Запуск ANALYZE без аргументов обновляет статистику для всех таблиц в базе данных, что может занять довольно много времени. Поэтому рекомендуется применять оператор ANALYZE выборочно к конкретным таблицам, когда данные изменились, или использовать утилиту analyzedb, которая выполняет операции ANALYZE с таблицами постепенно и одновременно. Для добавления оптимизированных таблиц утилита analyzedb обновляет статистику только в том случае, если текущая уже не актуальна.
Запускать оператор ANALYZE рекомендуется в следующих случаях:
- после загрузки данных;
- после операций создания индексов (CREATE INDEX),
- после операций INSERT, UPDATE и DELETE, которые значительно изменяют базовые данные.
ANALYZE блокирует чтение таблиц, поэтому его можно запускать параллельно с некоторыми другими действиями СУБД. Однако, не следует запускать ANALYZE при выполнении операций загрузки, INSERT, UPDATE, DELETE и CREATE INDEX.
Greenplum для инженеров данных и аналитиков данных
Код курса
GPDE
Ближайшая дата курса
27 января, 2025
Продолжительность
24 ак.часов
Стоимость обучения
72 000 руб.
Поскольку запуск ANALYZE на большой таблице может выполняться долго, есть смысл сгенерировать статистику для ее отдельных столбцов через команду ANALYZE table(column, …). Как правило, сюда включаются столбцы, используемые в JOIN-соединениях и условиях WHERE, а также выражениях SORT, GROUP BY или HAVING. Для таблицы с большим количеством разделов можно применить ANALYZE только к отдельным разделам, которые изменились, например, при добавлении нового, запустив сбор статистики для родительской (основной) таблицы или для конечных узлов — файлов разделов, где фактически хранятся данные и статистика. Промежуточные файлы партиционированных таблиц не хранят данных или статистики, поэтому на них оператор ANALYZE не работает. Найти имена партиционированных таблиц и их разделов можно в системном каталоге pg_partitions с помощью команды SELECT partitiontablename from pg_partitions WHERE tablename=’parent_table.
Время создания статистики с помощью оператора ANALYZE зависит от ее качества (точности): чем точнее нужны данные, тем длительнее будет период их сбора. Поэтому, чтобы достичь разумного компромисса между ресурсами и результатом при исследовании большой таблицы, ANALYZE берет случайную выборку ее содержимого, а не анализирует каждую строку в отдельности. Для задания точности анализа используются следующие настроечные параметры оператора [4]:
- default_statistics_target – определяет количество выборочных значений для всех столбцов таблицы, от 1 до 1000, значение по умолчанию равно 100. Этот параметр применяется ко всем столбцам и указывает количество значений, которые хранятся в списке общих значений. Повышение default_statistics_target может улучшить качество оценок планировщика запросов, особенно для столбцов с нерегулярными шаблонами данных. Параметр default_statistics_target устанавливается на уровне мастера/сессии и требует перезагрузки.
- gp_autostats_mode вместе с параметром gp_autostats_on_change_threshold определяет, когда запускается операция автоматического анализа, чтобы планировщик добавил к запросу шаг ANALYZE. По умолчанию значение параметра gp_autostats_mode установлено on_no_stats, что запускает сбор статистики для операций CREATE TABLE AS SELECT, INSERT или COPY для любой таблицы, для которой нет существующей статистики. Установка значения on_change запустит сбор статистики только тогда, когда количество затронутых строк превышает порог, определенный параметром gp_autostats_on_change_threshold, по умолчанию равный 2147483647. Автоматический сбор статистики с on_change запускается для операций CREATE TABLE AS SELECT, UPDATE, DELETE, INSERT и COPY. Установка параметра gp_autostats_mode в значение none отключает автоматический сбор статистики.
Для партиционированных таблиц автоматический сбор статистики не запускается, если данные вставляются из родительской таблицы верхнего уровня, но выполняется, если данные вставляются непосредственно в конечную таблицу хранения данных [4].
Разобравшись с оператором анализа и сбора статистики по таблицам, отметим, какие инструменты помогают дата-аналитику и SQL-разработчику оптимизировать выполнение запросов в Greenplum. Такими операторами являются EXPLAIN и EXPLAIN ANALYZE [3], которые не только отображают план выполнения запроса и предполагаемую стоимость запроса, включая подробности JIT-компиляции и дополнительную полезную информацию, подробно о которой мы поговорим в следующий раз. А про статистику таблиц Apache Hive читайте в нашей новой статье.
Администрирование Greenplum / Arenadata DB
Код курса
GRAD
Ближайшая дата курса
2 декабря, 2024
Продолжительность
40 ак.часов
Стоимость обучения
120 000 руб.
Узнайте больше про администрирование и эксплуатацию MPP-СУБД Greenplum для эффективного хранения и аналитики больших данных на новом практическом курсе «Greenplum для инженеров данных» в нашем лицензированном учебном центре обучения и повышения квалификации для разработчиков, менеджеров, архитекторов, инженеров, администраторов, Data Scientist’ов и аналитиков Big Data в Москве.
