MySQLのファイルシステムとストレージエンジンの概要についてまとめました。
※目次をクリックすると目次の下部にコンテンツが表示されます。
データベース、テーブルとファイルシステムの関係
〇データベースとディレクトリ
・MySQLはそれぞれのデータベース（スキーマ）をMySQLのデータディレクトリのサブディレクトリとして保存する。
・デフォルトでは、データベースディレクトリは”/var/lib/mysql”で、このディレクトリ内に各データベース単位にデータベース名のサブディレクトリが作成されている。
〇テーブルとファイル
・テーブルを作成するとそのテーブル定義をデータベースディレクトリ内に”テーブル名”.frmというファイル名のファイルを作成する。
〇OSとの関連
・MySQLでは、データベースの名前とテーブル定義をそのサーバーのファイルシステムに保存するので、名前が大文字小文字を区別するかはそのサーバーのOSに依存する。
・それぞれのストレージエンジンがテーブルデータとインデックスを固有の方法で保存するが、テーブル定義はそのサーバー自身が取り扱う。
・MySQLはそれぞれのデータベース（スキーマ）をMySQLのデータディレクトリのサブディレクトリとして保存する。
・デフォルトでは、データベースディレクトリは”/var/lib/mysql”で、このディレクトリ内に各データベース単位にデータベース名のサブディレクトリが作成されている。
〇テーブルとファイル
・テーブルを作成するとそのテーブル定義をデータベースディレクトリ内に”テーブル名”.frmというファイル名のファイルを作成する。
〇OSとの関連
・MySQLでは、データベースの名前とテーブル定義をそのサーバーのファイルシステムに保存するので、名前が大文字小文字を区別するかはそのサーバーのOSに依存する。
・それぞれのストレージエンジンがテーブルデータとインデックスを固有の方法で保存するが、テーブル定義はそのサーバー自身が取り扱う。
MyISAMストレージエンジンの概要
●トランザクション
・トランザクション非対応、電源障害時などにデータ破損のおそれ有り。
●ストレージ
・テーブルをデータファイル（.MYD）とインデックスファイル（.MYI）に分けて保存する。
・ダイナミックとスタティック（固定長）の行を含む事が出来る。
テーブル定義に基づいてどちらにするか決める。
●ロック
・行レベルではなく、テーブルロック。
・読込時はすべての対象テーブルを共有ロック、書込み時は、排他ロック。
・並列インサートが出来る。
●リペア
・手動または自動チェックとテーブルのリペアは出来る。（トランザクションとクラッシュリカバリーには対応していないので、一部のデータが消失している可能性がある）ただし、時間はかかる。
CHECK TABLEコマンド、REPAIR TABLEコマンド
・サーバーがオフラインの状態でmyisamchkコマンドラインツールを使ってチェック・リペアが出来る。
●インデックス
・BLOB、TEXTカラムの先頭から500文字でインデックスを作成できる。
・フルテキストインデックスをサポート。
●遅延書込み
・DELAY_KEY_WRITEオプションを使ってテーブルを作成すると、インデックスが更新されたときに即座にキーバッファのフラッシュが行われずテーブルが閉じられたときに初めてフラッシュされる。
性能を向上できるがサーバーがクラッシュした際にはインデックスが壊れて修復が必要になる可能性がある。
●テーブルの圧縮
・myisampackユーティリティを使ってテーブルを圧縮できる。データ変更を行わないテーブルに有用。
サイズが小さくなることによってディスクシークが短縮でき高速に出来る。
●性能
・シンプルなデザインでディスクやメモリのフットプリントが小さいので、使用用途によっては良好なパフォーマンスを示す。
・キーキャッシュの排他制御、テーブルロックの影響によってパフォーマンスが劣化することもある。
・トランザクション非対応、電源障害時などにデータ破損のおそれ有り。
●ストレージ
・テーブルをデータファイル（.MYD）とインデックスファイル（.MYI）に分けて保存する。
・ダイナミックとスタティック（固定長）の行を含む事が出来る。
テーブル定義に基づいてどちらにするか決める。
●ロック
・行レベルではなく、テーブルロック。
・読込時はすべての対象テーブルを共有ロック、書込み時は、排他ロック。
・並列インサートが出来る。
●リペア
・手動または自動チェックとテーブルのリペアは出来る。（トランザクションとクラッシュリカバリーには対応していないので、一部のデータが消失している可能性がある）ただし、時間はかかる。
CHECK TABLEコマンド、REPAIR TABLEコマンド
・サーバーがオフラインの状態でmyisamchkコマンドラインツールを使ってチェック・リペアが出来る。
●インデックス
・BLOB、TEXTカラムの先頭から500文字でインデックスを作成できる。
・フルテキストインデックスをサポート。
●遅延書込み
・DELAY_KEY_WRITEオプションを使ってテーブルを作成すると、インデックスが更新されたときに即座にキーバッファのフラッシュが行われずテーブルが閉じられたときに初めてフラッシュされる。
性能を向上できるがサーバーがクラッシュした際にはインデックスが壊れて修復が必要になる可能性がある。
●テーブルの圧縮
・myisampackユーティリティを使ってテーブルを圧縮できる。データ変更を行わないテーブルに有用。
サイズが小さくなることによってディスクシークが短縮でき高速に出来る。
●性能
・シンプルなデザインでディスクやメモリのフットプリントが小さいので、使用用途によっては良好なパフォーマンスを示す。
・キーキャッシュの排他制御、テーブルロックの影響によってパフォーマンスが劣化することもある。
InnoDBストレージエンジンの概要
●概要
・トランザクション処理可能なストレージエンジンのデフォルト。
・ロールバックが多い状況よりも短い時間のトランザクション処理用に適した設定。
・データをテーブルスペースと呼ばれる一つ又は複数のデータファイルに保存する。
・InnoDBのテーブルは、クラスターインデックス上に構築される。
プライマリキーの検索は非常に高速だが、セカンダリインデックスはプライマリキーのカラムを含むのでプライマリキーのサイズが大きいと他のインデックスも大きくなってしまう。
・ストレージのフォーマットはプラットフォームに依存しない。データとインデックスファイルをIntelベースのサーバからPowerPCなど他のサーバーにコピーする事も可能。
・InnoDBでは、内部で様々な最適化が行われている。
ディスクからのデータのプリフェッチ、自動でメモリにハッシュインデックス構築、インサートバッファなど。
●InnoDBストレージエンジンの有効、無効
・InnoDBストレージエンジンはデフォルトにて有効になっている。
もしInnoDBテーブルを無効にしたい場合は、MySQLオプションファイルにskip-innodbオプションを指定する。
・トランザクション処理可能なストレージエンジンのデフォルト。
・ロールバックが多い状況よりも短い時間のトランザクション処理用に適した設定。
・データをテーブルスペースと呼ばれる一つ又は複数のデータファイルに保存する。
・InnoDBのテーブルは、クラスターインデックス上に構築される。
プライマリキーの検索は非常に高速だが、セカンダリインデックスはプライマリキーのカラムを含むのでプライマリキーのサイズが大きいと他のインデックスも大きくなってしまう。
・ストレージのフォーマットはプラットフォームに依存しない。データとインデックスファイルをIntelベースのサーバからPowerPCなど他のサーバーにコピーする事も可能。
・InnoDBでは、内部で様々な最適化が行われている。
ディスクからのデータのプリフェッチ、自動でメモリにハッシュインデックス構築、インサートバッファなど。
●InnoDBストレージエンジンの有効、無効
・InnoDBストレージエンジンはデフォルトにて有効になっている。
もしInnoDBテーブルを無効にしたい場合は、MySQLオプションファイルにskip-innodbオプションを指定する。
InnoDBのテーブルスペース、テーブルファイル
●InnoDBのテーブルスペース、データファイル
・InnoDBストレージエンジンは、メインメモリ内にデータとインデックスをキャッシュする為の、それ自身のバッファプールを維持する。
・InnoDBは、テーブルとインデックスをテーブルスペースと呼ばれる一つ又は複数のデータファイルに格納する。
※MyISAMストレージエンジンでは、各テーブルが別々のファイル（データファイルは”.MYD”という拡張子のファイル、インデックスファイルは”.MYI”という拡張子のファイル）に分けて保存する。
・InnoDBストレージエンジンによって管理されている2つの重要なディスクベースリソースは、そのテーブルスペースデータファイルとログファイル。
※Version5.1では、InnoDB設定オプションを全く指定しなければ、データディレクトリ内に、ibdata1という名前の10MBの自動延長データファイルと、ib_logfile0とib_logfile1という名前の5MBのログファイルが作成される。
・InnoDBテーブルはMyISAMテーブルよりも多くのディスク領域を必要とする。
〇データファイルのパス（innodb_data_file_pathオプション）
・v5.1のデフォルトでは、ibdata1と名づけられた10MBの単一自動拡大データファイルが作成される。
ibdata1:10M:autoextend
・autoextendオプションを指定すると、テーブルスペースの中に空き領域がなければデータファイルを拡大する。
●システム（共有）テーブルスペース（ibdataファイル）
・InnoDBテーブル関連オブジェクト(データディクショナリ)とUndoログ、変更バッファー、二重書き込みバッファーのストレージ領域のメタデータを含むデータファイル(ibdataファイル)の小さなセット。
・InnoDBは、内部のデータディクショナリとUndoログをシステムのテーブルスペースに配置するため、常にこのテーブルスペースを必要とする。
・innodb_file_per_tableがOFFの場合は、すべてのInnoDBテーブルのテーブルとインデックスデータが含まれる。
・MySQL5.6.7より前では、デフォルトでfile-per-tableモードの設定がOFFになっていて、すべてのInnoDBテーブルとインデックスをシステムテーブルスペース内部に保持することで、多くの場合、このファイルが非常に大きくなっていた。
システムテーブルスペースは決して縮小しないので、大容量の一時データがロードされてから削除された場合、ストレージの問題が発生する可能性がある。
（MySQL5.6.7以降では、デフォルトはfile-per-tableモードの設定がONになっていて、各テーブルとそれに関連付けられたインデックスが別個の.ibdファイルに格納される。）
●Per-Tableテーブルスペース（innodb_file_per_table）
・各InnoDBテーブルとそのインデックスを別個の.ibdファイルに内に格納する。（MyISAMはテーブルをデータファイル（tbl_name.MYD）とインデックスファイル（tbl_name.MYI）に分けて格納する。）
・複数のテーブルスペースを利用する事は、特定のテーブルを別々の物理ディスクに移動したり、単一テーブルのバックアップを残りのInnoDBテーブルの利用を邪魔する事なく、素早く復元したいユーザにとって有益。
・もしinnodb_file_per_tableの記述をmy.cnfから削除してサーバを再起動すると、InnoDBは共有テーブルスペースファイル内にテーブルを再度作成する。
・innodb_file_per_tableはテーブル作成だけに影響を与え、既存テーブルにアクセスはしない。
このオプションを利用してサーバを起動すると、新しいテーブルは”.ibd”ファイルを利用して作成されるが、共有テーブルスペース内に存在するテーブルにアクセスする事もまだ可能。
もしオプションを削除してサーバを再起動すると、新しいテーブルは共有テーブルスペース内に作成されるが、複数のテーブルスペースを利用して作成されたテーブルにもまだアクセスする事ができる。
・innodb_file_per_tableを有効にして、システムテーブルスペースから各テーブルの”.ibd”ファイルにテーブルが移動された場合、システムテーブルスペースを構成するデータファイル（ibdata）のサイズは維持される。以前にテーブルが占有したスペースは、新しいInnoDBデータ用に再利用できるが、OS用には再利用されない。（OSから見えるファイルサイズは縮小しない）
〇設定値の確認
・InnoDBストレージエンジンは、メインメモリ内にデータとインデックスをキャッシュする為の、それ自身のバッファプールを維持する。
・InnoDBは、テーブルとインデックスをテーブルスペースと呼ばれる一つ又は複数のデータファイルに格納する。
※MyISAMストレージエンジンでは、各テーブルが別々のファイル（データファイルは”.MYD”という拡張子のファイル、インデックスファイルは”.MYI”という拡張子のファイル）に分けて保存する。
・InnoDBストレージエンジンによって管理されている2つの重要なディスクベースリソースは、そのテーブルスペースデータファイルとログファイル。
※Version5.1では、InnoDB設定オプションを全く指定しなければ、データディレクトリ内に、ibdata1という名前の10MBの自動延長データファイルと、ib_logfile0とib_logfile1という名前の5MBのログファイルが作成される。
・InnoDBテーブルはMyISAMテーブルよりも多くのディスク領域を必要とする。
〇データファイルのパス（innodb_data_file_pathオプション）
・v5.1のデフォルトでは、ibdata1と名づけられた10MBの単一自動拡大データファイルが作成される。
ibdata1:10M:autoextend
・autoextendオプションを指定すると、テーブルスペースの中に空き領域がなければデータファイルを拡大する。
●システム（共有）テーブルスペース（ibdataファイル）
・InnoDBテーブル関連オブジェクト(データディクショナリ)とUndoログ、変更バッファー、二重書き込みバッファーのストレージ領域のメタデータを含むデータファイル(ibdataファイル)の小さなセット。
・InnoDBは、内部のデータディクショナリとUndoログをシステムのテーブルスペースに配置するため、常にこのテーブルスペースを必要とする。
・innodb_file_per_tableがOFFの場合は、すべてのInnoDBテーブルのテーブルとインデックスデータが含まれる。
・MySQL5.6.7より前では、デフォルトでfile-per-tableモードの設定がOFFになっていて、すべてのInnoDBテーブルとインデックスをシステムテーブルスペース内部に保持することで、多くの場合、このファイルが非常に大きくなっていた。
システムテーブルスペースは決して縮小しないので、大容量の一時データがロードされてから削除された場合、ストレージの問題が発生する可能性がある。
（MySQL5.6.7以降では、デフォルトはfile-per-tableモードの設定がONになっていて、各テーブルとそれに関連付けられたインデックスが別個の.ibdファイルに格納される。）
●Per-Tableテーブルスペース（innodb_file_per_table）
・各InnoDBテーブルとそのインデックスを別個の.ibdファイルに内に格納する。（MyISAMはテーブルをデータファイル（tbl_name.MYD）とインデックスファイル（tbl_name.MYI）に分けて格納する。）
・複数のテーブルスペースを利用する事は、特定のテーブルを別々の物理ディスクに移動したり、単一テーブルのバックアップを残りのInnoDBテーブルの利用を邪魔する事なく、素早く復元したいユーザにとって有益。
・もしinnodb_file_per_tableの記述をmy.cnfから削除してサーバを再起動すると、InnoDBは共有テーブルスペースファイル内にテーブルを再度作成する。
・innodb_file_per_tableはテーブル作成だけに影響を与え、既存テーブルにアクセスはしない。
このオプションを利用してサーバを起動すると、新しいテーブルは”.ibd”ファイルを利用して作成されるが、共有テーブルスペース内に存在するテーブルにアクセスする事もまだ可能。
もしオプションを削除してサーバを再起動すると、新しいテーブルは共有テーブルスペース内に作成されるが、複数のテーブルスペースを利用して作成されたテーブルにもまだアクセスする事ができる。
・innodb_file_per_tableを有効にして、システムテーブルスペースから各テーブルの”.ibd”ファイルにテーブルが移動された場合、システムテーブルスペースを構成するデータファイル（ibdata）のサイズは維持される。以前にテーブルが占有したスペースは、新しいInnoDBデータ用に再利用できるが、OS用には再利用されない。（OSから見えるファイルサイズは縮小しない）
〇設定値の確認
mysql> show variables like 'innodb_file_per_table'; +-----------------------+-------+ | Variable_name | Value | +-----------------------+-------+ | innodb_file_per_table | OFF | +-----------------------+-------+ 1 row in set (0.00 sec)
●InnoDB初期化時の不具合
・InnoDBがそのテーブルスペースかログファイルを初期化しようとした時に何かが失敗すると、InnoDBによって作成されたファイル全て（全てのibdataファイルとib_logfileファイル）を削除しなければならない。
●システムテーブルスペースのサイズを小さくする手順
①mysqldumpを使って、全てのInnoDBテーブルをダンプ。
②MySQLサーバを停止。
③ibdataおよびib_logファイルを含む、すべての既存のテーブルスペースファイル(*.ibd)を削除。
④InnoDBテーブルのすべての”.frm”ファイルを削除。
⑤新しいテーブルスペースを構成する。
⑥サーバを再起動。
⑦ダンプファイルをインポート
※参考情報
http://download.nust.na/pub6/mysql/doc/refman/5.1/ja/adding-and-removing.html
https://dev.mysql.com/doc/refman/5.5/en/innodb-resize-system-tablespace.html
https://dev.mysql.com/doc/refman/5.6/ja/innodb-data-log-reconfiguration.html
https://dev.mysql.com/doc/refman/5.7/en/innodb-resize-system-tablespace.html