淺談MySQL Buffer學習及思考

    本博文旨在結合自己看書理解，並藉此圖進行說明，如有謬誤，望大家指正，以共同探討為目的，交流學習。首先介紹一下此圖的由來:最近看關於mysql方面書籍的一點心得，把文字轉化成圖片而得，方便理解。 

650) this.width=650;" src="http://www.bkjia.com/uploads/allimg/131229/1930004c8-0.png" border="0" alt="" />

然後對以上參數進行一下簡單的介紹： 

 

  
1、max_connections這個參數，這個參數指MySql的最大串連數，如果伺服器的並發串連請求量比較大，建議調高此值，以增加並行串連數量，伺服器根據自己的實際情況進行增加，如果串連數越多，因MySql會為每個串連提供串連緩衝區，就會開銷越多的記憶體，所以要適當調整該值，不能盲目提高設值。可以過mysql -e "SHOW VARIABLES LIKE 'max_connections';"查看目前狀態的串連數量來設定該值大小。當你常看到Too many connections 錯誤，可以增加此值了，預設是100。 
  
 
  
2、back_log這個參數主要是基於max_connections進行的一個額外串連，也就是說當mysql串連大於max_connections設定的值的話，而又在max_connections+back_log）之間，則mysql會把新串連放到堆棧中，等待之前串連的process釋放，如果當前最大請求超過了max_connections+back_log），就不會授權串連，當然該值也受約於系統的TCP/IP串連的偵聽隊列系統的tcp_max_syn_backlog值的大小），可以通過cat /proc/sys/net/ipv4/tcp_max_syn_backlog查看，當然可以修改該值 
  
sysctl -w net.ipv4.tcp_max_syn_backlog=N或在/etc/sysctl.conf中添加tcp_max_syn_backlog=N即可。 
  
 
  


 

     由於mysql被稱為記憶體式資料庫，當然很記憶體是密不可分了，而他和記憶體的關係主要是通過緩衝區大小的幾個參數吧。  緩衝包括：全域緩衝和局部緩衝。  全域緩衝參數大致有如下：key_buffer_size, innodb_buffer_pool_size, innodb_additional_mem_pool_size，innodb_log_buffer_size, query_cache_size 而局部緩衝我自己習慣這麼叫，雖然不專業，呵呵）一般mysql還會為每個串連分配串連緩衝。全域緩衝在上篇部落格中有介紹，這地方就省略了。  局部緩衝：    每個串連到MySQL伺服器的線程都需要有自己的緩衝。大概需要立刻分配256K，甚至線上程空閑時，它們使用預設的線程堆棧，網路緩衝等。事務開始之後，則需要增加更多的空間。運行較小的查詢可能僅給指定的線程增加少量的記憶體消耗，然而如果對資料表做複雜的操作例如掃描、排序或者需要暫存資料表，則需分配大約read_buffer_size，sort_buffer_size，read_rnd_buffer_size，tmp_table_size大小的記憶體空間。不過它們只是在需要的時候才分配，並且在那些操作做完之後就釋放了。有的是立刻分配成單獨的組塊。tmp_table_size可能高達MySQL所能分配給這個操作的最大記憶體空間了。注意，這裡需要考慮的不只有一點——可能會分配多個同一種類型的緩衝，例如用來處理子查詢。一些特殊的查詢的記憶體使用量量可能更大——如果在MyISAM表上做成批的插入時需要分配bulk_insert_buffer_size 大小的記憶體；執行 ALTER TABLE， OPTIMIZE TABLE， REPAIR TABLE命令時需要分配myisam_sort_buffer_size大小的記憶體。   

 

  
read_buffer_size是MySql讀入緩衝區大小。對錶進行順序掃描的請求將分配一個讀入緩衝區，MySql會為它分配一段記憶體緩衝區。read_buffer_size變數控制這一緩衝區的大小。如果對錶的順序掃描請求非常頻繁，並且你認為頻繁掃描進行得太慢，可以通過增加該變數值以及記憶體緩衝區大小提高其效能。  
  
 
  
sort_buffer_size是MySql執行排序使用的緩衝大小。如果想要增加ORDER BY的速度，首先看是否可以讓MySQL使用索引而不是額外的排序階段。如果不能，可以嘗試增加sort_buffer_size變數的大小。  
  
 
  
read_rnd_buffer_size是MySql的隨機讀緩衝區大小。當按任意順序讀取行時(例如，按照排序次序)，將分配一個隨機讀緩衝區。進行排序查詢時，MySql會首先掃描一遍該緩衝，以避免磁碟搜尋，提高查詢速度，如果需要排序大量資料，可適當調高該值。但MySql會為每個客戶串連發放該緩衝空間，所以應盡量適當設定該值，以避免記憶體開銷過大。  
  
 
  
tmp_table_size是MySql的heap堆積）表緩衝大小。所有聯合在一個DML指令內完成，並且大多數聯合甚至可以不用暫存資料表即可以完成。大多數暫存資料表是基於記憶體的(HEAP)表。具有大的記錄長度的暫存資料表(所有列的長度的和)或包含BLOB列的表格儲存體在硬碟上。如果某個內部heap堆積）表大小超過tmp_table_size，MySQL可以根據需要自動將記憶體中的heap表改為基於硬碟的MyISAM表。還可以通過設定tmp_table_size選項來增加暫存資料表的大小。也就是說，如果調高該值，MySql同時將增加heap表的大小，可達到提高聯結查詢速度的效果。 
 

   以上是結合自己常用的參數並查詢mysql手冊總結的。方便更直觀的學習和理解。均個人見解，如有疑問，可共同交流學習！ 

 

本文出自 “Ro の部落格” 部落格，請務必保留此出處http://luoweiro.blog.51cto.com/2186161/985887