MySQL體系架構
由連接池組件、管理服務和?工具組件、sql接口組件、查詢分析器組件、優化器組件、緩沖組件、插件式存儲引擎、物理?文件組成。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：
mysql> show variables like 'innodb_%version%'; +----------------+-------+ | Variable_name | Value | +----------------+-------+ | innodb_version | 1.2.4 | +----------------+-------+ 1 row in set (0.00 sec)
mysql> show engine innodb status; ===================================== 150709 15:26:40 INNODB MONITOR OUTPUT ===================================== Per second averages calculated from thelast 12 seconds ----------------- BACKGROUND THREAD ----------------- srv_master_thread loops: 0 srv_active, 0srv_shutdown, 804 srv_idle srv_master_thread log flush and writes: 804 ---------- SEMAPHORES ---------- OS WAIT ARRAY INFO: reservation count 2 OS WAIT ARRAY INFO: signal count 2 Mutex spin waits 0, rounds 0, OS waits 0 RW-shared spins 2, rounds 60, OS waits 2 RW-excl spins 0, rounds 0, OS waits 0 Spin rounds per wait: 0.00 mutex, 30.00RW-shared, 0.00 RW-excl ------------ TRANSACTIONS ------------ Trx id counter 5377 Purge done for trx's n:o < 4872 undo n:o< 0 History list length 1 LIST OF TRANSACTIONS FOR EACH SESSION: ---TRANSACTION 0, not started MySQL thread id 3, OS thread handle0x7f1656c75700, query id 5 localhost root init show engine innodb status -------- FILE I/O -------- I/O thread 0 state: waiting for completedaio requests (insert buffer thread) I/O thread 1 state: waiting for completedaio requests (log thread) I/O thread 2 state: waiting for completedaio requests (read thread) I/O thread 3 state: waiting for completedaio requests (read thread) I/O thread 4 state: waiting for completedaio requests (read thread) I/O thread 5 state: waiting for completedaio requests (read thread) I/O thread 6 state: waiting for completedaio requests (write thread) I/O thread 7 state: waiting for completedaio requests (write thread) I/O thread 8 state: waiting for completedaio requests (write thread) I/O thread 9 state: waiting for completedaio requests (write thread) Pending normal aio reads: 0 [0, 0, 0, 0] ,aio writes: 0 [0, 0, 0, 0] , ibufaio reads: 0, log i/o's: 0, sync i/o's: 0 Pending flushes (fsync) log: 0; bufferpool: 0 283 OS file reads, 5 OS file writes, 5 OSfsyncs 0.00 reads/s, 0 avg bytes/read, 0.00writes/s, 0.00 fsyncs/s ------------------------------------- INSERT BUFFER AND ADAPTIVE HASH INDEX ------------------------------------- Ibuf: size 1, free list len 0, seg size 2,0 merges merged operations: insert 0, delete mark 0, delete 0 discarded operations: insert 0, delete mark 0, delete 0 Hash table size 276707, node heap has 0buffer(s) 0.00 hash searches/s, 0.00 non-hashsearches/s --- LOG --- Log sequence number 1611537 Log flushed up to 1611537 Pages flushed up to 1611537 Last checkpoint at 1611537 0 pending log writes, 0 pending chkp writes 8 log i/o's done, 0.00 log i/o's/second ---------------------- BUFFER POOL AND MEMORY ---------------------- Total memory allocated 137363456; inadditional pool allocated 0 Dictionary memory allocated 39010 Buffer pool size 8192 Free buffers 8040 Database pages 152 Old database pages 0 Modified db pages 0 Pending reads 0 Pending writes: LRU 0, flush list 0 singlepage 0 Pages made young 0, not young 0 0.00 youngs/s, 0.00 non-youngs/s Pages read 152, created 0, written 1 0.00 reads/s, 0.00 creates/s, 0.00 writes/s No buffer pool page gets since the lastprintout Pages read ahead 0.00/s, evicted withoutaccess 0.00/s, Random read ahead 0.00/s LRU len: 152, unzip_LRU len: 0 I/O sum[0]:cur[0], unzip sum[0]:cur[0] -------------- ROW OPERATIONS -------------- 0 queries inside InnoDB, 0 queries in queue 0 read views open inside InnoDB Main thread process no. 2461, id139733873489664, state: sleeping Number of rows inserted 0, updated 0,deleted 0, read 0 0.00 inserts/s, 0.00 updates/s, 0.00deletes/s, 0.00 reads/s ---------------------------- END OF INNODB MONITOR OUTPUT
[root@mysrv ~]# mysql -u root -p Enter password: Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 3 Server version: 5.6.4-m7-log Sourcedistribution Copyright (c) 2000, 2011, Oracle and/or itsaffiliates. All rights reserved. Oracle is a registered trademark of OracleCorporation and/or its affiliates. Other names may be trademarksof their respective owners. Type 'help;' or '\h' for help. Type '\c' toclear the current input statement. mysql> show variables like'innodb_%io%'; +---------------------------------+---------+ | Variable_name | Value | +---------------------------------+---------+ | innodb_additional_mem_pool_size | 8388608| | innodb_io_capacity | 200 | | innodb_read_io_threads | 4 | | innodb_replication_delay | 0 | | innodb_use_native_aio | ON | | innodb_version | 1.2.4 | | innodb_write_io_threads | 4 | +---------------------------------+---------+ 7 rows in set (0.01 sec)
後台線程主要作用有三個：
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掛起等待事件的發生。
Mysql 進程結構
Loop：
每秒一次的操作包括：
? 日志緩沖刷新到磁盤，即使這個事務還沒有提交（總是）。
? 合並插入緩沖（可能）。
? 至多刷新100個InnoDB的緩沖池中的髒頁到磁盤（可能）。
? 如果當前沒有用戶活動，切換到background loop（可能）。
接著來看每10秒的操作，包括如下內容：
? 刷新100個髒頁到磁盤（可能）。
? 合並至多5個插入緩沖（總是）。
? 將日志緩沖刷新到磁盤（總是）。
? 刪除無用的Undo頁（總是）。
? 刷新100個或者10%髒頁到磁盤（總是）。
? 產生一個檢查點（總是）。
Background loop會執行以下操作
? 刪除無用的Undo頁（總是）。
? 合並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 ：事物進行過程中，不斷寫入。
