Использование CONNECT — Разбиение и фрагментация

CONNECT поддерживает спецификацию разбиения MySQL/MariaDB. Она реализуется аналогично MyISAM или InnoDB с использованием движка PARTITION, который должен быть включен для работы. Такое разбиение иногда называют «горизонтальным разбиением».

Разбиение позволяет распределять части отдельных таблиц по файловой системе в соответствии с правилами, которые вы можете задать по мере необходимости. Фактически, разные части таблицы хранятся как отдельные таблицы в разных местах. Выбранное пользователем правило, по которому выполняется разделение данных, известно как функция разбиения, которая в MariaDB может быть оператором модуля, простым сопоставлением со множеством диапазонов или списков значений, внутренней функцией хэширования или линейной функцией хэширования.

CONNECT продвигает эту концепцию, предоставляя два типа разбиения:

1. Файловое разбиение. Каждая часть хранится в отдельном файле, как в нескольких таблицах.
2. Таблица разбиения. Каждая часть хранится в отдельной таблице, как в таблицах типа TBL.

Проблемы с движком разбиения

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

1. Конкретные для движка параметры столбцов и индексов не распознаются и вызывают синтаксическую ошибку при создании таблицы. Решением является создание таблицы в два этапа: оператор CREATE TABLE, за которым следует оператор ALTER TABLE.
2. Строка подключения, указанная для таблицы, теряется движком разбиения. Решением является указание строки подключения в option_list.
3. Ошибка MySQL upstream #71095. В случае разбиения по столбцам со списком иногда возникает ложное утверждение «невозможное условие where». Это приводит к возврату неправильного пустого результата, когда он не должен быть пустым. Нет решения, но, надеюсь, эта ошибка будет исправлена.

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

Файловое разбиение

Файловое разбиение применяется к файловым типам таблиц CONNECT. Как и в случае с несколькими таблицами, физические данные хранятся в нескольких файлах вместо одного. Различия с несколькими таблицами следующие:

1. Данные распределяются между различными файлами в соответствии с правилом разбиения.
2. В отличие от нескольких таблиц, разбиение таблиц не является только для чтения.
3. В отличие от нескольких таблиц, разбиение таблиц может быть индексируемым.
4. Имена файлов генерируются из имен партиций.
5. Оптимизация запросов автоматически выполняется движком разбиения.

Имена файлов таблиц генерируются по-разному в зависимости от того, является таблица внутренней или внешней. Для внутренних таблиц, для которых имя файла не указано, имена файлов партиций:

Data file name: table_name#P#partition_name.table_file_type
Index file name: table_name#P#partition_name.index_file_type

Например, для таблицы:

CREATE TABLE t1 (
id INT KEY NOT NULL,
msg VARCHAR(32))
ENGINE=CONNECT TABLE_TYPE=FIX
partition by range(id) (
partition first values less than(10),
partition middle values less than(50),
partition last values less than(MAXVALUE));

CONNECT сгенерирует в текущем каталоге данных файлы:

| t1#P#first.fix
| t1#P#first.fnx
| t1#P#middle.fix
| t1#P#middle.fnx
| t1#P#last.fix
| t1#P#last.fnx

Это аналогично тому, что делает движок разбиения для других движков — разбиение внутренних таблиц CONNECT ведет себя так же, как и разбиение таблиц других движков. Только формат данных отличается.

Примечание: Если используется подразбиение, имена файлов внутренних таблиц и индексных файлов имеют вид:

| table_name#P#partition_name#SP#subpartition_name.type
| table_name#P#partition_name#SP#subpartition_name.index_type

Внешние таблицы

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

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

E:\Data\part1.txt
E:\Data\part2.txt
E:\Data\part3.txt

Это можно сделать, создав таблицу, такую как:

create table t2 (
id int not null,
msg varchar(32),
index XID(id))
engine=connect table_type=FIX file_name='E:/Data/part%s.txt'
partition by range(id) (
partition `1` values less than(10),
partition `2` values less than(50),
partition `3` values less than(MAXVALUE));

Правило состоит в том, что для каждой партиции имя соответствующего файла генерируется внутри заменой части «%s» в заданном значении параметра FILE _ NAME именем партиции.

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

В приведенном выше примере нет проблем, если таблица изначально пустая, но если она не пустая, могут возникнуть серьезные проблемы, если начальное распределение не соответствует распределению таблицы. Предположим, что строка, в которой «id» имеет значение 12, изначально находилась в файле part1.txt, она будет отображаться при выборе всей таблицы, но если вы запросите:

select * from t2 where id = 12;

Результат будет содержать 0 строк. Это происходит потому, что в соответствии с функцией разбиения оптимизация запросов будет искать только внутри второй партиции и пропустит строку, которая находится в неправильной партиции.

Один из способов проверки неправильного распределения, например, сравнить результаты запросов, таких как:

SELECT partition_name, table_rows FROM
information_schema.partitions WHERE table_name = 't2';

И

SELECT CASE WHEN id < 10 THEN 1 WHEN id < 50 THEN 2 ELSE 3 END
AS pn, COUNT(*) FROM part3 GROUP BY pn;

Если они совпадают, распределение может быть правильным, хотя это не доказывает этого. Однако, если они не совпадают, распределение, безусловно, неправильное.

Разбиение по специальному столбцу

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

tmp/boston.txt
tmp/chicago.txt
tmp/atlanta.txt

Каждый из них содержит данные того же типа:

ID: int
First_name: varchar(16)
Last_name: varchar(30)
Birth: date
Hired: date
Job: char(10)
Salary: double(8,2)

На них можно создать несколько таблиц, например, следующим образом:

create table mulemp (
id int NOT NULL,
first_name varchar(16) NOT NULL,
last_name varchar(30) NOT NULL,
birth date NOT NULL date_format='DD/MM/YYYY',
hired date NOT NULL date_format='DD/MM/YYYY',
job char(10) NOT NULL,
salary double(8,2) NOT NULL
) engine=CONNECT table_type=FIX file_name='tmp/*.txt' multiple=1;

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

Однако есть решение. Добавить в таблицу специальный столбец, который будет использоваться функцией разбиения. Например, создание новой таблицы можно сделать следующим образом:

create table partemp (
id int NOT NULL,
first_name varchar(16) NOT NULL,
last_name varchar(30) NOT NULL,
birth date NOT NULL date_format='DD/MM/YYYY',
hired date NOT NULL date_format='DD/MM/YYYY',
job char(16) NOT NULL,
salary double(10,2) NOT NULL,
city char(12) default 'boston' special=PARTID,
index XID(id)
) engine=CONNECT table_type=FIX file_name='E:/Data/Test/%s.txt';
alter table partemp
partition by list columns(city) (
partition `atlanta` values in('atlanta'),
partition `boston` values in('boston'),
partition `chicago` values in('chicago'));

Примечание 1: мы должны сделать это в два этапа из-за параметров столбцов CONNECT.

Примечание 2: специальный столбец PARTID возвращает имя партиции, в которой расположена строка.

Примечание 3: здесь вместо этого можно было использовать специальный столбец FNAME, поскольку имя файла указано как имя партиции.

Это может показаться довольно глупым, потому что, например, строка будет в партиции boston, если она принадлежит партиции boston! Однако это работает, потому что движок разбиения ничего не знает о специальных столбцах и ведет себя так, как будто столбец city был реальным столбцом.

Что произойдет, если мы заполним его следующим образом?

insert into partemp(id,first_name,last_name,birth,hired,job,salary) values
(1205,'Harry','Cover','1982-10-07','2010-09-21','MANAGEMENT',125000.00);
insert into partemp values
(1524,'Jim','Beams','1985-06-18','2012-07-25','SALES',52000.00,'chicago'),
(1431,'Johnny','Walker','1988-03-12','2012-08-09','RESEARCH',46521.87,'boston'),
(1864,'Jack','Daniels','1991-12-01','2013-02-16','DEVELOPMENT',63540.50,'atlanta');

Значение, заданное для столбца city (явное или по умолчанию), будет использоваться движком разбиения для определения партиции, в которую необходимо вставить строки. Оно будет проигнорировано CONNECT (специальному столбцу нельзя присвоить значение), но позже вернет соответствующее значение. Например:

select city, first_name, job from partemp where id in (1524,1431);

Этот запрос возвращает:

create table xt1 (
id int not null,
msg varchar(32))
engine=myisam;

create table xt2 (
id int not null,
msg varchar(32)); /* engine=innoDB */

create table xt3 (
id int not null,
msg varchar(32))
engine=connect table_type=CSV;

create table t3 (
id int not null,
msg varchar(32))
engine=connect table_type=PROXY tabname='xt%s'
partition by range columns(id) (
partition `1` values less than(10),
partition `2` values less than(50),
partition `3` values less than(MAXVALUE));

insert into t3 values
(4, 'four'),(7,'seven'),(10,'ten'),(40,'forty'),
(60,'sixty'),(81,'eighty one'),(72,'seventy two'),
(11,'eleven'),(1,'one'),(35,'thirty five'),(8,'eight');

select partition_name, table_rows from
information_schema.partitions where table_name = 't3';

Оптимизация запросов, конечно, автоматическая, например:

explain partitions select * from t3 where id = 81;

Этот запрос отвечает:

При выполнении этого запроса будет использоваться только подтаблица xt3.

Индексирование с разбиением таблиц

Использование типа таблицы PROXY кажется естественным. Однако в текущей версии проблема заключается в том, что таблицы PROXY (и ODBC) не индексируются. Вот почему, если вам нужна индексируемая таблица, вы должны использовать тип таблицы MYSQL. Выражение CREATE TABLE будет почти таким же:

create table t4 (
id int key not null,
msg varchar(32))
engine=connect table_type=MYSQL tabname='xt%s'
partition by range columns(id) (
partition `1` values less than(10),
partition `2` values less than(50),
partition `3` values less than(MAXVALUE));

Столбец id объявлен как ключ, а тип таблицы — теперь MYSQL. Это делает доступ к подтаблицам, обращающимся к серверу MariaDB, как к таблицам MYSQL. Обратите внимание, что это изменяет только способ доступа к подтаблицам CONNECT.

Однако индексация заставляет разделяемую таблицу использовать «удаленный индекс» так же, как и таблицы FEDERATED. Это означает, что при отправке запроса для получения данных таблицы к запросу будет добавлен оператор WHERE. Например, предположим, что вы запрашиваете:

select * from t4 where id = 7;

Запрос, отправленный на сервер, будет:

SELECT `id`, `msg` FROM `xt1` WHERE `id` = 7

В случае такого запроса это не сильно изменится, потому что оператор WHERE мог быть добавлен функцией cond_push, но это делает разницу в случае объединений. Главное, чтобы понимать, что реальная индексация выполняется вызываемой таблицей, и поэтому она должна быть индексирована.

Это также означает, что индексы таблиц xt1, xt2 и xt3 должны быть созданы отдельно, поскольку создание таблицы t2 как индексированной не создает индексов в подтаблицах.

Разбиение данных с помощью разбиения таблиц

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

create table rt1 (id int key not null, msg varchar(32))
engine=federated connection='mysql://root@host1/test/sales';

create table rt2 (id int key not null, msg varchar(32))
engine=federated connection='mysql://root@host2/test/sales';

create table rt3 (id int key not null, msg varchar(32))
engine=federated connection='mysql://root@host3/test/sales';

Создание таблицы разбиения, обращающейся ко всем этим таблицам, будет почти таким же, как мы сделали с таблицей t4:

create table t5 (
id int key not null,
msg varchar(32))
engine=connect table_type=MYSQL tabname='rt%s'
partition by range columns(id) (
partition `1` values less than(10),
partition `2` values less than(50),
partition `3` values less than(MAXVALUE));

.

Единственное различие заключается в том, что опция tabname теперь ссылается на таблицы rt1, rt2 и rt3. Однако, даже если это работает, это не лучший способ. Это связано с тем, что доступ к таблице через API MySQL выполняется дважды для каждой таблицы. Сначала CONNECT для доступа к таблице FEDERATED на локальном сервере, затем — второй раз движком FEDERATED для доступа к удалённой таблице.

Так как тип таблицы CONNECT MYSQL используется в любом случае, лучше использовать его для непосредственного доступа к удалённым таблицам. Действительно, имена разбиений также могут использоваться для изменения URL-адресов подключений. Например, в приведённом выше случае таблица разбиения может быть создана как:

create table t6 (
id int key not null,
msg varchar(32))
engine=connect table_type=MYSQL
option_list='connect=mysql://root@host%s/test/sales'
partition by range columns(id) (
partition `1` values less than(10),
partition `2` values less than(50),
partition `3` values less than(MAXVALUE));

Здесь можно отметить несколько моментов:

1. Как мы уже видели ранее, движок разбиения в настоящее время теряет строку подключения. Вот почему она была указана как «connect» в списке опций.
2. Для каждой подтаблицы разбиения часть строки подключения «%s» заменена именем разбиения.
3. Больше не требуется определять таблицы rt1, rt2 и rt3 (хотя это не навредит), и движок FEDERATED больше не используется для доступа к удалённым таблицам.

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

For rt1: connection='mysql://root:tinono@127.0.0.1:3307/test/xt1'
For rt2: connection='mysql://foo:foopass@denver/dbemp/xt2'
For rt3: connection='mysql://root@huston :5505/test/tabx'

Есть два решения. Первое — использовать части строки подключения для дифференциации как имён разбиений:

create table t7 (
id int key not null,
msg varchar(32))
engine=connect table_type=MYSQL
option_list='connect=mysql://%s'
partition by range columns(id) (
partition `root:tinono@127.0.0.1:3307/test/xt1` values less than(10),
partition `foo:foopass@denver/dbemp/xt2` values less than(50),
partition `root@huston :5505/test/tabx` values less than(MAXVALUE));

Второе решение, позволяющее избежать слишком сложных имён разбиений, заключается в создании федеративных серверов для доступа к удалённым таблицам (если они ещё не существуют, иначе просто используйте их). Например, первый может быть:

create server `server_one` foreign data wrapper 'mysql'
options
(host '127.0.0.1',
database 'test',
user 'root',
password 'tinono',
port 3307);

Аналогично, «server_two» и «server_three» будут созданы, и конечная таблица разбиения будет создана как:

create table t8 (
id int key not null,
msg varchar(32))
engine=connect table_type=MYSQL
option_list='connect=server_%s'
partition by range columns(id) (
partition `one/xt1` values less than(10),
partition `two/xt2` values less than(50),
partition `three/tabx` values less than(MAXVALUE));

Было бы ещё проще, если бы все удалённые таблицы имели одинаковое имя в удалённых базах данных, например, если бы они все именовались xt1, строка подключения могла быть установлена как «server_%s/xt1», а имена разбиений — просто «one», «two» и «three».

Разбиение по специальному столбцу

Техника, которую мы видели выше с разбиением файлов, также доступна с разбиением таблиц. Компании, желающие использовать как одну таблицу данные, распределённые по серверам филиалов компании, могут, как мы видели, добавить в определение таблицы специальный столбец. Например:

create table t9 (
id int not null,
msg varchar(32),
branch char(16) default 'main' special=PARTID,
index XID(id))
engine=connect table_type=MYSQL
option_list='connect=server_%s/sales'
partition by range columns(id) (
partition `main` values in('main'),
partition `east` values in('east'),
partition `west` values in('west'));

Этот пример предполагает, что были созданы федеративные серверы с именами «server_main», «server_east» и «server_west» и что все удалённые таблицы называются «sales». Также обратите внимание, что в этом примере столбец id больше не является ключом.

Текущие ограничения разбиения

Поскольку движок разбиения был написан до добавления некоторых других движков в MariaDB, его работа иногда несовместима с этими движками, особенно с CONNECT.

Операция UPDATE

С образцовыми таблицами выше вы можете выполнить операции UPDATE, такие как:

update t2 set msg = 'quatre' where id = 4;

Всё работает безупречно и принимается CONNECT. Однако давайте рассмотрим оператор:

update t2 set id = 41 where msg = 'four';

Этот оператор не принимается CONNECT. Причина в том, что столбец id, являющийся частью функции разбиения, изменение его значения может потребовать перемещения изменённой строки в другое разбиение. Способ, которым это делает движок разбиения, — это удаление старой строки и повторная вставка новой, изменённой. Однако это делается таким образом, который в настоящее время несовместим с CONNECT (помните, что CONNECT поддерживает UPDATE особым образом, особенно для типа таблицы MYSQL). Это ограничение может быть временным. В то же время решением является ручное выполнение вышеописанного,

Удаление строки для изменения и вставка изменённой строки:

delete from t2 where id = 4;
insert into t2 values(41, 'four');

Операция ALTER TABLE

Для всех внешних таблиц CONNECT операция ALTER TABLE не вносит никаких изменений в данные таблицы. Вот почему ALTER TABLE не следует использовать, в частности, для изменения определения разбиения, за исключением, конечно, исправления неправильного определения. Обратите внимание, что использование ALTER TABLE для создания таблицы разбиения в два этапа, потому что параметры столбца будут утеряны, допустимо, поскольку это относится к таблице, которая ещё не разделена.

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

Специальный столбец ROWID

Так как каждое разбиение обрабатывается отдельно как одна таблица, специальный столбец ROWID возвращает ранг строки в своём разбиении, а не во всей таблице. Это означает, что для таблиц разбиения ROWID и ROWNUM эквивалентны.