紹介

MySQLは、世界で最も広く使用されているリレーショナルデータベースシステムの1つで、ほとんどの大規模ウェブサイトで使用されています。そのため、MySQLを理解することは、ウェブマスターにとって必須のスキルです。

MySQLの論理アーキテクチャは、以下のように説明できます：

MySQLには、以下のような基本的なコンポーネントがあります：

• 接続/スレッド処理。
• クエリキャッシュ。
• パーサー。
• オプティマイザー。
• ストレージエンジン。

各コンポーネントの機能と役割を理解することは、MySQLを効果的に使用するためには欠かせません。この記事では、上記のモデルの下部にあるコンポーネント、ストレージエンジンについて紹介します。

ストレージエンジン

ストレージエンジンは、実際にはMySQLがデータをハードディスクに保存する方法です。MySQLは各データベースをdataフォルダ内のサブフォルダとして保存します。テーブルが作成されると、MySQLはその定義を.frm拡張子のファイルに保存し、作成されたテーブル名と一致します。テーブル定義の管理はMySQLサーバーの役割ですが、各ストレージエンジンはデータの保存方法やインデックス作成方法が異なります。

例えば、–datadirを/usr/local/mysql/dataに設定し、testというデータベース内でusersテーブルを次のように定義した場合：

create table users (
    id int not null auto_increment, 
    name varchar(30), 
    password varchar(20), 
    primary key(id)
);

この場合、/usr/local/mysql/data内にはtestというサブフォルダが作成され、その下に次のようなファイルが作成されます：

-rw-rw----  1 _mysql  wheel   8624  5  7 17:35 users.frm
-rw-rw----  1 _mysql  wheel  98304  5  7 17:35 users.ibd

現在のテーブルのストレージエンジンを確認するには、次のコマンドを使用します：SHOW DATABASE STATUS:

mysql> show table status like 'users' \G
*************************** 1. row ***************************
           Name: users
         Engine: InnoDB
        Version: 10
     Row_format: Compact
           Rows: 2
 Avg_row_length: 8192
    Data_length: 16384
Max_data_length: 0
   Index_length: 0
      Data_free: 0
 Auto_increment: 3
    Create_time: 2013-05-07 17:35:09
    Update_time: NULL
     Check_time: NULL
      Collation: latin1_swedish_ci
       Checksum: NULL
 Create_options:
        Comment:
1 row in set (0.01 sec)

この場合、ストレージエンジンはInnoDBで、行の形式はCompact、データ行は2行、次の自動インクリメント値は3です。

ストレージエンジンの概要

1. MyISAMエンジン

特徴：

• 全文検索インデックス。
• 圧縮。
• 空間関数（GIS）。
• トランザクションはサポートしていません。
• 行レベルロックはサポートしていません。

保存：

MyISAMは各テーブルを2つのファイルに保存します：データは.MYDファイルに、インデックスは.MYIファイルに保存されます。行には2種類があり、動的と静的（データの長さが変わるかどうかに依存します）。保存可能な行数はOSやディスク容量によって制限されます。MyISAMは6バイトのポインタを使用してデータレコードを指し示すため、最大256TBまでのデータサイズが制限されます。

機能：

• MyISAMはテーブル全体をロックします。ユーザー（MySQLサーバー）は読み取り時に共有ロックを、書き込み時に排他ロックを取得します。ただし、読み取りと書き込みは同時に行われる可能性があります。
• MyISAMはクラッシュ後にデータ修復と復旧を行うことができます。
• check table/repair tableコマンドを使用してエラーをチェックし、修復できます。
• MyISAMはフルテキストインデックスをサポートし、フルテキスト検索ができます。
• MyISAMはデータをすぐにディスクに書き込むのではなく、メモリ上のバッファに書き込み、一定時間後にディスクに書き込みます。このため書き込み速度が向上しますが、サーバーがクラッシュした後、myisamchkを使って破損したデータを復元する必要があります。
• MyISAMはデータ圧縮をサポートし、読み取り速度を向上させます。ただし、圧縮後のデータは更新できません。

2. InnoDBエンジン

特徴：

• MySQLのエンジンの中で最も複雑です。
• トランザクションをサポートしています。
• 修復と復旧に優れています。

保存：

InnoDBはデータを1つのファイル（テーブルスペースと呼ばれる）に保存します。

機能：

• InnoDBはMVCC（マルチバージョン同時実行制御）をサポートしており、同時アクセスの効率を向上させ、next-keyロック戦略をサポートします。
• InnoDBはクラスタ化インデックスを使用しており、主キーでの検索性能が非常に高いです。InnoDBはインデックスの並び替えをサポートしていないため、テーブルの構造が変更されると、すべてのデータが最初からインデックス作成されます（大きなテーブルでは遅くなります）。

3. Memoryエンジン

特徴：

• HEAPテーブルとも呼ばれます。

保存：

すべてのデータはメモリに保存されます。

機能：

• サーバー再起動後、テーブルの構造は保持されますが、データはすべて失われます。
• Memoryエンジンはハッシュインデックスを使用するため、クエリのルックアップが非常に高速です。
• Memoryエンジンはテーブルレベルロックを使用するため、同時実行性は高くありません。

4. Archiveエンジン

特徴：

• 挿入と選択のみをサポート。
• インデックス作成はサポートしていません。
• データはzlibで圧縮され、I/Oが少なく、書き込み速度が速いです。

機能：

• 高い書き込み速度で、ログアプリケーションに適しています。

5. CSVエンジン

特徴：

• CSVファイルをテーブルとして扱います。
• インデックス作成はサポートしていません。

機能：

• CSVファイルから情報を抽出してデータベースに書き込み、データベースから直接CSV結果を取得する場合に適しています。

6. Falconエンジン

特徴：

• 現代のハードウェア向けに設計されています：64ビットサーバー、大容量メモリ。
• まだ比較的新しく、ユーザーケースは少ないです。

7. Mariaエンジン（関連データベース：MariaDB）

特徴：

• MySQLの経験豊富な開発者たちによって設計され、MyISAMの代替を目的としています。
• トランザクションをオプションでサポートしています。
• エラーメンテナンス。
• 行レベルロックとMVCC。
• BLOBサポートが強化されています。

エンジン選択基準

• トランザクション: アプリケーションがトランザクションを必要とする場合、InnoDBが唯一の選択肢です。トランザクションが不要な場合、MyISAMが良い選択です。
• 同時実行性: 高負荷が求められ、トランザクションが不要な場合、MyISAMが最適な選択です。
• バックアップ: すべてのエンジンはある程度バックアップをサポートしています。また、システム設計の観点でもバックアップをサポートする必要があります。例えば、マスターとスレーブを含むデータベースサーバーを設計する場合、マスターはトランザクションが必要なのでInnoDBを使用し、スレーブはバックアップと読み取りを行うのでMyISAMを使用することができます。
• クラッシュ後の復旧: MyISAMはInnoDBよりもクラッシュ後の復旧能力が劣ります。
• システムの要求に応じた機能: ロギングが必要な場合、MyISAMまたはArchiveが適しています。CSVを直接保存する必要がある場合、CSVエンジンが適しています。

結論

この記事では、ストレージエンジンについての概要を紹介しました。いくつかの代表的なエンジンとその特徴を紹介し、選択基準についても説明しました。

この記事を通じて、データベースのストレージエンジン全般について、またMySQLのストレージエンジンについて理解し、ストレージエンジン選択の重要性を認識できたことを願っています。

出典: ktmt.github.io