SQL (язык структурированных запросов) — это простой язык, но он не так прост, когда вы работаете с наборами данных, содержащими миллионы записей.

Когда вы работаете со средними и большими таблицами, написание наиболее эффективных операторов SQL является приоритетом. Прежде чем вы начнете писать операторы SQL для любой платформы — Oracle, SQL Server, MySQL или любой другой — вот 10 наиболее распространенных ошибок программирования SQL, которых вам следует избегать.

ЗАБЫТЫЕ ПЕРВИЧНЫЕ КЛЮЧИ

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

Первичные ключи — это ваш первый шаг к реляционным базам данных. Они ссылаются на внешние ключи в реляционных таблицах. Например, если у вас есть таблица со списком клиентов, столбец «CustomerId» должен быть уникальным для каждого клиента. Это может быть ваш столбец первичного ключа. Затем ваше значение CustomerId будет помещено в таблицу «Заказ», чтобы связать две таблицы вместе. Всегда используйте первичный ключ в каждой создаваемой вами таблице, независимо от ее размера.

ПЛОХО УПРАВЛЯЕМАЯ ИЗБЫТОЧНОСТЬ ДАННЫХ

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

Например, предположим, что у вас есть таблица клиентов, содержащая адрес клиента. Поскольку адрес относится к клиенту, он находится в нужном месте. Затем вы создаете таблицу «Заказы» и добавляете адрес клиента в таблицу «Заказы». Этот тип избыточности данных плохо спроектирован.

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

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

ИЗБЕГАЙТЕ НЕ В ИЛИ В И ИСПОЛЬЗУЙТЕ ПРИСОЕДИНЯЙТЕСЬ ВМЕСТО

Операторы NOT IN и IN плохо оптимизированы. Они удобны, но обычно их можно заменить простым оператором JOIN. Ознакомьтесь с образцом запроса.

SELECT *FROM Customer
WHERE NOT IN (SELECT CustomerId FROM Order)

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

Альтернативный, более эффективный вариант выглядит следующим образом.

SELECT * FROM Customer c
LEFT JOIN Order o on c.CustomerId = o.CustomerId
WHERE o.CustomerId IS NULL

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

ЗАБЫТЫЕ ЗНАЧЕНИЯ NULL И ЗНАЧЕНИЯ ПУСТОЙ СТРОКИ

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

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

Когда вы определяете, что вы хотите использовать, убедитесь, что ваши запросы учитывают эти значения. Например, если вы включаете NULL для имени пользователя, вы должны выполнить запрос, используя фильтр NULL (НЕ NULL или IS NULL) в ваших запросах, чтобы включить или исключить эти записи.

СИМВОЛ ЗВЕЗДОЧКИ В ОПЕРАТОРАХ SELECT

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

SELECT * FROM Customer

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

Вместо приведенного выше запроса всегда определяйте каждый столбец. Следующее утверждение является примером.

SELECT CustomerId, FirstName, LastName FROM Customer

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

ЦИКЛ СО СЛИШКОМ БОЛЬШИМ КУРСОРОМ

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

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

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

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

НЕСООТВЕТСТВИЕ ДАННЫМ ПРОЦЕДУРЫ НАЗНАЧЕНИЯ НА ПОЛЕ

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

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

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

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

ЛОГИЧЕСКОЕ ИЛИ И И ОПЕРАЦИИ

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

Давайте рассмотрим инструкцию SQL, в которой сочетаются И и ИЛИ.

SELECT CustomerId
FROM Customer
WHERE FirstName = 'Jack' AND LastName = 'Destroyer' OR CustomerId > 0

Целью приведенного выше оператора является получение любого клиента с именем «Джек» и «Разрушитель», а идентификатор клиента больше нуля.

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

SELECT CustomerId
FROM Customer
WHERE (FirstName = 'Jack' OR LastName = 'Destroyer') AND CustomerId > 0

Мы изменили логику этого утверждения. Теперь первый набор скобок возвращает все записи, в которых имя клиента — Джек или Разрушитель. С помощью этого фильтра мы указываем SQL возвращать только те значения, где CustomerId больше нуля.

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

ПОДЗАПРОС ДОЛЖЕН ВЕРНУТЬ ОДНУ ЗАПИСЬ

Подзапросы — не лучший способ написания операторов SQL, но иногда их нельзя избежать. Когда вы используете подзапросы, они всегда должны возвращать одну запись, иначе ваш запрос не будет выполнен.

Давайте рассмотрим пример.

SELECT CustomerId,
(SELECT OrderId FROM Order o WHERE c.CustomerId = o.CustomerId)
FROM Customer c

В приведенном выше запросе мы получаем список идентификаторов клиентов из таблицы Customer. Обратите внимание, что идентификатор заказа мы получаем из таблицы заказов, где совпадает идентификатор клиента. Если есть только один заказ, этот запрос работает нормально. Однако, если для клиента имеется более одного заказа, подзапрос возвращает более одной записи, и запрос не выполняется. Вы можете избежать этой проблемы, используя оператор «Top 1».

Давайте изменим запрос на следующий.

SELECT CustomerId,
(SELECT Top 1 OrderId FROM Order o WHERE c.CustomerId = o.CustomerId ORDER BY OrderDate)
FROM Customer c

В приведенном выше запросе мы извлекаем только одну запись и упорядочиваем записи по дате. Этот запрос получает первый заказ, размещенный клиентом.

ПРИСОЕДИНЯЙТЕСЬ К ИНДЕКСАМ

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

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

Любые операторы JOIN, которые вы используете, должны иметь индекс для столбца. Если у вас нет индекса, попробуйте добавить его в таблицу.

ВЫВОД

Реляционные базы данных идеально подходят для большинства внутренних процедур, но вам необходимо создать правильный оператор SQL и оптимизировать таблицы и запросы для достижения максимальной производительности.

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