概要: MySQLには複数のストレージエンジンがあり、各ストレージエンジンにはそれぞれ長所と短所があります。優れたものを選んで使用できます：MyISAM、InnoDB、MERGE、MEMORY(HEAP)、BDB(BerkeleyDB)、EXAMPLE、FEDERATED、ARCHIVE、CSV、BLACKHOLE。

MySQL には複数のストレージエンジンがあり、各ストレージエンジンにはそれぞれ長所と短所があります。優れたものを選んで使用できます：

MyISAM、InnoDB、MERGE、MEMORY(HEAP)、BDB(BerkeleyDB)、EXAMPLE、FEDERATED、ARCHIVE、CSV、BLACKHOLE。

MySQL は、異なるテーブルタイプのプロセッサとしていくつかのストレージエンジンをサポートしています。MySQLストレージエンジンには、トランザクション安全テーブルを処理するエンジンと、非トランザクション安全テーブルを処理するエンジンが含まれます：

• MyISAM は非トランザクションテーブルを管理します。高速な保存と検索、および全文検索機能を提供します。MyISAM はすべての MySQL 構成でサポートされており、MySQL のデフォルトで別のエンジンを使用するように構成しない限り、デフォルトのストレージエンジンです。

• MEMORY ストレージエンジンは「インメモリ」テーブルを提供します。MERGE ストレージエンジンは、同じ MyISAM テーブルの集合を単一のテーブルとして処理することを可能にします。MyISAM と同様に、MEMORY と MERGE ストレージエンジンは非トランザクションテーブルを処理し、これらの2つのエンジンもデフォルトで MySQL に含まれています。

  注釈：MEMORY ストレージエンジンは正式に HEAP エンジンとして定義されています。

• InnoDB と BDB ストレージエンジンはトランザクション安全テーブルを提供します。BDB はそれをサポートする OS 向けにリリースされた MySQL-Max バイナリ配布版に含まれています。InnoDB もすべての MySQL 5.1 バイナリ配布版にデフォルトで含まれており、好みに応じて MySQL を構成してどちらのエンジンも許可または禁止できます。

• EXAMPLE ストレージエンジンは「スタブ」エンジンであり、何も行いません。このエンジンでテーブルを作成できますが、データは保存も検索もされません。このエンジンの目的は、MySQL ソースコード内の例として、新しいストレージエンジンの書き方を始める方法を示すことです。同様に、その主な関心は開発者向けです。

• NDB Cluster は、MySQL Cluster が複数のコンピュータに分割されたテーブルを実装するために使用するストレージエンジンです。これは MySQL-Max 5.1 バイナリ配布版で提供されています。このストレージエンジンは現在、Linux、Solaris、Mac OS X でのみサポートされています。将来の MySQL 配布版では、Windows を含む他のプラットフォームでのこのエンジンのサポートを追加したいと考えています。

• ARCHIVE ストレージエンジンは、インデックスなしで、非常に小さな領域で大量のデータを保存するために使用されます。

• CSV ストレージエンジンは、データをカンマ区切り形式でテキストファイルに保存します。

• BLACKHOLE ストレージエンジンはデータを受け入れますが保存せず、検索は常に空のセットを返します。

• FEDERATED ストレージエンジンは、データをリモートデータベースに保存します。MySQL 5.1 では、MySQL C Client API を使用して MySQL とのみ連携します。将来の配布版では、他のドライバまたはクライアント接続方法を使用して他のデータソースに接続できるようにしたいと考えています。

比較的よく使われるのは MyISAM と InnoBD です

MySQL ストレージエンジン MyISAM と InnoDB はどう選ぶか？

MySQL のストレージエンジンは MyISAM と InnoDB の2つだけではありませんが、よく使われるのはこの2つです。サイト運営者が MySQL のストレージエンジンに注意を払ったことがないかもしれませんが、実はストレージエンジンはデータベース設計における重要なポイントの1つです。では、ブログシステムはどのストレージエンジンを使うべきでしょうか？

以下で、2つのストレージエンジンの違いをそれぞれ見ていきましょう。

• 一、InnoDB はトランザクションをサポートし、MyISAM はサポートしません。これは非常に重要です。トランザクションは高度な処理方法であり、一連の追加・削除・変更操作の中でどれか1つでもエラーが発生した場合でもロールバックして元に戻すことができますが、MyISAM ではそれができません。
• 二、MyISAM はクエリと挿入を中心としたアプリケーションに適しており、InnoDB は頻繁な変更と高いセキュリティが関係するアプリケーションに適しています。
• 三、InnoDB は外部キーをサポートし、MyISAM はサポートしません。
• 四、MySQL は 5.1 より前のバージョンではデフォルトのストレージエンジンが MyISAM で、5.1 以降のバージョンではデフォルトのストレージエンジンが InnoDB です。
• 五、InnoDB は FULLTEXT タイプのインデックスをサポートしません。
• 六、InnoDB はテーブルの行数を保存しません。例えば select count(*) from table のとき、InnoDB は行数を計算するためにテーブル全体をスキャンする必要がありますが、MyISAM は保存されている行数を単純に読み出すだけです。注意点として、count(*) ステートメントに where 条件が含まれる場合、MyISAM もテーブル全体をスキャンする必要があります。
• 七、自動増加フィールドについて、InnoDB ではそのフィールドのみのインデックスを含める必要がありますが、MyISAM テーブルでは他のフィールドと一緒に複合インデックスを構築できます。
• 八、テーブル全体を空にするとき、InnoDB は1行ずつ削除するため効率が非常に遅いです。MyISAM はテーブルを再構築します。
• 九、InnoDB は行ロックをサポートします（ただし、状況によってはテーブル全体をロックすることもあります。例えばupdate table set a=1 where user like '%lee%'

上記の9つの違いと個人ブログの特徴を踏まえ、個人ブログシステムには MyISAM を推奨します。ブログでは主な操作は読み取りと書き込みであり、連鎖操作はほとんどないからです。そのため、MyISAM エンジンを選択すると、ブログのページを開く効率が InnoDB エンジンのブログよりも高くなります。もちろん、これはあくまで個人的な提案であり、ほとんどのブログは実際の状況に応じて慎重に選択する必要があります。

MyISAM と InnoDB の選択と使用に関するいくつかの点：

MYISAMとINNODBはMySQLデータベースが提供する2つのストレージエンジンです。両者の優劣はそれぞれ一長一短といえます。INNODBはトランザクション機能や行レベルロックなどのリレーショナルデータベースの高度な機能をサポートしますが、MYISAMはサポートしません。MYISAMは性能が優れており、占有するストレージ容量も少なくなります。したがって、どのストレージエンジンを選択するかは、具体的なアプリケーションによって異なります。

アプリケーションでどうしてもトランザクションを使用する必要があるなら、間違いなくINNODBエンジンを選択すべきです。ただし、INNODBの行レベルロックには条件があることに注意してください。WHERE条件で主キーを使用していない場合は、やはりテーブル全体がロックされます。例えば、DELETE FROM mytable のような削除文です。

アプリケーションがクエリ性能を重視するなら、MYISAMを使用すべきです。MYISAMはインデックスとデータが分離されており、インデックスは圧縮されているため、メモリをより有効に活用できます。そのため、クエリ性能はINNODBよりも明らかに優れています。圧縮されたインデックスはディスク容量も節約できます。また、MYISAMは全文検索インデックス機能を備えており、LIKEクエリの効率を大幅に最適化できます。

MYISAMは小規模なアプリケーションにしか使えないという人もいますが、それは単なる偏見です。データ量が比較的多い場合は、アーキテクチャをアップグレードして解決する必要があります。例えば、テーブル分割やデータベース分割（シャーディング）などであって、単にストレージエンジンに依存するものではありません。

その他の見解：

現在は一般的にinnodbが選ばれています。主にmyisamのテーブル全体ロック、読み書きの直列化問題、並行処理時のテーブルロックによる効率低下が理由です。myisamは読み書きが集中するアプリケーションでは一般的に選ばれません。

MySQLデータベースのデフォルトのストレージエンジンについて：

MyISAMとInnoDBはMySQLの2つのストレージエンジンです。デフォルトインストールであれば、それはInnoDBのはずです。my.iniファイル内に default-storage-engine=INNODB という記述があります。もちろん、テーブル作成時にストレージエンジンを指定することもできます。show create table xx で対応する情報を確認できます。

MySQLにおけるInnoDBとMyISAMの比較

MyISAM：

各MyISAMはディスク上に3つのファイルとして保存されます。最初のファイル名はテーブル名で始まり、拡張子がファイルタイプを示します。.frmファイルはテーブル定義を格納します。データファイルの拡張子は.MYD（MYData）です。

MyISAMテーブルは圧縮可能で、全文検索をサポートしています。トランザクションはサポートせず、外部キーもサポートしません。トランザクションのロールバックが発生した場合、不完全なロールバックとなり、原子性がありません。update実行時にはテーブルロックが行われ、並行性は比較的低くなります。大量のSELECTを実行する場合は、MyISAMの方が良い選択です。

MyISAMのインデックスとデータは分離されており、インデックスは圧縮されているため、メモリ使用率がかなり向上します。より多くのインデックスをロードできます。一方InnoDBはインデックスとデータが密接に結びついており、圧縮を使用していないため、InnoDBはMyISAMよりもかなりサイズが大きくなります。

MyISAMがメモリにキャッシュするのはインデックスであり、データではありません。一方InnoDBがメモリにキャッシュするのはデータです。相対的に、サーバーのメモリが大きいほど、InnoDBの利点がより発揮されます。

利点：データのクエリが比較的速く、大量のselectに適しており、全文インデックスが可能です。

欠点：トランザクションをサポートせず、外部キーもサポートせず、並行性が低く、大量のupdateには適していません。

InnoDB：

このタイプはトランザクションセーフです。BDBタイプと同じ特性を持ち、外部キーもサポートしています。InnoDBテーブルは非常に高速です。BDBよりも豊富な機能を備えているため、トランザクションセーフなストレージエンジンが必要な場合は、これを使用することをお勧めします。update時には行ロックが行われ、並行性は比較的高くなります。データに対して大量のINSERTまたはUPDATEを実行する場合は、パフォーマンスの観点からInnoDBテーブルを使用すべきです。

利点：トランザクションをサポート、外部キーをサポート、並行性が高く、大量のupdateに適しています。

欠点：データのクエリは比較的速いが、大量のselectには適していません。

トランザクションをサポートするInnoDBタイプのテーブルでは、速度に影響する主な原因は、AUTOCOMMITのデフォルト設定がオンになっており、プログラムが明示的にBEGINを呼び出してトランザクションを開始していないことです。そのため、1件挿入するたびに自動的にコミットされ、速度が著しく低下します。SQLを実行する前にbeginを呼び出し、複数のSQLを1つのトランザクションにまとめる（autocommitがオンでも可能）ことで、パフォーマンスを大幅に向上させることができます。

基本的な違いは次のとおりです：MyISAMタイプはトランザクション処理などの高度な処理をサポートしておらず、InnoDBタイプはサポートしています。

MyISAMタイプのテーブルは性能を重視しており、実行速度はInnoDBタイプよりも高速ですが、トランザクションサポートは提供しません。一方InnoDBはトランザクションサポートや外部キーなどの高度なデータベース機能を提供します。

その他の比較：

MyIASMはIASMテーブルの新しいバージョンであり、以下の拡張があります：

• バイナリレベルの移植性。
• NULLカラムのインデックス。
• 可変長行に対してISAMテーブルよりも断片化が少ない。
• 大容量ファイルをサポート。
• より優れたインデックス圧縮。
• より良いキー統計分布。
• より良く、より高速なauto_increment処理。

以下は、詳細および具体的な実装の違いです：

• 1. InnoDBはFULLTEXTタイプのインデックスをサポートしません。
• 2. InnoDBはテーブルの正確な行数を保存しません。つまり、select count(*) from table を実行すると、InnoDBはテーブル全体をスキャンして行数を計算する必要がありますが、MyISAMは保存されている行数を読み出すだけです。注意すべき点は、count(*)文にwhere条件が含まれる場合、両方のテーブルの操作は同じです。
• 3. AUTO_INCREMENTタイプのフィールドについて、InnoDBではそのフィールドのみを含むインデックスが必要ですが、MyISAMテーブルでは他のフィールドと一緒に複合インデックスを作成できます。
• 4. DELETE FROM table 実行時、InnoDBはテーブルを再構築せず、1行ずつ削除します。
• 5. LOAD TABLE FROM MASTER操作はInnoDBでは機能しません。解決方法は、最初にInnoDBテーブルをMyISAMテーブルに変更し、データをインポートした後にInnoDBテーブルへ戻すことです。ただし、追加のInnoDB機能（外部キーなど）を使用しているテーブルには適用できません。

また、InnoDBテーブルの行ロックも絶対的なものではありません。SQL文を実行する際にMySQLがスキャン範囲を特定できない場合、InnoDBテーブルでも同様にテーブル全体がロックされます。例えば、update table set num=1 where name like "%aaa%" のような場合です。

どのテーブルタイプも万能ではありません。業務タイプに合わせて適切なテーブルタイプを選択することで、初めてMySQLのパフォーマンス上の利点を最大限に発揮できます。

innodbとmyisamの更新比較：

innodbのデータ構成は、主キーに基づいて構築されたB+ツリーです。主キーが明示的に定義されていない場合、innodbはnot null unique keyを主キーとして選択します。それもない場合、innodbは6バイトの主キーを作成します。主キーインデックスはページを指し、具体的な行位置ではありません。

増分ではない主キーは挿入速度を非常に遅くします。例えば、携帯電話番号や身分証番号を主キーとして使用する場合です。そのため、AUTO_INCREMENTを上手に活用してください。

テーブルが大きくても怖くありません。怖いのはcountやオフセットの大きいlimitです。大きなlimitのオフセットを limit max_id, xxxxx のように置き換えることができます。

Limit 0 1000 | limit 1001 1000 | limit 2001 1000

Limit 0 1000 | where id>max_id1 limit 1000 | where id>max_id2 limit 1000

InnoDBにとって、ある列でテーブルを分割しても、単一サーバー上でパフォーマンスを向上させようとすることは意味がありません。

挿入速度とクエリ速度は、時に調和できない矛盾です。

InnoDBはcountに適さないというのは間違いです。MyISAMも同様に遅いのです。ただMyISAMはテーブル全体の行数をキャッシュしているため、テーブル全体のcountは高速です。クエリ条件があり、主キーでのクエリではない場合は、違いはありません。主キーでのcountが遅い理由は、innodbが主キーによって構成されており、主キーでcountする際にデータをロードするためです。

InnoDBのページ式ストレージにより、InnoDBはテーブル全体のキャッシュとホットバックアップをより容易に行えます。

テーブルのインデックスが多い場合、InnoDBの更新速度はMyISAMよりも速くなります。なぜなら、InnoDBのセカンダリインデックスはテーブルの主キーに関連付けられており、これは論理的な値です。一方、MyISAMのすべてのインデックスはデータの物理的な位置に関連付けられており、更新時にデータの物理的な位置が変わる可能性があります。もし変われば、すべてのインデックスを更新する必要があります。

InnoDBはテーブルの正確な行数を保存しません。つまり、select count(*) from table を実行すると、InnoDBはテーブル全体をスキャンして行数を計算する必要がありますが、MyISAMは保存された行数を読み出すだけで済みます。注意すべき点は、count(*)文にwhere条件が含まれる場合、両方のテーブルの操作は同じであることです。

原文アドレス：https://my.oschina.net/junn/blog/183341