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

一、SQL 執(zhí)行過程

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

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

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

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

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

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

mysql_reset_connection()

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

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

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

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

優(yōu)化方向：

• 修改 SQL
• 加索引
• 刪索引
• force index

-- 強(qiáng)制指定使用索引a
select * from t force index(a) where a between 10000 and 20000;

存儲(chǔ)引擎

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

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

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

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

二、crash-safe

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

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

redolog

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

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

binlog

MySQL 基本功能，不限存儲(chǔ)引擎，只記錄數(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，相對(duì)于內(nèi)存來說，磁盤太慢了。要提高性能就需要減少刷盤次數(shù)。

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

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

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

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

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

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

• 設(shè)置為1，binlog 在每次事務(wù)提交都直接持久化到磁盤
• 設(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 刷盤時(shí)機(jī)是在兩階段中的 prepare，commit 其實(shí)不需要刷盤(重啟后校驗(yàn) binlog 完整性)

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

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

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

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

組提交

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

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

binlog 也有組提交，不過一般用不上，因?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 的返回，最長延遲 binlog_group_commit_sync_delay 微秒，所以上面兩個(gè)參數(shù)是無損的（對(duì) crash-safe 沒有影響），但是會(huì)增加語句的響應(yīng)時(shí)間

需要注意 binlog_group_commit_sync_delay 和 binlog_group_commit_sync_no_delay_count 的邏輯先走。等到滿足了這兩個(gè)條件之一，再進(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ù)有修改時(shí)，并不會(huì)立即更新到磁盤中，而是等待特定的時(shí)機(jī)刷新。

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

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

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

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

可以使用用 fio 測(cè)試磁盤的 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í)際使用時(shí)，不可能讓磁盤一直全力去刷新，所以需要控制不同程度下動(dòng)態(tài)處理使用不同的速度百分比

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

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

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

?-- 查詢臟頁比例
?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 每次寫入的日志都有一個(gè)LSN序號(hào) write pos 直接刷臟頁是不會(huì)動(dòng) redolog 的，等后續(xù)應(yīng)用 redolog 的時(shí)候，會(huì)根據(jù)LSN 的大小來判斷這個(gè)頁有沒有應(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)容請(qǐng)搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持腳本之家！