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

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

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

Самое важное

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