MySql 最適化アーキテクチャとストレージ エンジン (概要共有)

この記事では、mysql アーキテクチャとストレージ エンジンの概要に関する関連知識を提供します。お役に立てば幸いです。

1. MySQL 構造システム

一般的に言えば、MySQL は次のようになります。ストレージ エンジン層は 3 つの層に分かれており、階層化をより詳細に行う場合は、ストレージ エンジン層をさらにエンジン層とストレージ層に分けることができます。一部のクライアントとリンク サービスです。これには、ローカル ソック通信や、クライアント/サーバー ツールによって実装されるほとんどの TCP/IP に似た通信が含まれます。主に、いくつかの接続処理、認可認証、および関連するセキュリティ ソリューションを完了します。スレッド プールの概念がこの層に導入され、認証を通じて安全にアクセスするクライアントにスレッドを提供します。 SSL ベースの安全なリンクもこの層に実装できます。また、サーバーは、安全にアクセスする各クライアントに対してサーバーが持つ操作権限を検証します。

サービス層
第 2 層のアーキテクチャは主に、SQL インターフェイスなどのコア サービス機能のほとんどを完了し、キャッシュされたクエリ、SQL 分析と最適化、および一部の組み込み機能を完了します。関数を実装します。プロシージャ、関数など、すべてのクロスストレージ エンジン機能もこの層に実装されます。この層では、サーバーはクエリを解析して対応する内部解析ツリーを作成し、テーブル クエリの順序の決定、インデックスを使用するかどうかなどの対応する最適化を完了し、最終的に対応する実行操作を生成します。 select ステートメントの場合、サーバーは内部キャッシュにもクエリを実行します。キャッシュ領域が十分に大きければ、大量の読み取り操作を解決する環境でシステムのパフォーマンスを大幅に向上させることができます。

エンジン層
ストレージ エンジン層、ストレージ エンジンは、MySQL でのデータの保存と取得を実際に担当し、サーバーは API を通じてストレージ エンジンと通信します。ストレージエンジンが異なれば機能も異なるため、ニーズに応じて適切なストレージエンジンを選択できます。

ストレージ層
データ ストレージ層は主にファイル システムにデータを保存し、ストレージ エンジンとの対話を完了します。他のデータベースと比較すると、MySQL は少し異なり、そのアーキテクチャはさまざまなシナリオに適用でき、適切に動作します。主にストレージ エンジンに反映されるプラグイン ストレージ エンジン アーキテクチャは、クエリ処理を他のシステム タスクやデータの保存と抽出から分離します。このアーキテクチャにより、ビジネス ニーズと実際のニーズに基づいて適切なストレージ エンジンを選択できます。

2. MySQL サーバーのコンポーネント

MySQL 構造の各層は 1 つまたは複数の部分で構成されます:

接続層コンポーネント

コネクタ: インタラクティブ インターフェイス

MySQL によって提供されるインタラクティブ コンポーネント (java、.net、など) PHP およびその他の言語は、このコンポーネントを使用して SQL ステートメントを操作し、SQL との対話を実現できます

サービス層コンポーネント

接続プール: 接続プール コンポーネント

ユーザー接続、スレッド処理、およびキャッシュを必要とするその他の要件の管理とバッファーに使用されます

管理サービスとユーティリティ: 管理サービスとツール コンポーネント
バックアップとリカバリ、Mysql レプリケーション、クラスタリング、セキュリティ管理などのシステム管理および制御ツールに使用されます。

SQL インターフェイス: SQL インターフェイス コンポーネント
受け入れるために使用されます。 DML、DDL、ストアド プロシージャなどのユーザー SQL コマンドを実行し、最終結果をユーザーに返します。

パーサー: クエリ アナライザー コンポーネント
SQL コマンドが渡されるときパーサーにとって、それは検証と分析であり、まず SQL コマンド構文の正当性を分析し、SQL コマンドをデータ構造に分解しようとします。分解が失敗した場合は、SQL ステートメントが不当であることを示すプロンプトが表示されます

オプティマイザー: オプティマイザー コンポーネント
SQL ステートメントはクエリ オプティマイザーを使用して、クエリを実行する前にクエリを最適化します。

例: select id,name from user where age = 20;

この選択クエリでは、最初にすべてのテーブルをクエリしてから年齢でフィルタリングするのではなく、まず where ステートメントに基づいて選択します

この選択クエリでは、すべての属性を取り出してからフィルター処理するのではなく、最初に ID と名前に基づいて属性投影を実行します。

これら 2 つのクエリ条件を接続して最終的なクエリ結果を生成します
2. キャッシュとバッファ: バッファ プール コンポーネント
3. クエリ キャッシュにヒットしたクエリ結果がある場合、クエリ ステートメントはクエリ キャッシュからデータを直接フェッチできます。このキャッシュ メカニズムは、一連の小さなキャッシュで構成されます。たとえば、テーブル キャッシュ、レコード キャッシュ、キー キャッシュ、権限キャッシュなどです。

エンジン レイヤー コンポーネント

プラガブル ストレージ エンジン: ストレージ エンジン
ストレージ エンジンは、データの保存、インデックスの確立、クエリ データの更新などのテクノロジの実装です。ストレージ エンジンはライブラリ ベースではなくテーブル ベースです。したがって、ストレージ エンジンはテーブル タイプとも呼ばれます。テーブルの作成、データ ストレージ、取得、更新などはすべて MySQL ストレージ エンジンによって完了します。これは、MySQL ストレージ エンジンが MySQL で果たす重要な役割でもあります。

MYSQL はプラグイン ストレージ エンジン アーキテクチャを提供するため、ユーザーはさまざまなニーズに応じてデータ テーブルにさまざまなストレージ エンジンを選択できます。また、ユーザーは独自のストレージ エンジンを作成したり、ニーズに応じてストレージ エンジンを作成したりすることもできます。異なるストレージ エンジンを使用するライブラリ内の異なるテーブルも許可されます。

ストレージ層コンポーネント

ファイル システム: ファイル システム

は、実際に MySQL を保存するために使用されます。データベース ファイルや一部のログ ファイルなど。一般的なものには Linux、Windows などが含まれます。

3. ストレージ エンジン

一般的に使用されるストレージ エンジンの違い

MySQL バージョン 5.5 より前のバージョンでは、デフォルトのストレージ エンジンは MySQL に付属する MyISAM でした。バージョン 5.5 以降、デフォルトのストレージ エンジンは、MySQL 用にサードパーティ企業によって開発された InnoDB に変更されました。なぜ変更するのでしょうか?

主な理由は、InnoDB がトランザクションと行レベルのロックをサポートしており、ビジネスの一貫性要件が高いシナリオにより適しているためです。

さまざまなストレージ エンジンのテーブル作成ステートメント:

CREATE TABLE `user_innodb` (
  `id` int(11) PRIMARY KEY AUTO_INCREMENT,
  `name` varchar(255) DEFAULT NULL,
  `gender` tinyint(1) DEFAULT NULL,
  `phone` varchar(11) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE `user_myisam` (
  `id` int(11) PRIMARY KEY  AUTO_INCREMENT,
  `name` varchar(255) DEFAULT NULL,
  `gender` tinyint(1) DEFAULT NULL,
  `phone` varchar(11) DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4;

CREATE TABLE `user_memory` (
  `id` int(11) PRIMARY KEY AUTO_INCREMENT,
  `name` varchar(255) DEFAULT NULL,
  `gender` tinyint(1) DEFAULT NULL,
  `phone` varchar(11) DEFAULT NULL
) ENGINE=MEMORY DEFAULT CHARSET=utf8mb4;

CREATE TABLE `user_archive` (
  `id` int(11) PRIMARY KEY AUTO_INCREMENT,
  `name` varchar(255) DEFAULT NULL,
  `gender` tinyint(1) DEFAULT NULL,
  `phone` varchar(11) DEFAULT NULL
) ENGINE=Archive DEFAULT CHARSET=utf8mb4;

CREATE TABLE `user_csv` (
  `id` int(11) NOT NULL,
  `name` varchar(255) NOT NULL,
  `gender` tinyint(1) NOT NULL,
  `phone` varchar(11) NOT NULL
) ENGINE=CSV DEFAULT CHARSET=utf8mb4;

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

InnoDB ストレージ エンジンの特性は他のストレージ エンジンとは異なります:

1. トランザクション制御
2. 外部キー制約

InnoDBストレージ テーブルとインデックス付けには 2 つの方法があります:

1. 共有テーブル スペース ストレージを使用します。この方法で作成されたテーブルのテーブル構造は .frm ファイルに保存されます。データとインデックスは次の場所に保存されます。 innodb_data_home_dir および innodb_data_file_path. によって定義されたテーブルスペース (複数のファイルにすることができます)。
2. マルチテーブルスペースストレージを使用する: この方法で作成されたテーブルのテーブル構造は .frm ファイルにまだ存在しますが、各テーブルのデータとインデックスは .ibd に個別に保存されます。

InnoDB 適切な使用環境:
InnoDB は MySQL のデフォルトのストレージ エンジンであり、トランザクション処理アプリケーションに使用され、外部キーをサポートします。アプリケーションのトランザクション整合性に対する要件が比較的高く、同時実行条件下でのデータの一貫性が必要であり、データ操作に挿入やクエリに加えて多くの更新操作や削除操作が含まれる場合は、InnoDB ストレージ エンジンがより適切な選択肢です。

InnoDB ストレージ エンジンは、削除や更新によって発生するロックを効果的に削減することに加えて、トランザクションの完全な送信とロールバックを保証することもできるため、比較的高いデータ精度を必要とする請求システムや金融システムなどのシステムに適しています。 . システムでは、InnoDB が最適な選択です。

MyISAM
MyISAM は、トランザクションや外部キーをサポートしていません。その利点は、高速アクセスと、トランザクションの整合性または主に SELECT と INSERT の要件がないことです。基本的に、どのアプリケーションでもこのエンジンを使用して、テーブルを作成します。

には次の 2 つの重要な機能があります。

• トランザクションをサポートしません
• ファイル保管方法

各 MyISAM はディスク上にあります。ファイル名はテーブル名と同じですが、拡張子は

• .frm (格納テーブル定義);
• .MYD(MYData, Store data);
• .MYI(MYIndex, storageindex);

MyISAM の適切な利用環境:
読み取り操作と挿入操作が主なアプリケーションの場合、更新操作と削除操作はほとんどなく、トランザクションの整合性と同時実行性の要件はそれほど高くないため、このストレージ エンジンの選択は非常に適しています。

MEMORY
メモリ ストレージ エンジンは、テーブル データをメモリに保存します。各 MEMORY テーブルは、実際には .frm 形式のディスク ファイルに対応します。このファイルにはテーブルの構造のみが保存され、そのデータ ファイルはメモリに保存されます。これにより、データ処理が高速になり、テーブル全体の効率が向上します。 。

MEMORY タイプのテーブルは、データがメモリに保存され、デフォルトで HASH インデックスが使用されるため、アクセスが非常に高速ですが、サービスがシャットダウンされると、テーブル内のデータは失われます。

MEMORY 適切な使用環境:
すべてのデータを RAM に保存します。これにより、高速な位置記録やその他の同様のデータ環境が必要な場合に、いくつかのアクセス ブロックを提供できます。

MEMORY の欠点は、テーブルのサイズに制限があることです。大きすぎるテーブルはメモリにキャッシュできません。次に、テーブル内のデータを復元できることを確認する必要があります。 . テーブル内のデータは、データベースが異常終了した後に復元できます。 MEMORY テーブルは通常、アクセス結果を迅速に取得するために更新頻度が低い小さなテーブルに使用されます。

MERGE
MERGE ストレージ エンジンは、一連の MyISAM テーブルの組み合わせです。これらの MyISAM テーブルは同じ構造を持つ必要があります。MERGE テーブル自体にはデータは格納されません。MERGE タイプのテーブルは次のとおりです。クエリと更新、削除操作、これらの操作は実際には内部 MyISAM テーブルに対して実行されます。

MERGE タイプのテーブルの挿入操作の場合、挿入されたテーブルは INSERT_METHOD 句によって定義され、3 つの異なる値を持つことができます。FIRST または LAST の値を使用すると、挿入操作は最初の値または LAST 値に適用されます。テーブル上で、この句が定義されていないか、NO として定義されている場合、この MERGE テーブルに対して挿入操作を実行できないことを意味します。

MERGE テーブルに対して DROP 操作を実行できますが、この操作は MERGE テーブルの定義を削除するだけであり、内部テーブルには影響しません。

MERGE 適切な使用環境:
一連の等価な MyISAM テーブルを論理的に結合し、オブジェクトとして参照するために使用されます。

MERGE テーブルの利点は、単一の MyISAM テーブルのサイズ制限を突破できることです。また、異なるテーブルを複数のディスクに分散することで、MERGE テーブルのアクセス効率を効果的に向上させることができます。これは、データ ウェアハウジングなどの VLDB 環境に非常に適しています。

推奨学習: mysql ビデオ チュートリアル

以上がMySql 最適化アーキテクチャとストレージ エンジン (概要共有)の詳細内容です。詳細については、PHP 中国語 Web サイトの他の関連記事を参照してください。