Рекомендации по работе с базами данных29.11.2024 14:01

703a9842d90a88bc20f0c2558fe04f37.png

Приветствуем, Хабр!

От лица лидов ИТ практик Страхового Дома ВСК, подготовили подборку полезных советов по работе с базами данных из личного опыта и решили, что они могут быть интересны и вам. Основное внимание уделено PostgreSQL, но многие из рекомендаций универсальны и могут применяться к различным системам управления базами данных (СУБД). К этим выводам пришли через собственные ошибки на продакшене, и, несмотря на то, что это было непросто, рады поделиться с вами нашим опытом!

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

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

Проектирование базы данных

Избегайте использования NULL в столбцах с частыми изменениями

Применение NULL в столбцах типа DATE, TIMESTAMP, INTEGER и других может негативно сказаться на производительности и усложнить оптимизацию запросов.

Оптимизатор запросов сталкивается с дополнительными трудностями при работе с такими столбцами:

  • Снижение эффективности индексации: индексация столбцов, допускающих NULL, может быть менее эффективной, так как индекс должен учитывать и NULL-значения, что увеличивает его объем и замедляет выполнение запросов

  • Усложнение оптимизации запросов: наличие NULL-значений усложняет процесс оптимизации запросов, что может привести к менее эффективному выбору планов выполнения Рассматривайте партиционирование и шардирование там, где это возможно

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

Пример: допустим, у нас есть таблица транзакций с колонкой даты. Если мы используем NULL для указания, что дата ещё не назначена, это может усложнить индексацию. Альтернативой может быть использование специальных значений, таких как '1970–01–01'.

Рассматривайте партиционирование и шардирование там, где это возможно

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

Пример: для таблицы с заказами, где ключом партиционирования служит дата заказа: 

CREATE TABLE orders (
    id SERIAL PRIMARY KEY,
    order_date DATE NOT NULL,
    customer_id INT NOT NULL
) PARTITION BY RANGE (order_date);

CREATE TABLE orders_2023 PARTITION OF orders FOR VALUES FROM ('2023-01-01') TO ('2023-12-31');

Оптимизация запросов

Избегайте использования CROSS JOIN и RIGHT JOIN

Применение CROSS JOIN и RIGHT JOIN может создать проблемы для оптимизации запросов по следующим причинам:

  • CROSS JOIN: возвращает декартово произведение двух таблиц, что может привести к огромному количеству строк в результате. Это серьезно снижает производительность, поэтому от использования CROSS JOIN лучше отказаться в большинстве случаев.

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

Для улучшения производительности предпочтительнее использовать:

  • INNER JOIN: возвращает только те строки, которые совпадают в обеих таблицах, что часто бывает более эффективным.

  • Пересмотр структуры запроса: иногда переработка структуры запроса позволяет избежать использования CROSS JOIN и RIGHT JOIN, разбив запрос на более простые и эффективные части.

Минимизируйте использование оператора OR

Применение оператора OR в запросах может привести к снижению производительности по следующим причинам:

  • Неэффективная индексация: при использовании OR оптимизатор запросов может испытывать сложности с выбором подходящих индексов, что приведет к сканированию большого объема данных.

  • Трудности в планировании запросов: оптимизатору может быть сложно выбрать оптимальный план выполнения запроса при наличии оператора OR, особенно если он сочетается с другими условиями.

Для повышения производительности можно:

  • Разбить условия на несколько простых запросов с использованием оператора AND.

  • Использовать UNION ALL вместо OR, что в некоторых случаях может быть более эффективным.

Пример: вместо использования `OR` можно разбить запрос на два отдельных: 

SELECT * FROM orders WHERE status = 'completed';
UNION ALL
SELECT * FROM orders WHERE status = 'pending'

UNION ALL вместо UNION

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

Осторожно используйте CTE и представления (WITH, GROUP BY, VIEW)

Часто CTE (Common Table Expressions) и представления материализуются, что создает барьеры для оптимизатора запросов. Чтобы избежать этого, указывайте в CTE идентификаторы. Например, вместо использования подзапроса, который может быть материализован, лучше использовать вариант, где фильтрация происходит до группировки.

Применяйте LIKE без лидирующего %

Применение оператора LIKE без лидирующего символа % (например, LIKE 'значение%') более эффективно, так как позволяет использовать индексы для поиска значений, начинающихся с указанной строки. Это значительно ускоряет выполнение запроса.

Ставьте WHERE вместо HAVING там, где это возможно

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

Индексация

Используйте функциональные индексы там, где это необходимо

Например, при условии `UPPER (FIO)=FIO` обычный индекс по полю FIO не будет использоваться. В этом случае следует создать функциональный индекс по `UPPER (FIO)`. Условие запроса должно полностью соответствовать функциональному индексу!

Пример функционального индекса: 

CREATE INDEX idx_upper_name ON customers (UPPER(name));

-- Запрос, который использует индекс:
SELECT * FROM customers WHERE UPPER(name) = 'ИВАНОВ'

Избегайте применения GUID, используйте SEQUENCE

Применение последовательности (SEQUENCE) вместо GUID (глобально уникального идентификатора) способствует оптимизации запросов и улучшению производительности по нескольким причинам:

Последовательные идентификаторы лучше индексируются, что ускоряет выполнение запросов, особенно при сортировке и поиске.

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

Использование SEQUENCE для генерации идентификаторов требует меньше ресурсов по сравнению с генерацией GUID.

Обязательная индексация внешних ключей

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

Индексация столбцов с высокой кардинальностью

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

Учитывайте сортировку в запросах при создании индексов

Если запрос использует сортировку, учтите это в основном индексе.

Ненужные индексы

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

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

Инструменты мониторинга и анализа

Используйте `EXPLAIN` и `EXPLAIN ANALYZE` для анализа запросов и поиска узких мест. Также полезны встроенные представления PostgreSQL, такие как `pg_stat_activity` и `pg_stat_statements`, для анализа активности и статистики запросов.

Заключение

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

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

© Habrahabr.ru