Новое время — новый Greenplum

Мы приглашаем вас принять участие в конференции, посвященной будущему Open-Source Greenplum 19 сентября в 18:00:00 UTC +3. Встреча будет проходить в гибридном формате — и офлайн, и онлайн. Онлайн-трансляция будет доступна для всех желающих.

Внезапное закрытие Greenplum его владельцем — компанией Broadcom - стало неприятным сюрпризом для всех, кто использует или планирует начать использовать решения на базе этой технологии. Многие ожидают выхода стабильной версии Greenplum 7 и надеются на её дальнейшее активное развитие.

Arenadata не могла допустить, чтобы разрабатываемый годами Open-Source проект Greenplum прекратил своё существование, поэтому 19 сентября мы представим наш ответ на данное решение Broadcom, а участники сообщества получат исчерпывающие разъяснения на все вопросы о дальнейшей судьбе этой технологии.

На конференции вас ждёт обсуждение следующих тем:

План возрождения Greenplum;
Дорожная карта;
Экспертное обсуждение и консультации.

Осталось до события

Главная
Arenadata QuickMarts
Базовые операции
Настройки производительности
Повышение производительности запросов

Повышение производительности запросов

Елена Дворядкина

Содержание

Индексы
Строгая типизация данных
- Пример
Сжатие и шифрование данных
- Пример
Кеширование запросов
PREWHERE
- Пример
Оптимизация операций JOIN

В этой статье описываются инструменты, которые предоставляет ADQM для повышения производительности запросов.

Индексы

Высокая производительность аналитических запросов в ADQM, как правило, достигается за счет индексирования таблиц — правильное определение табличных индексов может существенно сократить объем данных, которые необходимо будет считывать с диска для выполнения запросов.

Для таблиц семейства MergeTree ADQM поддерживает следующие типы индексов:

Первичный индекс — позволяет быстро находить диапазоны строк, которые потенциально содержат нужные значения. Ключ, по которому строится первичный индекс, может быть составным, то есть включать несколько столбцов.
Индексы пропуска данных — хранят некоторую дополнительную информацию по указанному столбцу в блоках данных и при выполнении запросов на чтение позволяют пропускать блоки данных, в которых точно нет запрашиваемых значений. Тип информации о содержимом блока данных, которую агрегирует и хранит индекс пропуска данных, определяется типом индекса: MinMax, Set, Bloom Filter или Inverted.

РЕКОМЕНДАЦИЯ

Подробную информацию о том, как работают индексы ADQM и как ими управлять, можно получить в статье Индексы.

Ниже приведены примеры, демонстрирующие как использование первичного индекса и индекса пропуска данных (например, типа Set) влияет на скорость чтения данных из таблицы MergeTree.

Первичный индекс

Создайте таблицу без первичного ключа (с одним числовым столбцом и одним строковым) и заполните ее тестовыми данными:

CREATE TABLE table_no_primary_key (a Int64, b String) ENGINE = MergeTree ORDER BY tuple();

INSERT INTO table_no_primary_key
SELECT toInt64(randUniform(0, 1000)), ['a', 'b', 'c', 'd', 'e'][(rand() % 5) + 1]
FROM numbers(100000000);

Выполните запрос, который вычисляет количество строк со значением 555 в столбце a:
SELECT count() FROM table_no_primary_key WHERE a = 555;
В результате видно, что выполнилось полное сканирование таблицы — ADQM обработал 100 миллионов строк, на что потребовалось примерно 0.5 секунды:
┌─count()─┐ │ 100162 │ └─────────┘ 1 row in set. Elapsed: 0.512 sec. Processed 100.00 million rows, 800.00 MB (195.30 million rows/s., 1.56 GB/s.)
Чтобы улучшить производительность этого запроса, нужно переопределить таблицу так, чтобы первый столбец был указан в качестве первичного ключа:
CREATE TABLE table_with_primary_key (a Int64, b String) ENGINE = MergeTree ORDER BY (a);
Заполните новую таблицы данными из первой таблицы:
INSERT INTO table_with_primary_key SELECT * FROM table_no_primary_key;

Повторите запрос с фильтрацией по столбцу a:

SELECT count() FROM table_with_primary_key WHERE a = 555;

В этом случае ADQM обработает 172 тысячи строк вместо 100 миллионов и выполнит запрос значительно быстрее:

┌─count()─┐
│  100162 │
└─────────┘

1 row in set. Elapsed: 0.004 sec. Processed 172.03 thousand rows, 1.38 MB (43.90 million rows/s., 351.17 MB/s.)

Если для запроса использовать выражение EXPLAIN с параметром indexes, то можно увидеть, что в процессе выполнения запроса из 12209 гранул была выбрана только 21, где, возможно, содержатся строки со значением 555 в столбце a:

EXPLAIN indexes = 1 SELECT count() FROM table_with_primary_key WHERE a = 555;

┌─explain────────────────────────────────────────────────────┐
│ Expression ((Projection + Before ORDER BY))                │
│   Aggregating                                              │
│     Expression (Before GROUP BY)                           │
│       Filter (WHERE)                                       │
│         ReadFromMergeTree (default.table_with_primary_key) │
│         Indexes:                                           │
│           PrimaryKey                                       │
│             Keys:                                          │
│               a                                            │
│             Condition: (a in [555, 555])                   │
│             Parts: 8/8                                     │
│             Granules: 21/12209                             │
└────────────────────────────────────────────────────────────┘

Cоставной ключ первичного индекса

Создайте таблицу с составным первичным ключом:
CREATE TABLE table_compound_primary_key (a Int64, b String) ENGINE = MergeTree ORDER BY (a, b);
Заполните таблицу данными:
INSERT INTO table_compound_primary_key SELECT toInt64(randUniform(0, 1000)), ['a', 'b', 'c', 'd', 'e'][(rand() % 5) + 1] FROM numbers(100000000);
Обратите внимание, в этом примере столбцы таблицы сильно различаются по кардинальности — первый столбец заполняется целыми числами из диапазона [0, 1000), а во втором содержится всего пять строковых значений.

Посмотрите скорость выполнения отдельных запросов с фильтрацией по первому и по второму столбцу первичного ключа:

Фильтрация по столбцу a Фильтрация по столбцу b

SELECT count()
FROM table_compound_primary_key
WHERE a = 555;

┌─count()─┐
│  100162 │
└─────────┘

1 row in set. Elapsed: 0.004 sec.
Processed 155.65 thousand rows, 1.25 MB
(38.72 million rows/s., 309.78 MB/s.)

SELECT count()
FROM table_compound_primary_key
WHERE b = 'c';

┌──count()─┐
│ 19997249 │
└──────────┘

1 row in set. Elapsed: 1.050 sec.
Processed 44.17 million rows, 441.66 MB
(42.07 million rows/s., 420.70 MB/s.)

Поменяйте столбцы в первичном ключе местами, упорядочив их по кардинальности от низкой к более высокой, согласно рекомендациям:
CREATE TABLE table_compound_primary_key1 (a Int64, b String) ENGINE = MergeTree ORDER BY (b, a);
INSERT INTO table_compound_primary_key1 SELECT * from table_compound_primary_key;

Посмотрите, как изменилась скорость выполнения тех же запросов с фильтрацией по каждому столбцу. Запрос, фильтрующий данные по столбцу b, выполняется теперь в два раза быстрее, при этом время выполнения запроса, фильтрующего по столбцу a, не увеличилось:

Фильтрация по столбцу a Фильтрация по столбцу b

SELECT count()
FROM table_compound_primary_key1
WHERE a = 555;

┌─count()─┐
│  100162 │
└─────────┘

1 row in set. Elapsed: 0.004 sec.
Processed 540.67 thousand rows, 4.33 MB
(123.51 million rows/s., 988.06 MB/s.)

SELECT count()
FROM table_compound_primary_key1
WHERE b = 'c';

┌──count()─┐
│ 19997249 │
└──────────┘

1 row in set. Elapsed: 0.566 sec.
Processed 20.01 million rows, 200.13 MB
(35.38 million rows/s., 353.83 MB/s.)

Индекс пропуска данных (Set)

Создайте тестовую таблицу с 10000 строк в каждой грануле и добавьте в нее 100 миллионов строк данных:

CREATE TABLE table_skip_index (a UInt64, b UInt64)
ENGINE MergeTree
PRIMARY KEY a
SETTINGS index_granularity=10000;

INSERT INTO table_skip_index SELECT number, intDiv(number,5000) FROM numbers(100000000);

При выполнении запроса с фильтрацией по столбцу b, который не входит в первичный ключ, сканируются все 100 миллионов записей:

SELECT * FROM table_skip_index WHERE b=100 OR b=555;

┌──────a─┬───b─┐
│ 500000 │ 100 │
│ 500001 │ 100 │
│ 500002 │ 100 │
│    ... │ ... │
└────────┴─────┘
10000 rows in set. Elapsed: 0.042 sec. Processed 100.00 million rows, 800.12 MB (2.38 billion rows/s., 19.01 GB/s.)

Добавьте индекс пропуска данных:
ALTER TABLE table_skip_index ADD INDEX my_index b TYPE set(100) GRANULARITY 2;
На этом этапе индекс будет применяться только к новыми данным, добавляемым в таблицу. Чтобы проиндексировать уже существующие в таблице данные, выполните следующий запрос:
ALTER TABLE table_skip_index MATERIALIZE INDEX my_index;
Повторно выполните запрос на выборку данных с тем же фильтром:
SELECT * FROM table_skip_index WHERE b=100 OR b=555;
Вместо обработки 100 миллионов строк (800 МБ) ADQM прочитает и проанализирует только 40 тысяч строк (440 КБ) — четыре гранулы по 10000 строк каждая.
┌──────a─┬───b─┐ │ 500000 │ 100 │ │ 500001 │ 100 │ │ 500002 │ 100 │ │ ... │ ... │ └────────┴─────┘ 10000 rows in set. Elapsed: 0.018 sec. Processed 40.00 thousand rows, 440.00 KB (2.19 million rows/s., 24.06 MB/s.)

Строгая типизация данных

Чтобы обеспечить высокую производительность запросов и минимизировать объем хранилища, важно проанализировать данные, которые будут храниться в таблице, и назначить каждому столбцу наиболее подходящий тип. Например, если столбец имеет низкую кардинальность (low cardinality), то есть содержит небольшой набор повторяющихся строковых значений, хранение этих значений можно оптимизировать, используя для столбца тип LowCardinality вместо String.

Пример

Создайте таблицу с двумя столбцами типа Int64 и String:
CREATE TABLE table_types (a Int64, b String) ENGINE = MergeTree ORDER BY a;
Вставьте в таблицу 100 миллионов строк с тестовыми данными (в каждой строке первого столбца хранится целое число из диапазона [0,1000), второго столбца — одна из пяти предопределенных строк):
INSERT INTO table_types SELECT toInt64(randUniform(0, 1000)), ['a', 'b', 'c', 'd', 'e'][(rand() % 5) + 1] FROM numbers(100000000);

Посмотрите объем несжатых данных:

SELECT
    name,
    type,
    formatReadableSize(data_uncompressed_bytes) AS uncompressed_size
FROM system.columns
WHERE table = 'table_types';

┌─name─┬─type───┬─uncompressed_size─┐
│ a    │ Int64  │ 759.91 MiB        │
│ b    │ String │ 189.98 MiB        │
└──────┴────────┴───────────────────┘

Зная диапазон значений в столбце a, можно использовать в схеме таблицы более подходящий тип для целочисленных данных — UInt16 вместо Int64 (см. диапазоны значений, поддерживаемые различными типами данных, в статье UInt*). Хранение строк в столбце b также можно оптимизировать, заменив тип String на LowCardinality. Создайте таблицу, аналогичную созданной ранее, но со строгими типами данных для столбцов:
CREATE TABLE table_strict_types (a UInt16, b LowCardinality(String)) ENGINE = MergeTree ORDER BY a;
INSERT INTO table_strict_types SELECT a, toLowCardinality(b) from table_types;

Проверьте, что размер данных в столбцах уменьшился:

SELECT
    name,
    type,
    formatReadableSize(data_uncompressed_bytes) AS uncompressed_size
FROM system.columns
WHERE table = 'table_strict_types';

┌─name─┬─type───────────────────┬─uncompressed_size─┐
│ a    │ UInt16                 │ 189.98 MiB        │
│ b    │ LowCardinality(String) │ 95.32 MiB         │
└──────┴────────────────────────┴───────────────────┘

Сравните выполнение одного и того же тестового запроса для первоначальной таблицы table_types и таблицы с оптимальными типами столбцов table_strict_types:
SELECT count() FROM table_types WHERE b = 'a';
┌──count()─┐ │ 20003609 │ └──────────┘ 1 row in set. Elapsed: 5.330 sec. Processed 100.00 million rows, 1.00 GB (18.76 million rows/s., 187.61 MB/s.)
SELECT count() FROM table_strict_types WHERE b = 'a';
┌──count()─┐ │ 20003609 │ └──────────┘ 1 row in set. Elapsed: 0.390 sec. Processed 100.00 million rows, 100.12 MB (256.40 million rows/s., 256.71 MB/s.)
В результате видно, что оба запроса сканируют одинаковое количество строк, но запрос, обращающийся к первой таблице, считывает объем данных почти в 10 раз больше.

Сжатие и шифрование данных

При выполнении запроса необходимо считывать данные с диска, и чем больше данных считывается, тем медленнее запрос. Объем считываемых данных можно уменьшить с помощью сжатия и шифрования. ADQM поддерживает кодеки сжатия общего назначения, которые позволяют обеспечить оптимальное соотношение между потреблением дискового пространства и ресурсов CPU, набор специализированных кодеков сжатия для определенных типов данных, а также кодеки шифрования. Полный список доступных кодеков сжатия и шифрования приведен в разделе Column Compression Codecs документации ClickHouse.

По умолчанию, ADQM применяет к столбцу метод сжатия, определенный в конфигурации сервера. Кроме того, можно указать метод сжатия для каждого отдельного столбца в запросе CREATE TABLE с помощью выражения CODEC:

CREATE TABLE <table_name>
(   <column_name1> <data_type1> CODEC(<compression_codec1>) [...],
    <column_name2> <data_type2> CODEC(<compression_codec2>) [...],
    ...)
ENGINE = MergeTree
...;

Кодеки можно последовательно комбинировать (например, CODEC(Delta, Default)).

Пример

Создайте тестовую таблицу с тремя столбцами — первый и второй столбцы сжимаются по алгоритмам LZ4 и ZSTD соответственно, третий хранит данные без сжатия:
CREATE TABLE table_sizes (a Int32 CODEC(LZ4), b Int32 CODEC(ZSTD), c Int32 CODEC(NONE)) ENGINE = MergeTree ORDER BY a;
Вставьте 100 миллионов строк с одинаковыми значениями во всех столбцах (например, 1):
INSERT INTO table_sizes SELECT 1, 1, 1 FROM numbers(100000000);

В системной таблице system.columns можно посмотреть размер хранимых данных и увидеть разницу между столбцами. Первые два сжатых разными кодеками столбца хранят 1.7 МБ и 279 КБ данных соответственно, а несжатый — около 380 МБ:

SELECT
    name,
    formatReadableSize(data_uncompressed_bytes) AS uncompressed_size,
    formatReadableSize(data_compressed_bytes) AS compressed_size,
    round(data_uncompressed_bytes / data_compressed_bytes, 2) AS ratio
FROM system.columns
WHERE table = 'table_sizes';

┌─name─┬─uncompressed_size─┬─compressed_size─┬───ratio─┐
│ a    │ 379.95 MiB        │ 1.71 MiB        │  222.15 │
│ b    │ 379.95 MiB        │ 279.06 KiB      │ 1394.21 │
│ c    │ 379.95 MiB        │ 380.10 MiB      │       1 │
└──────┴───────────────────┴─────────────────┴─────────┘

Запустите запрос, который вычисляет сумму по каждому столбцу, чтобы увидеть разницу в работе со сжатыми и несжатыми столбцами:

SELECT sum(a) FROM table_sizes;

┌────sum(a)─┐
│ 100000000 │
└───────────┘

1 row in set. Elapsed: 0.197 sec. Processed 100.00 million rows, 400.00 MB (507.58 million rows/s., 2.03 GB/s.)

SELECT sum(b) FROM table_sizes;

┌────sum(b)─┐
│ 100000000 │
└───────────┘

1 row in set. Elapsed: 0.091 sec. Processed 100.00 million rows, 400.00 MB (1.09 billion rows/s., 4.38 GB/s.)

SELECT sum(c) FROM table_sizes;

┌────sum(c)─┐
│ 100000000 │
└───────────┘

1 row in set. Elapsed: 0.389 sec. Processed 100.00 million rows, 400.00 MB (257.00 million rows/s., 1.03 GB/s.)

Все три запроса сканируют по 100 миллионов строк, но сканирование сжатых данных выполняется быстрее: 0.197 и 0.091 секунды против 0.389 секунды для несжатых данных.

Кеширование запросов

Еще один способ сократить время выполнения запросов SELECT, а также снизить нагрузку на сервер ADQM и оптимизировать потребление ресурсов — кеширование запросов. В этом случае запрос вычисляется один раз, результат сохраняется в кеше и используется в дальнейшем при повторном выполнении того же запроса. Если запрос не выполнился из-за ошибки или отмены пользователем, запись в кеш запросов не добавляется.

Управление кешированием запросов

Включите поддержку кеширования запросов в ADQM на уровне сессии:
SET allow_experimental_query_cache = 1;
Установите значение настройки use_query_cache в 1, чтобы сохранять в кеш результаты конкретного запроса или всех запросов текущей сессии:
- SELECT … SETTINGS use_query_cache = 1; — кеширование на уровне отдельного запроса;
- SET use_query_cache = 1; — кеширование запросов на уровне сессии.
Последующие выполнения запросов (также с параметром use_query_cache = 1) будут считывать вычисленный ранее результат из кеша и немедленно возвращать его.

Для более детальной настройки кеширования можно также использовать параметры (по умолчанию, оба значения 1):
- enable_writes_to_query_cache — определяет, должны ли результаты запроса SELECT сохраняться в кеш запросов;
- enable_reads_from_query_cache — определяет, должны ли результаты запроса SELECT извлекаться из кеша запросов.

ПРИМЕЧАНИЕ

Настройка use_query_cache и другие параметры, связанные с кешированием запросов, влияют только на автономные запросы SELECT.

Очистить кеш запросов можно с помощью команды SYSTEM DROP QUERY CACHE.

Настройка кеша запросов

Чтобы обеспечить возможность более детально управлять поведением кеша запросов, ADQM предоставляет набор конфигурационных параметров.

Настройки на уровне сервера

Размер кеша запросов (в байтах), максимальное количество записей в кеше и максимальный размер отдельных записей кеша (в байтах и в строках) можно настроить с помощью соответствующих параметров в секции query_cache конфигурационного файла сервера config.xml.

<query_cache>
    <max_size>1073741824</max_size>
    <max_entries>1024</max_entries>
    <max_entry_size>1048576</max_entry_size>
    <max_entry_rows>30000000</max_entry_rows>
</query_cache>

Настройки на уровне запроса/сеанса

Чтобы кешировать только самые частые или затратные с точки зрения времени запросы, можно настроить следующие параметры:
- query_cache_min_query_runs — сколько раз (минимум) должен быть выполнен запрос SELECT, прежде чем его результат будет сохранен в кеше запросов;
- query_cache_min_query_duration — минимальная продолжительность запроса (в миллисекундах), результаты которого будут записываться в кеш запросов.
Записи в кеше запросов устаревают по истечении определенного периода времени (по умолчанию, 60 секунд). Этот период можно настроить с помощью параметра query_cache_ttl.
Записи кеша запросов по умолчанию не передаются между пользователями из соображений безопасности. Однако при необходимости записи кеша можно отметить как доступные для других пользователей (то есть как общие), установив значение параметра query_cache_share_between_users в 1.
Результаты запросов с недетерминированными функциями (например, rand(), now()) по умолчанию не кешируются. Чтобы это изменить, используйте параметр query_cache_store_results_of_queries_with_nondeterministic_functions.

Просмотр информации о кеше запросов

Содержимое кеша запросов можно посмотреть в системной таблице system.query_cache:

SELECT * FROM system.query_cache;

┌─query───────────────────────────────────────────────────┬────────────key_hash─┬──────────expires_at─┬─stale─┬─shared─┬─result_size─┐
│ SELECT count() FROM table_types WHERE b = 'a' SETTINGS  │ 3145443392323842376 │ 2023-10-18 12:58:43 │     1 │      0 │         256 │
└─────────────────────────────────────────────────────────┴─────────────────────┴─────────────────────┴───────┴────────┴─────────────┘

В примере выше запись в кеше помечена как устаревшая (stale), так как время, прошедшее с момента кеширования результата запроса, превышает время жизни записи кеша. Это означает, что при следующем запуске запроса не будет использоваться результат кешированного запроса, а вместо этого обновится запись кеша.

Выражение SETTINGS в тексте запроса (поле query) показывается частично, так как осуществляется внутренняя очистка всех параметров, связанных с кешем запросов, прежде чем запрос будет использоваться в качестве ключа для кеша запросов.

Количество попаданий и промахов кеша запросов с момента запуска сервера учитываются как события QueryCacheHits и QueryCacheMisses в системной таблице system.events. Оба счетчика обновляются только для запросов SELECT, которые выполняются с настройкой use_query_cache = 1.

SELECT * FROM system.events WHERE (event = 'QueryCacheHits' OR event = 'QueryCacheMisses');

┌─event────────────┬─value─┬─description────────────────────────────────────────────────────────────────────────────────────────────┐
│ QueryCacheHits   │     2 │ Number of times a query result has been found in the query cache (and query computation was avoided).  │
│ QueryCacheMisses │     4 │ Number of times a query result has not been found in the query cache (and required query computation). │
└──────────────────┴───────┴────────────────────────────────────────────────────────────────────────────────────────────────────────┘

Пример

Выполните запрос SELECT, обращающийся к неоптимизированной таблице table_types, созданой выше:
SELECT count() FROM table_types WHERE b = 'a';
На выполнение запроса требуется примерно 2.5 секунды:
┌──count()─┐ │ 19999789 │ └──────────┘ 1 row in set. Elapsed: 2.584 sec. Processed 100.00 million rows, 1.00 GB (38.70 million rows/s., 387.00 MB/s.)
Подключите возможность кеширования запросов и повторите выполнение запроса с активированной настройкой use_query_cache:
SET allow_experimental_query_cache = 1;
SELECT count() FROM table_types WHERE b = 'a' SETTINGS use_query_cache = 1;
┌──count()─┐ │ 19999789 │ └──────────┘ 1 row in set. Elapsed: 2.924 sec. Processed 100.00 million rows, 1.00 GB (34.20 million rows/s., 341.98 MB/s.)
Как видно, на выполнение запроса потребовалось примерно столько же времени (даже чуть больше), поскольку при первом запуске запроса с настройкой use_query_cache = 1 результат его выполнения запишется в кеш. Последующие выполнения этого же запроса (также с настройкой use_query_cache = 1) в пределах интервала времени, указанного в query_cache_ttl, будут считывать результат из кеша и возвращать его немедленно:
SELECT count() FROM table_types WHERE b = 'a' SETTINGS use_query_cache = 1;
┌──count()─┐ │ 19999789 │ └──────────┘ 1 row in set. Elapsed: 0.003 sec.

PREWHERE

PREWHERE — способ оптимизации запроса SELECT … WHERE, позволяющий выполнить фильтрацию данных до операции чтения, тем самым уменьшить объем данных, которые будут считываться из таблицы, и ускорить выполнение запроса. В ADQM оптимизация фильтрации PREWHERE применяется автоматически (при включенной по умолчанию настройке optimize_move_to_prewhere) — даже если выражение PREWHERE явно не указано в запросе, часть условий фильтрации WHERE перемещается в PREWHERE и проверяется в первую очередь.

Вы можете явно указать в запросе выражение PREWHERE, чтобы скорректировать оптимизацию фильтрации вручную. Сначала из запрашиваемой таблицы считываются столбцы, указанные в PREWHERE. Затем считываются другие столбцы, необходимые для выполнения остальной части запроса, но только те блоки, в которых условие PREWHERE выполняется хотя бы для некоторых строк. Если в большом количестве блоков условие PREWHERE не выполняется для всех строк и выражение PREWHERE использует меньше столбцов, чем другие части запроса, оптимизация через PREWHERE может существенно сократить количество данных, считываемых с диска для выполнения запроса.

В запросе могут быть одновременно указаны выражения PREWHERE и WHERE (сначала выполнится PREWHERE, затем — WHERE).

Если в запросе есть модификатор FINAL, оптимизация PREWHERE не всегда выполняется корректно. Она действует только если включены обе настройки optimize_move_to_prewhere и optimize_move_to_prewhere_if_final.

Пример

Создайте тестовую таблицу с тремя столбцами и добавьте в нее 10 миллионов строк (в первом столбце — целые числа по порядку, во втором — случайные числа в диапазоне [0, 1000), в третьем — значение test string в 100 строках, значения для остальных строк сконвертированы из соответствующих числовых идентификаторов в первом столбце):
CREATE TABLE table_prewhere (id UInt64, int_value Int64, string_value String) ENGINE = MergeTree ORDER BY id;
INSERT INTO table_prewhere SELECT number, toInt64(randUniform(0, 1000)), if(number between 11 and 110, 'test string', toString(number)) FROM numbers(10000000);

Выполните запрос, фильтрующий данные по столбцам, которые не входят в первичный ключ:

SELECT count()
FROM table_prewhere
WHERE (int_value > 550) AND (string_value = 'test string');

┌─count()─┐
│      43 │
└─────────┘
1 row in set. Elapsed: 0.638 sec. Processed 10.00 million rows, 238.89 MB (15.68 million rows/s., 374.58 MB/s.)

Если перед выполнением запроса включить трассировку командой SET send_logs_level='debug', то можно увидеть, что ADQM автоматически переместил первое условие фильтрации int_value > 550 в PREWHERE:
InterpreterSelectQuery: MergeTreeWhereOptimizer: condition "int_value > 550" moved to PREWHERE
Таким образом, запрос сначала получает много строк по условию int_value > 550, из которых большая часть потом отфильтровывается по условию string_value = 'test string'. Чтобы оптимизировать запрос, измените порядок фильтрации, явно передав условие фильтрации по столбцу string_value в PREWHERE:
SELECT count() FROM table_prewhere PREWHERE string_value = 'test string' WHERE int_value > 550;
В этом случае запрос сначала по условию string_value = 'test string' получает небольшой набор строк, который затем проверяется на второе условие int_value > 550. В результате обрабатывается меньше данных (158.89 МБ вместо 238.89 МБ) и запрос выполняется быстрее:
┌─count()─┐ │ 43 │ └─────────┘ 1 row in set. Elapsed: 0.249 sec. Processed 10.00 million rows, 158.89 MB (40.12 million rows/s., 637.45 MB/s.)

Оптимизация операций JOIN

Операции JOIN в ADQM можно оптимизировать несколькими способами для повышения производительности запросов. Ниже приведены некоторые рекомендации:

ADQM поддерживает несколько алгоритмов выполнения соединения — выберите алгоритм, наиболее оптимальный для вашего запроса с учетом применяемого типа соединения, строгости и движка объединяемых таблиц. Устанавливается алгоритм соединения с помощью настройки join_algorithm — можно назначить один или несколько алгоритмов (тогда один из доступных алгоритмов будет выбираться для каждого конкретного запроса на основе его типа/строгости и движка таблицы) или разрешить планировщику запросов выбирать и динамически изменять алгоритм во время выполнения запроса в зависимости от доступности ресурсов. Сравнение алгоритмов соединения и рекомендации по выбору алгоритмов, подходящих для разных сценариев, можно найти в блог-посте ClickHouse Choosing the Right Join Algorithm.
Используйте оптимальные ключи соединения (столбцы, по которым сопоставляются строки между двумя таблицами). В идеале ключи соединения должны быть проиндексированы и иметь низкую кардинальность (небольшое количество уникальных значений). Тип данных ключа также может влиять на производительность (например, использование строкового ключа соединения может быть медленнее, чем использование целочисленного ключа).
При использовании JOIN в запросе отсутствует оптимизация порядка выполнения соединения по отношению к другим стадиям запроса. Соединение (поиск в правой таблице) выполняется до фильтрации WHERE и до агрегации. Чтобы явно указать порядок вычислений и повысить производительность запроса, рекомендуется применять фильтр как можно раньше, то есть соединять подзапросы.
Результаты соединений не кешируются — каждый раз для выполнения запроса с одинаковым JOIN, подзапрос соединения выполняется заново. Чтобы этого избежать, можно использовать специальный табличный движок Join, представляющий собой подготовленное множество для соединения, которое всегда находится в оперативной памяти.
Для небольших объемов данных (до ~200 ГБ в сжатом виде) рекомендуется использовать не меньший объем памяти.
Иногда вместо JOIN более эффективно использовать IN или словари. При этом не рекомендуется использовать JOIN со словарями, поскольку функции dictGet работают эффективнее.
В некоторых сценариях эффективнее денормализовать данные путем объединения нескольких таблиц в одну, чтобы полностью исключить использование операций JOIN и повысить производительность запросов.

Нашли ошибку? Выделите текст и нажмите Ctrl+Enter чтобы сообщить о ней