Используйте более легкие таблицы
Эта рекомендация очевидна, ведь чем меньше строк в таблице, тем меньше ресурсов будет затрачено на ее чтение. Но в процессе разработки этот аспект может забыться.
Например, у нас есть таблица transactions, содержащая транзакции всех пользователей, обогащенная датой активации (то есть датой первой транзакции), вычисляемой отдельно и хранимой в таблице date_activation. Число строк transactions равно числу транзакций всех активных пользователей, а количество строк в date_activation соответствует числу самих пользователей. Именно поэтому, если нас не интересуют транзакции, а нужна только дата активации, следует обращаться к date_activation, хотя она выглядит как промежуточная таблица для расчета полноценной витрины с деньгами transactions.
Чем меньше джойнов, тем лучше
Лучше обратиться к одной таблице и потом разделить результат по разным фильтрам, чем обращаться два раза к одной с разными условиями.
Например, можно использовать такой запрос:
SELECT ga.d, regs, regs_p
FROM
( -- события регистраций обычных пользователей в данных из Google Analytics
SELECT DATE(event_timestamp) AS d,
COUNT(DISTINCT reg_user_id) regs
FROM datamart.ga4_hits
WHERE event_name = 'registration'
GROUP BY 1
) AS ga
LEFT JOIN
( -- события регистраций партнеров в данных из Google Analytics
SELECT DATE(event_timestamp) AS d,
COUNT(DISTINCT reg_user_id) regs_p
FROM datamart.ga4_hits
WHERE event_name = 'registration_partner'
GROUP BY 1
) AS ga_p ON ga_p.d = ga.d
ORDER BY 1 DESC
Но эффективнее будет работать этот:
SELECT DATE(event_timestamp) AS d,
COUNT(DISTINCT IF(event_name='registration', reg_user_id, NULL)) regs,
COUNT(DISTINCT IF(event_name='registration_partner', reg_user_id, NULL)) regs_p
FROM datamart.ga4_hits_archive
WHERE event_name LIKE 'registration%'
GROUP BY 1
ORDER BY 1 DESC
В джойне ставьте более крупную таблицу первой
Clickhouse помещает правую (вторую) таблицу при джойне в память, поэтому для экономии памяти следует в запросе сначала обращаться к таблице помельче.
Если порядок подзапросов важен (LEFT JOIN, а не просто JOIN) и получается так, что к легкому подзапросу присоединяется очень тяжелый, всё равно меняйте порядок подзапросов местами, а LEFT JOIN замените на RIGHT JOIN.
Например, мы хотим взять данные всех юзеров, у которых были транзакции. Привычный вариант запроса выглядит так:
SELECT *
FROM
(
SELECT user_id, login, date_registration, email, name, phone
FROM datamart.users -- таблица на 100 тыс. строк
) AS u
LEFT JOIN
(
SELECT user_id, price, date_paid, service_name
FROM datamart.transactions -- таблица на 10 млн строк
) AS t ON u.user_id = t.user_id
WHERE t.user_id IS NOT NULL
Однако в этом случае мы к более легкой таблице присоединяем тяжелую и только после этого фильтруем. Правильнее с точки зрения использования памяти применять такой запрос:
SELECT *
FROM
(
SELECT user_id, price, date_paid, service_name
FROM datamart.transactions -- таблица на 10 млн строк
) AS t
RIGHT JOIN
(
SELECT user_id, login, date_registration, email, name, phone
FROM datamart.users -- таблица на 100 тыс. строк
) AS u ON u.user_id = t.user_id
Clickhouse разместит в памяти легкую users, а не тяжелую transactions, и запрос выполнится гораздо быстрее.
Фильтруйте на ранних этапах
Количество строк, выдаваемых подзапросами, имеет значение, поэтому наружу следует передавать поменьше строк. А для этого нужно отсекать лишнее как можно раньше.
Это касается и отсечения дубликатов строк. Например, у нас есть таблица с архивными данными из CRM и таблица свежих, обновляемых данных. Часть строк одной таблицы дублирует строки другой. Чтобы собрать данные из обеих таблиц, можно использовать запрос, в котором уникализация производится в конечном селекте:
SELECT DISTINCT *
FROM
(
SELECT created_at, crm_id, name, contact_data, user_id
FROM datamart.crm
UNION ALL
SELECT created_at, crm_id, name, contact_data, user_id
FROM datamart.crm_archive
)
Но я рекомендую использовать более оптимальный запрос, который сразу убирает дубликаты из обеих частей юниона:
SELECT *
FROM
(
SELECT created_at, crm_id, name, contact_data, user_id
FROM datamart.crm
UNION DISTINCT
SELECT created_at, crm_id, name, contact_data, user_id
FROM datamart.crm_archive
)
В итоге во внешний подзапрос попадает уже меньшее число уникальных строк.
Проверяйте скорость выполнения запроса
Напоследок совет о том, как выяснить, какой запрос шустрее. Запустите последовательно оба сравниваемых запроса с характерным комментарием вначале вроде
-- test variant 1и-- test variant 2соответственно. Затем обратитесь к системному логу запросов, чтобы узнать, как быстро запросы выполнены и сколько ресурсов они потребовали:
SELECT event_time,
query,
formatReadableSize(memory_usage),
ProfileEvents.Values[indexOf(ProfileEvents.Names, 'UserTimeMicroseconds')] AS userCPU,
ProfileEvents.Values[indexOf(ProfileEvents.Names, 'SystemTimeMicroseconds')] AS systemCPU,
user,
*
FROM system.query_log
WHERE (event_date >= today() - 2) AND (event_time >= (now() - 3600*48))
AND query LIKE '%-- test variant%'
AND type='QueryFinish'
ORDER BY event_time DESC
LIMIT 2;
Резюме
Чтобы ускорить выполнение SQL-запросов в Clickhouse, следуйте таким рекомендациям по их оптимизации:
- Используйте более легкие таблицы всегда, когда это возможно.
- Сокращайте количество объединений типа JOIN.
- В джойнах ставьте более крупную таблицу первой.
- Применяйте фильтрацию и уникализацию как можно раньше.
- Сравнивайте скорость выполнения запросов с помощью system.query_log.
