Денормализация данных
Нормальная форма хранения данных предполагает избегания дублирования данных. Ключевых правила два:
- Атомарность означает, что все сущности хранятся в неделимом виде. Например, если мы храним адрес, то он скорее всего будет поделен на название города, страны и улицу. Все они должны быть представлены отдельными таблицами. Название города будет атомарным, т.к. дальше делиться не будет.
- Уникальность требует, чтобы каждая сущность была определена только один раз. Например, название города с идентификатором 1 должно присутствовать только в таблице cities.
Нормализация обеспечивает удобство работы с данными. Так, при обновлении названия города, нужно будет сделать изменение только в одной записи таблицы городов.
Зато с точки зрения производительности нормализация обходится очень дорого. Для выбора названия города пользователя, нам понадобится сделать несколько запросов вместо одного. JOIN’ы негативно влияют на производительность приложения.
Денормализация
Денормализация – это постепенный процесс избавления от правил нормализации там, где это необходимо. Обычно это случаи, в которых есть частые повторные запросы к логически связанным данным. Например, постоянный выбор данных пользователя и названия его города из двух таблиц.
Существует два основных подхода при денормализации данных:
- Дублирование.
- Предварительная подготовка.
1. Дублирование данных
Допустим у нас есть таблицы такой структуры:
**users** id name city_id
**cities** id title country
---+ +---
| **users** |
—+ +—
| id |
| name |
| city_id |
—+ +—
—+ +—
| **cities** |
—+ +—
| id |
| title |
| country |
—+ +—
Вставка нового пользователя будет выглядеть так:
$city_id = 15;
mysql_query(‘INSERT INTO users SET name = “‘ . $name . ‘”, city_id = ‘ . $city_id);
Для выборки названия города или страны пользователя нам понадобится делать два запроса либо один JOIN:
SELECT * FROM users u JOIN cities c ON (c.id = u.city_id)
Для того, чтобы использовать преимущество дублирования, нам понадобится добавить колонку city_title в таблицу users:
users id name city_id **city_title**
Во время вставки пользователя, в эту колонку нужно будет сохранять название города. Теперь вставка нового пользователя будет выглядеть так:
$city_id = 15;
**$city = mysql_fetch_assoc( mysql_query(‘SELECT * FROM cities WHERE id = ‘ . $city_id) );**
mysql_query(‘INSERT INTO users SET name = “‘ . $name . ‘”, city_id = ‘ . $city_id . ‘, **city_title=”‘ . $city[‘title’] . ‘”**’);
В результате, мы сможем выбрать данные пользователя сразу с названием города за один простой запрос:
SELECT id, name, city_title FROM users
Связи один ко многим
Связи один ко многим также можно оптимизировать используя дублирование. Представим в качестве примера таблицы постов с метками в блоге:
**posts** id title body
**tags** id title
**post_tags** post_id tag_id
---+ +---
| **posts** |
—+ +—
| id |
| title |
| body |
—+ +—
—+ +—
| **tags** |
—+ +—
| id |
| title |
—+ +—
—+ +—
| **post_tags** |
—+ +—
| post_id |
| tag_id |
—+ +—
Для выборки меток поста нам понадобится сделать два отдельных запроса (или один JOIN):
SELECT * FROM tags t JOIN post_tags pt ON (pt.tag_id = t.id) WHERE pt.post_id = 1;
Вместо этого мы могли бы сохранить список меток в отдельной текстовой колонке через запятую:
posts id title body **tags**
Тогда при выводе постов не будет необходимости делать дополнительные запросы для получения списка меток.
2. Предварительная подготовка данных
Агрегатные запросы обычно наиболее тяжелые. Например, получение количества записей по определенному условию:
SELECT count(*) FROM users WHERE group_id = 17
Кроме дублирования данных из одних таблиц в другие, можно также сохранять данные, которые рассчитываются. Тогда можно будет избежать тяжелых агрегатных выборок.
Например, для хранения количества пользователей в группе, необходимо добавить дополнительную колонку:
groups id title **user_count**
Тогда, при каждом добавлении пользователя, необходимо будет увеличивать значение в колонке user_count на 1:
UPDATE groups SET user_count = user_count + 1 WHERE id = 17
Такая схема хранения данных обычно называется факты + измерения:
При этом у нас есть таблицы с основными данными (факты) и таблицы измерений, где сохраняются расчетные данные.
Вертикальные таблицы
Вертикальная структура использует строки таблицы для хранения названия полей и их значений.
Преимуществом хранения данных в таком виде является возможность удобного шардинга. Кроме это будет возможность добавлять новые поля без изменения структуры таблицы. Вертикальная структура хорошо подойдет для таблиц, в которых могут меняться колонки.
Аналогичной структурой и преимуществами обладают Key-Value базы данных.
Самое важное
Денормализацию важно внедрять постепенно и только для тех случаев, когда присутствуют повторные выборки связанных данных с разных таблиц. Помните, при дублировании данных вырастет количество записей, но уменьшится количество чтений. Рассчитываемые данные также удобно сохранять в колонках, чтобы избежать ненужных агрегатных выборок.
Сообщить об опечатке
Текст, который будет отправлен нашим редакторам: