mysql的內存分配，是調優的重中之重，所以必須搞清楚內存是怎麼分配的
mysql> show global variables like '%buffer%'; +-------------------------+------------+ | Variable_name | Value | +-------------------------+------------+ | bulk_insert_buffer_size | 4194304 | | innodb_buffer_pool_size | 2013265920 | | innodb_change_buffering | inserts | | innodb_log_buffer_size | 8388608 | | join_buffer_size | 1048576 | | key_buffer_size | 16777216 | | myisam_sort_buffer_size | 262144 | | net_buffer_length | 16384 | | preload_buffer_size | 32768 | | read_buffer_size | 1048576 | | read_rnd_buffer_size | 1048576 | | sort_buffer_size | 1048576 | | sql_buffer_result | OFF | +-------------------------+------------+ 13 rows in set (0.01 sec) mysql> show global variables like '%cache%'; +------------------------------+----------------------+ | Variable_name | Value | +------------------------------+----------------------+ | binlog_cache_size | 1048576 | | have_query_cache | YES | | key_cache_age_threshold | 300 | | key_cache_block_size | 1024 | | key_cache_division_limit | 100 | | max_binlog_cache_size | 18446744073709547520 | | query_cache_limit | 1048576 | | query_cache_min_res_unit | 4096 | | query_cache_size | 0 | | query_cache_type | ON | | query_cache_wlock_invalidate | OFF | | table_definition_cache | 256 | | table_open_cache | 100 | | thread_cache_size | 100 | +------------------------------+----------------------+ 14 rows in set (0.00 sec)
可以看到部分配置信息
內存的組成
1、線程共享內存 2、線程獨享內存
used_Mem =
+ key_buffer_size
+ query_cache_size
+ innodb_buffer_pool_size
+ innodb_additional_mem_pool_size
+ innodb_log_buffer_size
+ max_connections *(
+ read_buffer_size
+ read_rnd_buffer_size
+ sort_buffer_size
+ join_buffer_size
+ binlog_cache_size
+ thread_stack
+ tmp_table_size
+ bulk_insert_buffer_size
)
線程獨享內存
1、read_buffer_size： 順序讀取數據緩沖區使用內存
這部分內存主要用於當需要順序讀取數據的時候，如無發使用索引的情況下的全表掃描，全索引掃描等。在這種時候，MySQL 按照數據的存儲順序依次讀取數據塊，每次讀取的數據快首先會暫存在read_buffer_size中，當 buffer 空間被寫滿或者全部數據讀取結束後，再將buffer中的數據返回給上層調用者，以提高效率。
2、read_rnd_buffer_size：隨機讀取數據緩沖區使用內存
和順序讀取相對應，當 MySQL 進行非順序讀取（隨機讀取）數據塊的時候，會利用這個緩沖區暫存讀取的數據。如根據索引信息讀取表數據，根據排序後的結果集與表進行Join等等。總的來說，就是當數據塊的讀取需要滿足一定的順序的情況下，MySQL 就需要產生隨機讀取，進而使用到 read_rnd_buffer_size 參數所設置的內存緩沖區。
3、sort_buffer_size：排序使用內存
MySQL 用此內存區域進行排序操作（filesort），完成客戶端的排序請求。當我們設置的排序區緩存大小無法滿足排序實際所需內存的時候，MySQL 會將數據寫入磁盤文件來完成排序。由於磁盤和內存的讀寫性能完全不在一個數量級，所以sort_buffer_size參數對排序操作的性能影響絕對不可 小視
4、join_buffer_size：連接使用內存
應用程序經常會出現一些兩表（或多表）Join的操作需求，MySQL在完成某些 Join 需求的時候（all/index join），為了減少參與Join的“被驅動表”的讀取次數以提高性能，需要使用到 Join Buffer 來協助完成 Join操作。 當 Join Buffer 太小，MySQL 不會將該 Buffer 存入磁盤文件，而是先將Join Buffer中的結果集與需要 Join 的表進行 Join 操作，然後清空 Join Buffer 中的數據，繼續將剩余的結果集寫入此 Buffer 中，如此往復。這勢必會造成被驅動表需要被多次讀取，成倍增加 IO 訪問，降低效率。
5、thread_stack：線程棧信息使用內存
主要用來存放每一個線程自身的標識信息，如線程id，線程運行時基本信息等等，我們可以通過 thread_stack 參數來設置為每一個線程棧分配多大的內存
6、tmp_table_size：臨時表使用內存
當我們進行一些特殊操作如需要使用臨時表才能完成的 Order By，Group By 等等，MySQL 可能需要使用到臨時表。當我們的臨時表較小（小於 tmp_table_size 參數所設置的大小）的時候，MySQL 會將臨時表創建成內存臨時表，只有當 tmp_table_size 所設置的大小無法裝下整個臨時表的時候，MySQL 才會將該表創建成 MyISAM 存儲引擎的表存放在磁盤上。不過，當另一個系統參數 max_heap_table_size 的大小還小於 tmp_table_size 的時候，MySQL 將使用 max_heap_table_size 參數所設置大小作為最大的內存臨時表大小，而忽略 tmp_table_size 所設置的值。而且 tmp_table_size 參數從 MySQL 5.1.2 才開始有，之前一直使用 max_heap_table_size。
7、bulk_insert_buffer_size：批量插入暫存使用內存
當我們使用如 insert … values(…),(…),(…)… 的方式進行批量插入的時候，MySQL 會先將提交的數據放如一個緩存空間中，當該緩存空間被寫滿或者提交完所有數據之後，MySQL 才會一次性將該緩存空間中的數據寫入數據庫並清空緩存。此外，當我們進行 LOAD DATA INFILE 操作來將文本文件中的數據 Load 進數據庫的時候，同樣會使用到此緩沖區
8、binlog_cache_size：二進制日志緩沖使用內存
我們知道InnoDB存儲引擎是支持事務的，實現事務需要依賴於日志技術，為了性能，日志編碼采用二進制格式。那麼，我們如何記日志呢？有日志的時候，就 直接寫磁盤？可是磁盤的效率是很低的，如果你用過Nginx，一般Nginx輸出access log都是要緩沖輸出的。因此，記錄二進制日志的時候，我們是否也需要考慮Cache呢？答案是肯定的，但是Cache不是直接持久化，於是面臨安全性的 問題——因為系統宕機時，Cache中可能有殘余的數據沒來得及寫入磁盤。因此，Cache要權衡，要恰到好處：既減少磁盤I/O，滿足性能要求；又保證 Cache無殘留，及時持久化，滿足安全要求
設置太大的話，會比較消耗內存資源；設置太小的話，如果用戶提交一個“長事務（long_transaction）”，比如：批量導入數據。那麼該事務必然會產生很多binlog，這樣 cache可能不夠用（默認binlog_cache_size是32K），不夠用的時候mysql會把uncommitted的部分寫入臨時文件（臨時 文件cache的效率必然沒有內存cache高），等到committed的時候才會寫入正式的持久化日志文件。
線程共享內存
1、query_cache_size：查詢緩存
查詢緩存是 MySQL 比較獨特的一個緩存區域，用來緩存特定 Query 的結果集（Result Set）信息，共享給所有客戶端。
通過對 Query 語句進行特定的 Hash 計算之後與結果集對應存放在 Query Cache 中，以提高完全相同的 Query 語句的相應速度。
當我們打開 MySQL 的 Query Cache 之後，MySQL 接收到每一個 SELECT 類型的 Query 之後都會首先通過固定的 Hash 算法得到該 Query 的 Hash 值，然後到 Query Cache 中查找是否有對應的 Query Cache。如果有，則直接將 Cache 的結果集返回給客戶端。如果沒有，再進行後續操作，得到對應的結果集之後將該結果集緩存到 Query Cache 中，再返回給客戶端。
當任何一個表的數據發生任何變化之後，與該表相關的所有 Query Cache 全部會失效，所以 Query Cache 對變更比較頻繁的表並不是非常適用，但對那些變更較少的表是非常合適的，可以極大程度的提高查詢效率，如那些靜態資源表，配置表等等。為了盡可能高效的利 用 Query Cache，MySQL 針對 Query Cache 設計了多個 query_cache_type 值和兩個 Query Hint：SQL_CACHE 和 SQL_NO_CACHE。
a、當query_cache_type 設置為0（或者 OFF）的時候 不使用 Query Cache
b、當query_cache_type設置為1（或者 ON）的時候，當且僅當 Query 中使用了 SQL_NO_CACHE 的時候 MySQL 會忽略 Query Cache
c、query_cache_type 設置為2（或者DEMAND）的時候，當且僅當Query 中使用了 SQL_CACHE 提示之後，MySQL 才會針對該 Query 使用 Query Cache。
可以通過 query_cache_size 來設置可以使用的最大內存空間
2、binlog_cache_size：二進制日志緩沖區
二進制日志緩沖區主要用來緩存由於各種數據變更操做所產生的 Binary Log 信息。
為了提高系統的性能，MySQL 並不是每次都是將二進制日志直接寫入 Log File，而是先將信息寫入 Binlog Buffer 中，當滿足某些特定的條件（如 sync_binlog參數設置）之後再一次寫入 Log File 中。我們可以通過 binlog_cache_size 來設置其可以使用的內存大小，同時通過 max_binlog_cache_size 限制其最大大小（當單個事務過大的時候 MySQL 會申請更多的內存）。當所需內存大於 max_binlog_cache_size 參數設置的時候，MySQL 會報錯：“Multi-statement transaction required more than ‘max_binlog_cache_size’ bytes of storage”。
3、key_buffer_size：MyISAM索引緩存
The key_buffer_size indicates the size of the key cache that MySQL uses to store indexes in memory. The cache stores index blocks in memory to avoid reading the disk repeatedly. The key_buffer_size is one of the most important variables to tune to improve MySQL database performance. The index blocks of MyISAM tables are stored in the key cache and are accessible to all processes which use MySQL globally.
The maximum size of the key_buffer_size variable is 4 GB on 32 bit machines, and larger for 64 bit machines. MySQL recommends that you keep the key_buffer_size less than or equal to 25% of the RAM on your machine. This also depends on the other processes that use memory on the machine and it is wise to check if you consistently have 25% of free memory using the Linux command free. More on this later.
4、innodb_log_buffer_size：InnoDB 日志緩沖區
這是 InnoDB 存儲引擎的事務日志所使用的緩沖區。類似於 Binlog Buffer，InnoDB 在寫事務日志的時候，為了提高性能，也是先將信息寫入 Innofb Log Buffer 中，當滿足 innodb_flush_log_trx_commit 參數所設置的相應條件（或者日志緩沖區寫滿）之後，才會將日志寫到文件（或者同步到磁盤）中。可以通過 innodb_log_buffer_size 參數設置其可以使用的最大內存空間。
注：innodb_flush_log_trx_commit 參數對 InnoDB Log 的寫入性能有非常關鍵的影響。該參數可以設置為0，1，2，解釋如下：
mysql日志操作步驟 ：log_buffer ---mysql寫 (write)---> log_file ---OS刷新 (flush)---> disk
具體的展示
0（延遲寫）： log_buffer --每隔1秒--> log_file —實時—> disk
1（實時寫，實時刷）： log_buffer —實時—> log_file —實時—> disk
2（實時寫，延遲刷）： log_buffer —實時—> log_file --每隔1秒--> disk
推薦的做法是 innodb_flush_log_at_trx_commit=2 ，sync_binlog=N (N為500 或1000) 且使用帶蓄電池後備電源的緩存cache，防止系統斷電異常
注：sync_binlog =N (N>0) ，MySQL 在每寫 N次 二進制日志binary log時，會使用fdatasync()函數將它的寫二進制日志binary log同步到磁盤中去
此外，MySQL文檔中還提到，這幾種設置中的每秒同步一次的機制，可能並不會完全確保非常准確的每秒就一定會發生同步，還取決於進程調度 的問題。實際上，InnoDB 能否真正滿足此參數所設置值代表的意義正常 Recovery 還是受到了不同 OS 下文件系統以及磁盤本身的限制，可能有些時候在並沒有真正完成磁盤同步的情況下也會告訴 mysqld 已經完成了磁盤同步。
5、innodb_buffer_pool_size：InnoDB 數據和索引緩存
InnoDB Buffer Pool 對 InnoDB 存儲引擎的作用類似於 Key Buffer Cache 對 MyISAM 存儲引擎的影響，主要的不同在於 InnoDB Buffer Pool 不僅僅緩存索引數據，還會緩存表的數據，而且完全按照數據文件中的數據快結構信息來緩存，這一點和 Oracle SGA 中的 database buffer cache 非常類似。所以，InnoDB Buffer Pool 對 InnoDB 存儲引擎的性能影響之大就可想而知了。可以通過 (Innodb_buffer_pool_read_requests – Innodb_buffer_pool_reads) / Innodb_buffer_pool_read_requests * 100% 計算得到 InnoDB Buffer Pool 的命中率。
6、innodb_additional_mem_pool_size：InnoDB 字典信息緩存
InnoDB 字典信息緩存主要用來存放 InnoDB 存儲引擎的字典信息以及一些 internal 的共享數據結構信息。所以其大小也與系統中所使用的 InnoDB 存儲引擎表的數量有較大關系。不過，如果我們通過 innodb_additional_mem_pool_size 參數所設置的內存大小不夠，InnoDB 會自動申請更多的內存，並在 MySQL 的 Error Log 中記錄警告信息。
這裡所列舉的各種共享內存，是我個人認為對 MySQL 性能有較大影響的集中主要的共享內存。實際上，除了這些共享內存之外，MySQL 還存在很多其他的共享內存信息，如當同時請求連接過多的時候用來存放連接請求信息的back_log隊列等。
