Оптимизация

Когда мы говорим об "эффективности запроса" мы подразумеваем то, насколько эффективно запрос расходует ресурсы системы. В первую очередь мы обращаем внимание на:

  • нагрузку на жесткий диск сервера, где хранятся таблицы.

  • нагрузку на оперативную память сервера, где запросы обрабатываются.

  • нагрузку на оперативную память клиента (т.е. нашего компьютера), где результат запроса отображается

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

Но сначала обсудим то, как мы будем разбираться в эффективности запроса:

Как проверить эффективность запроса

system.query_log

Для оценки эффективности запроса можно использовать системную таблицу system.query_log.

При подключении к лерими нам она недоступна, но доступна вью к ней — my.query_log. Вот пример запроса, где мы увидим все запросы к таблице crs_available_resource_to_procedure:

SELECT event_time, query, read_rows, read_bytes, result_rows, result_bytes, memory_usage
FROM my.query_log
WHERE query ilike '%crs_available_resource_to_procedure%'
	and query not ilike '%from my%'
	and type = 2
ORDER BY event_time DESC

  • event_time — время выполнения запроса

  • query — текст запроса

  • read_rows — общее количество строк, считанных из всех таблиц и табличных функций, участвующих в запросе.

  • read_bytes — общее количество байтов, считанных из всех таблиц и табличных функций, участвующих в запросе.

  • result_rows — количество строк в результате запроса SELECT или количество строк в запросе INSERT.

  • result_bytes — объём RAM в байтах, использованный для хранения результата запроса.

  • memory_usage — потребление RAM запросом.

EXPLAIN

Это оператор, показывающий план выполнения запроса. Благодаря нему можно узнать, как запрос будет выполняться СУБД.

  • Может использоваться без параметров. Такой запрос показывает план запроса:

EXPLAIN SELECT toYYYYMM(signed_dt), count() as cnt
FROM data_analyzer.diagnosis_dst_meta
GROUP BY toYYYYMM(signed_dt)
HAVING toYYYYMM(signed_dt) IN (202501, 202502, 202503)

  • Может использоваться с SYNTAX — отображает запрос после выполнения оптимизации кликхаусом:

EXPLAIN SYNTAX SELECT toYYYYMM(signed_dt), count() as cnt
FROM data_analyzer.diagnosis_dst_meta
GROUP BY toYYYYMM(signed_dt)
HAVING toYYYYMM(signed_dt) IN (202501, 202502, 202503)

  • Можно вывести подробный план запроса в JSON. Попробуйте и изучите результат:

EXPLAIN PLAN json = 1, indexes = 1, description = 1
SELECT toYYYYMM(signed_dt), count() as cnt
FROM data_analyzer.diagnosis_dst_meta
GROUP BY toYYYYMM(signed_dt)
HAVING toYYYYMM(signed_dt) IN (202501, 202502, 202503)

DDL таблицы

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

DDL (Data Definition Language) — запрос, который эту таблицу создаёт. Нам полезно знать типы данных, ключ партиционирования, ключи сортировки, гранулярность индекса, настройки.

Мы можем узнать DDL через интерфейс:

DDL таблицы в DBeaver

Или при помощи запроса:

SHOW CREATE data_analyzer.diagnosis_dst_meta

Обратим внимание на последние строки:

PARTITION BY toYYYYMM(signed_dt) -- ключ партиционирования
PRIMARY KEY (document_class_id, -- индекс
 document_id)
ORDER BY (document_class_id, -- ключи сортировки
 document_id,
 xpath,
 line_id)
SETTINGS index_granularity = 8192; -- настройки гранулярности индекса

Продолжение

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

PreviousПод капотомNextSELECT

Last updated