基于mysql體系結構的深入解析

2020-01-18 23:37:08

字體：大中小

來源：轉載

供稿：網友

由：連接池組件、管理服務和工具組件、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萬行的數據，可能導致表空間增大，回滾后，表空間不會減小回去。

上一篇：關于mysql 的時間類型選擇

下一篇：淺析drop user與delete from mysql.user的區別