Реляционные базы данных и как с ними оптимально работать
Реляционные базы данных отлично подходят для задач любой сложности, поэтому важно знать, как получить от них максимальную отдачу. Давайте разбираться.
MySQL – популярный выбор для малых и крупных корпоративных компаний благодаря его способности к масштабированию. Следом за ним по популярности идет SQL Server и PostgreSQL.
Начнем с основ хранения данных в реляционной базе данных.
Понимание реляционных БД
Хранение
MySQL является реляционной базой данных, где все данные представлены в виде кортежей, сгруппированных в отношения. Кортеж представлен его атрибутами.
Допустим, у нас есть приложение, где люди могут одалживать книги. Нам нужно будет хранить все операции над книгами. Для их хранения мы создали простейшую реляционную таблицу при помощи следующей команды:
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:
set profiling=1;
После того как значение true установлено, выполните запрос еще раз при помощи следующей команды для просмотра времени выполнения:
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 ;
Она вставит 100 000 случайных записей в таблицу 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 ...
Реляционные базы данных со сложными запросами страдают от времени выполнения.
Кажется, что это неплохо для одного запроса, но когда в системе происходят тысячи или даже миллионы запросов, выполняющиеся каждую минуту, это имеет большое значение. Увеличение времени ожидания будет влиять на общую производительность приложения.
Давайте вернем скорость
Index
MySQL и другие реляционные базы данных применяют индексирование структуры данных, помогающее получить выборку быстрее.
Существуют различные типы индексирования в MySQL:
- Primary Key – индекс на первичный ключ. По умолчанию первичные ключи всегда индексируются. Это гарантирует, что две строки не будут иметь первичный ключ.
- Unique – уникальный ключ обеспечит отсутствие одинаковых значений у нескольких записей. Несмотря на это, несколько значений Null могут храниться с уникальным индексом.
- Index – добавляется к любым другим полям, кроме первичного ключа.
- Full Text – помогает работать с текстовыми данными.
Существует еще два способа хранения индекса:
- Hash – используется в основном для точного соответствия ( = ) и не работает со сравнениями (≥, ≤)
- B-tree – это наиболее распространенный способ хранения данных, обеспечивающий эффективную работу с дисковой памятью.
MySQL использует B-tree по умолчанию. Данные хранятся в двоичном дереве, что делает извлечение данных очень быстрым.
Структурирование данных, при помощи B-дерева, помогает избежать полного сканирования таблицы со всеми кортежами.
Чтобы повысить производительность нашей book_transactions, добавим индекс в поле lender_id.
CREATE INDEX lenderId ON book_transactions(lender_id)
Эта команда добавляет индекс в поле lender_id. Давайте посмотрим, как это влияет на производительность при обработке 1,5 млн. записей. Запустим тот же запрос еще раз:
SELECT * FROM book_transactions WHERE lender_id = 1;
Query_ID | Duration | Query
1 | 0.00787600 | SELECT * FROM book_transactions ...
С добавлением правильного индекса мы видим резкое увеличение производительности.
Реляционные базы данных и их индексы
Индекс, который мы добавили выше, был индексом одиночного поля. Индексы также могут быть добавлены в составное поле.
Если в запросе участвует несколько полей, можно применить составной индекс. Это следует делать так:
CREATE INDEX lenderReturnDate ON book_transactions(lender_id, return_date);
Другое применение индексов
Запросы – это не единственное направление использования индексов. Они также могут быть использованы в условиях ORDER BY. Давайте отсортируем записи относительно lender_id:
SELECT * FROM book_transactions ORDER BY lender_id; 1517185 rows in set (4.08 sec)
Результат неутешительный. Что пошло не так при условии, что мы установили индекс? Попробуем углубиться в реляционные базы данных, чтобы разобраться с функцией EXPLAIN.
Использование Explain
Добавим Explain, чтобы увидеть, как запрос будет выполняться в нашем текущем наборе данных:
EXPLAIN SELECT * FROM book_transactions ORDER BY lender_id;
Вывод команды:
Существуют различные поля, возвращаемые Explain-ом. Заглянем в таблицу выше и выясним проблему.
- rows: общее количество строк, которые будут проверяться.
- filtered: процент строк, которые будут проверяться для получения данных.
- type: указывается, если используется индекс. ALL означает, что индекс не используется.
- possible_keys, key, key_len: NULL, что означает, что никакой индекс не используется.
Так почему же запрос не использует индекс?
Это происходит потому, что в запросе есть select *, т. е. выборка всех полей нашей таблицы.
Индекс содержит сведения только об индексируемых полях. Это означает, что MySQL снова должен будет обратиться к главной таблице для выборки данных. Для этого нужно немного переписать запрос.
Выборка только необходимого поля
Чтобы убрать необходимость перехода к основной таблице для запроса, нам нужно выбрать только то значение, которое присутствует в индексной таблице:
SELECT lender_id FROM book_transactions ORDER BY lender_id;
Это вернет результат за 0,46 секунды, что намного быстрее. Но показатель можно еще улучшить.
Поскольку этот запрос выполняется для всех 1,5 млн. записей, он выполняется немного дольше, поскольку ему необходимо загрузить данные в память.
Используем Limit
Нам не нужны все записи одновременно, и мы можем использовать оператор LIMIT для упрощения выборки:
SELECT lender_id FROM book_transactions ORDER BY lender_id LIMIT 1000;
С применением Limit, время ответа существенно улучшилось: 0,0025 секунды. Теперь мы можем получить следующую партию с применением OFFSET:
SELECT lender_id FROM book_transactions ORDER BY lender_id LIMIT 1000 OFFSET 1000;
Это приведет к выборке 1000 строк. При этом мы можем увеличить offset и limit, чтобы получить все данные. Только есть одно "но": с увеличением offset уменьшается производительность запроса.
Это происходит потому, что MySQL сканирует все данные, чтобы достичь точки смещения. Поэтому лучше не использовать большой offset.
Как насчет Count?
Движок InnoDB имеет способность писать в БД параллельно. Это делает его очень масштабируемым и улучшает пропускную способность в секунду.
Но за это придется заплатить. InnoDB не может добавлять счетчик кэша для записей в любой таблице. Подсчет должен быть произведен в реальном временя путем просмотра всей фильтрованной информации. Это делает COUNT медленным.
Поэтому для подсчета большого количества записей рекомендуется вычислять суммарные данные из прикладной логики.
Почему бы не добавить индекс ко всем полям?
Добавление индекса помогает повысить производительность, но также имеет свои издержки. Его следует использовать эффективно, иначе это приведет к следующим проблемам:
- Нужен мощный сервер.
- Когда что-то удаляется, происходит переиндексация (CPU загружен, и процесс удаления медленный).
- Когда что-то добавляется, происходит переиндексация (CPU загружен, и процесс вставки медленный).
Теперь мы понимаем, что добавление индекса облегчает жизнь, но мы по-прежнему не можем выбрать все данные, кроме тех, которые индексируются для быстрой работы.
Так как же выбрать все атрибуты и при этом не потерять производительность?
Партиционирование
Пока мы строим индексы, у нас есть информация только об индексируемом поле, и нет данных о полях, которых нет в индексе.
Как уже говорилось ранее, MySQL нужно “заглянуть” в главную таблицу, чтобы получить данные о других полях, а это замедлит время выполнения.
Данная проблема решается при помощи партиционирования.
Партиционирование (разбиение) – это метод, в котором MySQL разделяет большие таблицы на несколько малых, но все еще управляет ими как одной.
При выполнении любой операции с таблицей необходимо указать, какой раздел используется. При партиционировании MySQL будет немного проще, т. к. используется маленький набор данных для запроса. Заранее продуманное разбиение в соответствии с задачей – ключ к высокой производительности.
Если на данном этапе используется один сервер, то будет ли он масштабируемым?
Шардинг
Когда информации очень много, хранить ее на одном сервере затруднительно. Какой-нибудь раздел может быть “крупным”, и запросы к нему будут задерживать параллельные обращения к другим разделам.
Предположим, что данные за последние три месяца используются чаще всего, тогда как более старые – реже. Возможно, последние данные в основном обновляются / создаются, тогда как старые данные только читаются.
Чтобы устранить эту проблему, можно переместить раздел с последними тремя месяцами на другой сервер. Шардинг – это способ разделения большого набора данных на более мелкие блоки и перемещения в отдельные СУБД. Другими словами, шардинг можно назвать “горизонтальным разделением”.
Реляционные базы данных имеют возможность масштабироваться по мере роста приложения, нужно только найти правильный индекс и настроить инфраструктуру в соответствии с потребностями.