資料庫優化建議

　  資料庫優化建議（21條）~.~

• 為緩存優化你的查詢

  大多數的MySQL伺服器都開啟了看查詢緩存。這是提高性能最有效的方法之一，而且這是被MySQL的資料庫引擎處理的。當有很多的查詢被執行了多次的時候，這些查詢結果會被放到一個緩存中，這樣，後續的相同的查詢就不用操作表，而直接訪問緩存結果了。像NOW()和RAND()或者是其他的諸如此類的SQL函數都不會開啟查詢緩存，因為這些函數的返回是易變的，所以，需要用一個變數來代替MySQL的函數，從而開啟緩存

• EXPLAIN你的SELECT查詢

  使用SELECT查詢時，前面加上EXPLAIN關鍵字可以讓你知道MySQL是如何處理SQL語句的。這可以幫你分析你的查詢語句或是表結構的性能瓶頸。EXPLAIN的查詢結果還會告訴你你的索引主鍵是如何利用的，你的數據表是如何被搜索和排序的等等...

• 當只有一行數據時使用LIMTT1

  當查詢表的時候，已經知道結果只會有一條結果，但因為可能需要去fetch游標，或是會去檢查返回的記錄數。在這種情況下，加上LIMTT1可以增加性能。這樣，MySQL資料庫引擎會在找到一條數據後停止搜索，而不是繼續往後查找下一條符合記錄的數據。

• 為搜索欄位建立索引

  索引並不一定就是給主鍵或是唯一欄位。在一張表中，如果某個欄位經常會用來做搜索，那麼，就可以為其建立索引

• 在Join表得我時候使用相當類型的列，並將其索引

  如果一個應用程式有很多聯合（join）查詢，應該確認兩個表中關聯的欄位是被建過索引的，而且應該是相同類型的欄位。這樣，MySQL內部會啟動為你優化join的SQL語句的機制

• 不要使用ORDER BY RAND()

  這樣會打亂數據行，讓數據的性能呈指數級下降

• 避免SELECT *

  從資料庫里讀取的數據越多，那麼查詢就會變得越慢。如果你的資料庫伺服器和WEB伺服器是兩台獨立的伺服器的話，還會增加網路傳輸的負載。

• 為每張表都設置一個主鍵ID

  應該為資料庫里的每張表都設置一個ID做為其主鍵，而且最好的是一個INT型的（推薦使用UNSIGNED），用VARCHAR等類型來當主鍵會使用得性能下降，並設置上自動增加的AUTO_INCREMENT標誌。在程式中，應該根據表的ID來構造你的數據結構。

• 欄位的取值是有限而且固定的，使用ENUM

  ENUM類型是非常快和緊湊的。在實際上，其保存的是TINYINT，但其外表 上顯示為字元串。這樣一來，用這個欄位來做一些選項列表變得相當的完美。如果已經知道這些欄位的取值是有限而且固定的，那麼，你應該使用ENUM而不是VARCHAR

• 從PROCEDURE ANALYSE()取得建議

  PROCEDURE ANALYSE() 會讓MySQL幫你去分析你的欄位和其實際的數據，並會給你一些有用的建議。只有表中有實際的數據，這些建議才會變得有用，因為要做一些大的決定是需要有數據作為基礎的。

• 儘可能的使用NOT NULL

  除非特殊原因使用NULL值，否則應該總是讓欄位保持NOT NULL。NULL其實也需要額外的空間，所有儘可能的讓欄位保持非空約束

• Prepared Statements

  Prepared Statements很像存儲過程，是一種運行在後臺的SQL語句集合，我們可以從使用prepared statements獲得很多好處，無論是性能問題還是安全問題。Prepared Statements可以檢查一些綁定好的變數，這樣可以保護程式不會受到“SQL註入式”攻擊。在性能方面，當一個相同的查詢被使用多次的時候，這會帶來可觀的性能優勢。可以給這些Prepared Statements定義一些參數，而MySQL只會解析一次。 雖然最新版本的MySQL在傳輸Prepared Statements是使用二進位形勢，所以這會使得網路傳輸非常有效率。

• 無緩衝的查詢

  正常情況下，當在腳本中執行一個SQL語句的時候，程式會一直停滯，直到SQL執行完才繼續往下執行。可以使用無緩衝查詢來改變這個行為。mysql_unbuffered_query()發送一個SQL語句到MySQL而並不像mysql_query()一樣去自動fethch和緩存結果。這會相當節約很多可觀的記憶體，尤其是那些會產生大量結果的查詢語句，並且，你不需要等到所有的結果都返回，只需要第一行數據返回的時候，你就可以開始馬上開始工作於查詢結果了。然而，這會有一些限制。因為你要麼把所有行都讀走，或是你要在進行下一次的查詢前調用 mysql_free_result() 清除結果。而且， mysql_num_rows() 或 mysql_data_seek() 將無法使用。所以，是否使用無緩衝的查詢需要仔細考慮。

• 把IP地址存為UNSIGNED INT

  存放IP欄位時，需要使用UNSIGNED INT，因為IP地址會使用整個32位的無符號整形。如果你用整形來存放，只需要4個位元組，並且可以有定長的欄位。而且，這會帶來查詢上的優勢。在查詢中，可以使用INET_ATON()來把一個字元串IP轉成一個整形，並使用INET_NTOA()把一個整形轉成一個字元串IP。

• 給欄位設置固定長度

  如果表中的所有欄位都是“固定長度”的，整個表會被認為是 “static” 或 “fixed-length”。固定長度的表會提高性能，因為MySQL搜尋得會更快一些，因為這些固定的長度是很容易計算下一個數據的偏移量的，所以讀取的自然也會很快。而如果欄位不是定長的，那麼，每一次要找下一條的話，需要程式找到主鍵。並且，固定長度的表也更容易被緩存和重建。不過，唯一的副作用是，固定長度的欄位會浪費一些空間，因為定長的欄位無論你用不用，它都是要分配那麼多的空間。

• 垂直分割

  “垂直分割”是一種把資料庫中的表按列變成幾張表的方法，這樣可以降低表的複雜度和欄位的數目，從而達到優化的目的。小一點的表總是會有好的性能。

• 長度越小的列效率越快

  對於大多數資料庫引擎來說，硬碟操作可能是最重大的瓶頸。所以，把數據變得緊湊會非常使用有幫助，因為這減少了對硬碟的訪問。如果一個表只有幾列，那麼使用MEDIUMINT,SMALLINT或是更小的TINYINT會比INT更好。如果不需要記錄時間，使用DATE要比DATETIME好的多。甚至你只是需要update一個欄位，整個表都會被鎖起來，而別的進程，就算是讀進程都無法操作直到讀操作完成

• 選擇正確的存儲引擎

  在MySQL中有兩個存儲引擎MyISAM和InnoDB，每個引擎都有弊有利。MyISAM適合於大量查詢的應用，而對於大量寫操作的應用則支持的不太好。

  InnoDB是一個非常複雜的存儲引擎，對於一些小的應用，它會比MyISAM還慢，但是它支持行鎖，所以在寫操作比較多的時候，會更優秀。而且它還支持如事務等更多的高級應用。

• 使用對象關係映射器

  使用一個對象關係映射器（Object Relational Mapper），能夠獲得可靠的性能增漲。使用對象關係映射器，只有在需要去取值的時候才會真正去做，但這種機制的副作用是很可能會因為要去創建很多很小的查詢反而降低性能。對象關係映射器還可以把你的SQL語句打包成一個事物，這會比單獨執行SQL快得多。PHP中可以使用Doctrine對象關係映射器

• 小心永久鏈接

  “永久鏈接”的目的是用來減少重新創建MySQL鏈接的次數。當一個鏈接被創建了，它會永遠處在連接的狀態，就算資料庫操作已經結束了。而且，自從httpd開始重用它的子進程後，也就是說，下一次的HTTP請求會重用httpd的子進程，並重用相同的MySQL鏈接。在理論上來說，這聽起來非常的不錯。但是從個人經驗（也是大多數人的）上來說，這個功能製造出來的麻煩事更多。因為，你只有有限的鏈接數，記憶體問題，文件句柄數，等等。 而且，httpd運行在極端並行的環境中，會創建很多很多的子進程。這就是為什麼這種“永久鏈接”的機制工作地不好的原因。在決定要使用“永久鏈接”之前，需要好好地考慮一下整個系統的架構。