MySQLクライアントとサーバーのプロトコルの解釈

MySQL サーバーから高いパフォーマンスを得る必要がある場合、最善の方法は、MySQL がクエリを最適化して実行する仕組みを時間をかけて研究することです。これらを理解すると、ほとんどのクエリ最適化は十分に根拠のあるものとなり、クエリ最適化プロセス全体がより論理的になります。次の図は、MySQL がクエリを実行するプロセスを示しています。

1. クライアントは SQL ステートメントをサーバーに送信します。
2. サーバーはクエリ キャッシュをチェックします。キャッシュにデータがある場合は、キャッシュされた結果が直接返されます。それ以外の場合は、SQL ステートメントが次のリンクに渡されます。
3. サーバーは SQL ステートメントを解析、前処理、最適化した後、それをクエリ オプティマイザーに渡してクエリ プランを形成します。
4. クエリ実行エンジンは、ストレージ エンジン インターフェイスを呼び出してクエリ プランを実行します。
5. サーバーはクエリの結果をクライアントに返します。

上記の手順はすべて複雑なので、次のいくつかの記事で各リンクについて詳しく説明します。クエリの最適化プロセスは特に複雑であり、理解することが重要です。

MySQL クライアント/サーバー プロトコル

MySQL クライアント/サーバー プロトコルの内部詳細を理解する必要はありませんが、高アプリケーション レベルでどのように動作するかを理解する必要があります。このプロトコルは半二重であるため、MySQL サーバーは同時にメッセージを送受信できず、メッセージを複数の短いメッセージに分割することもできません。このメカニズムにより、MySQL 通信がシンプルかつ高速になりますが、一方でいくつかの制限も追加されます。たとえば、これはフロー制御が不可能であり、一方がメッセージを送信すると、もう一方は応答する前にメッセージ全体を受信する必要があることを意味します。それは、前後に卓球をするようなものです。一度にボールを持っているのは片側だけで、ボールを受け取ったときだけ、それを打ち返すことができます。

クライアントはクエリを単一のパケットでサーバーに送信するため、クエリが大きい場合は max_allowed_pa​​cket を構成することが重要です。クライアントがクエリを送信すると、結果が返されるのを待つだけです。

対照的に、サーバーの応答は通常、複数のパケットで構成されます。サーバーが応答したら、クライアントは結果セット全体を取得する必要があります。クライアントは、単に数行を取得して、残りのデータを送信しないようにサーバーに指示することはできません。クライアントがデータの最初の数行のみを返す必要がある場合、サーバーがすべてのデータを返すまで待機し、不要なデータを破棄するか、または接続を切断するしかありません。どちらの方法も適切な選択ではないため、適切な LIMIT 句が非常に重要です。

ほとんどの MySQL 接続ライブラリは、結果セット全体をフェッチしてメモリにキャッシュすること、または必要なデータ行をフェッチすることをサポートしています。通常、デフォルトの動作では、結果セット全体をフェッチし、メモリにキャッシュします。要求されたすべての行が返されるまで、MySQL サーバーはこのクエリのロックとリソースを解放しないため、これを知っておくことは重要です。ほとんどのクライアント ライブラリでは、データがサーバーから取得されているように見えますが、実際にはデータはキャッシュから読み取られるだけの場合もあります。これはほとんどの場合問題ありませんが、長い時間がかかったり、大量のメモリを占有したりする大規模なデータクエリには適していません。クエリ結果をキャッシュしないように指定すると、メモリ使用量が少なくなり、結果をより速く処理できます。この方法の欠点は、クエリ中にサーバー側のロックとリソースの使用が発生することです。

PHP を例にとると、PHP でよく使用されるクエリ コードは次のとおりです。

<?php
$link = mysql_connect('localhost', 'ユーザー', 'パスワード');
$result = mysql_query('SELECT * FROM huge_table', $link);
($row = mysql_fetch_array($result)) の間 {
  //データ結果を処理中}

?>

このコードは必要な行だけを取得しているように見えます。ただし、このクエリは実際には、mysql_query を呼び出した後にすべての結果をメモリに格納します。 while ループは実際にはメモリ内のデータを反復します。逆に、mysql_query の代わりに mysql_unbuffered_query を使用すると、結果はキャッシュされません。

<?php
$link = mysql_connect('localhost', 'ユーザー', 'パスワード');
$result = mysql_unbuffered_query('SELECT * FROM huge_table', $link);
($row = mysql_fetch_array($result)) の間 {
  //データ結果を処理中}

?>

プログラミング言語によって、キャッシュの上書きの処理方法が異なります。たとえば、Perl DBD::mysql ドライバーでは、次に示すように、mysql_use_result 属性を通じて C 言語クライアント ライブラリ (デフォルトは mysql_buffer_result) を指定する必要があります。

パーレル

DBI を使用します。
my $dbn = DBI->connect('DBI:mysql:;host=localhost', 'user', 'password');
私の $sth = $dbn->prepare('SELECT * FROM huge_table', {mysql_use_result => 1});
$sth->execute();
(私の$row = $sth->fetchrow_array()) {
	#データ結果を処理中}

prepare は結果をキャッシュするのではなく使用するように指定することに注意してください。接続時に指定することもできますが、その場合、各クエリはキャッシュされません。

my $dbn = DBI->connect('DBI:mysql:;mysql_use_result=1;host=localhost', 'user', 'password');

上記は、MySQL クライアントおよびサーバー プロトコルの詳細な解釈です。MySQL クライアントおよびサーバー プロトコルの詳細については、123WORDPRESS.COM の他の関連記事に注目してください。