Делаем «крайне необходимый» шаг к оптимизации времени выполнения запросов

По моему опыту, после Python язык структурированных запросов (SQL) является наиболее востребованным инструментом, который многие специалисты по данным используют в своих проектах по машинному обучению и науке о данных. Восхитительно играть с базами данных и видеть, как они взаимодействуют друг с другом с помощью различных механизмов, определенных в SQL, одним из которых, конечно же, является SQL JOINS!

Если вы не знакомы с соединениями, я настоятельно рекомендую этот пост, чтобы понять их, прежде чем двигаться дальше. Сказав это, давайте перейдем к тому, почему я привел вас сюда и, конечно же, почему я сказал, что вы должны немедленно прекратить использовать условное ИЛИ именно в SQL Joins.

Эксперимент 1:

Рассмотрим следующий запрос, который принимает две таблицы, а именно Table1 и Table2, и выполняет внутреннее соединение между двумя таблицами при условии, что значение в "col3" ИЛИ что в "col2" равны.

На первый взгляд, я уверен, вы подумаете, что не так с этим запросом, верно? Но когда я реализовал аналогичный запрос с условным «ИЛИ» с внутренним соединением для таблиц, каждая из которых содержала около десяти тысяч (10⁴) записей, запрос вел себя ненормально. Выполнение заняло почти 3 часа и 55 минут, что побудило меня решить проблему, связанную с этим. Я провел аналогичный эксперимент, используя условное «И» вместо условного «ИЛИ», и он был выполнен в течение нескольких минут.

Эксперимент 2:

Быстрый поиск в StackOverflow привел меня здесь, в котором говорилось, что мы можем преобразовать условное ИЛИ в операцию ОБЪЕДИНЕНИЕ, как показано ниже:

По сути, мы можем разделить условие соединения на несколько запросов и объединить результаты с помощью метода SQL UNION. Вы можете прочитать об UNION в SQL здесь.

К моему удивлению, этот запрос был выполнен всего менее 2 минут на тех же таблицах, которые я использовал в эксперименте 1.

Чтобы устранить случайность и гарантировать, что недоступность ресурсов не повлияет на время выполнения, я трижды повторил описанные выше эксперименты. Гистограмма ниже показывает среднее время выполнения эксперимента 1 и эксперимента 2 в этих трех испытаниях.

Приведенные выше наблюдения действительно вызвали множество вопросов о том, как планировщик запросов SQL оптимизирует SQL-запросы. Чтобы узнать, почему возникают некоторые проблемы с условным «ИЛИ», давайте повторим приведенные ниже вопросы.

Вопрос 1: Как именно выполняются соединения в SQL?

Здесь было бы полезно, если бы вы сначала поняли, что объединение двух таблиц с использованием наивного подхода путем выполнения вложенного поиска невозможно, поскольку мы обычно имеем дело с большими объемами данных. Например, обычный вложенный поиск с миллионом строк (10⁶) в каждой таблице будет эквивалентен триллиону операций цикла (10¹²).

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

Вопрос 2. Указывает ли время выполнения запроса, близкое к четырем часам, в эксперименте 1, что было выбрано вложенное соединение?

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

Вопрос 3. Если объединения обычно оптимизируются в SQL, почему в первом эксперименте SQL по-прежнему использует вложенную операцию?

Проблема конкретно связана с типом условия соединения, используемого в эксперименте 1, т. е.

condition1 OR condition2

Мы можем сделать вывод из наблюдаемого времени выполнения, что указанное выше условие соединения НЕ оптимизируется для HASH JOIN. Другими словами, SQL недостаточно умен, чтобы оптимизировать условие соединения или понять, что запрос по существу эквивалентен «ОБЪЕДИНЕНИЮ» нескольких запросов — если только не указано явно, как в эксперименте 2. Вот почему потребовалось невероятное количество операций. время выполнять.

Вопрос 4. Почему запрос в Эксперименте 2 выполняется быстрее?

Как сказано выше, SQL неспособен распознать, что запрос в Эксперименте 1 может быть эквивалентен «ОБЪЕДИНЕНИЮ» двух запросов. Это делает исходный запрос неоптимизируемым, и у нас нет другого выбора, кроме как явно встроить эту информацию в запрос и разделить ее вручную, чтобы помочь модулям оптимизации SQL.

Это подводит нас к концу этого поста. В заключение отметим, что, насколько мне известно, эти ограничения существуют именно в соединениях SQL. Вы по-прежнему можете использовать условное «ИЛИ» в других местах, таких как «ГДЕ», «ИМЕЕТ» и т. д.

Большое спасибо за чтение, и я надеюсь, вам понравилось.

Ваши мысли и отзывы высоко ценятся.