Руководство по SQL: Как лучше писать запросы

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

Вот почему в этом руководстве по SQL предоставлен пошаговый обзор, которые позволит вам оценить качество вашего запроса:

• Во-первых, мы начнем с краткого обзора важности изучения SQL для работы в области науки о данных.
• Затем вы сначала узнаете о том, как обрабатывать и выполнять SQL-запросы, чтобы вы правильно поняли важность написания качественных запросов: в частности, вы увидите, как происходит грамматический разбор запроса, как он переписывается, оптимизируется и, наконец, оценивается.
• В связи с этим, вы не только рассмотрите некоторые антишаблоны запросов, которые новички обычно применяют при написании запросов, но и больше узнаете об альтернативных решениях, позволяющих избегать подобных ошибок. Вы узнаете больше о запросах, основанных на записях и процедурных подходах.
• Вы также увидите, как эти анти-шаблоны связаны с проблемами производительности и что, помимо «ручного» подхода к улучшению SQL-запросов, вы можете анализировать свои запросы также более структурированным, углубленным методом с помощью некоторых инструментов, которые помогут вам рассмотреть план запроса, а также,
• Мы рассмотрим вопросы времени выполнения запросов и нотации O-большое, чтобы получить более подробное представление об оценке временной сложности плана выполнения запроса, и, наконец,
• Вы получите несколько указаний на то, как можно организовать очередь запросов.

Вам требуется пройти курс по SQL? Рассмотрите в качестве варианты курс на DataCamp под названием «Введение в SQL и науку о данных»!

Зачем мне изучать SQL, если я занимаюсь данными?

SQL весьма далек от забвения – напротив, это один из самых востребованных навыков, который вы можете найти в описаниях вакансий в области обработки больших данных, независимо от того, хотите ли вы устроиться на должность аналитика данных, инженера по данным, научного сотрудника в области данных или в качестве еще кого-либо. Этот факт подтверждается результатами исследования рынка труда, проведенным O’Reilly в 2016 году: 70% респондентов, участвовавших в опросе, подтвердили, что в своей профессиональной деятельности они используют SQL. Более того, в обзоре результатов этого исследования язык SQL занимает более высокую позицию, по сравнению с другими языками программирования, такими как R (57%) и Python (54%).

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

Неплохо для языка, который был разработан еще в начале 1970-х годов прошлого века, не правда ли?

Но почему так часто используется именно этот язык? И почему он до сих пор не мертв, как многие другие языки того же поколения?

Для объяснения этого факта можно найти несколько причин: во-первых, компании в основном хранят данные в реляционных системах управления базами данных (RDBMS) или в системах управления реляционными потоками данных (RDSMS), и SQL требуется для доступа к таким хранимым данным. SQL – это универсальный язык данных: он дает вам возможность взаимодействовать практически с любой базой данных или даже создавать свои локальные базы данных!

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

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

Если хотите, можем привести такую аналогию с линейной алгеброй: сосредоточив все усилия только на этой одной области математики, вы сможете использовать полученные знания и как хорошую основу для овладения машинным обучением!

Короче говоря, вот причины, по которым вам следует изучить язык структурированных запросов:

• Он довольно прост в изучении, даже для новичков. Рост знаний и навыков происходит довольно быстро, и вы в кратчайшие сроки научитесь писать запросы.
• Изучение SQL подчиняется принципу «однажды изученное может применяться повсюду», поэтому это отличное вложение вашего времени и сил!
• Это отличное дополнение к языкам программирования. В некоторых случаях писать запрос даже предпочтительнее, чем писать код, потому что он более эффективен!
• …

И чего же ты все еще ждешь? 🙂

Обработка и выполнение SQL-запросов

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

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

Затем в дело вступает оптимизатор – его задача найти оптимальное выполнение или построить оптимальный план данного запроса. План выполнения точно определяет, какой алгоритм используется для каждой операции, и как координируется выполнение операций.

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

Теперь вы, вероятно, задаетесь вопросом, что считается «хорошим планом запроса».

Как вы уже прочитали выше, критерий стоимости плана играет огромную роль. А именно, для оценки плана необходимы такие вещи, как количество дисковых операций ввода-вывода, стоимость процессора и общее время отклика, которое может наблюдаться для клиента базы данных, а также общее время выполнения. Именно здесь появляется понятие временной сложности. Но об этом вы узнаете чуть позже.

Затем выполняется выбранный план запроса, они оцениваются механизмом выполнения системы и после этого возвращаются результаты вашего запроса.

Таким образом эту последовательность можно записать в виде следующего списка шагов (см. картинку с английской терминологией ниже):

• SQL-выражение
• Синтаксической разбор
• Компоновка
• Оптимизация запроса
• Выполнение запроса
• Результаты запроса
  

  

Из предыдущего раздела может быть уже понятно, что принцип обработки «что на входе, то и на выходе» (Garbage In, Garbage Out (GIGO)) естественным образом распространяется на обработку и выполнение запроса: тот, кто формулирует запрос, также держит в руках и ключи от производительности SQL-запроса. Если оптимизатор получает плохо сформулированный запрос, он может только сделать так …

Это означает, что есть некоторые вещи, которые вы можете сделать, когда пишете запрос. Как мы уже во введении, ваша ответственность за качество написания запроса складывается из двух составляющих: в написании запросов, соответствующих определенному стандарту, и понимании того, где могут возникать проблемы при исполнении вашего запроса.

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

• Оператор  WHERE;
• Ключевые слова  INNER JOIN  или LEFT JOIN,
• Оператор  HAVING.

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

Тем не менее, вы также должны понимать, что производительность – это то, что понимается в определенном контексте: просто так сказать, что эти причины и ключевые слова плохи, не есть способ понимания производительности SQL запроса. То есть наличие предложения WHERE или HAVING в вашем запросе не обязательно означает, что это плохой запрос …

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

1. Получайте только нужные данные

Идеология «чем больше данных, тем лучше» ‑ это не то, что вам нужно для написания SQL-запросов: вы рискуете не только затуманить свои идеи, получая данных на много больше, чем вам нужно, но также производительность вашего запроса может пострадать от того, что он будет выбираться слишком много данных.

Вот почему обычно рекомендуется заботиться об инструкции SELECT, операторах DISTINCT и LIKE.

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

Помните, что коррелированный подзапрос – это подзапрос, который использует значения из внешнего запроса. И обратите внимание, что, хотя NULL и может здесь использоваться в качестве «константы», это выглядит очень запутанно для понимания вашего запроса другими разработчиками!

Рассмотрим следующий пример, чтобы понять, что мы подразумеваем под использованием константы:

SELECT driverslicensenr, name 
FROM Drivers 
WHERE EXISTS (SELECT '1' FROM Fines 
              WHERE fines.driverslicensenr = drivers.driverslicensenr);

Совет: полезно знать, что наличие коррелированного подзапроса не является хорошей идеей. Вы всегда можете отказаться от него, например, переписав запрос через INNER JOIN:

SELECT driverslicensenr, name 
FROM drivers 
INNER JOIN fines ON fines.driverslicensenr = drivers.driverslicensenr;

Оператор SELECT DISTINCT используется для возврата только различных значений. DISTINCT – это условие, которого, при возможности, лучше всего избегать. Как вы можете видеть и на других примерах, время выполнения увеличивается только в том случае, если вы добавили это предложение в свой запрос. Поэтому всегда стоит подумать над тем, действительно ли вам нужна операция DISTINCT, чтобы получить нужный вам результат.

Когда вы используете оператор LIKE в запросе, индекс не используется, если шаблон начинается с % или  _. Эти шаблоны запрещают использование индексов базы данных (если он имеются). Ну и конечно, с другой стороны, этот тип запроса потенциально оставляет открытой возможность для извлечения слишком большого количества записей, которые не обязательно могут удовлетворять цели вашего запроса.

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

2. Ограничьте свои результаты

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

Вы можете добавить оператор LIMIT или TOP к своим запросам, чтобы установить максимальное число выбираемых в результате строк. Вот некоторые примеры:

SELECT TOP 3 * FROM Drivers;

Обратите внимание, что вы можете дополнительно указать PERCENT, например, если вы измените первую строку запроса с помощью SELECT TOP 50 PERCENT *.

SELECT driverslicensenr, name FROM Drivers LIMIT 2;

Кроме того, вы также можете добавить оператор ROWNUM, что эквивалентно использованию LIMIT в запросе:

SELECT * 
FROM Drivers 
WHERE driverslicensenr = 123456 AND ROWNUM <= 3;

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

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

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

3. Пишите запросы как можно проще

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

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

Когда вы используете оператор OR в запросе, вероятно, вы не можете воспользоваться индексом.

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

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

Рассмотрим следующий запрос:

SELECT driverslicensenr, name 
FROM Drivers 
WHERE driverslicensenr = 123456 OR driverslicensenr = 678910 OR driverslicensenr = 345678;

Вы можете заменить оператор на:

SELECT driverslicensenr, name 
FROM Drivers 
WHERE driverslicensenr IN (123456, 678910, 345678);

Два оператора SELECTс  UNION.

Совет. Здесь вам нужно быть осторожным, и излишне не прибегать к использованию операции объединения UNION, потому что в этом случае вы проходите одну и ту же таблицу несколько раз. С другой стороны, вы должны понимать, что при использовании UNION в запросе время выполнения увеличивается. Альтернативой операции UNION является переформулировка запроса таким образом, чтобы все условия были помещены в одну инструкцию SELECT или с использованием OUTER JOIN вместо UNION.

Совет. Помните также, что, хотя OR и другие операторы, которые будут упомянуты в следующих разделах, вероятно, не используют индекс, индексные запросы не всегда предпочтительны!

Когда ваш запрос содержит оператор NOT, вероятно, индекс не используется, как и для оператора OR. А это неизбежно замедлит выполнение вашего запроса. Если вы не понимаете, что мы подразумеваем, рассмотрите следующий запрос:

SELECT driverslicensenr, name FROM Drivers WHERE NOT (year > 1980);

Этот запрос определенно будет работать медленнее, чем вы могли бы ожидать, главным образом потому, что он сформулирован намного сложнее, чем это могло бы быть: в таких случаях, как этот, лучше искать альтернативу. Рассмотрите возможность замены NOT операторами сравнения, такими как  >, <> или !>. Этот пример запроса действительно может быть переписан примерно в таком виде:

SELECT driverslicensenr, name FROM Drivers WHERE year <= 1980;

Это уже выглядит аккуратно, не так ли?

Оператор AND – это другой оператор, который не использует индекс и тоже может замедлить выполнение вашего запроса, особенно если он используется слишком сложным и неэффективным способом, как в примере ниже:

SELECT driverslicensenr, name 
FROM Drivers 
WHERE year >= 1960 AND year <= 1980;

Лучше переписать этот запрос и использовать оператор BETWEEN:

SELECT driverslicensenr, name 
FROM Drivers 
WHERE year BETWEEN 1960 AND 1980;

Кроме того, операторы ALL и ALL  ‑ это такие операторы, с которыми нужно обращаться очень осторожно, потому что, включение их приводит к отказу от использования индекса. Альтернативными вариантами, которые могут здесь пригодится, являются функции агрегации, такие как  MIN или MAX.

Совет. В тех случаях, когда вы используете предлагаемые альтернативы, вы должны знать, что все функции агрегации, такие как SUM, AVG, MIN, MAX во многих строках, могут привести к повышению времени выполнения запроса. В этих случаях вы можете попытаться либо свести к минимуму количество строк для обработки или предварительного расчета этих значений. Здесь вы снова убеждаетесь в том, как важно знать, как можно больше о структуре своих данных, о цели запроса … когда вы принимаете решения о том, какой запрос использовать!

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

SELECT driverslicensenr, name 
FROM Drivers 
WHERE year + 10 = 1980;

Это выглядит причудливо, да? Попробуйте вместо этого пересмотреть расчет и переписать запрос примерно так:

SELECT driverslicensenr, name 
FROM Drivers 
WHERE year = 1970;

4. Скажите нет грубой силе

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

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

• Избыточные условия для объединений

Когда вы добавляете слишком много условий для объединений, вы, по сути, предписываете SQL выбрать определенный путь. Может быть, однако, что этот путь не всегда более эффективен.

Предложение HAVING было первоначально добавлено в SQL, потому что ключевое слово WHERE не могло использоваться с агрегатными функциями.  HAVING обычно используется с предложением GROUP BY , чтобы ограничить группы возвращаемых строк только теми, которые соответствуют определенным условиям. Однако, если вы используете это предложение в своем запросе, индекс не используется, который, как вы уже знаете, что может привести к запросу, который будет не реально выполнить.

Если вы ищете альтернативу, подумайте об использовании предложения  WHERE. Рассмотрим следующие запросы:

SELECT state, COUNT(*) FROM Drivers WHERE state IN ('GA', 'TX') GROUP BY state ORDER BY state
SELECT state, COUNT(*) FROM Drivers GROUP BY state HAVING state IN ('GA', 'TX') ORDER BY state

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

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

Обратите внимание, что разница между этими двумя предложениями заключается в том, что оператор WHERE вводит условие для отдельных строк, тогда как оператор HAVING вводит условие агрегирования или повторных выборов, в которых один результат, такой как MIN, MAX, SUM, … был создан из нескольких строк.

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

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

Методы, основанные на процедурах и процедурных подходах к запросам

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

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

Примером такого подхода является избыточные условия в соединениях или случаях, когда вы злоупотребляете предложением  HAVING, как в приведенных выше примерах, когда вы запрашиваете базу данных, выполняя функцию, а затем вызываете другую функцию или используете логику, содержащую цикл, условия, пользовательские функции (UDF), курсоры, … для получения окончательного результата. При таком подходе вы часто будете спрашивать подмножество данных, затем запрашивать другое подмножество из данных и так далее.

Неудивительно, что этот подход часто называют «поэтапным» или запросом «по очереди».

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

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

Совет. Подход, основанный на наборе данных, требуется большинству ведущих работодателей! Поэтому вам потребуется часто переключаться между этими двумя подходами.

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

От запроса к планам выполнения

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

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

Однако, возможно, такой структурированный и глубокий подход в основном основывается на анализе планов запросов, который, как вы помните, получается при синтаксическом разборе запроса, представляется в виде «дереве разбора» и позволяет определить, какой алгоритм используется для каждой операции, а также метод улучшения производительности выполнения запроса.

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

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

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