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

kak optimalno rabotat s relyaczionnymi bazami dannyh

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

MySQL был популярным выбором для малых и крупных компаний из-за его способности к масштабированию. Подобным образом PostgreSQL также получил рост популярности.

NuCURJVqv3ixjGlK-U2yBGDWGzmoloI9PzIe
Источник фото: https://insights.stackoverflow.com/survey/2018/

Согласно опросу Stack Overflow 2018 MySQL является самой популярной базой данных среди всех пользователей.

Нижеследующие примеры используют InnoDB как механизм MySQL. Они не ограничиваются только MySQL, но также относятся к другим реляционным базам данных, таким как PostgreSQL. Весь контрольный анализ производится на компьютере с 8 ГБ оперативной памяти и процессором i5 2,7 ГГц.

Давайте начнем с оснований того, как реляционная база данных хранит данные.

Понимание реляционных баз данных

Хранение

MySQL – это реляционная база данных, где все данные представлен в виде кортежей, сгруппированных в отношения. Кортеж представлен его атрибутами.

5aDL3AR1tfjEnDtw4rPGQYskfOyDJ-V2RBgg
Источник изображения: https://commons.wikimedia.org/wiki/File:Relational_database_terms.svg

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

> CREATE TABLE book_transactions ( id INTEGER NOT NULL   AUTO_INCREMENT, book_id INTEGER, borrower_id INTEGER, lender_id INTEGER, return_date DATE, PRIMARY KEY (id));

Таблица выглядит так:

book_transactions
------------------------------------------------
id  borrower_id  lender_id  book_id  return_date

Здесь id является первичным ключом и borrower_id, lender_id, book_id это внешние ключи. После запуска нашей программы будет записано несколько транзакций:

book_transactions
------------------------------------------------
id  borrower_id  lender_id  book_id  return_date
------------------------------------------------
1   1            1          1        2018-01-13
2   2            3          2        2018-01-13
3   1            2          1        2018-01-13

Получение данных

У нас есть страница информационной панели для каждого пользователя, где они могут просматривать транзакции с арендованными книгами. Итак, давайте вытащим транзакции книги для пользователя:

> SELECT * FROM book_transactions WHERE borrower_id = 1;
book_transactions
------------------------------------------------
id  borrower_id  lender_id  book_id  return_date
------------------------------------------------
1   1            1          1        2018-01-13
2   1            2          1        2018-01-13

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

> set profiling=1;

Когда профилирование установлено, снова запустите запрос и воспользуйтесь следующей командой, чтобы просмотреть время выполнения:

> show profiles;

Это вернет продолжительность запроса, который мы выполнили.

Query_ID | Duration   | Query
       1 | 0.00254000 | SELECT * FROM book_transactions ...

Выполнение кажется очень хорошим.

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

Проблема

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

Чтобы вставить много данных в эту таблицу, я написал следующую процедуру:

DELIMITER //
 CREATE PROCEDURE InsertALot()
   BEGIN
   DECLARE i INT DEFAULT 1;
   WHILE (i <= 100000) DO
    INSERT INTO book_transactions (borrower_id, lender_id, book_id,   return_date) VALUES ((FLOOR(1 + RAND() * 60)), (FLOOR(1 + RAND() * 60)), (FLOOR(1 + RAND() * 60)), CURDATE());
    SET i = i+1;
   END WHILE;
 END //
 DELIMITER ;
* It took around 7 minutes to insert 1.5 million data

Это вставляет 100000 случайных записей в нашу таблицу book_transactions. После этого профайлер демонстрирует незначительное увеличение времени выполнения:

Query_ID | Duration   | Query
       1 | 0.07151000 | SELECT * FROM book_transactions ...

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

Query_ID | Duration   | Query
       1 | 0.36795200 | SELECT * FROM book_transactions ...

Это простой запрос с целым полем.

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

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

Будет гораздо больше времени ожидания, и это повредит общей производительности программы. Время выполнения того же запроса увеличилось с 2 мс до 370 мс.

Возврат скорости

указатель

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

В MySQL существуют разные типы индексации:

  • Первичный ключ — Индекс добавлен в первичный ключ. По умолчанию первичные ключи всегда индексируются. Это также гарантирует, что две строчки не имеют одинакового значения первичного ключа.
  • Уникальный Уникальный индекс ключа гарантирует, что никакие две строки по отношению не имеют одинакового значения. Несколько значений Null может храниться с уникальным индексом.
  • Индекс — Добавление к другим полям, кроме первичного ключа.
  • Полный текст Полнотекстовый индекс помогает выполнять запросы на данные на основе символов.

В основном существует два способа хранения индекса:

Хэш — это в основном используется для точного соответствия (=) и не работает с сравнениями (≥, ≤)

B-дерево — Это наиболее распространенный способ хранения упомянутых типов индексов.

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

xUuJBk8kDuyCrIzQTpfznpiwENHzXr5gMLYC
Формат хранения данных B-Tree

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

В приведенном выше B-дереве всего 16 узлов. Скажем, нам нужно найти число 6. Нам нужно всего-навсего сделать 3 сканирования, чтобы получить число. Это помогает повысить производительность поиска.

Итак, чтобы улучшить производительность нашего отношения book_transactions, давайте добавим индекс в поле lender_id.

> CREATE INDEX lenderId ON book_transactions(lender_id)
----------------------------------------------------
* It took around 6.18sec Adding this index

Вышеприведенная команда добавляет индекс к полю lender_id. Давайте посмотрим, как это влияет на производительность для 1,5 миллиона данных, которые у нас есть, снова запустив тот же запрос.

> SELECT * FROM book_transactions WHERE lender_id = 1;
Query_ID | Duration   | Query
       1 | 0.00787600 | SELECT * FROM book_transactions ...

Ууууу! Мы вернулись.

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

Сводный и единичный указатель

Индекс, который мы добавили, был индексом одного поля. Индексы также можно добавить в составное поле.

Если бы наш запрос содержал несколько полей, нам помог бы составленный индекс. Мы можем добавить составленный индекс с помощью такой команды:

> CREATE INDEX lenderReturnDate ON book_transactions(lender_id, return_date);

Другое использование индексов

Запросы – это не единственное использование индексов. Их можно использовать для СОРТИРОВАТЬ ЗА пункт также. Давайте упорядочим записи по lender_id.

> SELECT * FROM book_transactions ORDER BY lender_id;
1517185 rows in set (4.08 sec)

4.08 сек, это много! Итак, что пошло не так ли? У нас есть свой индекс. Давайте глубоко погрузимся в то, как выполняется запрос с помощью ПОЯСНИТЕ пункт.

Использование Explain

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

> EXPLAIN SELECT * FROM book_transactions ORDER BY lender_id;

Результат этого таков, как показано ниже:

ZDNJI8luN9QYAnV54eRQ4WZp6WMCQQLnGdOY
Используйте пояснения, чтобы увидеть, как будет выполняться запрос

Существуют разные отрасли, которые объясняют возврат. Давайте заглянем в таблицу выше и выясним проблему.

строки: Общее количество строк, которые будут просканированы

отфильтровано: Процент строки, которая будет просканирована для получения данных

тип: Он предоставляется, если используется индекс. ALL означает, что он не использует индекс

возможные_ключи, ключ, ключ_лен все значения NULL, что означает, что индекс не используется.

Итак, почему запрос не использует индекс?

Это потому, что у нас есть select * в нашем запросе, что означает, что мы выбираем все поля из нашего отношения.

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

Итак, как нам написать запрос?

Выберите Только обязательное поле

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

> SELECT lender_id FROM book_transactions ORDER BY lender_id;

Это вернет результат за 0,46 секунды, что гораздо быстрее. Но есть еще куда усовершенствоваться.

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

Используйте предел

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

> SELECT lender_id
  FROM book_transactions
  ORDER BY lender_id LIMIT 1000;

При наличии ограничений время ответа теперь резко улучшается и выполняется в 0,0025 секунды. Теперь мы можем получить следующую партию СМЕЩЕНИЕ.

> SELECT lender_id
  FROM book_transactions
  ORDER BY lender_id LIMIT 1000 OFFSET 1000;

Это позволит получить следующую партию из 1000 строк. Посредством этого мы можем увеличить смещение и ограничение, чтобы получить все данные. Но есть «поладок»! С увеличением смещения производительность запроса снижается.

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

А как насчет запроса Count?

Двигатель InnoDB имеет возможность писать одновременно. Это делает его очень масштабируемым и улучшает пропускную способность в секунду.

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

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

Почему бы не добавить индекс ко всем полям?

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

  • Нужно много памяти, большая машина
  • Когда мы удаляем, происходит повторное индексирование (удаление интенсивно и медленнее)
  • Когда мы что-то добавляем, происходит переиндексация (интенсивные и более медленные вставки)
  • Обновление не выполняет полную переиндексацию, поэтому обновление происходит быстрее и эффективнее ЦБ.

Теперь мы поняли, что добавление индекса очень помогает. Но мы не можем выбрать все данные, кроме индексируемых для быстрой работы.

Итак, как мы можем выбрать все атрибуты и получить быструю производительность?

Перегородка

Пока мы создаем индексы, у нас есть информация только о индексируемом поле. Но у нас нет данных о полях, которых нет в индексе.

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

Мы можем решить это с помощью разбиения.

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

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

Но если мы все еще используем ту же машину, будет ли она масштабироваться?

Шардинг

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

Конкретный раздел может быть трудным и требует больше запросов, тогда как другие меньше. Так одно повлияет на другое. Они не могут масштабироваться по отдельности.

Скажем, данные за последние три месяца наиболее используемы, тогда как старые данные используются меньше. Возможно, последние данные в основном обновляются/создаются, тогда как старые данные в основном только когда-либо читаются.

Чтобы решить эту проблему, мы можем переместить последние три месяца раздела на другую машину. Шардинг – это способ, с помощью которого делим большой набор данных на меньшие фрагменты и переходим к отдельным СУБД. То есть шардинг также можно назвать «горизонтальным разделением».

Реляционные базы данных могут масштабироваться по мере роста программы. Необходимо найти правильный индекс и настроить инфраструктуру в соответствии с потребностями.

Также опубликовано в блоге Milap Neupane: Как оптимально работать с реляционными базами данных

Добавить комментарий

Ваш адрес email не будет опубликован.