淺談MySQL學習及思考

 本博文旨在結合自己看書理解，並藉此圖進行說明，如有謬誤，望大家指正，以共同探討為目的，交流學習。

首先介紹一下架構圖的由來:最近看關於mysql方面書籍的一點心得，把文字轉化成圖片而得，方便理解。 

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

我主要從讀、寫、底層磁碟三方面進行闡述：

1、讀操作：

  我們知道資料在讀取的時候，需要從磁碟讀到記憶體中，然後再做相應的操作，而在最佳化讀操作的時候，主要想buffer，cache這些進行最佳化：

 

  
key_buffer_size  
  
這個對MyIsam表來說是一個比較重要的參數，一般可以把他設定成記憶體的30%-40%，當然這還要根據具體情況，MyISAM表會使用作業系統的緩衝來快取資料，因此需要留出部分記憶體給它們，很多情況下資料比索引大多了。 
  
 
  
innodb_buffer_pool_size  
  
這個對InnoDB來說是一個比較重要的參數，而InnoDB對緩衝更為敏感，MyISAM可以在預設的 key_buffer_size 設定下啟動並執行可以，然而Innodb在預設的 innodb_buffer_pool_size 設定下卻跟蝸牛似的。由於Innodb把資料和索引都緩衝起來，無需留給作業系統太多的記憶體，因此如果只需要用Innodb的話則可以設定它高達 70-80% 的可用記憶體。一些應用於 key_buffer 的規則有 — 如果你的資料量不大，並且不會暴增，那麼無需把 innodb_buffer_pool_size 設定的太大了。 
  
 
  
table_cache  
  
表的緩衝，這個佔用系統的資源和記憶體，因為每一個現成都需要開啟一個暫存資料表，所以當串連數大的時候可以加大此值。 
  
 
  
thread-cache  
  
線程的緩衝，線程的建立和銷毀開銷可能會很大，所以每個線程的串連和斷開需要，如果程式中活躍的並發串連數和Thread-Created的值比較大，可以稍微設定大一點此值。 
  
 
  
query-cache  
  
如果應用程式中有大量的讀，可以設定大一點此值，但是也不要太大，因為維護它也需要不少的開銷。一般設定32M-512M即可。 
  
 
  
sort_buffer_size  
  
這個是connection級的參數，在每個connection第一次需要使用這個buffer的時候，一次性分配設定的記憶體，此值不是越大越好，如果設定過大，碰上高並發的情況下就會使效能降低，sort_buffer_size 超過2KB的時候，就會使用mmap()而不是 malloc() 來進行記憶體配置，導致效率降低。  
  
 
  
mysql暫存資料表 當工作在非常大的表上時，你可能偶爾需要運行很多查詢獲得一個大量資料的小的子集，不是對整個表運行這些查詢，而是讓MySQL每次找出所需的少數記錄，將記錄選擇到一個暫存資料表可能更快些，然後多這些表執行查詢。 mysql伺服器會自動建立內部暫存資料表：該暫存資料表可以是只存在於記憶體的memory暫存資料表，或者是儲存於硬碟的myisam暫存資料表；而且初始建立的memory暫存資料表由於表的增大可能會轉變為myisam暫存資料表——其轉化臨界點由max_heap_table_size和tmp_table_size系統變數的較小值決定的！注意：max_heap_table_size系統變數應用於所有的memory引擎的表，不管是使用者暫存資料表、正常表、或者內部暫存資料表。當然程式也可以建立暫存資料表：create temporary table XX; 當然這是程式控制，建立使用完後再刪除，由程式控制了。 
 

  以上是讀操作的一些介紹，接下來是寫操作方面的。

2、寫操作：

  寫操作分為熱門資料和普通資料，簡而言之就是按照頻繁程度進行劃分的。然頻繁修改的資料可以和非頻繁的資料進行分開。

舉個例子:

 

  
比如我網站每天PV 1000w,而在PV統計的表中，我每次訪問就會插入一條資料，一天下來1000w，當然這還不可能是分攤在24小時，就按照10個小時的中高峰期來說，每個小時也是100w條資料，如果我的網站其他表中的跟新的資料每天2w條，相對1000w來說，就是太少了，但是這2w條資料有事比較重要的資料，如果是使用者註冊、客戶購買商品下的訂單，他可比記錄PV資訊更重要吧，這時候問題就出現了：我的熱門資料究竟是什嗎？是PV統計還是訂單、註冊使用者，不言而喻，當然其中一個還是重點資料了，所以為了不讓記錄PV的資料來影響更重要的資料的更新，我們可以把他分開，如果後面還有主從同步的話，分開後，同步的負載也會降低很多，這樣就可以只同步2w條資料，而不用考慮那1000w條資料了，進而主要資料庫的負載也會降低。 
 

  現在是把熱門資料和普通資料分開了，但是對於高並發的資料庫伺服器來說，如何抗並發，這到成了一個重要的問題，當然高配伺服器，叢集用上，包括Nosql也用上可一解決這樣的問題，如果在設計的時候能使用隊列機制，這樣不就更好了嘛！地鐵上看到一句廣告語：有序方能通暢！）

  當然你可能有更好的方法，可以共同交流。

3、底層磁碟規劃：

 

  
RAID0:
  
連續以位或位元組為單位分割資料，並行讀/寫於多個磁碟上，因此具有很高的資料轉送率，但它沒有資料冗餘，因此並不能算是真正的RAID結構。RAID0隻是單純地提高效能，並沒有為資料的可靠性提供保證，而且其中的一個磁碟失效將影響到所有資料。因此，RAID 0不能應用於資料安全性要求高的場合。 
 
 
650) this.width=650;" src="http://www.bkjia.com/uploads/allimg/131229/1942464341-1.png" border="0" alt="" />
 
 

  
RAID1：
  
它是通過磁碟資料鏡像實現資料冗餘，在成對的獨立磁碟上產生互為備份的資料。當未經處理資料繁忙時，可直接從鏡像拷貝中讀取資料，因此RAID1： 
  
可以提高讀取效能。RAID1是磁碟陣列中單位成本最高的，但提供了很高的資料安全性和可用性。當一個磁碟失效時，系統可以自動切換到鏡像磁碟上讀寫，而不需要重組失效的資料。 
 
 
 

  
RAID0+1： 
  
也被稱為RAID10標準，實際是將RAID0和RAID1標準結合的產物，在連續地以位或位元組為單位分割資料並且並行讀/寫多個磁碟的同時，為每一塊磁碟作磁碟鏡像進行冗餘。它的優點是同時擁有RAID 0的超凡速度和RAID 1的資料高可靠性，但是CPU佔用率同樣也更高，而且磁碟的利用率比較低。 

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

  
RAID5： 
  
不單獨指定的奇偶盤，而是在所有磁碟上交叉地存取資料及同位資訊。在RAID5上，讀/寫指標可同時對陣列裝置進行操作，提供了更高的資料流量。RAID5更適合於小資料區塊和隨機讀寫的資料。RAID3與RAID5相比，最主要的區別在於RAID3每進行一次資料轉送就需涉及到所有的陣列盤；而對於RAID5來說，大部分資料轉送只對一塊磁碟操作，並可進行並行操作。在RAID5中有“寫損失”，即每一次寫操作將產生四個實際的讀/寫操作，其中兩次讀舊的資料及奇偶資訊，兩次寫新的資料及奇偶資訊。 
 
 
650) this.width=650;" src="http://www.bkjia.com/uploads/allimg/131229/1942463636-3.png" border="0" alt="" />
 
 

  
RAID6： 
  
RADI6技術是在RAID5基礎上，為了進一步加強資料保護而設計的一種RAID方式，實際上是一種擴充RAID5等級。與RAID5的不同之處於除了每個硬碟上都有同級資料XOR校正區外，還有一個針對每個資料區塊的XOR校正區。當然，當前盤資料區塊的校正資料不可能存在當前盤而是交錯儲存的，具體形式見圖。這樣一來，等於每個資料區塊有了兩個校正保護屏障一個分層校正，一個是總體校正），因此RAID6的資料冗餘效能相當好。但是，由於增加了一個校正，所以寫入的效率較RAID5還差，而且控制系統的設計也更為複雜，第二塊的校正區也減少了有效儲存空間。 
 
 
650) this.width=650;" src="http://www.bkjia.com/uploads/allimg/131229/1942463439-4.png" border="0" alt="" />

  而對於熱門資料可以使用RAID10，這樣他的效能和安全性會提高很多；而普通資料可以採用RAID5，主要提供安全性，像暫存資料表這樣的使用RAID0，在效能上發揮巨大的優勢。

以上均個人見解，如有疑問，可共同交流學習！

 

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