Базы данных – одна из самых важных частей корпоративных приложений. В связи с чем, очень важно уделять особое внимание программированию баз данных. Очень часто программированию баз данных не уделяется достаточно средств, предполагая, что это нечто легкое, с чем сможет справиться почти любой человек. Данное заблуждение ошибочно и способно привести, если не к краху проекта в целом, то к большим затратам на поддержку и сопровождения проекта.
Для лучшей работы приложения, использующего базы данных, необходимы специалисты, такие как администраторы баз данных и программисты баз данных. В своем приложении вы можете использовать Microsoft SQL Server, Oracle, Sybase, DB2 или любую другую СУБД. Если во время разработки приложения вы не привлекаете к работе специалистов по базам данных, то достаточно часто «узким местом» в проекте становится именно база данных. В этой статье предложены методы написания SQL-скриптов так, чтобы администраторам баз данных и разработчикам было проще разобраться в проекте и на более высоком уровне поддерживать и сопровождать базу данных.
Далее я привожу принципы и рекомендации для обеспечения лучшего качества разработки и поддержки баз данных. Это список не претендует на полноту, но будет постоянно обновляться.
- Примите соглашение об именовании объектов базы данных, стандартизируйте его в своей компании и следуйте этому соглашению. Это позволит повысить читаемость вашего кода и сделает его более понятным. Здесь можно найти пример именования объектов.
- Старайтесь приводить свои данные, по крайней мере, к 3-ей нормальной форме. Но не стоит следовать этому утверждению как догме, т.к. в некоторых случаях небольшие отступления от этого правила могут ускорить выполнение запросов.
- Старайтесь писать комментарии к нетривиальному коду в хранимых процедурах, триггерах, а так же в пакетных SQL командах, чтобы другим разработчикам было проще разобраться в вашем коде. Не стоит беспокоиться о длине комментариев, так они никак не влияют на производительность запросов.
- Не используйте SELECT *. Всегда указывайте имена требуемых полей в выражении SELECT. Например: SELECT CustomerId, CustomerName. Таким образом, вы уменьшите количество операций ввода-вывода диска, что улучшит производительность.
- Старайтесь избегать курсоров всегда когда это возможно. Всегда придерживайтесь подхода «основанного на наборах» вместо «процедурного» для доступа к данным и манипулирования ими. Достаточно часто курсоров можно избежать, заменив их оператором SELECT. Если, все же, вам не удается избежать курсора заменой оператором SELECT, используйте цикл вместо курсора. Цикл почти всегда быстрее, чем курсор. Но для замены курсора на цикл должен присутствовать столбец (первичный ключ или уникальный ключ), чтобы однозначно идентифицировать каждую строку. Здесь можно посмотреть примеры использования WHILE циклов.
- Избегайте создания временных таблиц, стараясь обрабатывать данные на столько, на сколько это возможно, поскольку создание временной таблицы влечет за собой больше операций ввода-вывода диска. Также лучше использовать дополнительные SQL выражения, просмотры (представления), переменные типа TABLE вместо временных таблиц.
- Старайтесь не использовать символ обобщения (%) в начале слова при поиске с использованием LIKE, поскольку это приводит к сканированию (пересмотру) индекса, что проигрывает индексному поиску. В следующем примере первое выражение приводит к пересмотру индекса, в то время как второе к поиску по индексу:
Также избегайте поиска с использованием операторов неравенств (<> и NOT), которые приводят к сканированию таблицы и пересмотру индекса.
SELECT CustomerId FROM Customers WHERE CustomerName LIKE '%soft'SELECT CustomerId FROM Customers WHERE CustomerName LIKE 'M%t' - При необходимости используйте «производные таблицы», т.к. они работают быстрее. Например, следующий запрос ищет вторую по величине зарплату среди сотрудников по таблице Employees:
Этот запрос может быть переписан с использованием производной таблицы, как показано ниже, в результате чего запрос выполняется в 2 раза быстрее первого:
SELECT MIN(Salary)
FROM Employess
WHERE EmpID in (
SELECT TOP 2 EmpID
FROM Employees
ORDER BY Salary DESC
)Это просто пример, и результат, полученный вами, может сильно отличаться от представленного в зависимости от свойств базы данных, индексов, объема данных и т.п.
SELECT MIN(Salary)
FROM (
SELECT TOP 2 Salary
FROM Employees
ORDER BY Salary DESC
) AS A - Во время разработки базы данных не забывайте уделять время быстродействию. Позже, во время промышленной эксплуатации базы данных, будет намного сложнее настраивать производительность базы данных, т.к. для этого скорее всего вам потребуется пересобрать таблицы и индексы, переписать запросы и т.п. Для анализирования работы запросов можно пользоваться командами SHOWPLAN_TEXT и SHOWPLAN_ALL, а также визуальным представлением плана выполнения запроса (execution plan) в Query Analyzer или SQL Management Studio. Запросы следует писать так, что бы они использовали «поиск по индексу» (Index Seek), а не «сканирование индексов» (Index Scan), и тем более избегать «сканирование таблиц» (Table Scan). Сканирование индексов или таблиц достаточно дорогостоящая операция в плане производительности, поэтому лучше их избегать. И, конечно же, используйте «правильные» индексы на «правильных» колонках.
- Используйте команду SET NOCOUNT ON в начале пакетных команд SQL, хранимых процедур и триггеров в рабочей версии скриптов, чтобы предотвратить вывод сообщений таких как (1 row(s) affected) после выполнения команд INSERT, UPDATE, DELETE и SELECT. Это позволит повысить производительность хранимых процедур за счет уменьшения сетевого трафика.
- Для наглядности и повышения читаемости кода используйте JOIN операторы, определенные стандартом ANSI SQL, вместо классического соединения таблиц. С ANSI соединениями (JOIN) команда WHERE используется только для задания условия отбора (фильтрации) данных, тогда как в классическом соединения команда WHERE используется еще и для задания условий соединения таблиц. Далее приведены два примера, первый из которых демонтирует использование классического соединения таблиц, а второй - использование ANSI JOIN для соединения таблиц.
SELECT a.au_id, t.title
FROM titles t, authors a, titleauthor ta
WHERE a.au_id = ta.au_id
AND ta.title_id = t.title_id
AND t.title LIKE '%Computer%'
SELECT a.au_id, t.title
FROM authors a
INNER JOIN titleauthor ta ON a.au_id = ta.au_id
INNER JOIN titles t ON ta.title_id = t.title_id
WHERE t.title LIKE '%Computer%' - Не используйте префикс "sp_" в именах своих хранимых процедур. Префикс sp_ зарезервирован для системных процедур, поставляемых с SQL Server. Каждый раз когда SQL Server'у необходимо выполнить хранимую процедуру, имеющую префикс sp_, первое что он делает - ищет хранимую процедуру в базе данных master, затем просматривает по всем спецификаторам (база данных, владелец), затем как владелец dbo. Таким образом можно сократить время поиска хранимой процедуры, избегая префикс sp_.
- Представления (View) обычно используются для отображения различной информации пользователям в зависимости от их требований. Представления также используются для ограничения доступа к таблицам, на основе которых построены представления, предоставляя права только на представления. И самое главное, представления позволяют вам упрощать ваши запросы. Объединяйте все необходимые данные, соединения (join) и вычисления в представления, в результате вам не придется дублировать их при написании запросов. Вместо это просто сделайте SELECT из вашего представления.
- Используйте пользовательские типы данных (User Define Datatypes), если специфические поля повторяется во многих ваших таблицах. Таким образом вы получите непротиворечивые поля, гарантировано имеющие одинаковые типы данных.
- Не предоставляйте возможности вашим приложениям выбирать\манипулировать данными, используя SELECT или выражения INSERT\UPDATE\DELETE. Вместо этого создайте хранимые процедуры и предоставьте приложениям к ним доступ. В результате чего вы получите "чистый" и последовательный доступ к данным из любых модулей ваших приложений, что позволит в тоже время заключить бизнес-логику приложений в базе данных.
- Старайтесь не использовать типы данных TEXT и NTEXT для хранения текста больших объемов. Существует ряд проблем связанных с использованием типа данных TEXT. Например, вы не можете напрямую записывать или обновлять текстовые данные используя выражения INSERT или UPDATE. Вместо этого вы будете вынуждены использовать специальные выражения READTEXT, WRITETEXT и UPDATETEXT. Также существует множество ошибок связанных с репликацией таблиц, содержащих текстовые поля. Таким образом, если нет необходимости хранить текст большего размера, чем 8KB, лучше использовать CHAR(8000) или VARCHAR(8000).
- Если есть возможность, то старайтесь не хранить бинарные файлы и файлы образов (Binary Lange Object или BLOB'ы) в базе данных. Вместо этого храните путь к бинарному файлу в базе данных и используйте его как указатель на сам файл, который может храниться где угодно на сервере. В результате чего, вы получите более высокую производительность, работая с большими бинарными файлами вне базы данных. И в добавок ко всему, база данных не предназначена для хранения файлов.
