MySQL的最佳化（一）

一、我們可以且應該最佳化什嗎？  

硬體  

作業系統/軟體庫  

SQL伺服器(設定和查詢)  

應用編程介面(API)  

應用程式  

------------------------------------------------------------  

二、最佳化硬體  

如果你需要龐大的資料庫表(> 2G )，你應該考慮使用64位的硬體結構，像Alpha、Sparc或即將推出的IA64。因為MySQL內部使用大量64位的整數，64位的CPU將提供更好的效能。  

對大資料庫，最佳化的次序一般是RAM、快速硬碟、CPU能力。  

更多的記憶體通過將最常用的鍵碼頁面存放在記憶體中可以加速鍵碼的更新。  

如果不使用事務安全(transaction-safe)的表或有大表並且想避免長檔案檢查，一台UPS就能夠在電源故障時讓系統安全關閉。  

對於資料庫存放在一個專用伺服器的系統，應該考慮 1G
的乙太網路。延遲與輸送量同樣重要。  

----------------------------------------------------------------  

三、最佳化磁碟  

為系統、程式和臨時檔案配備一個專用磁碟，如果確是進行很多修改工作，將更新日誌和交易記錄放在專用磁碟上。  
低尋道時間對資料庫磁碟非常重要。對與大表，你可以估計你將需要log(行數)/log(索引塊長度/3*2/(鍵碼長度  +  資料指標長度))+1次尋到才能找到一行。對於有500000行的表，索引Mediun  int類型的列，需要log(500000)  /  log(1024/3*2/(3  +  2))+1=4次尋道。上述索引需要500000*7*3/2=
5.2M 的空間。實際上，大多數塊將被緩衝，所以大概只需要1-2次尋道。  
然而對於寫入（如上），你將需要4次尋道請求來找到在哪裡存放新鍵碼，而且一般要2次尋道來更新索引並寫入一行。  
對於大型資料庫，你的應用將受到磁碟尋道速度的限制，隨著資料量的增加呈N  log  N資料級遞增。  
將資料庫和表分在不同的磁碟上。在MySQL中，你可以為此而使用符號連結。  
條列磁碟(RAID  0)將提高讀和寫的輸送量。  
帶鏡像的條列(RAID  0+1)將更安全並提高讀取的輸送量。寫入的輸送量將有所降低。  
不要對臨時檔案或可以很容易地重建的資料所在的磁碟使用鏡像或RAID(除了RAID  0)。  
在Linux上，在引導時對磁碟使用命令hdparm  -m16  -d1以啟用同時讀寫多個扇區和DMA功能。這可以將回應時間提高5~50%。  
在Linux上，用async  (預設)和noatime掛載磁碟(mount)。  
對於某些特定應用，可以對某些特定表使用記憶體磁碟，但通常不需要。  

---------------------------------------------------------------  

四、最佳化作業系統  

不要交換區。如果記憶體不足，增加更多的記憶體或配置你的系統使用較少記憶體。  
不要使用NFS磁碟(會有NFS鎖定的問題)。  
增加系統和MySQL伺服器的開啟檔案數量。(在safe_mysqld指令碼中加入ulimit  -n  #)。  
增加系統的進程和線程數量。  
如果你有相對較少的大表，告訴檔案系統不要將檔案打碎在不同的磁軌上(Solaris)。  
使用支援大檔案的檔案系統(Solaris)。  
選擇使用哪種檔案系統。在Linux上的Reiserfs對於開啟、讀寫都非常快。檔案檢查只需幾秒種。  

----------------------------------------------------------------  

五、選擇應用編程介面  

PERL  
可在不同的作業系統和資料庫之間移植。  
適宜快速原型。  
應該使用DBI/DBD介面。  
PHP  
比PERL易學。  
使用比PERL少的資源。  
通過升級到PHP4可以獲得更快的速度。  
C  
MySQL的原生介面。  
較快並賦予更多的控制。  
低層，所以必須付出更多。  
C++  
較高層次，給你更多的時間來編寫應用。  
仍在開發中  
ODBC  
運行在Windows和Unix上。  
幾乎可在不同的SQL伺服器間移植。  
較慢。MyODBC只是簡單的直通驅動程式，比用原生介面慢19%。  
有很多方法做同樣的事。很難像很多ODBC驅動程式那樣運行，在不同的領域還有不同的錯誤。  
問題成堆。Microsoft偶爾還會改變介面。  
不明朗的未來。(Microsoft更推崇OLE而非ODBC)  
JDBC  
理論上可在不同的作業系統何時據庫間移植。  
可以運行在web用戶端。  
Python和其他  
可能不錯，可我們不用它們。  

---------------------------------------------------------------  

六、最佳化應用  

應該集中精力解決問題。  
在編寫應用時，應該決定什麼是最重要的：  
速度  
作業系統間的可移植性  
SQL伺服器間的可移植性  
使用持續的串連。.  
緩衝應用中的資料以減少SQL伺服器的負載。  
不要查詢應用中不需要的列。  
不要使用SELECT  *  FROM  table_name...  
測試應用的所有部分，但將大部分精力放在在可能最壞的合理的負載下的測試整體應用。通過以一種模組化的方式進行，你應該能用一個快速“啞模組”替代找到的瓶頸，然後很容易地標出下一個瓶頸。  
如果在一個批處理中進行大量修改，使用LOCK  TABLES。例如將多個UPDATES或DELETES集中在一起。  

---------------------------------------------------------------  

七、應該使用可移植的應用  

Perl  DBI/DBD  
ODBC  
JDBC  
Python(或其他有普遍SQL介面的語言)  
你應該只使用存在於所有目的SQL伺服器中或可以很容易地用其他構造類比的SQL構造。www.mysql.com上的Crash-me頁可以協助你。  
為作業系統/SQL伺服器編寫封裝程式來提供缺少的功能。  

-------------------------------------------------------------  

八、如果你需要更快的速度，你應該：  

找出瓶頸(CPU、磁碟、記憶體、SQL伺服器、作業系統、API或應用)並集中全力解決。  
使用給予你更快速度/靈活性的擴充。  
逐漸瞭解SQL伺服器以便能為你的問題使用可能最快的SQL構造並避免瓶頸。  
最佳化表布局和查詢。  
使用複製以獲得更快的選擇(select)速度。  
如果你有一個慢速的網路連接資料庫，使用壓縮客戶/伺服器協議。  
不要害怕時應用的第一個版本不能完美地移植，在你解決問題時，你總是可以在以後最佳化它。  

-------------------------------------------------------------  

九、最佳化MySQL  

挑選編譯器和編譯選項。  
位你的系統尋找最好的啟動選項。  
通讀MySQL參考手冊並閱讀Paul  DuBios的《MySQL》一書。(已有中文版-譯註)  
多用EXPLAIN  SELECT、SHOW  VARIABLES、SHOW  STATUS和SHOW  PROCESSLIST。  
瞭解查詢最佳化工具的工作原理。  
最佳化表的格式。  
維護你的表(myisamchk、CHECK  TABLE、  OPTIMIZE  TABLE)  
使用MySQL的擴充功能以讓一切快速完成。  
如果你注意到了你將在很多場合需要某些函數，編寫MySQL  UDF函數。  
不要使用表級或列級的GRANT，除非你確實需要。  
購買MySQL支援人員以協助你解決問題:)  

-----------------------------------------------------------  

十、編譯和安裝MySQL  

通過位你的系統挑選可能最好的編譯器，你通常可以獲得10-30%的效能提高。  
在Linux/Intel平台上，用pgcc(gcc的奔騰晶片最佳化版)編譯MySQL。然而，二進位代碼將只能運行在Intel奔騰CPU上。  
對於一種特定的平台，使用MySQL參考手冊上推薦的最佳化選項。  
一般地，對特定CPU的原生編譯器(如Sparc的Sun  Workshop)應該比gcc提供更好的效能，但不總是這樣。  
用你將使用的字元集編譯MySQL。  
靜態編譯產生mysqld的執行檔案(用--with-mysqld-ldflags=all-static)並用strip  sql/mysqld整理最終的執行檔案。  
注意，既然MySQL不使用C++擴充，不帶擴充支援編譯MySQL將贏得巨大的效能提高。  
如果作業系統支援原生線程，使用原生線程(而不用mit-pthreads)。  
用MySQL基準測試來測試最終的二進位代碼。