- Зачем мне изучать SQL, если я занимаюсь данными?
- Обработка и выполнение SQL-запросов
- 1. Получайте только нужные данные
- 2. Ограничьте свои результаты
- 3. Пишите запросы как можно проще
- 4. Скажите нет грубой силе
- Методы, основанные на процедурах и процедурных подходах к запросам
- От запроса к планам выполнения
- Временная сложность и O-большое
- Тонкая настройка 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
. Таким образом вы будете запрашивать только те данные, которые служат цели вашего запроса.
Если у вас есть коррелированные подзапросы с оператором
EXISTS
, вы должны попытаться использовать константу в инструкции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
приводит к невозможности воспользоваться индексом. Понимание производительности требует не только более структурированного, но и более глубокого подхода.
Однако, возможно, такой структурированный и глубокий подход в основном основывается на анализе планов запросов, который, как вы помните, получается при синтаксическом разборе запроса, представляется в виде «дереве разбора» и позволяет определить, какой алгоритм используется для каждой операции, а также метод улучшения производительности выполнения запроса.
Как вы уже прочитали во введении, возможно, вам понадобится перенастраивать планы выполнения, созданные оптимизатором, вручную. В таких случаях вам нужно будет снова проанализировать структуру запроса, просмотрев его план.
Чтобы получить этот план, вам нужно будет использовать инструменты, которые вам предоставляет система управления базами данных. Вот некоторые инструменты, которые уже могут иметься в вашем распоряжении:
- В некоторых пакетах есть инструменты, которые генерируют графическое представление плана запроса. Взгляните на этот пример:
- Другие могут предоставить вам текстовое описание плана запроса. Одним из примеров является оператор
EXPLAIN PLAN
в Oracle, но конкретный синтаксис команды зависит от той СУБД, с которой вы работаете. В других СУБД это может бытьEXPLAIN
(MySQL, PostgreSQL) илиEXPLAIN QUERY PLAN
(SQLite).
Заметьте: если вы работаете с PostgreSQL, вы различаете EXPLAIN
, по которой вы просто получаете описание того как планировщик намеревается выполнить запрос без его запуска, тогда как EXPLAIN ANALYZE
фактически выполняет запрос и возвращает вам сопоставительный анализ ожидаемых и фактических планов запросов. Вообще говоря, фактический план выполнения – это тот, который получается при фактическом выполнении запрос, тогда как оценочный план выполнения оценивает, как может выполняться запрос, при этом не выполняя его фактически. Конечно реальный план выполнения намного полезнее, поскольку он содержит дополнительные сведения и статистику того, что на самом деле произошло при выполнении запроса.
В оставшейся части этого раздела вы узнаете об EXPLAIN
и ANALYZE
и о том, как вы можете их использовать, чтобы узнать о планах выполнения запросов и вероятной производительности выполнения вашего запроса.
Совет. Если вы хотите больше узнать об EXPLAIN
или более подробно ознакомиться с примерами, советуем вам прочитать книгу «Разумные объяснения», написанную Гийомом Леларжем.
Временная сложность и O-большое
Теперь, когда вы кратко рассмотрели план запроса, вы можете начать разбираться глубже и понимать производительность выполнения запроса в более формальных терминах с помощью теории сложности вычислений. Эта область теоретической информатики, которая, среди прочего, занимается классификацией вычислительных задач в зависимости от их сложности. Этими вычислительными задачами могут быть как алгоритмы так и запросы.
Однако запросы, как правило, классифицироваться не по сложности, а по времени, которое требуется для их выполнения и получения некоторых результатов. Это называется временной сложностью, и для измерения такой тип сложности вы можете использовать формальную нотацию O-большое.
Записью O-большое вы выражаете время выполнения – насколько быстро он будет расти, в зависимости от подаваемой на вход величины при условии, что входная величина становится сколь угодно большой. Нотация O-большого исключает коэффициенты и термины более низкого порядка, поэтому вы можете сосредоточиться на важной части времени выполнения вашего запроса: скорости роста. Таким образом можно отбросить постоянные коэффициенты члены младшего порядка, а временная сложность будет описываться асимптотически. Это означает, что размер входных данных стремится в бесконечность.
На языке баз данных сложность определяет, сколько времени потребуется для выполнения запроса, поскольку размер таблиц баз данных и, следовательно, сами базы данных, постоянно увеличиваются.
Обратите внимание, на то, что размер базы данных не только увеличивается по мере наполнения таблиц, но и благодаря тому факту, что индексы базы данных также вырастают в размере.
Как вы уже видели, план выполнения определяет, среди прочего, какой алгоритм используется для каждой операции, что позволяет время выполнения запроса выражать как функцию размера таблицы, участвующей в плане запроса и называемой функция сложности. Другими словами, вы можете использовать нотацию O-большое и свой план выполнения для оценки сложности запросов и производительности.
В следующих подразделах вы получите общее представление о четырех типах временных сложностей и рассмотрите примеры того, как сложность запросов может варьироваться в зависимости от контекста, в котором вы его выполняете.
Подсказка: индексы являются частью излагаемого здесь материала!
Обратите внимание, однако, что существуют разные типы индексов, различные планы выполнения и различные реализации для различных баз данных, которые необходимо учитывать, так что приведенные ниже оценки временной сложности являются весьма обобщенными и могут варьироваться в зависимости от конкретных реализаций и настроек.
Подробнее см. здесь.
Резюмируя заметим, что вы также можете посмотреть следующий список подсказок для оценки производительности запросов в соответствии с их сложностью по времени выполнения и того, насколько хорошо они будут выполнять:
Тонкая настройка SQL
С учетом плана и временной сложности выполнения запроса, вы можете выполнить более тонкую настройку своего SQL-запроса. Вы можете начать с того, что уделите особе внимание следующим моментам:
- Заменить ненужное полное сканирование больших таблиц на сканирование индексовэ
- Убедиться, что применяется оптимальный порядок соединения таблиц.
- Убедиться, что индексы используются оптимально, а также в том, что
- Небольшие таблицы кэшированы и используются полностью индексированные таблицы.
Поздравляем! Вы дошли до конца этого сообщения в нашем блоге, получив небольшой обзор по теме производительности SQL-запросов. Надеюсь, вы получили больше информации о об анти-шаблонах, оптимизаторе запросов и инструментах, которые можно использовать для просмотра, оценки и интерпретации сложности плана ваших запросов. Однако это только малая часть знаний! Если вы хотите узнать больше, рекомендуем вам прочитать книгу «Системы управления базами данных», написанную Р. Рамакришнаном и Дж. Герке.
Наконец, приведу цитату от пользователя StackOver-flow
«Мой любимый анти-паттерн служит не для проверки запросов. он применяется тогда, когда:Ваш запрос включает несколько таблиц.
Вы считаете, что у вас есть оптимальная структура запроса и не утруждаетесь проверкой своих предположений.
Вы используете первый вариант правильно работающего запроса, не анализируя даже насколько он близок к оптимальному».
Начать работу с SQL вам может помочь вводный курс Data-Camp по SQL для науки о данных!
Специально для сайта ITWORLD.UZ. Новость взята с сайта NOP::Nuances of programming