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

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

一、SQL 執(zhí)行過(guò)程

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

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

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

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

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

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

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

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

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

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

優(yōu)化方向：

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

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

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

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

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

二、crash-safe

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

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

redolog

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

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

binlog

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

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

兩階段提交

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

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

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

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

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

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

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

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

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

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

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

將某個(gè)臟頁(yè)刷新到磁盤(pán)前，會(huì)先保證該臟頁(yè)對(duì)應(yīng)的 redolog 刷新到磁盤(pán)中

此外，如果遇到大事務(wù) redolog 緩存占用到了 innodb_log_buffer_size 的一半，會(huì)主動(dòng)寫(xiě)盤(pán)（只write）

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

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

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

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

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

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

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

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

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

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

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

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

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

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

這兩個(gè)參數(shù)原理是延遲 commit success 的返回，最長(zhǎng)延遲 binlog_group_commit_sync_delay 微秒，所以上面兩個(gè)參數(shù)是無(wú)損的（對(duì) crash-safe 沒(méi)有影響），但是會(huì)增加語(yǔ)句的響應(yīng)時(shí)間

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

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

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

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

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

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

可以使用用 fio 測(cè)試磁盤(pán)的 IOPS，機(jī)械硬盤(pán)建議100，SSD 建議200或更高，比如1000，最高不建議超過(guò)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í)際使用時(shí)，不可能讓磁盤(pán)一直全力去刷新，所以需要控制不同程度下動(dòng)態(tài)處理使用不同的速度百分比

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

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

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

?-- 查詢臟頁(yè)比例?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序號(hào) InnoDB 每次寫(xiě)入的日志都有一個(gè)LSN序號(hào) write pos 直接刷臟頁(yè)是不會(huì)動(dòng) redolog 的，等后續(xù)應(yīng)用 redolog 的時(shí)候，會(huì)根據(jù)LSN 的大小來(lái)判斷這個(gè)頁(yè)有沒(méi)有應(yīng)用到這條 log

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

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