MySQL常用ストレージエンジン機能と使い方の詳細
7194 ワード
この例では、MySQLの一般的なストレージエンジンの機能と使い方について説明します.皆さんの参考にしてください.具体的には以下の通りです.
MySQLストレージエンジンには主に2つの種類があります.
1.トランザクション・セキュリティ・テーブル:InnoDB、BD.
2.非取引安全表:MyISAM、MEMORY、MERGE、EXAMPLE、NDB Cluster、ARCHIVE、CSV、BLACKHOLE、FEDERATEDなど.
MySQLのデフォルトのストレージエンジンはMyISAM(バージョン5.7ではInnoDB)です.
プロファイルでデフォルトのストレージエンジンのパラメータを設定します:default-table-type.
現在のデータベースでサポートされているストレージエンジンを問い合せます.
現在のデフォルトのストレージエンジンを表示するには、次の手順に従います.
新しいテーブルの作成時にストレージエンジンを指定します.
以下では、MyISAM、InnoDB、MEMORY、MERGEの4つの比較的一般的なストレージエンジンについて詳しく説明します.
一、MyISAM
1.データファイル:
MyISAMデータテーブルは、ディスクに3つのファイルに格納されます.ファイル名はテーブル名と同じです.拡張子は次のとおりです.
(1).frm:データテーブル構造定義を格納します.
(2).MYD:テーブルデータを格納します.
(3).MYI:テーブルインデックスを格納します.
ここで、データファイルとインデックスファイルは異なるディレクトリに配置でき、IOを平均的に分布し、より速い速度を得ることができます.インデックスファイルとデータファイルのパスを指定します.テーブルを作成するときにdata directory文とindex directory文で指定する必要があります.(ファイルパスには絶対パスでアクセス権が必要)
MyISAMタイプのテーブルは破損する可能性があります.原因は様々ですが、破損したテーブルはアクセスできない可能性があります.修復が必要か、アクセス後にエラーの結果を返す必要があることを示します.check table文を使用してMyISAMテーブルの健康を確認し、破損したMyISAMテーブルをrepair table文で修復できます.
2. 保存形式:
(1)静的テーブル(デフォルト):フィールドは非長くなります(各レコードは固定長です).ストレージは非常に迅速で、キャッシュしやすく、障害が発生して回復しやすい.通常、ダイナミックテーブルよりもスペースがかかります.
(2)ダイナミックテーブル:占有スペースは比較的少ないが,頻繁な更新削除レコードは断片化し,optimize tableやmyisamchk-rコマンドを定期的に実行して性能を改善する必要があり,障害が発生した場合のリカバリが困難である.
(3)圧縮テーブル:myisampackツールを使用して作成され、非常に小さなディスク領域を占有します.各レコードは単独で圧縮されるため、非常に小さなアクセス支出しかありません.
静的テーブルのデータは、格納時に列の幅に従って補完スペースを定義し、データが適用される前に削除されます.保存する必要がある内容の後ろにもともとスペースがある場合は、結果を返すときに削除されます.(実はデータ型charの動作で、ダイナミックテーブルにこのデータ型があれば同じ問題があります)
(静的テーブルと動的テーブルは、使用中のカラムのタイプに応じて自動的に選択されます.)
3. メリットとデメリット:
(1)メリット:アクセス速度が速い.
(2)トランザクションはサポートされておらず,外部キーもサポートされていない.
4. 適用状況:
アプリケーションが読み取り操作と挿入操作を主とし、更新と削除操作が少なく、トランザクションの整合性、同時性の要求が高くない場合は、このストレージエンジンを選択するのが適切です.MyISAMは、Web、データウェアハウス、その他のアプリケーション環境で最もよく使用されるストレージエンジンの1つです.
二、InnoDB
1.記憶方式:
InnoDBストレージ・テーブルとインデックスには、次の2つの方法があります.
(1)共有表領域を用いる記憶:このようにして作成する表構造は.frmファイルでは、innodb_にデータとインデックスが保存されます.data_home_dirとinnodb_data_file_pathで定義された表領域は、複数のファイルであってもよい.
(2)多表領域記憶を用いる:このようにして作成する表構造は依然として存在する.frmファイルには、各テーブルのデータとインデックスが個別に保存する.idbファイルにあります.パーティションテーブルの場合、各パーティションは個別に対応する.idbファイルは、ファイル名が「テーブル名+パーティション名」であり、パーティションの作成時に各パーティションのデータファイルの場所を指定して、テーブルのIOを複数のディスクに均一に分散させることができます.
マルチテーブルスペースの格納方法を使用するには、パラメータinnodb_を設定する必要があります.file_per_tableはサーバを再起動してから有効になり、新しいテーブルのみ有効になります.マルチ表領域のデータファイルにはサイズ制限はなく、初期サイズを設定する必要もなく、ファイルの最大制限、拡張サイズなどのパラメータを設定する必要もありません.マルチテーブルスペースの格納方式においても共有テーブルスペースは必須であり、InnoDBはこのファイルに内部データ辞書とワークログを格納ので、マルチテーブルスペース特性を用いたテーブルをバックアップする際に直接コピーする.idbファイルはだめです.コマンドでデータのバックアップをデータベースに復元できます.
ただし、テーブルの元のデータベースにのみリカバリできます.他のデータベースにリカバリする必要がある場合はmysqldumpとmysqlimportで実装する必要があります.
2.データファイル:
InnoDBのデータファイルはテーブルの格納方式により決定される.
(1)共有表領域ファイル:パラメータinnodb_data_home_dirとinnodb_data_file_path定義は、データ辞書やログなどを格納するために使用されます.
(2).frm:テーブル構造定義を格納します.
(3).idb:マルチテーブルスペースストレージ方式を使用する場合、テーブルデータとインデックスを格納するために使用され、共有テーブルスペースストレージを使用する場合、このファイルはありません.
3.外部キー制約:
InnoDBはMySQLの外部キー制約をサポートする唯一のエンジンです.外部キー制約により、データベース自体が外部キーを介してデータの整合性と一貫性を保証できますが、外部キーを導入すると速度とパフォーマンスが低下します.外部キーを作成するときは、親テーブルに対応するインデックスが必要で、サブテーブルは外部キーを作成するときに自動的に対応するインデックスを作成します.
外部キーコンストレイントの使用例:
KEY:索引制約名を定義する.
CONTRAINT:外部キー制約名を定義します.(データベース内で一意であるべきで、システムを指定しないと自動的に制約名が生成されます)
ON:親テーブル操作が子テーブルに与える影響を指定します(デフォルトはrestrictを使用しません).
Restrictとno action:サブテーブルに関連レコードがある場合、親テーブルは更新または削除できません.
Cascade:親テーブルの更新または削除時に、サブテーブルに対応するレコードを更新または削除します.
Set null:親テーブルが更新または削除されると、サブテーブルの対応するフィールドがnullに設定されます.
テーブルが他のテーブルによって外部キー参照が作成されると、そのテーブルの対応するインデックスまたはプライマリ・キーは削除されません.複数のテーブルのデータをインポートする場合、テーブルのインポート順序を無視する必要がある場合は、外部キーのチェックを一時的に閉じることができます.load dataとalter table操作を実行する場合は、外部キーコンストレイントを一時的に閉じることで処理の速度を速めることもできます.
閉じるコマンド:
オープンコマンド:
4.優劣:
(1)メリット:コミット、ロールバック、クラッシュ・リカバリ機能を備えたトランザクション・セキュリティを提供します.
(2)劣勢:MyISAMよりもInnoDB書き込みの処理効率が悪く、データとインデックスを保持するためにより多くのディスク領域を占有する.
5.適用状況:
アプリケーションがトランザクションの整合性に対して比較的高い要求があり、同時条件の下でデータの整合性を要求し、データ操作が挿入とクエリーのほかに多くの更新、削除操作を含む場合、InnoDBストレージエンジンは比較的適切な選択であるべきである.InnoDBストレージエンジンは、削除や更新によるロックを効果的に低減するだけでなく、トランザクションの完全なコミットとロールバックを確保することができ、課金システムや財務システムのようなデータの正確性に対する要求が高いシステムでは、InnoDBが適切です.
三、MEMORY
1.データファイル:
各MEMORYテーブルは1つのみ対応する.frmディスクファイル.テーブルの構造定義を格納し、テーブルデータをメモリに格納します.デフォルトでは、BTREEインデックスではなくHASHインデックスが使用されます.
2.優劣:
(1)利点:データがメモリに存在するため、アクセス速度が非常に速い.
(2)劣勢:サービスがオフになると、テーブルのデータが失われる.テーブルのサイズに制限があります.
3.適用状況:
Memoryストレージエンジンは、主にコンテンツの変化が頻繁ではないコードテーブル、または統計操作の中間結果テーブルとして使用され、中間結果を効率的に分析し、最終的な統計結果を得るのに便利である.
四、MERGE
1.エンジン原理:
Mergeストレージエンジンは、MyISAMテーブルのセットであり、これらのMyISAMテーブルは構造が完全に同じでなければならない.mergeテーブル自体にはデータがなく、mergeタイプのテーブルに対してクエリー、更新、削除の操作を行うことができる.これらの操作は、実際には内部の実際のMyISAMテーブルに対して行われる.
insert_を介してmethod句はmergeテーブルの挿入操作を定義します:firstまたはlastを使用して、挿入操作を第1または最後のテーブルに相応に作用させることができます.定義または定義しないNoは、このmergeテーブルを挿入操作できないことを示します.mergeテーブルのdrop操作はmergeの定義を削除しただけで、内部のテーブルには何の影響もありません.
2.データファイル:
(1).frm:テーブル定義を格納します.
(2).MRG:mergeテーブルがどのテーブルで構成されているか、新しいデータを挿入する際の根拠を含む組合せテーブルの情報を格納します.修正できます.mrgファイルはmergeテーブルを変更しますが、変更後はflush tablesでリフレッシュします.
3.使用例:
4.適用状況:
一連の同等のMyISAMテーブルを論理的に結合し、オブジェクトとして参照するために使用されます.MERGEテーブルの利点は、単一のMyISAMテーブルサイズの制限を突破し、異なるテーブルを複数のディスクに分散することで、MERGEテーブルのアクセス効率を効果的に改善できることです.これは、データストアなどのVLDB環境に適している.
MySQLについてもっと兴味のある読者は、「MySQLストレージプロセステクニック大全」、「MySQL常用関数大概」、「MySQLログ操作テクニック大全」、「MySQLトランザクション操作テクニック大概」および「MySQLデータベースロックに関するテクニック汇总」を参照してください.
本明細書では、MySQLデータベース・メーターについて説明します.
MySQLストレージエンジンには主に2つの種類があります.
1.トランザクション・セキュリティ・テーブル:InnoDB、BD.
2.非取引安全表:MyISAM、MEMORY、MERGE、EXAMPLE、NDB Cluster、ARCHIVE、CSV、BLACKHOLE、FEDERATEDなど.
MySQLのデフォルトのストレージエンジンはMyISAM(バージョン5.7ではInnoDB)です.
プロファイルでデフォルトのストレージエンジンのパラメータを設定します:default-table-type.
現在のデータベースでサポートされているストレージエンジンを問い合せます.
show engines;
show variables like 'have%';
現在のデフォルトのストレージエンジンを表示するには、次の手順に従います.
show variables like '%table_type%';
新しいテーブルの作成時にストレージエンジンを指定します.
create table(...) engine=MyISAM;
以下では、MyISAM、InnoDB、MEMORY、MERGEの4つの比較的一般的なストレージエンジンについて詳しく説明します.
一、MyISAM
1.データファイル:
MyISAMデータテーブルは、ディスクに3つのファイルに格納されます.ファイル名はテーブル名と同じです.拡張子は次のとおりです.
(1).frm:データテーブル構造定義を格納します.
(2).MYD:テーブルデータを格納します.
(3).MYI:テーブルインデックスを格納します.
ここで、データファイルとインデックスファイルは異なるディレクトリに配置でき、IOを平均的に分布し、より速い速度を得ることができます.インデックスファイルとデータファイルのパスを指定します.テーブルを作成するときにdata directory文とindex directory文で指定する必要があります.(ファイルパスには絶対パスでアクセス権が必要)
MyISAMタイプのテーブルは破損する可能性があります.原因は様々ですが、破損したテーブルはアクセスできない可能性があります.修復が必要か、アクセス後にエラーの結果を返す必要があることを示します.check table文を使用してMyISAMテーブルの健康を確認し、破損したMyISAMテーブルをrepair table文で修復できます.
2. 保存形式:
(1)静的テーブル(デフォルト):フィールドは非長くなります(各レコードは固定長です).ストレージは非常に迅速で、キャッシュしやすく、障害が発生して回復しやすい.通常、ダイナミックテーブルよりもスペースがかかります.
(2)ダイナミックテーブル:占有スペースは比較的少ないが,頻繁な更新削除レコードは断片化し,optimize tableやmyisamchk-rコマンドを定期的に実行して性能を改善する必要があり,障害が発生した場合のリカバリが困難である.
(3)圧縮テーブル:myisampackツールを使用して作成され、非常に小さなディスク領域を占有します.各レコードは単独で圧縮されるため、非常に小さなアクセス支出しかありません.
静的テーブルのデータは、格納時に列の幅に従って補完スペースを定義し、データが適用される前に削除されます.保存する必要がある内容の後ろにもともとスペースがある場合は、結果を返すときに削除されます.(実はデータ型charの動作で、ダイナミックテーブルにこのデータ型があれば同じ問題があります)
(静的テーブルと動的テーブルは、使用中のカラムのタイプに応じて自動的に選択されます.)
3. メリットとデメリット:
(1)メリット:アクセス速度が速い.
(2)トランザクションはサポートされておらず,外部キーもサポートされていない.
4. 適用状況:
アプリケーションが読み取り操作と挿入操作を主とし、更新と削除操作が少なく、トランザクションの整合性、同時性の要求が高くない場合は、このストレージエンジンを選択するのが適切です.MyISAMは、Web、データウェアハウス、その他のアプリケーション環境で最もよく使用されるストレージエンジンの1つです.
二、InnoDB
1.記憶方式:
InnoDBストレージ・テーブルとインデックスには、次の2つの方法があります.
(1)共有表領域を用いる記憶:このようにして作成する表構造は.frmファイルでは、innodb_にデータとインデックスが保存されます.data_home_dirとinnodb_data_file_pathで定義された表領域は、複数のファイルであってもよい.
(2)多表領域記憶を用いる:このようにして作成する表構造は依然として存在する.frmファイルには、各テーブルのデータとインデックスが個別に保存する.idbファイルにあります.パーティションテーブルの場合、各パーティションは個別に対応する.idbファイルは、ファイル名が「テーブル名+パーティション名」であり、パーティションの作成時に各パーティションのデータファイルの場所を指定して、テーブルのIOを複数のディスクに均一に分散させることができます.
マルチテーブルスペースの格納方法を使用するには、パラメータinnodb_を設定する必要があります.file_per_tableはサーバを再起動してから有効になり、新しいテーブルのみ有効になります.マルチ表領域のデータファイルにはサイズ制限はなく、初期サイズを設定する必要もなく、ファイルの最大制限、拡張サイズなどのパラメータを設定する必要もありません.マルチテーブルスペースの格納方式においても共有テーブルスペースは必須であり、InnoDBはこのファイルに内部データ辞書とワークログを格納ので、マルチテーブルスペース特性を用いたテーブルをバックアップする際に直接コピーする.idbファイルはだめです.コマンドでデータのバックアップをデータベースに復元できます.
ALTER TABLE tbl_name DISCARD TABLESPACE;
ALTER TABLE tbl_name IMPORT TABLESPACE;
ただし、テーブルの元のデータベースにのみリカバリできます.他のデータベースにリカバリする必要がある場合はmysqldumpとmysqlimportで実装する必要があります.
2.データファイル:
InnoDBのデータファイルはテーブルの格納方式により決定される.
(1)共有表領域ファイル:パラメータinnodb_data_home_dirとinnodb_data_file_path定義は、データ辞書やログなどを格納するために使用されます.
(2).frm:テーブル構造定義を格納します.
(3).idb:マルチテーブルスペースストレージ方式を使用する場合、テーブルデータとインデックスを格納するために使用され、共有テーブルスペースストレージを使用する場合、このファイルはありません.
3.外部キー制約:
InnoDBはMySQLの外部キー制約をサポートする唯一のエンジンです.外部キー制約により、データベース自体が外部キーを介してデータの整合性と一貫性を保証できますが、外部キーを導入すると速度とパフォーマンスが低下します.外部キーを作成するときは、親テーブルに対応するインデックスが必要で、サブテーブルは外部キーを作成するときに自動的に対応するインデックスを作成します.
外部キーコンストレイントの使用例:
CREATE TABLE `dep` (
`id` smallint(6) NOT NULL AUTO_INCREMENT,
`name` varchar(20) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
CREATE TABLE `emp` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`name` varchar(20) DEFAULT NULL,
`dep_id` smallint(6) NOT NULL,
PRIMARY KEY (`id`),
KEY `idx_fk_dep_id` (`dep_id`),
CONSTRAINT `fk_emp_dep` FOREIGN KEY (`dep_id`) REFERENCES `dep` (`id`) ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
KEY:索引制約名を定義する.
CONTRAINT:外部キー制約名を定義します.(データベース内で一意であるべきで、システムを指定しないと自動的に制約名が生成されます)
ON:親テーブル操作が子テーブルに与える影響を指定します(デフォルトはrestrictを使用しません).
Restrictとno action:サブテーブルに関連レコードがある場合、親テーブルは更新または削除できません.
Cascade:親テーブルの更新または削除時に、サブテーブルに対応するレコードを更新または削除します.
Set null:親テーブルが更新または削除されると、サブテーブルの対応するフィールドがnullに設定されます.
テーブルが他のテーブルによって外部キー参照が作成されると、そのテーブルの対応するインデックスまたはプライマリ・キーは削除されません.複数のテーブルのデータをインポートする場合、テーブルのインポート順序を無視する必要がある場合は、外部キーのチェックを一時的に閉じることができます.load dataとalter table操作を実行する場合は、外部キーコンストレイントを一時的に閉じることで処理の速度を速めることもできます.
閉じるコマンド:
set foreign_key_checks=0;
オープンコマンド:
set foreign_key_checks=1;
4.優劣:
(1)メリット:コミット、ロールバック、クラッシュ・リカバリ機能を備えたトランザクション・セキュリティを提供します.
(2)劣勢:MyISAMよりもInnoDB書き込みの処理効率が悪く、データとインデックスを保持するためにより多くのディスク領域を占有する.
5.適用状況:
アプリケーションがトランザクションの整合性に対して比較的高い要求があり、同時条件の下でデータの整合性を要求し、データ操作が挿入とクエリーのほかに多くの更新、削除操作を含む場合、InnoDBストレージエンジンは比較的適切な選択であるべきである.InnoDBストレージエンジンは、削除や更新によるロックを効果的に低減するだけでなく、トランザクションの完全なコミットとロールバックを確保することができ、課金システムや財務システムのようなデータの正確性に対する要求が高いシステムでは、InnoDBが適切です.
三、MEMORY
1.データファイル:
各MEMORYテーブルは1つのみ対応する.frmディスクファイル.テーブルの構造定義を格納し、テーブルデータをメモリに格納します.デフォルトでは、BTREEインデックスではなくHASHインデックスが使用されます.
2.優劣:
(1)利点:データがメモリに存在するため、アクセス速度が非常に速い.
(2)劣勢:サービスがオフになると、テーブルのデータが失われる.テーブルのサイズに制限があります.
3.適用状況:
Memoryストレージエンジンは、主にコンテンツの変化が頻繁ではないコードテーブル、または統計操作の中間結果テーブルとして使用され、中間結果を効率的に分析し、最終的な統計結果を得るのに便利である.
四、MERGE
1.エンジン原理:
Mergeストレージエンジンは、MyISAMテーブルのセットであり、これらのMyISAMテーブルは構造が完全に同じでなければならない.mergeテーブル自体にはデータがなく、mergeタイプのテーブルに対してクエリー、更新、削除の操作を行うことができる.これらの操作は、実際には内部の実際のMyISAMテーブルに対して行われる.
insert_を介してmethod句はmergeテーブルの挿入操作を定義します:firstまたはlastを使用して、挿入操作を第1または最後のテーブルに相応に作用させることができます.定義または定義しないNoは、このmergeテーブルを挿入操作できないことを示します.mergeテーブルのdrop操作はmergeの定義を削除しただけで、内部のテーブルには何の影響もありません.
2.データファイル:
(1).frm:テーブル定義を格納します.
(2).MRG:mergeテーブルがどのテーブルで構成されているか、新しいデータを挿入する際の根拠を含む組合せテーブルの情報を格納します.修正できます.mrgファイルはmergeテーブルを変更しますが、変更後はflush tablesでリフレッシュします.
3.使用例:
CREATE TABLE `m1` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`name` varchar(20) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;
CREATE TABLE `m2` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`name` varchar(20) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;
CREATE TABLE `m` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`name` varchar(20) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=MRG_MyISAM DEFAULT CHARSET=utf8 INSERT_METHOD=LAST UNION=(`m1`,`m2`);
4.適用状況:
一連の同等のMyISAMテーブルを論理的に結合し、オブジェクトとして参照するために使用されます.MERGEテーブルの利点は、単一のMyISAMテーブルサイズの制限を突破し、異なるテーブルを複数のディスクに分散することで、MERGEテーブルのアクセス効率を効果的に改善できることです.これは、データストアなどのVLDB環境に適している.
MySQLについてもっと兴味のある読者は、「MySQLストレージプロセステクニック大全」、「MySQL常用関数大概」、「MySQLログ操作テクニック大全」、「MySQLトランザクション操作テクニック大概」および「MySQLデータベースロックに関するテクニック汇总」を参照してください.
本明細書では、MySQLデータベース・メーターについて説明します.