13.1.19 CREATE TABLESPACE Statement — MySQL 5.7

13.1.19 Оператор CREATE TABLESPACE

CREATE TABLESPACE tablespace_name

  InnoDB and NDB:
    ADD DATAFILE 'file_name'

  InnoDB only:
    [FILE_BLOCK_SIZE = value]

  NDB only:
    USE LOGFILE GROUP logfile_group
    [EXTENT_SIZE [=] extent_size]
    [INITIAL_SIZE [=] initial_size]
    [AUTOEXTEND_SIZE [=] autoextend_size]
    [MAX_SIZE [=] max_size]
    [NODEGROUP [=] nodegroup_id]
    [WAIT]
    [COMMENT [=] 'string']

  InnoDB and NDB:
    [ENGINE [=] engine_name]

Этот оператор используется для создания табличного пространства. Точный синтаксис и семантика зависят от используемого движка хранения. В стандартных выпусках MySQL 5.7 это всегда табличное пространство InnoDB. MySQL NDB Cluster 7.5 также поддерживает табличные пространства, использующие движок хранения NDB, помимо тех, которые используют InnoDB.

Учитываемые моменты для InnoDB

CREATE TABLESPACE используется для создания общих табличных пространств. Общее табличное пространство — это совместное табличное пространство. Оно может содержать несколько таблиц и поддерживает все форматы строк таблиц. Общие табличные пространства могут создаваться в расположении, относящемся к каталогу данных или независимом от него.

После создания общего табличного пространства InnoDB, вы можете использовать CREATE TABLE tbl_name ... TABLESPACE [=] tablespace_name или ALTER TABLE tbl_name TABLESPACE [=] tablespace_name для добавления таблиц в табличное пространство. Дополнительную информацию см. в Разделе 14.6.3.3, «Общие табличные пространства».

Учитываемые моменты для NDB Cluster

Этот оператор используется для создания табличного пространства, которое может содержать один или несколько файлов данных, предоставляя пространство для хранения таблиц данных диска NDB Cluster (см. Раздел 21.6.11, «Таблицы данных диска NDB Cluster»). С помощью этого оператора создается и добавляется один файл данных в табличное пространство. Дополнительные файлы данных можно добавить в табличное пространство, используя оператор ALTER TABLESPACE (см. Раздел 13.1.9, «Оператор ALTER TABLESPACE»).

Примечание

Все объекты данных диска NDB Cluster используют один и тот же пространство имен. Это означает, что каждый объект данных диска должен иметь уникальное имя (а не только каждый объект данных диска заданного типа). Например, вы не можете иметь табличное пространство и группу файлов журнала с одним и тем же именем, или табличное пространство и файл данных с одним и тем же именем.

Группа файлов журнала из одного или нескольких UNDO файлов журнала должна быть назначена создаваемому табличному пространству с помощью предложения USE LOGFILE GROUP. logfile_group должен быть существующей группой файлов журнала, созданной с помощью CREATE LOGFILE GROUP (см. Раздел 13.1.15, «Оператор CREATE LOGFILE GROUP»). Несколько табличных пространств могут использовать одну и ту же группу файлов журнала для UNDO логирования.

При установке EXTENT_SIZE или INITIAL_SIZE можно необязательно добавить после числа однобуквенное сокращение для порядка величины, аналогичное тем, что используются в my.cnf. Обычно это одна из букв M (для мегабайтов) или G (для гигабайтов).

INITIAL_SIZE и EXTENT_SIZE подвергаются округлению следующим образом:

EXTENT_SIZE округляется вверх до ближайшего целого кратного 32К.
INITIAL_SIZE округляется вниз до ближайшего целого кратного 32К; этот результат округляется вверх до ближайшего целого кратного EXTENT_SIZE (после любого округления).

Примечание

NDB резервирует 4% табличного пространства для операций перезапуска узла данных. Это резервированное пространство нельзя использовать для хранения данных. Это ограничение действует начиная с NDB 7.6.

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

Когда CREATE TABLESPACE используется с ENGINE [=] NDB, табличное пространство и соответствующий файл данных создаются на каждом узле данных кластера. Вы можете проверить, были ли созданы файлы данных, и получить информацию о них, запросив таблицу схемы информации FILES. (См. пример позже в этом разделе.)

(См. Раздел 24.3.9, «Таблица INFORMATION_SCHEMA FILES».)

Параметры

ADD DATAFILE: Определяет имя файла данных табличного пространства; этот параметр всегда обязателен. file_name, включая любой указанный путь, необходимо заключить в одинарные или двойные кавычки. Имена файлов (без учёта расширения) и имена каталогов должны иметь длину не менее одного байта. Файлы и каталоги с нулевой длиной имени не поддерживаются.

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

Файлы данных InnoDB. Табличное пространство InnoDB поддерживает только один файл данных, имя которого должно включать расширение .ibd.

Для табличного пространства InnoDB файл данных по умолчанию создаётся в каталоге данных MySQL (datadir). Чтобы разместить файл данных в другом месте, помимо стандартного, укажите абсолютный путь к каталогу или путь, относительный к стандартному расположению.

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

Примечание

В MySQL 5.7, ALTER TABLESPACE не поддерживается InnoDB.

Файлы данных NDB. Табличное пространство NDB поддерживает несколько файлов данных, которые могут иметь любые допустимые имена файлов; к табличному пространству NDB Cluster можно добавить больше файлов данных после его создания, используя оператор ALTER TABLESPACE.

Файл данных табличного пространства NDB по умолчанию создаётся в каталоге файловой системы узла данных — то есть в каталоге с именем ndb_nodeid_fs/TS в каталоге данных узла данных (DataDir), где nodeid — идентификатор узла данных (NodeId). Чтобы разместить файл данных в другом месте, помимо стандартного, укажите абсолютный путь к каталогу или путь, относительный к стандартному расположению. Если указанный каталог не существует, NDB пытается создать его; учётная запись системного пользователя, под которой работает процесс узла данных, должна иметь соответствующие разрешения.

Примечание

При определении пути, используемого для файла данных, NDB не расширяет символ ~ (тильда).

Если на одном физическом узле запускается несколько узлов данных, применимы следующие соображения:
- Нельзя указывать абсолютный путь при создании файла данных.
- Невозможно создать файлы данных табличного пространства вне каталога файловой системы узла данных, если каждый узел данных не имеет отдельного каталога данных.
- Если каждый узел данных имеет свой каталог данных, файлы данных можно создать где угодно внутри этого каталога.
- Если каждый узел данных имеет свой каталог данных, также может быть возможно создать файл данных за пределами каталога данных узла, используя относительный путь, при условии, что этот путь разрешается в уникальное местоположение в файловой системе хоста для каждого узла данных, работающего на этом хосте.
FILE_BLOCK_SIZE: Этот параметр, специфичный для InnoDB и игнорируемый NDB, определяет размер блока для файла данных табличного пространства. Значения могут быть указаны в байтах или килобайтах. Например, размер блока файла 8 килобайт может быть указан как 8192 или 8К. Если этот параметр не указан, FILE_BLOCK_SIZE по умолчанию использует значение innodb_page_size. FILE_BLOCK_SIZE требуется, если вы планируете использовать табличное пространство для хранения сжатых InnoDB таблиц (ROW_FORMAT=COMPRESSED). В этом случае необходимо определить табличное пространство FILE_BLOCK_SIZE при создании табличного пространства.

Если FILE_BLOCK_SIZE равно значению innodb_page_size, табличное пространство может содержать только таблицы со сжатым форматом строк (COMPACT, REDUNDANT и DYNAMIC). Таблицы со сжатым форматом строк имеют другой размер физической страницы, чем несжатые таблицы. Поэтому сжатые таблицы не могут сосуществовать в одном табличном пространстве с несжатыми таблицами.

Для общего табличного пространства, содержащего сжатые таблицы, необходимо указать FILE_BLOCK_SIZE, а значение FILE_BLOCK_SIZE должно быть допустимым размером страницы сжатия, связанным со значением innodb_page_size. Кроме того, размер физической страницы сжатой таблицы (KEY_BLOCK_SIZE) должен быть равен FILE_BLOCK_SIZE/1024. Например, если innodb_page_size=16K, и FILE_BLOCK_SIZE=8K, размер физической страницы таблицы должен быть 8. Дополнительная информация приведена в Разделе 14.6.3.3, «Общие табличные пространства».
USE LOGFILE GROUP: Обязательный для NDB параметр, представляющий собой имя группы файлов журнала, ранее созданной с помощью CREATE LOGFILE GROUP. Не поддерживается для InnoDB, где выдает ошибку.
EXTENT_SIZE: Этот параметр специфичен для NDB и не поддерживается InnoDB, где он приводит к ошибке. EXTENT_SIZE задаёт размер, в байтах, экстентов, используемых любыми файлами, принадлежащими табличному пространству. Значение по умолчанию составляет 1МБ. Минимальный размер составляет 32КБ, а теоретический максимальный размер — 2ГБ, хотя практический максимальный размер зависит от ряда факторов. В большинстве случаев изменение размера экстентов не оказывает ощутимого влияния на производительность, и рекомендуется использовать значение по умолчанию, за исключением самых необычных ситуаций.

Экстент — единица выделения дискового пространства. Один экстент заполняется данными до тех пор, пока этот экстент не будет заполнен, после чего используется другой экстент. Теоретически, до 65 535 (64К) экстентов могут использоваться на файл данных; однако, рекомендуется использовать максимальное значение 32 768 (32К). Рекомендуемый максимальный размер для одного файла данных составляет 32ГБ — то есть 32К экстентов × 1 МБ на экстент. Кроме того, после выделения экстенту для определенного раздела, он не может быть использован для хранения данных из другого раздела; экстент не может хранить данные более чем из одного раздела. Это означает, например, что табличное пространство с одним файлом данных, чьё значение INITIAL_SIZE (описано в следующем пункте) составляет 256 МБ, а значение EXTENT_SIZE составляет 128МБ, содержит всего два экстенты и может использоваться для хранения данных не более чем из двух различных разделов таблиц данных.

Вы можете узнать, сколько экстентов осталось свободными в определённом файле данных, запросив таблицу Information Schema FILES, и таким образом получить оценку того, сколько места осталось свободным в файле. Более подробное обсуждение и примеры см. в Разделе 24.3.9, «Таблица INFORMATION_SCHEMA FILES».
INITIAL_SIZE: Этот параметр специфичен для NDB и не поддерживается InnoDB, где он приводит к ошибке.

Параметр INITIAL_SIZE задаёт общий размер, в байтах, файла данных, указанного с помощью ADD DATATFILE. После создания этого файла его размер изменить нельзя; однако, вы можете добавить дополнительные файлы данных в табличное пространство, используя ALTER TABLESPACE ... ADD DATAFILE.

INITIAL_SIZE — необязательный параметр; его значение по умолчанию — 134217728 (128 МБ).

На 32-битных системах максимальное поддерживаемое значение для INITIAL_SIZE — 4294967296 (4 ГБ).
AUTOEXTEND_SIZE: В настоящее время игнорируется MySQL; зарезервировано для возможного использования в будущем. Не оказывает никакого эффекта ни в одной версии MySQL 5.7 или MySQL NDB Cluster 7.5, независимо от используемого движка хранения.
MAX_SIZE: В настоящее время игнорируется MySQL; зарезервировано для возможного использования в будущем. Не оказывает никакого эффекта ни в одной версии MySQL 5.7 или MySQL NDB Cluster 7.5, независимо от используемого движка хранения.
NODEGROUP: В настоящее время игнорируется MySQL; зарезервировано для возможного использования в будущем. Не оказывает никакого эффекта ни в одной версии MySQL 5.7 или MySQL NDB Cluster 7.5, независимо от используемого движка хранения.
WAIT: В настоящее время игнорируется MySQL; зарезервировано для возможного использования в будущем. Не оказывает никакого эффекта ни в одной версии MySQL 5.7 или MySQL NDB Cluster 7.5, независимо от используемого движка хранения.
COMMENT: В настоящее время игнорируется MySQL; зарезервировано для возможного использования в будущем. Не оказывает никакого эффекта ни в одной версии MySQL 5.7 или MySQL NDB Cluster 7.5, независимо от используемого движка хранения.
ENGINE: Определяет движок хранения, использующий табличное пространство, где engine_name — имя движка хранения. В настоящее время стандартные релизы MySQL 5.7 поддерживают только движок хранения InnoDB. MySQL NDB Cluster 7.5 поддерживает как табличные пространства NDB, так и InnoDB. Значение системной переменной default_storage_engine используется для ENGINE, если этот параметр не указан.

Примечания

Правила наименования MySQL-табличных пространств см. в разделе 9.2 «Имена объектов схемы». В дополнение к этим правилам, запрещён символ косой черты (“/”), а также имена, начинающиеся с innodb_, так как этот префикс зарезервирован для системного использования.
Табличные пространства не поддерживают временные таблицы.
Параметры innodb_file_per_table, innodb_file_format и innodb_file_format_max не влияют на операции CREATE TABLESPACE. innodb_file_per_table не нужно включать. Общие табличные пространства поддерживают все форматы строк таблиц независимо от настроек формата файлов. Аналогично, общие табличные пространства поддерживают добавление таблиц любого формата строк с помощью CREATE TABLE ... TABLESPACE, независимо от настроек формата файлов.
innodb_strict_mode не применим к общим табличным пространствам. Правила управления табличными пространствами строго выполняются независимо от innodb_strict_mode. Если параметры CREATE TABLESPACE некорректны или несовместимы, операция завершается неудачно независимо от параметра innodb_strict_mode. При добавлении таблицы в общее табличное пространство с помощью CREATE TABLE ... TABLESPACE или ALTER TABLE ... TABLESPACE, innodb_strict_mode игнорируется, но оператор оценивается так, как если бы innodb_strict_mode был включён.
Используйте DROP TABLESPACE для удаления табличного пространства. Все таблицы должны быть удалены из табличного пространства с помощью DROP TABLE перед удалением самого табличного пространства. Перед удалением табличного пространства NDB Cluster необходимо также удалить все его файлы данных с помощью одного или нескольких операторов ALTER TABLESPACE ... DROP DATATFILE. См. раздел 21.6.11.1 «NDB Cluster Disk Data Objects».
Все части таблицы InnoDB, добавленные в общее табличное пространство InnoDB, находятся в этом общем табличном пространстве, включая индексы и страницы BLOB.

Для таблицы NDB, назначенной табличному пространству, на диске хранятся только те столбцы, которые не индексированы, и фактически используют файлы данных табличного пространства. Индексы и индексированные столбцы для всех таблиц NDB всегда хранятся в памяти.
Подобно системному табличному пространству, операция обнуления или удаления таблиц, хранящихся в общем табличном пространстве, создаёт внутреннее свободное пространство в общем табличном пространстве, которое может использоваться только для новых данных InnoDB. Пространство не возвращается операционной системе, как это происходит для табличных пространств на основе файла на таблицу.
Общее табличное пространство не ассоциируется ни с какой базой данных или схемой.
ALTER TABLE ... DISCARD TABLESPACE и ALTER TABLE ...IMPORT TABLESPACE не поддерживаются для таблиц, принадлежащих общему табличному пространству.
Сервер использует блокировку метаданных на уровне табличного пространства для DDL, который ссылается на общие табличные пространства. В сравнении, сервер использует блокировку метаданных на уровне таблицы для DDL, который ссылается на табличные пространства на основе файла на таблицу.
Сгенерированное или существующее табличное пространство не может быть изменено на общее табличное пространство.
Таблицы, хранящиеся в общем табличном пространстве, могут открываться только в MySQL 5.7.6 и более поздних версиях из-за добавления новых флагов таблиц.
Нет конфликта между именами общих табличных пространств и именами табличных пространств на основе файла на таблицу. Символ “/”, присутствующий в именах табличных пространств на основе файла на таблицу, не допускается в именах общих табличных пространств.
mysqldump и mysqlpump не выполняют дамп InnoDB операторов CREATE TABLESPACE.

Примеры InnoDB

Этот пример демонстрирует создание общего табличного пространства и добавление трёх нескомпрессированных таблиц разных форматов строк.

mysql> CREATE TABLESPACE `ts1` ADD DATAFILE 'ts1.ibd' ENGINE=INNODB;

mysql> CREATE TABLE t1 (c1 INT PRIMARY KEY) TABLESPACE ts1 ROW_FORMAT=REDUNDANT;

mysql> CREATE TABLE t2 (c1 INT PRIMARY KEY) TABLESPACE ts1 ROW_FORMAT=COMPACT;

mysql> CREATE TABLE t3 (c1 INT PRIMARY KEY) TABLESPACE ts1 ROW_FORMAT=DYNAMIC;

Этот пример демонстрирует создание общего табличного пространства и добавление сжатой таблицы. В примере предполагается значение по умолчанию innodb_page_size в 16 КБ. Значение FILE_BLOCK_SIZE в 8192 требует, чтобы у сжатой таблицы было значение KEY_BLOCK_SIZE 8.

mysql> CREATE TABLESPACE `ts2` ADD DATAFILE 'ts2.ibd' FILE_BLOCK_SIZE = 8192 Engine=InnoDB;

mysql> CREATE TABLE t4 (c1 INT PRIMARY KEY) TABLESPACE ts2 ROW_FORMAT=COMPRESSED KEY_BLOCK_SIZE=8;

Пример NDB

Предположим, что вы хотите создать табличное пространство NDB Cluster Disk Data под названием myts, используя файл данных под названием mydata-1.dat. Табличное пространство NDB всегда требует использования группы файлов журнала, состоящей из одного или нескольких файлов журнала отката. В этом примере мы сначала создаём группу файлов журнала mylg, содержащую один файл журнала отката myundo-1.dat, используя оператор CREATE LOGFILE GROUP, показанный здесь:

mysql> CREATE LOGFILE GROUP myg1
    ->     ADD UNDOFILE 'myundo-1.dat'
    ->     ENGINE=NDB;
Query OK, 0 rows affected (3.29 sec)

Теперь вы можете создать описанное ранее табличное пространство с помощью следующего оператора:

mysql> CREATE TABLESPACE myts
    ->     ADD DATAFILE 'mydata-1.dat'
    ->     USE LOGFILE GROUP mylg
    ->     ENGINE=NDB;
Query OK, 0 rows affected (2.98 sec)

Теперь вы можете создать таблицу Disk Data, используя оператор CREATE TABLE с параметрами TABLESPACE и STORAGE DISK, подобно тому, что показано здесь:

mysql> CREATE TABLE mytable (
    ->     id INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
    ->     lname VARCHAR(50) NOT NULL,
    ->     fname VARCHAR(50) NOT NULL,
    ->     dob DATE NOT NULL,
    ->     joined DATE NOT NULL,
    ->     INDEX(last_name, first_name)
    -> )
    ->     TABLESPACE myts STORAGE DISK
    ->     ENGINE=NDB;
Query OK, 0 rows affected (1.41 sec)

Важно отметить, что только столбцы dob и joined из mytable фактически хранятся на диске, поскольку столбцы id, lname и fname индексированы.

Как уже упоминалось ранее, при использовании CREATE TABLESPACE с ENGINE [=] NDB создаются табличное пространство и ассоциированный файл данных на каждом узле данных NDB Cluster. Вы можете проверить, что файлы данных были созданы, и получить информацию о них, обратившись к таблице Information Schema FILES, как показано здесь:

mysql> SELECT FILE_NAME, FILE_TYPE, LOGFILE_GROUP_NAME, STATUS, EXTRA
    ->     FROM INFORMATION_SCHEMA.FILES
    ->     WHERE TABLESPACE_NAME = 'myts';

+--------------+------------+--------------------+--------+----------------+
| file_name    | file_type  | logfile_group_name | status | extra          |
+--------------+------------+--------------------+--------+----------------+
| mydata-1.dat | DATAFILE   | mylg               | NORMAL | CLUSTER_NODE=5 |
| mydata-1.dat | DATAFILE   | mylg               | NORMAL | CLUSTER_NODE=6 |
| NULL         | TABLESPACE | mylg               | NORMAL | NULL           |
+--------------+------------+--------------------+--------+----------------+
3 rows in set (0.01 sec)

Дополнительную информацию и примеры см. в разделе 21.6.11.1 «NDB Cluster Disk Data Objects».