ClickHouse: большой составной индекс в ORDER BY или пропускающие индексы по каждому полю

В ClickHouse выбор между составным индексом в ORDER BY и пропускающими индексами по каждому полю зависит от того, как именно вы планируете выполнять запросы, какие операции вам нужно ускорить, и какие данные обрабатываются в вашей таблице. Рассмотрим оба подхода и их преимущества.

1. Составной индекс в ORDER BY

Составной индекс создается при указании нескольких полей в выражении ORDER BY при создании таблицы. В ClickHouse это не традиционный индекс, а скорее способ организации данных, чтобы ускорить выполнение запросов с фильтрацией или сортировкой по этим полям.

Преимущества:

• Оптимизация запросов с сортировкой: Если ваши запросы часто включают сортировку по этим полям или фильтрацию, то составной индекс в ORDER BY позволяет ClickHouse эффективно организовать данные в хранилище. Это ускоряет выполнение запросов, так как данные уже отсортированы по нужным полям.
• Лучше для диапазонных запросов: Если запросы фильтруют данные по диапазонам нескольких полей одновременно (например, WHERE field1 = x AND field2 BETWEEN y AND z), составной индекс в ORDER BY будет очень эффективным, так как данные будут отсортированы, и ClickHouse сможет пропускать блоки данных, не подходящие под запрос.

Недостатки:

• Гибкость: Составной индекс хорош для определённых типов запросов, но не так гибок для других случаев. Например, если запрос фильтрует только одно поле из нескольких в ORDER BY, то эффективность может снизиться.
• Ресурсы: Большие составные индексы могут занимать много памяти и увеличить время вставки данных, так как данные должны быть организованы в соответствии с этим индексом.

Пример составного индекса:

В этом примере:

• Данные сортируются по полям category и created_at.
• Это полезно для запросов, которые фильтруют или сортируют по этим полям (например, WHERE category = 'x' AND created_at BETWEEN '2020-01-01' AND '2020-12-31').

2. Пропускающие индексы по каждому полю

Пропускающие индексы (например, индексы типа minmax или set) создаются на отдельных полях, и они позволяют ClickHouse пропускать блоки данных, которые не соответствуют условиям запроса.

Преимущества:

• Гибкость: Пропускающие индексы можно использовать для оптимизации разных типов запросов. Например, можно создавать отдельные индексы для каждого поля, что позволяет ускорить запросы, которые фильтруют по любому из этих полей.
• Подходит для точных совпадений или диапазонов: Пропускающие индексы типа set идеально подходят для точных совпадений по значениям в поле (например, WHERE category = 'x'), а индексы типа minmax эффективны для диапазонных запросов (например, WHERE created_at BETWEEN '2020-01-01' AND '2020-12-31').
• Менее ресурсоемко при вставке данных: Пропускающие индексы не требуют такой же сортировки данных, как составной индекс в ORDER BY, что снижает нагрузку при вставке данных.

Недостатки:

• Не так эффективно при сложных запросах с несколькими фильтрами: Если запрос использует фильтры по нескольким полям одновременно (например, WHERE field1 = x AND field2 = y), то несколько отдельных пропускающих индексов могут не быть такими эффективными, как один составной индекс, который уже организует данные по этим полям.
• Могут потребовать большего объема памяти: Пропускающие индексы для каждого поля могут занимать больше памяти, чем один составной индекс, если данных в таблице много.

Пример пропускающих индексов:

В этом примере:

• Индексы создаются по полям category и created_at отдельно.
• Это полезно для запросов, которые фильтруют только по одному из этих полей (например, WHERE category = 'x' или WHERE created_at > '2020-01-01').

Когда что выбрать?

Когда выбрать составной индекс в ORDER BY:

• Если ваши запросы часто выполняют сортировку по нескольким полям одновременно.
• Если ваши запросы фильтруют данные по нескольким полям, которые перечислены в ORDER BY, например, WHERE field1 = x AND field2 = y.
• Если вам важна оптимизация запросов с диапазонными фильтрами на этих полях (например, BETWEEN, >=, <=).

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

• Если ваши запросы фильтруют данные только по одному или нескольким полям, которые не входят в ORDER BY.
• Если вам нужно ускорить точные совпадения по полям (например, WHERE field = 'x').
• Если вам нужна гибкость для ускорения различных типов запросов с фильтрацией по разным полям.

Итог:

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