MySQL 的 20+ 條最佳實務_Mysql

資料庫操作是當今 Web 應用程式中的主要瓶頸。 不僅是 DBA（資料庫管理員）需要為各種效能問題操心，程式員為做出準確的結構化表，最佳化查詢效能和編寫更優代碼，也要費盡心思。 在本文中，我列出了一些針對程式員的 MySQL 最佳化技術。
在我們開始學習之前，我補充一點：你可以在 Envato Market 上找到大量的 MySQL 指令碼和公用程式。

1.最佳化查詢的查詢快取

大部分MySQL伺服器都有查詢快取功能。這是提高效能的最有效方法之一，這是由資料庫引擎私下處理的。當同一個查詢被多次執行，結果會直接從緩衝裡提取，這樣速度就很快。

主要的問題是，這對程式員來說太簡單了，不容易看到，我們很多人都容易忽略。我們實際上是可以組織查詢快取執行任務的。

// 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'");

查詢快取在第一行不執行的原因在於CURDTE()功能的使用。這適用於所有的非確定性功能，就像NOW()和RAND()等等。。。因為功能返回的結果是可變的。MySQL決定禁用查詢器的查詢快取。我們所需要做的是通過添加一額外一行PHP，在查詢前阻止它發生。

2. EXPLAIN你的選取查詢

使用EXPLAIN關鍵詞可以協助瞭解MySQL是怎樣運行你的查詢的。這有助於發現瓶頸和查詢或表結構的其它問題。

EXPLAIN的查詢結果會展示哪一個索引被使用過，表示怎樣掃描和儲存的，等等。。。

選擇一個SELECT查詢（一個有串連的複雜查詢會更好），在它的前面添加關鍵詞EXPLAIN，這樣就可以直接使用資料庫了。結果會以一個漂亮的表來展示。例如，就好比我執行串連時忘了添加一欄的索引：

現在它只會從表2裡面掃描9和16行，而非掃描7883行。經驗法則是乘以所有“行”那一欄的數字，你的查詢效能會跟結果數字成比例的。

3. 擷取唯一行時使用LIMIT 1

有時當你查表時，你已經知道你正在尋找的結果只有一行。你可能正在擷取唯一記錄，或者你可能只是查詢是否存在滿足你的WHERE子句條件的記錄。

在這種情況下，將LIMIT 1添加到查詢條件中可以提高效能。這樣，資料庫引擎將在找到剛剛第一個記錄之後停止掃描記錄，而不是遍曆整個表或索引。

// 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. 索引搜尋欄位

索引不僅僅是為了主鍵或唯一鍵。如果你會在你的表中按照任何列搜尋，你就都應該索引它們。

正如你所看到的，這個規則也適用於如 "last_name LIKE 'a%'"的部分字串搜尋。當從字串的開頭搜尋時，MySQL就可以使用那一列的索引。

你也應該明白什麼樣搜尋可以不使用有規律的索引。例如，當搜尋一個單詞時（例如，"WHERE post_content LIKE '%apple%'"），你將不會看到普通索引的好處。你最好使用 mysql 全文檢索搜尋或者構建你自己的索引解決方案。

5. 索引並對串連使用同樣的欄位類型

如果你的應用程式套件組合含許多串連查詢, 你需要確保串連的欄位在兩張表上都建立了索引。 這會影響MySQL如何內部最佳化串連操作。

此外,被串連的欄位,需要使用同樣類型。例如, 如果你使用一個DECIMAL欄位, 串連另一張表的INT欄位, MySQL將無法使用至少一個索引。 即使字元編碼也需要使用相同的字元類型。

// 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. 不要ORDER BY RAND()

起初這是一個聽起來挺酷的技巧, 讓許多菜鳥程式員陷入了這個陷阱。但你可能不知道，一旦你開始在查詢中使用它，你建立了非常可怕的查詢瓶頸。

如果你真的需要對結果隨機排序, 這有一個更好的方法。補充一些額外代碼,你將可以防止當資料成指數級增長時造成的瓶頸。關鍵問題是，MySQL必須在排序之前對錶中的每一行執行RAND()操作（這需要處理能力），並且僅僅給出一行。

// 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");

所以挑選一個小於結果數的隨機數，並將其用作LIMIT子句中的位移量。

7. 避免使用SELECT *

從資料表中讀取的資料越多，查詢操作速度就越慢。它增加了磁碟操作所需的時間。此外，當資料庫伺服器與Web伺服器分開時，由於必須在伺服器之間傳輸資料，將會有更長的網路延遲。

這是一個好習慣：當你使用SELECT語句時總是指定你需要的列。

// 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. 幾乎總是有一個id欄位

在每個以id列為PRIMARY KEY的資料表中，優先選擇AUTO_INCREMENT或者INT。 也可以優選使用UNSIGNED，因為該值不能為負的。

即使你擁有一個具有唯一使用者名稱欄位的使用者表，也不要將其作為主鍵。 VARCHAR欄位作為主鍵（檢索）速度較慢。通過內部ID引用所有的使用者資料，你的代碼中將更加結構化。

有些後台操作是由MySQL引擎本身完成的，它在內部使用主鍵欄位。當資料庫設定越複雜（叢集，分區等...），這就變得更加重要了。

這個規則的一個可能的例外是“關聯表”，用於兩個表之間的多對多類型的關聯。例如，“posts_tags”表中包含兩列：post_id，tag_id，用於儲存表名為“post”和“tags”的兩個表之間的關係。這些表可以具有包含兩個id欄位的PRIMARY鍵。

9. 相比VARCHAR優先使用ENUM

ENUM枚舉類型是非常快速和緊湊的。在內部它們像TINYINT一樣儲存，但它們可以包含和顯示字串值。這使他們成為某些領域的完美候選。

如果有一個欄位只包含幾種不同的值，請使用ENUM而不是VARCHAR。例如，它可以是名為“status”的列，並且只包含諸如“active”，“inactive”，“pending”，“expired”等的值...

關於如何重構你的資料表，甚至有一種方法是可以從MySQL本身得到“建議”。 當你有一個VARCHAR欄位，它實際上建議你將該列類型更改為ENUM。這通過調用PROCEDURE ANALYZE()來完成。

10. 使用PROCEDURE ANALYSE()擷取建議

PROCEDURE ANALYSE() 將使用MySQL分析列結構和表中的實際資料，為你提供一些建議。它只有在資料表中有實際資料時才有用，因為這在分析決策時很重要。

例如，如果你建立了一個INT類型的主鍵，但沒有太多行，MySQL則可能建議您改用MEDIUMINT。或者如果你使用VARCHAR欄位，如果表裡只有很少的取值，你可能會得到一個建議是將其轉換為ENUM。

你也可以在其中一個表視圖中單擊phpmyadmin中的“建議表結構”連結來執行此操作。

請記住，這些只是建議。 如果你的資料表變得越來越大，他們甚至可能不是正確的建議。至於如何修改最終是你來決定。

11. 如果可以的話使用NOT NULL

除非你有非常重要的理由使用NULL值，否則你應該設定你的列為NOT NULL。

首先，問一下你自己在Null 字元串值和NULL值之間（對應INT欄位：0 vs. NULL）是否有任何的不同.如果沒有理由一起使用這兩個，那麼你就不需要一個NULL欄位（你知道在Oracle中NULL和Null 字元串是一樣的嗎？）。

NULL列需要額外的空間，他們增加了你的比較語句的複雜度。如果可以的話盡量避免它們。當然，我理解一些人，他們也許有非常重要的理由使用NULL值，這不總是一件壞事。

摘自MySQL 文檔：

"NULL列在行記錄它們的值是否為NULL時需要額外的空間。例如MyISAM 表，每一個NULL列擁有額外的一個位元，聚集在最近的位元組。"

12. 預先處理語句

使用預先處理語句有諸多好處，包括更高的效能和更好的安全性。

預先處理語句預設情況下會過濾綁定到它的變數，這對於避免SQL注入攻擊極為有效。當然你也可以指定要過濾的變數。但這些方法更容易出現人為錯誤，也更容易被程式員遺忘。這在使用架構或 ORM 的時候會出現一些問題。

既然我們關注效能，那就應該說說這個方面的好處。當在應用中多次使用同一個查詢的時候，它的好處特別明顯。既然向同一個預備好的語句中傳入不同的參數值，MySQL 對這個語句也只會進行一次解析。

同時，最新版本的 MySQL 在傳輸預備好的語句時會採用二進位形式，這樣做的作用非常明顯，而且對減少網路延遲很有協助。

曾經有一段時間，許多程式員為了一個重要的原因則避免使用預先處理語句。這個原因就是，它們不會被MySQL 緩衝。不過在 5.1 版本的某個時候，查詢快取也得到的支援。

想在 PHP 中使用預先處理語句，你可以看看 mysqli 擴充 或使用資料抽象層，如 PDO。

// create a prepared statementif ($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();}

13. 無緩衝查詢

通常當你從指令碼執行一個查詢，在它可以繼續後面的任務之前將需要等待查詢執行完成。你可以使用無緩衝的查詢來改變這一情況。

在PHP 文檔中對  mysql_unbuffered_query() f函數有一個很好的解釋： 

"mysql_unbuffered_query() 發送SQL查詢語句到MySQL不會像 mysql_query()那樣自動地取並緩衝結果行。這讓產生大量結果集的查詢節省了大量的記憶體，在第一行已經被取回時你就可以立即在結果集上繼續工作，而不用等到SQL查詢被執行完成。"

然而，它有一定的局限性。你必須在執行另一個查詢之前讀取所有的行或調用mysql_free_result() 。另外你不能在結果集上使用mysql_num_rows() 或 mysql_data_seek() 。

14. 使用 UNSIGNED INT 儲存IP地址

很多程式員沒有意識到可以使用整數類型的欄位來儲存 IP 位址，所以一直使用 VARCHAR(15) 類型的欄位。使用 INT 只需要 4 個位元組的空間，而且欄位長度固定。

必須確保列是 UNSINGED INT 類型，因為 IP 位址可能會用到 32 位無符號整型資料的每一個位。

在查詢中可以使用 INET_ATON() 來把一個IP轉換為整數，用 INET_NTOA() 來進行相反的操作。在 PHP 也有類似的函數，ip2long() 和 long2ip()。

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

15. 固定長度（靜態）的表會更快

（譯者註：這裡提到的表的長度，實際是指表頭的長度，即表中每條資料佔用的空間大小，而不是指表的資料量）

如果表中所有列都是“固定長度”，那麼這個表被認為是“靜態”或“固定長度”的。不固定的列類型包括 VARCHAR、TEXT、BLOB等。即使表中只包含一個這些類型的列，這個表就不再是固定長度的，MySQL 引擎會以不同的方式來處理它。

固定長度的表會提高效能，因為 MySQL 引擎在記錄中檢索的時候速度會更快。如果想讀取表中的某一地，它可以直接計算出這一行的位置。如果行的大小不固定，那就需要在主鍵中進行檢索。

它們也易於緩衝，崩潰後容易重建。不過它們也會佔用更多空間。例如，如果你把一個 VARCHAR(20) 的字元改為 CHAR(20) 類型，它會總是佔用 20 個位元組，不管裡面存的是什麼內容。

你可以使用“垂直資料分割”技術，將長度變化的列拆分到另一張表中。來看看：

16. 垂直資料分割

垂直資料分割是為了最佳化表結構而對其進行縱向拆分的行為。

樣本 1: 你可能會有一張使用者表，包含家庭住址，而這個不是一個常用資料。這時候你可以選擇把表拆分開，將住址資訊儲存到另一個表中。這樣你的主使用者表就會更小。如你所知，表越小越快。

樣本 2: 表中有一個 "last_login" 欄位，使用者每次登入網站都會更新這個欄位，而每次更新都會導致這個表緩衝的查詢資料被清空。這種情況下你可以將那個欄位放到另一張表裡，保持使用者表更新量最小。

不過你也需要確保不會經常聯集查詢分開後的兩張表，要不然你就得忍受由這帶來的效能下降。

17. 拆分大型DELETE或INSERT語句

如果你需要在網站上執行大型DELETE或INSERT查詢，則需要注意不要影響網路流量。當執行大型語句時，它會鎖表並使你的Web應用程式停止。

Apache運行許多並行進程/線程。 因此它執行指令碼效率很高。所以伺服器不期望開啟過多的串連和進程，這很消耗資源，特別是記憶體。

如果你鎖表很長時間（如30秒或更長），在一個高流量的網站，會導致進程和查詢堆積，處理這些進程和查詢可能需要很長時間，最終甚至使你的網站崩潰。

如果你的維護指令碼需要刪除大量的行，只需使用LIMIT子句，以避免阻塞。

while (1) {  mysql_query("DELETE FROM logs WHERE log_date <= '2009-10-01' LIMIT 10000");  if (mysql_affected_rows() == 0) {    // done deleting    break;  }  // you can even pause a bit  usleep(50000);}

18. 越小的列越快

對於資料庫引擎來說，磁碟空間可能是最需要注意的瓶頸。對效能而言，“小”和“緊縮”有助於減少磁碟傳輸量。

MySQL 文檔中有一個列表，列舉了各種資料類型所需要的儲存空間。

如果資料表預計只會有少量的行，那就沒必要把主鍵定義為 INT 類型，可以用 MEDIUMINT、SMALLINT 甚至 TINYINT 來代替。（譯者註：對於日期資料，）如果不需要時間部分，就應該使用 DATE 而不是 DATETIME。

請確保留出合理的資料成長空間，不然就可能造成像Slashdot那樣的結果（譯者註：Slashdot 因為資料增長將評論表的主鍵改為了 INT 型，但沒有修改其父表中的相應的資料類型，雖然一個 ALTER 語句就可以解決問題，但是需要至少停止某些業務三個小時）。

19. 選擇正確的儲存引擎

MySQL 有兩個主要的儲存引擎：MyISAM 和 InnoDB，它們各有利弊。

MyISAM 適用於讀請求特別多的應用，但不適用於有大量寫請求的情況。甚至你只是要更新一行中的某個欄位，都會造成整張表被鎖，然後直到這個查詢完成，其它進程都不能從這張表讀取資料。MyISAM 在計算 SELECT COUNT(*) 這種類型的查詢時速度非常快。

InnoDB 是一個複雜的儲存引擎，在多數小型應用中它比 MyISAM 慢。但是它支援行級鎖，有更好的尺度。它還支援一些進階特性，比如事務。

• MyISAM 儲存引擎

• InnoDB 儲存引擎

20. 使用對象關係映射器（ORM, Object Relational Mapper）

通過使用ORM（對象關係映射器），你可以獲得一定的效能提升。ORM可以完成的一切事情，手動編碼也可完成。但這可能意味著需要太多額外的工作，並且需要高水平的專業知識。

ORM以“消極式載入”著稱。這意味著它們僅在需要時擷取實際值。但是你需要小心處理他們，否則你可能最終建立了許多微型查詢，這會降低資料庫效能。

ORM還可以將多個查詢批處理到事務中，其操作速度比向資料庫發送單個查詢快得多。

目前我最喜歡的PHP-ORM是Doctrine。我寫了一篇關於如何安裝Doctrine與CodeIgniter的文章（install Doctrine with CodeIgniter）。

21. 小心使用持久串連

持久串連意味著減少重建串連到MySQL的成本。 當持久串連被建立時，它將保持開啟狀態直到指令碼完成運行。 因為Apache重用它的子進程，下一次進程運行一個新的指令碼時，它將重用相同的MySQL串連。

• PHP：mysql_pconnect()

理論上看起來不錯。 但從我個人（和許多其他人）的經驗看來，這個功能可能會導致更多麻煩。 你可能會出現串連數限制問題、記憶體問題等等。

Apache總是並行啟動並執行，它建立許多子進程。 這是持久串連在這種環境中不能很好工作的主要原因。 在你考慮使用mysql_pconnect()之前，請諮詢你的系統管理員。

原文出處： Burak Guzel   譯文出處：開源中國