MySQL的基礎架構與日誌系統實例分析

一、MySQL基礎架構

# MySQL可以分成Server層與儲存引擎層兩部分

Server層包括連接器、查詢快取、分析器、最佳化器、執行器等，涵蓋MySQL的大多數核心服務功能，以及所有的內建函數（如日期、時間、數學和加密函數等），所有跨儲存引擎的功能都在這一層實現，例如預存程序、觸發器、視圖等

儲存引擎負責資料的儲存和提取。多個儲存引擎（如InnoDB、MyISAM和Memory）都能被支持，這是基於插件的架構模式實現的。 InnoDB是目前最常使用的儲存引擎，自MySQL 5.5.5版本起成為預設儲存引擎。指定記憶體引擎執行的方法是在SQL語句中使用engin=memory

不同的儲存引擎共用一個Server層

1、連接器

連接器負責跟隨客戶端建立連線、取得權限、維持和管理連線。連線指令一般是：

mysql -h$ip -P$port -u$user -p

連線指令中的mysql是客戶端工具，用來跟服務端建立連線。在完成TCP握手後，連接器就要開始認證身份

• 如果用戶名或密碼不對，就會收到一個"Access denied for user"的錯誤，然後客戶端程序結束執行

• 連接器會在驗證通過使用者名稱密碼後，從權限表中檢索你擁有的權限。之後，這個連接裡面的權限判斷邏輯，都將依賴於此時讀到的權限

這就意味著，一個用戶成功建立連接後，即使用管理員帳號對這個使用者的權限做了修改，也不會影響已經存在連線的權限。修改完成後，只有再新建的連線才會使用新的權限設定

連線完成後，如果你沒有後續的動作，這個連線就處於空閒狀態，可以在show processlist指令中看到它

Command為Sleep表示此連線是閒置連線

客戶端如果太久沒動靜，連接器就會自動將它中斷。這個時間是由參數wait_timeout控制的。預設值是8小時

如果在連線被斷開之後，客戶端再次發送請求的話，就會收到一個錯誤提示：Lost connection to MySQL server during query。這時候就需要重新連接，然後在執行請求了

當客戶端持續發送請求時，長連接指的是與資料庫建立連接後一直使用同一個連接。短連接則是指每次執行完很少的幾次查詢就斷開連接，下次查詢再重新建立一個

建立連接的過程通常是比較複雜的，所以建議盡量使用長連接

但全部使用長連線後，有些時候MySQL佔用記憶體漲得特別快，這是因為MySQL在執行過程中臨時使用的記憶體是管理在連線物件裡面的。這些資源會在連線中斷的時候才會被釋放。所以如果長連線累計下來，可能導致記憶體佔用太大，被系統強行殺掉（OOM），從現像看就是MySQL異常重啟了

#可以透過以下兩種方案解決這個問題：

1.定期斷開長連線。根據程式內部判斷，如果執行了一個佔用記憶體較大的查詢，則在一定時間後斷開連線。如果需要再次查詢，則需要重新建立連線

2.如果使用的是MySQL5.7或更新版本，可以在每次執行一個比較大的操作後，透過執行mysql_reset_connection來重新初始化連線資源。重新表述：雖然這個過程不需要重新連線或驗證權限，但會將連線狀態還原為建立初始狀態

#2、查詢快取

建立連線完成後，可以執行select語句了。 MySQL拿到一個查詢請求後，會先到查詢快取看看，之前是不是執行過這條語句。先前執行的語句及其結果可能以鍵值對形式直接緩存在記憶體中。 key是查詢的語句，value是查詢的結果。如果在快取中查找到了key，那麼對應的value會直接回傳給客戶端

如果語句不在查詢快取中，就會繼續後面的執行階段。執行完成後，執行結果會存入查詢快取。如果查詢命中緩存，MySQL不需要執行後面的複雜操作，就可以直接返回結果，這個效率很高

但是大多數情況下不建議使用查詢緩存，因為查詢緩存的失效非常頻繁，只要一個表格的更新，這個表上所有的查詢快取都會被清空。對於更新壓力大的資料庫來說，查詢快取的命中率會非常低

可以将参数query_cache_type设置成DEMAND，这样对于默认的SQL语句都不使用查询缓存。而对于确定要是查询缓存的语句，可以用SQL_CACHE显示指定，如下面这条语句一样：

select SQL_CACHE * from T where ID=10；

MySQL8.0版本直接将查询缓存的整块功能删掉了

3、分析器

如果没有命中查询缓存，就要开始真正执行语句了。MySQL首先要对SQL语句做解析

分析器会先做词法分析。MySQL需要识别一条由多个字符串和空格组成的SQL语句，将其中的每个字符串识别并理解其代表的意义

select * from T where ID=10；

当输入select关键字时，MySQL会识别它为一个查询语句。它也要把字符串T识别成表名T，把字符串ID识别成列ID

做完了这些识别以后，就要做语法分析。基于词法分析的结果，语法分析器会依据MySQL语法规则来检查这个SQL语句是否符合规范。如果语法不对，就会收到"You have an error in your SQL syntax"的错误提示

4、优化器

经过了分析器，在开始执行之前，还要先经过优化器的处理

优化器是在表里面有多个索引的时候，决定使用哪个索引；或者在一个语句有多表关联的时候，决定各个表的连接顺序

5、执行器

优化器阶段完成后，这个语句的执行方案就确定下来了，然后进入执行器阶段，开始执行语句

开始执行的时候，要先判断一下你对这个表T有没有执行查询的权限，如果没有，就会返回没有权限的错误，如下所示

mysql> select * from T where ID=10;
ERROR 1142 (42000): SELECT command denied to user 'b'@'localhost' for table 'T'

如果有权限，就打开表继续执行。打开表的时候，执行器就会根据表的引擎定义，去使用这个引擎提供的接口

比如在表T中，ID字段没有索引，那么执行器的执行流程是这样的：

1.调用InnoDB引擎接口取这个表的第一行，判断ID值是不是10，如果不是则跳过，如果是则将这个行存在结果集中

2.调用引擎接口取下一行，重复相同的判断逻辑，直到取到这个表的最后一行

3.执行器将上述遍历过程中所有满足条件的行组成的记录集作为结果集返回给客户端

在慢查询日志中可以查看到一个名为rows_examined的字段，其表示执行该语句所扫描的行数。这个值就是在执行器每次调用引擎获取数据行的时候累加的

在有些场景下，执行器调用一次，在引起内部则扫描了多行，因此引擎扫描行数跟rows_examined并不是完全相同的

二、日志系统

表T的创建语句如下，这个表有一个主键ID和一个整型字段c：

create table T(ID int primary key, c int);

如果要将ID=2这一行的值加1，SQL语句如下：

update T set c=c+1 where ID=2;

1、redo log（重做日志）

在MySQL中，如果每次的更新操作都需要写进磁盘，然后磁盘也要找到对应的那条记录，然后再更新，整个过程IO成本、查找成本都很高。MySQL里常说的WAL技术，全称是Write-Ahead Logging，它的关键点就是先写日志，再写磁盘

当有一条记录需要更新的时候，InnoDB引擎就会把记录写到redo log里面，并更新buffer pool的page，这个时候更新就算完成了

buffer pool是物理页的缓存，对InnoDB的任何修改操作都会首先在buffer pool的page上进行，然后这样的页面将被标记为脏页并被放到专门的flush list上，后续将由专门的刷脏线程阶段性的将这些页面写入磁盘

InnoDB的redo log是固定大小的，比如可以配置为一组4个文件，每个文件的大小是1GB，从头开始写，写到末尾就又回到开头循环写

write pos是当前记录的位置，一边写一边后移，写到第3号文件末尾后就回到0号文件开头。check point是当前要擦除的位置，也是往后推移并且循环的，擦除记录前要把记录更新到数据文件

write pos和check point之间空着的部分，可以用来记录新的操作。如果write pos追上check point，这时候不能再执行新的更新，需要停下来擦掉一些记录，把check point推进一下

有了redo log，InnoDB就可以保证即使数据库发生异常重启，之前提交的记录都不会丢失，这个能力称为crash-safe

2、binlog（归档日志）

MySQL整体来看就有两块：一块是Server层，主要做的是MySQL功能层面的事情；还有一块是引擎层，负责存储相关的具体事宜。InnoDB引擎拥有一种特定的日志称为redo log，而Server层也有其自己的日志，被称为binlog

为什么会有两份日志？

因為最開始MySQL裡並沒有InnoDB引擎。 MySQL自帶的引擎是MyISAM，但是MyISAM沒有crash-safe的能力，binlog日誌只能用於歸檔。而InnoDB是以插件形式引入MySQL的，既然只依賴binlog是沒有crash-safe能力的，所以InnoDB使用redo log來實作crash-safe能力

##binlog的日誌格式：

1）、STATEMENT模式

#2 ）、ROW模式

3）、MIXED模式

3、redo log和binlog日誌的不同

4.兩階段提交

執行器和InnoDB引擎在執行這個update語句時的內部流程：##1.執行器先找到引擎取ID =2這一行。 ID是主鍵，引擎直接用樹搜尋找到這一行。如果ID=2這一行所在的資料也本來就在內存中，就直接返回給執行器；否則，需要先從磁碟讀入內存，然後再返回

2.執行器拿到引擎給的行數據，把這個值加上1，得到新的一行數據，再調用引擎介面寫入這行新數據

3.引擎將這行新數據更新到記憶體中，同時將這個更新操作記錄到redo log裡面，此時redo log處於prepare狀態。然後告知執行器執行完成了，隨時可以提交事務

4.執行器產生這個操作的binlog，並把binlog寫入磁碟

5.執行器呼叫引擎的提交事務接口，引擎把剛剛寫入的redo log改成提交狀態，更新完成

update語句的執行流程圖如下，圖中淺色框表示在InnoDB內部執行的，深色框表示是在執行器中執行的

將redo log的寫入拆成了兩個步驟：prepare和commit，這就是兩階段提交

由於redo log和binlog是兩個獨立的邏輯，如果不用兩階段提交，要嘛就是先寫完redo log再寫binlog，或是先寫完binlog再寫redo log

1.先寫完redo log再寫binlog 。如果在redo log寫完，binlog還沒寫完的時候，MySQL進程異常重新啟動。由於redo log寫完之後，系統即使崩潰，仍然能夠把資料恢復回來，所以恢復後這一行c的值是1。但由於binlog還沒寫完就crash了，這時候binlog裡面就沒有記錄這個語句，binlog中記錄的這一行c的值為0

2.先寫binlog後寫redo log。如果在binlog寫完之後crash，由於redo log還沒寫，崩潰恢復以後這個事務無效，所以這一行的c的值是0。但是binlog裡面已經記錄把c從0改成1這個日誌了。因此，在使用binlog復原時，將多出一個事務，最終復原出的第c列的值將為1

#如果沒有使用兩階段提交，資料庫狀​​態可能與使用其日誌復原的庫狀態不一致。 redo log和binlog都可以用來表示事務的提交狀態，而兩階段提交就是讓這兩個狀態保持邏輯上的一致

redo log用來保證crash-safe能力。 innodb_flush_log_at_trx_commit這個參數設定成1的時候，表示每次事務的redo log都直接持久化到磁盤，這樣可以保證MySQL異常重啟之後資料不遺失

sync_binlog這個參數設定成1的時候，表示每次事務的binlog都持久化到磁碟，這樣可以保證MySQL異常重啟之後binlog不遺失

三、MySQL刷脏页

1、刷脏页的场景

当内存数据页跟磁盘数据页不一致的时候，我们称这个内存页为脏页。当内存数据被写入磁盘后，内存和磁盘上的数据页就会保持一致，这种状态被称为“干净页”

• 第一种场景是，InnoDB的redo log写满了，这时候系统会停止所有更新操作，把checkpoint往前推进，redo log留出空间可以继续写
  
  checkpoint位置从CP推进到CP’，就需要将两个点之间的日志对应的所有脏页都flush到磁盘上。之后，上图中从write pos到CP’之间就是可以再写入的redo log的区域

• 第二种场景是，系统内存不足。当内存空间不足以分配新的内存页时，系统会选择淘汰一些数据页来腾出内存空间以供其他数据页使用。如果淘汰的是脏页，就要先将脏页写到磁盘

这时候不能直接把内存淘汰掉，下次需要请求的时候，从磁盘读入数据页，然后拿redo log出来应用不就行了？

这里是从性能考虑的。如果刷脏页一定会写盘，就保证了每个数据页有两种状态：一种是内存里存在，内存里就肯定是正确的结果，直接返回；另一种是内存里没有数据，就可以肯定数据文件上是正确的结果，读入内存后返回。这样的效率最高

• 第三种场景是，MySQL认为系统空闲的时候刷脏页，当然在系统忙的时候也要找时间刷一点脏页

• 第四种场景是，MySQL正常关闭的时候会把内存的脏页都flush到磁盘上，这样下次MySQL启动的时候，就可以直接从磁盘上读数据，启动速度会很快

redo log写满了，要flush脏页，出现这种情况的时候，整个系统就不能再接受更新了，所有的更新都必须堵住

内存不够用了，要先将脏页写到磁盘，这种情况是常态。InnoDB用缓冲池管理内存，缓冲池中的内存页有三种状态：

• 第一种是还没有使用的

• 第二种是使用了并且是干净页

• 第三种是使用了并且是脏页

InnoDB的策略是尽量使用内存，因此对于一个长时间运行的库来说，未被使用的页面很少

如果要读取的数据页不在内存中，则需要从缓冲池中请求一个数据页。这时候只能把最久不使用的数据页从内存中淘汰掉：如果要淘汰的是一个干净页，就直接释放出来复用；但如果是脏页，即必须将脏页先刷到磁盘，变成干净页后才能复用

刷页虽然是常态，但是出现以下两种情况，都是会明显影响性能的：

• 一个查询要淘汰的脏页个数太多，会导致查询的响应时间明显变长

• 日志写满，更新全部堵住，写性能跌为0，这种情况对敏感业务来说，是不能接受的

2、InnoDB刷脏页的控制策略

首先，要正确地告诉InnoDB所在主机的IO能力，这样InnoDB才能知道需要全力刷脏页的时候，可以刷多快。参数为innodb_io_capacity，建议设置成磁盘的IOPS

考虑到脏页比例和redo log写入速度，InnoDB的刷盘速度得到优化。默认值为75％，参数innodb_max_dirty_pages_pct限制了脏页的比例。脏页比例是通过Innodb_buffer_pool_pages_dirty/Innodb_buffer_pool_pages_total得到的，SQL语句如下：

mysql>  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;

四、日志相关问题

问题一：在两阶段提交的不同时刻，MySQL异常重启会出现什么现象

如果在图中时刻A的地方，也就是写入redo log处于prepare阶段之后、写binlog之前，发生了崩溃，由于此时binlog还没写，redo log也还没提交，所以崩溃恢复的时候，这个事务会回滚。这时候，binlog还没写，所以也不会传到备库

如果在图中时刻B的地方，也就是binlog写完，redo log还没commit前发生崩溃，那崩溃恢复的时候MySQL怎么处理？

崩溃恢复时的判断规则：

1）如果redo log里面的事务是完整的，也就是已经有了commit标识，则直接提交

2）如果redo log里面的事务只有完整的prepare，则判断对应的事务binlog是否存在并完整

a.如果完整，则提交事务

b.否则，回滚事务

时刻B发生崩溃对应的就是2(a)的情况，崩溃恢复过程中事务会被提交

问题二：MySQL怎么知道binlog是完整的？

一个事务的binlog是有完整格式的：

• statement格式的binlog，最后会有COMMIT

• row格式的binlog，最后会有一个XID event

问题三：redo log和binlog是怎么关联起来的？

它们有一个共同的数据字段，叫XID。崩溃恢复的时候，会按顺序扫描redo log：

• 如果碰到既有prepare、又有commit的redo log，就直接提交

• 如果碰到只有prepare、而没有commit的redo log，就拿着XID去binlog找对应的事务

问题四：redo log一般设置多大？

如果是现在常见的几个TB的磁盘的话，redo log设置为4个文件、每个文件1GB

问题五：正常运行中的实例，数据写入后的最终落盘，是从redo log更新过来的还是从buffer pool更新过来的呢？

redo log并没有记录数据页的完整数据，所以它并没有能力自己去更新磁盘数据页，也就不存在数据最终落盘是由redo log更新过去的情况

脏页是指在正常运行的实例中，当数据页被修改后，与存储在磁盘上的数据页不一致。最终数据落盘，就是把内存中的数据页写盘。这个过程，甚至与redo log毫无关系

2.在崩溃恢复场景中，InnoDB如果判断到一个数据页可能在崩溃恢复的时候丢失了更新，就会将它对到内存，然后让redo log更新内存内容。更新完成后，内存页变成脏页，就回到了第一种情况的状态

问题六：redo log buffer是什么？是先修改内存，还是先写redo log文件？

在一个事务的更新过程中，日志是要写多次的。比如下面这个事务：

begin;insert into t1 ...insert into t2 ...commit;

这个事务要往两个表中插入记录，插入数据的过程中，生成的日志都得先保存起来，但又不能在还没commit的时候就直接写到redo log文件里

所以，redo log buffer就是一块内存，用来先存redo日志的。也就是说，在执行第一个insert的时候，数据的内存被修改了，redo log buffer也写入了日志。在执行commit语句时，才真正将日志写入redo log文件

五、MySQL是怎么保证数据不丢的？

只要redo log和binlog保证持久化到磁盘，就能确保MySQL异常重启后，数据可以恢复

1、binlog的写入机制

事务执行过程中，先把日志写到binlog cache，事务提交的时候，再把binlog cache写到binlog文件中。无法分割一个事务的binlog，因此无论该事务有多大，都必须确保一次性写入

系统给binlog cache分配了一片内存，每个线程一个，参数binlog_cache_size用于控制单个线程内binlog cache所占内存的大小。如果超过了这个参数规定的大小，就要暂存到磁盘

事务提交的时候，执行器把binlog cache里的完整事务写入到binlog中，并清空binlog cache

每个线程有自己binlog cache，但是共用一份binlog文件

• 图中的write，指的就是把日志写入到文件系统的page cache，并没有把数据持久化到磁盘，所以速度比较快

• 图中的fsync，才是将数据持久化到磁盘的操作。一般情况下认为fsync才占磁盘的IOPS

write和fsync的时机，是由参数sync_binlog控制的：

• sync_binlog=0的时候，表示每次提交事务都只write，不fsync

• sync_binlog=1的时候，表示每次提交事务都会执行fsync

• sync_binlog=N（N>1）的时候，表示每次提交事务都write，但累积N个事务后才fsync

因此，在出现IO瓶颈的场景中，将sync_binlog设置成一个比较大的值，可以提升性能，对应的风险是：如果主机发生异常重启，会丢失最近N个事务的binlog日志

2、redo log的写入机制

在执行事务过程中所产生的 redo log 需要先写入 redo log 缓存。redo log buffer里面的内容不是每次生成后都要直接持久化到磁盘，也有可能在事务还没提交的时候，redo log buffer中的部分日志被持久化到磁盘

redo log可能存在三种状态，对应下图的三个颜色块

这三张状态分别是：

• 存在redo log buffer中，物理上是在MySQL進程記憶體中，就是圖中紅色的部分

• 寫到磁碟，但沒有持久化，物理上是在檔案系統的page cache裡面，也就是圖中黃色的部分

• #持久化到磁碟，對應的是hard disk，也就是圖中的綠色部分

日誌寫到redo log buffer和write到page cache都是很快的，但是持久化到磁碟的速度就慢多了

為了控制redo log的寫入策略，InnoDB提供了innodb_flush_log_at_trx_commit參數，它有三種可能取值：

• 設定為0的時候，表示每次交易提交時都只是把redo log留在redo log buffer中

• 設定為1的時候，表示每次交易提交時都會將redo log直接持久化到磁碟

• 設定為2的時候，表示每次交易提交時都只是把redo log寫到page cache

#InnoDB有一個後台線程，每隔1秒，就會把redo log buffer中的日誌，呼叫write寫到檔案系統的page cache，然後呼叫fsync持久化到磁碟。事務執行中間程序的redo log也是直接寫在redo log buffer中的，這些redo log也會被後台執行緒一起持久化到磁碟。換句話說，即使一個事務尚未提交，其重做日誌也可能已經持久化到磁碟

還有兩個場景會讓一個沒有提交的事務的redo log寫入到磁碟中

1.redo log buffer所佔用的空間即將達到innodb_log_buffer_size一半的時候，後台執行緒會主動寫盤。由於事務並沒有提交，所以這個寫盤動作只是write，而沒有調用fsync，也就是只留在檔案系統的page cache

2.並行的事務提交的時候，順帶將這個事務的redo log buffer持久化到磁碟。假設一個事務A執行到一半，已經寫了一些redo log到buffer中，這時候有另外一個線程的事務B提交，如果innodb_flush_log_at_trx_commit設定的是1，事務B要把redo log buffer裡的日誌全部持久化到磁碟.當發生這種情況時，將把與事務A相關的redo日誌緩衝區一起記錄到磁碟上

兩階段提交，時序上redo log先prepare，再寫binlog，最後再把redo log commit 。如果把innodb_flush_log_at_trx_commit設定成1，那麼redo log在prepare階段就要持久化一次

MySQL的雙1配置，指的就是sync_binlog和innodb_flush_log_at_trx_commit都設定成1。也就是說，在一個交易完整提交前，需要等待兩次刷盤，一次是redo log（prepare階段），一次是binlog

3、群組提交機制

日誌邏輯序號LSN是單調遞增的，用來對應redo log的一個個寫入點，每次寫入長度為length的redo log，LSN的值就會加上length。 InnoDB的資料頁中也會記錄LSN，以確保避免重複執行redo log而造成資料頁的重複更新

上圖是三個並發交易在prepare階段，都寫完redo log buffer，持久化到磁碟的過程，對應的LSN分別是50、120和160

1.trx1是第一個到達的，會被選為這組的leader

#2.等trx1要開始寫盤的時候，這個群組裡面已經有了三個事務，這時候LSN也變成了160

3.trx1去寫盤的時候，帶的就是LSN= 160，因此等trx1返回時，所有LSN小於等於160的redo log，都已經被持久化到磁碟

4.這時候trx2和trx3就可以直接返回了

一個組提交裡面，組員越多，節約磁碟IOPS的效果要好

為了讓一次fsync帶的組員更多，MySQL做了拖時間的優化

binlog也可以群組提交了，在執行上圖第4步把binlog fsync到磁碟時，如果有多個事務的binlog已經寫完了，也是一起持久化的，這樣也可以減少IOPS的消耗

如果想提升binlog群組提交的效果，可以透過設定binlog_group_commit_sync_delay和binlog_group_commit_sync_no_delay_count兩個參數來實現

#1.binlog_group_commit_sync_delay參數表示延遲多少微妙後才呼叫fsync

這兩個條件只要有一個滿足就會呼叫fsync

#WAL機制主要得益於兩方面：

• redo log和binlog都是順序寫的，磁碟的順序寫比隨機寫速度要快

• 組提交機制，可以大幅降低磁碟訂單IOPS消耗

4、如果MySQL現在出現了效能瓶頸，而且瓶頸在IO上，可以透過哪些方法來提升效能

1.設定binlog_group_commit_sync_delay（延遲多少微妙後才呼叫fsync）和binlog_group_commit_sync_no_delay_count（累積多少次以後才呼叫fsync）參數，減少binlog的寫盤次數。雖然該方法可能增加語句的回應時間，但不會有資料遺失的風險，因為它是透過故意等待來實現的

2.將sync_binlog設定為大於1的值（每次提交交易都write，但累積N個事務後才fsync）。這樣做的風險是，主機掉電的時候會丟binlog日誌

3.將innodb_flush_log_at_trx_commit設定為2（每次交易提交時都只是把redo log寫到page cache）。這樣做的風險是，主機掉電的時候會失去資料

以上是MySQL的基礎架構與日誌系統實例分析的詳細內容。更多資訊請關注PHP中文網其他相關文章！