Что делать с запросами к СУБД, выполнение которых затягивается на десятки минут, как можно оптимизировать вложенные операторы, чтобы получить нужные данные за секунды? За счет чего подобные операции выполняются в Visiology автоматически? Ответы на эти вопросы мы попробуем дать сегодня на примере небольшого синтетического теста со сложным SQL-запросом, и разберемся при чем тут комбинаторы в ClickHouse. Эта статья будет полезна тем, кто интересуется SQL-оптимизаторами, а также всем существующим и будущим пользователям Visiology, кто хочет заглянуть под капот системы. Если вы из их числа, добро пожаловать под кат :)
По мере того, как количество данных на BI-платформе растет, становится очевидно, что для комфортной работы важны не только возможности системы, но и ее производительность. В Visiology есть возможность обрабатывать запросы, затрагивающие сотни миллионов строк, благодаря тому, что в Visiology используется собственный движок ДанКо. Тут помогают синтаксис DAX и высокопроизводительная колоночная СУБД ClickHouse. Чтобы платформа работала быстро, в состав движка ДанКо входят SQL-оптимизаторы, которые значительно повышают эффективность выполнения запросов.
Важной частью DAX-движка ДанКо в Visiology являются SQL оптимизаторы, которые помогают производить преобразования SQL с учетом используемой модели данных.
Комбинаторы ClickHouse дают дополнительные преимущества для оптимизаторов SQL ДанКо, которых нет в других реляционных базах данных.
Рассмотрим пример работы одного из SQL оптимизаторов ДанКо, который использует комбинаторы ClickHouse и информацию о кардинальности (в смысле баз данных, т.е., по сути, доля уникальных значений в столбцах таблиц) на тестовых данных:
Таблица
salesсо 100 миллионами записей с полями уникальный номер заказаorder_number, количествоamount, дата заказаorder_dateи тип продажиsales_type(число с тремя значениями: 0, 1 и 2).Таблица календаря
datesс 10 тысячами записей с полем датаdate, месяцmonthи годyear.
Будет использован следующий SQL для генерации таблиц в ClickHouse:
CREATE OR REPLACE TABLE sales ( order_number Int64, amount Float64, order_date Date, sales_type Int64 ) ENGINE = Log; INSERT INTO sales SELECT toString(1000000000 + number) AS order_number, number % 100 AS amount, dateAdd(number % 700, toDate('2023-01-01')), number % 3 AS sales_type FROM system.numbers LIMIT 100000000;
CREATE OR REPLACE TABLE dates(date Date, month Int64, year Int64) ENGINE = Log; INSERT INTO DATES SELECT dateAdd(number, toDate('2023-01-01')) AS date, toMonth(dateAdd(number, toDate('2023-01-01'))) AS month, toYear(dateAdd(number, toDate('2023-01-01'))) AS year FROM system.numbers LIMIT 10000;
Наш пример может показаться простым, но, тем не менее, он интересен даже просто с точки зрения работы реляционных баз данных. Это будет синтетический запрос с проблемами производительности (ведь на запросе без проблем производительности ничего и не видно), но на его примере можно проиллюстрировать возможности SQL оптимизаторов. Видно, что до оптимизации в этом запросе есть не то, что один CROSS JOIN, а даже вложенные друг в друга CROSS JOIN в CROSS JOIN:
-- до оптимизации - выполняется 12 секунд SELECT expression_result.grouped_sales_type AS grouped_sales_type, expression_result.grouped_date AS grouped_date, if(if(expression_result.grouped_date <= max(if(toLastDayOfMonth(expression_result.grouped_date) = expression_result.date, toLastDayOfMonth(addYears(expression_result.date, -1)), addYears(expression_result.date, -1))), true, false) AND if(expression_result.grouped_date >= min(if(toLastDayOfMonth(expression_result.date) = expression_result.date, toLastDayOfMonth(addYears(expression_result.date, -1)), addYears(expression_result.date, -1))), true, false), true, false) AS condition FROM (SELECT grid.grouped_sales_type AS grouped_sales_type, grid.date AS grouped_date, dates.date AS date FROM dates AS dates CROSS JOIN (SELECT grid.grouped_sales_type AS grouped_sales_type, dates.date AS date FROM (SELECT sales.sales_type AS grouped_sales_type FROM sales AS sales GROUP BY sales.sales_type) AS grid CROSS JOIN dates AS dates) AS grid ) AS expression_result GROUP BY expression_result.grouped_sales_type, expression_result.grouped_date;
Для такого вида запроса можно дать следующее словесное описание:
Проверка попадания даты в период и особая логика для последнего дня месяца
Да, глубокого смысла в запросе нет, он полезен с точки зрения иллюстрации возможностей оптимизации. Ведь это, казалось бы, совершенно безнадежный запрос — 2 вложенных друг в друга CROSS JOIN. Такого вида SQL при увеличении количества записей в таблице календаря dates, например, до 30 тысяч, может выполняться минуты. Но ClickHouse дает в чем-то уникальные возможности оптимизации за счет комбинаторов. В итоге можно сравнительно легко оптимизировать запрос с таким уровнем вложенности.
Итак, после оптимизации методами ДанКо, основанными на кардинальностях и комбинаторах ClickHouse, мы получаем:
-- после оптимизации - выполняется меньше секунды SELECT expression_result.grouped_sales_type AS grouped_sales_type, expression_result.grouped_date AS grouped_date, if(if(expression_result.grouped_date <= maxMerge(complex_date_max_state), true, false) AND if(expression_result.grouped_date >= minMerge(complex_date_min_state), true, false), true, false) AS condition FROM (SELECT grid.grouped_sales_type AS grouped_sales_type, grid.date AS grouped_date, maxStateMerge(complex_max_state) AS complex_date_max_state, minStateMerge(complex_min_state) AS complex_date_min_state FROM (SELECT maxState(if(toLastDayOfMonth(dates.date) = dates.date, toLastDayOfMonth(addYears(dates.date, -1)), addYears(dates.date, -1))) AS complex_max_state, minState(if(toLastDayOfMonth(dates.date) = dates.date, toLastDayOfMonth(addYears(dates.date, -1)), addYears(dates.date, -1))) AS complex_min_state FROM dates AS dates) AS dates CROSS JOIN (SELECT grid.grouped_sales_type AS grouped_sales_type, dates.date AS date FROM (SELECT sales.sales_type AS grouped_sales_type FROM sales AS sales GROUP BY sales.sales_type) AS grid CROSS JOIN dates AS dates) AS grid GROUP BY grid.grouped_sales_type AS grouped_sales_type, grid.date AS grouped_date ) AS expression_result GROUP BY expression_result.grouped_sales_type, expression_result.grouped_date;
Видно, что оптимизация происходит «на 3 уровня вниз» с использованием maxMerge/maxStateMerge/maxState (и аналогично mixMerge/minStateMerge/minState), позволяет успешно выполнить менее чем за секунду достаточно «безнадежный» с точки зрения производительности синтетический запрос с двумя вложенными CROSS JOIN для таблицы продаж с 100 млн записей и календарем с 10 тысячами записей, причем, обратите внимание, вложенные CROSS JOIN остаются на месте! Это достигается за счет комбинаторов ClickHouse.
Интересно, что оптимизация производится с учетом кардинальностей, а sales_type у нас — низкокардинальный (т.е. содержит небольшое количество уникальных значений — всего 3 разных значения). Поэтому GROUP BY по sales_type оправдан и приносит улучшение производительности.
Если в запросе вместо низкокардинального sales_type (с тремя разными значениями) будет использоваться уникальный (и высококардинальный) order_number, то GROUP BY order_number не будет иметь смысла с точки зрения производительности, соответственно, такой в таком случае ДанКо и не будет делать такую оптимизацию из соображений кардинальности.
Заключение
Конечно, такого вида пример — лишь показательный образец применения комбинаторов на синтетическом SQL, и я здесь привел его только для демонстрации возможностей оптимизаторов ДанКо, которые используют особенности ClickHouse. Но подобная задача может быть актуальна при автоматическом построении запросов самых разных типов.
Желаю успехов в BI и построении дашбордов!
