目錄一、SQL 執(zhí)行過程連接器--客戶端與 MySQL 服務(wù)器的連接優(yōu)化器--基于成本的優(yōu)化方式選擇索引，生成執(zhí)行計劃存儲引擎二、crash-saferedologbinlog兩階段提交三、性能日志與磁盤IO的關(guān)系組提交臟頁刷盤（InnoDB Buffer Pool)

本文閱讀前提：需要比較熟悉 MySQL 的基本功能，有使用 MySQL 的相關(guān)經(jīng)驗(yàn)

一、SQL 執(zhí)行過程

MySQL 中是如何執(zhí)行 SQL 的，過程如下：

連接器->(查詢緩存)->分析器->優(yōu)化器->執(zhí)行器->存儲引擎

查詢緩存模塊，8.0 中已做廢此功能。

連接器--客戶端與 MySQL 服務(wù)器的連接

配置文件中，max_connections 用于控制最大連接數(shù)，默認(rèn)值151。

需要注意的是，連接操作比較耗資源，所以一般使用長連接，不過長連接有內(nèi)存占用的問題。解決方案定時斷開或者在大查詢之后斷開重連（5.7之后可以調(diào)用 MySQL_reset_connection()，在不斷開的情況下釋放資源）

mysql_reset_connection()優(yōu)化器--基于成本的優(yōu)化方式選擇索引，生成執(zhí)行計劃

優(yōu)化器選擇索引的依據(jù)

是否排序是否使用臨時表回表開消掃描行數(shù)（估計值，采樣，索引的區(qū)分度（不同的值））

MySQL 的優(yōu)化器不是很完美，有時候需要優(yōu)化 SQL，需要用 explain 分析索引使用情況

優(yōu)化方向：

修改 SQL加索引刪索引force index-- 強(qiáng)制指定使用索引aselect * from t force index(a) where a between 10000 and 20000;存儲引擎

MySQL支持多種存儲引擎，同一個數(shù)據(jù)庫不同的表可以設(shè)置不同的引擎

InnoDB：5.5 后默認(rèn)，支持事務(wù)，外鍵，適合大部分場景

MyISAM：5.5 之前默認(rèn)

MEMORY：內(nèi)存，速度快，不能持久化，主從同步的有問題

二、crash-safe

實(shí)現(xiàn) crash-safe：MySQL 異常重啟，數(shù)據(jù)不會丟失

實(shí)現(xiàn)方式：WAL（預(yù)寫式日志） 技術(shù)，先寫日志再把數(shù)據(jù)寫磁盤，保證數(shù)據(jù)操作的原子性和持久性

redolog

InnoDB 存儲引擎功能，記錄的是物理日志，記錄的是數(shù)據(jù)頁的物理修改（指 InooDB 中同一數(shù)據(jù)頁在磁盤上和內(nèi)存中的差異，因?yàn)槭琼樞騃O，性能會比隨機(jī)IO快，這種數(shù)據(jù)頁叫臟頁）

記錄方式，循環(huán)覆寫，固定的大小，不能用于歸檔。

binlog

MySQL 基本功能，不限存儲引擎，只記錄數(shù)據(jù)的變化，是邏輯日志

歸檔日志，可用于服務(wù)器之間主從同步，備份恢復(fù)

兩階段提交

redolog 保證了 MySQL 數(shù)據(jù)不丟失

binlog 用于歸檔，保證了數(shù)據(jù)庫可以備份每一次事務(wù)提交

如何保證歸檔的數(shù)據(jù)和當(dāng)前的數(shù)據(jù)一致，也就是如果讓 “當(dāng)前數(shù)據(jù)” 與 “備份數(shù)據(jù)” 一致或主機(jī)與從機(jī)數(shù)據(jù)一致。

MySQL 的實(shí)現(xiàn)方案，過程如下：

執(zhí)行SQL->更新內(nèi)存中的數(shù)據(jù)頁（如果沒有從磁盤中加載到內(nèi)存）-> 寫入redolog(redolog prepare 狀態(tài)) -> 寫binlog->提交事務(wù)(redolog commit 狀態(tài))

寫日志執(zhí)行過程為兩階段提交 prepare->commit ，保證了數(shù)據(jù)邏輯上的一致性

三、性能日志與磁盤IO的關(guān)系

數(shù)據(jù)庫的主要性能瓶頸就是磁盤IO，相對于內(nèi)存來說，磁盤太慢了。要提高性能就需要減少刷盤次數(shù)。

配置文件中，innodb_flush_log_at_trx_commit 用于控制 redolog 日志持久化策略

設(shè)置為1，redolog 每次事務(wù)提交都直接持久化到磁盤設(shè)置為2，每次只寫到 page cache

后臺有一個線程每秒寫盤（write + fsync），刷盤的時候可以順帶把其它未提交的事務(wù)數(shù)據(jù)刷新，有數(shù)據(jù)丟失的風(fēng)險，最多丟失1秒的數(shù)據(jù)，但是IO性能會更好。

將某個臟頁刷新到磁盤前，會先保證該臟頁對應(yīng)的 redolog 刷新到磁盤中

此外，如果遇到大事務(wù) redolog 緩存占用到了 innodb_log_buffer_size 的一半，會主動寫盤（只write）

配置文件中，sync_binlog 用于控制 binlog 日志持久化策略

設(shè)置為1，binlog 在每次事務(wù)提交都直接持久化到磁盤設(shè)置為N，表示每次只 write 到文件系統(tǒng)的 page cache，N個事務(wù)后 fsync。MySQL 崩潰不會丟數(shù)據(jù)，但是系統(tǒng)斷電會丟最多N個數(shù)據(jù)，一般設(shè)置為100-1000

上面兩階段提交，需要注意的是，redolog 刷盤時機(jī)是在兩階段中的 prepare，commit 其實(shí)不需要刷盤(重啟后校驗(yàn) binlog 完整性)

正常情況下，數(shù)據(jù)庫需要保證crash-safe, 必須配置成配置雙1(innodb_flush_log_at_trx_commit=1和sync_binlog=1)，此時一個事務(wù)的提交會刷盤兩次（redolog 和 binlog）

配置文件中，innodb_log_buffer_size redolog 緩存大小，如果有大事務(wù)，可以設(shè)置大一些，默認(rèn)16M

有時候?yàn)榱颂岣咝阅埽琈ySQL 會設(shè)置為非雙1，比如：

業(yè)務(wù)高峰備庫延遲追主庫恢復(fù)備份批量導(dǎo)入組提交

在 redolog 中有 LSN 日志邏輯序列號，記錄 redolog 寫入點(diǎn)，每次遞增日志的寫入長度。通過 LSN 可以判斷，在事務(wù)提交的時候可能其它事務(wù)已經(jīng)幫你刷盤了，不需要重復(fù)刷盤。 讓 redolog 的刷盤盡量靠后，可以更好的利用組提交。

InnoDB 做了一個兩階段提交優(yōu)化，redolog 的 fsync 放在 binlog write 之后

binlog 也有組提交，不過一般用不上，因?yàn)?redolog 的 fsync 步驟比較快，達(dá)不到積累binlog日志的效果。

在 sync_binlog 設(shè)置為1時如果你想提升 binlog 組提交的效果，可以修改如下配置：

配置文件中 binlog_group_commit_sync_delay 參數(shù)，表示延遲多少微秒后才調(diào)用 fsync;

配置文件中 binlog_group_commit_sync_no_delay_count 參數(shù)，表示累積多少次事務(wù)以后才調(diào)用 fsync。

以上兩個參數(shù)是或的關(guān)系，滿足一個就會 fsync。

這兩個參數(shù)原理是延遲 commit success 的返回，最長延遲 binlog_group_commit_sync_delay 微秒，所以上面兩個參數(shù)是無損的（對 crash-safe 沒有影響），但是會增加語句的響應(yīng)時間

需要注意 binlog_group_commit_sync_delay 和 binlog_group_commit_sync_no_delay_count 的邏輯先走。等到滿足了這兩個條件之一，再進(jìn)入 sync_binlog 參數(shù)控制的階段。

臟頁刷盤（InnoDB Buffer Pool)

配置文件中，innodb_buffer_pool_size innodb 緩存大小，建議設(shè)置為設(shè)置為主機(jī)內(nèi)存的60-80%

配置文件中，innodb_change_buffer_max_size 設(shè)置 change buffer 占用 buffer pool 的比例，默認(rèn)是50%

InnoDB 并沒有使用操作系統(tǒng)中文件系統(tǒng)自帶的緩存，而是自己實(shí)現(xiàn)緩存，InnoDB 中數(shù)據(jù)以頁為單位從磁盤中加載到內(nèi)存，頁的大小一般為 16 KB，為減少磁盤 IO 負(fù)擔(dān)，當(dāng)內(nèi)存數(shù)據(jù)頁數(shù)據(jù)有修改時，并不會立即更新到磁盤中，而是等待特定的時機(jī)刷新。

記錄內(nèi)存數(shù)據(jù)頁和磁盤數(shù)據(jù)頁之間物理差異就是 redolog 的工作, 臟頁的刷新和 redolog 息息相關(guān)。

刷臟頁（這里指內(nèi)存數(shù)據(jù)頁與磁盤不一致，相反的叫干凈頁）的過程叫 flush，flush 時機(jī)有以下幾種情況：

redolog 滿了, 此時 MySQL 不能處理任何寫操作，這也是 MySQL 卡死的主要原因之一系統(tǒng)內(nèi)存不足，干凈頁/臟頁都可能釋放，如果是臟頁，會觸發(fā) flush空閑時正常開閉程序時

配置文件中，innodb_io_capacity 這個參數(shù)告訴 InnoDB 磁盤的性能，控制全力刷新磁盤時的性能，默認(rèn)200

可以使用用 fio 測試磁盤的 IOPS，機(jī)械硬盤建議100，SSD 建議200或更高，比如1000，最高不建議超過20000

?touch /tmp/test_io?fio -filename=/tmp/test_io -direct=1 -iodepth 1 -thread -rw=randrw -ioengine=psync -bs=16k -size=500M -numjobs=10 -runtime=10 -group_reporting -name=mytest

實(shí)際使用時，不可能讓磁盤一直全力去刷新，所以需要控制不同程度下動態(tài)處理使用不同的速度百分比

動態(tài)處理由兩個因素決定，兩個維度取更大的值刷新

臟頁比例redolog 生成速度（checkpoint 和 write pos差值）

配置文件中，innodb_max_dirty_pages_pct 用于設(shè)置臟頁百分比上限，默認(rèn)90，意思是 InnoDB 會避免達(dá)到90這個值，如果達(dá)到了些值 InnoDB 會全力刷新

?-- 查詢臟頁比例?select VARIABLE_VALUE into @a from `performance_schema`.global_status where VARIABLE_NAME = 'innodb_buffer_pool_pages_dirty';?select VARIABLE_VALUE into @b from `performance_schema`.global_status where VARIABLE_NAME = 'innodb_buffer_pool_pages_total';?select @a/@b;

checkpoint 是 redolog 中記錄要擦除的LSN序號 InnoDB 每次寫入的日志都有一個LSN序號 write pos 直接刷臟頁是不會動 redolog 的，等后續(xù)應(yīng)用 redolog 的時候，會根據(jù)LSN 的大小來判斷這個頁有沒有應(yīng)用到這條 log

配置文件中，innodb_flush_neighbors 表示如果臟頁旁邊也是臟頁，是否一起flush，用于機(jī)械盤設(shè)置為1，用于 ssd 建議設(shè)置為0（MySQL8的默認(rèn)值）

到此這篇關(guān)于MySQL高效可靠處理持久化數(shù)據(jù)的教程指南的文章就介紹到這了,更多相關(guān)MySQL處理持久化數(shù)據(jù)內(nèi)容請搜索好吧啦網(wǎng)以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持好吧啦網(wǎng)！