系列
MySQL實(shí)戰(zhàn)45講閱讀筆記-MySQL入門
MySQL實(shí)戰(zhàn)45講閱讀筆記-日志
MySQL實(shí)戰(zhàn)45講閱讀筆記-鎖
MySQL實(shí)戰(zhàn)45講閱讀筆記-索引
MySQL實(shí)戰(zhàn)45講閱讀筆記-MVCC
日志系統(tǒng)在任何系統(tǒng)都是很重要的，MySQL也不例外；MySQL日志系統(tǒng)主要分為下面這幾類
- 錯誤日志(error log)
- 通用日志(general log)
- 慢查詢?nèi)罩?slow query log)
- 重做日志(redo log)
- 二進(jìn)制日志(bin log)
- 回滾日志(undo log)
其中redolog和undolog是屬于innodb引擎的專有日志，其他的都是MySQL級別的日志；下面是各個日志的功能和基本用法
錯誤日志(Error Log)
錯誤日志是默認(rèn)開啟的，在配置文件中log-error=/var/log/mysqld.log可以看到日志所在位置，且是不允許關(guān)閉的；
一般是記錄在MySQL啟動和關(guān)閉時一些日志信息和運(yùn)行過程中比較嚴(yán)重的錯誤日志，但不光只有錯誤日志還有一些其他的信息如innodb引擎狀態(tài)的改變信息等；
可以使用下面的語句
mysqladmin flush-logs -u[username] -p
或登陸到mysql客戶端里面flush logs來刷新日志，在5.5.7之前的版本使用該命令后會自動備份一份錯誤日志文件名+_old的新文件后重新創(chuàng)建一個新的錯誤日志，但是在5.7.25的版本使用這個命令貌似不會刷新日志文件；
通用日志(General Log)
通用日志記錄了所有的SQL語句，默認(rèn)是不開啟的，因?yàn)榫€上可能會造成大量的日志記錄，可以通過在配置文件添加general_log=1|0或者在客戶端運(yùn)行SET [GLOBAL] general_log=[ON|OFF]來開啟或者關(guān)閉；雖然不推薦一直打開，但是可以通過短期的開啟來查找高峰期內(nèi)哪些SQL語句使用頻率最頻繁，同時General log也會記錄所有的DDL語句；
慢查詢?nèi)罩?Slow Query Log)
顧名思義就是用來記錄所有執(zhí)行時間大于指定時間的SQL語句，可以通過在配置文件添加slow_query_log=on或者在客戶端使用SET GLOBAL slow_query_log = on來開啟，同時參數(shù)long_query_time是用來控制是否記錄到日志里面的臨界值，比如SET long_query_time = 1后所有時間大于1s的語句都會記錄下來；同樣開啟慢查詢?nèi)罩緯硪稽c(diǎn)的性能損耗，但是無傷大雅，可以選擇在對數(shù)據(jù)庫或者程序優(yōu)化期間打開一段時間；
- 相關(guān)參數(shù)
show global variables like '%log%'
----------------------------------------
slow_query_log ON #是否開啟狀態(tài)
slow_query_log_file /var/lib/mysql/VM_16_3_centos-slow.log #日志位置
log_queries_not_using_indexes ON #記錄沒有使用索引的SQL語句
long_query_time 1 #大于該時間都需要記錄
----------------------------------------
RedoLog
Innodb的WAL機(jī)制核心就是日志先行，更新任何數(shù)據(jù)前必須先在日志記錄，這是保證Innodb在宕機(jī)后可以恢復(fù)沒有落盤的數(shù)據(jù)，實(shí)現(xiàn)事務(wù)的持久性；redo log默認(rèn)在磁盤上由兩個名為ib_logfile0和ib_logfile1(默認(rèn)是兩個)的文件物理表示，可以通過修改innodb_log_files_in_group 調(diào)整redolog文件的個數(shù)，innodb_log_file_size控制著每個file文件的大??；
redolog寫文件是從頭開始寫到末尾然后又回到頭部循環(huán)寫，write pos是當(dāng)前寫的位置，checkpoint是當(dāng)前需要擦除的位置，兩個之間的距離表示可供寫入的大??；
為什么需要Redo log
Innodb buffer pool內(nèi)部儲存的是數(shù)據(jù)頁，當(dāng)修改innodb表上某行數(shù)據(jù)時，如果該行不在內(nèi)存中則需要將該行所在的數(shù)據(jù)頁從磁盤讀入到內(nèi)存去，然后在內(nèi)存中更新該行，現(xiàn)在內(nèi)存中的數(shù)據(jù)頁與磁盤中就不一致了，把這種內(nèi)存數(shù)據(jù)頁與磁盤數(shù)據(jù)頁不一致的數(shù)據(jù)頁稱為臟頁（dirty page），DB需要把臟頁數(shù)據(jù)寫入磁盤，但是如果每一次更新數(shù)據(jù)就會帶來一次磁盤操作的話那么機(jī)器肯定撐不住；所以說mysql會把標(biāo)記為臟頁的數(shù)據(jù)頁儲存在一個flush list里面，用一個專門的后臺線程定時刷臟；那么如果在mysql還沒有刷臟的時候數(shù)據(jù)庫掛了怎么辦呢，所以就需要redo log；
Redo Log的意義
事務(wù)將數(shù)據(jù)的變更寫到內(nèi)存頁中同時記錄到redolog里面，然后提交就算這個事務(wù)已經(jīng)結(jié)束，期間只有一次磁盤IO，效率遠(yuǎn)比比事務(wù)將數(shù)據(jù)直接寫到磁盤里面要高的多，并且redolog寫到磁盤可以看作是順序IO，所以redolog能提升性能的一個方面就在于將對磁盤的隨機(jī)IO寫轉(zhuǎn)換成順序IO；且事務(wù)因?yàn)殄礄C(jī)可能會丟失，redolog還有一個作用就是保證事務(wù)的持久性；
內(nèi)存頁中數(shù)據(jù)和磁盤的不一致，把這種數(shù)據(jù)頁稱為臟頁，反之稱為干凈頁，把內(nèi)存數(shù)據(jù)寫入磁盤的操作稱為刷臟頁(flush)；
-
Innodb的刷臟策略
Innodb的刷臟速度是根據(jù)臟頁比例和redolog的寫盤速度來決定的，innodb_max_dirty_pages_pct參數(shù)表示innodb最大的臟頁比例
innodb_max_dirty_pages_pct 75.00 #默認(rèn)比例為75%
innodb會根據(jù)當(dāng)前的臟頁比例和innodb_max_dirty_pages_pct計(jì)算出一個因子A；
innodb每次寫入日志都會有一個序號，當(dāng)前的序號和checkpoint對應(yīng)的序號（LSN）之間差值記做N，然后通過N計(jì)算出因子B；
取A和B之間的最大值，最后根據(jù)innodb_io_capacity來計(jì)算出刷臟速度；
innodb_io_capacity是用來調(diào)整刷臟頁數(shù)據(jù)的參數(shù)，默認(rèn)為200單位是頁，可以設(shè)置為當(dāng)前機(jī)器的IOPS；
刷臟頁次數(shù)太頻繁的話可能會造成原本很快的SQL語句速度降低，還有SQL語句需要等待刷臟結(jié)束后才能執(zhí)行的也是影響的一大因素，比如redolog寫滿了需要將checkpoint往前推，就必須要進(jìn)行flush才能執(zhí)行更新語句；所以需要合理的設(shè)置innodb_io_capacity，可以通過觀察臟頁比例來調(diào)整
select VARIABLE_VALUE into @a from global_status where VARIABLE_NAME = 'Innodb_buffer_pool_pages_dirty';
select VARIABLE_VALUE into @b from global_status where VARIABLE_NAME = 'Innodb_buffer_pool_pages_total';
select @a/@b;
這個臟頁比例最好經(jīng)常接近設(shè)置的innodb_max_dirty_pages_pct值；
redo log的寫入機(jī)制
redo log寫入前是先寫入到redo log buffer，buffer中的內(nèi)容是不會持久化到磁盤中，如果寫buffer的過程中異常退出了會丟失這部分日志，但是這部分的事務(wù)是沒有提交的所有丟失的影響也不大；
innodb_flush_log_at_trx_commit參數(shù)控制著redo log的寫入策略：
- 0： 表示每次事務(wù)提交時都只是把 redo log 留在 redo log buffer中，然后每秒后臺線程使用fsync寫入磁盤；
- 1：表示每次事務(wù)提交時都使用fsync將 redo log 直接持久化到磁盤；
- 2：表示每次事務(wù)提交時都只是把 redo log 寫到 page cache中，然后每秒后臺線程使用fsync寫入磁盤；
Innodb中有一個后臺線程每隔1s會把redo log buffer中的日志write到文件系統(tǒng)的page cache中，然后使用fsync持久化到磁盤里面；
所有線程都共用一個buffer，這表示其他線程可能會把當(dāng)前線程沒寫完的日志一起fsync到磁盤中；如果buffer占用的空間達(dá)到innodb_log_buffer_size的一半時后臺線程同樣也會把buffer內(nèi)容寫到page cache里面；
innodb_flush_neighbors
當(dāng)刷一個臟頁的時候發(fā)現(xiàn)旁邊的數(shù)據(jù)頁同樣是一個臟頁，那么innodb就會順帶把旁邊一起刷了，并且還會檢測旁邊的旁邊是不是臟頁。。。默認(rèn)值為1就是啟用這種機(jī)制，0的話表示只刷自己的；
這種機(jī)制的意義在于將隨機(jī)IO轉(zhuǎn)換成順序IO，但是對于SSD來說提升性能的意義可能沒有機(jī)械硬盤大；
BinLog
Binlog是一種二進(jìn)制日志，儲存MySQL的所有DDL和DML語句(除select語句之外)，可以說數(shù)據(jù)備份和主備同步就是依賴binlog來實(shí)現(xiàn)的；Redo log屬于innodb引擎才有的日志，而binlog是mysql server層持有的二進(jìn)制日志，屬于邏輯日志；
Redolog是循環(huán)寫，空間是固定的，而binlog則是寫完一個文件后可以切換到下一個文件繼續(xù)寫下去，沒有大小的限制(受限于磁盤空間)，單個binlog文件大小由參數(shù)max_binlog_size控制；
啟用binlog需要在配置文件加上
log-bin=mysql-bin
binlog_format=row|statement|mixed
server-id=1
binlog_format共有三種模式：
row - 記錄被修改行的所有數(shù)據(jù)
- 優(yōu)點(diǎn)：能夠保證在復(fù)制過程中數(shù)據(jù)的準(zhǔn)確性
- 缺點(diǎn)：會產(chǎn)生大量的日志數(shù)據(jù)，尤其是alter table的時候會讓日志暴漲
SET @@session.character_set_client=33,@@session.collation_connection=33,@@session.collation_server=8;
BEGIN;
#Table_map: `Test`.`ss` mapped to number 113
#Write_rows: table id 113 flags: STMT_END_F
BINLOG '
F7UZXRMBAAAALgAAAJgMAAAAAHEAAAAAAAEABFRlc3QAAnNzAAIDAwACngizFg==
F7UZXR4BAAAALAAAAMQMAAAAAHEAAAAAAAEAAgAC//wDAAAAAQAAAMcZh7k='/*!*/;
### INSERT INTO `Test`.`ss`
### SET
### @1=3 /* INT meta=0 nullable=0 is_null=0 */
### @2=1 /* INT meta=0 nullable=1 is_null=0 */
#Xid = 159
COMMIT;
statement - 每一條被修改數(shù)據(jù)的sql都會記錄到master的bin-log中，slave在復(fù)制的時候sql會解析成和原來master端執(zhí)行過的相同的sql再次執(zhí)行;
- 優(yōu)點(diǎn)：不需要記錄每一條SQL語句與每行的數(shù)據(jù)變化，這樣子binlog的日志也會比較少，減少了磁盤IO，提高性能。
- 缺點(diǎn)：在某些情況下會導(dǎo)致master-slave中的數(shù)據(jù)不一致(觸發(fā)器，一些函數(shù)如sleep()等會出現(xiàn)問題
SET @@SESSION.GTID_NEXT= 'ANONYMOUS'/*!*/;
#Query thread_id=7 exec_time=0 error_code=0
SET TIMESTAMP=1561965696/*!*/;
BEGIN/;
#Intvar
SET INSERT_ID=1/*!*/;
#Query thread_id=7 exec_time=0 error_code=0
SET TIMESTAMP=1561965696/*!*/;
insert into ss(`value`) values(1)/;
#Xid = 129
COMMIT/*!*/;
mixed - 混合模式，即是row和statement模式的混合，對于可能會產(chǎn)生復(fù)制數(shù)據(jù)不一致的SQL會以row模式記錄，反之則用statement模式；
binlog的寫入機(jī)制
事務(wù)執(zhí)行過程中先把日志寫到binlog cache中，事務(wù)提交的時候再把binlog cache寫到binlog文件中；一個事務(wù)的binlog是不可能拆開寫的，所以無論事務(wù)多大也要保證一次性寫入，如果單個事務(wù)過大，可能會造成一個binlog文件寫不下需要跨文件處理，這是非常影響性能的；
MySQL給每個線程分配一塊binlog cache，大小由參數(shù)binlog_cache_size控制，如果事務(wù)大過這塊內(nèi)存，則會存在磁盤的臨時文件中；
雖然每個線程都有自己的cache，但是是共用一份binlog文件，當(dāng)事務(wù)commit的時候會把cache/臨時文件中的內(nèi)容寫(write)到文件系統(tǒng)的page cache中，文件系統(tǒng)再通過fsync持久化到磁盤中；
- sync_binlog
0：表示每次提交事務(wù)只write不fsync，性能最好；
1：表示每次提交事務(wù)都會write和fsync，性能最不好但是安全性最高；
N(>1)：表示每次提交事務(wù)都write，但是積累到n個事務(wù)才fsync；
sync_binlog和innodb_flush_log_at_trx_commit都設(shè)置為1是保證crash safe的基礎(chǔ)要求，俗稱雙1配置；
當(dāng)sync_binlog設(shè)置為1的時候每次事務(wù)提交都會經(jīng)歷redolog和binlog的刷盤，意味著對TPS性能造成極大的影響，MySQL有個叫做組提交的機(jī)制來優(yōu)化binlog和redolog的提交；
組提交(group commit)
組提交的基本思想是盡量積攢足夠多的日志然后一起fsync；
在redolog的prepare階段把日志從redolog buffer中寫入到fs page cache后不是立即使用fsync刷盤，而是等待binlog write之后再進(jìn)行redolog的fsync；在并發(fā)條件下可能在binlog-write或者redolog prepare-fsync階段積攢更多的redolog和binlog日志一起fsync，從而提升性能；相關(guān)參數(shù)
binlog_group_commit_sync_delay：表示binlog事務(wù)提交后等待延遲多少時間才fsync，默認(rèn)0，單位為微秒；
binlog_group_commit_sync_no_delay_count：表示binlog事務(wù)要積攢多少個才fsync，必須要上面不為0才有效；
兩者是或的關(guān)系，只要有一條滿足就會使用fsync寫到磁盤；為什么WAL機(jī)制能提升性能
redo log和binlog能提升性能的地方在于把大量的隨機(jī)寫轉(zhuǎn)成順序?qū)?，且還有組提交優(yōu)化能有效降低磁盤的IOPS消耗；為什么redolog buffer是全局的但是binlog cache是每個線程自己維護(hù)的
因?yàn)闉榱吮ＷCbinlog事務(wù)的完整性，一個事務(wù)在binlog是連續(xù)寫的，等整個事務(wù)完成以后再寫入文件，而redolog buffer里面事務(wù)沒有這個要求，相反如果并行事務(wù)提交時是允許該事務(wù)在redolog buffer中只寫入一半就可以被其他事務(wù)順帶寫入磁盤中；
UndoLog
Undo log的存在保證了事務(wù)的原子性，MVCC就是依賴它來實(shí)現(xiàn)，當(dāng)對任何行做了修改的時候都會在undo log里面記錄，大量的undo log構(gòu)成行的歷史版本記錄，在需要的時候可以回退(rollback)到任何版本；
- 儲存空間
innodb_undo_tablespaces參數(shù)控制undo log日志獨(dú)立表空間的數(shù)量，取值范圍0-128，0表示不啟用獨(dú)立的undo log表空間，而是儲存在ibdata文件中，一旦實(shí)例創(chuàng)建后就不能再修改這個配置；啟用后每個文件默認(rèn)大小為10M；
