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

Нормальная форма хранения данных предполагает избегания дублирования данных. Ключевых правила два:

  • Атомарность означает, что все сущности хранятся в неделимом виде. Например, если мы храним адрес, то он скорее всего будет поделен на название города, страны и улицу. Все они должны быть представлены отдельными таблицами. Название города будет атомарным, т.к. дальше делиться не будет.
  • Уникальность требует, чтобы каждая сущность была определена только один раз. Например, название города с идентификатором 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 базы данных.

Самое важное

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

Подпишитесь на Хайлоад с помощью Google аккаунта
или закройте эту хрень