Лучшие практики проектирования таблиц

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

MARS3
MARS2
HEAP
AO

1 Таблицы MARS3

Таблицы MARS3 зависят от расширения matrixts. Перед созданием таблицы необходимо сначала установить это расширение в базе данных, где будет использоваться движок хранения MARS3.

Примечание!
Расширение matrixts имеет уровень базы данных. Создавайте его один раз на базу данных; не создавайте его повторно.

=# CREATE EXTENSION matrixts;

Используйте USING MARS3 для указания движка хранения при создании таблицы и ORDER BY для определения ключа сортировки. Ниже приведён базовый пример:

=# CREATE TABLE mars3(
    time timestamp with time zone,
    tag_id int,
    i4 int4,
    i8 int8
)
USING MARS3 ORDER BY (tag_id, time);

Рассмотрим более подробный пример временного ряда телеметрии транспортных средств для дальнейшего объяснения.

=# CREATE TABLE vehicle_basic_data_mars3(
  daq_time timestamp ,
  vin varchar(32)  COLLATE "C" ,
  lng float ,
  lat float ,
  speed float ,
  license_template varchar(16) ,
  flag integer
)
USING MARS3
WITH (compresstype=zstd, compresslevel=3,compress_threshold=1200,
        mars3options='rowstore_size=64')
DISTRIBUTED BY (vin)
ORDER BY (vin, daq_time)
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);

После успешного создания таблицы MARS3 вы можете создать индекс mars3_brin на ключе сортировки. Индекс mars3_brin — это разреженный индекс с минимальным влиянием на объём дискового пространства и производительность вставки. В сочетании с упорядоченной природой ключа сортировки он обеспечивает оптимальную эффективность запросов по ключу сортировки.

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

=# CREATE INDEX idx_mars3 ON vehicle_basic_data_mars3 USING mars3_brin(vin, daq_time);

На основе параметров и операторов из приведённого выше примера следуют рекомендации по лучшим практикам:

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

Выбор ключа сортировки

При проектировании таблицы тщательно выбирайте ключ сортировки. Цель ключа сортировки — физически размещать данные с похожими временными измерениями или атрибутами рядом друг с другом, снижая количество операций поиска ввода-вывода и повышая производительность запросов. Следовательно, ключ сортировки должен соответствовать основным шаблонам ваших запросов. Для точечных запросов по одному устройству используйте идентификатор устройства (например, vin). Для детализированных, агрегированных или многопользовательских запросов в пределах временных диапазонов используйте как идентификатор устройства, так и временную метку (например, (vin, daq_time)).

Мы рекомендуем использовать идентификатор основной сущности в качестве ключа сортировки. Если присутствует временная метка, включайте её после идентификатора. В примере ключ сортировки — (vin, daq_time).
COLLATE "C"

Применяйте эту опцию только к полям идентификатора устройства; не применяйте её к другим полям. Это улучшает сортировку и производительность запросов для столбцов текстового типа.
USING MARS3

Это фиксированный синтаксис для таблиц MARS3 и не должен изменяться.
WITH (compresstype=zstd, compresslevel=3, mars3options='compress_threshold=1200, rowstore_size=64')

Мы рекомендуем использовать сжатие zstd уровня 3. В данном примере используется универсальное сжатие. Для настройки пользовательских параметров сжатия см. Использование сжатия.

В этой таблице размер Run L0 составляет 64 МБ, а порог сжатия — 1200.
DISTRIBUTED BY (vin)

Используйте этот оператор для указания ключа распределения.
Ключ распределения определяет, как данные распределяются между сегментами, и существенно влияет на производительность. Основные соображения:
- Избегайте перекоса данных, чтобы обеспечить равномерное распределение и максимизировать параллелизм.
- Для соединений нескольких таблиц заранее согласуйте ключ соединения с ключом распределения, чтобы избежать перемещения данных.
В примере в качестве ключа распределения используется vin, чтобы все данные одного устройства находились на одном сегменте, что обеспечивает эффективные локальные вычисления и исключает межузловое перераспределение данных.
PARTITION BY RANGE (daq_time)

Укажите ключ партиционирования для партиционированных таблиц.
Мы рекомендуем использовать время в качестве ключа партиционирования. Это позволяет автоматически удалять устаревшие данные с помощью Автоматического управления партициями и использовать отсечение партиций для фильтрации нерелевантных временных партиций.

Целевое количество строк в партиции — от одного до десяти миллионов. Слишком много мелких партиций потребляет избыточную память; слишком мало партиций снижает эффективность отсечения и усложняет управление жизненным циклом данных.

В примере ключом партиционирования является daq_time. Большинство запросов фильтруют по времени сбора данных. Например, добавление условия вида WHERE daq_time >= CURRENT_DATE - INTERVAL '1 day' позволяет базе данных быстро определить соответствующую партицию и эффективно извлечь данные.
( START ('2022-07-01 00:00:00') INCLUSIVE END ('2022-08-01 00:00:00') EXCLUSIVE EVERY (INTERVAL '1 days') ,DEFAULT PARTITION OTHERS);

Этот SQL-запрос указывает, что субпартиции будут создаваться с 2022-07-01 00:00:00 до момента, непосредственно предшествующего 2022-08-01 00:00:00, с использованием оператора START...END и ключевого слова INCLUSIVE、EXCLUSIVE.
EVERY (INTERVAL '1 day')

Интервалы субпартиций установлены на 1天. Вместо day вы можете использовать hour, month или year, в зависимости от объёма данных. Например:
- Если ваша система получает 1–10 млн записей в день, используйте 1 day.
- Если дневной объём составляет десятки или сотни тысяч, подходит 1 month.
- Если дневной объём менее 10 тыс., достаточно 1 year.
DEFAULT PARTITION OTHERS

Определяет партицию по умолчанию. Данные с временными метками, не попадающими ни в один определённый диапазон, хранятся здесь.
Используйте VACUUM/VACUUM FULL для оптимальной производительности запросов

Выполнение VACUUM/VACUUM FULL преобразует неупорядоченные строки в упорядоченные столбцовые блоки.
VACUUM FULL выполняет то же преобразование и дополнительно объединяет несколько блоков в один, обеспечивая ещё лучшую производительность запросов. Однако VACUUM FULL требует исключительной блокировки таблицы, поэтому не может выполняться одновременно с другими операциями на той же таблице.

Когда выполнять VACUUM/VACUUM FULL:
- При непрерывной инжекции данных очистка не требуется.
- Когда данные стабилизируются, выполните очистку перед запуском запросов.
- После масштабных изменений данных (пакетные обновления/вставки) выполните очистку после завершения операции.
Включите режим уникальности для пакетной инжекции данных

Когда данные от одного и того же устройства приходят пакетами с одинаковой временной меткой, MARS3 может объединять дублирующиеся записи (одинаковые daq_time и vin).

Эта функция должна быть явно включена через uniquemode=true при создании таблицы, так как по умолчанию она отключена (false). Например:
- При uniquemode=true три записи от устройства 'A01' с меткой '2022-01-01 00:00:00' будут объединены, оставив только последнюю.
- При uniquemode=false (по умолчанию) все три записи сохраняются.
Примечание!
Чтобы включить режим уникальности, первый столбец в предложении ORDER BY должен иметь ограничение NOT NULL. Режим уникальности MARS3 не поддерживает удаление.

Пример DDL с включённым режимом уникальности:
```
 =# CREATE TABLE vehicle_basic_data_mars3_um(
   daq_time timestamp ,
   vin varchar(32)  COLLATE "C" NOT NULL,
   lng float ,
   lat float ,
   speed float ,
   license_template varchar(16) ,
   flag integer
 )
 USING MARS3
 WITH (compresstype=zstd, compresslevel=3,compress_threshold=1200,uniquemode=true,
         mars3options='rowstore_size=64')
 DISTRIBUTED BY (vin)
 ORDER BY (vin, daq_time)
 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);
```

2 Таблицы MARS2

Таблицы MARS2 зависят от расширения matrixts. Установите расширение перед созданием таблиц.

Примечание!
Расширение matrixts имеет уровень базы данных. Установите его один раз на базу данных.

=# CREATE EXTENSION matrixts;

Используйте USING MARS2 для указания движка хранения 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, чтобы обеспечить нормальную работу чтения и записи.

Индекс обеспечивает физическое упорядочивание данных, размещая схожие или связанные данные рядом друг с другом, что снижает I/O и ускоряет запросы. Следовательно, ключ сортировки должен отражать типичные шаблоны запросов. Для запросов по одному устройству используйте идентификатор устройства (vin). Для запросов по временным диапазонам или нескольким устройствам используйте (vin, daq_time).

=# CREATE INDEX idx_mars2 ON vehicle_basic_data_mars2 
USING mars2_btree(vin, daq_time);

Лучшие практики на основе примера:

Примечание!
Следуйте нашим рекомендациям, но не копируйте их слепо. Проанализируйте ваш конкретный сценарий.

Выбор ключа сортировки

Выбирайте ключ сортировки при проектировании таблицы. Мы рекомендуем использовать идентификатор основной сущности. Если доступна временная метка, включайте её после идентификатора. В примере ключ сортировки — (vin, daq_time).

Поскольку ключ сортировки является частью индекса, запросы к нему могут быть эффективно локализованы без полного сканирования таблицы. Это оптимизирует запросы, начинающиеся с сущности, обеспечивая соседнее хранение связанных данных, снижая I/O и ускоряя доступ.
encoding (minmax)

Эта опция повышает вычислительную эффективность.
- Применяйте кодирование minmax ко всем столбцам ключа сортировки. Поскольку они упорядочены, фильтры minmax эффективны при полном сканировании.
- Используйте minmax для столбцов, участвующих в фильтрации условий min, max, avg, sum, count или WHERE.
Применяйте это с осторожностью. Чрезмерное использование увеличивает нагрузку на CPU и I/O при инжекции данных.
COLLATE "C"

То же, что и в примере MARS3.
USING MARS2

Фиксированный синтаксис для таблиц MARS2; не изменяйте.
WITH (compresstype=zstd, compresslevel=3)

Используйте сжатие zstd уровня 3. Подробнее о настройках см. Использование сжатия.
DISTRIBUTED BY (vin)

То же, что и в примере MARS3.
PARTITION BY RANGE (daq_time)

То же, что и в примере MARS3.
( START ('2022-07-01 00:00:00') INCLUSIVE END ('2022-08-01 00:00:00') EXCLUSIVE EVERY (INTERVAL '1 days'),default partition others);

То же, что и в примере MARS3.
EVERY (INTERVAL '1 day')

То же, что и в примере MARS3.
Включите режим уникальности для пакетных данных

MARS2 поддерживает объединение дублирующих записей (одинаковые daq_time и vin) при пакетной инжекции данных.

Включите эту функцию через uniquemode=true при создании индекса, так как по умолчанию она отключена (false). Например:
- При uniquemode=true три записи от устройства 'A01' с меткой '2022-01-01 00:00:00' объединяются в одну (последняя запись остаётся).
- При uniquemode=false (по умолчанию) все три записи сохраняются.
Пример DDL индекса с включённым режимом уникальности:
```
 =# CREATE INDEX idx_mars2_um ON vehicle_basic_data_mars2_um 
 USING mars2_btree(vin, daq_time) WITH(uniquemode = true);
```
Примечание!
Таблица MARS2 поддерживает только один индекс, который нельзя удалить после создания.

3 Таблицы HEAP / AO

3.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);

3.2 Таблицы AO

3.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);

3.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);

3.3 Выбор между HEAP и AO

Лучшие практики выбора между HEAP и AO:

Используйте движок хранения HEAP для таблиц или партиций, которые подвергаются частым обновлениям, удалениям или индивидуальным вставкам.
Используйте HEAP для таблиц или партиций, подвергающихся параллельным обновлениям, удалениям или вставкам.
Используйте движок хранения AO для таблиц или партиций, которые загружаются один раз и редко обновляются, с последующими пакетными вставками. Никогда не выполняйте одиночные операции UPDATE, DELETE или INSERT на таблицах AO.
Параллельные пакетные вставки разрешены на таблицах AO, но параллельные пакетные обновления или удаления не рекомендуются.
Пространство, занимаемое удалёнными или обновлёнными строками в таблицах AO, не освобождается так эффективно, как в таблицах HEAP. Следовательно, AO не подходит для часто обновляемых таблиц.
AO предназначен для больших таблиц, которые загружаются один раз, редко изменяются и часто запрашиваются для аналитической обработки.

← Предыдущая

Введение

Сжатие

Русский English 简体中文