В зависимости от различных характеристик движка хранения вы можете гибко создавать разные таблицы в соответствии с требованиями различных сценариев. Ниже приведены примеры.
Таблицы MARS2 зависят от расширения matrixts. Перед созданием таблицы необходимо сначала создать расширение в базе данных с использованием соответствующего движка хранения.
Примечание!
matrixtsрасширяется на уровне базы данных и может быть создано один раз в базе данных без повторного создания.CREATE EXTENSION matrixts;При создании таблиц используйте
USING MARS2для указания движка хранения. Базовая структура создания выглядит следующим образом:CREATE TABLE mars2( time timestamp with time zone, tag_id int, i4 int4, i8 int8 ) USING MARS2;Приведём далее примеры, связанные со сценариями временных данных транспортных средств, для более детального объяснения.
CREATE TABLE vehicle_basic_data_mars2( daq_time timestamp encoding (minmax), vin varchar(32) COLLATE "C" encoding (minmax), lng float encoding (minmax), lat float encoding (minmax), speed float , license_template varchar(16) , flag integer ) USING MARS2 WITH (compresstype=zstd, compresslevel=3) DISTRIBUTED BY (vin) PARTITION BY RANGE (daq_time) ( START ('2022-07-01 00:00:00') INCLUSIVE END ('2022-08-01 00:00:00') EXCLUSIVE EVERY (INTERVAL '1 day') ,default partition others);После успешного создания таблицы MARS2 необходимо создать дополнительный индекс типа
mars2_btree, чтобы обеспечить корректное чтение и запись. Цель использования сортировки индекса — разместить физически близко данные с одинаковыми или схожими характеристиками, чтобы сократить количество операций ввода-вывода и повысить эффективность запросов. Поэтому выбор ключей сортировки должен соответствовать основным характеристикам бизнес-запросов. Например, если требуется одиночный запрос по устройству, то ключом сортировки является номер устройства (vin) в сценарии временных данных. Если требуется детальный запрос, агрегация или запрос по нескольким устройствам в определённый период времени, то ключом сортировки является номер устройства и временная метка (vin, daq_time).CREATE INDEX idx_mars2 ON vehicle_basic_data_mars2 USING mars2_btree(vin, daq_time) WITH(uniquemode = true);
На основе конкретных параметров и операторов из приведённого выше примера таблицы сформулируем рекомендации по лучшим практикам:
> ***Примечание!***
Мы надеемся, что вы будете проектировать решения, опираясь на наши предложения, но не рекомендуем слепо их копировать. Сценарии временных данных постоянно меняются, и анализ конкретных ситуаций остаётся крайне необходимым.
1. **Выбор ключа сортировки**
При проектировании таблицы необходимо тщательно выбирать ключ сортировки. Мы рекомендуем использовать идентификатор субъекта данных. Если присутствует временная метка, её можно добавить к идентификатору в качестве составного ключа сортировки. В примере это `(vin, daq_time)`.
Ключ сортировки является частью индекса, поэтому такие запросы могут напрямую локализовать место хранения через индекс, избегая полного сканирования таблицы. Это позволяет оптимизировать запросы по субъекту данных и хранить данные в порядке, соответствующем идентификатору субъекта.
Данные одного субъекта хранятся непрерывно, что снижает количество операций ввода-вывода и ускоряет выполнение запросов.
2. **encoding (minmax)**
Этот параметр используется для повышения производительности вычислений.
Во-первых, рекомендуется добавить опцию `minmax` ко всем ключам сортировки, поскольку ключи сортировки упорядочены, и фильтрация по minmax эффективно сокращает объём сканируемых данных при полном сканировании таблицы.
Во-вторых, если вы планируете выполнять агрегирующие запросы (min, max, avg, sum, count и т.п.) по этому полю или использовать его в условиях фильтрации `WHERE`, то также необходимо добавить эту опцию.
Добавление этой опции должно определяться бизнес-сценарием. Не добавляйте её ко всем полям без анализа — это приведёт к значительному увеличению нагрузки на CPU и дисковый I/O при записи данных.
3. **COLLATE "C"**
Эта опция добавляется только в поле кодировки устройства и не требуется для других полей. Она повышает скорость сортировки и поиска для текстовых типов данных.
4. **USING MARS2**
Использует фиксированный метод записи для таблиц MARS2. Нельзя изменять содержимое или регистр ключевых слов.
5. **WITH (compresstype=zstd, compresslevel=3)**
Задаёт алгоритм сжатия как `zstd`. Также поддерживаются алгоритмы `rle_type`, `zlib`, `lz4`. Рекомендуется использовать `zstd` с уровнем сжатия 3. В данном примере используется общий алгоритм сжатия; для настройки пользовательских схем сжатия см. раздел "2. Использование сжатия" ниже.
6. **DISTRIBUTED BY (vin)**
Используйте этот оператор для выбора ключа распределения.
Ключи распределения определяют, как данные распределяются между узлами (сегментами), что существенно влияет на производительность. Основные критерии выбора:
(1) Избегайте дисбаланса данных — стремитесь к равномерному распределению, чтобы запросы могли выполняться максимально параллельно;
(2) При соединении нескольких таблиц старайтесь, чтобы ключи соединения совпадали с ключами распределения на этапе проектирования, чтобы избежать перемещения данных.
В примере мы используем поле кодировки устройства в качестве ключа распределения, что позволяет эффективно запрашивать данные одного устройства и выполнять связанные вычисления, избегая потерь производительности из-за перераспределения данных между узлами.
7. **PARTITION BY RANGE (daq_time)**
Задаёт ключ партиционирования для партиционированной таблицы.
Рекомендуется выбирать время в качестве ключа партиционирования. Во-первых, это позволяет автоматически удалять старые данные с помощью [автоматического управления партициями](/ru/doc/5.0/datamodel/apm). Во-вторых, позволяет использовать возможность отсечения партиций для фильтрации ненужных временных интервалов.
Рекомендуется поддерживать размер партиции в пределах от одного до десяти миллионов строк. Слишком маленькие партиции приведут к избыточному потреблению памяти, слишком большие — затруднят фильтрацию и удаление данных.
В примере мы используем время сбора данных устройств в качестве ключа партиционирования. Часто запросы фильтруют данные по времени сбора. Например, при запросе данных за день необходимо добавить условие `WHERE daq_time >= CURRENT_DATE - INTERVAL '1 day'`, что позволит базе данных быстро определить, в какой подтаблице находятся нужные данные, и быстро их извлечь.
8. **( START ('2022-07-01 00:00:00') INCLUSIVE
END ('2022-08-01 00:00:00') EXCLUSIVE
EVERY (INTERVAL '1 days')
, default partition others);**
Этот SQL-запрос создаёт подтаблицы, начинающиеся с 00:00:00 1 июля 2022 года и заканчивающиеся до 00:00:00 1 августа 2022 года, используя ключевые слова `INCLUSIVE` и `EXCLUSIVE`.
9. **EVERY (INTERVAL '1 day')**
Временной интервал подтаблицы составляет 1 день. Помимо дня, можно использовать час, месяц, год и т.д., выбирая интервал в зависимости от объёма данных. Например, если сервер получает от одного до десяти миллионов записей в день, то интервал в 1 день — оптимальный выбор. Если объём данных за день составляет лишь десятки или сотни тысяч записей, лучше выбрать интервал в 1 месяц. Если же данные за день — лишь десятки тысяч, то можно создавать подтаблицы с интервалом в 1 год.
10. **default partition others**
Обозначает партицию по умолчанию. Если для данных не найдена соответствующая подтаблица в других партициях, они будут сохранены в этой партиции.
> ***Примечание!***
Когда данные от одного и того же устройства в один и тот же момент времени поступают пакетами, MARS2 может объединять данные с одинаковым значением `vin` и одинаковой временной меткой `daq_time`. Эта функция объединения требует ручного указания параметра `uniquemode=true` при создании индекса, поскольку его значение по умолчанию — `false`. Например, если указано `uniquemode=true`, устройство 'A01' отправит 3 записи на '2022-01-01 00:00:00', и в итоге две предыдущие записи будут перезаписаны последней, оставив только одну. Если же используется значение по умолчанию `uniquemode=false`, то все три записи от устройства 'A01' на '2022-01-01 00:00:00' будут сохранены без какого-либо объединения.
## 2. Таблицы HEAP / AO
### 2.1 Таблица HEAP
Таблица HEAP — это движок хранения по умолчанию в YMatrix. Если при создании таблицы не указан движок хранения, все созданные таблицы будут таблицами HEAP.CREATE TABLE disk_heap( time timestamp with time zone, tag_id int, read float, write float ) DISTRIBUTED BY (tag_id);
### 2.2 Таблица AO
#### 2.2.1 Таблица AORO
Таблица AORO — это таблица AO с хранением по строкам. Хранение по строкам — это стандартный метод хранения для таблиц AO.
AORO поддерживает сжатие на уровне таблицы, но не на уровне столбцов.
CREATE TABLE disk_aoro( time timestamp with time zone, tag_id int, read float, write float ) WITH(appendonly=true,orientation=row,compresstype=zlib,compresslevel=4) DISTRIBUTED BY (tag_id);
#### 2.2.2 Таблица AOCO
Таблица AOCO — это таблица AO с хранением по столбцам.
AOCO поддерживает как сжатие на уровне таблицы, так и на уровне столбцов.
CREATE TABLE disk_aoco( time timestamp with time zone, tag_id int ENCODING(compresstype=zlib,compresslevel=5), read float, write float ) WITH(appendonly=true,orientation=column,compresstype=zlib,compresslevel=6) DISTRIBUTED BY (tag_id);
### 2.3 Рекомендации по выбору
Лучшие практики выбора между HEAP и AO таблицами:
- Используйте движок хранения HEAP для таблиц и партиций, где часто выполняются операции обновления, удаления и одиночные вставки.
- Используйте движок хранения HEAP для таблиц и партиций, которые подвергаются параллельным операциям обновления, удаления и вставки.
- Используйте движок хранения AO для таблиц и партиций, которые редко обновляются после первоначальной загрузки и далее обрабатываются только пакетными операциями. **Никогда не выполняйте одиночные операции обновления, удаления или вставки в таблицах AO. Параллельные пакетные вставки допускаются, но параллельные пакетные обновления или удаления строго запрещены.**
- Пространство, занимаемое обновлёнными и удалёнными строками в таблицах AO, не перераспределяется и не переиспользуется так эффективно, как в таблицах HEAP. Поэтому движок AO не подходит для часто обновляемых таблиц. Его цель — эффективная работа с большими таблицами, загружаемыми единожды, редко обновляемыми и часто используемыми для аналитических запросов.