kazminaviktoriya1
В профиль
Выход
![](data:image/svg+xml,<svg xmlns="http://www.w3.org/2000/svg" width="42" height="42"><rect fill-opacity="0"/></svg>)
Добавить аккаунт
Оптимизация хранилища данных. Скорость извлечения данных
В предыдущем уроке вы изучили оптимизацию хранения данных. Но помимо места, занимаемого БД, важна скорость чтения информации из БД. Ведь если в хранилище данных сложно найти информацию, то пользы от него будет мало. В этом уроке подробнее остановимся на оптимизации скорости чтения данных.
Скорость чтения из БД зависит от многих факторов. На некоторые из них могут повлиять пользователи БД. Например, аналитик может изменить структуру запроса — вместо всех столбцов выбирать только необходимые параметры. Другие факторы связаны с особенностями хранилища данных.
Рассмотрим основные подходы, которые позволяют повысить скорость извлечения данных:
- денормализация,
- индексация,
- партиционирование.
Денормализация
Нормализация базы данных не всегда полезна, она бывает избыточной и иногда замедляет работу БД. Денормализация — это постепенный процесс избавления от правил нормализации там, где это необходимо.
Существует множество подходов к денормализации данных. Вот два из них:
- Дублирование. Представьте, что вы храните информацию о клиенте в таблице «Клиент», а информацию о его полном адресе в таблице «Адрес». Но вам постоянно приходится сортировать клиентов по стране и объединять две таблицы. Вместо этого информацию о стране можно было бы добавить в таблицу «Клиент».
- Предварительная подготовка. Допустим, в БД есть таблица с данными о продажах. Менеджмент компании интересуется только месячными показателями. Вместо того чтобы постоянно агрегировать данные продаж, можно создать отдельную таблицу с итогами по месяцам.
Как обозначается денормализация в модели данных
В физической модели данных все таблицы отображаются в таком виде, в каком они должны быть разработаны. Если логическая модель соответствует 3НФ, а физическая модель должна быть денормализована, то структура физической модели будет не совпадать со структурой логической модели — появятся избыточные данные.
Логическая модель
Физическая модель
Когда стоит использовать
Денормализацию стоит внедрять аккуратно и только в тех случаях, когда есть много однотипных запросов к разным таблицам БД. При денормализации растёт количество записей, а значит, и ресурсов, необходимых для хранения данных, но уменьшается количество обращений к БД.
Изучите таблицу
orders («Заказы») и описания внесённых изменений. Выберите, какие из них относятся к нормализации, а какие — к денормализации.Из таблицы «Заказы» выделили таблицу «Заказы онлайн», так как отдел маркетинга использует данные онлайн-продаж для настройки рекламы в интернете.
Денормализация
Выделение таблицы — это денормализация. Без этого действия нужно было группировать данные изначальной таблицы по полю
source.Из таблицы «Заказы» выделили таблицу «Адреса», так как адрес зависел от идентификатора покупателя.
Нормализация
Атрибуты адреса не зависели от первичного ключа — идентификатора продаж. Это не соответствовало 2НФ. Чтобы исправить ситуацию, нужно было нормализовать данные.
В таблицу «Заказы» добавили поле данных «Количество продуктов» для удобства сбора статистики.
Денормализация
Выделение таблицы — это денормализация. При необходимости можно было получить эту информацию из поля
products.Индексация
Рассмотрим, зачем нужны индексы в базе данных, на примере книжной полки. Книги вы ставите на полку и забираете оттуда, когда хотите что-то прочесть. Так как полка маленькая, вы не задумываетесь о системе классификации книг, а просто ставите их на любые пустые места.
Каждый раз, когда вам нужно найти определённую книгу, вы просматриваете все переплёты с самого начала полки до нужной книги или до конца, если книги не нашлось. К счастью, для одной полки это довольно быстро.
Теперь представьте, что речь идёт не об одной полке, а о помещении библиотеки, где находятся тысячи книг. Тут-то вы и начинаете задумываться о том, что неплохо бы ввести систему классификации, например по названию книги.
Сортировать тысячи книг в алфавитном порядке было бы затруднительно. Поэтому вы составляете таблицу Excel. В каждой строчке вы записываете название книги, в каком стеллаже, на какой полке она стоит. Таким образом, чтобы найти книгу по названию, вам достаточно открыть таблицу, найти нужную строку, а затем подойти к указанному стеллажу.
Конечно, для поддержки такой системы требуется дополнительное время, но всё же это гораздо быстрее, чем искать книгу среди сотен других.
Теперь проведём аналогию с базой данных:
- Помещение — это таблица в БД.
- Таблица Excel — это упрощённый вариант индекса в БД. Индекс — это набор данных, записанных в удобном виде, который позволяет быстрее искать нужные строки.
- Название книги — это ключ в индексе, уникальное значение, которое может ссылаться на одну или несколько записей (например, если книг с одинаковым названием много и они расположены в разных местах). Даже если записей для каждого значения будет несколько, это всё равно быстрее, чем перебирать все данные вручную.
- Поиск книги — это SQL-запросы для получения данных. При этом после добавления индекса сами по себе запросы не меняются. Например, вы всё так же ищите книгу «Основы системного анализа» 😉
Таким образом, индекс — это структура, которая хранит значения одного или нескольких полей таблицы в отсортированном виде со ссылкой на соответствующую запись в основной таблице.
Как обозначается индекс в модели данных
В физической модели данных поля-индексы указываются в описании таблицы. В примере ниже проиндексированы столбцы
surname и email.
Когда стоит использовать
При добавлении индексов стоит учитывать объём данных. Индексы ускоряют извлечение данных и выполнение запросов в больших базах. С другой стороны, индексы замедляют вставку и удаление данных, поэтому стоит взвесить плюсы и минусы перед тем, как создавать индексы.
Партиционирование
Вернёмся к книгам. В любом издании «Войны и мира» Л. Н. Толстого будет около 1000 страниц — чуть больше или чуть меньше, в зависимости от шрифта и размера книги. Не каждый переплёт справится с таким объёмом страниц. Часто произведение выпускают в трёх или четырёх томах. Отдельный том легче выпустить с технологической точки зрения, да и читателю гораздо удобнее держать в руках небольшую книгу. Похожая ситуация и с базами данных.
Партиционирование базы данных — это разделение данных на части. Их можно хранить на разных дисках и серверах. Доступность и производительность при этом повышаются, так как запросы направляются не к огромному куску данных, а к мелким и легковесным частям.
Горизонтальное партиционирование — это разделение таблицы на строки. Например, данные о продуктах можно разбить на две таблицы с сортировкой по алфавиту.
Как горизонтальное партиционирование обозначается в модели данных
Основная таблица ассоциируется со вспомогательными таблицами, где есть аналогичные поля данных. В примере ниже данные разбиваются по годам, поэтому в наименовании вспомогательных таблиц указан год, но может быть использован и другой ключ партиционирования.
Горизонтальное партиционирование отчасти напоминает индексирование. Однако в случае с партиционированием таблица физически разделяется на несколько частей, поэтому для ускорения чтения данных нужно знать название итоговой таблицы.
Вертикальное партиционирование — это разделение таблицы на столбцы. Во всех вспомогательных таблицах обязательно должен быть ключ. Например, данные о продажах можно разбить так: в первую таблицу добавить ключ транзакции, сумму и дату, а во вторую — ключ транзакции и все вспомогательные данные.
Как вертикальное партиционирование обозначается в модели данных
Основная таблица ассоциируется со вспомогательными таблицами. У каждой вспомогательной таблицы свой набор полей данных.
Когда стоит использовать
Партиционирование стоит использовать для больших БД, когда разбиение таблицы на мелкие части может значительно ускорить чтение данных. Например, если запрос чаще всего идёт не ко всей таблице, а к определённым полям данных (вертикальное партиционирование) или записям (горизонтальное партиционирование).
Подведём итоги
Скорость извлечения данных — не менее важный параметр, чем объём памяти, занимаемый этими данными. Чем больше хранилище данных, тем сложнее найти в нём необходимую информацию.
Чтобы ускорить чтение из БД, команда разработки может прибегнуть к следующим методам:
- денормализация — добавление избыточных данных;
- индексация — увеличение скорости чтения по определённым полям данных;
- партиционирование — разбиение таблиц на несколько мелких.
К сожалению, все эти методы увеличивают размер БД, поэтому задача СА — в том числе определить пользовательские сценарии, обосновывающие выбор того или иного метода оптимизации хранилища данных.
