MySQL ストレージ エンジンについて話す

MySQL のストレージ エンジンは MySQL アーキテクチャの重要な部分であり、プラグイン ストレージ エンジンは他のデータベースと区別する重要な機能です。これは、MySQL アーキテクチャのサーバー側の最下部にあり、基礎となる物理構造の実装であり、さまざまな技術的な方法でデータをファイルまたはメモリに保存するために使用されます。ストレージ エンジンごとに異なるストレージ メカニズム、インデックス作成手法、およびロックが使用されます。レベル。一般的な MySQL ストレージ エンジンには、InnoDB、MyISAM、Memory、Archive などが含まれます。これらには独自の特性があり、さまざまな特定のアプリケーションに応じて、対応するストレージ エンジン テーブルを確立できます。

さまざまなストレージ エンジンについて説明する前に、いくつかの基本概念を理解する必要があります。

(1) トランザクション

トランザクションは、アトミックな SQL ステートメントのセット、またはデータベースの場合の独立した作業単位です。エンジンがこの一連の SQL ステートメントをデータベースに正常に適用できた場合、そのステートメントは実行されます。クラッシュなどの理由でステートメントのいずれかが実行できない場合、すべてのステートメントは実行されません。つまり、トランザクション内のすべてのステートメントは、成功するか失敗するかのどちらかです。

銀行アプリケーションの典型的な例を見てみましょう:

銀行のデータベースに小切手テーブルと普通預金テーブルの 2 つのテーブルがあるとします。顧客 A は、当座預金口座から普通預金口座に 2,000 元を送金したいと考えています。ステップ:

a. A の当座預金残高が 2,000 元を超えていることを確認します。

c. A の当座預金残高から 2,000 元を追加します。

これら 3 つのステップをトランザクションにパッケージ化する必要があります。いずれかのステップが失敗した場合は、すべてのステップをロールバックする必要があります。そうしないと、銀行の顧客である A は、何らかの問題が発生して、原因不明の 2,000 元を失う可能性があります。これは典型的なトランザクションであり、トランザクション全体のすべての操作が成功するか失敗して、その一部だけを実行することは不可能です。取引。

(2) 読み取りロックと書き込みロック

同時にデータを変更する必要がある SQL が複数ある場合、同時実行制御の問題が発生します。

パブリックメールボックスを仮定すると、ユーザー A がメールボックスを読み、同時にユーザー B がメールボックス内のメールを削除すると、結果はどうなりますか?顧客 A は、読み取り時にエラーで終了するか、一貫性のないメールボックス データを読み取る可能性があります。メールボックスをデータベース内のテーブルとして扱うと、同じ問題があることがわかります。

この種の古典的な問題を解決する方法は同時実行制御です。つまり、同時読み取りまたは同時書き込みを処理する場合、この問題は 2 種類のロックで構成されるロック システムを実装することで解決できます。これら 2 種類のロックは共有ロックと排他ロックであり、読み取りロックや書き込みロックとも呼ばれます。

読み取りロックは共有されます。つまり、複数のクライアントが互いに干渉することなく、同時に同じリソースを読み取ることができます。書き込みロックは排他的です。つまり、書き込みロックが他の書き込みロックと読み取りロックをブロックすることでのみ、特定の時点で 1 人のユーザーのみが書き込みを実行できるようになり、書き込み中の同じリソースを他のユーザーが読み取ることができなくなります。書き込みロックは読み取りロックよりも優先されます。

(3) 行ロックとテーブルロック

実際のデータベースシステムではロックは刻々と発生し、ロックも細分化されます。共有リソースの発行を改善する方法は、ロックをより選択的にすることです。すべてのリソースではなく、変更が必要なデータの一部のみをロックするため、正確なロックが必要です。ただし、ロックを行うと、ロックの取得、ロックが解放されているかどうかの確認、ロックの解放などのリソースも消費されるため、システムのオーバーヘッドが増加します。いわゆるロック戦略は、ロックのオーバーヘッドとデータのセキュリティの間のバランスを見つけることです。このバランスはパフォーマンスにも影響します。

各 MySQL ストレージ エンジンには、独自のロック戦略とロック粒度があります。最も一般的に使用される 2 つの重要なロック戦略は、テーブル ロックと行ロックです。

テーブル ロックは最も低コストの戦略であり、テーブル全体をロックします。ユーザーがテーブルに書き込むときは、最初に書き込みロックを取得する必要があります。これにより、他のユーザーによるテーブルの読み取りおよび書き込み操作がすべてブロックされます。書き込みロックがない場合、他の読み取りユーザーが読み取りロックを取得でき、読み取りロックは相互にブロックしません。行ロックは最大の同時処理をサポートできますが、最大のロック オーバーヘッドももたらします。ロックするのは指定されたレコードのみであり、他のプロセスは同じテーブル内の他のレコードを操作できます。テーブルレベルのロックは高速ですが競合が多く、行レベルのロックは競合は少ないですが低速です。

上記の概念を理解すると、さまざまなストレージ エンジンの違いを明確に区別できます。

InnoDB ストレージ エンジン

MySQL ストレージ エンジンは、公式ストレージ エンジンとサードパーティ ストレージ エンジンに分けることができ、優れたパフォーマンスと自動クラッシュ回復特性を備えた強力なサードパーティ ストレージ エンジンです。 . 現在の MySQL ストレージ エンジンの主流であり、トランザクション ストレージと非トランザクション ストレージの両方で人気があります。

InnoDB ストレージ エンジンは、トランザクション、行ロック、非ロック読み取り、外部キーをサポートします。

特別な理由がない限り、アプリケーションでテーブルを作成するときは、最初に InnoDB の使用を検討できます。 InnoDB も非常に優れたストレージ エンジンであり、時間をかけて詳しく学ぶ価値があります。このストレージ エンジンについては今後研究する予定なので、ここでは詳しく説明しません。

2. MyISAM ストレージ エンジン

MyISAM ストレージ エンジンは、MySQL が公式に提供するストレージ エンジンであり、InnoDB が登場するまでは主流でしたが、主に完成されていないため、徐々に廃止されています。おそらく、MySQL の開発者は、すべてのアプリケーションがトランザクションを必要とするわけではないと考えているため、トランザクションをサポートしないこのストレージ エンジンが存在します。

MyISAM は、トランザクション、行レベルのロック、テーブル ロック、およびフルテキスト インデックスをサポートしていません。最大の欠点は、クラッシュ後に安全に回復できないことです。

MyISAM はシンプルな設計で、データはコンパクトな形式で保存されるため、特定のシナリオではパフォーマンスが非常に優れていますが、すべてのクエリが「ロック」状態になっている場合は、パフォーマンスの問題が発生します。長い間、テーブルロック それが原因です。

したがって、読み取り専用データの場合、またはテーブルが比較的小さく修復操作を許容できる場合は、トランザクションを必要としないアプリケーションの場合でも MyISAM を使用でき、MyISAM ストレージ エンジンを選択すると、より高いパフォーマンスが得られる可能性があります。これは MySQL に付属しています。MyISAM ストレージ エンジンを使用するテーブルは、デフォルトの information_schema ライブラリに存在します。

| TRIGGERS | CREATETEMPORARY TABLE `TRIGGERS` (
  `TRIGGER_CATALOG` varchar(512) NOT NULLDEFAULT '',
  `TRIGGER_SCHEMA` varchar(64) NOT NULL DEFAULT'',
  `TRIGGER_NAME` varchar(64) NOT NULL DEFAULT'',
  `EVENT_MANIPULATION` varchar(6) NOT NULLDEFAULT '',
  `EVENT_OBJECT_CATALOG` varchar(512) NOT NULLDEFAULT '',
  `EVENT_OBJECT_SCHEMA` varchar(64) NOT NULLDEFAULT '',
  `EVENT_OBJECT_TABLE` varchar(64) NOT NULLDEFAULT '',
  `ACTION_ORDER` bigint(4) NOT NULL DEFAULT'0',
  `ACTION_CONDITION` longtext,
  `ACTION_STATEMENT` longtext NOT NULL,
  `ACTION_ORIENTATION` varchar(9) NOT NULLDEFAULT '',
  `ACTION_TIMING` varchar(6) NOT NULL DEFAULT'',
  `ACTION_REFERENCE_OLD_TABLE` varchar(64)DEFAULT NULL,
  `ACTION_REFERENCE_NEW_TABLE` varchar(64)DEFAULT NULL,
  `ACTION_REFERENCE_OLD_ROW` varchar(3) NOTNULL DEFAULT '',
  `ACTION_REFERENCE_NEW_ROW` varchar(3) NOTNULL DEFAULT '',
  `CREATED` datetime DEFAULT NULL,
  `SQL_MODE` varchar(8192) NOT NULL DEFAULT '',
  `DEFINER` varchar(77) NOT NULL DEFAULT '',
  `CHARACTER_SET_CLIENT` varchar(32) NOT NULLDEFAULT '',
  `COLLATION_CONNECTION` varchar(32) NOT NULLDEFAULT '',
  `DATABASE_COLLATION` varchar(32) NOT NULLDEFAULT ''
)ENGINE=MyISAM DEFAULT CHARSET=utf8 |

3. メモリストレージエンジン

メモリストレージエンジンはテーブル内のデータをメモリ上に置くため非常に高速ですが、テーブルロックをサポートしているため、同時実行パフォーマンスが劣ります。ストレージ エンジンが再起動するかデータベースがクラッシュすると、テーブル内のすべてのデータが失われます。このストレージ エンジンは、通常、クエリの中間結果セットを保存するために MySQL で使用されます。 MySQL に付属するデフォルトの information_schema ライブラリとして、メモリ ストレージ エンジンを使用するテーブルが多数あります。

|TABLES | CREATE TEMPORARY TABLE `TABLES` (
  `TABLE_CATALOG` varchar(512) NOT NULL DEFAULT'',
  `TABLE_SCHEMA` varchar(64) NOT NULL DEFAULT'',
  `TABLE_NAME` varchar(64) NOT NULL DEFAULT '',
  `TABLE_TYPE` varchar(64) NOT NULL DEFAULT '',
  `ENGINE` varchar(64) DEFAULT NULL,
  `VERSION` bigint(21) unsigned DEFAULT NULL,
  `ROW_FORMAT` varchar(10) DEFAULT NULL,
  `TABLE_ROWS` bigint(21) unsigned DEFAULTNULL,
  `AVG_ROW_LENGTH` bigint(21) unsigned DEFAULTNULL,
  `DATA_LENGTH` bigint(21) unsigned DEFAULTNULL,
  `MAX_DATA_LENGTH` bigint(21) unsigned DEFAULTNULL,
  `INDEX_LENGTH` bigint(21) unsigned DEFAULTNULL,
  `DATA_FREE` bigint(21) unsigned DEFAULT NULL,
  `AUTO_INCREMENT` bigint(21) unsigned DEFAULTNULL,
  `CREATE_TIME` datetime DEFAULT NULL,
  `UPDATE_TIME` datetime DEFAULT NULL,
  `CHECK_TIME` datetime DEFAULT NULL,
  `TABLE_COLLATION` varchar(32) DEFAULT NULL,
  `CHECKSUM` bigint(21) unsigned DEFAULT NULL,
  `CREATE_OPTIONS` varchar(255) DEFAULT NULL,
  `TABLE_COMMENT` varchar(2048) NOT NULLDEFAULT ''
) ENGINE=MEMORY DEFAULT CHARSET=utf8|

4. アーカイブ ストレージ エンジン

アーカイブ ストレージ エンジンは、INSERT 操作と SELECT 操作のみをサポートし、行ロックをサポートしますが、その最大の利点は、優れた圧縮機能を備えていることです。圧縮率は通常 1:10 に達し、より少ないディスク容量で同じデータを保存できます。

アーカイブ ストレージ エンジンは、履歴データ、ログ情報データなどのアーカイブ データの保存に非常に適しています。この種のデータには非常に大量のデータが含まれることが多く、基本的にこのストレージ エンジンを使用する場合は INSERT 操作と SELECT 操作のみが必要です。ディスク容量を大幅に節約できます。

例として、特定のライブラリに 2 億 5,000 万件のレコードがある履歴テーブルを取り上げます。

mysql> select TABLE_ROWSfrom TABLES where TABLE_NAME='history';
+------------+
| TABLE_ROWS |
+------------+
|  251755162 |
+------------+
1 row in set (0.01 sec)

元々 InnoDB ストレージ エンジンにデフォルト設定されていたとき、テーブル サイズは 12G でした。

mysql> select concat(round(sum(DATA_LENGTH/1024/1024), 2),'MB')as  
data from TABLES where TABLE_NAME='history';
+------------+
| data       |
+------------+
| 12918.88MB |
+------------+
1 row in set (0.00 sec)

アーカイブ ストレージ エンジンを使用して上記のテーブルを再構築し、同じデータを再挿入すると、テーブルのサイズは 2G 未満になり、ストレージの圧縮率が良好であることがわかります。

他のストレージ エンジンはあまり一般的に使用されないため、ここでは説明しません。

上記は MySQL ストレージ エンジンのコンテンツに関するものです。その他の関連コンテンツについては、PHP 中国語 Web サイト (www.php.cn) に注目してください。