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

本文閱讀前提：需要比較熟悉 MySQL 的基本功能，有使用 MySQL 的相關經驗

一、SQL 執行過程

MySQL 中是如何執行 SQL 的，過程如下：

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

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

連接器--客戶端與 MySQL 服務器的連接

配置文件中，max_connections 用于控制最大連接數，默認值151。

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

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

優化器選擇索引的依據

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

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

優化方向：

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

MySQL支持多種存儲引擎，同一個數據庫不同的表可以設置不同的引擎

InnoDB：5.5 后默認，支持事務，外鍵，適合大部分場景

MyISAM：5.5 之前默認

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

二、crash-safe

實現 crash-safe：MySQL 異常重啟，數據不會丟失

實現方式：WAL（預寫式日志） 技術，先寫日志再把數據寫磁盤，保證數據操作的原子性和持久性

redolog

InnoDB 存儲引擎功能，記錄的是物理日志，記錄的是數據頁的物理修改（指 InooDB 中同一數據頁在磁盤上和內存中的差異，因為是順序IO，性能會比隨機IO快，這種數據頁叫臟頁）

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

binlog

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

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

兩階段提交

redolog 保證了 MySQL 數據不丟失

binlog 用于歸檔，保證了數據庫可以備份每一次事務提交

如何保證歸檔的數據和當前的數據一致，也就是如果讓 “當前數據” 與 “備份數據” 一致或主機與從機數據一致。

MySQL 的實現方案，過程如下：

執行SQL->更新內存中的數據頁（如果沒有從磁盤中加載到內存）-> 寫入redolog(redolog prepare 狀態) -> 寫binlog->提交事務(redolog commit 狀態)

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

三、性能日志與磁盤IO的關系

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

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

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

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

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

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

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

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

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

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

配置文件中，innodb_log_buffer_size redolog 緩存大小，如果有大事務，可以設置大一些，默認16M

有時候為了提高性能，MySQL 會設置為非雙1，比如：

業務高峰備庫延遲追主庫恢復備份批量導入組提交

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

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

binlog 也有組提交，不過一般用不上，因為 redolog 的 fsync 步驟比較快，達不到積累binlog日志的效果。

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

配置文件中 binlog_group_commit_sync_delay 參數，表示延遲多少微秒后才調用 fsync;

配置文件中 binlog_group_commit_sync_no_delay_count 參數，表示累積多少次事務以后才調用 fsync。

以上兩個參數是或的關系，滿足一個就會 fsync。

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

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

臟頁刷盤（InnoDB Buffer Pool)

配置文件中，innodb_buffer_pool_size innodb 緩存大小，建議設置為設置為主機內存的60-80%

配置文件中，innodb_change_buffer_max_size 設置 change buffer 占用 buffer pool 的比例，默認是50%

InnoDB 并沒有使用操作系統中文件系統自帶的緩存，而是自己實現緩存，InnoDB 中數據以頁為單位從磁盤中加載到內存，頁的大小一般為 16 KB，為減少磁盤 IO 負擔，當內存數據頁數據有修改時，并不會立即更新到磁盤中，而是等待特定的時機刷新。

記錄內存數據頁和磁盤數據頁之間物理差異就是 redolog 的工作, 臟頁的刷新和 redolog 息息相關。

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

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

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

可以使用用 fio 測試磁盤的 IOPS，機械硬盤建議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

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

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

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

配置文件中，innodb_max_dirty_pages_pct 用于設置臟頁百分比上限，默認90，意思是 InnoDB 會避免達到90這個值，如果達到了些值 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 的，等后續應用 redolog 的時候，會根據LSN 的大小來判斷這個頁有沒有應用到這條 log

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

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