20+MySQL的最佳實踐

Evangelined3 8年前發布 | 8K 次閱讀 MySQL 數據庫服務器

這篇文章講的非常好，遵循里面的實踐會讓開發者少走不少彎路，但是譯者并不完全同意本文的觀點，異議會以譯者注這樣的形式提出。

在web應用中，數據庫常常會成為性能瓶頸。解決這些性能問題不只是DBA的事，作為開發者，一樣有義務通過設計合理的表結構，采用優化后的查詢和編寫更好的代碼來提高數據庫的性能。在這篇文章中，將列舉一些針對開發者的MySQL優化實踐。

查詢時使用緩存

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

上面一條sql將不會讀取緩存，因為用到了 CURDATE() 這個方法，其它的一些方法會導致一樣的后果，像 NOW() , RAND() 。這些函數的返回結果會變，所以MySQL會對這個查詢不使用緩存。如果想要從緩存中讀取，就使用上面列出的第二條語句。

使用EXPLAIN

使用 EXPLAIN 關鍵字能夠讓你知道在執行查詢的時候MySQL到底做了什么。這會幫助你調試，從而發現某些問題。

EXPLAIN 語句會展示哪些索引會被使用到，表是如何被掃描，排序的。

寫一條sql，在前面加上EXPLAIN關鍵字。可以用phpadmin編輯這個信息。

假設忘了給一個字段加索引，執行下面的語句

EXPLAIN SELECT username, group_name 
FROM users u 
JOIN groups g ON (u.group_id=g.id) 
WHERE g.id BETWEEN 1 AND 10

提示沒加索引

給group_id 加了索引后

加了索引后

可以看到，沒加索引前，掃描了7883行，加了索引后掃描了9，16行，執行效率大大提高了。

查詢返回一行結果時使用LIMIT 1

有時在執行查詢時，知道結果只會返回一行。你想查找一個unique記錄，或者你只想檢查一下任意行的記錄是否存在(譯者注：這種情況應該使用COUNT)。

這樣的情況下，在查詢中加入 LIMIT 1 可以提升性能。因為加入了limit語句，數據庫引擎在找到一條數據后，就會停止查詢，而不會全表掃描。

添加索引

索引并不僅僅是 primary key 或者是 unique key 。如果有字段會被經常查詢到，絕大多數情況下都應該給她們加上索引。

示例查詢

正如上面的示例，即使是像這樣的查詢

... WHERE last_name LIKE 'a%'

數據庫引擎依然會用上索引。

同時，也應該明白有些查詢即使添加了索引查詢效率也不會提升，比如這樣的查詢

... WHERE post_content LIKE '%apple%'

JOIN查詢條件的優化

在采用大量JOIN查詢時，應該讓條件兩邊的字段都是所在表的索引，并且兩遍的字段類型要一致。

$r = mysql_query("SELECT company_name FROM users
    LEFT JOIN companies ON (users.state = companies.state)
    WHERE users.id = $user_id");

比如上面的查詢，應該為users的state，和companies的state添加索引，并確認它們的類型一致，如果它們類型不一致的話，MySQL將不會用到它們的索引。對于某些字符串字段，編碼也應該保持一致。

不要使用RAND()

有些技巧新手會覺得很酷，但在性能上會很坑，比如下面這條。

$r = mysql_query(
"SELECT username FROM user ORDER BY RAND() LIMIT 1"
);

這條語句的問題在于，MySQL會對表中的每一條數據執行 RAND 操作（這樣會消耗計算），然后排序，再返回一條數據。如果真的需要獲取一條隨機的結果，可以這樣做

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

上面的代碼生成一個小于總行數的值，然后將這個值設為offset，返回一條數據。

不要使用SELECT *

查詢的數據越多，查詢的效率越慢。因為這增加了磁盤IO的時間。當數據庫和應用不在同一臺機器上時，這還會增加網絡的延遲。

只取想要的字段是個好習慣

// 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

總是為表設計一個id字段

為每一張表設計一個id字段，并設置為PRIMARY KEY, AUTO_INCREMENT。類型最好味UNSIGNED，因為這個字段不可能為負。

甚至你的users表中每個人的username都不同，也不要讓username成為主鍵，因為VARCHAR類型作為主鍵效率更低。

多用ENUM少用VARCHAR

ENUM類型在內部是作為TINYINT類型存儲的，所以又快又節省空間，并且還能存儲字符串。這讓它成為某些字段的最好選擇。

如果有一個字段，里面的類型是有限的，比如，status，里面的值可能只有（ "active", "inactive", "pending", "expired"），這時選用用ENUM。

（譯者注：譯者并不完全同意這個觀點，實際上在軟件開發的過程中，字段出現變化是很正常的事，用VARCHAR可以更好得保持開發靈活性）

善用ANALYSE()

可以使用 ANALYSE過程讓MySQL對當前的表和數據進行分析，并提出一些改進意見。只有當表里面存有數據的時候，這個分析才有意義。

舉個例子，如果表中有一個主鍵是INT型，而數據量并不大的情況下，MySQL會建議將這個主鍵類型改成MEDIUMINT；如果字段使用的是VARCHAR，而值比較單一，MySQL會建議將類型改成ENUM。

注意，這只是建議，最終還是得你自己拿意見。

盡可能使用NOT NULL

除非有特別的原因使用NULL，否則應該永遠將字段設置我哦NOT NULL。

首先，想清楚，使用空字符串和NULL有什么區別（或者0和NULL有什么區別），如果沒有區別的話，就不要使用NULL（oracle里NULL和空字符串是同義的）。

MySQL官方文檔這樣說的

"NULL columns require additional space in the row to record whether their values are NULL. For MyISAM tables, each NULL column takes one bit extra, rounded up to the nearest byte."

使用預處理語句

有很多的理由來使用預處理語句，不論是考慮到性能還是安全因素。

預處理會檢查綁定好的變量，這樣對防治SQL注入攻擊很有好處。當然你也可以通過肉眼來檢查，但是手工的方法很容易造成錯誤。使用成熟的框架或者ORM也可以減少這方面的隱患。

再說說性能問題。當同一條查詢被使用多次的時候，使用預處理會提升性能。你可以對同一條預處理語句傳遞多次值，MySQL只會解析一次。

最新的MySQL版本會將預處理語句以二進制文件的形式傳輸，這樣更高效，并會降低網絡擁堵。

之前的預處理語句是不會被緩存的，但自從5.1版本后，預處理語句也會被緩存了。

下面是預處理語句的PHP示例

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

無緩存的查詢

每當你執行一條查詢時，程序會等待查詢的結果出來后再進行下面的邏輯。

使用。使用無緩存查詢可以改變這一行為。

用UNSIGNED INT類型存儲IP地址

有很多程序員會將IP地址存為VARCHAR(15) 類型，實際上完全可以將IP地址存為4個字節的固定長度。

固定長度的表查詢效率更快

當一個表中所有的字段長度都是固定的時候，這個表通常被認為是static 或者是 fixed-length 。長度不固定的類型有VARCHAR，BLOB，TEXT，當表中出現上面的任意一種類型時，這個表就不再被認為是fixed-length，引擎在處理的時候會采用不同的方法。

固定長度的表會提高性能，MySQL查詢會更快一些，因為固定的長度是很容易計算下一個數據的偏移量的，所以讀取的自然也會很快。而如果字段不是定長的，那么，每一次要找下一條的話，都需要程序找到主鍵。

并且，固定長度的表也更容易被緩存和重建。不過，唯一的副作用是，固定長度的字段會浪費一些空間，因為定長的字段無論你用不用，他都是要分配那么多的空間。

垂直分割

“垂直分割”是一種把數據庫中的表按列變成幾張表的方法，這樣可以降低表的復雜度和字段的數目，從而達到優化的目的。

示例一：在Users表中有一個字段是家庭地址，這個字段是可選字段，相比起其它字段，你并不需要經常讀取或是改寫這個它。那么，為什么不把它放到另外一張表中呢？這樣會讓你的表有更好的性能。小一點的表性能總是更好。

示例二：你有一個叫 “last_login” 的字段，它會在每次用戶登錄時被更新。但是，每次更新時會導致該表的查詢緩存被清空。所以，你可以把這個字段放到另一個表中，這樣就不會影響你對用戶ID，用戶名，用戶角色的不停地讀取了，因為查詢緩存會幫你增加很多性能。

另外，你需要注意的是，這些被分出去的字段所形成的表，最好經常性地去Join他們，不然的話，這樣的性能會比不分割時還要差，而且，會是極數級的下降。

拆分大的INSERT和DELETE語句

當在一個在線網站執行大的INSERT和DELETE語句時，需要格外小心，因為這樣的操作會鎖表，這可能會導致網站陷入停頓。

Apache 會有很多的子進程或線程。當所有的子進程或線程能快速完成任務時，其效率是最高的。而服務器不希望有過多的子進程和連接，這相當地消耗資源，尤其是內存。

如果你把你的表鎖上一段時間，比如30秒鐘，那么對于一個有很高訪問量的站點來說，這30秒所積累的訪問進程/線程，數據庫鏈接，打開的文件數，可能不僅僅會讓你的WEB服務Crash，還可能會讓你的整臺服務器馬上掛了。

所以，如果當需要大規模地刪除表中的數據時，一定把DELETE語句拆分，使用 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);
}

越小的列會越快

對于大多數的數據庫引擎來說，硬盤操作可能是最大的瓶頸。所以，把你的數據變得緊湊會對這種情況非常有幫助，因為這減少了對硬盤的訪問。

參看 MySQL 的文檔 Storage Requirements 查看所有的數據類型。

如果一個表只會有幾列罷了（比如說字典表，配置表），那么，我們就沒有理由使用 INT 來做主鍵，使用 MEDIUMINT, SMALLINT 或是更小的 TINYINT 會更經濟一些。如果你不需要記錄時間，使用 DATE 要比 DATETIME 好得多。

當然，你也需要留夠足夠的擴展空間，不然，你日后來干這個事，你會死的很難看，參看 Slashdot的例子（2009年11月06日），一個簡單的ALTER TABLE語句花了3個多小時，因為里面有一千六百萬條數據。

選擇正確的存儲引擎

在 MySQL 中有兩個存儲引擎 MyISAM 和 InnoDB，每個引擎都有利有弊。

MyISAM 適合于一些需要大量查詢的應用，但其對于有大量寫的操作支持并不是很好。甚至你只是需要update一個字段，整個表都會被鎖起來，而別的進程，就算是讀進程都無法操作直到update操作完成。另外，MyISAM 對于 SELECT COUNT(*) 這類的計算是超快無比的。

InnoDB 的趨勢會是一個非常復雜的存儲引擎，對于一些小的應用，它會比 MyISAM 還慢。但是它支持“行鎖” ，于是在寫操作比較多的時候，會更優秀。并且，他還支持更多的高級應用，比如：事務。

使用一個對象關系映射器（Object Relational Mapper）

使用 ORM (Object Relational Mapper)，你能夠獲得可靠的性能增漲。一個ORM可以做的所有事情，也能被手動的編寫出來。但是，這需要一個高級專家。

ORM 的最重要的是“Lazy Loading”，也就是說，只有在需要的去取值的時候才會去真正的去做。但你也需要小心這種機制的副作用，因為這很有可能會因為要去創建很多很多小的查詢反而會降低性能。

ORM 還可以把你的SQL語句打包成一個事務，這會比單獨執行他們快得多得多。

小心“永久鏈接”

“永久鏈接”的目的是用來減少重新創建MySQL鏈接的次數。當一個鏈接被創建了，它會永遠處在連接的狀態，就算是數據庫操作已經結束了。而且，自從我們的Apache開始重用它的子進程后——也就是說，下一次的HTTP請求會重用Apache的子進程，并重用相同的 MySQL 鏈接。

在理論上來說，這聽起來非常的不錯。但是從個人經驗（也是大多數人的）上來說，這個功能制造出來的麻煩事更多。因為，你只有有限的鏈接數，內存資源，文件句柄數，等等。

而且，Apache 運行在極端并行的環境中，會創建很多很多的子進程。這就是為什么這種“永久鏈接”的工作機制不好的原因。在你決定要使用“永久鏈接”之前，你需要好好地考慮一下你的整個系統的架構。

來自：http://www.jianshu.com/p/fd80948c99b0

本文由用戶 Evangelined3 自行上傳分享，僅供網友學習交流。所有權歸原作者，若您的權利被侵害，請聯系管理員。

轉載本站原創文章，請注明出處，并保留原始鏈接、圖片水印。

本站是一個以用戶分享為主的開源技術平臺，歡迎各類分享！

本文地址：http://www.baiduhome.net/lib/view/open1473835478916.html

MySQL 數據庫服務器