MySQL ストレージ エンジンの詳細な紹介

ストレージエンジンとは何ですか？

リレーショナルデータベーステーブルは、情報を保存および整理するために使用されるデータ構造であり、Excel スプレッドシートの形式に似た、行と列で構成されるテーブルとして理解できます。単純なテーブルもあれば、複雑なテーブルもあり、長期データの保存にはまったく使用されないテーブルもあり、読み取りは非常に高速ですが、実際の開発プロセスでデータを挿入する際には非常に不十分なテーブルもあります。テーブルが異なるということは、異なるタイプのデータが格納されることを意味し、データ処理に違いが生じます。 MySQL の場合、MySQL の強力な機能を最大限に活用できるように、データ処理のニーズに応じてさまざまなストレージ エンジンを選択できます。このブログ記事では、各エンジンの特性と適用される状況を要約および分析するものであり、より深い内容には絡みません。私の学習方法は、まず使い方を学び、使い方を知り、次に使い方を見つけることです。以下は、MySQL でサポートされるストレージ エンジンの簡単な紹介です。

MySQL 5.5 以降は、デフォルトで InnoDB ストレージ エンジンを使用します。InnoDB と BDB はトランザクション セーフ テーブルを提供しますが、他のストレージ エンジンは非トランザクション セーフ テーブルです。

デフォルトのエンジンを変更するには、構成ファイルのdefault-storage-engineを変更できます。 showvariableslike'default_storage_engine'; を使用すると、現在のデータベースをデフォルト エンジンで表示できます。コマンド: showengines および showvariableslike'have%' は、現在のデータベースでサポートされているエンジンを一覧表示できます。 「値」が「無効」と表示されているレコードは、データベースがこのエンジンをサポートしており、データベースの起動時に無効になっていることを示します。 MySQL5.1 以降では、INFORMATION_SCHEMA データベースに ENGINES テーブルがあり、これが提供する情報は showengines ステートメントとまったく同じです。selectenginefrominformation_chema.engineswheretransactions='yes ステートメントを使用してクエリできます。 ';

はい Engine キーワードを使用して、データベースの作成または変更時に使用するエンジンを指定します。

主なストレージエンジン: MyISAM、InnoDB、MEMORY、MERGE はじめに:

テーブルを作成するとき、engine=... または type=.... を通じて使用するエンジンを指定します。 showtablestatusfromDBname は、指定されたテーブルをエンジンに表示します。

(1) MyISAM

特にアクセス速度が速く、トランザクションの整合性の要件がないため、基本的にこのエンジンはテーブルの作成に使用できます。

各 MyISAM はディスク上に 3 つのファイルとして保存され、ファイル名とテーブル名は同じですが、拡張子は次のとおりです:

.frm (ストレージテーブル定義)

MYD (MYData、格納データ)

MYI ( MYIndex、ストレージインデックス)

データファイルとインデックスファイルを異なるディレクトリに配置することで、IOを均等に分散し、より高速な速度を得ることができます。データ ファイルとインデックス ファイルのパスを指定するには、テーブルの作成時に DATADIRECTORY ステートメントと INDEXDIRECTORY ステートメントを使用して指定する必要があります。ファイル パスには絶対パスを使用する必要があります。

各MyISAMテーブルにはフラグがあり、MyISAMデータテーブルをチェックするときにサーバーまたはmyisamchkプログラムによって設定されます。 MyISAM テーブルには、データ テーブルが最後の使用後に正常に閉じられたかどうかを示すフラグもあります。サーバーがダウンまたはクラッシュした場合、このフラグを使用して、データ テーブルをチェックして修復する必要があるかどうかを判断できます。このチェックを自動的に実行したい場合は、サーバーの起動時に --myisam-recover 現象を使用できます。これにより、サーバーは MyISAM データ テーブルを開くたびにテーブルのフラグを自動的にチェックし、必要な修復を行うようになります。 MyISAM タイプのテーブルが破損している可能性があります。CHECKTABLE ステートメントを使用して MyISAM テーブルの状態を確認し、REPAIRTABLE ステートメントを使用して破損した MyISAM テーブルを修復できます。

MyISAM テーブルは 3 つの異なるストレージ形式もサポートしています:

静的 (固定長) テーブル

動的テーブル

圧縮テーブル

静的テーブルはデフォルトのストレージ形式です。静的テーブルのフィールドはすべて非可変長フィールドであるため、各レコードは固定長になります。この格納方法の利点は、保存が非常に速く、キャッシュが容易で、障害からの回復が容易であることです。通常、動的テーブルよりも多くのスペースを必要とするということです。静的テーブルは、データが格納されるときに列定義の幅定義に従ってスペースを埋めますが、これらのスペースはアクセス時に取得されず、アプリケーションに返される前に削除されます。同時に、次の点にも注意してください。場合によっては、フィールドの後にスペースを返す必要がある場合があります。この形式を使用すると、後続のスペースは自動的に処理されます。

動的テーブルには可変長フィールドが含まれており、レコードは固定長ではありません。このようなストレージの利点は、使用するスペースが少ないことですが、レコードの頻繁な更新と削除により断片化が発生するため、定期的に OPTIMIZETABLE ステートメントを実行する必要があります。または myisamchk-r コマンドを使用してパフォーマンスを向上させます。障害が発生した場合、リカバリは比較的困難です。

圧縮テーブルは myisamchk ツールによって作成され、非常に小さなスペースを占めます。各レコードが個別に圧縮されるため、アクセスのオーバーヘッドは非常にわずかです。

(2) InnoDB

InnoDB は、堅牢なトランザクション ストレージ エンジンであり、多くのインターネット企業で使用されており、ユーザーが非常に大規模なデータ ストレージを操作するための強力なソリューションを提供します。 MySQL バージョン 5.6.13 がコンピューターにインストールされており、InnoDB がデフォルトのストレージ エンジンです。 InnoDB では、行レベルのロックと外部キー制約も導入されています。次の状況では、InnoDB を使用することが最も理想的な選択です:

1. 集中的なテーブルを更新します。 InnoDB ストレージ エンジンは、複数の同時更新リクエストの処理に特に適しています。

2.事務。 InnoDB ストレージ エンジンは、トランザクションをサポートする標準の MySQL ストレージ エンジンです。

3. 自動災害復旧。他のストレージ エンジンとは異なり、InnoDB テーブルは災害から自動的に回復できます。

4. 外部キー制約。 MySQL で外部キーをサポートする唯一のストレージ エンジンは InnoDB です。

5. 列の AUTO_INCREMENT 属性の自動追加をサポートします。

InnoDB ストレージ エンジンは、コミット、ロールバック、クラッシュ回復機能を備えたトランザクション セキュリティを提供します。ただし、MyISAM のストレージ エンジンと比較すると、InnoDB の書き込み処理効率は低く、データとインデックスを保持するためにより多くのディスク領域を消費します。

1) 自動拡張カラム:

InnoDB テーブルの自動拡張カラムは手動で挿入できますが、挿入された値が空または 0 の場合、実際に挿入される値は自動拡張後の値になります。自動拡張値の開始値は、「ALTERTABLE...AUTO_INCREMENT=n;」ステートメントによって強制的に設定できます。デフォルト値は 1 ですが、強制されたデフォルト値はメモリに保存され、データベースの再起動後に失われます。 。 LAST_INSERT_ID() を使用すると、現在のスレッドが前回レコードを挿入するために使用した値をクエリできます。複数のレコードが一度に挿入された場合、最初のレコードで使用された自動インクリメント値が返されます。

InnoDB テーブルの場合、自動拡張カラムはインデックスである必要があります。複合インデックスの場合は、複合インデックスの最初の列でもある必要があります。ただし、MyISAM テーブルの場合は、レコードの挿入後に自動拡張列を複合インデックスの他の列にすることができます。成長列は、前の列への組み合わせインデックスに従って増加します。

2) 外部キーの制約:

MySQL で外部キーをサポートする唯一のストレージ エンジンは InnoDB です。外部キーを作成する場合、親テーブルには対応するインデックスが必要で、子テーブルは作成時に対応するインデックスを自動的に作成します。外部キー。

インデックスを作成するとき、親テーブルを削除または更新するときに、子テーブルで対応する操作 (restrict、cascade、setnull、noaction など) を指定できます。このうち、restrict と noaction は同じです。これは、子テーブルが関連付けられている場合は親テーブルを更新できないことを意味します。casecade は、親テーブルが更新または削除されると、子テーブルに対応するレコードが更新または削除されることを意味します。 setnull は、親テーブルが更新または削除されることを意味します。削除すると、サブテーブルの対応するフィールドが setnull になります。

別のテーブルが作成した外部キ​​ーによってテーブルが参照されている場合、テーブルの対応するインデックスや主キーの削除は禁止されます。

setforeign_key_checks=0; を使用して外部キー制約を一時的にオフにし、setforeign_key_checks=1; を使用して制約をオンにすることができます。

(3) MEMORY

メモリは、メモリに格納されている内容を使ってテーブルを作成します。各 MEMORY テーブルは実際にはディスク ファイルに対応しており、その形式は .frm です。 MEMORY タイプのテーブル アクセスは、データがメモリに配置され、デフォルトで HASH インデックスが使用されるため、非常に高速です。ただし、サーバーがシャットダウンされると、テーブル内のデータは失われますが、テーブルは引き続き存在します。

デフォルトでは、メモリ データ テーブルはハッシュ インデックスを使用します。このインデックスを使用して「等価比較」を実行すると非常に高速になりますが、「範囲比較」は非常に遅くなります。したがって、ハッシュ インデックス値は、「=」および「」演算子での使用には適していません。内部のorderby句で使用します。本当に「」演算子を使用したい場合は、btree インデックスを使用して処理を高速化できます。

MEMORYデータテーブルに格納されるデータ行は固定長形式を使用するため、処理を高速化します。これは、BLOBやTEXTなどの可変長データ型を使用できないことを意味します。 VARCHAR は可変長型ですが、MySQL 内部では固定長の CHAR 型として扱われるため使用可能です。

createtabletab_memoryengine=memoryselectid,name,age,addrfrommanorderbyid;

インデックスの指定にはUSINGHASH/BTREEを使用します。

createindexmem_hashusinghashontab_memory(city_id);

MySQL サービスを開始するときに --init-file オプションを使用し、insertinto...select やloaddatainfile などのステートメントをこのファイルに配置して、サービスの開始時に永続化して安定させることができます。テーブルのデータ ソースへのロードが開始されます。

サーバーには、同時に使用される MEMORY テーブルを維持するのに十分なメモリが必要です。MEMORY テーブルが使用されなくなった場合、MEMORY テーブルによって占有されているメモリを解放するには、DELETEFROM または truncatetable を実行するか、テーブル全体を削除する必要があります。 。

各 MEMORY テーブルに配置されるデータの量は、max_heap_table_size システム変数によって制限されます。このシステム変数の初期値は 16M です。同時に、MEMORY テーブルを作成するときに MAX_ROWS 句を使用して最大数を指定できます。テーブル内の行数。

MySQL Memory ストレージ エンジンを使用するための出発点は速度です。最速の応答時間を得るために、論理記憶媒体としてシステム メモリが使用されます。テーブル データをメモリに保存すると高いパフォーマンスが得られますが、mysqld デーモンがクラッシュすると、すべてのメモリ データが失われます。速度が向上すると、いくつかの欠点も生じます。 Memory データ テーブルに格納されるデータは定長形式を使用する必要があります。つまり、BLOB や TEXT などの可変長データ型は可変長型ですが、MySQL が内部的に扱うため、使用できません。固定長のCHAR型として使用できます。

(4) MERGE

マージストレージエンジンは一連の MyISAM テーブルの構造がまったく同じである必要があります。MERGE タイプのテーブルにはクエリを実行できません。 、更新、および削除 これらの操作は実際には内部 MyISAM テーブルに対して実行されます。 MERGE テーブルへの挿入操作の場合、挿入されるテーブルは 3 つの異なる値を持つことができ、それに応じて最初または最後のテーブルに挿入操作が実行されます。この句を使用するか、NO に設定して、MERGE テーブルを挿入できないことを示します。 MERGE テーブルに対してドロップ操作を実行できます。この操作は MERGE テーブルの定義のみを削除し、内部テーブルには影響しません。 MERGE は、MERGE テーブル名で始まる 2 つのファイルをディスク上に保持します。.frm ファイルにはテーブルの定義が保存され、.MRG ファイルには、MERGE テーブルがどのテーブルで構成されているか、およびその基礎が含まれる、結合されたテーブルに関する情報が含まれます。データを挿入しています。 MERGE テーブルは .MRG ファイルを変更することで変更できますが、変更後には flashtable を使用して更新する必要があります。

createtableman_all(idint,namevarchar(20))engine=mergeunion=(man1,man2)insert_methos=last;

以上がMySQL ストレージ エンジンの詳細な紹介の詳細内容です。詳細については、PHP 中国語 Web サイトの他の関連記事を参照してください。