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

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

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

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

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

Данные таблицы MergeTree делятся на части — куски данных (data parts), строки внутри которых сортируются по столбцам первичного ключа. Каждый кусок данных логически делится на гранулы (granules). Гранула — минимальный неделимый набор данных, состоящий из целого количества строк, который считывается при выборке данных. Первая строка гранулы помечается значениями столбцов первичного ключа для этой строки — такая отметка называется засечкой (mark). Для каждого куска данных создается свой индекс — файл с засечками. Таким образом, в ADQM не индексируется каждая отдельная строка, а первичный индекс куска данных имеет одну запись (засечку) на группу строк (гранулу), то есть используется разреженный индекс (sparse index). Вместо непосредственного поиска отдельных строк по всей таблице разреженный первичный индекс позволяет быстро (с помощью бинарного поиска) идентифицировать гранулы, которые могут включать строки, соответствующие фильтру запроса. Затем найденные группы потенциально подходящих строк параллельно загружаются в память для поиска в них строк, точно соответствующих условию фильтрации.

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

Назначение первичного ключа

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

Чтобы назначить таблице MergeTree первичный ключ, используйте выражение ORDER BY или PRIMARY KEY в запросе CREATE TABLE при создании таблицы:

CREATE TABLE <table_name> (<column_name> <column_type>, ...)
ENGINE = MergeTree
ORDER BY <sorting_key_expr>
[PRIMARY KEY <primary_key_expr>];

  • ORDER BY — ключ сортировки в виде одного или нескольких столбцов таблицы. ADQM использует ключ сортировки в качестве первичного ключа, если первичный ключ не задан с помощью выражения PRIMARY KEY.

  • PRIMARY KEY — первичный ключ. По умолчанию первичный ключ совпадает с ключом сортировки ORDER BY, поэтому в большинстве случаев выражение PRIMARY KEY отдельно использовать не нужно. Но при необходимости можно указать первичный ключ (столбцы, значения которых будут записаны как засечки в индексный файл), отличный от ключа сортировки (столбцы, по которым будут упорядочены строки в кусках данных). В этом случае кортеж столбцов первичного ключа должен быть префиксом кортежа столбцов ключа сортировки (например, если первичный ключ — (a, b), то ключ сортировки должен быть (a, b, c…​)).

Для каждой таблицы может быть определен только один первичный ключ. Можно создать таблицу без первичного ключа — для этого используйте синтаксис ORDER BY tuple().

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

Пример

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

    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);

  2. Выполните запрос, который вычисляет количество строк со значением 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.)

  3. Чтобы улучшить производительность этого запроса, нужно переопределить таблицу так, чтобы первый столбец был указан в качестве первичного ключа:

    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;

  4. Повторите запрос с фильтрацией по столбцу 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_key1 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                             │
└────────────────────────────────────────────────────────────┘

Составной первичный ключ

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

  • эффективность фильтрации по дополнительным ключевым столбцам в запросах;

  • степень сжатия файлов данных таблицы.

Когда запрос фильтрует данные по первому ключевому столбцу, ADQM запускает алгоритм бинарного поиска по индексным засечкам этого столбца. Для фильтрации по другим ключевым столбцам применяется менее эффективный алгоритм generic exclusion search. Например, первичный ключ (a, b) будет полезен для ускорения запросов, фильтрующих данные по столбцу a, но не улучшит производительность запросов с фильтрацией по столбцу b, несмотря на то, что столбец b является частью составного первичного ключа. И наоборот, первичный индекс таблицы с ключом (b, a) будет ускорять запросы с фильтрацией по столбцу b, но не оптимизирует запросы, фильтрующие данные по столбцу a.

Если нужно ускорить оба типа запросов (с фильтрацией по первому столбцу и с фильтрацией по второму столбцу) и при этом кардинальность (количество различных значений) столбцов составного первичного ключа одинаково высокая, имеет смысл удалить второй ключевой столбец из первичного индекса, что приведет к меньшему потреблению памяти индексом, и вместо этого использовать несколько первичных индексов. Чтобы это реализовать, можно:

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

Пример

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

    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), а во втором содержится всего пять строковых значений.

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

    Фильтрация по столбцу 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.)

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

    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;

  4. Посмотрите, как изменилась скорость выполнения тех же запросов с фильтрацией по каждому столбцу. Запрос, фильтрующий данные по столбцу 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.)

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

Для таблиц семейства MergeTree можно также указать индексы пропуска данных (data skipping indexes). Индекс пропуска данных агрегирует некоторую информацию по заданному выражению (столбцу) в блоках данных. Затем эта информация используются при выполнении запросов SELECT для уменьшения объема считываемых с диска данных путем пропуска блоков данных, в которых гарантированно нет совпадений по условию фильтрации запроса. Пропускаемый блок состоит из гранул данных в количестве, равном гранулярности данного индекса.

Управление индексами

Индексы пропуска данных описываются с помощью выражения INDEX в секции описания столбцов запроса CREATE TABLE:

CREATE TABLE <table_name>
(   <column_name> <column_type>, ...
    INDEX <index_name> <index_expr> TYPE <index_type> [GRANULARITY <granularity_value>], ...)
ENGINE = MergeTree
...;

где:

  • <index_name> — имя индекса;

  • <index_expr> — выражение, по которому будет агрегироваться информация (часто это имя столбца);

  • <index_type> — тип индекса, определяющий, какая дополнительная информация о содержимом блока данных будет храниться в индексе (эта информация позволит быстро находить нужные блоки и пропускать все остальные);

  • <granularity_value> — гранулярность индекса, то есть количество гранул данных в пропускаемом блоке (значение по умолчанию — 1).

В существующую таблицу индекс пропуска данных можно добавить с помощью следующего запроса:

ALTER TABLE <table_name> ADD INDEX <index_name> <index_expr> TYPE <index_type> [GRANULARITY <granularity_value>];

Когда индекс добавляется в существующую таблицу, он не обновляется автоматически. ALTER TABLE изменяет метаданные, поэтому индекс будет рассчитываться только для новых данных, вставляемых в таблицу. Чтобы применить индекс к существующим данным, выполните дополнительную команду:

ALTER TABLE <table_name> MATERIALIZE INDEX <index_name>;

Удалить индексы можно запросами:

  • ALTER TABLE <table_name> DROP INDEX <index_name> — удаляет описание индекса из метаданных таблицы и удаляет индексные файлы с диска;

  • ALTER TABLE <table_name> CLEAR INDEX <index_name> — удаляет файлы индекса с диска без удаления описания индекса из метаданных.

Типы индекса

MinMax

Индекс minmax хранит минимальное и максимальное значение столбца (или выражения) для каждого блока.

Set

Индекс set(<max_rows>) хранит уникальные значения столбца (или выражения) в блоке данных в количестве, не большем чем <max_rows> (0 означает, что ограничений нет). Когда индексированный столбец используется в выражении WHERE, ADQM может cчитать небольшой набор значений вместо полного столбца. Этот тип индекса хорошо работает, если столбец имеет низкую кардинальность (небольшое количество уникальных значений) в каждом наборе гранул, но высокую кардинальность в целом.

Bloom Filter

Поддерживается три типа индекса на основе фильтра Блума:

  • bloom_filter([<false_positive>]) — фильтр Блума для столбца. Необязательный параметр <false_positive> определяет допустимую частоту получения ложноположительных ответов от фильтра. Возможны значения в диапазоне (0, 1). Значение по умолчанию: 0.025.

    Этот тип индекса поддерживается для следующих типов данных: Int*, UInt*, Float*, Enum, Date, DateTime, String, FixedString, Array, LowCardinality, Nullable, UUID и Map.

  • tokenbf_v1(<size_of_bloom_filter_in_bytes>, <number_of_hash_functions>, <random_seed>) — входная строка разбивается на токены (последовательности символов, разделенные не буквенно-цифровыми символами), а затем токены сохраняются в фильтре Блума. Подходит для поиска точного совпадения в строке (например, когда нужно найти определенную часть URL-адреса или параметр запроса в столбце URL).

    Все три параметра индекса связаны с настройкой фильтра Блума:

    • <size_of_bloom_filter_in_bytes> — размер фильтра в байтах (фильтры большего размера имеют меньше ложноположительных срабатываний);

    • <number_of_hash_functions> — количество хеш-функций, использующихся в фильтре Блума;

    • <random_seed> — состояние генератора случайных чисел для хеш-функций фильтра Блума.

    Этот тип индекса работает только c данными типа String, FixedString или Map.

  • ngrambf_v1(<n>, <size_of_bloom_filter_in_bytes>, <number_of_hash_functions>, <random_seed>) — входная строка разбивается на n-граммы (подстрока из n символов), а затем сохраняется в фильтре Блума. Подходит для полнотекстового поиска, особенно на языках без разрывов слов, таких как китайский. Первый параметр <n> задает размер n-грамм, остальные — аналогичны tokenbf_v1. Этот тип индекса работает только c типами данных String, FixedString и Map.

Inverted

Инвертированный индекс inverted([<n>], [<max_rows_per_posting_list>]) хранит сопоставление уникальных слов (или n-грамм) текстового столбца с указателями на их расположение в таблице (номера строк, в которых они содержатся). Используется для оптимизации полнотекстового поиска по столбцам типов String, FixedString, Array(String), Array(FixedString), Map(String) и Map(String). В настоящее время работает в экспериментальном режиме. Более подробную информацию и пример использования можно получить в статье Полнотекстовый поиск.

Пример

  1. Создайте тестовую таблицу с 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);

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

    SELECT * FROM table_skip_index WHERE b IN (100, 555);
    ┌──────a─┬───b─┐
│ 500000 │ 100 │
│ 500001 │ 100 │
│ 500002 │ 100 │
│    ... │ ... │
└────────┴─────┘
10000 rows in set. Elapsed: 0.159 sec. Processed 100.00 million rows, 800.12 MB (629.21 million rows/s., 5.03 GB/s.)

  3. Добавьте индекс пропуска данных:

    ALTER TABLE table_skip_index ADD INDEX my_index b TYPE set(100) GRANULARITY 2;

    На этом этапе индекс будет применяться только к новыми данным, добавляемым в таблицу. Чтобы проиндексировать уже существующие в таблице данные, выполните следующий запрос:

    ALTER TABLE table_skip_index MATERIALIZE INDEX my_index;

  4. Повторно выполните запрос на выборку данных с тем же фильтром:

    SELECT * FROM table_skip_index WHERE b IN (100, 555);

    Вместо обработки 100 миллионов строк (800 МБ) ADQM прочитает и проанализирует только 40 тысяч строк (440 КБ) — четыре гранулы по 10000 строк каждая.

    ┌──────a─┬───b─┐
│ 500000 │ 100 │
│ 500001 │ 100 │
│ 500002 │ 100 │
│    ... │ ... │
└────────┴─────┘
10000 rows in set. Elapsed: 0.028 sec. Processed 40.00 thousand rows, 440.00 KB (1.42 million rows/s., 15.57 MB/s.)

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

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

Пример

  1. Создайте таблицу с двумя столбцами типа Int64 и String:

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

  2. Вставьте в таблицу 100 миллионов строк с тестовыми данными (в каждой строке первого столбца хранится целое число из диапазона [0,1000), второго столбца — одна из пяти предопределенных строк):

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

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

    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        │
└──────┴────────┴───────────────────┘

  4. Зная диапазон значений в столбце 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;

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

    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         │
└──────┴────────────────────────┴───────────────────┘

  6. Сравните выполнение одного и того же тестового запроса для первоначальной таблицы 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)).

Пример

  1. Создайте тестовую таблицу с тремя столбцами — первый и второй столбцы сжимаются по алгоритмам LZ4 и ZSTD соответственно, третий хранит данные без сжатия:

    CREATE TABLE table_sizes (a Int32 CODEC(LZ4), b Int32 CODEC(ZSTD), c Int32 CODEC(NONE))
ENGINE = MergeTree
ORDER BY a;

  2. Вставьте 100 миллионов строк с одинаковыми значениями во всех столбцах (например, 1):

    INSERT INTO table_sizes SELECT 1, 1, 1 FROM numbers(100000000);

  3. В системной таблице 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 │
└──────┴───────────────────┴─────────────────┴─────────┘

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

    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 и оптимизировать потребление ресурсов — кеширование запросов. В этом случае запрос вычисляется один раз, результат сохраняется в кеше и используется в дальнейшем при повторном выполнении того же запроса. Если запрос не выполнился из-за ошибки или отмены пользователем, запись в кеш запросов не добавляется.

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

  1. Включите поддержку кеширования запросов в ADQM на уровне сессии:

    SET allow_experimental_query_cache = 1;

  2. Установите значение настройки 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). │
└──────────────────┴───────┴────────────────────────────────────────────────────────────────────────────────────────────────────────┘

Пример

  1. Выполните запрос 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.)

  2. Подключите возможность кеширования запросов и повторите выполнение запроса с активированной настройкой 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.

Пример

  1. Создайте тестовую таблицу с тремя столбцами и добавьте в нее 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);

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

    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.)

  3. Если перед выполнением запроса включить трассировку командой 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 чтобы сообщить о ней