由：連接池組件、管理服務和工具組件、sql接口組件、查詢分析器組件、優(yōu)化器組件、       
緩沖組件、插件式存儲引擎、物理文件組成。
mysql是獨有的插件式體系結構，各個存儲引擎有自己的特點。    

   


   
mysql各個存儲引擎概述：
innodb存儲引擎：[/color][/b] 面向oltp(online transaction processing)、行鎖、支持外鍵、非鎖定讀、默認采用repeaable級別（可重復讀）通過next-keylocking策略避免幻讀、插入緩沖、二次寫、自適應哈希索引、預讀
myisam存儲引擎：不支持事務、表鎖、全文索引、適合olap（在線分析處理），其中myd:放數據文件，myi:放索引文件
ndb存儲引擎：集群存儲引擎，share nothing，可提高可用性
memory存儲引擎：數據存放在內存中，表鎖，并發(fā)性能差，默認使用哈希索引
archive存儲引擎：只支持insert和select zlib算法壓縮1：10，適合存儲歸檔數據如日志等、行鎖
maria存儲引擎：目的取代myisam、緩存數據和索引、行鎖、mvcc

    


   
innodb特性：
主體系結構：默認7個后臺線程，4個io thread(insert buffer、log、read、write),1個master thread(優(yōu)先級最高),1個鎖(lock)監(jiān)控線程，1個錯誤監(jiān)控線程。可以通過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)的算法來保留在緩沖池中的緩存數據。如果數據庫文件需要修改，總是首先修改在緩存池中的頁(發(fā)生修改后即為臟頁)，然后再按照一定的頻率將緩沖池的臟頁刷新到文件。通過命令show engine innodb status;來查看。
日志緩沖：將重做日志信息先放入這個緩沖區(qū)，然后按一定頻率將其刷新到重做日志文件。




master thread:
loop主循環(huán)每秒一次的操作：
日志緩沖刷新到磁盤，即使這個事務還沒有提交。(總是執(zhí)行，所以再大的事務commit 
的時間也是很快的)           
合并插入緩沖(innodb當前一秒發(fā)生的io次數小于5次則執(zhí)行)
至多刷新100個innodb的緩沖池中的臟頁到磁盤(超過配置的臟頁所占緩沖池比例則執(zhí)
行，在配置文件中由innodb_max_dirty_pages_pac決定，默認是90，新版本是75，
google建議是80)
如果當前沒用用戶活動，切換到backgroud loop        

loop主循環(huán)每10秒一次的操作：
刷新100個臟頁到磁盤(過去10秒IO操作小于200次則執(zhí)行)
合并至多5個插入緩沖(總是)
將日志緩沖到磁盤(總是)
刪除無用的Undo頁(總是)
刷新100個或者10個臟頁到磁盤(有超過70%的臟頁，刷新100個臟頁;否則刷新10個臟頁)
產生一個檢查點

backgroud loop，若當前沒有用戶活動(數據庫空閑時)或者數據庫關閉時，就會切換到這個循環(huán)：
刪除無用的Undo頁(總是)
合并20個插入緩沖(總是)
跳回到主循環(huán)(總是)
不斷刷新100個頁，直到符合條件(可能在flush loop中完成)

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

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

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



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

mysql 文件
參數文件：告訴Mysql實例啟動時在哪里可以找到數據庫文件，并且指定某些初始化參數，這些參數定義了某種內存結構的大小等設置。用文件存儲，可編輯，若啟動時加載不到則不能成功啟動（與其他數據庫不同）。參數有動態(tài)和靜態(tài)之分，靜態(tài)相當于只讀，動態(tài)是可以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指定了日志文件組所在路徑，默認在數據庫路徑下。

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

mysql innodb表
表空間：表空間可看做是InnoDB存儲引擎邏輯結構的最高層。
段：表空間由各個段組成，常見的段有數據段、索引段、回滾段等。
區(qū)：由64個連續(xù)的頁組成，每個頁大小為16kb，即每個區(qū)大小為1MB。
頁：每頁16kb，且不能更改。常見的頁類型有：數據頁、Undo頁、系統(tǒng)頁、事務數據頁、插入緩沖位圖頁、插入緩沖空閑列表頁、未壓縮的二進制大對象頁、壓縮的二進制大對象頁。
行：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個字節(jié)前綴數據，之后跟的是偏移量，指向行溢出頁，也就是Uncompressed BLOB Page。新的InnoDB Plugin引入了新的文件格式稱為Barracuda，其有兩種新的行記錄格式Compressed和Dynamic，兩者對于存入Blog字段采用了完全溢出的方式，在數據庫頁中存放20個字節(jié)的指針，實際的數據都存入在BLOB Page中。           





數據頁結構：數據頁結構由以下7個部分組成：
File Header(文件頭):記錄頁的一些頭信息，如頁偏移量、上一頁、下一頁、頁類型等，固定長度為38個字節(jié)。

Page Header(頁頭)：記錄頁的狀態(tài)信息，堆中記錄數、指向空閑列表的指針、已刪除記錄的字節(jié)數、最后插入的位置等，固定長度共56個字節(jié)。

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

Infimun記錄是比該頁中任何主鍵都要小的值，Supermum指比任何可能大的值還要大的值。這兩個值在頁創(chuàng)建時被建立，并且在任何情況下不會被刪除。在Compact行格式和Redundant行格式下，兩者占用的字節(jié)數各不相同。



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

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

File Trailer(文件結尾信息)：為了保證頁完整地寫入磁盤(如寫過程的磁盤損壞、機器宕機等)，固定長8個字節(jié)。                                         

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

分區(qū)：Mysql數據庫支持RANGE、LIST、HASH、KEY、COLUMNS分區(qū)，并且可以使用HASH或KEY來進行子分區(qū)。

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+樹的，即高度平衡的，葉子節(jié)點存放數據。

聚集索引：由于聚集索引是按照主鍵組織的，所以每一張表只能有一個聚集索引，每個數據頁都通過雙向鏈表進行連接，葉子節(jié)點存放一整行的信息，所以查詢優(yōu)化器更傾向走聚集索引。此外，對于聚集索引的存儲是邏輯上連續(xù)的。所以，聚集索引對于主鍵的排序查找和范圍查找速度非?？臁?br>
輔助索引：也叫非聚集索引，葉子節(jié)點不存全部數據，主要存鍵值及一個boomark(其實就是聚集索引的鍵)告訴InnoDB哪里可以找到與索引相對應的行數據，如一個高度為3的輔助索引和一個高度為3的聚集索引，若根據輔助索引來查詢行記錄，一共需要6次IO。另外輔助索引可以有多個。


索引的使用原則：高選擇、取出表中的少部分數據(也稱為唯一索引)。一般取出的數據量超過表中數據的20%，優(yōu)化器不會使用索引，而進行全表掃描。如對于性別等字段是沒有意義的。

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

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

mysql innodb中的鎖
InnoDB存儲引擎鎖的實現和Oracle非常類似，提供一致性的非鎖定讀、行級鎖支持、行級鎖沒有相關的開銷，可以同時得到并發(fā)性和一致性。
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。通過這三張表，可以更簡單地監(jiān)控當前的事務并分析可能存在的鎖的問題。
INNODB_TRX由8個字段組成：
trx_id:InnoDB存儲引擎內部唯一的事務ID
trx_state:當前事務的狀態(tài)。
trx_started:事務的開始時間。
trx_requested_lock_id:等待事務的鎖ID。如trx_state的狀態(tài)為LOCK WAIT,那么該值代表當前的等待之前事務占用鎖資源的ID.
若trx_state不是LOCK WAIT,則該值為NULL。
trx_wait_started:事務等待開始的時間。
trx_weight:事務的權重，反映了一個事務修改和鎖住的行數。在InnoDB存儲引擎中，當發(fā)生死鎖需要回滾時，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存儲引擎通過行多版本控制的方式來讀取當前執(zhí)行時間數據庫中行的數據。如果讀取的行正在執(zhí)行Delete、update操作，這時讀取操作不會因此而會等待行上鎖的釋放，相反，InnoDB存儲引擎會去讀取行的一個快照數據?？煺諗祿侵冈撔兄鞍姹镜臄祿?，該實現是通過Undo段來實現。而Undo用來事務中回滾數據，因此快照本身是沒有額外開銷的。此外，快照數據是不需要上鎖的，因為沒有必要對歷史的數據進行修改。一個行可能有不止一個快照數據，所以稱這種技術為行多版本技術。由此帶來并發(fā)控制，稱之為多版本并發(fā)控制(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

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

        臟讀：一個事務讀到另一個事務未提交的更新數據，即讀到臟數據。
          例:
              1.Mary的原工資為1000, 財務人員將Mary的工資改為了8000(但未提交事務)       
              2.Mary讀取自己的工資 ,發(fā)現自己的工資變?yōu)榱?000，歡天喜地！
              3.而財務發(fā)現操作有誤，回滾了事務,Mary的工資又變?yōu)榱?000, 像這樣,Mary記取的工資數8000是一個臟數據。
              解決辦法：臟讀只有在事務隔離級別是Read Uncommitted的情況下才會出現，innoDB默認隔離級別是Repeatable Read，所以生產環(huán)境下不會出現臟讀。

        不可重復讀：在同一個事務中,多次讀取同一數據,返回的結果有所不同。換句話說就是,后續(xù)讀取可以讀到另一個事務已提交的更新數據。相反"可重復讀"在同一事務多次讀取數據時,能夠保證所讀數據一樣,也就是后續(xù)讀取不能讀到另一事務已提交的更新數據。臟讀和不可重復讀的主要區(qū)別在于，臟讀是讀到未提交的數據，不可重復讀是讀到已提交的數據。
          例:
              1.在事務1中，Mary 讀取了自己的工資為1000,操作并沒有完成
              2.在事務2中，這時財務人員修改了Mary的工資為2000,并提交了事務.
              3.在事務1中，Mary 再次讀取自己的工資時，工資變?yōu)榱?000
              解決辦法：讀到已提交的數據，一般數據庫是可接受的，因此事務隔離級別一般設為Read Committed。Mysql InnoDB通過Next-Key Lock算法避免不可重復讀，默認隔離級別為Repeatable Read。

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