mysqlの理論と基礎知識を詳しく紹介-mysql チュートリアル-php.cn

この記事では、mysql の理論と基礎知識を詳しく紹介します。一定の参考値があるので、困っている友達が参考になれば幸いです。

mysqlの理論と基礎知識を詳しく紹介

mysql アーキテクチャ

1. ネットワーク接続層

クライアントコネクタ: MySQL の組み込みサポートを提供サーバ。現在、一般的な Java、C、Python、.NET など、ほとんどすべての主流のサーバー側プログラミングテクノロジがサポートされており、それぞれの API テクノロジを通じて MySQL との接続を確立します。

2. サービスレイヤー (MySQL Server)

サービスレイヤーは MySQL Server の中核であり、主にシステム管理および制御ツール、接続プール、SQL インターフェイス、パーサー、クエリオプティマイザー、およびキャッシュの 6 つの部分で構成されます。

接続プール: クライアントとデータベース間の接続の保存と管理を担当し、1 つのスレッドが 1 つの接続の管理を担当します。

システム管理および制御ツール (管理サービスおよびユーティリティ): バックアップとリカバリ、セキュリティ管理、クラスター管理など。

SQL インターフェイス (SQL インターフェイス): さまざまなタイプを受け入れるために使用されます。クライアント SQL コマンドによって送信されたデータを収集し、ユーザーがクエリする必要がある結果を返します。 DML、DDL、ストアドプロシージャ、ビュー、トリガーなど。

Parser (パーサー): 要求された SQL を解析して「解析ツリー」を生成します。次に、いくつかの MySQL ルールに従って解析ツリーが正当であるかどうかをさらに確認します。

クエリオプティマイザー (オプティマイザー): 「解析ツリー」がパーサー文法チェックに合格すると、オプティマイザーに渡されて実行プランに変換され、ストレージエンジンと対話します。

select uid,name from user where virgin=1;

Select->>Projection->>Joinstrategy

1)select 最初に、where ステートメントに従って選択します。クエリすべてのデータを抽出してフィルタリングします。

2) 選択クエリは、すべてのフィールドではなく、uid と名前に基づいて属性プロジェクションを実行します。

3) 前の選択とプロジェクションを接続して、最終的にクエリ結果

キャッシュ (キャッシュ&バッファ): キャッシュメカニズムは、一連の小さなキャッシュで構成されます。たとえば、テーブルキャッシュ、レコードキャッシュ、権限キャッシュ、エンジンキャッシュなどです。クエリキャッシュにヒットしたクエリ結果がある場合、クエリステートメントはクエリキャッシュからデータを直接フェッチできます。

3. ストレージエンジン層 (プラグ可能ストレージエンジン)

ストレージエンジンは、MySQL でのデータの保存と抽出を担当し、MySQL と対話します。基礎となるシステムファイル。 MySQL ストレージエンジンはプラグインであり、サーバーのクエリ実行エンジンはインターフェイスを介してストレージエンジンと通信し、インターフェイスは異なるストレージエンジン間の違いを保護します。現在、ストレージエンジンは数多くあり、それぞれに独自の特徴がありますが、最も一般的なものは MyISAM と InnoDB です。

4. システムファイル層 (ファイルシステム)

この層は、データベースデータとログをファイルシステムに保存し、ストレージエンジンとの対話を完了する役割を果たします。、ファイルの物理ストレージ層です。主にログファイル、データファイル、設定ファイル、pid ファイル、ソケットファイルなどが含まれます。

ログファイル

エラーログ(エラーログ)

デフォルトで有効になり、「%log_error%」のような変数を表示します

一般的なクエリログ( 一般的なクエリログ)

一般的なクエリステートメントを記録し、「%general%」などの変数を表示します。

バイナリログ (バイナリログ)

MySQL データベースで実行された変更を記録します。操作を実行し、ステートメントの発生時刻と実行時刻を記録しますが、データベースを変更しない select や show などの SQL は記録しません。主にデータベースのリカバリとマスター/スレーブレプリケーションに使用されます。

show variables like '%log_bin%'; //有効にするかどうか

show variables like '%binlog%'; //パラメータ view

show binary logs; // スロークエリログファイルを表示します。

クエリログ (スロークエリログ)

実行時間が経過したすべてのクエリ SQL を記録します。デフォルトは 10 秒です。

show variables like '%slow_query%'; //有効にするかどうか

show variables like '%long_query_time%'; //Duration

設定ファイル

my.cnf、my.ini などのすべての MySQL 構成情報ファイルを保存するために使用されます。

データファイル

db.opt ファイル: このライブラリで使用されるデフォルトの文字セットと検証ルールを記録します。

frm ファイル: テーブル構造の定義情報など、テーブルに関するメタデータ (メタ) 情報が格納されます。各テーブルには frm ファイルが存在します。

MYD ファイル: MyISAM ストレージエンジン専用であり、MyISAM テーブルのデータを保存します。各テーブルには .MYD ファイルがあります。

MYI ファイル: MyISAM ストレージエンジン専用であり、MyISAM テーブルのインデックス関連情報を保存します。各 MyISAM テーブルは .MYI ファイルに対応します。

ibd ファイルおよび IBDATA ファイル: InnoDB データファイル (インデックスを含む) を保存します。 InnoDB ストレージエンジンには、排他的テーブルスペースと共有テーブルスペースという 2 つのテーブルスペースモードがあります。排他的テーブルスペースは .ibd ファイルを使用してデータを保存し、各 InnoDB テーブルは 1 つの .ibd ファイルに対応します。共有テーブルスペースは .ibdata ファイルを使用し、すべてのテーブルは 1 つ (または複数の自己構成) .ibdata ファイルを使用します。

ibdata1 ファイル: テーブルメタデータ、Undo ログなどを保存するシステムテーブルスペースデータファイル。

ib_logfile0、ib_logfile1 ファイル: REDO ログのログファイル。

pid ファイル

pid ファイルは、Unix/Linux 環境の mysqld アプリケーションのプロセスファイルであり、他の多くの Unix/Linux サーバープログラムと同様に、独自のプロセス ID を保存します。

ソケットファイル

ソケットファイルは Unix/Linux 環境でも利用でき、ユーザーは Unix Socket を使用して、TCP/を経由せずに Unix/Linux 環境のクライアントに接続できます。 IP ネットワーク MySQL に接続します。

InnoDB と MyISAM

トランザクションと外部キー

InnoDB は、セキュリティと整合性を備えたトランザクションと外部キーをサポートしており、大規模なシステムに適しています。挿入または更新操作の数

MyISAM はトランザクションと外部キーをサポートしません。高速なストレージと取得を提供し、多数の選択クエリ操作に適しています

ロックメカニズム

InnoDB は行レベルのロックをサポートし、指定されたレコードをロックします。ロックはインデックスに基づいて実装されます。

MyISAM はテーブルレベルのロックをサポートしており、テーブル全体をロックします。

インデックス構造

InnoDB はクラスター化インデックス (クラスター化インデックス) を使用しており、インデックスとレコードは一緒に保存され、インデックスとレコードの両方がキャッシュされます。

MyISAMはノンクラスタードインデックス(ノンクラスタードインデックス)を採用しており、インデックスとレコードが分離されています。

同時処理機能

MyISAM はテーブルロックを使用するため、書き込み操作の同時実行率が低くなり、読み取り間のブロックがなくなり、読み取りと書き込みがブロックされます。

#InnoDB の読み取りおよび書き込みのブロックは分離レベルに関連付けることができ、マルチバージョン同時実行制御 (MVCC) を使用して高い同時実行性をサポートできます

#ストレージファイル

InnoDB テーブルは、.frm テーブル構造ファイルと .ibd データファイルの 2 つのファイルに対応します。 InnoDB テーブルは最大 64 TB をサポートします。

MyISAM テーブルは、.frm テーブル構造ファイル、MYD テーブルデータファイル、および .MYI インデックスファイルの 3 つのファイルに対応します。 MySQL 5.0 以降、デフォルトの制限は 256 TB です。

Redo Log と Binlog の違い

Redo Log は InnoDB エンジンの機能ですが、Binlog は MySQL Server の組み込み機能であり、バイナリファイルに記録されます。

Redo Log はデータページの更新ステータス内容を記録する物理ログ、Binlog は更新プロセスを記録する論理ログです。

Redo Log は循環的に書き込まれ、ログ領域のサイズは固定され、Binlog は追加で書き込まれ、1 つ書き込まれた後は次のログが書き込まれ、上書きされません。

Redo Log はサーバーの異常ダウン後のトランザクションデータの自動回復に使用でき、Binlog はマスター/スレーブレプリケーションとデータの回復に使用できます。 Binlog には自動クラッシュセーフ機能がありません。

アプリケーションでは、クエリを最適化するためにスレーブデータベースに複数のインデックスを追加できますが、書き込み効率を向上させるためにメインデータベースのこれらのインデックスを省略することもできます。

#読み取りと書き込みの分離スキーム

1書き込み直後に読み取り

データベースへの書き込み後、読み取り操作は 1 時間以内に完了します。一定時間メインライブラリに移動し、その後スレーブライブラリにリード操作でアクセスします。

2 セカンダリクエリ

最初にスレーブデータベースに移動してデータを読み取ります。データが見つからない場合は、メインデータベースに移動してデータを読み取ります。この操作により、読み取り圧力がメインライブラリに簡単に返されます。悪意のある攻撃を回避するために、データベースアクセス API 操作をカプセル化することをお勧めします。これは、セキュリティと低結合に有益です。

3特殊な業務処理に応じて

ビジネスの特性や重要度に応じて調整たとえば、リアルタイム性の高い重要な業務データの読み書きをメインデータベースに配置することが可能です。高いリアルタイム性を必要としない副業の場合は、読み取りと書き込みを分離し、データベースからクエリを実行できます。