Izenx
Разработка

Полезные навыки работы с БД для начинающего backend разработчика

Работа с базами данных (БД) является неотъемлемой частью задач backend разработчика. В данной статье мы рассмотрим несколько полезных навыков в решении задач, которые часто встречаются на практике. Для демонстрации примеров возьмем СУБД PostgreSQL, фреймворки PHP и рассмотрим следующие темы:

  • Проблема n+1
  • Транзакции
  • Индексы
  • Нормализация и денормализация
  • Массовая загрузка и пакетная обработка

Проблема n+1

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

Пример

У нас есть три таблицы: "Place", "Users" и "Category". Каждое место может иметь несколько посетителей и одну категорию. Когда мы хотим получить список всех мест и их посетителей, мы можем сделать JOIN, который присоединяет эти таблицы. Но поскольку каждому месту может соответствовать несколько посетителей, то для каждого места будет выполняться отдельный запрос для извлечения всех его посетителей. Это создает дополнительный запрос (n+1), где n — количество записей в основной таблице (Place) , и 1 — запрос для каждого пользователя:

$query = $this->em ->getRepository(Place::class) ->createQueryBuilder('p') ->leftJoin('p.users', 'u') ->leftJoin('p.category', 'c') ->orderBy('p.title', 'ASC');

SQL запрос:

SELECT p0_.id AS id_0, p0_.title AS title_1, p0_.category_id AS category_id_2 FROM place p0_ LEFT JOIN place_user p2_ ON p0_.id = p2_.place_id LEFT JOIN "user" u1_ ON u1_.id = p2_.user_id LEFT JOIN category c3_ ON p0_.category_id = c3_.id ORDER BY p0_.title ASC

Как можно видеть из запроса, не происходит выборки пользователей, следовательно, получим дополнительные запросы на загрузку пользователей: 5021 query in 3306.08 ms.

Исправим запрос, добавив выборку пользователей:

$q = $this->em ->getRepository(Place::class) ->createQueryBuilder('p') ->select('u','c','p') ->leftJoin('p.users', 'u') ->leftJoin('p.category', 'c') ->orderBy('p.title', 'ASC');

SQL запрос:

SELECT p0_.id AS id_0, p0_.title AS title_1, u1_.id AS id_2, u1_.name AS name_3, u1_.is_active AS is_active_4, c2_.id AS id_5, c2_.title AS title_6, p0_.category_id AS category_id_7 FROM place p0_ LEFT JOIN place_user p3_ ON p0_.id = p3_.place_id LEFT JOIN "user" u1_ ON u1_.id = p3_.user_id LEFT JOIN category c2_ ON p0_.category_id = c2_.id ORDER BY p0_.title ASC"

В результате, всё выгружается за один запрос: 1 query in 29.32 ms.

Следует отметить, что ORM предоставляют возможность автоматической оптимизации запросов для избежания проблемы n+1, советую изучить эти механизмы по запросу в поисковике — lazy/eager/etc fetch {orm_name}.

Транзакции

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

Ключевые свойства транзакций, называются ACID:

  • Атомарность (Atomicity): Транзакция является атомарной, что означает, что все ее операции выполнены либо все, либо ни одна из них. Нет промежуточного состояния. Если хотя бы одна операция не может быть выполнена, все изменения откатываются (rollback), и база данных возвращается к исходному состоянию.
  • Согласованность (Consistency): Транзакция переводит базу данных из одного согласованного состояния в другое. Это означает, что транзакция должна удовлетворять всем предопределенным правилам и ограничениям данных, чтобы сохранить целостность базы данных.
  • Изолированность (Isolation): Изоляция гарантирует, что выполняемая транзакция изолирована от других транзакций, выполняющихся параллельно. Изменения, сделанные одной транзакцией, не видны другим, пока транзакция не завершится успешно.
  • Долговечность (Durability): После успешного завершения транзакции ее изменения остаются в базе данных даже в случае сбоя системы или перезагрузки.

Пример

Есть платежная система, счета описаны в таблице «account»:

INSERT INTO account (account_id, account_name, balance) VALUES (1, 'Счет #1', 1000), (2, 'Счет #2', 1500);

Задача перевести 1000 со счета (account_id = 1) на счет (account_id= 2):

UPDATE account SET balance = balance - 1000 WHERE account_id = 1; UPDATE account SET balanc = balance + 1000 WHERE account_id = 2;

Очевидное решение, посмотрим результат:

account_id account_name balance "1" "Счет #1" "0" "2" "Счет #2" "1500"

Понятно почему у первого счета balance = 0, но почему у второго счета баланс не изменился? Дело в том, что я допустил ошибку в запросе на обновление второго счета, следовательно, выполнился только первый запрос.

Для профилактики подобных ситуаций подойдут транзакции:

BEGIN TRANSACTION; UPDATE account SET balance = balance - 1000 WHERE account_id = 1; UPDATE account SET balanc = balance + 1000 WHERE account_id = 2; COMMIT;

Так как в запросе допущена ошибка, то транзакция не выполнится:

account_id account_name balance "1" "Счет #1" "1000" "2" "Счет #2" "1500"

Индексы

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

Существует несколько видов индексов, самый распространенный создается на основе B-tree.

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

  • Ускорение поиска данных: Индексы позволяют быстро находить нужные записи без необходимости сканировать всю таблицу.
  • Повышение производительности сортировки: Индексы также ускоряют операции сортировки данных.
  • Оптимизация соединений таблиц: При выполнении соединения (JOIN) между таблицами индексы помогают быстрее находить соответствующие строки.
  • Увеличение производительности фильтрации: Индексы сокращают время выполнения операций фильтрации (WHERE) по определенным условиям.

Однако, необходимо учитывать и некоторые недостатки:

  • Дополнительное использование памяти: Индексы занимают дополнительное пространство в базе данных, что может привести к увеличению размера БД.
  • Замедление операций записи: При вставке, обновлении или удалении данных индексы должны обновляться, что может повлечь за собой некоторую накладную нагрузку.
  • Потребность в обслуживании: Индексы должны быть обслуживаемы, что может потребовать периодической перестройки или переиндексации.
  • Выбор оптимальных индексов: Создание слишком много или неправильных индексов может негативно сказаться на производительности.

Некоторые из наиболее распространенных видов индексов:

  • Индекс по одному столбцу (Single-Column Index): Это наиболее простой тип индекса, который создается на одном столбце таблицы. Ускоряет поиск и сортировку по этому столбцу.
  • Индекс по нескольким столбцам (Composite Index): Этот тип индекса создается на нескольких столбцах таблицы. Позволяет ускорить поиск, сортировку и фильтрацию, которые включают несколько столбцов.
  • Уникальный индекс (Unique Index): Обеспечивает уникальность значений в указанных столбцах таблицы. Запрещает вставку дублирующихся значений в индексируемые столбцы.
  • Полнотекстовый индекс (Full-Text Index): Используется для выполнения полнотекстового поиска по текстовым полям. Позволяет быстро находить совпадения по словам или фразам.
  • Кластерный индекс (Clustered Index): Определяет физический порядок хранения данных в таблице. Таблица может иметь только один кластерный индекс, и он определяет порядок сортировки всех строк в таблице.
  • Индекс с битовым отображением (Bitmap Index): Используется для индексирования булевых или категориальных данных. Каждому возможному значению присваивается битовое значение, упрощая поиск и фильтрацию.
  • Разреженный индекс (Sparse Index): Применяется к таблицам с большим количеством пустых или нулевых значений. Экономит место и ускоряет выполнение операций на таких таблицах.

Лекция от VK team по теме индексов.

Нормализация и денормализация

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

Денормализация

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

Пример

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

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

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

Массовая загрузка и пакетная обработка

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

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

Пример

Необходимо получить данные из xml файла и заполнить ими таблицу cities с полями name, created_at, updated_at.

Решение без массовой и пакетной вставки:

public function handle() { $filePath = storage_path('app/cities.xml'); $xmlReader = new \XMLReader(); $xmlReader->open($filePath); while ($xmlReader->read() && $xmlReader->name !== 'city'); while ($xmlReader->name === 'city') { $node = new SimpleXMLElement($xmlReader->readOuterXML()); City::create([ 'name' => (string) $node->name, ]); $xmlReader->next('city'); } $xmlReader->close(); $this->info('Cities table has been filled'); }

На каждый город будет запрос:

[132] => Array ( [query] => insert into `cities` (`name`, `updated_at`, `created_at`) values (?, ?, ?) [bindings] => Array ( [0] => Crooksland [1] => 2023-05-28 04:10:44 [2] => 2023-05-28 04:10:44 ) [time] => 2.87 ) [133] => Array ( [query] => insert into `cities` (`name`, `updated_at`, `created_at`) values (?, ?, ?) [bindings] => Array ( [0] => Alvinaport [1] => 2023-05-28 04:10:44 [2] => 2023-05-28 04:10:44 ) [time] => 2.18 )

В среднем, один запрос выполняется 2.5 ms, а в файле более 2.000.000 городов.

Решение с массовой и пакетной вставкой:

public function handle() { $startTime = microtime(true); $startMemory = memory_get_usage(); $filePath = storage_path('app/cities.xml'); $xmlReader = new \XMLReader(); $xmlReader->open($filePath); $batchSize = 1000; $counter = 0; $cities = []; while ($xmlReader->read() && $xmlReader->name !== 'city'); while ($xmlReader->name === 'city') { $node = new SimpleXMLElement($xmlReader->readOuterXML()); $cities[] = ['name' => (string) $node->name, 'updated_at' => date('Y-m-d H:i:s'), 'created_at' => date('Y-m-d H:i:s')]; if (++$counter % $batchSize === 0) { City::insert($cities); $cities = []; } $xmlReader->next('city'); } if (!empty($cities)) { City::insert($cities); } $xmlReader->close(); $endTime = microtime(true); $endMemory = memory_get_usage(); $timeDiff = $endTime - $startTime; $memoryDiff = $endMemory - $startMemory; $this->info('Time taken: ' . round($timeDiff, 2) . ' seconds'); $this->info('Memory used: ' . round($memoryDiff / 1024 / 1024, 2) . ' MB'); $this->info('Cities table has been filled'); }

Лог запросов:

[query] => insert into `cities` (`created_at`, `name`, `updated_at`) values (?, ?, ?), (?, ?, ?), (?, ?, ?), (?, ?, ?), (?, ?, ?), (?, ?, ?)..... [bindings] => Array ( [0] => 2023-05-28 04:28:08 [1] => North Rory [2] => 2023-05-28 04:28:08 [3] => 2023-05-28 04:28:08 [4] => Berniecemouth .......

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

При $batchSize = 1000:

Time taken: 90.58 seconds Memory used: 120.75 MB

При $batchSize = 10000:

Time taken: 78.49 seconds Memory used: 1177.92 MB
55