Семинар №10. Clickhouse. Диалекты SQL. Оптимизация запросов

1. Clickhouse

Введение

ClickHouse — это столбцовая система управления базами данных (СУБД) для онлайн-обработки аналитических запросов (OLAP – Online Analytical Processing).

В первой части занятия, чтобы не изобретать велосипед, мы рассмотрим основные различия между ClickHouse и уже знакомым нам PostgreSQL, чтобы понять, что из себя вообще представляет ранее не известный нам зверь, созданный Яндексом. Мы сосредоточимся на особенностях ClickHouse и его отличиях от PostgreSQL, чтобы понять, когда и почему стоит выбирать ту или иную СУБД.

Архитектура

PostgreSQL

  • Строчно-ориентированная СУБД.
  • Оптимизирована для транзакционных операций (OLTP – Online Transaction Processing), таких как вставка, обновление и удаление строк.
  • Поддерживает сложные реляционные схемы и транзакции с полным соблюдением ACID.

ClickHouse

  • Колонно-ориентированная СУБД.
  • Предназначена для высокоскоростного аналитического анализа данных (OLAP).
  • Оптимизирована для работы с большими объемами данных, что делает её более эффективной для аналитических запросов.

Производительность

PostgreSQL

  • Отлично справляется с OLTP-запросами, где необходимо быстро выполнять транзакции.
  • Поддерживает сложные запросы и индексы, но может уступать в скорости при обработке больших объемов данных для аналитики.

ClickHouse

  • Превосходит PostgreSQL при выполнении аналитических запросов, таких как агрегации и выборки из больших таблиц.
  • Обеспечивает высокую производительность за счёт колонно-ориентированного хранения данных и сжатия.

Хранение данных

PostgreSQL

  • Строчное хранение данных.
  • Подходит для операций, затрагивающих все поля строки.

ClickHouse

  • Колоночное хранение данных.
  • Обеспечивает быстрый доступ к отдельным столбцам, что уменьшает объём считываемых данных при выборках и ускоряет аналитические запросы.

Транзакционность

PostgreSQL

  • Полностью поддерживает ACID-транзакции, что делает её надёжной для приложений с высокой потребностью в консистентности данных.

ClickHouse

  • Не поддерживает полноценные ACID-транзакции.
  • Сосредоточен на скорости и масштабируемости, что может быть ограничением для приложений с высокими требованиями к транзакционной целостности.

Масштабируемость

PostgreSQL

  • Основное масштабирование — вертикальное.
  • Поддерживает некоторые возможности горизонтального масштабирования через расширения (например, Citus).

ClickHouse

  • Предназначен для горизонтального масштабирования.
  • Эффективно обрабатывает распределённые запросы и данные, что делает его подходящим для обработки больших объёмов данных в кластере.

Области применения

PostgreSQL

  • Отлично подходит для OLTP-приложений, где важны транзакции и сложные бизнес-логики.
  • Применяется в приложениях, требующих высокой консистентности данных.

ClickHouse

  • Идеален для аналитических задач, дашбордов, обработки событий и журналов.
  • Предпочтителен, когда требуется быстрая обработка больших объёмов данных и выполнение сложных аналитических запросов.

Заключение

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

2.1 Диалекты SQL

В SQL существует много различных диалектов, каждый из которых используется в разных задачах и имеет небольшие различия со всеми остальными. Наиболее яркий пример из реальной жизни – это британский, американский и австралийский английский.

Сейчас мы попытаемся выяснить, зачем нам столько различных диалектов, рассмотрим, где они используются и посмотрим на примеры запросов, которые в зависимости от диалекта могут быть записаны по-разному.

MySQL

  • Использование: веб-приложения, проекты с открытым исходным кодом.
  • Преимущества: простота, высокая скорость, гибкость.
  • Недостатки: нет встроенной поддержки расширенных функций, базовые параметры репликации.
  • Сценарий: стартап с платформой социальных сетей, масштабируемость с сотен до миллионов пользователей.

PostgreSQL (PL/pgSQL)

  • Использование: сложные корпоративные приложения, системы с расширенными типами данных.
  • Преимущества: хранимые процедуры, триггеры, пользовательские типы данных, поддержка PostGIS.
  • Недостатки: сложнее, медленнее, ресурсозатратнее (по сравнению с MySQL).
  • Сценарий: ГИС для городского планирования, управление пространственными данными.

Oracle SQL (PL/SQL)

  • Использование: крупные предприятия, финансовый сектор.
  • Преимущества: контроль транзакций, обработка ошибок, безопасность данных.
  • Недостатки: высокие затраты на лицензирование и обслуживание, слабая гибкость.
  • Сценарий: банк с высокими требованиями к безопасности и производительности.

SQL Server (T-SQL)

  • Использование: корпоративные среды, интеграция с продуктами Microsoft.
  • Преимущества: процедурное программирование, локальные переменные, функции обработки строк и дат.
  • Недостатки: дорогое лицензирование, совместимость только с Windows, низкая популярность.
  • Сценарий: медицинский институт, анализ данных пациентов, интеграция с Power BI.

IBM DB2

  • Использование: корпоративные решения, финансовый и банковский сектор.
  • Преимущества: надежная поддержка транзакций, эффективная работа с большими объемами данных, поддержка OLTP и OLAP одновременно.
  • Недостатки: высокая стоимость, сложность и непопулярность.

SQLite

  • Использование: мобильные приложения, встроенные системы.
  • Преимущества: портативность, надежность, простота.
  • Недостатки: слабо справляется с большим объемом данных, общий доступ только для чтения, нет расширенных функций.
  • Сценарий: офлайн-приложение для заметок, хранение данных на устройстве.

2.2 Примеры запросов в разных диалектах

Рассмотрим запрос, возвращающий названия лабораторных элементов, для которых были зафиксированы события в таблице labevents за последний год.

SQLite:

SELECT d_labitems.label 
FROM d_labitems 
WHERE d_labitems.itemid IN (
    SELECT labevents.itemid as itemid
    FROM labevents 
    WHERE datetime(labevents.charttime) >= datetime(current_time,'-1 year') 
    GROUP BY labevents.itemid 
 );

PostgreSQL:

SELECT d_labitems.label 
FROM d_labitems 
WHERE d_labitems.itemid IN (
    SELECT labevents.itemid
    FROM labevents 
    WHERE labevents.charttime >= NOW() - INTERVAL '1 year'
    GROUP BY labevents.itemid
);

T-SQL

SELECT d_labitems.label 
FROM d_labitems 
WHERE d_labitems.itemid IN (
    SELECT labevents.itemid
    FROM labevents 
    WHERE labevents.charttime >= DATEADD(YEAR, -1, GETDATE())
    GROUP BY labevents.itemid
);

Пример выше наглядно демонстрирует различия в работе с функциями времени в разных диалектах.

Помимо функций времени есть и другие различия: к примеру, в Clickhouse есть полезные для аналитиков функции по типу: uniq(), quantile(), median(), histogram(), topK().

В Postgres в свою очередь есть больше возможностей при работе со строками и регулярными выражениями: CONCAT(), SUBSTRING(), REPLACE(), TRIM(), REGEXP_REPLACE(), REGEXP_MATCHES().

Также различия могут встречаться в оконных функциях и в работе с массивами.

3. Оптимизация запросов

3.1. Жизненный цикл запроса, план запроса.

3.1.1. Жизненный цикл запроса.

Мы написали запрос, что происходит дальше?

  1. Создается подключение к СУБД. В СУБД отправляется запрос в виде обычного текста.
  2. Парсер проверяет корректность синтаксиса запроса и создает дерево запроса.
  3. Система переписывания запросов преобразует запрос – получаем обновленное дерево запроса; используется система правил)
  4. Планировщик / оптимизатор создает план запроса.
  5. Обработчик рекурсивно обходит план запроса и получает результирующий набор строк.

Дерево запроса — специальное внутреннее представление SQL-запросов с полным его разбором по ключевым параметрам:

  • Тип команды (SELECT, UPDATE, DELETE, INSERT);
  • Список используемых отношений;
  • Целевое отношение, в которое будет записан результат;
  • Список полей (* преобразуется в полный список всех полей);
  • Список ограничений (которые указаны в WHERE);
  • и т.д.

3.1.2. План запроса. Как читать, на что обращать внимание. Операторы EXPLAIN и ANALYZE.

Планировщик (planner) – компонент PostgreSQL, пытающийся выработать наиболее эффективный способ выполнения запроса SQL.

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

Оператор EXPLAIN:

Функции:

  • Выводит план выполнения, генерируемый планировщиком PostgreSQL для заданного оператора.
  • Показывает, как будут сканироваться таблицы, затрагиваемые оператором — просто последовательно, по индексу и т.д.
  • Показывает, какой алгоритм соединения будет выбран для объединения считанных из таблиц строк.
  • Показывает ожидаемую стоимость (в условных единицах) выполнения запроса.
  • ОТСУТСТВУЕТ в стандарте SQL.

Синтаксис:

EXPLAIN [ ( option [, ...] ) ] statement
EXPLAIN [ ANALYZE ] [ VERBOSE ] statement

where option can be one of:
ANALYZE [ boolean ]
VERBOSE [ boolean ]
COSTS [ boolean ]
BUFFERS [ boolean ]
TIMING [ boolean ]
FORMAT { TEXT | XML | JSON | YAML }

    INSERT INTO my_table ...;
    EXPLAIN SELECT * FROM my_table;
- - - -
QUERY PLAN
Seq Scan on my_table (cost=0.00..18334.00 rows=1000000 width=37)

Что это значит?

  • Данные читаются методом Sed Scan (см. следующий вопрос)
  • Данные читаются из таблицы my_table
  • cost — затраты (в некоторых условных единицах) на получение первой строки..всех строк
  • rows — приблизительное количество возвращаемых строк при выполнении операции Seq Scan (никакие строки не вычитываются, значение приблизительное)
  • width — средний размер одной строки в байтах

При повторном вызове покажет старую статистику, необходимо вызвать команду ANALYZE для ее обновления.

Оператор ANALYZE:

Функции:

  • Собирает статистическую информацию о содержимом таблиц в базе данных и сохраняет результаты в системном каталоге pg_statistic;
  • Без параметров анализирует все таблицы в текущей базе данных.
  • Если в параметрах передано имя таблицы, обрабатывает только заданную таблицу.
  • Если в параметрах передан список имен столбцов, то сбор статистики запустится только по этим столбцам.
  • ОТСУТСТВУЕТ в стандарте SQL.

Синтаксис:

ANALYZE [ VERBOSE ] [ table_name [ ( column_name [, ...] ) ] ]

ANALYZE my_table;
EXPLAIN SELECT * FROM my_table;

EXPLAIN (ANALYZE) SELECT * FROM my_table;
- - - -
QUERY PLAN
Seq Scan on foo (cost=0.00..18334.10 rows=1000010 width=37)
(actual time=0.402..97.000 rows=1000010 loops=1)

Planning time: 0.042 ms
Execution time: 138.229 ms

Теперь запрос будет исполняться реально.

  • actual time — реальное время в миллисекундах, затраченное для получения первой строки и всех строк соответственно.
  • rows — реальное количество строк, полученных при Seq Scan.
  • loops — сколько раз пришлось выполнить операцию Seq Scan.
  • Planning time — время, потраченное планировщиком на построение плана запроса.
  • Execution time — общее время выполнения запроса.

3.2. Индексы. Определение, условия использования, способы сканирования.

Индекс — специальный объект БД, хранящийся отдельно от таблиц и обеспечивающий быстрый доступ к данным. Это вспомогательные структуры: любой индекс можно удалить и восстановить заново по информации в таблице. Индексы служат также для поддержки некоторых ограничений целостности.

В PostgreSQL 9.6 встроены шесть разных видов индексов.

Свойства:

  • Все индексы — вторичные, они отделены от таблицы. Вся информация о них содержится в системном каталоге.
  • При добавлении/изменении данных, связанных с индексом, индекс каждый раз перестраивается (это замедляет выполнение запроса).
  • Внутри могут лежать разные математические структуры (B-дерево, черно-красное дерево…)
  • Индексы могут быть многоколоночными (поддержание условия на несколько полей).
  • Индексы связывают ключи и TID (tuple id - #page: #offset) — номер страницы и строки на ней.
  • Обновление полей таблицы, по которым не создавались индексы, не приводит к перестроению индексов (Heap-Only Tuples, HOT).

Оптимизация HOT: при апдейте строки, если это возможно, Postgres поставит новую копию строки сразу после старой копии строки. Также в старой копии строки проставляется специальная метка, указывающая на то, что новая копия строки находится сразу после старой. Поэтому обновлять все индексы не нужно.

Условия использования:

  • Совпадают оператор и типы аргументов.
  • Индекс валиден.
  • Важен порядок полей внутри многоколоночного индекса, чтобы накладывать условия, ожидая, что оптимизатор выберет индекс.
  • План с его использованием — оптимален (минимальная стоимость).
  • Всю информацию Postgres берет из системного каталога.

Синтаксис:

CREATE [UNIQUE] INDEX [CONCURRENTLY] [name] ON table_name [USING METHOD]...
CREATE INDEX ON my_table(column_2);
ALTER INDEX [IF EXISTS] name RENAME TO new_name
DROP INDEX [CONCURRENTLY] [IF EXISTS] name [, ...] [CASCADE|RESTRICT]

ALTER INDEX [IF EXISTS] name RENAME TO new_name
ALTER INDEX [IF EXISTS] name SET TABLESPACE tablespace_name
ALTER INDEX [IF EXISTS] name SET (storage_parameter = value [, ... ])
ALTER INDEX [IF EXISTS] name RESET (storage_parameter [, ... ])
DROP INDEX [CONCURRENTLY] [IF EXISTS] name [, ...] [CASCADE|RESTRICT]

Общая информация

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

3.3. Способы считывания данных

3.3.1. Seq Scan

Идея: Последовательное, блок за блоком, чтение данных таблицы.

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

Недостатки: Обычно выполняется гораздо медленнее индексного сканирования, так как считывает все данные таблицы.

3.3.2. Index Scan

Идея: Используется индекс для условий WHERE (селективность условия), читает таблицу при отборе строк.

Преимущества: При селективности условия время N → lnN. (В результате выполнения запроса выбирается значительно меньше строк, чем их кол-во в странице)

Недостатки: Если будем собирать индекс по всем полям, он будет весить зачастую значительно больше, чем данные в таблице. При увеличении выборки возрастают шансы, что придется возвращаться к одной и той же табличной странице несколько раз. (В таком случае оптимизатор переключается на Bitmap Scan)

3.3.3. Bitmap Index Scan

Идея: Сначала Index Scan, затем контроль выборки по таблице. В большей части работа со строками (индекс по строке, битовая карта страниц, последующий отбор)

Преимущества: Эффективно для большого количества строк.

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

  • В случае почти упорядоченных данных построение битовой карты — лишний шаг, обычное индексное сканирование будет таким же.
  • Создается битовая карта, где предполагаем, что в соответствии с собранной статистикой наши строки удовлетворяют нашему условию; в ней есть странички;
  • Если условия наложены на несколько полей таблицы, и эти поля проиндексированы, сканирование битовой карты позволяет (если оптимизатор сочтет это выгодным) использовать несколько индексов одновременно. Для каждого индекса строятся битовые карты версий строк, которые затем побитово логически умножаются (если выражения соединены условием AND), либо логически складываются (если выражения соединены условием OR).

3.3.4. Index Only Scan

Идея: Практически не обращаемся к таблице, все необходимые значения в индексе.

Преимущества: Очень быстрая операция.

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

Если индекс уже содержит все необходимые для запроса данные, то индекс называется покрывающим.

3.4. Написание оптимальных запросов.

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

Разберем несколько способов оптимизировать запросы:

  1. Удаление лишних операций
SELECT * 
FROM employees
WHERE salary + 1000 > 5000;
  1. Замена подзапросов на JOIN
SELECT name 
FROM employees 
WHERE department_id IN (
    SELECT id 
    FROM departments 
    WHERE name = 'IT'
);

преобразуем в

SELECT e.name 
FROM employees e
JOIN departments d ON e.department_id = d.id
WHERE d.name = 'IT';
  1. Добавление индексов
CREATE INDEX idx_sale_date ON sales(sale_date);

SELECT * 
FROM sales 
WHERE sale_date BETWEEN '2024-01-01' AND '2024-12-31';
  1. Не использовать OR при JOIN‘ах
SELECT DISTINCT
	PRODUCT.ProductID,
	PRODUCT.Name
FROM Production.Product PRODUCT
INNER JOIN Sales.SalesOrderDetail DETAIL
ON PRODUCT.ProductID = DETAIL.ProductID
OR PRODUCT.rowguid = DETAIL.rowguid;

  1. Хранение численных данных в виде числа, а не в виде строки

  2. Явно указываем только те столбцы, которые нам необходимы.

  3. Проставлять LIMIT.

4. SQLGlot

Для перевода SQL-запросов из одного диалекта в другой, а также для оптимизации запросов в python есть библиотека SQLGlot.

Для установки библиотеки необходимо прописать:

pip install sqlglot

Пример перевода группы запросов из одного диалекта в другие:

import sqlglot as sq

list_of_queries = ...

for query in list_of_queries:
    converted_query_clickhouse = sq.transpile(query, read="sqlite", write="clickhouse")[0]
    converted_query_postgres = sq.transpile(query, read="sqlite", write="postgres")[0]

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

Пример оптимизации запроса с помощью этой библиотеки:

import sqlglot
from sqlglot.optimizer import optimize

print(
    optimize(
        sqlglot.parse_one("""
            SELECT A OR (B OR (C AND D))
            FROM x
            WHERE Z = date '2021-01-01' + INTERVAL '1' month OR 1 = 0
        """),
        schema={"x": {"A": "INT", "B": "INT", "C": "INT", "D": "INT", "Z": "STRING"}}
    ).sql(pretty=True)
)

преобразует запрос в следующий код:

SELECT
  (
    "x"."a" <> 0 OR "x"."b" <> 0 OR "x"."c" <> 0
  )
  AND (
    "x"."a" <> 0 OR "x"."b" <> 0 OR "x"."d" <> 0
  ) AS "_col_0"
FROM "x" AS "x"
WHERE
  CAST("x"."z" AS DATE) = CAST('2021-02-01' AS DATE)

Испорлнение кода

Можно также прямо внутри python’а исполнить код sql-запроса с помощью execute:

from sqlglot.executor import execute

tables = {
    "sushi": [
        {"id": 1, "price": 1.0},
        {"id": 2, "price": 2.0},
        {"id": 3, "price": 3.0},
    ],
    "order_items": [
        {"sushi_id": 1, "order_id": 1},
        {"sushi_id": 1, "order_id": 1},
        {"sushi_id": 2, "order_id": 1},
        {"sushi_id": 3, "order_id": 2},
    ],
    "orders": [
        {"id": 1, "user_id": 1},
        {"id": 2, "user_id": 2},
    ],
}

execute(
    """
    SELECT
      o.user_id,
      SUM(s.price) AS price
    FROM orders o
    JOIN order_items i
      ON o.id = i.order_id
    JOIN sushi s
      ON i.sushi_id = s.id
    GROUP BY o.user_id
    """,
    tables=tables
)

5. Задачи

Представим, что вы дежурный аналитик в Enkoff-Банке и ваши коллеги пишут неоптимальные запросы. К вам ночью позвонили разработчики и пожаловались, что ваши процессы сожрали всю квоту и прод упал. Оптимизируйте запросы и спасите свою команду.

1. Смотрим, как часто проходили различные ивенты после 1 февраля 2022 года.

select event_name, count(*) as cnt
from (
    select * from table1
    where day_start > '2022-02-01'
)
group by event_name;

2. Смотрим, сколько заказов совершил каждый пользователь.

SELECT 
    name, 
    (
        SELECT COUNT(*) 
        FROM orders 
        WHERE user_id = users.id
    ) AS order_count

FROM users;

3. Смотрим, какие запросы поступали от пользователя с id = 228.

SELECT req_url 
FROM logs 
WHERE 1=1
    and req_url LIKE '%http://www.example.org/%'
    and user_id = 228;

4. Хотим посмотреть на самые лучшие вузы России по рейтингу

SELECT uni_name, rating FROM universities ORDER BY rating;

5. Хотим посмотреть имена сотрудников из отдела продаж с зп > 50к или олдов.

SELECT DISTINCT e.name
FROM employees e
JOIN departments d 
    ON e.department_id = d.id
WHERE 1=1 
    AND e.salary > 50000 
    AND d.department_name LIKE 'Sales%'
   OR e.hire_date < '2020-01-01';

6. Хотим вывести названия продуктов определенных категорий.

SELECT product_name 
FROM products 
WHERE 1=0 
    OR category = 'fruit' 
    OR category = 'dairy'
    OR category = 'frozen';

Источники

Интересная статья про диалекты

Документация MySQL

Документация PostgreSQL

Документация Oracle SQL

Документация SQLite

SQLGlot