MySQL Study之--MySQL體系結構深入解析

MySQL體系架構

由連接配接池元件、管理服務和工具元件、sql接口元件、查詢分析器元件、優化器元件、緩沖元件、插件式存儲引擎、實體檔案組成。mysql是獨有的插件式體系結構,各個存儲引擎有自己的特點。

MySQL記憶體結構：

Mysql 程序結構

Mysql不像oracle那樣是通過多程序來完成其功能的。預設情況下，InnoDB存儲引擎的背景線程有7個：

4個IO thread，

1個master thread，

1個鎖（lock）監控線程，

1個錯誤監控線程；

在InnoDB Plugin版本開始增加了預設IO thread的數量，預設的read thread和write thread分别增大到了4個，并且不再使用innodb_file_ io_threads參數，而是分别使用innodb_read_io_threads和innodb_write_io_threads參數。

檢視mysql thread：

<code>mysql> show variables like </code><code>'innodb_%version%'</code><code>; </code>

<code>| Variable_name | Value | </code>

<code>| innodb_version | </code><code>1.2.</code><code>4</code> <code>| </code>

<code>mysql> show engine innodb status; </code>

<code>150709</code> <code>15</code><code>:</code><code>26</code><code>:</code><code>40</code> <code>INNODB MONITOR OUTPUT </code>

<code>Per second averages calculated </code><code>from</code> <code>thelast </code><code>12</code> <code>seconds </code>

<code>BACKGROUND THREAD </code>

<code>srv_master_thread loops: </code><code>0</code> <code>srv_active, 0srv_shutdown, </code><code>804</code> <code>srv_idle </code>

<code>srv_master_thread log flush </code><code>and</code> <code>writes: </code><code>804</code>

<code>SEMAPHORES </code>

<code>OS WAIT ARRAY INFO: reservation count </code><code>2</code>

<code>OS WAIT ARRAY INFO: signal count </code><code>2</code>

<code>Mutex spin waits </code><code>0</code><code>, rounds </code><code>0</code><code>, OS waits </code><code>0</code>

<code>RW-shared spins </code><code>2</code><code>, rounds </code><code>60</code><code>, OS waits </code><code>2</code>

<code>RW-excl spins </code><code>0</code><code>, rounds </code><code>0</code><code>, OS waits </code><code>0</code>

<code>Spin rounds per wait: </code><code>0.00</code> <code>mutex, </code><code>30.</code><code>00RW-shared, </code><code>0.00</code> <code>RW-excl </code>

<code>TRANSACTIONS </code>

<code>Trx id counter </code><code>5377</code>

<code>Purge done </code><code>for</code> <code>trx's n:o < </code><code>4872</code> <code>undo n:o< </code><code>0</code>

<code>History list length </code><code>1</code>

<code>LIST OF TRANSACTIONS FOR EACH SESSION: </code>

<code>---TRANSACTION </code><code>0</code><code>, </code><code>not</code> <code>started </code>

<code>MySQL thread id </code><code>3</code><code>, OS thread handle</code><code>0x7f1656c75700</code><code>, query id </code><code>5</code> <code>localhost root </code><code>init</code>

<code>show engine innodb status </code>

<code>I/O thread </code><code>0</code> <code>state: waiting </code><code>for</code> <code>completedaio requests (</code><code>insert</code> <code>buffer thread) </code>

<code>I/O thread </code><code>1</code> <code>state: waiting </code><code>for</code> <code>completedaio requests (log thread) </code>

<code>I/O thread </code><code>2</code> <code>state: waiting </code><code>for</code> <code>completedaio requests (read thread) </code>

<code>I/O thread </code><code>3</code> <code>state: waiting </code><code>for</code> <code>completedaio requests (read thread) </code>

<code>I/O thread </code><code>4</code> <code>state: waiting </code><code>for</code> <code>completedaio requests (read thread) </code>

<code>I/O thread </code><code>5</code> <code>state: waiting </code><code>for</code> <code>completedaio requests (read thread) </code>

<code>I/O thread </code><code>6</code> <code>state: waiting </code><code>for</code> <code>completedaio requests (write thread) </code>

<code>I/O thread </code><code>7</code> <code>state: waiting </code><code>for</code> <code>completedaio requests (write thread) </code>

<code>I/O thread </code><code>8</code> <code>state: waiting </code><code>for</code> <code>completedaio requests (write thread) </code>

<code>I/O thread </code><code>9</code> <code>state: waiting </code><code>for</code> <code>completedaio requests (write thread) </code>

<code>Pending normal aio reads: </code><code>0</code> <code>[</code><code>0</code><code>, </code><code>0</code><code>, </code><code>0</code><code>, </code><code>0</code><code>] ,aio writes: </code><code>0</code> <code>[</code><code>0</code><code>, </code><code>0</code><code>, </code><code>0</code><code>, </code><code>0</code><code>] , </code>

<code> </code><code>ibufaio reads: </code><code>0</code><code>, log i/o</code><code>'s: 0, sync i/o'</code><code>s: </code><code>0</code>

<code>Pending flushes (fsync) log: </code><code>0</code><code>; bufferpool: </code><code>0</code>

<code>283</code> <code>OS file reads, </code><code>5</code> <code>OS file writes, </code><code>5</code> <code>OSfsyncs </code>

<code>0.00</code> <code>reads/s, </code><code>0</code> <code>avg bytes/read, </code><code>0.</code><code>00writes/s, </code><code>0.00</code> <code>fsyncs/s </code>

<code>INSERT BUFFER AND ADAPTIVE HASH INDEX </code>

<code>Ibuf: size </code><code>1</code><code>, free list len </code><code>0</code><code>, seg size </code><code>2</code><code>,</code><code>0</code> <code>merges </code>

<code>merged operations: </code>

<code> </code><code>insert</code> <code>0</code><code>, </code><code>delete</code> <code>mark </code><code>0</code><code>, </code><code>delete</code> <code>0</code>

<code>discarded operations: </code>

<code>Hash table size </code><code>276707</code><code>, node heap has 0buffer(s) </code>

<code>0.00</code> <code>hash searches/s, </code><code>0.00</code> <code>non-hashsearches/s </code>

<code>Log sequence number </code><code>1611537</code>

<code>Log flushed up to </code><code>1611537</code>

<code>Pages flushed up to </code><code>1611537</code>

<code>Last checkpoint </code><code>at</code> <code>1611537</code>

<code>0</code> <code>pending log writes, </code><code>0</code> <code>pending chkp writes </code>

<code>8</code> <code>log i/o</code><code>'s done, 0.00 log i/o'</code><code>s/second </code>

<code>BUFFER POOL AND MEMORY </code>

<code>Total memory allocated </code><code>137363456</code><code>; inadditional pool allocated </code><code>0</code>

<code>Dictionary memory allocated </code><code>39010</code>

<code>Buffer pool size </code><code>8192</code>

<code>Free buffers </code><code>8040</code>

<code>Database pages </code><code>152</code>

<code>Old database pages </code><code>0</code>

<code>Modified db pages </code><code>0</code>

<code>Pending reads </code><code>0</code>

<code>Pending writes: LRU </code><code>0</code><code>, flush list </code><code>0</code> <code>singlepage </code><code>0</code>

<code>Pages made young </code><code>0</code><code>, </code><code>not</code> <code>young </code><code>0</code>

<code>0.00</code> <code>youngs/s, </code><code>0.00</code> <code>non-youngs/s </code>

<code>Pages read </code><code>152</code><code>, created </code><code>0</code><code>, written </code><code>1</code>

<code>0.00</code> <code>reads/s, </code><code>0.00</code> <code>creates/s, </code><code>0.00</code> <code>writes/s </code>

<code>No buffer pool page gets since the lastprintout </code>

<code>Pages read ahead </code><code>0.00</code><code>/s, evicted withoutaccess </code><code>0.00</code><code>/s, Random read ahead </code><code>0.00</code><code>/s </code>

<code>LRU len: </code><code>152</code><code>, unzip_LRU len: </code><code>0</code>

<code>I/O sum[</code><code>0</code><code>]:cur[</code><code>0</code><code>], unzip sum[</code><code>0</code><code>]:cur[</code><code>0</code><code>] </code>

<code>ROW OPERATIONS </code>

<code>0</code> <code>queries inside InnoDB, </code><code>0</code> <code>queries </code><code>in</code> <code>queue </code>

<code>0</code> <code>read views open inside InnoDB </code>

<code>Main thread process no. </code><code>2461</code><code>, id139733873489664, state: sleeping </code>

<code>Number</code> <code>of rows inserted </code><code>0</code><code>, updated </code><code>0</code><code>,deleted </code><code>0</code><code>, read </code><code>0</code>

<code>0.00</code> <code>inserts/s, </code><code>0.00</code> <code>updates/s, </code><code>0.</code><code>00deletes/s, </code><code>0.00</code> <code>reads/s </code>

<code>END OF INNODB MONITOR OUTPUT</code>

<code>[root@mysrv ~]# mysql -u root -p </code>

<code>Enter password: </code>

<code>Welcome to the MySQL monitor. Commands end </code><code>with</code> <code>; </code><code>or</code> <code>\g. </code>

<code>Your MySQL connection id is </code><code>3</code>

<code>Server version: </code><code>5.6.</code><code>4</code><code>-m7-log Sourcedistribution </code>

<code>Oracle is a registered trademark of OracleCorporation </code><code>and</code><code>/</code><code>or</code> <code>its </code>

<code>affiliates. Other names may be trademarksof their respective </code>

<code>owners. </code>

<code>Type </code><code>'help;'</code> <code>or</code> <code>'\h'</code> <code>for</code> <code>help. Type </code><code>'\c'</code> <code>toclear the current input statement. </code>

<code><code>"font-size:14px;"</code><code>>mysql> show variables like</code><code>'innodb_%io%'</code><code>; </code>

<code>| Variable_name | Value | </code>

<code>| innodb_additional_mem_pool_size | </code><code>8388608</code><code>| </code>

<code>| innodb_io_capacity | </code><code>200</code> <code>| </code>

<code>| innodb_read_io_threads | </code><code>4</code> <code>| </code>

<code>| innodb_replication_delay | </code><code>0</code> <code>| </code>

<code>| innodb_use_native_aio | ON | </code>

<code>| innodb_version | </code><code>1.2.</code><code>4</code> <code>| </code>

<code>| innodb_write_io_threads | </code><code>4</code> <code>| </code>

背景線程主要作用有三個：

1. 重新整理記憶體池中的資料，保證緩沖池中的記憶體緩存的是最近的資料

2. 将修改的資料檔案重新整理到磁盤檔案

3. 在資料庫發生異常情況下，保證InnoDB能恢複到正常運作狀态

master thread的線程優先級别最高。其内部由幾個循環（loop）組成：主循環（loop）、背景循環（backgroundloop）、重新整理循環（flush loop）、暫停循環（suspend loop）。master thread會根據資料庫運作的狀态在loop、background loop、 flush loop和suspend loop中進行切換。loop稱為主循環，因為大多數的操作都在這個循環中，其中有兩大部分操作：每秒鐘的操作和每10秒的操作.

Loop為主循環，如果沒有使用者活動，就切換到backgroundloop中，然後跳轉會loop，如果還需要flush重新整理，那就轉到flush loop，如果在flush loop中也空閑，那就切換到suspend loop中，将master thread挂起等待事件的發生。

Loop：

每秒一次的操作包括：

日志緩沖重新整理到磁盤，即使這個事務還沒有送出（總是）。

合并插入緩沖（可能）。

至多重新整理100個InnoDB的緩沖池中的髒頁到磁盤（可能）。

如果目前沒有使用者活動，切換到background loop（可能）。

接着來看每10秒的操作，包括如下内容：

重新整理100個髒頁到磁盤（可能）。

合并至多5個插入緩沖（總是）。

将日志緩沖重新整理到磁盤（總是）。

删除無用的Undo頁（總是）。

重新整理100個或者10%髒頁到磁盤（總是）。

産生一個檢查點（總是）。

Background loop會執行以下操作

合并20個插入緩沖（總是）。

跳回到主循環（總是）。

不斷重新整理100個頁，直到符合條件（可能，跳轉到flush loop中完成）。

Mysql程序結構--控制磁盤io的參數

innodb_io_capacity

用來表示磁盤IO的吞吐量，預設值為200。對于重新整理到磁盤的數量（髒緩沖），會按照innodb_io_capacity的百分比來重新整理相對數量的頁

在合并插入緩沖時，合并插入緩沖的數量為innodb_io_capacity數值的5%。

在從緩沖區重新整理髒頁時，重新整理髒頁的數量為innodb_io_capacity。

innodb_max_dirty_pages_pct

預設值為75%。加快髒頁重新整理頻率，減少恢複時間，也可保證磁盤IO負載。

innodb_adaptive_flushing：

在innodbplugin中。該值影響每1秒重新整理髒頁的數量。原來的重新整理規則是：如果髒頁在緩沖池所占的比例小于innodb_max_dirty_pages_pct時，不重新整理髒頁。大于innodb_max_dirty_pages_pct時，重新整理100個髒頁，而innodb_adaptive_flushing參數的引入，InnoDB存儲引擎會通過一個名為buf_flush_get_desired_flush_rate的函數判斷産生重做日志的速度來判斷最合适的重新整理髒頁的數量。是以，當髒頁的比例小于innodb_max_dirty_pages_pct時，也會重新整理一定量的髒頁。

還有兩個和mysql啟動和關閉有關的參數

innodb_fast_shutdown

這個參數影響着innodb表的行為，該參數可設定為0，1，2

0: 表示當MySQL關閉時，InnoDB需要完成所有的full purge和merge insert buffer操作。耗時

比較長。

1: 是預設值。表示不需要完成full purge和merge insert buffer操作，但是在緩沖池中的一

些資料髒頁會重新整理到磁盤。

2: 表示不完成full purge和merge insert buffer操作，也不将緩沖池中的資料髒頁寫回到磁

盤，而是将日志寫入到日志檔案。MySQL下次啟動時，會執行恢複操作。

innodb_force_recovery

影響InnoDB的恢複狀況。預設為0，表示需恢複時執行所有的恢複操作。若不能有效恢複，則MySQL有可能當機，錯誤資訊會被寫入錯誤日志檔案。還有 1~6等不同值，根據

需要可以設定。

0 :正常的關閉和啟動，不會做任何強迫恢複操作；

1 :跳過錯誤頁，讓mysqld服務繼續運作。跳過錯誤索引記錄和存儲頁，可以執行備份操作

2 :阻止InnoDB的主線程運作。清理操作時出現mysqld服務崩潰，則會阻止資料恢複操作；

3 :恢複的時候，不進行事務復原；

4 :阻止INSERT緩沖區的合并操作。不做合并操作，為防止出現mysqld服務崩潰。不計算表的統計資訊

5 :mysqld服務啟動的時候不檢查復原日志：InnoDB引擎對待每個不确定的事務就像送出的事務一樣；

6 :不做事務日志前滾恢複操作；

Mysql記憶體結構—buffer pool

InnoDB存儲引擎記憶體主要由幾個部分組成：緩沖池（bufferpool）、重做日志緩沖池（redo log buffer）以及額外的記憶體池（additional memory pool）

緩沖池是占記憶體最大的部分，用來存放各種資料的緩存。因為InnoDB的存儲引擎的工作方式總是将資料庫檔案按頁（每頁16K）讀取到緩沖池，然後按最近最少使用（LRU）的算法來保留在緩沖池中的緩存資料。如果資料庫檔案需要修改，總是首先修改在緩存池中的頁（發生修改後，該頁即為髒頁），然後再按照一定的頻率将緩沖池的髒頁重新整理（flush）到檔案。

buffer pool是通過三種list來管理的：

1) free list

2) lru list

3) flush list

buffer pool中的最小機關是page，在innodb中定義三種page

1) free page :此page未被使用，此種類型page位于free連結清單中

2) clean page:此page被使用，對應資料檔案中的一個頁面，但是頁面沒有被修改，此種類型

page位于lru連結清單中

3) dirty page:此page被使用，對應資料檔案中的一個頁面，但是頁面被修改過，此種類型page

位于lru連結清單和flush連結清單中

插入緩沖：

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

兩次寫：

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

恢複資料=頁副本+重做日志

Mysql記憶體結構—log buffer與管理池

日志緩沖：

日志緩沖将重做日志資訊先放入這個緩沖區，然後按一定頻率将其重新整理到重做日志檔案。該值一般不需要設定為很大，因為一般情況下每一秒鐘就會将重做日志緩沖重新整理到日志檔案，是以我們隻需要保證每秒産生的事務量在這個緩沖大小之内即可。

額外的記憶體池：

額外的記憶體池同樣十分重要。在InnoDB存儲引擎中，對記憶體的管理是通過一種稱為記憶體堆（heap）的方式進行的，他是用于緩存InnoDB引擎的資料字典資訊和内部資料結構，在對一些資料結構本身配置設定記憶體時，需要從額外的記憶體池中申請，當該區域的記憶體不夠時，會從緩沖池中申請，若是mysqld服務上的表對象數量較多，InnoDB引擎資料量很大，且innodb_buffer_pool_size的值設定較大，則應該适當地調整innodb_additional_mem_pool_size的值。若是出現緩存區的記憶體不足，則會直接向作業系統申請記憶體配置設定，并且會向MySQL的error log檔案寫入警告資訊。

binlog和redolog差別：

1) binary log,會記錄所有與MySQL有關的日志記錄,包括 Innodb、MyISAM、Heap等其他存儲引

擎的日志而 innodb存儲引擎的重做日志(redolog),隻記錄有關其本身的事務日志

2) 記錄内容不同：

binary log:不管你将二進制日志檔案的記錄格式設定為STATEMENT，還是ROW,又或是

MIXED, 其記錄的都是關于一個事務的具體操作内容.

redo log: 記錄innodb 每個頁的更改的實體情況

3）寫入時間不同：

binary log：事務送出前進行記錄

redo log ：事物進行過程中，不斷寫入。

本文轉自客居天涯 51CTO部落格，原文連結：http://blog.51cto.com/tiany/1672606，如需轉載請自行聯系原作者

MySQL Study之--MySQL體系結構深入解析

繼續閱讀

轉詳解C#資料庫存取圖檔三大方式

BMP檔案結構及圖像每行位元組計算方法

磁盤結構及在Linux中的命名

mysql優化（sql優化）

資料遷移方法資料遷移原則資料遷移之雙寫方案資料遷移之級聯同步方案

redis叢集資料一緻性_RedisRaft為Redis叢集帶來強大的資料一緻性

寶塔面闆mysql恢複2018.1.8更新

Centos7 MySQL 5.7 安裝MySQL 5.7 安裝

查找入職員工時間排名倒數第三的員工所有資訊

Hibernate使用Hibernate的“3個準備，7個步驟”Hibernate API簡介操作實體對象對象識别

雲計算面試題——mysql/存儲引擎/備份

SQL語言基礎：常用的資料查詢語句

Ubuntu16.04安裝Apache+MySQL+PHP1. 安裝Apache2. 安裝MySQL3. 安裝PHP4. 安裝phpMyAdmin

MySQL的4種隔離級别？出現問題

neo4j之cypher使用文檔

mysql使用source指令導入.sql檔案