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

Реляционная база данных — это набор данных с предопределенными связями между ними. Эти данные организованы в виде набора таблиц, состоящих из столбцов и строк. В базе данных хранится информация, которая является отражением какой -то части реального мира, сохраненная в таблицах.

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

Одним из фундаментальных понятий для работы с такими типами БД является транзакция.

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

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

1. Читаем баланс на счете №1
2. Читаем баланс на счете №2 - целевом
3. Уменьшаем баланс на счете №1
4. Увеличиваем баланс на счете №2
5. Читаем баланс на счете №1 - убеждаемся что он уменьшился
6. Читаем баланс на счете №2 - убеждаемся, что он увеличился
7. Вы великолепны (успешно завершаем транзакцию)

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

СПосле того, как познакомились с транзакцией, мы можем поговорить о принципе ACID. Ему соответствует большинство реляционных БД. ACID — это аббревиатура:

• Atomicity — Атомарность (гарантирует, что каждая транзакция будет выполнена полностью или не будет выполнена совсем)

• Consistency — Согласованность (каждая успешная транзакция фиксирует только допустимые результаты)

• Isolation — Изолированность (во время выполнения транзакции параллельные транзакции не должны оказывать влияния на ее результат.)

• Durability — Надежность (если пользователь получил подтверждение, что транзакция выполнена, он может быть уверен, что совершенные им изменения не будут отменены из-за сбоя).

Также, как и транзакция, понятия первичного и внешнего ключа являются основными сущностями при работе с реляционными БД.

Первичный ключ — набор определенных признаков, уникальных для каждой записи. Обозначается первичный ключ как primary key.

• Натуральный (номер паспорта, СНИЛС)
• Суррогатный (INTEGER ID, UUID)

Внешний ключ или Foreign key обеспечивает однозначную логическую связь, между таблицами одной БД.

Например, есть две таблицы А и В. В таблице А (обувь), есть первичный ключ «размер», в таблице В (цвет) есть колонка с названием «размер». Соответственно, «размер» является внешним ключом для логической связи таблиц В и А.

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

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

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

Первая нормальная форма (1NF)
Чтобы база данных находилась в ней, нужно, чтобы ее таблицы отвечали этим принципам:
• В таблице не должно быть дублирующих строк.
• В каждой ячейке таблицы хранится атомарное значение.
• В столбце хранятся данные одного типа.
• Отсутствуют массивы и списки в любом виде.

Вторая нормальная форма (2NF)
Чтобы база данных находилась в ней, таблицы должны отвечать этим требованиям:
• Таблица находится в первой нормальной форме.
• Таблица должна иметь ключ.
• Все неключевые столбцы таблицы должны зависеть от полного ключа (в случае, если он составной).

Третья нормальная форма (3NF)
Требование этой формы включает в себя два пункта:
• Таблица должна быть во второй нормальной форме.
• Все колонки в таблице зависят от первичного ключа и не зависят друг от друга.
Всего нормальных форм шесть, но для большинства задач достаточно, чтобы данные в БД прибывали в 3 нормальной форме.

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

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

В PostgreSQL есть следующие виды индексов:

• B-tree (поддерживает все стандартные операции сравнения и может применяться с большинством типов данных – для сортировки, ограничений уникальности и поиска по диапазону значений);

• Hash (обеспечивают быстрый доступ к данным по равенству; не поддерживают сортировку или поиск по диапазону значений);

• GiST (обобщенные и многоцелевые индексы, которые нужны для работы со сложными типами данных: геометрическими объектами, текстами и массивами; позволяют выполнять быстрый поиск по пространственным, текстовым и иерархическим данным);

• SP-GiST (нужны для работы с непересекающимися и неравномерно распределенными данными; эффективны для поиска в геометрических и IP-адресных данных);

• GIN (используются для полнотекстового поиска и поиска по массивам, JSON и триграммам);

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

Рассмотрим типы данных, которые встречаются в PostgreSQL.
• Текстовые типы данных, среди которых ‘text’, ‘varchar’, ‘char’, ‘citext’. Для индексирования текстовых данных можно использовать B-tree индексы.

• Числовые типы данных. Их также можно индексировать с помощью B-tree — они также поддерживают операции сравнения.

• Дата и время. Типы данных, такие как ‘date’, ‘time’, ‘timestamp’, ‘interval’ также поддерживают индексирование с помощью B-tree индексов.

• Массивы. Их можно индексировать с использованием GIN или GIST индексов. GIN подходят для быстрого поиска элементов в массиве, в то время как GIST могут быть использованы для поиск пересечений между массивами.

• Пространственные типы данных. ‘geometry’, ‘geography’ могут быть проиндексированы с помощью GiST и SP-GiST индексов.

• JSON и JSONB можно индексировать с помощью GIN или GIST индексов.

Вероятно, вы сталкивались с такой ситуацией, когда при написании SQL-запроса он сперва выполняется за секунду или полсекунды, а со временем процесс становится очень медленным. Чтобы понять, почему выполнение замедлилось, нужно использовать функцию EXPLAIN.

Как работает EXPLAIN? Допустим, мы взяли таблицу, заполнили рандомными данными. Затем сделали select, но перед этим написали explain. Таким образом мы получаем план запроса. EXPLAIN показывает, как наш планировщик будет выполнять запрос. Сначала его разбивает на составные части парсер, потом подключается анализатор, потом может быть рерайтер, который переписывает запрос, так как он будет эффективен.

Seq Scan означает последовательное, блок за блоком чтение данных таблицы foo.

cost – это не время, а некое сферическое в вакууме понятие, призванное оценить затраты на операции: на получение первой строки, всех строк.

rows — приблизительное количество возвращаемых строк при выполнении операции Seq Scan. Это значение возвращает планировщик.

width — средний размер одной строки в байтах.

Если вы выполняете Explain и видите Seq Scan, вам нужно либо подумать над тем, как ваша база данных спроектирована — может быть, стоит добавить ячейки, разбить какие-то данные. Ранее мы говорили о трех нормальных формах, возможно, нужно привести БД к одной из них, либо добавить индексов.

Помимо Explain также существует функция Analyze. При ее выполнении:
• считывается определенное количество строк таблицы, выбранных случайным образом;
• собирается статистика значений по каждой из колонок таблицы.

То есть EXPLAIN — это как бы ожидание планировщика, а ANALYZE — то, как будет выполняться запрос на физических данных.

Чтобы сверить ожидания планировщика с физическим выполнением — используем EXPLAIN (ANALYZE). В выводе команды добавляются:

actual times — реальное время в миллисекундах, затраченное для получения первой строки и всех строк соответственно

rows — реальное количество строк, полученных при Seq Scan

loops — сколько раз пришлось выполнить операцию Seq Scan

Total runtime — общее время выполнения запроса

Без использования происходит Seq Scan — последовательное сканирование, база данных идет от первой строки до последней, по всем срокам в таблице.

Если добавить индекс, можно наблюдать, как меняется QUERY PLAN и время выполнения. Добавили индекс, где c1 > 500. Запрос, выполненный с его использованием, выглядит так:

Резюмируем: если мы видим в запросе Seq Scan, это значит, что выборка идет без использования индексов. В этом случае добавление индекса может ускорить наш запрос.

При долгой разработке, когда создаются сервисы с длинным жизненным циклом, при использовании индексов могут возникнуть следующие проблемы:
• неиспользуемые индексы
• префиксные «клоны»
• timestamp «в середине»
• индексируемый boolean
• массивы в индексе
• NULL-мусор.

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