MySql資料庫3【最佳化3】緩衝設定的最佳化

標籤：style   io   使用   ar   strong   檔案   資料   問題   sp   

1、表緩衝

相關參數： table_open_cache

　　指定表緩衝的大小。每當MySQL訪問一個表時，如果在表緩衝區中還有空間，該表就被開啟並放入其中，這樣可以更快地訪問表內容。通過檢查峰值時間的狀態值，如果發現open_tables等於table_cache，並且opened_tables在不斷增長，那麼就需要增加table_open_cache的值了。注意，不能盲目地把這個參數設定得很大，如果設定太大，會引起檔案描述符不足，造成效能不穩定或者資料庫連接失敗。建議為512

　　table_cache = 512指定表快取的大小, 如果opened_tables太大,應該把table_cache變大。每當MySQL訪問一個表時，如果在表緩衝區中還有空間，該表就被開啟並放入其中，這樣可以更快地訪問表內容。通過檢查峰值時間的狀態值Open_tables和Opened_tables，可以決定是否需要增加table_cache的值。如果你發現 open_tables等於table_cache，並且opened_tables在不斷增長，那麼你就需要增加table_cache的值了(上述狀態值可以使用SHOW STATUS LIKE ‘Open%tables‘獲得)。注意，不能盲目地把table_cache設定成很大的值。如果設定得太高，可能會造成檔案描述符不足，從而造成效能不穩定或者串連失敗。

 

2、查詢快取

相關參數： query_cache_size / query_cache_type

　　這個參數也是一個重要的最佳化參數。QC（註：查詢快取簡稱） 主要用來緩衝 MySQL 中的 結果集，也就是一條SQL語句執行的結果集，所以僅僅只能針對select 語句。在MySQL伺服器上進行查詢，可以啟用高速查詢快取。讓資料庫引擎在後台悄悄的處理是提高效能的最有效方法之一。當同一個查詢被執行多次時，如果結果是從緩衝中提取，那是相當快的。

　　但隨著發展，這個參數也爆露出來一些問題。機器的記憶體越來越大，人們也都習慣性的把以前有用的參數分配的值越來越大。這個參數加大後也引發了一系列問題。我們首先分析一下 query_cache_size的工作原理：一個SELECT查詢在DB中工作後，DB會把該語句緩衝下來，當同樣的一個SQL再次來到DB裡調用時，DB在該表沒發生變化的情況下把結果從緩衝中返回給Client。這裡有一個關建點，就是DB在利用Query_cache工作時，要求該語句涉及的表在這段時間內沒有發生變更。那如果該表在發生變更時，Query_cache裡的資料又怎麼處理呢？首先要把Query_cache和該表相關的語句全部置為失效，然後在寫入更新。那麼如果Query_cache非常大，該表的查詢結構又比較多，查詢語句失效也慢，一個更新或是Insert就會很慢，這樣看到的就是Update或是Insert怎麼這麼慢了。所以在資料庫寫入量或是更新量也比較大的系統，該參數不適合分配過大。而且在高並發，寫入量大的系統，建系把該功能禁掉。
    QC 的使用需要多個參數配合，其中最為關鍵的是 query_cache_size和 query_cache_type，前者設定緩衝記錄集的記憶體大小，後者設定在何情境下使用QC 。在以往的經驗來看，中等規模的網站，query_cache_size 設定 256MB 足夠了。當然，還可以通過計算QC的命中率來進行調整。

    Qcache_hits/ (Qcache_hits + Qcache_inserts)  * 100%

　　query_cache_type有三種選擇：0（OFF，不使用QC），1（ON，預設使用QC ），2（DEMAND，預設不使用QC）。為什麼加上“預設”？MySQL還支援動態使用緩衝的SQL文法，如下：

　　# 強制使用緩衝    SELECT SQL_CACHE  id  FROM table

　　# 強制不使用緩衝  SELECT SQL_NO_CACHE  id  FROM table

 

在有些處理任務中，我們實際上是可以阻止查詢快取工作的。

 // query cache does NOT work
        $r = mysql_query("SELECT usernameFROM user WHERE signup_date >= CURDATE()");
     // query cache works!
        $today =date("Y-m-d");
       $r = mysql_query("SELECTusername FROM user WHERE signup_date >= ‘$today‘");

 

3、索引緩衝

相關參數：key_buffer_size

　　這個是對MyISAM表效能影響最大的一個參數，用來設定用於緩衝 MyISAM儲存引擎中索引檔案的記憶體地區大小。如果有足夠的記憶體，這個快取區域大小可以設為所有的 MyISAM表的索引大小的總和，即 data 目錄下所有*.MYI檔案大小的總和。

　　注意，由於 MyISAM 引擎只會緩衝索引塊到記憶體中，而不會緩衝表資料庫塊。所以，查詢SQL語句一定要儘可能讓過濾條件都在索引中，以便使用到索引緩衝來提高查詢效率。

　　計算索引緩衝未命中的機率Key_reads / Key_read_requests * 100%

　　如果Key_reads太大,則應該把key_buffer_size變大。增加它可得到更好處理的索引(對所有讀和多重寫)，如果對錶的順序掃描請求非常頻繁，並且你認為頻繁掃描進行得太慢，可以通過增加該變數值以及記憶體緩衝區大小提高其效能。對於記憶體在4GB左右的伺服器該參數可設定為384M或512M。通過檢查狀態值Key_read_requests和 Key_reads,可以知道key_buffer_size設定是否合理。比例key_reads /key_read_requests應該儘可能的低，至少是1:100，1:1000更好(上述狀態值可以使用SHOW STATUS LIKE ‘key_read%‘獲得)。注意：該參數值設定的過大反而導致是伺服器整體效率降低。

 

4、插入緩衝

相關參數：bulk_insert_buffer_size

　　用於使用 MyISAM引擎，用來緩衝批量插入資料的時候臨時緩衝寫入資料。當我們使用如下幾種資料寫入語句的時候，會使用這個記憶體地區來緩衝批量結構的資料以協助批量寫入資料檔案，預設8M，建議不要超過32M

insert … select …
insert … values (…),(…),(…),…
load data infile… into… /* 非空表 */

 

 5、日誌緩衝

相關參數：binlog_cache_size

　　用於在開啟了二進位日誌（binlog）記錄功能的環境中，是 MySQL 用來提高 binlog 的記錄效率而設計的一個在短時間內緩衝binlog 資料的記憶體緩衝。

　　如果資料庫中沒有大事務，寫入不是特別頻繁，2MB～4MB是一個合適的選擇。但是如果資料庫大事務較多，寫入比較頻繁，可適當加大。使用的時候，還可以通過　　binlog_cache_use 以及binlog_cache_disk_use來分析設定的binlog_cache_size是否足夠，是否有大量的binlog_cache由於記憶體大小不夠而使用臨時檔案來緩衝了。

 

6、其他緩衝參數設定

　　1）、key_buffer  改變索引緩衝長度

一般，該變數控制緩衝區的長度在處理索引表（讀/寫操作）時使用。MySQL使用手冊指出該變數可以不斷增加以確保索引表的最佳效能，並推薦使用與系統記憶體25％的大小作為該變數的值。這是MySQL十分重要的組態變數之一，如果你對最佳化和提高系統效能有興趣，可以從改變 key_buffer_size變數的值開始。 

　　2）、long_query_time 對緩長查詢設定一個時間限制

MySQL帶有“慢查詢日誌”，它會自動地記錄所有的在一個特定的時間範圍內尚未結束的查詢。這個日誌對於跟蹤那些低效率或者行為不端的查詢以及尋找最佳化對象都非常有用。long_query_time變數控制這一最大時間限定，以秒為單位。 

3）、sort_buffer_size   屬重點最佳化參數

sort_buffer_size = 4M 查詢排序時所能使用的緩衝區大小。注意：該參數對應的分配記憶體是每串連獨佔，如果有100個串連，那麼實際分配的總共排序緩衝區大小為100 × 4 = 400MB。所以，對於記憶體在4GB左右的伺服器推薦設定為4-8M。 

4）、read_buffer_size 讀查詢操作所能使用的緩衝區大小

read_buffer_size= 4M。和sort_buffer_size一樣，該參數對應的分配記憶體也是每串連獨享。MySql讀入緩衝區大小。對錶進行順序掃描的請求將分配一個讀入緩衝區，MySql會為它分配一段記憶體緩衝區。read_buffer_size變數控制這一緩衝區的大小。如果對錶的順序掃描請求非常頻繁，並且你認為頻繁掃描進行得太慢，可以通過增加該變數值以及記憶體緩衝區大小提高其效能。 

5）、join_buffer_size 聯集查詢操作所能使用的緩衝區大小

　　join_buffer_size= 8M，和sort_buffer_size一樣，該參數對應的分配記憶體也是每串連獨享。 

6）、thread_cache_size

thread_cache_size= 64伺服器線程緩衝這個值表示可以重新利用儲存在緩衝中線程的數量，如果Threads_created太大,就要增加thread_cache_size的值。當中斷連線時如果緩衝中還有空間,那麼用戶端的線程將被放到緩衝中,如果線程重新被請求，那麼請求將從緩衝中讀取,如果緩衝中是空的或者是新的請求，那麼這個線程將被重新建立,如果有很多新的線程，增加這個值可以改善系統效能.通過比較 Connections 和 Threads_created 狀態的變數，可以看到這個變數的作用 

7）、tmp_table_size

如果Created_tmp_disk_tables太大, 就要增加tmp_table_size的值,用基於記憶體的暫存資料表代替基於磁碟的。tmp_table_size 的預設大小是 32M。如果一張暫存資料表超出該大小，MySQL產生一個 The tabletbl_name is full 形式的錯誤，如果你做很多進階 GROUP BY 查詢，可以增加 tmp_table_size 值。 

8）、read_rnd_buffer_size

MySql的隨機讀（查詢操作）緩衝區大小。當按任意順序讀取行時(例如，按照排序次序)，將分配一個隨機讀緩衝區。進行排序查詢時，MySql會首先掃描一遍該緩衝，以避免磁碟搜尋，提高查詢速度，如果需要排序大量資料，可適當調高該值。但MySql會為每個客戶串連發放該緩衝空間，所以應盡量適當設定該值，以避免記憶體開銷過大。

 

MySql資料庫3【最佳化3】緩衝設定的最佳化