對mysql資料庫進行最佳化總結

在PHP的開發中使用mysql資料庫視乎已經成了每個PHP程式設計師的習慣性，而要想PHP操作mysql時，速度可以更快，更方便，那麼我們就得對mysql資料庫進行最佳化處理，對mysql優化在面試中也是很長被提問的，那我們就一起來看看吧！

1. 最佳化你的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'");
 // 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'");

2. 用EXPLAIN讓你的SELECT查詢更清晰

使用EXPLAIN關鍵字是另一個MySQL優化技巧，可以讓你了解MySQL正在進行什麼樣的查詢操作，這可以幫助你發現瓶頸的所在，並顯示出查詢或表結構在哪裡出了問題。
EXPLAIN查詢的結果，可以告訴你那些索引正在被引用，表是如何被掃描和排序的等等。

實作一個SELECT查詢（最好是比較複雜的一個，帶joins方式的），在裡面添加上你的關鍵字解釋，在這裡我們可以使用phpMyAdmin，他會告訴你表中的結果。舉例來說，如果我在執行joins時，正忘記往一個索引中添加列，EXPLAIN能幫助我找到問題的所在。

新增索引到group_id field後

3.利用LIMIT 1取得唯一行

############有時，當你要查詢一張表格是，你知道自己只需要看一行。你可能會去的一條十分獨特的記錄，或者只是剛好檢查了任何存在的記錄數，他們都滿足了你的WHERE子句。 #########在這種情況下，增加一個LIMIT 1會令你的查詢更有效。這樣資料庫引擎發現只有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.索引中的檢索欄位################## ###索引不僅是主鍵或唯一鍵。如果你想搜尋表中的任何列，你應該一直指向索引。 ##################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. 不要使用BY RAND()指令#########

这是一个令很多新手程序员会掉进去的陷阱。你可能不知不觉中制造了一个可怕的平静。这个陷阱在你是用BY RAND（）命令时就开始创建了。
如果您真的需要随机显示你的结果，有很多更好的途径去实现。诚然这需要写更多的代码，但是能避免性能瓶颈的出现。问题在于，MySQL可能会为表中每一个独立的行执行BY 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");

7. 尽量避免SELECT *命令

从表中读取越多的数据，查询会变得更慢。他增加了磁盘需要操作的时间，还是在数据库服务器与WEB服务器是独立分开的情况下。你将会经历非常漫长的网络延迟，仅仅是因为数据不必要的在服务器之间传输。
始终指定你需要的列，这是一个非常良好的习惯。

 // 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. 从PROCEDURE ANALYSE()中获得建议

PROCEDURE ANALYSE()可让MySQL的柱结构分析和表中的实际数据来给你一些建议。如果你的表中已经存在实际数据了，能为你的重大决策服务。

9. 准备好的语句

准备好的语句，可以从性能优化和安全两方面对大家有所帮助。
准备好的语句在过滤已经绑定的变量默认情况下，能给应用程序以有效的保护，防止SQL注入攻击。当然你也可以手动过滤，不过由于大多数程序员健忘的性格，很难达到效果。

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

10. 将IP地址存储为无符号整型

许多程序员在创建一个VARCHAR（15）时并没有意识到他们可以将IP地址以整数形式来存储。当你有一个INT类型时，你只占用4个字节的空间，这是一个固定大小的领域。
你必须确定你所操作的列是一个UNSIGNED INT类型的,因为IP地址将使用32位unsigned integer。

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

11.永远为每张表设置一个ID

我们应该为数据库里的每张表都设置一个ID做为其主键，而且最好的是一个INT型的(推荐使用UNSIGNED)，并设置上自动增加的AUTO_INCREMENT标志。
就算是你users表有一个主键叫“email”的字段，你也别让它成为主键。使用VARCHAR类型来当主键会使用得性能下降。另外，在你的程序中，你应该使用表的ID来构造你的数据结构。
而且，在MySQL数据引擎下，还有一些操作需要使用主键，在这些情况下，主键的性能和设置变得非常重要，比如，集群，分区……
在这里，只有一个情况是例外，那就是“关联表”的“外键”，也就是说，这个表的主键，通过若干个别的表的主键构成。我们把这个情况叫做“外键”。比如：有一个“学生表”有学生的ID，有一个“课程表”有课程ID，那么，“成绩表”就是“关联表”了，其关联了学生表和课程表，在成绩表中，学生ID和课程ID叫“外键”其共同组成主键。

12.使用ENUM而不是VARCHAR

#ENUM類型是非常快速且緊湊的。在實際上，其保存的是TINYINT，但其外表上顯示為字串。這樣一來，用這個字段來做一些選項清單變得相當的完美。
如果你有一個字段，例如“性別”，“國家”，“民族”，“狀態”或“部門”，你知道這些字段的取值是有限而且固定的，那麼，你應該使用ENUM而不是VARCHAR。
MySQL也有一個「建議」(見第十條)告訴你怎麼去重新組織你的表格結構。當你有一個VARCHAR字段時，這個建議會告訴你把其改成ENUM類型。使用PROCEDURE ANALYSE() 你可以得到相關的建議。

13.從PROCEDURE ANALYSE()取得建議p程式設計師站

PROCEDURE ANALYSE() 會讓MySQL幫你分析你的字段和其實際的數據，並會給你一些有用的建議。只有表中有實際的數據，這些建議才會變得有用，因為要做一些大的決定是需要有數據作為基礎的。
例如，如果你建立了一個INT欄位作為你的主鍵，然而並沒有太多的數據，那麼，PROCEDURE ANALYSE()會建議你把這個欄位的型別改成MEDIUMINT。或者你使用了一個VARCHAR字段，因為數據不多，你可能會得到一個讓你把它改成ENUM的建議。這些建議，都是可能因為數據不夠多，所以決策做得就不夠準。
在phpmyadmin裡，你可以在查看表時，點擊「Propose table structure」來查看這些建議

一定要注意，這些只是建議，只有當你的表裡的資料越來越多時，這些建議才會變得準確。一定要記住，你才是最終做決定的人

14.盡可能的使用NOT NULL php程式設計師站

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

首先，問問你自己「Empty」和「NULL」有多大的區別(如果是INT，那就是0和NULL)?如果你覺得它們之間沒有什麼區別，那麼你就不要使用NULL。 (你知道嗎?在Oracle裡，NULL 和Empty的字串是一樣的!)
不要以為NULL 不需要空間，其需要額外的空間，並且，在你進行比較的時候，你的程式會更複雜。當然，這裡並不是說你就不能使用NULL了，現實情況是很複雜的，還是會有些情況下，你需要使用NULL值。
下面摘自MySQL自己的文件：

15. Prepared Statements

Prepared Statements很像儲存過程，是一種運行在背景的SQL語句集合，我們可以從使用prepared statements獲得許多好處，無論是效能問題還是安全性問題。 Prepared Statements可以檢查一些你綁定好的變量，這樣可以保護你的程式不會受到「SQL注入式」攻擊。當然，你也可以手動地檢查你的這些變量，然而，手動的檢查容易出問題，而且很常會被程式設計師忘了。當我們使用一些framework或是ORM的時候，這樣的問題會好一點。
在效能方面，當一個相同的查詢被使用多次的時候，這會為你帶來可觀的效能優勢。你可以為這些Prepared Statements定義一些參數，而MySQL只會解析一次。
雖然最新版本的MySQL在傳輸Prepared Statements是使用二進位形勢，所以這會讓網路傳輸非常有效率。
當然，也有一些情況下，我們需要避免使用Prepared Statements，因為其不支援查詢快取。但據說版本5.1後支援了。 在PHP中要使用prepared statements，你可以查看其使用手冊：mysqli擴充或是使用資料庫抽象層，如：PDO.

16.無緩衝的查詢

正常的情況下，當你在當你在你的腳本中執行一個SQL語句的時候，你的程式會停在那裡直到沒這個SQL語句返回，然後你的程式再往下繼續執行。你可以使用無緩衝查詢來改變這個行為。
關於這個事情，在PHP的文檔中有一個非常不錯的說明：mysql_unbuffered_query()函數：
上面那句話翻譯過來是說，mysql_unbuffered_query()發送一個SQL語句到MySQL而不像mysql_query()一樣去自動fethch和快取結果。這會相當節約很多可觀的內存，尤其是那些會產生大量結果的查詢語句，並且，你不需要等到所有的結果都返回，只需要第一行數據返回的時候，你就可以開始馬上開始工作於查詢結果了。
然而，這會有一些限制。因為你要嘛把所有行都讀走，或是你要在進行下一次的查詢前呼叫 mysql_free_result() 清除結果。而且， mysql_num_rows() 或 mysql_data_seek() 將無法使用。所以，是否使用無緩衝的查詢你需要仔細考慮。

17.把IP位址存成UNSIGNED INT

很多程式設計師都會建立一個VARCHAR(15) 欄位來存放字符串形式的IP而不是整形的IP。如果你用整形來存放，只需要4個字節，並且你可以有定長的字段。而且，這會為你帶來查詢上的優勢，尤其是當你需要使用這樣的WHERE條件：IP between ip1 and ip2。
我們必需要使用UNSIGNED INT，因為IP位址會使用整個32位元的無符號整形。
而你的查詢，你可以使用 INET_ATON()來把一個字串IP轉成一個整形，並使用INET_NTOA()把一個整形轉成一個字串IP。在PHP中，也有這樣的函數 ip2long()和long2ip()。

18.固定長度的表格會更快

#如果表格中的所有欄位都是「固定長度」的，整個表會被認為是“static” 或“fixed-length”。 例如，表中沒有以下類型的欄位： VARCHAR，TEXT，BLOB。只要你包括了其中一個這些字段，那麼這個表就不是「固定長度靜態表」了，這樣，MySQL 引擎會用另一種方​​法來處理。
固定長度的表會提高效能，因為MySQL搜尋得會更快一些，因為這些固定的長度是很容易計算下一個資料的偏移量的，所以讀取的自然也會很快。而如果欄位不是定長的，那麼，每次要找下一條的話，就需要程式找到主鍵。
並且，固定長度的表也更容易被快取和重建。不過，唯一的副作用是，固定長度的字段會浪費一些空間，因為定長的字段無論你用不用，他都是要分配那麼多的空間。 php程式設計師站
使用「垂直分割」技術(見下一條)，你可以分割你的表成為兩個一個是定長的，一個則是不定長的。

19.垂直分割

「垂直分割」是一種把資料庫中的表按列變成幾張表的方法，這樣可以降低表格的複雜度和欄位的數目，從而達到最佳化的目的。 (以前，在銀行做過項目，見過一張表有100多個字段，很恐怖)
示例一：在Users表中有一個字段是家庭地址，這個字段是可選字段，相比起，而且你在資料庫操作的時候除了個人資訊外，你並不需要經常讀取或是改寫這個欄位。那麼，為什麼不把他放到另外一張表中呢?這樣會讓你的表有更好

#相關推薦：

MySQL最佳化總結-查詢總條數

#

以上是對mysql資料庫進行最佳化總結的詳細內容。更多資訊請關注PHP中文網其他相關文章！