20 практических советов для разработчиков использующих базы данных SQL

Kate · 2 Апр 2023

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

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

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

1. Использование EXISTS

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

SELECT *
FROM orders
WHERE EXISTS(SELECT *
FROM customers
WHERE orders.customer_id = customers.customer_id
AND customers.country = 'USA');

-- В этом примере мы выбираем все заказы, у которых есть связанный клиент из США.

2. Использование флагов типа boolean или integer

Для создания столбцов, значения которых определяют состояние объектов, рекомендуется использовать тип boolean, если ваша база данных его поддерживает, вместо хранения информации в текстовых полях. В таблицах с большим количеством записей использование таких полей может сильно замедлить работу. Если ваша база данных не поддерживает тип boolean, используйте NUMERIC(1) для хранения значений 1 или 0.

CREATE TABLE employees (
employe_id int,
name varchar(255),
is_active BIT
);
-- В этом примере мы создаем таблицу "сотрудники" с полем "is_active", которое может быть типа boolean. Значения 1 или 0 будут означать, активен ли сотрудник или нет.

3. Конверсии с UPPER, TO_CHAR и т. д. в WHERE-клаузах

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

4. Не используйте HAVING для фильтрации данных

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

SELECT *
FROM orders
WHERE order_date > '2021-01-01'

-- время выполнения запроса составило - 0,01 сек.

SELECT customer_id, COUNT(order_id) as num_orders
FROM orders
GROUP BY customer_id
HAVING COUNT(order_id) > 5

-- время выполнения запроса составило - 0,04 сек.

5. Будьте внимательны с настройкой MAXVALUE для SEQUENCES

При создании объекта типа SEQUENCE в базе данных Oracle, важно установить крайне высокое значение для свойства MAXVALUE, чтобы в дальнейшем не произошла остановка приложения из-за достижения максимального значения. Важно отметить, что установка высокого значения не приведет к ненужному выделению места, так как это является только параметром конфигурации объекта типа SEQUENCE.

Если вы не знакомы с SEQUENCE или не знаете, что это такое, то это уникальное свойство базы данных Oracle, которое не имеет поля автоинкремента для генерации последовательных номеров в таблицах. Ранее, для старых версий Oracle, приходилось создавать этот дополнительный тип объекта, пока не был реализован похожий функционал (автоинкремент) в версии 12c.

6. Влияние объектно-ориентированного подхода на проектирование баз данных

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

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

7. Преимущества использования процедур и представлений (view)

Если мы не используем процедуры и представления (view), то каждый раз, когда мы выполняем SQL-запрос, СУБД производит анализ синтаксиса запроса на правильность, проверяет существование ссылочных объектов и так далее.

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

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

8. Важность правильного выбора типов данных

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

9. Выбирайте только нужные для выборки столбцы

Это самый общеизвестный совет: избегайте использования SELECT * FROM. В запросах с JOIN часто используем большое количество таблиц. Указание только тех столбцов, которые действительно используются, является хорошей практикой, почти обязательной для нас, разработчиков. Еще одно преимущество, которое я также считаю важным, состоит в упрощении чтения SQL при обслуживании базы данных.

10. Кэширование

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

11. Типы переменных и параметров

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

12. Нормализация данных

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

13. Настройка языка, local и культуры

Идеально, если база данных, используемая в приложении, настроена в соответствии с языком/культурой, совместимыми с бизнес-правилами или контекстом системы, чтобы не приходилось выполнять явные преобразования в запросах к базе данных, что замедляет производительность. Эти настройки связаны с аспектами глобализации. В случае с Oracle это National Language Support.

14. Использование «значений в нескольких строках»

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

Например, мы хотим добавить несколько записей в таблицу "Категории товаров" с полями "Название" и "Описание". Можно сделать это, используя синтаксис значений в нескольких строках, примерно так:

INSERT INTO Categories (Name, Description)
VALUES
('Electronics', 'Electronic devices and accessories'),
('Clothing', 'Clothes and apparel'),
('Home goods', 'Furniture and household items')
Это добавит три новые записи в таблицу "Categories" с указанными значениями полей "Name" и "Description".

15. Мониторинг запросов

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

16. Не откладывайте внешние ключи на потом

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

17. Оптимальное использование таблиц журналов и историй

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

18. Добавление комментариев по желанию

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

19. Таблицы без первичных ключей

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

20. Посвящение времени моделированию базы данных

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

Инвестирование в этот этап имеет высокую окупаемость.

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