解讀MySQL的客戶(hù)端和服務(wù)端協(xié)議

如果需要從 MySQL 服務(wù)端獲得很高的性能，最佳的方式就是花時(shí)間研究 MySQL 優(yōu)化和執(zhí)行查詢(xún)的機(jī)制。一旦理解了這些，大部分的查詢(xún)優(yōu)化是有據(jù)可循的，從而使得整個(gè)查詢(xún)優(yōu)化的過(guò)程更有邏輯性。下圖展示了 MySQL 執(zhí)行查詢(xún)的過(guò)程：

1. 客戶(hù)端將 SQL 語(yǔ)句發(fā)送到服務(wù)端。
2. 服務(wù)端檢查查詢(xún)緩存。如果緩存中已有數(shù)據(jù)，則直接返回緩存結(jié)果；否則，將 SQL 語(yǔ)句傳遞給下一環(huán)節(jié)。
3. 服務(wù)端解析、預(yù)處理和優(yōu)化 SQL 語(yǔ)句后，傳遞到查詢(xún)優(yōu)化器中形成查詢(xún)計(jì)劃。
4. 查詢(xún)執(zhí)行引擎通過(guò)調(diào)用存儲(chǔ)引擎接口執(zhí)行查詢(xún)計(jì)劃。
5. 服務(wù)端將查詢(xún)結(jié)果返回給客戶(hù)端。

上述的幾個(gè)步驟都有其復(fù)雜性，接下來(lái)幾篇文章將詳細(xì)講述各個(gè)環(huán)節(jié)。查詢(xún)優(yōu)化過(guò)程尤其復(fù)雜，并且理解這一環(huán)節(jié)很重要。

MySQL 客戶(hù)端/服務(wù)端協(xié)議

雖然并不需要了解 MySQL 客戶(hù)端/服務(wù)端協(xié)議的內(nèi)部細(xì)節(jié)，但需要從高應(yīng)用層面理解其是如何工作的。這個(gè)協(xié)議是半雙工的，這意味著 MySQL 服務(wù)端不同同時(shí)發(fā)送和接收消息，以及不可以將消息拆成多條短消息發(fā)送。這種機(jī)制一方面使得 MySQL 的通信簡(jiǎn)單快速，另一方面也增加了一些限制。例如，這意味著無(wú)法進(jìn)行流控，一旦一方發(fā)送了消息，另一方在響應(yīng)前必須接收整個(gè)消息。這就好像來(lái)回打乒乓球一樣，同一時(shí)間只有一方有球，只有接到了球才能把它打回去。

客戶(hù)端通過(guò)單個(gè)數(shù)據(jù)包將查詢(xún)語(yǔ)句發(fā)送給服務(wù)端，因此在存在大的查詢(xún)語(yǔ)句時(shí)配置 max_allowed_packet 很重要。一旦客戶(hù)端發(fā)送查詢(xún)語(yǔ)句后，它就只能等待返回結(jié)果。

相反，服務(wù)端的響應(yīng)通常是由多個(gè)數(shù)據(jù)包組成的。一旦服務(wù)端響應(yīng)后，客戶(hù)端必須獲取整個(gè)結(jié)果集?？蛻?hù)端沒(méi)法簡(jiǎn)單地獲取幾行然后告訴服務(wù)端不要再發(fā)送剩余的數(shù)據(jù)。如果客戶(hù)端僅僅需要返回?cái)?shù)據(jù)前面的幾行，只能是等待服務(wù)端全部數(shù)據(jù)返回后再?gòu)闹衼G棄不需要的數(shù)據(jù)，或者是粗暴地?cái)嚅_(kāi)連接。不管哪種方式都不是好的選擇，因此合適的 LIMIT子句就顯得十分重要。

大部分的 MySQL連接庫(kù)支持獲取整個(gè)結(jié)果集并在內(nèi)存中緩存起來(lái)，或者是獲取需要的數(shù)據(jù)行。默認(rèn)的行為通常是獲取整個(gè)結(jié)果集然后在內(nèi)存緩存。知道這一點(diǎn)很重要，因?yàn)?MySQL 服務(wù)端在所有請(qǐng)求的數(shù)據(jù)行沒(méi)返回前，不會(huì)釋放這次查詢(xún)的鎖和資源。大部分客戶(hù)端庫(kù)會(huì)讓你感覺(jué)數(shù)據(jù)是從服務(wù)端獲取的，實(shí)際上這些數(shù)據(jù)可能僅僅是從緩存中讀取的。這在大部分時(shí)間是沒(méi)問(wèn)題的，但對(duì)于耗時(shí)很久或占據(jù)很多內(nèi)存的大數(shù)據(jù)量查詢(xún)來(lái)說(shuō)就不合適了。如果指定了不緩存查詢(xún)結(jié)果，那么占用的內(nèi)存會(huì)更小，并且可以更快地處理結(jié)果。缺點(diǎn)是這種方式會(huì)在查詢(xún)時(shí)引起 服務(wù)端的鎖和資源占用。

以 PHP 為例，以下是PHP常用的查詢(xún)代碼：

?php
$link = mysql_connect('localhost', 'user', 'password');
$result = mysql_query('SELECT * FROM huge_table', $link);
while ($row = mysql_fetch_array($result)) {
  //處理數(shù)據(jù)結(jié)果
}

?>

這個(gè)代碼看起來(lái)好像是只獲取了需要的數(shù)據(jù)行。然而，這個(gè)查詢(xún)通過(guò) mysql_query 的調(diào)用后實(shí)際上將全部結(jié)果放到了內(nèi)存中。而 while 循環(huán)實(shí)際上是對(duì)內(nèi)存中的數(shù)據(jù)進(jìn)行循環(huán)迭代。相反，如果使用 mysql_unbuffered_query 替代 mysql_query 的話，那就不會(huì)緩存結(jié)果。

?php
$link = mysql_connect('localhost', 'user', 'password');
$result = mysql_unbuffered_query('SELECT * FROM huge_table', $link);
while ($row = mysql_fetch_array($result)) {
  //處理數(shù)據(jù)結(jié)果
}

?>

不同的編程語(yǔ)言處理緩存覆蓋的方式不同。例如，Perl 的 DBD::mysql 驅(qū)動(dòng)需要通過(guò) mysql_use_result 屬性指定 C 語(yǔ)音客戶(hù)端庫(kù)（默認(rèn)是 mysql_buffer_result），示例如下：

#!/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()) {
	#處理數(shù)據(jù)結(jié)果
}

注意到 prepare 指定了使用結(jié)果而不是緩存結(jié)果。也可以通過(guò)在連接的時(shí)候指定，這會(huì)使得每次查詢(xún)都不緩存。

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

以上就是解讀MySQL的客戶(hù)端和服務(wù)端協(xié)議的詳細(xì)內(nèi)容，更多關(guān)于MySQL 客戶(hù)端和服務(wù)端協(xié)議的資料請(qǐng)關(guān)注腳本之家其它相關(guān)文章！