《Mysql必讀MySQL 的 20+ 條最佳實踐》要點：
本文介紹了Mysql必讀MySQL 的 20+ 條最佳實踐，希望對您有用。如果有疑問，可以聯(lián)系我們。

MYSQL教程數(shù)據(jù)庫操作是當今 Web 應用程序中的主要瓶頸. 不僅是 DBA(數(shù)據(jù)庫管理員)必要為各種性能問題操心,程序員為做出準確的結構化表,優(yōu)化查詢性能和編寫更優(yōu)代碼,也要費盡心思. 在本文中,我列出了一些針對程序員的 MySQL 優(yōu)化技術.
在我們開始學習之前,我補充一點：你可以在 Envato Market 上找到大量的 MySQL 腳本和實用程序.

MYSQL教程

MYSQL教程1.優(yōu)化查詢的查詢緩存

MYSQL教程大部分MySQL服務器都有查詢緩存功能.這是提高性能的最有效的辦法之一,這是由數(shù)據(jù)庫引擎私下處理的.當同一個查詢被多次執(zhí)行,結果會直接從緩存里提取,這樣速度就很快.

MYSQL教程主要的問題是,這對程序員來說太簡單了,不容易看到,我們很多人都容易忽略.我們實際上是可以組織查詢緩存執(zhí)行任務的.

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

MYSQL教程查詢緩存在第一行不執(zhí)行的原因在于CURDTE()功能的使用.這適用于所有的非確定性功能,就像NOW()和RAND()等等...因為功能返回的結果是可變的.MySQL決定禁用查詢器的查詢緩存.我們所必要做的是通過添加一額外一行PHP,在查詢前阻止它發(fā)生.

MYSQL教程2. EXPLAIN你的選擇查詢

MYSQL教程使用EXPLAIN關鍵詞可以贊助了解MySQL是怎樣運行你的查詢的.這有助于發(fā)現(xiàn)瓶頸和查詢或表結構的其它問題.

MYSQL教程EXPLAIN的查詢結果會展示哪一個索引被使用過,表現(xiàn)怎樣掃描和儲存的,等等...

MYSQL教程選擇一個SELECT查詢(一個有連接的復雜查詢會更好),在它的前面添加關鍵詞EXPLAIN,這樣就可以直接使用數(shù)據(jù)庫了.結果會以一個漂亮的表來展示.例如,就比如我執(zhí)行連接時忘了添加一欄的索引：

MYSQL教程

MYSQL教程現(xiàn)在它只會從表2里面掃描9和16行,而非掃描7883行.經驗法則是乘以所有“行”那一欄的數(shù)字,你的查詢性能會跟結果數(shù)字成比例的.

MYSQL教程3. 獲取唯一行時使用LIMIT 1

MYSQL教程有時當你查表時,你已經知道你正在查找的結果只有一行.你可能正在獲取唯一記錄,或者你可能只是查詢是否存在滿足你的WHERE子句條件的記錄.

MYSQL教程在這種情況下,將LIMIT 1添加到查詢條件中可以提高性能.這樣,數(shù)據(jù)庫引擎將在找到剛剛第一個記錄之后停止掃描記錄,而不是遍歷整個表或索引.

MYSQL教程
// 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) {
  // ...
}

MYSQL教程4. 索引搜索字段

MYSQL教程索引不僅僅是為了主鍵或唯一鍵.如果你會在你的表中依照任何列搜索,你就都應該索引它們.

MYSQL教程

MYSQL教程正如你所看到的,這個規(guī)則也適用于如 "last_name LIKE 'a%'"的部分字符串搜索.當從字符串的開頭搜索時,MySQL就可以使用那一列的索引.

MYSQL教程你也應該明白什么樣搜索可以不使用有規(guī)律的索引.例如,當搜索一個單詞時(例如,"WHERE post_content LIKE '%apple%'"),你將不會看到普通索引的好處.你最好使用 mysql 全文搜索或者構建你自己的索引辦理方案.

MYSQL教程5. 索引并對連接使用同樣的字段類型

MYSQL教程如果你的應用程序包括許多連接查詢, 你需要確保連接的字段在兩張表上都建立了索引. 這會影響MySQL如何內部優(yōu)化連接操作.

MYSQL教程此外,被連接的字段,必要使用同樣類型.例如, 如果你使用一個DECIMAL字段, 連接另一張表的INT字段, MySQL將無法使用至少一個索引. 即使字符編碼也必要使用相同的字符類型.

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

MYSQL教程6. 不要ORDER BY RAND()

MYSQL教程起初這是一個聽起來挺酷的技巧, 讓許多菜鳥程序員陷入了這個陷阱.但你可能不知道,一旦你開始在查詢中使用它,你創(chuàng)建了非常可怕的查詢瓶頸.

MYSQL教程如果你真的需要對結果隨機排序, 這有一個更好的辦法.補充一些額外代碼,你將可以防止當數(shù)據(jù)成指數(shù)級增長時造成的瓶頸.關鍵問題是,MySQL必須在排序之前對表中的每一行執(zhí)行RAND()操作(這需要處理能力),并且僅僅給出一行.

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

MYSQL教程所以挑選一個小于結果數(shù)的隨機數(shù),并將其用作LIMIT子句中的偏移量.

MYSQL教程7. 避免使用SELECT *

MYSQL教程從數(shù)據(jù)表中讀取的數(shù)據(jù)越多,查詢操作速度就越慢.它增加了磁盤操作所需的時間.此外,當數(shù)據(jù)庫服務器與Web服務器分開時,由于必需在服務器之間傳輸數(shù)據(jù),將會有更長的網絡延遲.

MYSQL教程這是一個好習慣：當你使用SELECT語句時總是指定你必要的列.

MYSQL教程
// 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

MYSQL教程8. 幾乎總是有一個id字段

MYSQL教程在每個以id列為PRIMARY KEY的數(shù)據(jù)表中,優(yōu)先選擇AUTO_INCREMENT或者INT. 也可以優(yōu)選使用UNSIGNED,因為該值不克不及為負的.

MYSQL教程即使你擁有一個具有唯一用戶名字段的用戶表,也不要將其作為主鍵. VARCHAR字段作為主鍵(檢索)速度較慢.通過內部ID引用所有的用戶數(shù)據(jù),你的代碼中將更加結構化.

MYSQL教程有些后臺操作是由MySQL引擎自己完成的,它在內部使用主鍵字段.當數(shù)據(jù)庫設置越復雜(集群,分區(qū)等...),這就變得更加重要了.

MYSQL教程這個規(guī)則的一個可能的例外是“關聯(lián)表”,用于兩個表之間的多對多類型的關聯(lián).例如,“posts_tags”表中包括兩列：post_id,tag_id,用于保存表名為“post”和“tags”的兩個表之間的關系.這些表可以具有包括兩個id字段的PRIMARY鍵.

MYSQL教程9. 相比VARCHAR優(yōu)先使用ENUM

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

MYSQL教程如果有一個字段只包括幾種不同的值,請使用ENUM而不是VARCHAR.例如,它可以是名為“status”的列,并且只包括諸如“active”,“inactive”,“pending”,“expired”等的值...

MYSQL教程關于如何重構你的數(shù)據(jù)表,甚至有一種辦法是可以從MySQL本身得到“建議”. 當你有一個VARCHAR字段,它實際上建議你將該列類型更改為ENUM.這通過調用PROCEDURE ANALYZE()來完成.

MYSQL教程10. 使用PROCEDURE ANALYSE()獲取建議

MYSQL教程PROCEDURE ANALYSE()?將使用MySQL分析列結構和表中的實際數(shù)據(jù),為你提供一些建議.它只有在數(shù)據(jù)表中有實際數(shù)據(jù)時才有用,因為這在分析決策時很重要.

MYSQL教程例如,如果你創(chuàng)建了一個INT類型的主鍵,但沒有太多行,MySQL則可能建議您改用MEDIUMINT.或者如果你使用VARCHAR字段,如果內外只有很少的取值,你可能會得到一個建議是將其轉換為ENUM.

MYSQL教程你也可以在其中一個表視圖中單擊phpmyadmin中的“建議表結構”鏈接來執(zhí)行此操作.

MYSQL教程

MYSQL教程請記住,這些只是建議. 如果你的數(shù)據(jù)表變得越來越大,他們甚至可能不是正確的建議.至于如何修改最終是你來決定.

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

MYSQL教程除非你有非常重要的理由使用NULL值,否則你應該設置你的列為NOT NULL.

MYSQL教程首先,問一下你本身在空字符串值和NULL值之間(對應INT字段：0 vs. NULL)是否有任何的不同.如果沒有理由一起使用這兩個,那么你就不需要一個NULL字段(你知道在Oracle中NULL和空字符串是一樣的嗎?).

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

MYSQL教程摘自MySQL 文檔：

MYSQL教程"NULL列在行記錄它們的值是否為NULL時必要額外的空間.例如MyISAM 表,每一個NULL列擁有額外的一個比特,聚集在最近的字節(jié)."

MYSQL教程12. 預處理語句

MYSQL教程使用預處理語句有諸多好處,包括更高的性能和更好的平安性.

MYSQL教程預處理語句默認情況下會過濾綁定到它的變量,這對于避免SQL注入攻擊極為有效.當然你也可以指定要過濾的變量.但這些辦法更容易出現(xiàn)人為錯誤,也更容易被程序員遺忘.這在使用框架或 ORM 的時候會出現(xiàn)一些問題.

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

MYSQL教程同時,最新版本的 MySQL 在傳輸預備好的語句時會采用二進制形式,這樣做的作用非常明顯,而且對減少網絡延遲很有贊助.

MYSQL教程曾經有一段時間,許多程序員為了一個重要的原因則避免使用預處理語句.這個原因便是,它們不會被MySQL 緩存.不過在 5.1 版本的某個時候,查詢緩存也得到的支持.

MYSQL教程想在 PHP?中使用預處理語句,你可以看看?mysqli 擴展?或使用數(shù)據(jù)抽象層,如?PDO.

MYSQL教程
// 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();
}

MYSQL教程13. 無緩沖查詢

MYSQL教程通常當你從腳本執(zhí)行一個查詢,在它可以繼續(xù)后面的任務之前將必要等待查詢執(zhí)行完成.你可以使用無緩沖的查詢來改變這一情況.

MYSQL教程在PHP 文檔中對??mysql_unbuffered_query()?f函數(shù)有一個很好的解釋：?

MYSQL教程"mysql_unbuffered_query() 發(fā)送SQL查詢語句到MySQL不會像 mysql_query()那樣自動地取并緩沖結果行.這讓產生大量結果集的查詢節(jié)省了大量的內存,在第一行已經被取回時你就可以立即在結果集上繼續(xù)工作,而不用比及SQL查詢被執(zhí)行完成."

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

MYSQL教程14. 使用 UNSIGNED INT 存儲IP地址

MYSQL教程很多程序員沒有意識到可以使用整數(shù)類型的字段來存儲 IP 地址,所以一直使用?VARCHAR(15) 類型的字段.使用 INT 只必要 4 個字節(jié)的空間,而且字段長度固定.

MYSQL教程必需確保列是 UNSINGED INT 類型,因為 IP 地址可能會用到 32 位無符號整型數(shù)據(jù)的每一個位.

MYSQL教程在查詢中可以使用?INET_ATON()?來把一個IP轉換為整數(shù),用?INET_NTOA()?來進行相反的操作.在 PHP 也有類似的函數(shù),ip2long()?和?long2ip().

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

MYSQL教程15. 固定長度(靜態(tài))的表會更快

MYSQL教程(譯者注：這里提到的表的長度,實際是指表頭的長度,即表中每條數(shù)據(jù)占用的空間大小,而不是指表的數(shù)據(jù)量)

MYSQL教程如果表中所有列都是“固定長度”,那么這個表被認為是“靜態(tài)”或“固定長度”的.不固定的列類型包括 VARCHAR、TEXT、BLOB等.即使表中只包括一個這些類型的列,這個表就不再是固定長度的,MySQL 引擎會以不同的方式來處理它.

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

MYSQL教程它們也易于緩存,瓦解后容易重建.不過它們也會占用更多空間.例如,如果你把一個 VARCHAR(20) 的字符改為 CHAR(20) 類型,它會總是占用 20 個字節(jié),不管里面存的是什么內容.

MYSQL教程你可以使用“垂直分區(qū)”技術,將長度變化的列拆分到另一張表中.來看看：

MYSQL教程16. 垂直分區(qū)

MYSQL教程垂直分區(qū)是為了優(yōu)化表結構而對其進行縱向拆分的行為.

MYSQL教程示例 1: 你可能會有一張用戶表,包括家庭住址,而這個不是一個常用數(shù)據(jù).這時候你可以選擇把表拆分開,將住址信息保存到另一個表中.這樣你的主用戶表就會更小.如你所知,表越小越快.

MYSQL教程示例 2: 表中有一個 "last_login" 字段,用戶每次登錄網站都會更新這個字段,而每次更新都會導致這個表緩存的查詢數(shù)據(jù)被清空.這種情況下你可以將那個字段放到另一張表里,堅持用戶表更新量最小.

MYSQL教程不過你也必要確保不會經常聯(lián)合查詢分開后的兩張表,要不然你就得忍受由這帶來的性能下降.

MYSQL教程17. 拆分大型DELETE或INSERT語句

MYSQL教程如果你必要在網站上執(zhí)行大型DELETE或INSERT查詢,則必要注意不要影響網絡流量.當執(zhí)行大型語句時,它會鎖表并使你的Web應用程序停止.

MYSQL教程Apache運行許多并行進程/線程. 因此它執(zhí)行腳本效率很高.所以服務器不期望打開過多的連接和進程,這很消耗資源,特別是內存.

MYSQL教程如果你鎖表很長時間(如30秒或更長),在一個高流量的網站,會導致進程和查詢堆積,處理這些進程和查詢可能需要很長時間,最終甚至使你的網站瓦解.

MYSQL教程如果你的維護腳本必要刪除大量的行,只需使用LIMIT子句,以避免阻塞.

MYSQL教程
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);
}

MYSQL教程18. 越小的列越快

MYSQL教程對于數(shù)據(jù)庫引擎來說,磁盤空間可能是最必要注意的瓶頸.對性能而言,“小”和“緊縮”有助于減少磁盤傳輸量.

MYSQL教程MySQL 文檔中有一個列表,列舉了各種數(shù)據(jù)類型所必要的存儲空間.

MYSQL教程如果數(shù)據(jù)表預計只會有少量的行,那就沒需要把主鍵定義為 INT 類型,可以用 MEDIUMINT、SMALLINT 甚至 TINYINT 來代替.(譯者注：對于日期數(shù)據(jù),)如果不需要時間部分,就應該使用 DATE 而不是 DATETIME.

MYSQL教程請確保留出合理的數(shù)據(jù)成長空間,不然就可能造成像Slashdot那樣的結果(譯者注：Slashdot 因為數(shù)據(jù)增長將評論表的主鍵改為了 INT 型,但沒有修改其父表中的相應的數(shù)據(jù)類型,雖然一個 ALTER 語句就可以辦理問題,但是需要至少停止某些業(yè)務三個小時).

MYSQL教程19. 選擇正確的存儲引擎

MYSQL教程MySQL 有兩個主要的存儲引擎：MyISAM 和 InnoDB,它們各有利弊.

MYSQL教程MyISAM 適用于讀哀求特別多的應用,但不適用于有大量寫哀求的情況.甚至你只是要更新一行中的某個字段,都會造成整張表被鎖,然后直到這個查詢完成,其它進程都不能從這張表讀取數(shù)據(jù).MyISAM 在計算 SELECT COUNT(*) 這種類型的查詢時速度非常快.

MYSQL教程InnoDB 是一個復雜的存儲引擎,在多數(shù)小型應用中它比 MyISAM 慢.但是它支持行級鎖,有更好的尺度.它還支持一些高級特性,好比事務.

• MYSQL教程MyISAM 存儲引擎

• MYSQL教程InnoDB 存儲引擎

MYSQL教程20. 使用對象關系映射器(ORM, Object Relational Mapper)

MYSQL教程通過使用ORM(對象關系映射器),你可以獲得必定的性能提升.ORM可以完成的一切事情,手動編碼也可完成.但這可能意味著需要太多額外的工作,并且需要高水平的專業(yè)知識.

ORM以“延遲加載”著稱.這意味著它們僅在需要時獲取實際值.但是你需要小心處理他們,否則你可能最終創(chuàng)建了許多微型查詢,這會降低數(shù)據(jù)庫性能.

ORM還可以將多個查詢批處理到事務中,其操作速度比向數(shù)據(jù)庫發(fā)送單個查詢快得多.

目前我最喜歡的PHP-ORM是Doctrine.我寫了一篇關于如何安裝Doctrine與CodeIgniter的文章(install Doctrine with CodeIgniter).

MYSQL教程21. 小心使用持久連接

MYSQL教程持久連接意味著減少重建連接到MySQL的成本. 當持久連接被創(chuàng)建時,它將堅持打開狀態(tài)直到腳本完成運行. 因為Apache重用它的子進程,下一次進程運行一個新的腳本時,它將重用相同的MySQL連接.

• MYSQL教程PHP：mysql_pconnect()

MYSQL教程理論上看起來不錯. 但從我個人(和許多其他人)的經驗看來,這個功能可能會導致更多麻煩. 你可能會出現(xiàn)連接數(shù)限制問題、內存問題等等.

Apache總是并行運行的,它創(chuàng)建許多子進程. 這是持久連接在這種環(huán)境中不克不及很好工作的主要原因. 在你考慮使用mysql_pconnect()之前,請咨詢你的系統(tǒng)管理員.

《Mysql必讀MySQL 的 20+ 條最佳實踐》是否對您有啟發(fā)，歡迎查看更多與《Mysql必讀MySQL 的 20+ 條最佳實踐》相關教程，學精學透。維易PHP學院為您提供精彩教程。

轉載請注明本頁網址：
http://www.snjht.com/jiaocheng/11207.html