MariaDBでIdentifier to File Name Mappingを理解し、プログラミング: パフォーマンス向上とファイルシステム整理を実現

Identifier to File Name Mapping の仕組み

MariaDB は、innodb_file_per_table 設定を使用して、各テーブルに対して個別の物理ファイルを作成します。この設定が有効な場合、データベースは ibdata1 などのデフォルトのファイル名ではなく、テーブル名に基づいたファイル名を使用します。

さらに、innodb_file_per_table_space 設定を使用して、テーブルスペースごとにファイル名フォーマットを定義することができます。これにより、複数のテーブルを単一のファイルシステムに格納したり、特定のテーブルスペースにパフォーマンス上の優先順位を付けたりすることができます。

Identifier to File Name Mapping のプログラミング

Identifier to File Name Mapping をプログラムで設定するには、次の方法があります。

innodb_file_per_table および innodb_file_per_table_space 設定を my.cnf 構成ファイルで設定する。

innodb_file_per_table=1
innodb_file_per_table_space=MY_TABLESPACE: autoextend_size=10M

ALTER TABLE ステートメントを使用して、個々のテーブルのファイル名を明示的に設定する。

ALTER TABLE my_table
ENGINE=InnoDB
FILE_PER_TABLE
TABLESPACE MY_TABLESPACE;

CREATE TABLE ステートメントで ENGINE=InnoDB オプションと FILE_PER_TABLE オプションを指定する。

CREATE TABLE my_table (
  id INT PRIMARY KEY,
  name VARCHAR(255)
) ENGINE=InnoDB FILE_PER_TABLE;

Identifier to File Name Mapping の利点

データベース管理の簡素化
個々のテーブルファイルを使用すると、特定のテーブルをバックアップしたり、復元したりすることが容易になります。
ファイルシステムの整理
個々のテーブルファイルを使用すると、データベースファイルを論理的に整理しやすくなります。
パフォーマンスの向上
個々のテーブルファイルを使用すると、データベースエンジンはテーブルデータに直接アクセスできるため、I/O 性能が向上します。

Identifier to File Name Mapping の注意点

複雑さの増加
個々のテーブルファイルを使用すると、データベース管理が複雑になる可能性があります。
ファイルシステムのオーバーヘッド
個々のテーブルファイルを使用すると、ファイルシステムのオーバーヘッドが増加する可能性があります。

Identifier to File Name Mapping の例

次の例は、my_table というテーブルのファイル名を my_table.ibf に設定する方法を示しています。

ALTER TABLE my_table
ENGINE=InnoDB
FILE_PER_TABLE
TABLESPACE MY_TABLESPACE;

この例では、MY_TABLESPACE というテーブルスペースが使用されます。このテーブルスペースが存在しない場合は、データベースエンジンによって自動的に作成されます。

Identifier to File Name Mapping は、MySQL にも実装されています。
Identifier to File Name Mapping は、MariaDB 5.5 以降で使用できます。

innodb_file_per_table と innodb_file_per_table_space 設定を my.cnf 構成ファイルで設定する

[mysqld]
innodb_file_per_table=1
innodb_file_per_table_space=MY_TABLESPACE: autoextend_size=10M

この設定は、すべてのテーブルに対して個別のファイルを作成し、MY_TABLESPACE という名前のテーブルスペースに格納することを指定します。

ALTER TABLE ステートメントを使用して、個々のテーブルのファイル名を明示的に設定する

ALTER TABLE my_table
ENGINE=InnoDB
FILE_PER_TABLE
TABLESPACE MY_TABLESPACE;

このステートメントは、my_table というテーブルのファイル名を my_table.ibf に設定します。

CREATE TABLE ステートメントで ENGINE=InnoDB オプションと FILE_PER_TABLE オプションを指定する

CREATE TABLE my_table (
  id INT PRIMARY KEY,
  name VARCHAR(255)
) ENGINE=InnoDB FILE_PER_TABLE;

ファイルシステムパスの指定

上記の例では、ファイル名はデフォルトのデータディレクトリ内に作成されます。ファイルシステムパスを明示的に指定するには、FILE_PER_TABLE オプションと DATAFILE オプションを組み合わせて使用します。

ALTER TABLE my_table
ENGINE=InnoDB
FILE_PER_TABLE
DATAFILE '/path/to/data/my_table.ibf'
TABLESPACE MY_TABLESPACE;

このステートメントは、my_table というテーブルのファイル名を /path/to/data/my_table.ibf に設定します。

FILE_PER_TABLE オプションと DATAFILE オプションを組み合わせて使用する場合、DATAFILE オプションで指定したファイルが存在する必要があります。
innodb_file_per_table 設定と innodb_file_per_table_space 設定は、互いに排他的です。どちらか一方のみを設定できます。
上記のコードは、MariaDB 5.5 以降で使用できます。

しかし、Identifier to File Name Mapping にはいくつかの欠点があります。

複雑さの増加
個々のテーブルファイルを使用すると、データベース管理が複雑になる可能性があります。
ファイルシステムのオーバーヘッド
個々のテーブルファイルを使用すると、ファイルシステムのオーバーヘッドが増加する可能性があります。

これらの欠点を回避するために、Identifier to File Name Mapping の代替方法をいくつか検討することができます。

パーティショニング

パーティショニングは、大きなテーブルを論理的に分割し、複数の小さなテーブルに格納する方法です。これにより、I/O 性能を向上させ、ファイルシステムのオーバーヘッドを削減することができます。

例

CREATE TABLE my_table (
  id INT PRIMARY KEY,
  name VARCHAR(255),
  created_at DATE
)
PARTITION BY RANGE (created_at) (
  PARTITION p2023_01 VALUES LESS THAN (2023-02-01),
  PARTITION p2023_02 VALUES LESS THAN (2023-03-01),
  PARTITION p2023_03 VALUES LESS THAN (2023-04-01),
  ...
);

この例では、my_table テーブルは、created_at 列の値に基づいて月ごとにパーティション化されます。

ファイルグループ

ファイルグループは、複数のテーブルファイルを論理的にグループ化する機能です。これにより、特定のテーブルスペースにパフォーマンス上の優先順位を付けたり、特定のストレージデバイスにテーブルファイルを格納したりすることができます。

例

CREATE FILESPACE my_filespace ENGINE=InnoDB
DEFAULT_TABLESPACE my_filespace
DATAFILE '/path/to/data/my_filespace.ibf';

CREATE TABLE my_table (
  id INT PRIMARY KEY,
  name VARCHAR(255)
) ENGINE=InnoDB TABLESPACE my_filespace;

この例では、/path/to/data/my_filespace.ibf という名前のファイルスペースが作成され、my_table テーブルはこのファイルスペースに格納されます。

シンボリックリンク

シンボリックリンクは、物理ファイルと論理ファイル間のリンクを作成する方法です。これにより、個々のテーブルファイルを使用せずに、Identifier to File Name Mapping の利点を享受することができます。

例

ln -s /path/to/data/my_table.ibf /data/my_table.ibf

この例では、/path/to/data/my_table.ibf という物理ファイルへのシンボリックリンクが /data/my_table.ibf という名前で作成されます。

外部ストレージ

外部ストレージ (NFS や Ceph など) を使用すると、データベースファイルをネットワーク上の別の場所に格納することができます。これにより、ファイルシステムのオーバーヘッドを削減し、ストレージ容量を拡張することができます。

例

CREATE TABLE my_table (
  id INT PRIMARY KEY,
  name VARCHAR(255)
) ENGINE=InnoDB
DATAFILE 'nfs://storage-server/data/my_table.ibf';

この例では、my_table テーブルのファイルは、nfs://storage-server/data/my_table.ibf というネットワーク上の場所にあります。