基於mysql體繫結構的深入解析

由：串連池組件、管理服務和工具組件、sql介面組件、查詢分析器組件、最佳化器組件、       
緩衝組件、外掛程式式儲存引擎、物理檔案組成。
mysql是專屬的外掛程式式體繫結構，各個儲存引擎有自己的特點。    

   

   
mysql各個儲存引擎概述：
innodb儲存引擎：[/color][/b] 面向oltp(online transaction processing)、行鎖、支援外鍵、非鎖定讀、預設採用repeaable層級（可重複讀）通過next-keylocking策略避免幻讀、插入緩衝、二次寫、自適應雜湊索引、預讀
myisam儲存引擎：不支援事務、表鎖、全文索引、適合olap（線上分析處理），其中myd:放資料檔案，myi:放索引檔案
ndb儲存引擎：叢集儲存引擎，share nothing，可提高可用性
memory儲存引擎：資料存放在記憶體中，表鎖，並發效能差，預設使用雜湊索引
archive儲存引擎：只支援insert和select zlib演算法壓縮1：10，適合儲存歸檔資料如日誌等、行鎖
maria儲存引擎：目的取代myisam、快取資料和索引、行鎖、mvcc

    

   
innodb特性：
主體繫結構：預設7個後台線程，4個io thread(insert buffer、log、read、write),1個master thread(優先順序最高),1個鎖(lock)監控線程，1個錯誤監控線程。可以通過show engine innodb status來查看。新版本已對預設的read thread和write thread分別增大到4個，可通過show variables like 'innodb_io_thread%'查看。
儲存引擎組成：緩衝池(buffer pool)、重做日誌緩衝池(redo log buffer)以及額外的記憶體池(additional memory pool).具體配置可由show variables like 'innodb_buffer_pool_size'、show variables like
'innodb_log_buffer_size'、show variables like 'innodb_additional_mem_pool_size'來查看。
緩衝池：占最大塊記憶體，用來存放各種資料的緩衝包括有索引頁、資料頁、undo頁、插入緩衝、自適應雜湊索引、innodb儲存的鎖資訊、資料字典資訊等。工作方式總是將資料庫檔案按頁(每頁16k)讀取到緩衝池，然後按最近最少使用(lru)的演算法來保留在緩衝池中的快取資料。如果資料庫檔案需要修改，總是首先修改在緩衝池中的頁(發生修改後即為髒頁)，然後再按照一定的頻率將緩衝池的髒頁重新整理到檔案。通過命令show engine innodb status;來查看。
日誌緩衝：將重做日誌資訊先放入這個緩衝區，然後按一定頻率將其重新整理到重做記錄檔。

master thread:
loop主迴圈每秒一次的操作：
日誌緩衝重新整理到磁碟，即使這個事務還沒有提交。(總是執行，所以再大的事務commit 
的時間也是很快的)           
合并插入緩衝(innodb當前一秒發生的io次數小於5次則執行)
至多重新整理100個innodb的緩衝池中的髒頁到磁碟(超過配置的髒頁所佔緩衝池比例則執
行，在設定檔中由innodb_max_dirty_pages_pac決定，預設是90，新版本是75，
google建議是80)
如果當前沒用使用者活動，切換到backgroud loop        

loop主迴圈每10秒一次的操作：
重新整理100個髒頁到磁碟(過去10秒IO操作小於200次則執行)
合并至多5個插入緩衝(總是)
將日誌緩衝到磁碟(總是)
刪除無用的Undo頁(總是)
重新整理100個或者10個髒頁到磁碟(有超過70%的髒頁，重新整理100個髒頁;否則重新整理10個髒頁)
產生一個檢查點

backgroud loop，若當前沒有使用者活動(資料庫空閑時)或者資料庫關閉時，就會切換到這個迴圈：
刪除無用的Undo頁(總是)
合并20個插入緩衝(總是)
跳回到主迴圈(總是)
不斷重新整理100個頁，直到符合條件(可能在flush loop中完成)

如果flush loop中也沒有什麼事情可以做了，InnoDB儲存引擎會切換到suspend_loop，將master thread掛起，等待事件的發生。若啟用了InnoDB儲存引擎，卻沒有使用任何InnoDB儲存引擎的表，那麼master thread總是處於掛起狀態

插入緩衝：不是緩衝池的一部分，Insert Buffer是物理頁的一個組成部分,它帶來InnoDB效能的提高。根據B+演算法(下文會提到)的特點，插入資料的時候會主鍵索引是順序的，不會造成資料庫的隨機讀取，而對於非叢集索引(即輔助索引)，葉子節點的插入不再是順序的了，這時需要離散地訪問非叢集索引，插入效能在這裡變低了。InnoDB引入插入緩衝，判斷非叢集索引頁是否在緩衝池中，如果在則直接插入;不在，則先放在 插入緩衝區中。然後根據上述master thread中介紹的，會有一定的頻率將插入緩衝合并。此外，輔助索引不能是唯一的，因為插入到插入緩衝時，並不去尋找索引頁的情況，否則仍然會造成隨機讀，失去插入緩衝的意義了。插入緩衝可能會佔緩衝池中記憶體，預設也能會佔到1/2，所以可以將這個值調小點，到1/3。通過IBUF_POOL_SIZE_PER_MAX_SIZE來設定，2表示1/2,3表示1/3。

兩次寫：  它帶來InnoDB資料的可靠性。如果寫失效，可以通過重做日誌進行恢複，但是重做日誌中記錄的是對頁的物理操作，如果頁本身損壞，再對其進行重做是沒有意義的。所以，在應用重做日誌前，需要一個頁的副本，當寫入失效發生時，先通過頁的副本來還原該頁，再進行重做，這就是doublewire。
恢複資料=頁副本+重做日誌
             

             
自適應雜湊索引：InnoDB儲存引擎提出一種自適應雜湊索引，儲存引擎會監控對錶上索引的尋找，如果觀察到建立建立雜湊索引會帶來速度的提升，則建立雜湊索引，所以稱之為自適應的。自適應雜湊索引只能用來搜尋等值的查詢，如select * from table where index_col='***', 此外自適應雜湊是由InnoDB儲存引擎控制的，我們只能通過innodb_adaptive_hash_index來禁用或啟用，預設開啟。

mysql 檔案
參數檔案：告訴Mysql執行個體啟動時在哪裡可以找到資料庫檔案，並且指定某些初始化參數，這些參數定義了某種記憶體結構的大小等設定。用檔案儲存體，可編輯，若啟動時載入不到則不能成功啟動（與其他資料庫不同）。參數有動態和靜態之分，靜態相當於唯讀，動態是可以set的。如我們通過show variable like '***'查出來的key、value值，是可以通過set key=value直接修改的。同是，修改時還有範圍之分，即這個seesion個有效和全域有效，在對應的key前加上session或global即可，如select @@seesion.read_buffer_size、set @@global.read_buffer_size。
記錄檔：用來記錄Mysql執行個體對某種條件做出響應時寫入的檔案。如錯誤記錄檔檔案、二進位記錄檔、慢查詢記錄檔、查詢記錄檔等。
錯誤記錄檔：通過show variables like 'log_error'來查看錯誤記錄檔存放地址
慢查詢日誌：通過show variables like '%long%' 查看慢查詢日誌記錄的閾值，新版本設成了0.05；通過show variables like 'log_slow_queries'查看是否開啟了，預設為關閉的；通過show variabes like 'log_queries_not_using_indexes'查看是將沒有使用索引的查詢記錄到慢日誌中。mysql中可以直接通過mysqldumpslow命令來查看慢日誌。
二進位檔案：不記錄查詢，只記錄對資料庫所有的修改操作。目的是為了恢複(point-in-time修複)和複製。通過show variables like 'datadir'查看存放路徑。二進位日誌支援STATEMENT、ROW、MIX三種格式，通過binlog_format參數設定，通常設定為ROW，可以為資料庫的恢複和複製帶來更好的可靠性，但會帶來二進位檔案大小的增加，複製時會增加網路開銷。mysql中通過mysqlbinlog查看二進位記錄檔內容。
socket檔案：當用Unix域通訊端方式進行串連時需要的檔案。
pid檔案：Mysql執行個體的進程ID檔案。
Mysql表結構檔案：用來存放Mysql表結構定義檔案。因為Mysql外掛程式式儲存引擎的體繫結構，每個表都有一個對應的檔案，以frm尾碼結尾。
儲存引擎檔案：儲存自己的檔案來儲存各種資料，真正儲存了資料和索引等資料。下面主要介紹InnoDB的儲存引擎下的資料表空間檔案和重做記錄檔。
資料表空間檔案：InnoDB預設的資料表空間檔案為ibdata1,可通過show variables like 'innodb_file_per_table'查看每個表是否產生單獨的.idb資料表空間檔案。但是，單獨的資料表空間檔案僅儲存該表的資料、索引和插入緩衝等資訊，其餘資訊還是存放在預設的資料表空間中。

重做記錄檔：執行個體和介質失敗，重做記錄檔就能派上用場，如資料庫掉電，InnoDB儲存引擎會使用重做日誌恢複到掉電前的時刻，以此來保證資料的完整性。參數innodb_log_file_size指定了重做記錄檔的大小；innodb_log_file_in_group指定了記錄檔組中重做記錄檔的數量，預設為2，innodb_mirrored_log_groups指定了日誌鏡像檔案組的數量，預設為1，代表只有一個記錄檔組，沒有鏡像；innodb_log_group_home_dir指定了記錄檔組所在路徑，預設在資料庫路徑下。

二進位日誌和重做日誌的區別：首先，二進位日誌會記錄所有與Mysql有關的日誌記錄，包括InnoDB、MyISAM、Heap等其他儲存引擎的日誌。而InnoDB儲存引擎重做日誌只儲存有關其本身的交易記錄；其次內容不同，不管將二進位記錄檔記錄的格式設為STATEMENT還是ROW，又或者是MIXED，其記錄的都是關於一個事務的具體操作內容。而InnoDB儲存引擎的重做記錄檔記錄的關於每個頁的更改的物理情況 。此外，寫入時間不同，二進位記錄檔是在事務提交前進行記錄的，而在事務進行的過程中，不斷有重做日誌條目被 寫入重做記錄檔中。

mysql innodb表
資料表空間：資料表空間可看做是InnoDB儲存引擎邏輯結構的最高層。
段：資料表空間由各個段組成，常見的段有資料區段、索引段、復原段等。
區：由64個連續的頁組成，每個頁大小為16kb，即每個區大小為1MB。
頁：每頁16kb，且不能更改。常見的頁類型有：資料頁、Undo頁、系統頁、交易資料頁、插入緩衝位元影像頁、插入緩衝空閑列表頁、未壓縮的二進位大對象頁、壓縮的二進位大對象頁。
行：InnoDB儲存引擎是面向行的(row-oriented)，每頁最多允許存放7992行資料。
行記錄格式：常見兩種行記錄格式Compact和Redundant，mysql5.1版本後，主要是Compact行記錄格式。對於Compact，不管是char型還是varchar型，null型都是不佔用儲存空間的；對於Redudant,varchar的null不佔用空間，char的null型是佔用儲存空間的。

varchar類型的長度限制是65535，其實達不到，會有別的開銷，一般是65530左右，這還跟選取的字元集有關。此外這個長度限制是一整行的，例如：create table test(a varchar(22000), b varchar(22000), cvarchar(22000)) charset=latin1 engine=innodb也會報錯。

對於blob類型的資料，在資料頁面中只儲存了varchar(65535)的前768個位元組首碼資料，之後跟的是位移量，指向行溢出頁，也就是Uncompressed BLOB Page。新的InnoDB Plugin引入了新的檔案格式稱為Barracuda，其有兩種新的行記錄格式Compressed和Dynamic，兩者對於存入Blog欄位採用了完全溢出的方式，在資料庫頁中存放20個位元組的指標，實際的資料都存入在BLOB Page中。           

資料頁結構：資料頁結構由以下7個部分組成：
File Header(檔案頭):記錄頁的一些頭資訊，如頁位移量、上一頁、下一頁、頁類型等，固定長度為38個位元組。

Page Header(頁頭)：記錄頁的狀態資訊，堆中記錄數、指向空閑列表的指標、已刪除記錄的位元組數、最後插入的位置等，固定長度共56個位元組。

Infimun+Supremum Records:在InnoDB儲存引擎中，每個資料頁中有兩個虛擬行記錄，用來限定記錄的邊界。

Infimun記錄是比該頁中任何主鍵都要小的值，Supermum指比任何可能大的值還要大的值。這兩個值在頁建立時被建立，並且在任何情況下不會被刪除。在Compact行格式和Redundant行格式下，兩者佔用的位元組數各不相同。

User Records(使用者記錄，即行記錄):實現記錄的內容。再次強調，InnoDB儲存引擎表總是B+村索引組織的。
Free Space(空閑空間)：指空閑空間，同樣也是個鏈表資料結構。當一條記錄被刪除後，該空間會被加入空閑鏈 表中。

Page Directory(頁目錄)：頁目錄存放了記錄的相對位置，並不是位移量，有些時候這些記錄稱為Slots(槽)，InnoDB並不是每個記錄一個槽，槽是一個稀疏目錄，即一個槽中可能屬於多個記錄，最少屬於4條記錄，最多屬於8條記錄。需要牢記的是，B+樹索引本身並不能找到具體的一條記錄，B+樹索引能找到只是該記錄所在的頁。資料庫把頁載入記憶體，然後通過Page Directory再進行二叉尋找。只不過二叉尋找的時間複雜度低，同時記憶體中的尋找很快，因此通過忽略了這部分尋找所用的時間。

File Trailer(檔案結尾資訊)：為了保證頁完整地寫入磁碟(如寫過程的磁碟損壞、機器宕機等)，固定長8個位元組。                                         

視圖：Mysql中的視圖總是虛擬表，本身不支援物化視圖。但是通過一些其他技巧(如觸發器)，同樣也可以實現一些簡單的物化視圖的功能。

分區：Mysql資料庫支援RANGE、LIST、HASH、KEY、COLUMNS分區，並且可以使用HASH或KEY來進行子分區。

mysql innodb常見索引與演算法：
B+樹索引：B+樹的資料結構相對較複雜，B代表的是balance最早是從平衡二叉樹演化而來，但B+樹並不是一個二叉樹，對其較詳細的介紹可以參見這篇文章：http://blog.csdn.net/v_JULY_v/article/details/6530142 由於B+樹索引的高扇出性，因此在資料庫中，B+樹的高度一般都在2~3層，也就對於尋找某一索引值的行記錄，最多隻要2到3次IO,現在一般的磁碟每秒至少可以做100次IO，2~3次的IO意味著查詢時間只需0.02~0.03秒。

資料庫中的B+索引可以分為叢集索引(clustered index)和輔助叢集索引(secondary index),但其內部都是B+樹的，即高度平衡的，葉子節點存放資料。

叢集索引：由於叢集索引是按照主鍵組織的，所以每一張表只能有一個叢集索引，每個資料頁都通過雙向鏈表進行串連，葉子節點存放一整行的資訊，所以查詢最佳化工具更傾向走叢集索引。此外，對於叢集索引的儲存是邏輯上連續的。所以，叢集索引對於主鍵的排序尋找和範圍尋找速度非常快。

輔助索引：也叫非叢集索引，葉子節點不存全部資料，主要存索引值及一個boomark(其實就是叢集索引的鍵)告訴InnoDB哪裡可以找到與索引相對應的行資料，如一個高度為3的輔助索引和一個高度為3的叢集索引，若根據輔助索引來查詢行記錄，一共需要6次IO。另外輔助索引可以有多個。

索引的使用原則：高選擇、取出表中的少部分資料(也稱為唯一索引)。一般取出的資料量超過表中資料的20%，最佳化器不會使用索引，而進行全表掃描。如對於性別等欄位是沒有意義的。

聯合索引： 也稱複合索引，是在多列（>=2）上建立的索引。Innodb中的複合索引也是b+ tree結構。索引的資料包含多列(col1, col2, col3…)，在索引中依次按照col1, col2, col3排序。如(1, 2), (1, 3),(2,0)…使用複合索引要充分利用最左首碼原則，顧名思義，就是最左優先。如建立索引ind_col1_col2(col1, col2)，那麼在查詢where col1 = xxx and col2 = xx或者where col1 = xxx都可以走ind_col1_col2索引，但where col2=****是走不到索引的。在建立多列索引時，要根據業務需求，where子句中使用最頻繁且過濾效果好的的一列放在最左邊。

雜湊索引：雜湊演算法也是比較常見的演算法，mysql innoDB中使用了比較常見的鏈地址法進行去重。此外上面已經提及，innoDB中的hash是自適應的，什麼時候使用hash是系統決定的，無法進行人工設定。
二分尋找法：這個演算法比較常見，這裡就不多提及了。在InnoDB中，每頁Page Directory中的槽是按照主鍵的順序存放的，對於某一條具體記錄的查詢是通過對Page Directory進行二分尋找得到的。

mysql innodb中的鎖
InnoDB儲存引擎鎖的實現和Oracle非常類似，提供一致性的非鎖定讀、行級鎖支援、行級鎖沒有相關的開銷，可以同時得到並發性和一致性。
InnoDB儲存引擎實現了如下兩種標準的行級鎖：
共用鎖定(S Lock)：允許事務讀一行資料；
獨佔鎖定(X Lock)：允許事務刪除或者更新一行資料。
當一個事務已經獲得了行r的共用鎖定，那麼另外的事務可以立即獲得行r的共用鎖定，因為讀取沒有改變行r的資料，我們稱這種情況為鎖相容。但如果有事務想獲得行r的獨佔鎖定，則它必須等待事務釋允許存取r上的共用鎖定————這種情況稱為鎖不相容。

在InnoDB Plugin之前，只能通過SHOW FULL PROCESSLIST，SHOW ENGINE INOODB STATUS等命令來查看當前的資料庫請求，然後再判斷當前事務中的鎖的情況。新版本的InnoDB Plugin中，在INFORMATION_SCHEMA架構下添加了INNODB_TRX、INNODB_LOCKS、InnoDB_LOCK_WAITS。通過這三張表，可以更簡單地監控當前的事務並分析可能存在的鎖的問題。
INNODB_TRX由8個欄位組成：
trx_id:InnoDB儲存引擎內部唯一的事務ID
trx_state:當前事務的狀態。
trx_started:事務的開始時間。
trx_requested_lock_id:等待事務的鎖ID。如trx_state的狀態為LOCK WAIT,那麼該值代表當前的等待之前事務佔用鎖資源的ID.
若trx_state不是LOCK WAIT,則該值為NULL。
trx_wait_started:事務等待開始的時間。
trx_weight:事務的權重，反映了一個事務修改和鎖住的行數。在InnoDB儲存引擎中，當發生死結需要復原時，InnoDB儲存會選
擇該值最小的進行復原。
trx_mysql_thread_id:Mysql中的線程ID,SHOW PROCESSLIST顯示的結果。
trx_query:事務啟動並執行sql語句。
通過select * from infomation_schema.INNODB_TRX;可查看 

INNODB_LOCKS表，該表由如下欄位組成：
     lock_id:鎖的ID。
     lock_trx_id:事務ID。
     lock_mode:鎖的模式。
     lock_type:鎖的類型，表鎖還是行鎖。
     lock_table:要加鎖的表。
     lock_index:鎖的索引。
     lock_space:InnoDB儲存引擎資料表空間的ID號。
     lock_page:被鎖住的頁的數量。若是表鎖，則該值為NULL。
     lock_rec:被鎖住的行的數量。若是表鎖，則該值為NULL。
     lock_data:被鎖住的行的主索引值。當是表鎖時，該值為NULL。
     通過select * from information_schema.INNODB_LOCK;可查看

     INNODB_LOCK_WAIT由4個欄位組成：
     requesting_trx_id:申請鎖資源的事務ID。
     requesting_lock_id:申請的鎖的ID。
     blocking_trx_id:阻塞的鎖的ID。
     通過select * from information_schema.INNODB_LOCK_WAITS;可查看。 

一致性的非鎖定讀：InnoDB儲存引擎通過行多版本控制的方式來讀取當前執行時間資料庫中行的資料。如果讀取的行正在執行Delete、update操作，這時讀取操作不會因此而會等待行上鎖的釋放，相反，InnoDB儲存引擎會去讀取行的一個快照資料。快照資料是指該行之前版本的資料，該實現是通過Undo段來實現。而Undo用來事務中復原資料，因此快照本身是沒有額外開銷的。此外，快照資料是不需要上鎖的，因為沒有必要對曆史的資料進行修改。一個行可能有不止一個快照資料，所以稱這種技術為行多版本技術。由此帶來並發控制，稱之為多版本並發控制(Multi VersionConcurrency Control, MVCC)。

事務的隔離等級：Read uncommitted、Read committed、Repeatable read、serializable。在Read Committed和Repeatable Read下，InnoDB儲存引擎使用非鎖定一致性讀。然而，對於快照的定義卻不同。在Read Committed交易隔離等級下，對於快照資料，非一致性讀總是讀取被鎖定行的最新一份快照資料。在Repeatable交易隔離等級下，對於快照資料，非一致性讀總是讀取事務開始時的行資料版本。                 

鎖的演算法：
     Record Lock:單行記錄上的鎖
     Gap Lock:間隙鎖，鎖定一個範圍，但不包含記錄本身
     Next-Key Lock:Gap Lock + Record Lock，鎖定一個範圍，並且鎖定記錄本身。更加詳細的介紹可以參見這篇blog,http://www.db110.com/?p=1848

鎖的問題：
丟失更新：經典的資料庫問題，當兩個或多個事務選擇同一行，然後基於最初選定的值更新該行時，會發生丟失更新問題。每個事務都不知道其它事務的存在。最後的更新將重寫由其它事務所做的更新，這將導致資料丟失。 　　
         例:
             事務A和事務B同時修改某行的值，
              1.事務A將數值改為1並提交
              2.事務B將數值改為2並提交。
              這時資料的值為2，事務A所做的更新將會丟失。
              解決辦法：事務並行變串列操作，對更新操作加獨佔鎖定。

        髒讀：一個事務讀到另一個事務未提交的更新資料，即讀到髒資料。
          例:
              1.Mary的原工資為1000, 財務人員將Mary的工資改為了8000(但未提交事務)       
              2.Mary讀取自己的工資 ,發現自己的工資變為了8000，歡天喜地！
              3.而財務發現操作有誤，復原了事務,Mary的工資又變為了1000, 像這樣,Mary記取的工資數8000是一個髒資料。
              解決辦法：髒讀只有在交易隔離等級是Read Uncommitted的情況下才會出現，innoDB預設隔離等級是Repeatable Read，所以生產環境下不會出現髒讀。

        不可重複讀取：在同一個事務中,多次讀取同一資料,返回的結果有所不同。換句話說就是,後續讀取可以讀到另一個事務已提交的更新資料。相反"可重複讀"在同一事務多次讀取資料時,能夠保證所讀資料一樣,也就是後續讀取不能讀到另一事務已提交的更新資料。髒讀和不可重複讀取的主要區別在於，髒讀是讀到未提交的資料，不可重複讀取是讀到已提交的資料。
          例:
              1.在事務1中，Mary 讀取了自己的工資為1000,操作並沒有完成
              2.在事務2中，這時財務人員修改了Mary的工資為2000,並提交了事務.
              3.在事務1中，Mary 再次讀取自己的工資時，工資變為了2000
              解決辦法：讀到已提交的資料，一般資料庫是可接受的，因此交易隔離等級一般設為Read Committed。Mysql InnoDB通過Next-Key Lock演算法避免不可重複讀取，預設隔離等級為Repeatable Read。

mysql innodb中的事務   
事務的四個特性：原子性、一致性、隔離性、持久性
隔離性通過鎖實現，原子性、一致性、持久性通過資料庫的redo和undo來完成。
重做日誌記錄了事務的行為，通過redo實現，保證了事務的完整性，但事務有時還需要撤銷，這時就需要產生undo。undo和redo正好相反，對於資料庫進行修改時，資料庫不但會產生redo，而且還會產生一定的undo，即使執行的事務或語句由於某種原因失敗了，或者如果用一條rollback語句請求復原，就可以用這些undo資訊將資料復原到修改之前的樣子。與redo不同的是,redo存放在重做記錄檔中，undo存放在資料庫內部的一個特殊段(segment)中，這稱為undo段(undo segment)，undo段位於共用資料表空間內。還有一點重要的是，undo記錄的是與事務操作相反的邏輯操作，如insert undo 記錄一個delete，所以undo只是邏輯地將資料庫恢複成事務開始前的樣子。如:insert 10萬行的資料，可能導致資料表空間增大，復原後，資料表空間不會減小回去。