Таблицы с системой версионирования

MariaDB поддерживает временные таблицы данных в виде таблиц с системой версионирования (позволяя вам выполнять запросы и операции с историческими данными, обсуждаемыми ниже), периодами применимости (позволяя вам выполнять запросы и операции с временным диапазоном данных) и битемпоральными таблицами (которые объединяют как систему версионирования, так и периоды применимости).

Таблицы с системой версионирования

Таблицы с системой версионирования хранят историю всех изменений, а не только данные, которые в настоящее время действительны. Это позволяет анализировать данные на любой момент времени, аудировать изменения и сравнивать данные с разных моментов времени. Типичные случаи использования:

• Судебный анализ & правовые требования к хранению данных в течение N лет.
• Анализ данных (задний, тенденции и т. д.), например, для получения информации о персонале на год назад.
• Восстановление состояния таблицы на определенный момент времени.

Таблицы с системой версионирования были впервые представлены в стандарте SQL:2011.

Создание таблицы с системой версионирования

Синтаксис CREATE TABLE был расширен для возможности создания таблицы с системой версионирования. Для того, чтобы таблица была с системой версионирования, согласно SQL:2011, она должна иметь две сгенерированные колонки, период и специальную табличную опцию:

CREATE TABLE t(
   x INT,
   start_timestamp TIMESTAMP(6) GENERATED ALWAYS AS ROW START,
   end_timestamp TIMESTAMP(6) GENERATED ALWAYS AS ROW END,
   PERIOD FOR SYSTEM_TIME(start_timestamp, end_timestamp)
) WITH SYSTEM VERSIONING;

В MariaDB также можно использовать упрощенный синтаксис:

CREATE TABLE t (
   x INT
) WITH SYSTEM VERSIONING;

В последнем случае дополнительные колонки не будут созданы, и они не будут засорять вывод, например, SELECT * FROM t. Информация о версионировании всё равно будет храниться, и к ней можно получить доступ через псевдоколонки ROW_START и ROW_END:

SELECT x, ROW_START, ROW_END FROM t;

Добавление или удаление системы версионирования из таблицы

Существующую таблицу можно изменить, чтобы включить в нее систему версионирования.

CREATE TABLE t(
  x INT
);

ALTER TABLE t ADD SYSTEM VERSIONING;

SHOW CREATE TABLE t\G
*************************** 1. row ***************************
       Table: t
Create Table: CREATE TABLE `t` (
  `x` int(11) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1 WITH SYSTEM VERSIONING

Аналогичным образом, систему версионирования можно удалить из таблицы:

ALTER TABLE t DROP SYSTEM VERSIONING;

SHOW CREATE TABLE t\G
*************************** 1. row ***************************
       Table: t
Create Table: CREATE TABLE `t` (
  `x` int(11) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1

Также можно добавить систему версионирования со всеми явно созданными колонками:

ALTER TABLE t ADD COLUMN ts TIMESTAMP(6) GENERATED ALWAYS AS ROW START,
              ADD COLUMN te TIMESTAMP(6) GENERATED ALWAYS AS ROW END,
              ADD PERIOD FOR SYSTEM_TIME(ts, te),
              ADD SYSTEM VERSIONING;

SHOW CREATE TABLE t\G
*************************** 1. row ***************************
       Table: t
Create Table: CREATE TABLE `t` (
  `x` int(11) DEFAULT NULL,
  `ts` timestamp(6) GENERATED ALWAYS AS ROW START,
  `te` timestamp(6) GENERATED ALWAYS AS ROW END,
  PERIOD FOR SYSTEM_TIME (`ts`, `te`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 WITH SYSTEM VERSIONING

Вставка данных

При вставке данных в таблицу с системой версионирования ей присваивается значение row_start текущей метки времени и значение row_end FROM_UNIXTIME(2147483647.999999). Текущую метку времени можно настроить, установив системную переменную timestamp, например:

SELECT NOW();
+---------------------+
| NOW()               |
+---------------------+
| 2022-10-24 23:09:38 |
+---------------------+
 
INSERT INTO t VALUES(1);
 
SET @@timestamp = UNIX_TIMESTAMP('2033-10-24');

INSERT INTO t VALUES(2);
 
SET @@timestamp = default;

INSERT INTO t VALUES(3);
 
SELECT a,row_start,row_end FROM t;
+------+----------------------------+----------------------------+
| a    | row_start                  | row_end                    |
+------+----------------------------+----------------------------+
|    1 | 2022-10-24 23:09:38.951347 | 2038-01-19 05:14:07.999999 |
|    2 | 2033-10-24 00:00:00.000000 | 2038-01-19 05:14:07.999999 |
|    3 | 2022-10-24 23:09:38.961857 | 2038-01-19 05:14:07.999999 |
+------+----------------------------+----------------------------+

Запрос исторических данных

SELECT

Для запроса исторических данных используется клаузула FOR SYSTEM_TIME непосредственно после имени таблицы (перед алиасом таблицы, если таковой имеется). SQL:2011 предоставляет три синтаксических расширения:

• AS OF используется для просмотра таблицы в определенный момент времени в прошлом:

SELECT * FROM t FOR SYSTEM_TIME AS OF TIMESTAMP'2016-10-09 08:07:06';

• BETWEEN start AND end отобразит все строки, которые были видимыми в любой момент времени между двумя указанными моментами. Работает включительно; строка, видимая ровно в start или ровно в end, также будет показана.

SELECT * FROM t FOR SYSTEM_TIME BETWEEN (NOW() - INTERVAL 1 YEAR) AND NOW();

• FROM start TO end также отобразит все строки, которые были видимыми в любой момент времени между двумя указанными моментами времени, включая start, но исключая end.

SELECT * FROM t FOR SYSTEM_TIME FROM '2016-01-01 00:00:00' TO '2017-01-01 00:00:00';

Кроме того, MariaDB реализует нестандартное расширение:

• ALL отобразит все строки, исторические и текущие.

SELECT * FROM t FOR SYSTEM_TIME ALL;

Если клаузула FOR SYSTEM_TIME не используется, таблица будет отображать текущие данные. Это обычно то же самое, что если бы вы указали FOR SYSTEM_TIME AS OF CURRENT_TIMESTAMP, за исключением того, что вы настроите значение row_start (до MariaDB 10.11, это возможно только путем установки переменной secure_timestamp). Например:

CREATE OR REPLACE TABLE t (a int) WITH SYSTEM VERSIONING;

SELECT NOW();
+---------------------+
| NOW()               |
+---------------------+
| 2022-10-24 23:43:37 |
+---------------------+

INSERT INTO t VALUES (1);

SET @@timestamp = UNIX_TIMESTAMP('2033-03-03');

INSERT INTO t VALUES (2);

DELETE FROM t;

SET @@timestamp = default;


SELECT a, row_start, row_end FROM t FOR SYSTEM_TIME ALL;
+------+----------------------------+----------------------------+
| a    | row_start                  | row_end                    |
+------+----------------------------+----------------------------+
|    1 | 2022-10-24 23:43:37.192725 | 2033-03-03 00:00:00.000000 |
|    2 | 2033-03-03 00:00:00.000000 | 2033-03-03 00:00:00.000000 |
+------+----------------------------+----------------------------+
2 rows in set (0.000 sec)


SELECT a, row_start, row_end FROM t FOR SYSTEM_TIME AS OF CURRENT_TIMESTAMP;
+------+----------------------------+----------------------------+
| a    | row_start                  | row_end                    |
+------+----------------------------+----------------------------+
|    1 | 2022-10-24 23:43:37.192725 | 2033-03-03 00:00:00.000000 |
+------+----------------------------+----------------------------+
1 row in set (0.000 sec)


SELECT a, row_start, row_end FROM t;
Empty set (0.001 sec)

Представления и подзапросы

Когда таблица с системой версионирования используется в представлении или в подзапросе в предложении from, FOR SYSTEM_TIME можно использовать непосредственно в теле представления или подзапроса или (нестандартно) применять ко всему представлению, когда оно используется в SELECT:

CREATE VIEW v1 AS SELECT * FROM t FOR SYSTEM_TIME AS OF TIMESTAMP'2016-10-09 08:07:06';

Или

CREATE VIEW v1 AS SELECT * FROM t;
SELECT * FROM v1 FOR SYSTEM_TIME AS OF TIMESTAMP'2016-10-09 08:07:06';

Использование в репликации и бинарных журналах

Таблицы, использующие систему версионирования, неявно добавляют колонку row_end в первичный ключ. Хотя это обычно не проблема для большинства случаев использования, это может привести к проблемам при повторном применении операторов записи из бинарного журнала или в средах репликации, где первичный сервер повторяет оператор SQL на реплике.

В частности, эти записи включают значение в колонке row_end, содержащее метку времени, когда запись была первоначально сделана. Повторное появление первичного ключа со старыми колонками системы версионирования приводит к ошибке из-за дублирования.

Для устранения этой проблемы с репликацией MariaDB установите системную переменную secure_timestamp в значение YES на реплике. При установке реплика использует свои собственные системные часы при применении к журналу строк, что означает, что первичный сервер может повторять операцию столько раз, сколько потребуется, без возникновения конфликта. Повторные операции создают новые исторические строки с новыми значениями для колонок row_start и row_end.

Точная история транзакций в InnoDB

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

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

MariaDB поддерживает точную историю транзакций (только для движка InnoDB), которая позволяет видеть данные точно так, как их видел бы новый подключенный к серверу клиент при SELECT в указанный момент времени — строки, вставленные до этого момента, но подтвержденные после, не будут показаны.

Для использования точной истории транзакций InnoDB должен запоминать не метки времени, а идентификаторы транзакций в строке. Это делается путем создания сгенерированных колонок как BIGINT UNSIGNED, а не TIMESTAMP(6):

CREATE TABLE t(
   x INT,
   start_trxid BIGINT UNSIGNED GENERATED ALWAYS AS ROW START,
   end_trxid BIGINT UNSIGNED GENERATED ALWAYS AS ROW END,
   PERIOD FOR SYSTEM_TIME(start_trxid, end_trxid)
) WITH SYSTEM VERSIONING;

Эти колонки должны быть указаны явно, но их можно сделать НЕВИДИМЫМИ, чтобы избежать засорения SELECT * вывода.

При использовании точной истории транзакций можно необязательно использовать идентификаторы транзакций в клаузуле FOR SYSTEM_TIME:

SELECT * FROM t FOR SYSTEM_TIME AS OF TRANSACTION 12345;

Это отобразит данные точно так, как они выглядели для транзакции с идентификатором 12345.

Хранение истории отдельно

Когда история хранится вместе с текущими данными, это увеличивает размер таблицы, поэтому запросы к текущим данным — сканирование таблицы и поиск по индексам — займут больше времени, поскольку им нужно будет пропустить исторические данные. Если большинство запросов к этой таблице используют только текущие данные, может быть целесообразно хранить историю отдельно, чтобы уменьшить издержки от версионирования.

Это делается путем разбиения таблицы по SYSTEM_TIME. Благодаря оптимизации обрезки и выбора разбиений все запросы к текущим данным будут обращаться только к одному разбиению, хранящему текущие данные.

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

CREATE TABLE t (x INT) WITH SYSTEM VERSIONING
  PARTITION BY SYSTEM_TIME (
    PARTITION p_hist HISTORY,
    PARTITION p_cur CURRENT
  );

В этом примере вся история будет храниться в разбиении p_hist, а все текущие данные — в разбиении p_cur. Таблица должна иметь ровно одно текущее разбиение и по крайней мере одно историческое разбиение.

Разбиение по SYSTEM_TIME также поддерживает автоматическую ротацию разбиений. Можно вращать исторические разбиения по времени или по размеру. Этот пример демонстрирует, как вращать разбиения по размеру:

CREATE TABLE t (x INT) WITH SYSTEM VERSIONING
  PARTITION BY SYSTEM_TIME LIMIT 100000 (
    PARTITION p0 HISTORY,
    PARTITION p1 HISTORY,
    PARTITION pcur CURRENT
  );

MariaDB начнет записывать строки истории в разбиение p0, а когда оно достигнет размера 100000 строк, MariaDB переключится на разбиение p1. Поскольку исторических разбиений всего два, при переполнении p1 MariaDB выведет предупреждение, но продолжит запись в него.

Аналогично, можно вращать разбиения по времени:

CREATE TABLE t (x INT) WITH SYSTEM VERSIONING
  PARTITION BY SYSTEM_TIME INTERVAL 1 WEEK (
    PARTITION p0 HISTORY,
    PARTITION p1 HISTORY,
    PARTITION p2 HISTORY,
    PARTITION pcur CURRENT
  );

Это означает, что история первой недели после создания таблицы будет храниться в p0, история второй недели — в p1, а вся последующая история будет попадать в p2. Точное время вращения каждого разбиения можно увидеть в таблице INFORMATION_SCHEMA.PARTITIONS.

Можно объединить разбиение по SYSTEM_TIME и подразбиения:

CREATE TABLE t (x INT) WITH SYSTEM VERSIONING
  PARTITION BY SYSTEM_TIME
    SUBPARTITION BY KEY (x)
    SUBPARTITIONS 4 (
    PARTITION ph HISTORY,
    PARTITION pc CURRENT
  );

По умолчанию разбиения

CREATE TABLE t (x INT) WITH SYSTEM VERSIONING 
  PARTITION BY SYSTEM_TIME (
    PARTITION p0 HISTORY,  
    PARTITION pn CURRENT 
);

CREATE TABLE t (x INT) WITH SYSTEM VERSIONING 
  PARTITION BY SYSTEM_TIME;

CREATE TABLE t (x INT) WITH SYSTEM VERSIONING 
  PARTITION BY SYSTEM_TIME 
    INTERVAL 1 MONTH 
    PARTITIONS 12;

CREATE OR REPLACE TABLE t (x INT) WITH SYSTEM VERSIONING
  PARTITION BY SYSTEM_TIME PARTITIONS 12;
Query OK, 0 rows affected, 1 warning (0.518 sec)

Warning (Code 4115): Maybe missing parameters: no rotation condition for multiple HISTORY partitions.

CREATE OR REPLACE TABLE t (x INT) WITH SYSTEM VERSIONING
  PARTITION BY SYSTEM_TIME PARTITIONS 1;
ERROR 4128 (HY000): Wrong partitions for `t`: must have at least one HISTORY and exactly one last CURRENT

Автоматическое создание разбиений

CREATE TABLE t1 (x int) WITH SYSTEM VERSIONING
    PARTITION BY SYSTEM_TIME INTERVAL 1 HOUR AUTO;

CREATE TABLE t1 (x int) WITH SYSTEM VERSIONING
   PARTITION BY SYSTEM_TIME INTERVAL 1 MONTH
   STARTS '2021-01-01 00:00:00' AUTO PARTITIONS 12;

CREATE TABLE t1 (x int) WITH SYSTEM VERSIONING
  PARTITION BY SYSTEM_TIME LIMIT 1000 AUTO;

CREATE TABLE t1 (x int) WITH SYSTEM VERSIONING
  PARTITION BY SYSTEM_TIME INTERVAL 1 HOUR AUTO
  (PARTITION p0 HISTORY, PARTITION pn CURRENT);

CREATE TABLE t1 (x int) WITH SYSTEM VERSIONING
  PARTITION BY SYSTEM_TIME INTERVAL 1 HOUR AUTO;

# Disables auto-creation:
ALTER TABLE t1 PARTITION BY SYSTEM_TIME INTERVAL 1 HOUR;

# Enables auto-creation:
ALTER TABLE t1 PARTITION BY SYSTEM_TIME INTERVAL 1 HOUR AUTO;

Удаление старой истории

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

Можно полностью удалить версионирование из таблицы и добавить его снова, это удалит всю историю:

ALTER TABLE t DROP SYSTEM VERSIONING;
ALTER TABLE t ADD SYSTEM VERSIONING;

Однако это может быть довольно длительной операцией, так как таблице потребуется перестроение, возможно, дважды (в зависимости от движка хранилища).

Другой вариант — использовать разбиение и удалить некоторые из исторических партиций:

ALTER TABLE t DROP PARTITION p0;

Обратите внимание, что нельзя удалить текущую партицию или единственную историческую партицию.

И третий вариант; можно использовать вариант оператора DELETE, чтобы обрезать историю:

DELETE HISTORY FROM t;

или только старую историю до определенной точки во времени:

DELETE HISTORY FROM t BEFORE SYSTEM_TIME '2016-10-09 08:07:06';

или до определенной транзакции (с BEFORE SYSTEM_TIME TRANSACTION xxx).

Для защиты целостности истории этот оператор требует специального привилегии DELETE HISTORY.

В настоящее время использование оператора DELETE HISTORY со значением BEFORE SYSTEM_TIME, большим, чем ROW_END активных записей (как TIMESTAMP, у этого значения максимальное значение '2038-01-19 03:14:07' UTC) приведет к удалению исторических записей, а активные записи будут удалены и перемещены в историю. См. MDEV-25468.

До MariaDB 10.4.5 оператор TRUNCATE TABLE удалял все исторические записи из таблицы с системой версий.

С MariaDB 10.4.5 исторические данные защищены от операторов TRUNCATE в соответствии со стандартом SQL, и вместо этого генерируется ошибка 4137:

TRUNCATE t;
ERROR 4137 (HY000): System-versioned tables do not support TRUNCATE TABLE

Исключение столбцов из версионирования

Другое расширение MariaDB позволяет версионировать только подмножество столбцов в таблице. Это полезно, например, если у вас есть таблица с информацией о пользователе, которая должна быть версионирована, но один столбец, скажем, счетчик входа в систему, который часто увеличивается и не интересен для версионирования. Такой столбец можно исключить из версионирования, объявив его WITHOUT VERSIONING

CREATE TABLE t (
   x INT,
   y INT WITHOUT SYSTEM VERSIONING
) WITH SYSTEM VERSIONING;

Столбец также можно объявить WITH VERSIONING, что автоматически сделает таблицу версионированной. Приведенный ниже оператор эквивалентен приведенному выше:

CREATE TABLE t (
   x INT WITH SYSTEM VERSIONING,
   y INT
);

Системные переменные

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

system_versioning_alter_history

• Описание: SQL:2011 не допускает ALTER TABLE для таблиц с системой версий. Когда эта переменная установлена в значение ERROR, попытка изменить таблицу с системой версий приведет к ошибке. Когда эта переменная установлена в значение KEEP, ALTER TABLE будет разрешен, но история станет некорректной — при запросе исторических данных будет показана новая структура таблицы. Этот режим по-прежнему полезен, например, при добавлении новых столбцов в таблицу. Обратите внимание, что если исторические данные содержат или будут содержать null, попытка изменить эти столбцы на NOT NULL вернет ошибку (или предупреждение, если strict_mode не установлен).
• Командная строка: --system-versioning-alter-history=value
• Область: Глобальная, сессия
• Динамическая: Да
• Тип: Перечисление
• Значение по умолчанию: ERROR
• Допустимые значения: ERROR, KEEP

system_versioning_asof

• Описание: Если установлено значение определённой метки времени, к всем запросам будет применено неявное условие FOR SYSTEM_TIME AS OF. Это полезно, если нужно выполнить множество запросов к истории в определенный момент времени. Установите значение DEFAULT для восстановления поведения по умолчанию. Не оказывает влияния на DML, поэтому для запросов, таких как INSERT .. SELECT и REPLACE .. SELECT, необходимо явно указать AS OF.
• Командная строка: Нет
• Область: Глобальная, сессия
• Динамическая: Да
• Тип: Varchar
• Значение по умолчанию: DEFAULT

system_versioning_innodb_algorithm_simple

• Описание: Никогда не реализовывался полностью и удален в следующем релизе.
• Командная строка: --system-versioning-innodb-algorithm-simple[={0|1}]
• Область: Глобальная, сессия
• Динамическая: Да
• Тип: Булево
• Значение по умолчанию: ON
• Введено: MariaDB 10.3.4
• Удалено: MariaDB 10.3.5

system_versioning_insert_history

• Описание: Разрешает прямые вставки в столбцы ROW_START и ROW_END, если secure_timestamp разрешает изменение timestamp.
• Командная строка: --system-versioning-insert-history[={0|1}]
• Область: Глобальная, сессия
• Динамическая: Да
• Тип: Булево
• Значение по умолчанию: OFF
• Введено: MariaDB 10.11.0

Ограничения

• Ограничения для условий версионирования не могут быть применены к сгенерированным (виртуальным и постоянным) столбцам.
• До MariaDB 10.11 mariadb-dump не считывал исторические строки из версионированных таблиц, поэтому исторические данные не были бы резервными. Также восстановление меток времени не было бы возможно, поскольку они не могут быть определены путем вставки/пользователем. С MariaDB 10.11 используйте параметры -H или --dump-history для включения истории.

См. также

• Периоды времени приложения
• Двухвременные таблицы
• Таблица mysql.transaction_registry
• Временные таблицы MariaDB (видео)