MySQLを解読するクライアントとサービスエンドプロトコル

もしMySQLサービスから高い性能を得る必要があるならば、最適な方法は時間をかけてMySQL最適化とクエリを実行するメカニズムを研究することである。これらを理解すると、ほとんどのクエリ最適化は、逐次的であり、全体のクエリ最適化のプロセスをより論理的にする。以下の図は、MySQLがクエリーを実行するプロセスを示しています。

クライアントは、SQL文をサービス端末に送信する。

サービス端末はクエリキャッシュを検査する。キャッシュにデータがある場合は、そのままキャッシュ結果に戻ります。そうでなければ、SQL文を次のステップに伝えます。

サービス端末解析、前処理、及びSQL文を最適化した後、クエリ最適化器に伝達してクエリプランを形成する。

クエリー実行エンジンは、記憶エンジンインターフェースを呼び出してクエリープログラムを実行する。

サービス端末は、クエリ結果をクライアントに返す。

上記のいくつかのステップには複雑さがあります。次のいくつかの文章は各段階を詳しく説明します。お問い合わせの最適化の過程は特に複雑で、理解することが重要です。

MySQLクライアント/サービスエンドプロトコル
MySQLクライアント／サービスエンドプロトコルの内部詳細を知る必要はないが、高いアプリケーションレベルからは、どのように動作しているかを理解する必要がある。このプロトコルは半二重であり、これはMySQLサービス端末が異なると同時にメッセージを送信し受信することと、メッセージを複数のショートメッセージに分割して送信することができないことを意味する。このような機構は，MySQLの通信を簡単にする一方，いくつかの制限を加えた。例えば、これはフロー制御ができないことを意味し、一方がメッセージを送信すると、他方は応答する前にメッセージ全体を受信しなければならない。これは卓球を往復するように同じ時間に一方だけがボールを持っています。ボールを受け取ってこそ打ち返すことができます。
クライアントは、単一のパケットを通じて、クエリ文をサービスに送信します。したがって、大きなクエリ文がある場合は、max_を設定します。allowed_packetが重要です。クライアントがクエリ文を送信すると、結果が戻ってくるまで待つしかないです。
対照的に、サービス端末の応答は通常、複数のパケットからなる。サービスが応答すると、クライアントは結果セット全体を取得しなければならない。クライアントは簡単に何行かを取得することができませんでした。残りのデータはサービス側に送信しないようにしてください。クライアントがデータの前の何行かに戻るだけであれば、サーバー全体のデータが戻ってきたらそこから不要なデータを破棄したり、乱暴に接続を切断したりするだけです。どちらも良い選択ではないので、適切なLIMIT子文が重要です。
ほとんどのMySQL接続ライブラリは、結果セットを取得し、メモリにキャッシュするか、必要なデータ行を取得することをサポートしています。デフォルトの動作は、結果セット全体を取得してメモリにキャッシュすることが一般的です。このことを知っておくと、MySQLサービスがすべての要求のデータラインに戻る前に、このクエリのロックとリソースを解放しないからです。ほとんどのクライアントライブラリは、データがサービスから取得されたと感じていますが、実際には、これらのデータはキャッシュからのみ読み取られている可能性があります。これは大部分の時間で大丈夫ですが、時間がかかったり、多くのメモリを占めている大データ量の照会には不向きです。クエリ結果をキャッシュしないと指定された場合、占有メモリはより小さくなり、結果をより速く処理することができる。欠点は、このような方式が照会時にサービス端末のロックとリソース占有を引き起こすことである。
PHPを例にとって、以下はPHPの共通のクエリコードです。

<?php
$link = mysql_connect('localhost', 'user', 'password');
$result = mysql_query('SELECT * FROM huge_table', $link);
while ($row = mysql_fetch_array($result)) {
  //      
}

?>

このコードは必要なデータ行だけを取得したように見えます。しかし、このクエリはmysqlを通じて(通って)います。queryの呼び出し後、実際に結果を全部メモリに入れました。whileサイクルは実際にメモリ中のデータを繰り返します。逆に、mysqlを使えばunbuffered_queryがmysqlの代わりになっていますqueryなら、結果はキャッシュされません。

<?php
$link = mysql_connect('localhost', 'user', 'password');
$result = mysql_unbuffered_query('SELECT * FROM huge_table', $link);
while ($row = mysql_fetch_array($result)) {
  //      
}

?>

プログラミング言語によってキャッシュが上書きされる方法が異なります。例えば、PerlのDBD：mysql駆動はmysql_アメリカ.result属性指定C音声クライアントライブラリ（デフォルトはmysql_）ブザーレスリング）の例は以下の通りである。

#!/usr/bin/perl

use DBI;
my $dbn = DBI->connect('DBI:mysql:;host=localhost', 'user', 'password');
my $sth = $dbn->prepare('SELECT * FROM huge_table', {mysql_use_result => 1});
$sth->execute();
while (my $row = $sth->fetchrow_array()) {
	#      
}

prepareはキャッシュ結果ではなく使用結果を指定していることに注意してください。接続時に指定することで、クエリーごとにキャッシュしないようにすることもできます。

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

以上はMySQLのクライアントとサービスエンドプロトコルの詳細を解読しました。MySQLクライアントとサービスエンドプロトコルに関する資料は他の関連記事に注目してください。