首先我們來看一個(gè)公式，MySQL中內(nèi)存分為全局內(nèi)存和線程內(nèi)存兩大部分（其實(shí)并不全部，只是影響比較大的 部分）：

全局緩存：
key_buffer_size：決定索引處理的速度，尤其是索引讀的速度。默認(rèn)值是16M，通過檢查狀態(tài)值Key_read_requests和Key_reads，可以知道key_buffer_size設(shè)置是否合理。比例key_reads / key_read_requests應(yīng)該盡可能的低，至少是1:100，1:1000更好（上述狀態(tài)值可以使用'key_read%'獲得用來顯示狀態(tài)數(shù)據(jù)）。key_buffer_size只對MyISAM表起作用。即使你不使用MyISAM表，但是內(nèi)部的臨時(shí)磁盤表是MyISAM表，也要使用該值?？梢允褂脵z查狀態(tài)值'created_tmp_disk_tables'得知詳情。

innodb_buffer_pool_size：InnoDB使用該參數(shù)指定大小的內(nèi)存來緩沖數(shù)據(jù)和索引，這個(gè)是Innodb引擎中影響性能最大的參數(shù)。

innodb_additional_mem_pool_size：指定InnoDB用來存儲(chǔ)數(shù)據(jù)字典和其他內(nèi)部數(shù)據(jù)結(jié)構(gòu)的內(nèi)存池大小。缺省值是8M。通常不用太大，只要夠用就行，應(yīng)該與表結(jié)構(gòu)的復(fù)雜度有關(guān)系。如果不夠用，MySQL會(huì)在錯(cuò)誤日志中寫入一條警告信息。

innodb_log_buffer_size：指定InnoDB用來存儲(chǔ)日志數(shù)據(jù)的緩存大小，如果您的表操作中包含大量并發(fā)事務(wù)（或大規(guī)模事務(wù)），并且在事務(wù)提交前要求記錄日志文件，請盡量調(diào)高此項(xiàng)值，以提高日志效率。

query_cache_size：是MySQL的查詢緩沖大小。（從4.0.1開始，MySQL提供了查詢緩沖機(jī)制）使用查詢緩沖，MySQL將SELECT語句和查詢結(jié)果存放在緩沖區(qū)中，今后對于同樣的SELECT語句（區(qū)分大小寫），將直接從緩沖區(qū)中讀取結(jié)果。根據(jù)MySQL用戶手冊，使用查詢緩沖最多可以達(dá)到238%的效率。通過檢查狀態(tài)值'Qcache_%'，可以知道query_cache_size設(shè)置是否合理：如果Qcache_lowmem_prunes的值非常大，則表明經(jīng)常出現(xiàn)緩沖不夠的情況，如果Qcache_hits的值也非常大，則表明查詢緩沖使用非常頻繁，此時(shí)需要增加緩沖大小；如果Qcache_hits的值不大，則表明你的查詢重復(fù)率很低，這種情況下使用查詢緩沖反而會(huì)影響效率，那么可以考慮不用查詢緩沖。此外，在SELECT語句中加入SQL_NO_CACHE可以明確表示不使用查詢緩沖。

線程緩存
每個(gè)連接到MySQL服務(wù)器的線程都需要有自己的緩沖。大概需要立刻分配256K，甚至在線程空閑時(shí)，它們使用默認(rèn)的線程堆棧，網(wǎng)絡(luò)緩存等。事務(wù)開始之后，則需要增加更多的空間。運(yùn)行較小的查詢可能僅給指定的線程增加少量的內(nèi)存消耗，然而如果對數(shù)據(jù)表做復(fù)雜的操作例如掃描、排序或者需要臨時(shí)表，則需分配大約read_buffer_size，sort_buffer_size，read_rnd_buffer_size，tmp_table_size大小的內(nèi)存空間。不過它們只是在需要的時(shí)候才分配，并且在那些操作做完之后就釋放了。有的是立刻分配成單獨(dú)的組塊。tmp_table_size 可能高達(dá)MySQL所能分配給這個(gè)操作的最大內(nèi)存空間了。

read_buffer_size：是MySQL讀入緩沖區(qū)大小。對表進(jìn)行順序掃描的請求將分配一個(gè)讀入緩沖區(qū)，MySQL會(huì)為它分配一段內(nèi)存緩沖區(qū)。read_buffer_size變量控制這一緩沖區(qū)的大小。如果對表的順序掃描請求非常頻繁，并且你認(rèn)為頻繁掃描進(jìn)行得太慢，可以通過增加該變量值以及內(nèi)存緩沖區(qū)大小提高其性能。

sort_buffer_size：是MySQL執(zhí)行排序使用的緩沖大小。如果想要增加ORDER BY的速度，首先看是否可以讓MySQL使用索引而不是額外的排序階段。如果不能，可以嘗試增加sort_buffer_size變量的大小。

read_rnd_buffer_size：是MySQL的隨機(jī)讀緩沖區(qū)大小。當(dāng)按任意順序讀取行時(shí)(例如，按照排序順序)，將分配一個(gè)隨機(jī)讀緩存區(qū)。進(jìn)行排序查詢時(shí)，MySQL會(huì)首先掃描一遍該緩沖，以避免磁盤搜索，提高查詢速度，如果需要排序大量數(shù)據(jù)，可適當(dāng)調(diào)高該值。但MySQL會(huì)為每個(gè)客戶連接發(fā)放該緩沖空間，所以應(yīng)盡量適當(dāng)設(shè)置該值，以避免內(nèi)存開銷過大。

tmp_table_size：是MySQL的臨時(shí)表緩沖大小。所有聯(lián)合在一個(gè)DML指令內(nèi)完成，并且大多數(shù)聯(lián)合甚至可以不用臨時(shí)表即可以完成。大多數(shù)臨時(shí)表是基于內(nèi)存的(HEAP)表。具有大的記錄長度的臨時(shí)表 (所有列的長度的和)或包含BLOB列的表存儲(chǔ)在硬盤上。如果某個(gè)內(nèi)部heap（堆積）表大小超過tmp_table_size，MySQL可以根據(jù)需要自動(dòng)將內(nèi)存中的heap表改為基于硬盤的MyISAM表。還可以通過設(shè)置tmp_table_size選項(xiàng)來增加臨時(shí)表的大小。也就是說，如果調(diào)高該值，MySQL同時(shí)將增加heap表的大小，可達(dá)到提高聯(lián)接查詢速度的效果。

thread_stack ：主要用來存放每一個(gè)線程自身的標(biāo)識(shí)信息，如線程id，線程運(yùn)行時(shí)基本信息等等，我們可以通過 thread_stack 參數(shù)來設(shè)置為每一個(gè)線程棧分配多大的內(nèi)存。

join_buffer_size：應(yīng)用程序經(jīng)常會(huì)出現(xiàn)一些兩表（或多表）Join的操作需求，MySQL在完成某些 Join 需求的時(shí)候（all/index join），為了減少參與Join的“被驅(qū)動(dòng)表”的讀取次數(shù)以提高性能，需要使用到 Join Buffer 來協(xié)助完成 Join操作。當(dāng) Join Buffer 太小，MySQL 不會(huì)將該 Buffer 存入磁盤文件，而是先將Join Buffer中的結(jié)果集與需要 Join 的表進(jìn)行 Join 操作，然后清空 Join Buffer 中的數(shù)據(jù)，繼續(xù)將剩余的結(jié)果集寫入此 Buffer 中，如此往復(fù)。這勢必會(huì)造成被驅(qū)動(dòng)表需要被多次讀取，成倍增加 IO 訪問，降低效率。

binlog_cache_size：在事務(wù)過程中容納二進(jìn)制日志SQL 語句的緩存大小。二進(jìn)制日志緩存是服務(wù)器支持事務(wù)存儲(chǔ)引擎并且服務(wù)器啟用了二進(jìn)制日志(—log-bin 選項(xiàng))的前提下為每個(gè)客戶端分配的內(nèi)存，注意，是每個(gè)Client 都可以分配設(shè)置大小的binlog cache 空間。如果系統(tǒng)中經(jīng)常會(huì)出現(xiàn)多語句事務(wù)的話，可以嘗試增加該值的大小，以獲得更好的性能。當(dāng)然，我們可以通過MySQL 的以下兩個(gè)狀態(tài)變量來判斷當(dāng)前的binlog_cache_size 的狀況：Binlog_cache_use 和Binlog_cache_disk_use?！癿ax_binlog_cache_size”：和"binlog_cache_size"相對應(yīng)，但是所代表的是binlog 能夠使用的最大cache 內(nèi)存大小。當(dāng)我們執(zhí)行多語句事務(wù)的時(shí)候，max_binlog_cache_size 如果不夠大的話，系統(tǒng)可能會(huì)報(bào)出“ Multi-statement transaction required more than 'max_binlog_cache_size' bytes ofstorage”的錯(cuò)誤。
其中需要注意的是：table_cache表示的是所有線程打開的表的數(shù)目，和內(nèi)存無關(guān)。

最新評(píng)論