1.   第三章MYSQL 體系結構與儲存引擎

1.1.   mysql體系結構

體系結構分為兩大層：

l   mysql server層：包括連線層，SQL層；

l   儲存引擎層；

 

 

 

1.2.   query cache詳解及關閉

query cache只能快取靜態資料。適用於資料倉儲。

5.6前預設開啟，5.6後預設關閉。

徹底關閉query cache

涉及query_cache的兩個核心引數

mysql>show variables like “%query_cache_size%”

mysql>show variables like “$query_cache_type%”

必須一開始就將query_cache_type設定未off

 

備註：

壓力測試工具sysbench是一個開源的、模組化、跨平臺的多執行緒效能測試工具，可進行CPU、記憶體、磁碟IO、執行緒、資料庫的效能測試。，支援mysql,oracle,postgresql

下載地址：https://dev.mysql.com/downloads/benchmarks.html

具體測試過程見書  p44

1.3.   儲存引擎

儲存引擎MyISAM建議停用，轉換到InnoDB儲存引擎上。

1.4.   innoDB體系結構

1.4.1.   資料庫和資料庫例項

mysql資料庫是單程式多執行緒模型的資料庫

l   例項是程式加記憶體的組合。

l   執行緒：將記憶體資料重新整理到硬碟上。

l   磁碟檔案：redolog，資料檔案，Undo log

1.4.2.   InnoDB的儲存結構

InnoDB邏輯儲存單元主要分為表空間，段，區，頁。

層級關係：tablespace>segment>extent(64個page,1M)>page

表空間

       系統表空間；ibdata1

 

共享表空間  獨立表空間更好

獨立表空間：每表有自己表空間，可實現表空間轉移，回收方便。alter table tablename engine=innodb 或pt-online_schema_change即可

共享表空間不能線上回收空間，需匯出刪除再匯入，統計分析、日誌類不適合。

mysql 5.7後多了臨時表空間，通用表空間

 

 

段

資料段，回滾段，索引段。

每個段由N個區和32個零散的頁組成。

建立一個索引會建立兩個段，分別為非葉子節點和葉子節點段。

一個表有4個段，是索引個數的兩倍。

 

區

連續的頁組成，物理上連續分配的一段空間，每個區大小固定1M。

 

頁

InnoDB的最小物理儲存分配單位是page，有資料頁、回滾頁，一般情況一個區由64個連續的頁組成，頁預設16KB。

mysql5.6 可調低為8KB或4KB

mysql5.7 可調高為32KB或64KB

行

頁記錄行記錄的資訊

InnoDB儲存引擎面向列，也就是資料按照行儲存，行記錄資料按照行格式進行存放。

InnoDB儲存引擎有兩種檔案格式

1.      Antelope：有corrpact和redundant兩種行記錄格式

2.      Barracuda：有commpressed和dynamic兩種行記錄格式。

 

行溢位： 需要儲存的資料再當前儲存頁面之外，拆分到多個頁進行儲存。

redundant:最早的行記錄格式，比compact要消耗更多的儲存空間，不建議使用。

commpressed:壓縮行格式，對物理儲存層面上的資料庫和索引頁進行壓縮，記憶體不壓縮，呼叫到記憶體需轉換，消耗CPU,壓縮率只有1/2，不建議使用。

compact:溢位的列只存放768各字首位元組。

dynamic:新版本預設的行記錄格式，溢位資料存放溢位頁中，資料頁只存指標。溢位列所在的新頁利用率高，推薦使用。

mysql5.7 預設使用dynamic行記錄格式和Barracuda檔案格式；

 

透過檢視引數row_format可檢視

show table status like ‘%user%’\G;

row_format

檢視檔案格式

show variables like ‘%innodb_file%’;

innodb_file_format  

 

1.4.3.   記憶體結構

記憶體分為SGA(系統全域性區)和PGA(程式快取區)，

檢視配置引數：show variables like ‘%buffer%’

引數介紹，見p54

 

SGA（系統全域性區）

innodb_buffer_pool:快取InnoDB表的資料、索引、插入緩衝、資料字典等資訊；

innodb_log_buffer:事務在記憶體中的緩衝，即redo log buffer的大小；

Query Cache:高速查詢快取，生產環境建議關閉。只能快取靜態資料。適用於資料倉儲。

key_buffer_size:只用於MyISAM儲存引擎表，快取MyISAM儲存。

innodb_additional_mem_pool_size:儲存資料字典資訊和其他內部資料結構的記憶體池大小，在5.7.4中被移除。

 

SGA（程式緩衝區）

sort_buffer_size:用於SQL語句在記憶體中的臨時排序。

join_buffer_size:表連線使用，用於BKA

read_buffer_size:表順序掃描的快取，只能用於MyISAM儲存引擎。

read_rnd_buffer_size:隨機讀緩衝區大小，用於做mrr。

 

特別的

tmp_table_size:SQL語句在排序或者分組時沒有用到索引，就使用臨時表空間。

max_heap_table_size:管理heap、memory儲存引擎表。

查詢引數：

[mysql]>show variables like'%heap%';

[mysql]>show variables like'%tmp_table%'

兩個引數以最小為準，建議設定一樣大。

 

default_tmp_storage_engine:臨時表預設的儲存引擎；

interal_tmp_disk_storage_engine:磁碟臨時表的管理，決定（create temporary table）,5.7新增。

1.4.4.   Buffer狀態及其連結串列結構

page是InnoDB磁碟的IO最小單位，對應到記憶體中是一個個buffer，每個buffer分為3種狀態：

l   free buffer:：未被使用。

l   clean buffer：buffer資料和磁碟page資料一致。

l   dirty buffer:記憶體資料還未寫入到磁碟。

三種不同的buffer衍生三條連結串列

1.4.5.   各大重新整理執行緒及其作用

master thread執行緒 ：後臺主執行緒，優先順序最高。其內部有4個迴圈：

l   主迴圈loop，

l   後臺迴圈background loop，

l   重新整理迴圈flush loop，

l   暫停迴圈suspend loop。

根據資料庫執行狀態再4個迴圈間切換。在loop主迴圈中又包含兩種操作，分為：

 

四大IO執行緒

l   read thread: 資料庫讀寫請求執行緒，預設4個，可擴大。

l   write thread：

l   redo log thread：日誌緩衝區中內容重新整理到redo log中；

l   change buffer thread：把插入緩衝區內內容重新整理到磁碟。

 

page cleaner thread 負責髒頁重新整理的執行緒。show variables like ‘%innodb_page%’

purge thread: 負責刪除無用undo頁，由於進行DML語句的操作都會生成UNDO，系統需要定期對undo頁進行清理，就需要purge操作。

checkpoint： 在redo log切換時，執行checkpoint，redo log發生切換會觸發把髒頁重新整理到磁碟。

error monitor thread: 負責資料庫報錯的監控執行緒；

lock monitor thread 負責鎖的監控執行緒。

1.4.6.   記憶體重新整理機制

三個記憶體部分重新整理

redo log buffer ,data buffer ,binlog cache

 

redo log

重做日誌檔案，記錄事務操作的變化，記錄資料修改之後得值，不管事務是否提交都會記錄下來。至少2個redo log，磁碟用ib_logfile(0-N)命名；影響redo log buffer重新整理到磁碟得條件

a.透過innodb_flush_log_at_trx_commit控制，分別為0，1，2；

       0:redo log thread每隔1s 觸發redo log buffer和資料寫磁碟，但事務提交不啟動重新整理。

       1：每次事務提交，觸發redo log buffer和資料寫到磁碟。

       2：每次事務提交，觸發redo log buffer寫，但不重新整理磁碟。

b.mater thread：每秒重新整理

c.redo log buffer，使用超過1半觸發重新整理。

 

binlog

DML語句既寫redo log，也寫binlog檔案。

binlog叫mysql的二進位制日誌檔案，功能用於備份恢復和主從複製。sync_binlog引數決定重新整理條件

       0 ：事務提交後，不立即將binlog_cache資訊重新整理到磁碟，而讓filesystem自行決定同步。

       n:：每n次事務提交後，binlog_cache重新整理到磁碟。

 

確保資料庫安全的雙一模式：即sync_binlog=1，innodb_flush_log_at_trx_commit=1

 

redo log 和binlog的區別

第一：記錄內容不同

l   binlog是邏輯日誌，記錄所有資料的改變資訊。

l   redo log是物理日誌，記錄所有InnoDB表資料的變化。

第二：記錄內容的時間不同

l   binlog記錄commit完畢之後的DML和DDL SQL語句。

l   redo log 記錄事務發起之後的DML和DDL SQL語句。

第三：檔案使用方式的不同

l   binlog不是迴圈使用，在寫滿或例項重啟之後，會生成新檔案；

l   relo log是迴圈使用，最後一個寫滿再寫第一個。

第四：作用不同

l   binlog可以作為恢復資料使用，主從複製搭建。

l   redo log作為異常關機或介質故障後資料恢復使用。

 

 

MySQL兩階段提交過程 ：

兩階段提交分為prepare和commit階段。

l   準備階段（transaction prepare）：事務SQL先寫入到redo log buffer，然後做一個事務準備標誌，再將log buffer中資料重新整理到redo log。

l   提交階段（commit）:將事務產生的binlog寫入檔案，刷入磁碟。

l   再在redo log中做一個事務提交標誌，把binlog寫成功的標記一併寫入redo log檔案。

總結：只要binlog寫入完成，在主從複製環境中，都會正常完成事務。

 

髒頁的重新整理機制

l   redo log切換時，執行checkpoint，會觸發髒頁的重新整理。

l   透過innodb_max_dirty_pages_pct引數控制，表示buffer pool中dirty page的百分百。達到閾值啟動重新整理。預設75%，建議設定25%-50%，為避免後期影響效能。

l   由innodb_adaptive_flushing控制每秒重新整理髒頁的數目，智慧控制，預設開啟。

1.4.7.   InnoDB的三大特性

三大特性為：插入緩衝（change buffer）,兩次寫（double write），自適應雜湊索引（adaptive hash index）

 

插入緩衝：

將普通索引上的DML操作從隨機IO變成順序IO，提高IO效率。

原理：1.先判斷插入的普通索引頁是否在緩衝池中，如在就直接插入；如不在就先放到change buffer中，然後進行change buffer和普通索引的合併操作，多個插入合併到一個操作中，提高普通索引插入效能。

涉及引數：

[mysql]>show variables like'%change%';

+-------------------------------+-------+

| Variable_name                 | Value |

+-------------------------------+-------+

| innodb_change_buffer_max_size | 25    |

| innodb_change_buffering       | all   |

| session_track_state_change    | OFF   |

+-------------------------------+-------+

 

innodb_change_buffer_max_size:佔innodb_buffer_pool的最大比例，預設25%，建議50.

innodb_change_buffering:change buffer的型別，有如下幾種。

l   all:緩衝全部insert,delete標記操作和purge操作, 建議選擇預設的all。

l   none:關閉insert buffer;

l   inserts:insert 標記操作

l   delete:delete標記操作

l   changes:未進行實際insert和delete，只標記，等待後續purge;

l   purges:緩衝後臺程式的purges(物理刪除)操作.

 

 

兩次寫（dourble write）

       插入緩衝帶來針對普通索引插入效能上的提升，而double write就是保證吸入的安全性，防止系統當機。InnoDB發生資料頁部分寫（partial page write）問題，redo log檔案記錄的是頁的物理記錄，如果頁損壞就無法進行恢復，就用binlog恢復原來的頁，再透過redo log恢復。

 

雙寫緩衝位於系統表空間中的儲存區域，吸入順序如下：

l   InnoDB緩衝池中髒頁被寫入資料檔案之前，讀先寫入double write buffer。

l   然後分兩次從double write buffer,每次將1MB大小資料寫入磁碟共享表空間（double write）.

l   最後再從double write buffer寫入資料檔案。

雙寫緩衝不佔用兩倍IO，double 是一個大型的連續快，會透過一次fsync()通知作業系統。

 

自使用雜湊索引

InnoDB可以監控索引的搜尋，如注意到查詢可透過建立雜湊索引得到最佳化，就會自動完成。透過innodb_adaptive_hash_index引數控制。

從5.7.8開始，自適應雜湊索引搜尋系統是分割槽的，每個索引繫結到一個特殊的分割槽上面，各個分割槽都有自己的鎖存器來進行維護。分割槽透過innodb_adaptive_hash_index_parts引數控制，預設值8各，最大512，透過分割槽設定，可降低爭用，提高併發。

透過show engine innodb status命令輸出的semaphores部分監控自使用雜湊索引的使用及其競爭情況。如看到許多執行緒等待一個再btr0sea.c中建立的RW-latch,則它可能被用於禁用自適應雜湊索引。