MySQL ストレージエンジンに関する予備調査

目次:

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

2. InnoDB と MyISAM のパフォーマンスの比較

3、MyISAM と InnoDB ロックの比較

4. 2 つのストレージエンジンのインデックス比較

1 ストレージエンジンの紹介

s: 5.7.19 MySQL に基づくデータベース。

図 1.1 データベースのバージョン

Navicat for MySQL でテスト:

Enter sql: show Engines;

図 1.2 ストレージエンジンカテゴリ

パラメータの説明:

Engine: ストレージエンジン名

Support: MySQL がこのエンジンをサポートするかどうか

Comment: エンジンの説明

Transaction: トランザクション処理がサポートされるかどうか

XA : 分散トランザクション処理およびトランザクション処理環境は、「すぐに使える」外部キー、デフォルトのストレージエンジンをサポートします。

MyISAM

は、主に読み取り専用のデータウェアハウス、電子商取引およびエンタープライズアプリケーションに適しています。 MyISAM は、高度なキャッシュおよびインデックス作成メカニズムを使用して、データの取得とインデックス作成の速度を向上させますが、トランザクションや外部キーはサポートしていません。

l Blackhole

は、アプリケーションが実際にデータを書き込んでいるが、ディスクにデータを保存したくないテストシナリオに適しています。 Blackhole ストレージエンジンは特定の要件を満たしています。バイナリログが有効になっている場合、Blackhole ストレージエンジンをレプリケーショントポロジのリレーまたはプロキシとして使用して、SQL ステートメントがログに書き込まれます。この場合、リレーエージェントはマスターからのデータを処理してスレーブに送信しますが、それ自体はデータを保存しません。

l CSV

は、CSVログファイルを書き込み、構造化されたビジネスデータをスプレッドシートに素早くインポートするに適しています。 CSV ストレージエンジンは、表形式のカンマ区切り値 (CSV) ファイルを作成、読み取り、書き込みします。これにはインデックス作成メカニズムが提供されておらず、日時値の保存および変換時に特定の問題があり、データの保存が効率的ではないため、注意して使用する必要があります。

l メモリ

は、頻繁にアクセスされ、めったに変更されない静的データ(郵便番号リスト、都道府県と都市のリスト、分類リストなど)に適しており、アクセスに適していますスナップショット技術を利用した分布データや履歴データのデータベース。メモリ (HEAP とも呼ばれる) は、ハッシュメカニズムを使用して頻繁に使用されるデータを取得するメモリ内ストアであり、より高速な取得を可能にします。データはメモリに保存され、MySQL セッション内でのみ有効であるため、シャットダウン時にデータが更新され、削除されます。

l Federated

は、分散環境またはデータセット環境に適しています。 Federated Storage エンジンを使用すると、複数のデータベースサーバーのテーブルを結合できます。データは移動されず、リモートテーブルが同じストレージエンジンを使用する必要もありません。 Federated Storage Engine は現在、MySQL のほとんどのディストリビューションで無効になっています。

l Archive

は、めったにアクセスされない大量のアーカイブまたは履歴データの保存と取得に適しています。アーカイブストレージエンジンは、大量のデータを圧縮形式で保存し、インデックスをサポートせず、テーブルスキャンを通じてのみアクセスできます。

l MRG_MYISAM

は、データが1つ以上のデータベースの複数のテーブルに保存されるデータウェアハウスなどの非常に大規模なデータベースアプリケーションに適しています。 MRG_MYISAM ストレージエンジンの最大の特徴は、大きなテーブルを多数の異なる小さなテーブルに分割し、それらを異なるディスクに格納し、これらの小さなテーブルを結合して、検索と並べ替えを同時に実行する速度です。それぞれの小さなテーブルで管理する必要があるデータが少ないためです。

欠点:

l は、複合テーブルを形成するために同じ MyISAM テーブルを使用する必要があります。

l インデックスは、単一のインデックスよりも低いです。テーブル。

2 InnoDB と MyISAM のパフォーマンス比較

注: テストテーブルには 36 個のフィールドが含まれており、 988218 個のレコードが含まれています。

InnoDB ストレージエンジンのテストデータベースは Innodbtest という名前で、このテーブルが含まれており、テーブル名は Innodbtable です。MyISAM ストレージエンジンのテストデータベースは Myisamtest という名前で、このテーブルが含まれており、テーブル名は Myisamtable です。

MySQL で InnoDB および MyISAM ストレージエンジンを使用してテーブルをテストします。まず、準備作業を行います:

(1) MyISAM ストレージエンジンをテストするテーブルのストレージエンジンをデフォルトの InnoDB から MyISAM に変更します。

alter table myisamtable engine=myisam;

ログイン後にコピー

図2.1 ストレージエンジンの変更

(2) データベースの文字エンコーディングを変更し、utf-8に設定します

alter database myisamtest character set utf8;
alter database innodbtest character set utf8;

ログイン後にコピー

図 2.2 InnoDB ストレージエンジンテストライブラリの文字エンコーディングの変更

図 2.3 MyISAM ストレージエンジンテストライブラリの文字エンコーディングを変更する

2 つのストレージエンジンのいくつかの機能をテストする:

l ストレージ構造

(1) InnoDB :

テーブルデータは、サイズ 1.21 GB のデータファイル (Innodbtable.ibd) に保存されます。テーブルに関連するメタデータ情報は、テーブル構造の定義情報を含む、innodbtable.frm ファイルに保存されます。データベースの一部の定義情報はdb.optに定義されています。

図 2.4 InnoDB ディスクストレージディレクトリ

図 2.5 db.opt ファイルの内容

(2)

.frm ファイル: ストレージとアンプ; テーブル構造の定義情報など、テーブルに関連するメタデータ情報

.MYD ファイル: サイズ 853.34MB。MyISAM テーブルのデータが格納されます。

.MYI ファイル: サイズは 34.11MB、MyISAM テーブルのインデックス関連情報が保存されます。

db.opt: データベースの一部の定義情報を定義します。

図2.6 MyISAMディスクストレージディレクトリ

図2.7 db.optファイルの内容

l select

(1) InnoDB:

図 2.8 InnoDB 選択テスト

(2) MyISAM:

図 2 9 MyISAM 選択テスト

l を挿入します

(1) InnoDB:

図 2.10 InnoDB 挿入テスト

(2) MyISAM:

図 2.11 MyISAM 挿入テスト

l update

(1) InnoDB:

図 2.12 InnoDB 更新テスト

(2) MyISAM:

図 2.13 MyISAM 更新テスト

削除します

(1) InnoDB:

図 2.14 InnoDB 削除テスト

(2) MyISAM:

図 2.15テストを削除します

どこを削除しますか

(1) InnoDB:

図 2.16 InnoDB delete where test

(2) 私の ISAM:

フィギュア2.17 MyISAM delete where test

l count without where

(1) InnoDB:

図 2 .18 where テストなしの InnoDB カウント

(2) MyISAM:

図 2.19 where テストなしの MyISAM カウント

l group by

（1）InnoDB：

图2.20 InnoDB的group by测试

（2）MyISAM：

图2.21 MyISAM的group by测试

l 外键

创建一个新表，将测试表的主键作为新表的外键进行测试：

create table `foreigntest`(
`id` int primary key not null,
`taskid` varchar(64) not null,
`host` varchar(128) not null default '',
`month` char(8) not null,
constraint `fk_task_h_m` foreign key (`taskid`,`host`,`month`)
references `innodbtable`(`taskid`,`host`,`month`)
) charset=utf8mb4

ログイン後にコピー

（1）InnoDB：

图2.22 InnoDB的外键测试

（2）MyISAM：

图2.23 MyISAM的外键测试

总结如下表：

	InnoDB	MyISAM
存储结构	.ibd：存放表数据； .frm文件：存储与表相关的元数据信息，包括表结构的定义信息等；基于磁盘的资源是InnoDB表空间数据文件和它的日志文件，InnoDB表的大小只受限于操作系统文件的大小，一般为2GB。	每个表在磁盘上存储成三个文件： .MYD文件：存放表的数据。 .MYI文件：存放表的索引相关信息。 .frm文件：存储与表相关的元数据信息，包括表结构的定义信息等；
存储空间	InnoDB テーブルはより多くのメモリとディスクストレージを必要とし、データとインデックスをキャッシュするためにメインメモリ内に独自の専用バッファプールを確立します。	MyISAM は圧縮でき、保存スペースが小さくなります。
移植性	無料の解決策は、データファイルのコピー、binlogのバックアップ、またはmysqldumpの使用ですが、データ量が数十Gに達すると比較的困難です	MyISAM データはファイル形式で保存されるため、クロスプラットフォームのデータ転送に非常に便利です。バックアップおよびリカバリ中にテーブルを個別に操作できます
トランザクションセキュリティ	トランザクション (コミット)、ロールバック (ロールバック)、クラッシュ修復機能によりトランザクションをサポートします	はトランザクションをサポートしていません、各クエリはアトミックです
増加	改善されました(0.15秒)	(0.40 秒)
削除(どこで)	(32.79秒)	改善(16.51秒)
すべて削除	(263.86秒)	改善 (0.24 秒)
変更	(0.20 秒)	改善 (0.12 秒)
ちゃ	(139.75秒)	良い(65.57秒)
ロック	テーブルロックと行ロックをサポート行ロックにより、マルチユーザーの同時操作のパフォーマンスが大幅に向上します。ただし、InnoDB の行ロックは WHERE の主キーに対してのみ有効であり、主キーではない場合はテーブル全体がロックされます。	テーブルロックのみがサポートされています
外部キー	サポートされています	サポートされていません where なしの
count	はテーブルの特定の行数を保存せず、行ごとのスキャン統計 (70.88 秒) が必要です	の方が優れています。 MyISAM はテーブルの特定の行数を保存します。行数は単純に読み出されます。 (0.09秒)
グループ化	(35.14秒)	より良い(4.75秒)

注:

[1]テーブルスペース: InnoDB は、データ、インデックス、ロールバックメカニズムなど、マシンに依存しないファイルを整理するために使用されるツールです。デフォルトでは、すべてのテーブルがテーブルスペース (共有テーブルスペースと呼ばれます) を共有します。共有テーブルスペースは、自動的に複数のファイルに拡張されません。デフォルトでは、テーブルスペースは 1 つのファイルのみを占有し、データの増加に応じてファイルも増加します。 autoextend オプションを使用して、テーブルスペースで新しいファイルを作成できるようにします。

[2]クラッシュ修復機能: InnoDB ストレージエンジンは、ログファイルとテーブルスペースという 2 つのディスクベースのメカニズムを使用してデータを保存します。 InnoDB はこれらのログを使用して、シャットダウンまたはクラッシュする前にデータ回復を再構築します。プログラムの起動時に、InnoDB はログを読み取り、ダーティページをディスクに自動的に書き込み、システムがクラッシュする前にバッファされた更新を復元します。

3 確率は最も高く、同時実行性は最も低くなります。 (2) 行レベルのロック: オーバーヘッドが高く、ロックが遅い。ロックの粒度が最も小さく、ロックの競合の可能性が最も低く、同時実行性が最も高い。

(3) MyISAM テーブルに対する読み取り操作は、同じテーブルに対する他のユーザーの読み取りリクエストをブロックしませんが、同じテーブルに対する書き込みリクエストはブロックします。MyISAM テーブルに対する他のユーザーの書き込みリクエストはブロックされます。同じテーブル。テーブルの読み取りリクエストと書き込みリクエスト。MyISAM テーブルの読み取り操作と書き込み操作はシリアルです (スレッドがテーブルの書き込みロックを取得すると、ロックを保持しているスレッドのみがアクセスできます)。テーブルの更新操作を実行します。他のスレッドからの読み取りおよび書き込み操作は、ロックが解放されるまで待機します)

(4) 共有ロック: 1 つのトランザクションが行を読み取ることを許可し、他のトランザクションが排他的アクセスを取得できないようにします。同じデータセットにロックします。

(5) 排他的ロック (X): 排他的ロックを取得するトランザクションにデータの更新を許可し、他のトランザクションが同じデータセットの共有読み取りロックと排他的書き込みロックを取得するのを防ぎます。

(6) UPDATE、DELETE、INSERT ステートメントの場合、InnoDB は関連するデータセットに排他ロック (X) を自動的に追加しますが、通常の SELECT ステートメントの場合、InnoDB はロックを追加しません。

4 インデックス構造。このツリーのリーフノードデータフィールドには完全なデータレコードが保存されます。このインデックスのキーはデータテーブルの主キーであるため、InnoDB テーブルデータファイル自体が主インデックスになります。

l InnoDB テーブルで使用されるインデックスはクラスター化インデックスです。クラスター化インデックスは、インデックスだけでなくデータ自体も格納するデータ構造です。したがって、インデックス内の値が見つかると、追加のディスクシークを行わずにデータを直接取得できます。

l 主キーインデックスまたはテーブルの最初のインデックスは、クラスター化インデックスを使用して作成されます。

l InnoDB のすべての補助インデックスは、主キーをデータフィールドとして参照します。補助インデックスを作成すると、クラスタードインデックスのキーワード（主キー、一意キー、行ID）も補助インデックスに格納されるため、キーワードに基づいて素早く検索し、元のデータを素早く取得できます。クラスター化インデックス。つまり、主キー列を使用して補助インデックスをスキャンする場合、クエリでは補助インデックスを使用してデータを取得するだけで済みます。

l MyISAM:

l インデックスファイルとデータファイルは分離されており、インデックスファイルにはデータレコードのアドレスのみが保存されます。 B+treeをインデックス構造として使用し、リーフノードのデータフィールドにはデータレコードのアドレスが格納されます。

l MyISAM では、プライマリインデックスとセカンダリインデックス (セカンダリキー) の間に構造的な違いはありません。ただし、プライマリインデックスではキーが一意である必要があるのに対し、セカンダリインデックスのキーは繰り返すことができる点が異なります。

l 主な違い:

l メインインデックスの違い: InnoDB のデータファイル自体がインデックスファイルです。 MyISAMのインデックスとデータは分離されています。

l 補助インデックスの違い: InnoDB の補助インデックスデータフィールドには、アドレスの代わりに、対応するレコードの主キーの値が格納されます。 MyISAM のセカンダリインデックスとプライマリインデックスには大きな違いはありません。

注:

B+ ツリー: m 次 B+ ツリーの場合、次の特性があります:

l ノードには n 個のキーワードを含む n 個のサブツリーがあります。

l すべてのリーフノードには、すべてのキーワードに関する情報と、これらのキーワードを含むレコードへのポインターが含まれています。そして葉ノード自体もキーワードのサイズに応じて小さいものから大きいものへと順にリンクされています。

l すべての非終端ノードはインデックス部分とみなすことができ、ノードにはそのサブツリー (ルートノード) の最大 (または最小) のキーワードのみが含まれます。

l B+ ツリーでは、検索が成功したかどうかに関係なく、各検索はルートからリーフノードまでのパスをたどります。

l ツリー内の各ノードには、最大 m 個のサブツリーが含まれます。

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