數(shù)據(jù)庫操作是當(dāng)今 Web 應(yīng)用程序中的主要瓶頸。 不僅是 DBA（數(shù)據(jù)庫管理員）需要為各種性能問題操心，程序員為做出準(zhǔn)確的結(jié)構(gòu)化表，優(yōu)化查詢性能和編寫更優(yōu)代碼，也要費(fèi)盡心思。 在本文中，我列出了一些針對(duì)程序員的 MySQL 優(yōu)化技術(shù)，希望可以幫助大家更好的學(xué)習(xí)mysql 。
　　1.優(yōu)化查詢的查詢緩存
　　大部分MySQL服務(wù)器都有查詢緩存功能。這是提高性能的最有效的方法之一，這是由數(shù)據(jù)庫引擎私下處理的。當(dāng)同一個(gè)查詢被多次執(zhí)行，結(jié)果會(huì)直接從緩存里提取，這樣速度就很快。
　　主要的問題是，這對(duì)程序員來說太簡(jiǎn)單了，不容易看到，我們很多人都容易忽略。我們實(shí)際上是可以組織查詢緩存執(zhí)行任務(wù)的。
　　// 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'");
　　查詢緩存在第一行不執(zhí)行的原因在于CURDTE()功能的使用。這適用于所有的非確定性功能，就像NOW()和RAND()等等。。。因?yàn)楣δ芊祷氐慕Y(jié)果是可變的。MySQL決定禁用查詢器的查詢緩存。我們所需要做的是通過添加一額外一行PHP，在查詢前阻止它發(fā)生。
　　2. EXPLAIN你的選擇查詢
　　使用EXPLAIN關(guān)鍵詞可以幫助了解MySQL是怎樣運(yùn)行你的查詢的。這有助于發(fā)現(xiàn)瓶頸和查詢或表結(jié)構(gòu)的其它問題。
　　EXPLAIN的查詢結(jié)果會(huì)展示哪一個(gè)索引被使用過，表示怎樣掃描和儲(chǔ)存的，等等。。。
　　選擇一個(gè)SELECT查詢（一個(gè)有連接的復(fù)雜查詢會(huì)更好），在它的前面添加關(guān)鍵詞EXPLAIN，這樣就可以直接使用數(shù)據(jù)庫了。結(jié)果會(huì)以一個(gè)漂亮的表來展示。例如，就好比我執(zhí)行連接時(shí)忘了添加一欄的索引：

現(xiàn)在它只會(huì)從表2里面掃描9和16行，而非掃描7883行。經(jīng)驗(yàn)法則是乘以所有“行”那一欄的數(shù)字，你的查詢性能會(huì)跟結(jié)果數(shù)字成比例的。
　　3. 獲取唯一行時(shí)使用LIMIT 1
　　有時(shí)當(dāng)你查表時(shí)，你已經(jīng)知道你正在查找的結(jié)果只有一行。你可能正在獲取唯一記錄，或者你可能只是查詢是否存在滿足你的WHERE子句條件的記錄。
　　在這種情況下，將LIMIT 1添加到查詢條件中可以提高性能。這樣，數(shù)據(jù)庫引擎將在找到剛剛第一個(gè)記錄之后停止掃描記錄，而不是遍歷整個(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. 索引搜索字段
　　索引不僅僅是為了主鍵或唯一鍵。如果你會(huì)在你的表中按照任何列搜索，你就都應(yīng)該索引它們。

正如你所看到的，這個(gè)規(guī)則也適用于如 "last_name LIKE 'a%'"的部分字符串搜索。當(dāng)從字符串的開頭搜索時(shí)，MySQL就可以使用那一列的索引。
　　你也應(yīng)該明白什么樣搜索可以不使用有規(guī)律的索引。例如，當(dāng)搜索一個(gè)單詞時(shí)（例如，"WHERE post_content LIKE '%apple%'"），你將不會(huì)看到普通索引的好處。你最好使用 mysql 全文搜索 或者構(gòu)建你自己的索引解決方案。
　　5. 索引并對(duì)連接使用同樣的字段類型
　　如果你的應(yīng)用程序包含許多連接查詢, 你需要確保連接的字段在兩張表上都建立了索引。 這會(huì)影響MySQL如何內(nèi)部?jī)?yōu)化連接操作。
　　此外,被連接的字段,需要使用同樣類型。例如, 如果你使用一個(gè)DECIMAL字段, 連接另一張表的INT字段, MySQL將無法使用至少一個(gè)索引。 即使字符編碼也需要使用相同的字符類型。
　　// 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()
　　起初這是一個(gè)聽起來挺酷的技巧, 讓許多菜鳥程序員陷入了這個(gè)陷阱。但你可能不知道，一旦你開始在查詢中使用它，你創(chuàng)建了非常可怕的查詢瓶頸。
　　如果你真的需要對(duì)結(jié)果隨機(jī)排序, 這有一個(gè)更好的方法。補(bǔ)充一些額外代碼,你將可以防止當(dāng)數(shù)據(jù)成指數(shù)級(jí)增長(zhǎng)時(shí)造成的瓶頸。關(guān)鍵問題是，MySQL必須在排序之前對(duì)表中的每一行執(zhí)行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");
　　所以挑選一個(gè)小于結(jié)果數(shù)的隨機(jī)數(shù)，并將其用作LIMIT子句中的偏移量。
　　7. 避免使用SELECT *
　　從數(shù)據(jù)表中讀取的數(shù)據(jù)越多，查詢操作速度就越慢。它增加了磁盤操作所需的時(shí)間。此外，當(dāng)數(shù)據(jù)庫服務(wù)器與Web服務(wù)器分開時(shí)，由于必須在服務(wù)器之間傳輸數(shù)據(jù)，將會(huì)有更長(zhǎng)的網(wǎng)絡(luò)延遲。
　　這是一個(gè)好習(xí)慣：當(dāng)你使用SELECT語句時(shí)總是指定你需要的列。
　　// 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. 幾乎總是有一個(gè)id字段
　　在每個(gè)以id列為 PRIMARY KEY的數(shù)據(jù)表中，優(yōu)先選擇 AUTO_INCREMENT或者INT。 也可以優(yōu)選使用UNSIGNED，因?yàn)樵撝挡荒転樨?fù)的。
　　即使你擁有一個(gè)具有唯一用戶名字段的用戶表，也不要將其作為主鍵。 VARCHAR字段作為主鍵（檢索）速度較慢。通過內(nèi)部ID引用所有的用戶數(shù)據(jù)，你的代碼中將更加結(jié)構(gòu)化。
　　有些后臺(tái)操作是由MySQL引擎本身完成的，它在內(nèi)部使用主鍵字段。當(dāng)數(shù)據(jù)庫設(shè)置越復(fù)雜（集群，分區(qū)等...），這就變得更加重要了。
　　這個(gè)規(guī)則的一個(gè)可能的例外是“關(guān)聯(lián)表”，用于兩個(gè)表之間的多對(duì)多類型的關(guān)聯(lián)。例如，“posts_tags”表中包含兩列：post_id，tag_id，用于保存表名為“post”和“tags”的兩個(gè)表之間的關(guān)系。這些表可以具有包含兩個(gè)id字段的PRIMARY鍵。
　　9. 相比VARCHAR優(yōu)先使用ENUM
　　ENUM枚舉類型是非?？焖俸途o湊的。在內(nèi)部它們像TINYINT一樣存儲(chǔ)，但它們可以包含和顯示字符串值。這使他們成為某些領(lǐng)域的完美候選。
　　如果有一個(gè)字段只包含幾種不同的值，請(qǐng)使用ENUM而不是VARCHAR。例如，它可以是名為“status”的列，并且只包含諸如“active”，“inactive”，“pending”，“expired”等的值...
　　關(guān)于如何重構(gòu)你的數(shù)據(jù)表，甚至有一種方法是可以從MySQL本身得到“建議”。 當(dāng)你有一個(gè)VARCHAR字段，它實(shí)際上建議你將該列類型更改為ENUM。這通過調(diào)用PROCEDURE ANALYZE()來完成。
　　10. 使用PROCEDURE ANALYSE()獲取建議
　　PROCEDURE ANALYSE() 將使用MySQL分析列結(jié)構(gòu)和表中的實(shí)際數(shù)據(jù)，為你提供一些建議。它只有在數(shù)據(jù)表中有實(shí)際數(shù)據(jù)時(shí)才有用，因?yàn)檫@在分析決策時(shí)很重要。
　　例如，如果你創(chuàng)建了一個(gè)INT類型的主鍵，但沒有太多行，MySQL則可能建議您改用MEDIUMINT?；蛘呷绻闶褂肰ARCHAR字段，如果表里只有很少的取值，你可能會(huì)得到一個(gè)建議是將其轉(zhuǎn)換為ENUM。
　　你也可以在其中一個(gè)表視圖中單擊phpmyadmin中的“建議表結(jié)構(gòu)”鏈接來執(zhí)行此操作。
 
請(qǐng)記住，這些只是建議。 如果你的數(shù)據(jù)表變得越來越大，他們甚至可能不是正確的建議。至于如何修改最終是你來決定。
　　11. 如果可以的話使用NOT NULL
　　除非你有非常重要的理由使用NULL值，否則你應(yīng)該設(shè)置你的列為NOT NULL。
　　首先，問一下你自己在空字符串值和NULL值之間（對(duì)應(yīng)INT字段：0 vs. NULL）是否有任何的不同.如果沒有理由一起使用這兩個(gè)，那么你就不需要一個(gè)NULL字段（你知道在Oracle中NULL和空字符串是一樣的嗎？）。
　　NULL列需要額外的空間，他們?cè)黾恿四愕谋容^語句的復(fù)雜度。如果可以的話盡量避免它們。當(dāng)然，我理解一些人，他們也許有非常重要的理由使用NULL值，這不總是一件壞事。
　　摘自MySQL 文檔：
　　"NULL列在行記錄它們的值是否為NULL時(shí)需要額外的空間。例如MyISAM 表，每一個(gè)NULL列擁有額外的一個(gè)比特，聚集在最近的字節(jié)。"
　　12. 預(yù)處理語句
　　使用預(yù)處理語句有諸多好處，包括更高的性能和更好的安全性。
　　預(yù)處理語句默認(rèn)情況下會(huì)過濾綁定到它的變量，這對(duì)于避免SQL注入攻擊極為有效。當(dāng)然你也可以指定要過濾的變量。但這些方法更容易出現(xiàn)人為錯(cuò)誤，也更容易被程序員遺忘。這在使用框架或 ORM 的時(shí)候會(huì)出現(xiàn)一些問題。
　　既然我們關(guān)注性能，那就應(yīng)該說說這個(gè)方面的好處。當(dāng)在應(yīng)用中多次使用同一個(gè)查詢的時(shí)候，它的好處特別明顯。既然向同一個(gè)預(yù)備好的語句中傳入不同的參數(shù)值，MySQL 對(duì)這個(gè)語句也只會(huì)進(jìn)行一次解析。
　　同時(shí)，最新版本的 MySQL 在傳輸預(yù)備好的語句時(shí)會(huì)采用二進(jìn)制形式，這樣做的作用非常明顯，而且對(duì)減少網(wǎng)絡(luò)延遲很有幫助。
　　曾經(jīng)有一段時(shí)間，許多程序員為了一個(gè)重要的原因則避免使用預(yù)處理語句。這個(gè)原因就是，它們不會(huì)被MySQL 緩存。不過在 5.1 版本的某個(gè)時(shí)候，查詢緩存也得到的支持。
　　// 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. 無緩沖查詢
　　通常當(dāng)你從腳本執(zhí)行一個(gè)查詢，在它可以繼續(xù)后面的任務(wù)之前將需要等待查詢執(zhí)行完成。你可以使用無緩沖的查詢來改變這一情況。
　　在PHP 文檔中對(duì) mysql_unbuffered_query() f函數(shù)有一個(gè)很好的解釋：
　　"mysql_unbuffered_query() 發(fā)送SQL查詢語句到MySQL不會(huì)像 mysql_query()那樣自動(dòng)地取并緩沖結(jié)果行。這讓產(chǎn)生大量結(jié)果集的查詢節(jié)省了大量的內(nèi)存，在第一行已經(jīng)被取回時(shí)你就可以立即在結(jié)果集上繼續(xù)工作，而不用等到SQL查詢被執(zhí)行完成。"
　　然而，它有一定的局限性。你必須在執(zhí)行另一個(gè)查詢之前讀取所有的行或調(diào)用 mysql_free_result() 。另外你不能在結(jié)果集上使用 mysql_num_rows() 或 mysql_data_seek() 。
　　14. 使用 UNSIGNED INT 存儲(chǔ)IP地址
　　很多程序員沒有意識(shí)到可以使用整數(shù)類型的字段來存儲(chǔ) IP 地址，所以一直使用 VARCHAR(15) 類型的字段。使用 INT 只需要 4 個(gè)字節(jié)的空間，而且字段長(zhǎng)度固定。
　　必須確保列是 UNSINGED INT 類型，因?yàn)?IP 地址可能會(huì)用到 32 位無符號(hào)整型數(shù)據(jù)的每一個(gè)位。
　　在查詢中可以使用 INET_ATON() 來把一個(gè)IP轉(zhuǎn)換為整數(shù)，用 INET_NTOA() 來進(jìn)行相反的操作。在 PHP 也有類似的函數(shù)， ip2long() 和 long2ip() 。
　　$r = "UPDATE users SET ip = INET_ATON('{$_SERVER['REMOTE_ADDR']}') WHERE user_id = $user_id";
　　15. 固定長(zhǎng)度（靜態(tài)）的表會(huì)更快
　?。ㄗg者注：這里提到的表的長(zhǎng)度，實(shí)際是指表頭的長(zhǎng)度，即表中每條數(shù)據(jù)占用的空間大小，而不是指表的數(shù)據(jù)量）
　　如果表中所有列都是“固定長(zhǎng)度”，那么這個(gè)表被認(rèn)為是“靜態(tài)”或“固定長(zhǎng)度”的。不固定的列類型包括 VARCHAR、TEXT、BLOB等。即使表中只包含一個(gè)這些類型的列，這個(gè)表就不再是固定長(zhǎng)度的，MySQL 引擎會(huì)以不同的方式來處理它。
　　固定長(zhǎng)度的表會(huì)提高性能，因?yàn)?MySQL 引擎在記錄中檢索的時(shí)候速度會(huì)更快。如果想讀取表中的某一地，它可以直接計(jì)算出這一行的位置。如果行的大小不固定，那就需要在主鍵中進(jìn)行檢索。
　　它們也易于緩存，崩潰后容易重建。不過它們也會(huì)占用更多空間。例如，如果你把一個(gè) VARCHAR(20) 的字符改為 CHAR(20) 類型，它會(huì)總是占用 20 個(gè)字節(jié)，不管里面存的是什么內(nèi)容。
　　你可以使用 “ 垂直分區(qū)”技術(shù)，將長(zhǎng)度變化的列拆分到另一張表中。來看看：
　　16. 垂直分區(qū)
　　垂直分區(qū)是為了優(yōu)化表結(jié)構(gòu)而對(duì)其進(jìn)行縱向拆分的行為。
　　示例 1 : 你可能會(huì)有一張用戶表，包含家庭住址，而這個(gè)不是一個(gè)常用數(shù)據(jù)。這時(shí)候你可以選擇把表拆分開，將住址信息保存到另一個(gè)表中。這樣你的主用戶表就會(huì)更小。如你所知，表越小越快。
　　示例 2: 表中有一個(gè) "last_login" 字段，用戶每次登錄網(wǎng)站都會(huì)更新這個(gè)字段，而每次更新都會(huì)導(dǎo)致這個(gè)表緩存的查詢數(shù)據(jù)被清空。這種情況下你可以將那個(gè)字段放到另一張表里，保持用戶表更新量最小。
　　不過你也需要確保不會(huì)經(jīng)常聯(lián)合查詢分開后的兩張表，要不然你就得忍受由這帶來的性能下降。
　　17. 拆分大型DELETE或INSERT語句
　　如果你需要在網(wǎng)站上執(zhí)行大型DELETE或INSERT查詢，則需要注意不要影響網(wǎng)絡(luò)流量。當(dāng)執(zhí)行大型語句時(shí)，它會(huì)鎖表并使你的Web應(yīng)用程序停止。
　　Apache運(yùn)行許多并行進(jìn)程/線程。 因此 它執(zhí)行腳本效率很高。所以服務(wù)器不期望 打開 過多的連接和進(jìn)程，這很消耗資源，特別是內(nèi)存。
　　如果你 鎖表很長(zhǎng)時(shí)間（如30秒或更長(zhǎng)），在一個(gè)高流量的網(wǎng)站，會(huì)導(dǎo)致進(jìn)程和查詢堆積，處理這些進(jìn)程和查詢 可能需要很長(zhǎng)時(shí)間 ，最終甚至使 你的網(wǎng)站 崩潰。
　　如果你的維護(hù)腳本需要?jiǎng)h除大量的行，只需使用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.越少的列越快
　　對(duì)于數(shù)據(jù)庫引擎，磁盤可能是最重要的瓶頸。更小更緊湊的數(shù)據(jù)、減少磁盤傳輸量，通常有助于性能提高。
　　如果已知表具有很少的行，則沒有理由是主鍵類型為INT，可以用MEDIUMINT、SMALLINT代替，甚至在某些情況下使用TINYINT。 如果不需要完整時(shí)間記錄，請(qǐng)使用DATE而不是DATETIME。
　　19. 選擇正確的存儲(chǔ)引擎
　　MySQL有兩個(gè)主要存儲(chǔ)引擎，MyISAM和InnoDB。 每個(gè)都有自己的優(yōu)點(diǎn)和缺點(diǎn)。
　　MyISAM適用于讀取繁重的應(yīng)用程序，但是當(dāng)有很多寫入時(shí)它不能很好地?cái)U(kuò)展。 即使你正在更新一行的一個(gè)字段，整個(gè)表也被鎖定，并且在語句執(zhí)行完成之前，其他進(jìn)程甚至無法讀取該字段。 MyISAM在計(jì)算SELECT COUNT（*）的查詢時(shí)非常快。
　　InnoDB是一個(gè)更復(fù)雜的存儲(chǔ)引擎，對(duì)于大多數(shù)小的應(yīng)用程序，它比MyISAM慢。 但它支持基于行的鎖定，使其更好地?cái)U(kuò)展。 它還支持一些更高級(jí)的功能，比如事務(wù)。
　　·MyISAM存儲(chǔ)引擎
　　·InnoDB存儲(chǔ)引擎
　　20. 使用對(duì)象關(guān)系映射器（ORM, Object Relational Mapper）
　　通過使用ORM（對(duì)象關(guān)系映射器），你可以獲得一定的性能提升。ORM可以完成的一切事情，手動(dòng)編碼也可完成。但這可能意味著需要太多額外的工作，并且需要高水平的專業(yè)知識(shí)。
　　ORM以“延遲加載”著稱。這意味著它們僅在需要時(shí)獲取實(shí)際值。但是你需要小心處理他們，否則你可能最終創(chuàng)建了許多微型查詢，這會(huì)降低數(shù)據(jù)庫性能。
　　ORM還可以將多個(gè)查詢批處理到事務(wù)中，其操作速度比向數(shù)據(jù)庫發(fā)送單個(gè)查詢快得多。。
　　21. 小心使用持久連接
　　持久連接意味著減少重建連接到MySQL的成本。 當(dāng)持久連接被創(chuàng)建時(shí)，它將保持打開狀態(tài)直到腳本完成運(yùn)行。 因?yàn)锳pache重用它的子進(jìn)程，下一次進(jìn)程運(yùn)行一個(gè)新的腳本時(shí)，它將重用相同的MySQL連接。
　　·PHP： mysql_pconnect()
　　理論上看起來不錯(cuò)。 但從我個(gè)人（和許多其他人）的經(jīng)驗(yàn)看來，這個(gè)功能可能會(huì)導(dǎo)致更多麻煩。 你可能會(huì)出現(xiàn)連接數(shù)限制問題、內(nèi)存問題等等。
　　Apache總是并行運(yùn)行的，它創(chuàng)建許多子進(jìn)程。 這是持久連接在這種環(huán)境中不能很好工作的主要原因。 在你考慮使用mysql_pconnect()之前，請(qǐng)咨詢你的系統(tǒng)管理員。

來源：開源中國(guó)