MySQL資料庫最佳化技術之配置技巧總結_MySQL

本文實例講述了MySQL資料庫最佳化技術的配置方法。分享給大家參考，具體如下：

(一)減少資料庫存取

對於可以靜態化的頁面，盡量靜態化

對一個動態頁面中可以靜態的局部，採用靜態化

部分資料可以產生XML，或是文字檔案形式保存

使用資料快取技術，例如： MemCached

(二)優化的偵測方法

1.使用者體驗偵測

2.Mysql狀態偵測

在Mysql指令列裡面使用show status指令，得到目前mysql狀態。

主要關注下列屬性：

key_read_requests (索引讀取的請求數）(key_buffer_size設定影響)

key_reads（索引讀取響應數)

Key_blocks_used

Qcache_*

Open_tables(透過table_cache的設定影響)

Opened_tables

table_locks

3. 第三方工具偵測

mysqlreport http://hackmysql.com/mysqlreport

mytop http://jeremy.zawodny.com/mysql/mytop/

系統及Mysql的Log

系統指令： top, sar

Mysql的Log: slow_query.log

(三)硬體方面的最佳化

硬體方面，最容易成為Mysql瓶頸的部分是磁碟，其次是CPU和記憶體

磁碟方面

使用更快的磁碟，會對Mysql有很好的幫助

使用更多的硬碟，透過Raid，可以提高單一磁碟速度的問題

對於Raid方式，建議採用Raid 0+1 或 Raid 1+0

CPU

毫無疑問，較高主頻的CPU和較多的CPU數量可以給Mysql更

高的性能

記憶體

更高的內存，往往可以讓Mysql中的更多的資料緩存在內存中，

但是，一個重要的因素是，需要有正確的Mysql的配置

網卡

使用千兆網路卡及千兆網路

(四)作業系統方面的最佳化

1.不使用交換區。如果記憶體不足，增加更多的記憶體或配置你的系統使用較少記憶體

2. 不要使用NFS磁碟

3.增加系統和MySQL伺服器的開啟檔案數量

使用ulimit –n 65535

4.增加系統的進程和執行緒數量。

5.關閉不必要的應用，最佳化硬碟參數，使用hdparm測試

(五)應用級的最佳化

1.使用多伺服器負載平衡(多台讀取和寫入,用複製技術進行資料同步)

2.表的分區 (自訂分區,mysql5.1開始支援自帶分區功能)

3.使用資料快取技術memcached

(六)Mysql配置的最佳化

1.key_buffer(=512):索引緩衝所使用的記憶體數量

這對MyISAM表來說非常重要,設定在可用內存的25%-30%較好,通過檢查狀態值 Key_read_requests和 Key_reads,

可以知道key_buffer設定是否合理。比例key_reads / key_read_requests應該盡可能的低，至少是1:100，1:1000更好 ,否則說明 key_buffer 設定有點小

2.innodb_buffer_pool_size(= 512):索引緩衝使用的記憶體數量

3.table_cache (=1024):資料表快取區的尺寸

每當 MySQL 存取一個表格時，如果在表格緩衝區中還有空間，則該表就會被開啟並放入其中，這樣可以更快地存取表格內容。

透過檢查運行峰值時間的 Open_tables 和 Opened_tables 狀態值，可以決定是否需要調整 table_cache 的值。

如果你發現 open_tables 的值等於 table_cache，並且發現 opened_tables 狀態值不斷增長，那麼你就需要增加 table_cache 參數值了,

也不能盲目地把 table_cache 參數設定成很大的值，如果設定得太高，可能會造成檔案描述符不足，從而造成效能不穩定或連線失敗。

4.sort_buffer_size (=256):指定排序用緩衝區的長度

該參數對應的分配記憶體是每連接獨佔！如果有100個連接，那麼實際分配的總共排序緩衝區大小為100 × 6 ＝ 600MB。

所以，對於內存在4GB左右的伺服器建議設定為6-8M

5.join_buffer_size :關聯查詢用緩衝區的長度

4G記憶體以上，建議大於32M,此參數對應的分配記憶體也是每連線獨享！

6.max_connections (=1024):可以重複使用的執行緒數量

允許同時連接MySQL伺服器的客戶數量 ,可以觀察和估計系統在峰值最大的並發連接數來設定

7.thread_cache(=*):可以重複使用的執行緒數量

一般設定為CPU數×2

8.innodb_buffer_pool_size(= 512):innodb表快取池大小

這對Innodb表來說非常重要。 Innodb相比MyISAM表對緩衝更為敏感。 MyISAM可以在預設的 key_buffer_size 設定下運行的可以，

然而Innodb在預設的innodb_buffer_pool_size 設定下卻跟蝸牛似的。

由於Innodb把資料和索引都快取起來，無需留給作業系統太多的內存，因此如果只需要用Innodb的話則可以設定它高達 70-80% 的可用內存。

一些應用於 key_buffer 的規則有 -- 如果你的資料量不大，並且不會暴增，那麼無需把innodb_buffer_pool_size 設定的太大了.

9.innodb_flush_logs_at_trx_commit(=1) :交易提交後的日誌刷新模式

是否為Innodb比MyISAM慢1000倍而頭大?看來也許你忘了修改這個參數了。預設值是 1，這表示每次提交的更新事務(或每個事務之外的語句)都會刷新到磁碟中，

而這相當耗費資源，尤其是沒有電池備用快取時。很多應用程序，尤其是從 MyISAM轉變過來的那些，把它的值設為 2 就可以了，也就是不把日誌刷新到磁碟上，

而只刷新到作業系統的快取上。日誌仍然會每秒刷新到磁碟中去，因此通常不會遺失每秒1-2次更新的消耗。如果設定為0就快很多了，不過也相對不安全了,

MySQL伺服器崩潰時就會遺失一些交易。設定為2指揮遺失刷新到作業系統快取的那部分事務.

更多關於MySQL相關內容有興趣的讀者可查看本站專題：《MySQL索引操作技巧匯總》、《MySQL日誌操作技巧大全》、《MySQL事務操作技巧匯總》、《MySQL存儲過程技巧大全》、《 MySQL資料庫鎖定相關技巧總表》及《MySQL常用函數大匯總》

希望本文所述對大家MySQL資料庫計有所幫助。