В зависимости от различных характеристик движка хранения вы можете гибко создавать разные таблицы в соответствии с требованиями различных сценариев. Ниже приведены примеры.
Таблицы MARS3 зависят от расширения matrixts. Перед созданием таблицы необходимо сначала создать расширение в базе данных с использованием соответствующего движка хранения.
Примечание!
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)Используйте этот оператор для выбора ключа распределения.
Ключи распределения определяют, как данные распределяются между узлами (сегментами), что существенно влияет на производительность. Основные критерии выбора: (1) Старайтесь избегать перекоса данных, чтобы обеспечить равномерное распределение и максимально эффективное параллельное выполнение запросов; (2) При соединении нескольких таблиц старайтесь изначально согласовывать ключи соединения с ключами распределения, чтобы избежать перемещения данных.
В примере мы используем поле кода устройства в качестве ключа распределения, что позволяет эффективно запрашивать данные одного устройства и выполнять связанные вычисления, избегая потерь производительности из-за перераспределения данных между узлами.
PARTITION BY RANGE (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-запрос указывает, что вы создаете подпартиционированную таблицу с помощью оператора START...END и ключевых слов INCLUSIVE, EXCLUSIVE, начинающуюся с нулевой точки 2022-07-01 и заканчивающуюся нулевой точкой 2022-08-01.
EVERY (INTERVAL '1 day')Временной интервал подпартиций составляет 1 day. Помимо дня, можно использовать час, месяц, год и т.д., выбирая интервал в зависимости от объема данных. Например, если ваш сервер получает от одного до десяти миллионов записей в день, то использование интервала «1 день» — оптимальный выбор; если объем данных за день составляет лишь десятки или сотни тысяч, то подойдет интервал «1 месяц»; если же ежедневно поступает лишь десятки тысяч записей, то можно создавать подпартиции с интервалом «1 год».
DEFAULT PARTITION OTHERSОбозначает партицию по умолчанию. Если для данных не найдена соответствующая подтаблица в других партициях, они будут сохранены в этой партиции.
VACUUM/VACUUM FULL для достижения максимальной производительности запросовФункция выполнения VACUUM/VACUUM FULL: она преобразует неупорядоченные строки хранения в упорядоченные столбцовые блоки, а VACUUM FULL удаляет вышеуказанные VACUUM и объединяет несколько блоков в один, тем самым достигая экстремальной производительности запросов. Однако VACUUM FULL требует эксклюзивной блокировки таблицы и не может выполняться параллельно с другими операциями с этой таблицей.
Время выполнения VACUUM/VACUUM FULL: если вы непрерывно записываете данные в таблицу MARS3, вам не нужно беспокоиться о очистке. Когда данные в таблице стабилизируются, перед выполнением запросов необходимо провести очистку; если произошли изменения во всей базе данных или в определенной таблице, также рекомендуется выполнять очистку после завершения каждого изменения.
Когда данные с одного и того же временного момента от устройства поступают пакетами, MARS3 может объединять данные от одного и того же устройства (значение 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' останутся без изменений.
Примечание!
Если включен режим Unique, первое поле в предложении ORDER BY должно быть добавлено в ограничениеNOT NULLпри определении. В настоящее время режим Unique MARS3 не поддерживает удаление.
Если включен, оператор создания таблицы в примере выглядит следующим образом:
=# 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);Таблицы 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);
На основе конкретных параметров и операторов из приведенного выше примера таблицы сформулируем рекомендации по лучшим практикам:
Примечание!
Мы надеемся, что вы будете использовать наши предложения как основу для проектирования, но не рекомендуем слепо применять их. Сценарии временных данных постоянно меняются, и анализ конкретных ситуаций остается крайне важным.
При проектировании таблицы необходимо тщательно продумать выбор ключа сортировки. Рекомендуем выбирать идентификатор сущности данных. Если присутствует временная метка, её можно добавить к идентификатору в качестве ключа сортировки. В примере таблицы это (vin, daq_time).
Ключ сортировки является частью индекса, поэтому такие запросы могут быть локализованы непосредственно через индекс, избегая полного сканирования таблицы. Это оптимизирует запросы на основе сущности данных и позволяет хранить данные в порядке сущности.
Данные одной сущности хранятся последовательно, что снижает количество операций ввода-вывода и повышает скорость запросов.
Этот параметр используется для повышения вычислительной производительности.
Во-первых, рекомендуется добавить опцию minmax ко всем ключам сортировки, поскольку ключи сортировки упорядочены, и фильтрация minmax эффективно работает при сканировании всей таблицы.
Во-вторых, если вы планируете выполнять агрегирующие запросы (min, max, avg, sum, count и т.д.) по этому полю или применять фильтрацию по условию WHERE, необходимо добавить эту опцию.
Добавление этой опции должно определяться бизнес-сценарием. Не добавляйте её ко всем полям бездумно — это приведет к значительному увеличению нагрузки на CPU и дисковый ввод-вывод при записи данных.
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 выше.
DEFAULT PARTITION OTHERSТо же, что и в примере MARS3 выше.
Когда данные с одного и того же временного момента от устройства поступают пакетами, 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' останутся без изменений.
Если включен, оператор создания индекса в примере выглядит следующим образом:
=# CREATE INDEX idx_mars2_um ON vehicle_basic_data_mars2_um
USING mars2_btree(vin, daq_time) WITH(uniquemode = true);Примечание!
Таблица MARS2 поддерживает только один индекс, который нельзя удалить после создания.
Таблица HEAP — это движок хранения по умолчанию в YMatrix. Если при создании таблицы не указан движок хранения, все созданные таблицы будут таблицами HEAP.
=# CREATE TABLE disk_heap(
time timestamp with time zone,
tag_id int,
read float,
write float
)
DISTRIBUTED BY (tag_id);Таблица 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);Таблица 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);Рекомендации по выбору между таблицами HEAP и AO: