mysql學(xué)習(xí)6：第三章MYSQL 體系結(jié)構(gòu)與存儲(chǔ)引擎

1.   第三章MYSQL 體系結(jié)構(gòu)與存儲(chǔ)引擎

1.1.   mysql體系結(jié)構(gòu)

體系結(jié)構(gòu)分為兩大層：

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

l   存儲(chǔ)引擎層；

1.2.   query cache詳解及關(guān)閉

query cache只能緩存靜態(tài)數(shù)據(jù)。適用于數(shù)據(jù)倉(cāng)庫(kù)。

5.6前默認(rèn)開(kāi)啟，5.6后默認(rèn)關(guān)閉。

徹底關(guān)閉query cache

涉及query_cache的兩個(gè)核心參數(shù)

mysql>show variables like “%query_cache_size%”

mysql>show variables like “$query_cache_type%”

必須一開(kāi)始就將query_cache_type設(shè)置未off

備注：

壓力測(cè)試工具sysbench是一個(gè)開(kāi)源的、模塊化、跨平臺(tái)的多線(xiàn)程性能測(cè)試工具，可進(jìn)行CPU、內(nèi)存、磁盤(pán)IO、線(xiàn)程、數(shù)據(jù)庫(kù)的性能測(cè)試。，支持mysql,oracle,postgresql

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

具體測(cè)試過(guò)程見(jiàn)書(shū)  p44

1.3.   存儲(chǔ)引擎

存儲(chǔ)引擎MyISAM建議停用，轉(zhuǎn)換到InnoDB存儲(chǔ)引擎上。

1.4.   innoDB體系結(jié)構(gòu)

1.4.1.   數(shù)據(jù)庫(kù)和數(shù)據(jù)庫(kù)實(shí)例

mysql數(shù)據(jù)庫(kù)是單進(jìn)程多線(xiàn)程模型的數(shù)據(jù)庫(kù)

l   實(shí)例是進(jìn)程加內(nèi)存的組合。

l   線(xiàn)程：將內(nèi)存數(shù)據(jù)刷新到硬盤(pán)上。

l   磁盤(pán)文件：redolog，數(shù)據(jù)文件，Undo log

1.4.2.   InnoDB的存儲(chǔ)結(jié)構(gòu)

InnoDB邏輯存儲(chǔ)單元主要分為表空間，段，區(qū)，頁(yè)。

層級(jí)關(guān)系：tablespace>segment>extent(64個(gè)page,1M)>page

表空間

       系統(tǒng)表空間；ibdata1

共享表空間  獨(dú)立表空間更好

獨(dú)立表空間：每表有自己表空間，可實(shí)現(xiàn)表空間轉(zhuǎn)移，回收方便。alter table tablename engine=innodb 或pt-online_schema_change即可

共享表空間不能在線(xiàn)回收空間，需導(dǎo)出刪除再導(dǎo)入，統(tǒng)計(jì)分析、日志類(lèi)不適合。

mysql 5.7后多了臨時(shí)表空間，通用表空間

段

數(shù)據(jù)段，回滾段，索引段。

每個(gè)段由N個(gè)區(qū)和32個(gè)零散的頁(yè)組成。

創(chuàng)建一個(gè)索引會(huì)創(chuàng)建兩個(gè)段，分別為非葉子節(jié)點(diǎn)和葉子節(jié)點(diǎn)段。

一個(gè)表有4個(gè)段，是索引個(gè)數(shù)的兩倍。

區(qū)

連續(xù)的頁(yè)組成，物理上連續(xù)分配的一段空間，每個(gè)區(qū)大小固定1M。

頁(yè)

InnoDB的最小物理存儲(chǔ)分配單位是page，有數(shù)據(jù)頁(yè)、回滾頁(yè)，一般情況一個(gè)區(qū)由64個(gè)連續(xù)的頁(yè)組成，頁(yè)默認(rèn)16KB。

mysql5.6 可調(diào)低為8KB或4KB

mysql5.7 可調(diào)高為32KB或64KB

行

頁(yè)記錄行記錄的信息

InnoDB存儲(chǔ)引擎面向列，也就是數(shù)據(jù)按照行存儲(chǔ)，行記錄數(shù)據(jù)按照行格式進(jìn)行存放。

InnoDB存儲(chǔ)引擎有兩種文件格式

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

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

行溢出： 需要存儲(chǔ)的數(shù)據(jù)再當(dāng)前存儲(chǔ)頁(yè)面之外，拆分到多個(gè)頁(yè)進(jìn)行存儲(chǔ)。

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

commpressed:壓縮行格式，對(duì)物理存儲(chǔ)層面上的數(shù)據(jù)庫(kù)和索引頁(yè)進(jìn)行壓縮，內(nèi)存不壓縮，調(diào)用到內(nèi)存需轉(zhuǎn)換，消耗CPU,壓縮率只有1/2，不建議使用。

compact:溢出的列只存放768各前綴字節(jié)。

dynamic:新版本默認(rèn)的行記錄格式，溢出數(shù)據(jù)存放溢出頁(yè)中，數(shù)據(jù)頁(yè)只存指針。溢出列所在的新頁(yè)利用率高，推薦使用。

mysql5.7 默認(rèn)使用dynamic行記錄格式和Barracuda文件格式；

通過(guò)查看參數(shù)row_format可查看

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

row_format

查看文件格式

show variables like ‘%innodb_file%’;

innodb_file_format  

1.4.3.   內(nèi)存結(jié)構(gòu)

內(nèi)存分為SGA(系統(tǒng)全局區(qū))和PGA(程序緩存區(qū))，

查看配置參數(shù)：show variables like ‘%buffer%’

參數(shù)介紹，見(jiàn)p54

SGA（系統(tǒng)全局區(qū)）

innodb_buffer_pool:緩存InnoDB表的數(shù)據(jù)、索引、插入緩沖、數(shù)據(jù)字典等信息；

innodb_log_buffer:事務(wù)在內(nèi)存中的緩沖，即redo log buffer的大小；

Query Cache:高速查詢(xún)緩存，生產(chǎn)環(huán)境建議關(guān)閉。只能緩存靜態(tài)數(shù)據(jù)。適用于數(shù)據(jù)倉(cāng)庫(kù)。

key_buffer_size:只用于MyISAM存儲(chǔ)引擎表，緩存MyISAM存儲(chǔ)。

innodb_additional_mem_pool_size:保存數(shù)據(jù)字典信息和其他內(nèi)部數(shù)據(jù)結(jié)構(gòu)的內(nèi)存池大小，在5.7.4中被移除。

SGA（程序緩沖區(qū)）

sort_buffer_size:用于SQL語(yǔ)句在內(nèi)存中的臨時(shí)排序。

join_buffer_size:表連接使用，用于BKA

read_buffer_size:表順序掃描的緩存，只能用于MyISAM存儲(chǔ)引擎。

read_rnd_buffer_size:隨機(jī)讀緩沖區(qū)大小，用于做mrr。

特別的

tmp_table_size:SQL語(yǔ)句在排序或者分組時(shí)沒(méi)有用到索引，就使用臨時(shí)表空間。

max_heap_table_size:管理heap、memory存儲(chǔ)引擎表。

查詢(xún)參數(shù)：

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

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

兩個(gè)參數(shù)以最小為準(zhǔn)，建議設(shè)置一樣大。

default_tmp_storage_engine:臨時(shí)表默認(rèn)的存儲(chǔ)引擎；

interal_tmp_disk_storage_engine:磁盤(pán)臨時(shí)表的管理，決定（create temporary table）,5.7新增。

1.4.4.   Buffer狀態(tài)及其鏈表結(jié)構(gòu)

page是InnoDB磁盤(pán)的IO最小單位，對(duì)應(yīng)到內(nèi)存中是一個(gè)個(gè)buffer，每個(gè)buffer分為3種狀態(tài)：

l   free buffer:：未被使用。

l   clean buffer：buffer數(shù)據(jù)和磁盤(pán)page數(shù)據(jù)一致。

l   dirty buffer:內(nèi)存數(shù)據(jù)還未寫(xiě)入到磁盤(pán)。

三種不同的buffer衍生三條鏈表

1.4.5.   各大刷新線(xiàn)程及其作用

master thread線(xiàn)程 ：后臺(tái)主線(xiàn)程，優(yōu)先級(jí)最高。其內(nèi)部有4個(gè)循環(huán)：

l   主循環(huán)loop，

l   后臺(tái)循環(huán)background loop，

l   刷新循環(huán)flush loop，

l   暫停循環(huán)suspend loop。

根據(jù)數(shù)據(jù)庫(kù)運(yùn)行狀態(tài)再4個(gè)循環(huán)間切換。在loop主循環(huán)中又包含兩種操作，分為：

四大IO線(xiàn)程

l   read thread: 數(shù)據(jù)庫(kù)讀寫(xiě)請(qǐng)求線(xiàn)程，默認(rèn)4個(gè)，可擴(kuò)大。

l   write thread：

l   redo log thread：日志緩沖區(qū)中內(nèi)容刷新到redo log中；

l   change buffer thread：把插入緩沖區(qū)內(nèi)內(nèi)容刷新到磁盤(pán)。

page cleaner thread 負(fù)責(zé)臟頁(yè)刷新的線(xiàn)程。show variables like ‘%innodb_page%’

purge thread: 負(fù)責(zé)刪除無(wú)用undo頁(yè)，由于進(jìn)行DML語(yǔ)句的操作都會(huì)生成UNDO，系統(tǒng)需要定期對(duì)undo頁(yè)進(jìn)行清理，就需要purge操作。

checkpoint： 在redo log切換時(shí)，執(zhí)行checkpoint，redo log發(fā)生切換會(huì)觸發(fā)把臟頁(yè)刷新到磁盤(pán)。

error monitor thread: 負(fù)責(zé)數(shù)據(jù)庫(kù)報(bào)錯(cuò)的監(jiān)控線(xiàn)程；

lock monitor thread 負(fù)責(zé)鎖的監(jiān)控線(xiàn)程。

1.4.6.   內(nèi)存刷新機(jī)制

三個(gè)內(nèi)存部分刷新

redo log buffer ,data buffer ,binlog cache

redo log

重做日志文件，記錄事務(wù)操作的變化，記錄數(shù)據(jù)修改之后得值，不管事務(wù)是否提交都會(huì)記錄下來(lái)。至少2個(gè)redo log，磁盤(pán)用ib_logfile(0-N)命名；影響redo log buffer刷新到磁盤(pán)得條件

a.通過(guò)innodb_flush_log_at_trx_commit控制，分別為0，1，2；

       0:redo log thread每隔1s 觸發(fā)redo log buffer和數(shù)據(jù)寫(xiě)磁盤(pán)，但事務(wù)提交不啟動(dòng)刷新。

       1：每次事務(wù)提交，觸發(fā)redo log buffer和數(shù)據(jù)寫(xiě)到磁盤(pán)。

       2：每次事務(wù)提交，觸發(fā)redo log buffer寫(xiě)，但不刷新磁盤(pán)。

b.mater thread：每秒刷新

c.redo log buffer，使用超過(guò)1半觸發(fā)刷新。

binlog

DML語(yǔ)句既寫(xiě)redo log，也寫(xiě)binlog文件。

binlog叫mysql的二進(jìn)制日志文件，功能用于備份恢復(fù)和主從復(fù)制。sync_binlog參數(shù)決定刷新條件

       0 ：事務(wù)提交后，不立即將binlog_cache信息刷新到磁盤(pán)，而讓filesystem自行決定同步。

       n:：每n次事務(wù)提交后，binlog_cache刷新到磁盤(pán)。

確保數(shù)據(jù)庫(kù)安全的雙一模式：即sync_binlog=1，innodb_flush_log_at_trx_commit=1

redo log 和binlog的區(qū)別

第一：記錄內(nèi)容不同

l   binlog是邏輯日志，記錄所有數(shù)據(jù)的改變信息。

l   redo log是物理日志，記錄所有InnoDB表數(shù)據(jù)的變化。

第二：記錄內(nèi)容的時(shí)間不同

l   binlog記錄commit完畢之后的DML和DDL SQL語(yǔ)句。

l   redo log 記錄事務(wù)發(fā)起之后的DML和DDL SQL語(yǔ)句。

第三：文件使用方式的不同

l   binlog不是循環(huán)使用，在寫(xiě)滿(mǎn)或?qū)嵗貑⒅螅瑫?huì)生成新文件；

l   relo log是循環(huán)使用，最后一個(gè)寫(xiě)滿(mǎn)再寫(xiě)第一個(gè)。

第四：作用不同

l   binlog可以作為恢復(fù)數(shù)據(jù)使用，主從復(fù)制搭建。

l   redo log作為異常關(guān)機(jī)或介質(zhì)故障后數(shù)據(jù)恢復(fù)使用。

MySQL兩階段提交過(guò)程 ：

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

l   準(zhǔn)備階段（transaction prepare）：事務(wù)SQL先寫(xiě)入到redo log buffer，然后做一個(gè)事務(wù)準(zhǔn)備標(biāo)志，再將log buffer中數(shù)據(jù)刷新到redo log。

l   提交階段（commit）:將事務(wù)產(chǎn)生的binlog寫(xiě)入文件，刷入磁盤(pán)。

l   再在redo log中做一個(gè)事務(wù)提交標(biāo)志，把binlog寫(xiě)成功的標(biāo)記一并寫(xiě)入redo log文件。

總結(jié)：只要binlog寫(xiě)入完成，在主從復(fù)制環(huán)境中，都會(huì)正常完成事務(wù)。

臟頁(yè)的刷新機(jī)制

l   redo log切換時(shí)，執(zhí)行checkpoint，會(huì)觸發(fā)臟頁(yè)的刷新。

l   通過(guò)innodb_max_dirty_pages_pct參數(shù)控制，表示buffer pool中dirty page的百分百。達(dá)到閾值啟動(dòng)刷新。默認(rèn)75%，建議設(shè)置25%-50%，為避免后期影響性能。

l   由innodb_adaptive_flushing控制每秒刷新臟頁(yè)的數(shù)目，智能控制，默認(rèn)開(kāi)啟。

1.4.7.   InnoDB的三大特性

三大特性為：插入緩沖（change buffer）,兩次寫(xiě)（double write），自適應(yīng)哈希索引（adaptive hash index）

插入緩沖：

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

原理：1.先判斷插入的普通索引頁(yè)是否在緩沖池中，如在就直接插入；如不在就先放到change buffer中，然后進(jìn)行change buffer和普通索引的合并操作，多個(gè)插入合并到一個(gè)操作中，提高普通索引插入性能。

涉及參數(shù)：

[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的最大比例，默認(rèn)25%，建議50.

innodb_change_buffering:change buffer的類(lèi)型，有如下幾種。

l   all:緩沖全部insert,delete標(biāo)記操作和purge操作, 建議選擇默認(rèn)的all。

l   none:關(guān)閉insert buffer;

l   inserts:insert 標(biāo)記操作

l   delete:delete標(biāo)記操作

l   changes:未進(jìn)行實(shí)際insert和delete，只標(biāo)記，等待后續(xù)purge;

l   purges:緩沖后臺(tái)進(jìn)程的purges(物理刪除)操作.

兩次寫(xiě)（dourble write）

       插入緩沖帶來(lái)針對(duì)普通索引插入性能上的提升，而double write就是保證吸入的安全性，防止系統(tǒng)宕機(jī)。InnoDB發(fā)生數(shù)據(jù)頁(yè)部分寫(xiě)（partial page write）問(wèn)題，redo log文件記錄的是頁(yè)的物理記錄，如果頁(yè)損壞就無(wú)法進(jìn)行恢復(fù)，就用binlog恢復(fù)原來(lái)的頁(yè)，再通過(guò)redo log恢復(fù)。

雙寫(xiě)緩沖位于系統(tǒng)表空間中的存儲(chǔ)區(qū)域，吸入順序如下：

l   InnoDB緩沖池中臟頁(yè)被寫(xiě)入數(shù)據(jù)文件之前，讀先寫(xiě)入double write buffer。

l   然后分兩次從double write buffer,每次將1MB大小數(shù)據(jù)寫(xiě)入磁盤(pán)共享表空間（double write）.

l   最后再?gòu)膁ouble write buffer寫(xiě)入數(shù)據(jù)文件。

雙寫(xiě)緩沖不占用兩倍IO，double 是一個(gè)大型的連續(xù)快，會(huì)通過(guò)一次fsync()通知操作系統(tǒng)。

自使用哈希索引

InnoDB可以監(jiān)控索引的搜索，如注意到查詢(xún)可通過(guò)建立哈希索引得到優(yōu)化，就會(huì)自動(dòng)完成。通過(guò)innodb_adaptive_hash_index參數(shù)控制。

從5.7.8開(kāi)始，自適應(yīng)哈希索引搜索系統(tǒng)是分區(qū)的，每個(gè)索引綁定到一個(gè)特殊的分區(qū)上面，各個(gè)分區(qū)都有自己的鎖存器來(lái)進(jìn)行維護(hù)。分區(qū)通過(guò)innodb_adaptive_hash_index_parts參數(shù)控制，默認(rèn)值8各，最大512，通過(guò)分區(qū)設(shè)置，可降低爭(zhēng)用，提高并發(fā)。

通過(guò)show engine innodb status命令輸出的semaphores部分監(jiān)控自使用哈希索引的使用及其競(jìng)爭(zhēng)情況。如看到許多線(xiàn)程等待一個(gè)再btr0sea.c中創(chuàng)建的RW-latch,則它可能被用于禁用自適應(yīng)哈希索引。