Статьи

Журнальные столы, без беспорядка

Октябрь 29, 2018

Первоначально автор Дамир Сударевич 

Таблицы журналов обычно находятся на периферии проектных работ, иногда добавляются в качестве запоздалой мысли. Обычно таблица журналов содержит большое количество строк и множество необязательных столбцов, большинство из которых содержат значения NULL. Кроме того, поскольку операторы ALTER TABLE становятся дорогими в производственной системе, эти таблицы часто включают в себя некоторый сериализованный массив (JSON, CSV) для всех необязательных полей. В других хорошо спроектированных системах эти журнальные таблицы выглядят как неприятный беспорядок.

Таблица регистрации может выглядеть примерно так: несколько обязательных и много необязательных (1,2,3… X) столбцов.

Одним из способов решения этой проблемы заключается в разработке далеко хранения нулей вертикально секционирования таблицы —  довольно эффективную , если БД поддерживает присоединиться к ликвидации, также известной как устранение таблицы.

Исключение соединений — это функция оптимизатора запросов, которая исключает таблицы из плана выполнения —  при условии, что они не нужны для получения набора результатов.

Чтобы проиллюстрировать исключение объединения, предположим, что у нас есть таблица User с необязательным столбцом UserHeight, который, как ожидается, будет иметь значение NULL для большинства записей.

Хранение нулей может быть спроектировано; в этом примере только записи с известным UserHeight имеют строку в таблице User_2.

Итак, если у нас есть запрос,

select
  FirstName
, LastName
, UserHeight
     from User_1 as a
left join User_2 as b on b.UserID = a.UserID ;

тогда план запроса выглядит так:

Если для запроса не нужны столбцы из второй таблицы, например:

select FirstName
     from User_1 as a
left join User_2 as b on b.UserID = a.UserID ;

тогда план не включает в себя вторую таблицу.

Важно отметить, что первая таблица всегда включена, например:

select UserHeight
     from User_1 as a
left join User_2 as b on b.UserID = a.UserID ;

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

Может быть неочевидно, что эта функция правильно разрешает представления, например:

create view vUser as
select
    a.UserID as UserID
  , FirstName
  , LastName
  , UserHeight
     from User_1 as a
left join User_2 as b on b.UserID = a.UserID ;

Таким образом, для:

select
  FirstName
, LastName
, UserHeight
from vUser ;

результат, как и ожидалось:

И, если в списке выбора нет столбцов из второй таблицы:

select FirstName
from vUser ;

тогда только первая таблица включена в план выполнения.

Теперь этот принцип может быть применен к проблеме таблицы журналирования. Вместо первого примера регистрации мы могли бы использовать что-то вроде:

и создать вид:

create view vLog as
select
    a.LogID as LogID
  , LogTime
  , LogCategory
  , Optional_1
  , Optional_2
  , Optional_3
  , Optional_X
from Log as a
left join Log_1 as a1 on a1.LogID = a.LogID
left join Log_2 as a2 on a2.LogID = a.LogID
left join Log_3 as a3 on a3.LogID = a.LogID
left join Log_X as aX on aX.LogID = a.LogID ;

Например, если для запроса требуются только таблицы Log и Log_3, то:

select
    LogID
  , LogTime
  , Optional_3
from vLog ;

План включает в себя только эти две таблицы.

Чтобы добавить новый столбец (свойство), необходимо сделать следующее:

  1. Создайте новую таблицу с LogID и новым столбцом (создать пустую таблицу не очень дорого).
  2. Измените представление, чтобы включить новую таблицу (довольно просто).
  3. Измените код регистрации (это необходимо сделать в любом случае).

Чтобы этот метод работал, оптимизатор БД должен поддерживать исключение таблиц, а количество дополнительных таблиц должно быть управляемым. Что управляемо? Я думаю, что-то вроде 10, 20, 50 —  просто попробуйте. Все новые версии MS SQL Server, Oracle, DB2, PostgeSQL поддерживают эту функцию, но MySQL 5.x все еще не поддерживает; MariaDB делает.


О Дамире Сударевиче


Хранилище данных Парень копает данные, любит кошек.

 

33
Share: