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

一、SQL 執(zhí)行過程

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

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

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

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

配置文件中，max_connections 用于控制最大連接數(shù)，默認值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

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

存儲引擎

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

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

MyISAM：5.5 之前默認

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

二、crash-safe

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

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

redolog

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

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

binlog

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

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

兩階段提交

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

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

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

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

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

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

三、性能

日志與磁盤IO的關系

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

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

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

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

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

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

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

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

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

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

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

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

• 業(yè)務高峰
• 備庫延遲追主庫
• 恢復備份
• 批量導入

組提交

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

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

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

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

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

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

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

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

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

臟頁刷盤（InnoDB Buffer Pool)

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

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

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

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

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

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

配置文件中，innodb_io_capacity 這個參數(shù)告訴 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

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

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

• 臟頁比例
• 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 的，等后續(xù)應用 redolog 的時候，會根據(jù)LSN 的大小來判斷這個頁有沒有應用到這條 log

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

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

最新評論