mysql 效能調優五種方式

(一)連線

連線通常來自Web伺服器，以下列出了一些與連線有關的參數，以及該如何設定它們。

1、max_connections

這是Web伺服器允許的最大連接數，記住每個連接都要使用會話內存(關於會話內存，文章後面有涉及)。

2、max_packet_allowed

最大資料包大小，通常等於你需要在一個大塊中傳回的最大資料集的大小，如果你在使用遠端mysqldump，那它的值需要更大。

3、aborted_connects

檢查系統狀態的計數器，確定其沒有增長，如果數量增長說明客戶端連接時遇到了錯誤。

4、thread_cache_size

入站連接會在MySQL中創建一個新的線程，因為MySQL中打開和關閉連接都很廉價，速度也快，它就沒有像其它數據庫，如Oracle那麼多持續連接了，但線程預先創建並不會節省時間，這就是為什麼要MySQL線程快取的原因了。

如果在增長請密切注意創建的線程，讓你的線程緩存更大，對於2550或100的thread_cache_size，內存佔用也不多。

(二)查詢快取

MySQL中的快取查詢包括兩個解析查詢計劃，以及傳回的資料集，如果基礎表資料或結構有變化，將會使查詢快取中的項目無效。

1、query_cache_min_res_unit

MySQL參數中query_cache_min_res_unit查詢快取中的區塊是以這個大小進行分配的，使用下面的公式計算查詢快取的平均大小，根據計算結果設定這個變數，MyMy會更有效地使用SQL緩存，緩存更多的查詢，減少內存的浪費。 

2、query_cache_size

這個參數設定查詢快取的總大小。

3、query_cache_limit

這個參數告訴MySQL丟掉大於這個大小的查詢，一般大型查詢還是比較少見的，如運行一個批次執行一個大型報表的統計，因此那些大型結果集不應該填滿查詢快取。

qcache hit ratio = qcache_hits / (qcache_hits + com_select)

使用

SQL> show status like 'qcache%';

> showg;

average query size = (query_cache_size - qcache_free_memory)/qcache_queries_in_cache

使用

SQL> show variables likedry%'; atus like 'qcache%' ;

取得query_cache_size的值。

(三)臨時表

記憶體速度是相當快的，因此我們希望所有的排序操作都在記憶體中進行，我們可以透過調整查詢讓結果集更小以實現記憶體排序，或將變數設定得更大。

tmp_table_sizemax_heap_table_size

無論何時在MySQL中建立臨時表，它都會使用這兩個變數的最小值作為臨界值，除了在磁碟上建立臨時表外，還會建立許多會話，這些會話會搶佔有限制的資源，因此最好是調整查詢而不是將這些參數設定得更高，同時，需要注意的是有BLOB或TEXT欄位類型的表將直接寫入磁碟。深入淺出MySQL雙向複製技術

(四)會話內存

MySQL中每個會話都有其自己的內存，這個內存就是分配給SQL查詢的內存，因此你想讓它變得盡可能大以滿足需要。但你不得不平衡同一時間資料庫內一致性會話的數量。這裡顯得有點黑色藝術的是MySQL是按需分配快取的，因此，你不能只添加它們並乘以會話的數量，這樣估算下來比MySQL典型的使用要大得多。最佳做法是啟動MySQL，連接所有會話，然後繼續關注頂級會話的VIRT列，mysqld行的數量通常保持相對穩定，這就是實際的內存總用量，減去所有的靜態MySQL內存區域，就得到了實際的所有會話內存，然後除以會話的數量就得到平均值。

1、read_buffer_size快取連續掃描的區塊，這個快取是跨儲存引擎的，不只是MyISAM表。

2、sort_buffer_size

執行排序快取區的大小，最好將其設定為1M-2M，然後在會話中設置，為一個特定的查詢設定更高的值。

3、join_buffer_size

執行聯合查詢分配的快取區大小，將其設定為1M-2M大小，然後在每個會話中再單獨按需設定。

4、read_rnd_buffer_size

用於排序和order by操作，最好將其設為1M，然後在會話中可以將其作為會話變數設為更大的值。

(五)慢速查詢日誌

慢速查詢日誌是MySQL很有用的一個特性。

1、log_slow_queries

MySQL參數中log_slow_queries參數在my.cnf檔案中設定它，將其設為on，預設情況下，MySQL會將檔案放到資料目錄，檔案以「主機名稱-slow.log」的形式命名，但你在設定這個選項的時候也可以為其指定一個名字。

2、long_query_time

預設值是10秒，你可以動態設定它，值從1到將其設為on，如果資料庫啟動了，預設情況下，日誌將關閉。截至5.1.21和安裝了Google補丁的版本，這個選項可以以微秒設置，這是一個了不起的功能，因為一旦你消除了所有查詢時間超過1秒的查詢，說明調整非常成功，這樣可以幫助你在問題變大之前消除問題SQL。

3、log_queries_not_using_indexes

開啟這個選項是個不錯的主意，它真實地記錄了傳回所有行的查詢。

小結

我們介紹了MySQL參數的五大類設置，平時我們一般都很少碰它們，在進行數據庫性能調優和故障診斷時這些參數還是非常有用的。