Как работает оптимизация PREWHERE?
Клаузула PREWHERE — это оптимизация выполнения запросов в ClickHouse. Она уменьшает ввод-вывод и ускоряет выполнение запросов, избегая ненужных чтений данных и фильтруя нерелевантные данные перед чтением некритичных колонок с диска.
Этот гид объясняет, как работает PREWHERE, как измерить его влияние и как настроить его для наилучшей производительности.
Обработка запросов без оптимизации PREWHERE
Начнем с иллюстрации того, как обрабатывается запрос к таблице uk_price_paid_simple без использования PREWHERE:
① Запрос включает фильтр по колонке town, которая является частью первичного ключа таблицы, а значит также частью первичного индекса.
② Чтобы ускорить запрос, ClickHouse загружает первичный индекс таблицы в память.
③ Он сканирует записи индекса, чтобы определить, какие гранулы из колонки town могут содержать строки, соответствующие предикату.
④ Эти потенциально релевантные гранулы загружаются в память вместе с позиционно выровненными гранулами из любых других колонок, необходимых для запроса.
⑤ Оставшиеся фильтры затем применяются во время выполнения запроса.
Как вы можете видеть, без PREWHERE все потенциально релевантные колонки загружаются перед фильтрацией, даже если лишь несколько строк фактически совпадают.
Как PREWHERE улучшает эффективность запроса
Следующие анимации показывают, как обрабатывается запрос с применением клаузулы PREWHERE ко всем предикатам запроса.
Первые три шага обработки такие же, как и прежде:
① Запрос включает фильтр по колонке town, которая является частью первичного ключа таблицы — и, следовательно, тоже частью первичного индекса.
② Подобно выполнению без клаузулы PREWHERE, чтобы ускорить запрос, ClickHouse загружает первичный индекс в память,
③ затем сканирует записи индекса, чтобы определить, какие гранулы из колонки town могут содержать строки, соответствующие предикату.
Теперь, благодаря клаузуле PREWHERE, следующий шаг отличается: вместо того чтобы сразу читать все релевантные колонки, ClickHouse фильтрует данные колонка за колонкой, загружая только то, что действительно нужно. Это значительно снижает ввод-вывод, особенно для широких таблиц.
На каждом шаге он загружает только гранулы, которые содержат хотя бы одну строку, прошедшую — т.е. совпавшую — с предыдущим фильтром. В результате количество гранул, которые нужно загрузить и оценить для каждого фильтра, уменьшается монотонно:
Шаг 1: Фильтрация по городу
ClickHouse начинает обработку PREWHERE, ① считывая выбранные гранулы из колонки town и проверяя, какие из них содержат строки, соответствующие London.
В нашем примере все выбранные гранулы совпадают, поэтому ② для следующей колонки-фильтра — date — соответствующие позиционно выровненные гранулы выбираются для обработки:
Шаг 2: Фильтрация по дате
Далее ClickHouse ① считывает выбранные гранулы колонки date, чтобы оценить фильтр date > '2024-12-31'.
В этом случае два из трех гранул содержат совпадающие строки, поэтому ② только их позиционно выровненные гранулы из следующей колонки-фильтра — price — выбираются для дальнейшей обработки:
Шаг 3: Фильтрация по цене
Наконец, ClickHouse ① считывает два выбранных гранула из колонки price, чтобы оценить последний фильтр price > 10_000.
Только один из двух гранул содержит совпадающие строки, поэтому ② его позиционно выровненный гранул из колонки SELECT — street — нужно загрузить для дальнейшей обработки:
На финальном шаге загружается только минимальный набор гранул колонок, содержащих совпадающие строки. Это приводит к меньшему использованию памяти, меньшему вводу-выводу с диска и более быстрому выполнению запроса.
Обратите внимание, что ClickHouse обрабатывает одно и то же количество строк как в версиях запроса с PREWHERE, так и без. Однако с применением оптимизаций PREWHERE не нужно загружать все значения колонок для каждой обработанной строки.
Оптимизация PREWHERE применяется автоматически
Клаузулу PREWHERE можно добавить вручную, как показано в примере выше. Тем не менее, вам не нужно писать PREWHERE вручную. Когда настройка optimize_move_to_prewhere включена (по умолчанию true), ClickHouse автоматически перемещает условия фильтрации из WHERE в PREWHERE, приоритизируя те, которые наибольшим образом уменьшают объем чтения.
Идея заключается в том, что меньшие колонки быстрее сканируются, и к моменту обработки больших колонок большинство гранул уже были отфильтрованы. Поскольку все колонки имеют одинаковое количество строк, размер колонки в первую очередь определяется ее типом данных. Например, колонка UInt8 обычно намного меньше колонки String.
ClickHouse использует эту стратегию по умолчанию, начиная с версии 23.2, сортируя колонки фильтров PREWHERE для многоэтапной обработки в порядке увеличения не сжатого размера.
Начиная с версии 23.11, необязательная статистика колонок может дополнительно улучшить это, выбирая порядок обработки фильтров на основе фактической селективности данных, а не только размера колонок.
Как измерить влияние PREWHERE
Чтобы убедиться, что PREWHERE действительно помогает вашим запросам, вы можете сравнить производительность запроса с включенной и отключенной настройкой optimize_move_to_prewhere.
Начнем с выполнения запроса с отключенной настройкой optimize_move_to_prewhere:
ClickHouse прочитал 23.36 MB данных колонки, обработав 2.31 миллиона строк запроса.
Затем мы выполняем запрос с включенной настройкой optimize_move_to_prewhere. (Обратите внимание, что эта настройка является необязательной, так как она по умолчанию включена):
То же количество строк было обработано (2.31 миллиона), но благодаря PREWHERE ClickHouse прочитал более чем в три раза меньше данных колонки — всего 6.74 MB вместо 23.36 MB — что сократило общее время выполнения в три раза.
Для получения более глубокого понимания того, как ClickHouse применяет PREWHERE за кулисами, используйте EXPLAIN и журналы трассировки.
Мы исследуем логический план запроса, используя клаузулу EXPLAIN:
Большую часть вывода плана мы опускаем, поскольку он довольно подробный. По сути, он показывает, что все три предиката колонок были автоматически перемещены в PREWHERE.
Когда вы будете воспроизводить это самостоятельно, вы также увидите в плане запроса, что порядок этих предикатов основан на размерах типов данных колонок. Поскольку мы не включали статистику колонок, ClickHouse использует размер как запасной вариант для определения порядка обработки PREWHERE.
Если вы хотите углубиться еще дальше, вы можете наблюдать каждую отдельную операцию PREWHERE, запросив ClickHouse вернуть все журнальные записи уровня тестирования во время выполнения запроса:
Основные выводы
- PREWHERE избегает чтения данных колонок, которые будут затем отфильтрованы, экономя ввод-вывод и память.
- Она работает автоматически, когда
optimize_move_to_prewhereвключена (по умолчанию). - Порядок фильтрации имеет значение: небольшие и селективные колонки должны идти первыми.
- Используйте
EXPLAINи журналы, чтобы проверить, применяется ли PREWHERE и понять его влияние. - PREWHERE имеет наибольшее влияние на широкие таблицы и большие выборки с селективными фильтрами.
