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

Моя работа здесь включает в себя проектирование БД, проектирование и разработку RESTful API, разработку внешнего интерфейса и разработку Android. Я знаю, что это звучит много, но это весело, когда вы работаете в небольшой компании — вы должны носить много шляп одновременно. Некоторым людям может не понравиться эта идея обработки нескольких вещей, поскольку это влияет на их производительность. Однако для стажера или нового выпускника, такого как я, это дает достаточный опыт за короткий промежуток времени. Наряду с этим я участвовал в разработке каждого аспекта продукта — даже макетов экрана. Это повышает вашу мораль и дает вам чувство причастности к вашему проекту, что в конечном итоге проявляется в самоотверженности и тяжелой работе над проектом.

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

Задача для меня состояла в том, чтобы адаптировать старую систему для обработки запросов новой, то есть обработки 3 млн + 3 млн транзакций из нескольких тысяч транзакций. Самые простые запросы, которые раньше занимали несколько миллисекунд, стали выполняться более 3-4 минут. Время загрузки первой страницы на портале увеличилось до 2–3 минут с 2–3 секунд. В этом посте рассказывается об уроках, которые я получил, чтобы выполнить задачу, некоторые из которых могут быть вам полезны. :)

1. Нормализованная БД чрезвычайно важна

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

Однако чем больше таблиц и отношений, тем больше количество JOIN. Таким образом, запросы к большим таблицам замедлят производительность. Следовательно, крайне важно приложить значительные усилия к проектированию БД в самом начале.

2. Ограничения внешнего ключа не должны увеличивать нагрузку (и они не сбивают с толку)

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

Внешние ключи помогают применять ограничения для разработчиков, инженеров и всех потребителей БД для поддержания целостности данных. Эти ограничения на отношения также помогают запросам JOIN предоставлять результаты быстрее, чем их объединение вручную. Следовательно, используя ограничения внешнего ключа, можно упростить поддержание согласованности базы данных. Создание этих ограничений в конечном итоге позволит разработчику сосредоточиться на других важных частях проекта, а не писать шаблон каждый раз, когда он пытается ВСТАВИТЬ, УДАЛИТЬ или ОБНОВИТЬ данные.

3. Индексы спасают жизни

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

4. Осторожно используйте JOIN

В нормализованной базе данных слишком часто выполняются запросы JOIN. Эти JOIN необходимы для выполнения простейшего запроса, такого как запрос пользователя на вход в систему, для создания сложных отчетов. СОЕДИНЕНИЕ определяется как декартово произведение двух таблиц, за которыми следует условие фильтрации. [ref] Запросы СОЕДИНЕНИЯ занимают много времени, и время их выполнения быстро увеличивается с увеличением объема данных. В нашем продукте выполнение запроса JOIN занимало около 200 миллисекунд, когда в системе было примерно полмиллиона строк данных. Однако время увеличилось до 10 минут, когда эти данные достигли 7 миллионов строк данных.

Урок, извлеченныйздесь, заключался в том, чтобы СОЕДИНЯТЬ только те строки данных, которые нас интересуют. Вместо того, чтобы сначала СОЕДИНЯТЬ таблицы, а затем писать запрос WHERE, мы должны минимизировать количество строк в таблице, которые должны быть присоединился. Объединение двух таблиц с 5 млн строк занимает намного больше времени, чем объединение таблиц с 1 млн строк в каждой.

5. Разделить данные просто, но это меняет правила игры

Чтобы ускорить запросы, можно легко переместить редко используемые данные в отдельную фиктивную таблицу. Мотивация для этого подхода исходит из механизма кэширования. В зависимости от приложения мы можем разделить данные на основе пользователя или других параметров, а не частоты. Такое разделение данных может уменьшить объем данных, которые необходимо обрабатывать во время выполнения каждого запроса. UNION очень помог нам, когда мы выбрали этот подход, чтобы иногда использовать все данные после разделения данных.

6. Необходимо очистить БД

Когда мы удаляем какие-либо данные из базы данных, память не освобождается мгновенно. База данных сохраняет мертвую память для будущего использования. Текущая СУБД предоставляет утилиты (например, VACUUM в pgsql) для очистки базы данных. Как правило, такие утилиты автоматически запускаются СУБД через определенный интервал времени. Однако при работе с огромным объемом данных это автоматическое выполнение утилиты очистки не может удалить мертвые кортежи или освободить память. Эти неиспользуемые ресурсы существенно увеличивают нагрузку на выполнение запроса. Запуск этой утилиты вручную освободит данные и ускорит операции с БД.

7. Неактивные транзакции всегда замедляют работу

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

8. Есть сомнения? Ваш поиск заканчивается здесь! #DBдокументация

Каналы StackOverflow и QA — отличный ресурс для разрешения ваших конкретных сомнений. Однако лучший и наиболее эффективный способ узнать о функциях и возможностях любой БД — обратиться к документации по БД.