WEB開(kāi)發者不光要解決程序的效率問題，對數(shù)據庫的快速訪問和(hé)相應也是一個(gè)大(dà)問題。希望本文能對大(dà)家(jiā)掌握MySQL優化技(jì)巧有(yǒu)所幫助。

1.優化你(nǐ)的MySQL查詢緩存

在MySQL服務器(qì)上(shàng)進行(xíng)查詢，可(kě)以啓用高(gāo)速查詢緩存。讓數(shù)據庫引擎在後台悄悄的處理(lǐ)是提高(gāo)性能的最有(yǒu)效方法之一。當同一個(gè)查詢被執行(xíng)多(duō)次時(shí)，如果結果是從緩存中提取，那(nà)是相當快的。

但(dàn)主要的問題是，它是那(nà)麽容易被隐藏起來(lái)以至于我們大(dà)多(duō)數(shù)程序員會(huì)忽略它。在有(yǒu)些(xiē)處理(lǐ)任務中，我們實際上(shàng)是可(kě)以阻止查詢緩存工作(zuò)的。

// query cache does NOT work
 $r = mysql_query("SELECT username FROM user WHERE signup_date >= CURDATE()");
// query cache works! 
$today = date("Y-m-d");
$r = mysql_query("SELECT username FROM user WHERE signup_date >= '$today'");
// query cache does NOT work
$r = mysql_query("SELECT username FROM user WHERE signup_date >= CURDATE()");
// query cache works!
$today = date("Y-m-d");
$r = mysql_query("SELECT username FROM user WHERE signup_date >= '$today'");

2.用EXPLAIN使你(nǐ)的SELECT查詢更加清晰

使用EXPLAIN關鍵字是另一個(gè)MySQL優化技(jì)巧，可(kě)以讓你(nǐ)了解MySQL正在進行(xíng)什麽樣的查詢操作(zuò)，這可(kě)以幫助你(nǐ)發現瓶頸的所在，并顯示出查詢或表結構在哪裏出了問題。

EXPLAIN查詢的結果，可(kě)以告訴你(nǐ)那(nà)些(xiē)索引正在被引用，表是如何被掃描和(hé)排序的等等。

實現一個(gè)SELECT查詢（最好是比較複雜的一個(gè)，帶joins方式的），在裏面添加上(shàng)你(nǐ)的關鍵詞解釋，在這裏我們可(kě)以使用phpMyAdmin，他會(huì)告訴你(nǐ)表中的結果。舉例來(lái)說，假如當我在執行(xíng)joins時(shí)，正忘記往一個(gè)索引中添加列，EXPLAIN能幫助我找到問題的所在。

添加索引到group_id field後

3.利用LIMIT 1取得(de)唯一行(xíng)

有(yǒu)時(shí)，當你(nǐ)要查詢一張表是，你(nǐ)知道(dào)自己隻需要看一行(xíng)。你(nǐ)可(kě)能會(huì)去的一條十分獨特的記錄，或者隻是剛好檢查了任何存在的記錄數(shù)，他們都滿足了你(nǐ)的WHERE子句。

在這種情況下，增加一個(gè)LIMIT 1會(huì)令你(nǐ)的查詢更加有(yǒu)效。這樣數(shù)據庫引擎發現隻有(yǒu)1後将停止掃描，而不是去掃描整個(gè)表或索引。

// do I have any users from Alabama?
// what NOT to do:
$r = mysql_query("SELECT * FROM user WHERE state = 'Alabama'");
if (mysql_num_rows($r) > 0) {
    // ...    
}   
// much better:
$r = mysql_query("SELECT 1 FROM user WHERE state = 'Alabama' LIMIT 1");
if (mysql_num_rows($r) > 0) { 
     // ... 
}

4. 索引中的檢索字段

索引不僅是主鍵或唯一鍵。如果你(nǐ)想搜索表中的任何列，你(nǐ)應該一直指向索引。

5.保證連接的索引是相同的類型

如果應用程序中包含多(duō)個(gè)連接查詢，你(nǐ)需要确保你(nǐ)鏈接的列在兩邊的表上(shàng)都被索引。這會(huì)影(yǐng)響MySQL如何優化內(nèi)部聯接操作(zuò)。

此外，加入的列，必須是同一類型。例如，你(nǐ)加入一個(gè)DECIMAL列，而同時(shí)加入另一個(gè)表中的int列，MySQL将無法使用其中至少(shǎo)一個(gè)指标。即使字符編碼必須同為(wèi)字符串類型。

// looking for companies in my state
$r = mysql_query("SELECT company_name FROM users
     LEFT JOIN companies ON (users.state = companies.state)
     WHERE users.id = $user_id");
// both state columns should be indexed
// and they both should be the same type and character encoding 
// or MySQL might do full table scans

6.不要使用BY RAND()命令

這是一個(gè)令很(hěn)多(duō)新手程序員會(huì)掉進去的陷阱。你(nǐ)可(kě)能不知不覺中制(zhì)造了一個(gè)可(kě)怕的平靜。這個(gè)陷阱在你(nǐ)是用BY RAND()命令時(shí)就開(kāi)始創建了。

如果您真的需要随機顯示你(nǐ)的結果，有(yǒu)很(hěn)多(duō)更好的途徑去實現。誠然這需要寫更多(duō)的代碼，但(dàn)是能避免性能瓶頸的出現。問題在于，MySQL可(kě)能會(huì)為(wèi)表中每一個(gè)獨立的行(xíng)執行(xíng)BY RAND()命令（這會(huì)消耗處理(lǐ)器(qì)的處理(lǐ)能力），然後給你(nǐ)僅僅返回一行(xíng)。

// what NOT to do:
$r = mysql_query("SELECT username FROM user ORDER BY RAND() LIMIT 1");
// much better: 
$r = mysql_query("SELECT count(*) FROM user");
$d = mysql_fetch_row($r);
$rand = mt_rand(0,$d[0] - 1);
$r = mysql_query("SELECT username FROM user LIMIT $rand, 1");

7.盡量避免SELECT *命令

從表中讀取越多(duō)的數(shù)據，查詢會(huì)變得(de)更慢。他增加了磁盤需要操作(zuò)的時(shí)間(jiān)，還(hái)是在數(shù)據庫服務器(qì)與WEB服務器(qì)是獨立分開(kāi)的情況下。你(nǐ)将會(huì)經曆非常漫長的網絡延遲，僅僅是因為(wèi)數(shù)據不必要的在服務器(qì)之間(jiān)傳輸。

始終指定你(nǐ)需要的列，這是一個(gè)非常良好的習慣。

// not preferred
$r = mysql_query("SELECT * FROM user WHERE user_id = 1");
$d = mysql_fetch_assoc($r);
echo "Welcome {$d['username']}"; 
// better:
$r = mysql_query("SELECT username FROM user WHERE user_id = 1");
$d = mysql_fetch_assoc($r);
echo "Welcome {$d['username']}";
// the differences are more significant with bigger result sets

8.從PROCEDURE ANALYSE()中獲得(de)建議

PROCEDURE ANALYSE()可(kě)讓MySQL的柱結構分析和(hé)表中的實際數(shù)據來(lái)給你(nǐ)一些(xiē)建議。如果你(nǐ)的表中已經存在實際數(shù)據了，能為(wèi)你(nǐ)的重大(dà)決策服務。

9.準備好的語句

準備好的語句，可(kě)以從性能優化和(hé)安全兩方面對大(dà)家(jiā)有(yǒu)所幫助。

準備好的語句在過濾已經綁定的變量默認情況下，能給應用程序以有(yǒu)效的保護，防止SQL注入攻擊。當然你(nǐ)也可(kě)以手動過濾，不過由于大(dà)多(duō)數(shù)程序員健忘的性格，很(hěn)難達到效果。

// create a prepared statement
if ($stmt = $mysqli->prepare("SELECT username FROM user WHERE state=?")) {
      // bind parameters 
      $stmt->bind_param("s", $state); 
      // execute 
      $stmt->execute();
      // bind result variables
      $stmt->bind_result($username); 
      // fetch value
      $stmt->fetch(); 
      printf("%s is from %s\n", $username, $state);
      $stmt->close(); 
}

10.将IP地址存儲為(wèi)無符号整型

許多(duō)程序員在創建一個(gè)VARCHAR（15）時(shí)并沒有(yǒu)意識到他們可(kě)以将IP地址以整數(shù)形式來(lái)存儲。當你(nǐ)有(yǒu)一個(gè)INT類型時(shí)，你(nǐ)隻占用4個(gè)字節的空(kōng)間(jiān)，這是一個(gè)固定大(dà)小(xiǎo)的領域。

你(nǐ)必須确定你(nǐ)所操作(zuò)的列是一個(gè)UNSIGNED INT類型的,因為(wèi)IP地址将使用32位unsigned integer。

$r = "UPDATE users SET ip = INET_ATON('{$_SERVER['REMOTE_ADDR']}') WHERE user_id = $user_id";

十大(dà)MySQL優化技(jì)巧就介紹到這裏。