1. 為查詢緩存優化你的查詢

大多數的MySQL服務器都開啟了查詢緩存。這是提高性最有效的方法之一，而且這是被MySQL的數據庫引擎處理的。當有很多相同的查詢被執行了多次的時候，這些查詢結果會被放到一個緩存中，這樣，后續的相同的查詢就不用操作表而直接訪問緩存結果了。

 

這里最主要的問題是，對于程序員來說，這個事情是很容易被忽略的。因為，我們某些查詢語句會讓MySQL不使用緩存。請看下面的示例：

1 // 查詢緩存不開啟

2 $r = mysql_query("SELECT username FROM user WHERE signup_date >= CURDATE()");

3   

4 // 開啟查詢緩存

5 $today = date("Y-m-d");

6 $r = mysql_query("SELECT username FROM user WHERE signup_date >= '$today'");

上面兩條SQL語句的差別就是 CURDATE() ，MySQL的查詢緩存對這個函數不起作用。所以，像 NOW() 和 RAND() 或是其它的諸如此類的SQL函數都不會開啟查詢緩存，因為這些函數的返回是會不定的易變的。所以，你所需要的就是用一個變量來代替MySQL的函數，從而開啟緩存。

2. 當只要一行數據時使用 LIMIT 1

當你查詢表的有些時候，你已經知道結果只會有一條結果，但因為你可能需要去fetch游標，或是你也許會去檢查返回的記錄數。

 

在這種情況下，加上 LIMIT 1 可以增加性能。這樣一樣，MySQL數據庫引擎會在找到一條數據后停止搜索，而不是繼續往后查少下一條符合記錄的數據。

 

下面的示例，只是為了找一下是否有“中國”的用戶，很明顯，后面的會比前面的更有效率。（請注意，第一條中是Select *，第二條是Select 1）

01 // 沒有效率的：

02 $r = mysql_query("SELECT * FROM user WHERE country = 'China'");

03 if (mysql_num_rows($r) > 0) {

04     // ...

05 }

06   

07 // 有效率的：

08 $r = mysql_query("SELECT 1 FROM user WHERE country = 'China' LIMIT 1");

09 if (mysql_num_rows($r) > 0) {

10     // ...

11 }

3. 千萬不要 ORDER BY RAND()

想打亂返回的數據行？隨機挑一個數據？真不知道誰發明了這種用法，但很多新手很喜歡這樣用。但你確不了解這樣做有多么可怕的性能問題。

 

如果你真的想把返回的數據行打亂了，你有N種方法可以達到這個目的。這樣使用只讓你的數據庫的性能呈指數級的下降。這里的問題是：MySQL會不得不去執行RAND()函數（很耗CPU時間），而且這是為了每一行記錄去記行，然后再對其排序。就算是你用了Limit 1也無濟于事（因為要排序）

 

下面的示例是隨機挑一條記錄

1 // 千萬不要這樣做：

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

3   

4 // 這要會更好：

5 $r = mysql_query("SELECT count(*) FROM user");

6 $d = mysql_fetch_row($r);

7 $rand = mt_rand(0,$d[0] - 1);

8   

9 $r = mysql_query("SELECT username FROM user LIMIT $rand, 1");

4. 避免 SELECT *

從數據庫里讀出越多的數據，那么查詢就會變得越慢。并且，如果你的數據庫服務器和WEB服務器是兩臺獨立的服務器的話，這還會增加網絡傳輸的負載。

 

所以，你應該養成一個需要什么就取什么的好的習慣。

1 // 不推薦

2 $r = mysql_query("SELECT * FROM user WHERE user_id = 1");

3 $d = mysql_fetch_assoc($r);

4 echo "Welcome {$d['username']}";

5   

6 // 推薦

7 $r = mysql_query("SELECT username FROM user WHERE user_id = 1");

8 $d = mysql_fetch_assoc($r);

9 echo "Welcome {$d['username']}";

5. 永遠為每張表設置一個ID

我們應該為數據庫里的每張表都設置一個ID做為其主鍵，而且最好的是一個INT型的（推薦使用UNSIGNED），并設置上自動增加的AUTO_INCREMENT標志。

 

就算是你 users 表有一個主鍵叫 “email”的字段，你也別讓它成為主鍵。使用 VARCHAR 類型來當主鍵會使用得性能下降。另外，在你的程序中，你應該使用表的ID來構造你的數據結構。

 

而且，在MySQL數據引擎下，還有一些操作需要使用主鍵，在這些情況下，主鍵的性能和設置變得非常重要，比如，集群，分區……

 

在這里，只有一個情況是例外，那就是“關聯表”的“外鍵”，也就是說，這個表的主鍵，通過若干個別的表的主鍵構成。我們把這個情況叫做“外鍵”。比如：有一個“學生表”有學生的ID，有一個“課程表”有課程ID，那么，“成績表”就是“關聯表”了，其關聯了學生表和課程表，在成績表中，學生ID和課程ID叫“外鍵”其共同組成主鍵。

 

6. 使用 ENUM 而不是 VARCHAR

ENUM 類型是非常快和緊湊的。在實際上，其保存的是 TINYINT，但其外表上顯示為字符串。這樣一來，用這個字段來做一些選項列表變得相當的完美。

 

如果你有一個字段，比如“性別”，“國家”，“民族”，“狀態”或“部門”，你知道這些字段的取值是有限而且固定的，那么，你應該使用 ENUM 而不是 VARCHAR。

 

MySQL也有一個“建議”（見第十條）告訴你怎么去重新組織你的表結構。當你有一個 VARCHAR 字段時，這個建議會告訴你把其改成 ENUM 類型。使用 PROCEDURE ANALYSE() 你可以得到相關的建議。

7. 盡可能的使用 NOT NULL

除非你有一個很特別的原因去使用 NULL 值，你應該總是讓你的字段保持 NOT NULL。這看起來好像有點爭議，請往下看。

首先，問問你自己“Empty”和“NULL”有多大的區別（如果是INT，那就是0和NULL）？如果你覺得它們之間沒有什么區別，那么你就不要使用NULL。（你知道嗎？在 Oracle 里，NULL 和 Empty 的字符串是一樣的！)

不要以為 NULL 不需要空間，其需要額外的空間，并且，在你進行比較的時候，你的程序會更復雜。當然，這里并不是說你就不能使用NULL了，現實情況是很復雜的，依然會有些情況下，你需要使用NULL值。

8. 把IP地址存成 UNSIGNED INT

很多程序員都會創建一個 VARCHAR(15) 字段來存放字符串形式的IP而不是整形的IP。如果你用整形來存放，只需要4個字節，并且你可以有定長的字段。而且，這會為你帶來查詢上的優勢，尤其是當你需要使用這樣的WHERE條件：IP between ip1 and ip2。

我們必需要使用UNSIGNED INT，因為 IP地址會使用整個32位的無符號整形。

而你的查詢，你可以使用 INET_ATON() 來把一個字符串IP轉成一個整形，并使用 INET_NTOA() 把一個整形轉成一個字符串IP

9. 拆分大的 DELETE 或 INSERT 語句

如果你需要在一個在線的網站上去執行一個大的 DELETE 或 INSERT 查詢，你需要非常小心，要避免你的操作讓你的整個網站停止相應。因為這兩個操作是會鎖表的，表一鎖住了，別的操作都進不來了。

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

 

所以，如果你有一個大的處理，你定你一定把其拆分，使用 LIMIT 條件是一個好的方法。下面是一個示例：

01 while (1) {

02     //每次只做1000條

03     mysql_query("DELETE FROM logs WHERE log_date <= '2009-11-01' LIMIT 1000");

04     if (mysql_affected_rows() == 0) {

05         // 沒得可刪了，退出！

06         break;

07     }

08     // 每次都要休息一會兒

09     usleep(50000);

10 }

10. 越小的列會越快

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

如果一個表只會有幾列罷了（比如說字典表，配置表），那么，我們就沒有理由使用 INT 來做主鍵，使用 MEDIUMINT, SMALLINT 或是更小的 TINYINT 會更經濟一些。