《大話數據庫》-SQL語句執行時，底層究竟做了什麼小動作？
《大話數據庫》-SQL語句執行時，底層究竟做了什麼小動作？
前言
大家好，我是Taoye，試圖用玩世不恭過的態度對待生活的Coder。
現如今我們已然進入了大數據時代，無論是業內還是業外的朋友，相信都有聽說過數據庫這個名詞。數據是一個項目的精華，也扮演着為企業創造價值的重要角色，一個較為完善的公司一般都會有專門的DBA來管理數據庫，以便更好的為用戶服務。
互聯網的發展速度之快，以致大量的APP應用湧入用戶的視野，在大多數APP中都會有「推薦」這一板塊，而這個板塊功能的核心正是基於用戶以往的數據記錄而實現的。再如《死亡筆記》中L·Lawliet這一角色所提到的大數定律，在眾多繁雜的數據中必然存在着某種規律，偶然中必然包含着某種必然的發生。不管是我們提到的大數定律，還是最近火熱的大數據亦或其他領域都離不開大量數據的支持。
如上，我們可初步體會到數據的重要性，而要想更好的管理數據，則避免不了與數據庫打交道。對於數據庫而言，操作數據庫的用戶就相當於一位老闆，我們需要向數據庫發出命令，然後期望數據庫給我們返回想要的結果。
我們可以想像一下這麼一個場景，老闆給Taoye發佈了這麼一個任務：「Taoye啊，你作為一位專業的板磚工，我現在需要你在一小時的之內將工地的轉搬回來。」是的，老闆發佈任務只注重結果和效率，而不在意你板磚的過程。我們在執行SQL語句的時候也是如此，一般只關心執行的結果和效率是否滿足用戶的需求。
最近，Taoye重新把之前學習數據庫時候所記錄的筆記複習了一下，然後又系統性的拜讀了丁奇大大的《MySQL實戰45講》中的內容，所以想要把MySQL系列的知識內容單獨整理出來，在進行自我提高的同時，也希望能給予大家一點幫助。
MySQL的體系結構
我們要想系統性的學習MySQL數據庫，首先不得不了解MySQL的體系結構。在MySQL中，主要是由什麼功能模塊組成？每一個功能模塊在SQL語句執行的時候分別扮演了一個什麼樣的角色？MySQL的強大之處在哪，竟會受到如此之多的開發者的青睞？這些都是我們每一位學習MySQL的朋友必須了解甚至掌握的內容，以下便是MySQL數據庫的體系結構及其執行流程：
從上圖，我們可以看出MySQL整體上主要分為了Server層和存儲引擎層兩個部分。
在Server層內部又包括連接器、緩存、分析器、優化器、執行器等功能部件，主要負責了MySQL的大多數核心服務功能，比如存儲過程、函數、觸發器、視圖等。
而存儲引擎層主要的是負責數據的存儲和提取，在MySQL中可支持MyISAM、InnoDB、Memory等多種存儲引擎，其中最常見的是InnoDB和MyISAM，這也是我們在學習存儲引擎時候的一個重點。在MySQL 5.5.5版本之前默認使用的是MyISAM，而在此版本之後默認採用的是InnoDB存儲引擎。我們在實際創建數據表的時候，也可以通過ENGINE來指定使用的存儲引擎，如下所示。我們可以對tb_comment數據表指定使用MyISAM存儲引擎：
1CREATE TABLE `tb_comment` (
2 `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
3 `content` varchar(255) DEFAULT NULL,
4 `user_name` varchar(255) DEFAULT NULL,
5 `openid` varchar(255) DEFAULT NULL,
6 `comment_time` int(11) DEFAULT NULL
7 PRIMARY KEY (`id`) USING BTREE
8) ENGINE=MyISAM AUTO_INCREMENT=175 DEFAULT CHARSET=utf8;
MyISAM和InnoDB這兩種存儲引擎最主要的區別是事務以及鎖機制：
- InnoDB支持事務，而MyISAM不支持事務
- InnoDB一般採用的是行鎖，鎖的粒度小，開銷大，鎖錶慢，但是在高並發場景下性能更好。而MyISAM採用的是表鎖，特徵與行鎖相反。
關於MySQL的事務和鎖機制，這裡只是簡單的提一下，具體的細節我們後面聊。下面我們將MySQL的體系結構中每一個功能模塊單獨的分離開來，依次看看每一個功能模塊所體現出的作用。
連接器
我們要想正常的操作數據庫，首先需要經過連接器這道大門。在正式引出連接器之前，各位看官不妨來看看下面一個例子：
金主大大成天擔心自己財產的安全，必然會將自己的money存儲在銀行金庫中。某一天金主大大要想取出一部分的財產來維持公司的運營，而銀行為了保障金庫中money的安全性，金主必然要進行身份核驗以及一系列防盜系統的檢測。
在MySQL數據庫中，連接器的作用其實就類似於上面的身份校驗以及防盜系統，主要是負責與客戶端建立連接、權限校驗、維持和管理連接。我們要想操作數據庫，首先需要通過賬號、密碼等信息來連接數據庫，假如我們想要以root賬戶、密碼為666666來連接192.168.31.100:3307的MySQL服務，則可以執行以下命令：
1mysql -h 192.168.31.100 -P 3307 -u root -p 666666
2
3# 如果是本地連接，則執行如下，密碼也可回車後輸入
4mysql -u root -p 666666
當數據匹配成功時，連接器就能允許用戶與數據庫建立連接。此外，連接器還需要驗證該用戶是否有權限對數據表進行操作，這個時候連接器會去權限表中查詢連接用戶的權限，只有在具有操作權限的前提下才能操作數據表。如果我們在與數據庫已經建立連接的前提下，但是不對數據庫進行任何操作，這個時候連接就會處於空閑狀態，我們可以通過show processlist命令來查看已經建立的連接數和處於空閑狀態的連接，其中command字段為sleep表示連接空閑：
1mysql> show processlist;
2+----+------+-----------------+------+---------+------+----------+------------------+
3| Id | User | Host | db | Command | Time | State | Info |
4+----+------+-----------------+------+---------+------+----------+------------------+
5| 3 | root | localhost:53372 | NULL | Sleep | 28 | | NULL |
6| 4 | root | localhost:53378 | NULL | Query | 0 | starting | show processlist |
7+----+------+-----------------+------+---------+------+----------+------------------+
8
如果連接長期處於空閑狀態而不做任何操作，當超過一定時間時，就會自動斷開連接，而這個時間閾值主要是通過wait_timeout屬性來決定的，默認是28800，即8小時。show variables like '%wait_timeout%'"可查看時間閾值，set @@session.wait_timeout=xxx可修改當前會話下的時間閾值，具體操作如下：
1mysql> set @@session.wait_timeout=30000;
2Query OK, 0 rows affected (0.00 sec)
3
4mysql> show variables like "%wait_timeout%";
5+--------------------------+----------+
6| Variable_name | Value |
7+--------------------------+----------+
8| innodb_lock_wait_timeout | 50 |
9| lock_wait_timeout | 31536000 |
10| wait_timeout | 30000 |
11+--------------------------+----------+
123 rows in set, 1 warning (0.00 sec)
緩存
緩存這個概念，學習過《計算機組成原理》或是其他相關課程的朋友應該並不陌生，緩存一般使用的是SRAM（靜態隨機存儲器）技術實現的，相較於DRAM（動態隨機存儲器）而言，它最主要的優勢在於速度快，能夠大大提高數據的查詢效率。
關於緩存，我們可以來做一個簡答的計算題：
假設查詢一次緩存需要1s，查詢一次內存需要10s，緩存命中的概率為90%，一位用戶想要查詢100次，則使用緩存和不使用緩存的平均查詢時間是多少？
可見，根據局部性原理，緩存的存在是可以大大提高數據的查詢效率的。
由上方的執行流程圖，我們也可以知道，客戶端通過連接器與MySQL服務器建立連接之後，這個時候就會來到緩存中查詢用戶所需的數據。假設用戶向MySQL發出以下一條查詢語句：
1mysql> select * from tb_comment where id=1;
MySQL收到用戶發出的請求之後，就會先到緩存中看看之前是否有執行過相同的語句，而且之前執行的結果會以key-value鍵值對的形式直接進行存儲。假如之前有執行過這條命令，則直接從緩存中取出數據並反饋給用戶，如果沒有執行過，則會繼續走 分析器 -> 優化器 -> 執行器 -> 存儲引擎這條鏈路。所以說，緩存的命中可以省去後面一系列操作所消耗的時間，這也是緩存提高查詢效率的原因。
按道理來講，緩存的引入應該是相當不錯的，而且用戶查詢次數越多就越能體現緩存的強大，但為什麼在MySQL 8.0版本之後直接捨去了緩存部件呢？
在理想情況下，緩存引入確實是非常的完美，但是在數據庫中，我們除了查詢操作之外，還有更新操作（增、刪、改）。每當我們執行過一次更新操作的時候，數據庫中的數據就已然發生了改變，而當我們再次發出命令從緩存中取出的數據就不再是用戶所期望的數據了。所以對MySQL而言，每當用戶進行更新操作時，都會清空一次緩存，然後再次重新緩存新的數據，而這個過程給MySQL帶來的壓力是很大的，也大大削弱了SQL語句的執行效率。所以說，緩存對於一些查詢多，更新少的數據表比較有用，而對那些更新比較頻繁的數據表就會適得其反。
如果使用的是MySQL 8.0以下的版本，我們可以根據實際需求來確定是否開啟緩存，主要是通過my.cnf配置文件中的query_cache_type來決定的，0代表禁用緩存，1代表開啟緩存，2代表根據需要使用緩存。在執行SQL語句的時候可以附帶SQL_CACHE和SQL_NO_CACHE來確定執行時是否使用緩存，並且可以通過show status like '%qcache%'命令來查看緩存的整體情況：
1mysql> select SQL_CACHE * from tb_comment where id=1;
2mysql> show status like "%qcache%";
3+-------------------------+---------+
4| Variable_name | Value |
5+-------------------------+---------+
6| Qcache_free_blocks | 1 |
7| Qcache_free_memory | 1031872 |
8| Qcache_hits | 0 |
9| Qcache_inserts | 0 |
10| Qcache_lowmem_prunes | 0 |
11| Qcache_not_cached | 10 |
12| Qcache_queries_in_cache | 0 |
13| Qcache_total_blocks | 1 |
14+-------------------------+---------+
15
分析器
如果在執行查詢SQL時沒有命中緩存，則需要去數據表中查詢用戶所需的數據了，也就是開始進入分析器來對用戶發出的SQL語句進行分析。
分析的時候主要是包括預處理和解析的過程，在這個階段會解析SQL語句的語義，並對語句中的一些關鍵字和非關鍵字進行提取、解析，並組成一個解析樹交給後面的執行器執行。具體的關鍵詞包括但不限定於select/update/delete/or/in/where/group by/having/count/limit等，如果分析到語法或關鍵詞錯誤，會直接給客戶端拋出異常:ERROR:You have an error in your SQL syntax.
比如用戶執行：select * from tb_comment where user_id=1，在分析器中就會通過語義規則器將select、from、where等提取和匹配出來，然後進行分析並校驗，假如在校驗的時候發現tb_comment中並不存在user_id字段，則會報錯：Unknown column 'user_id' in 'where clause
1mysql> select * from tb_comment where user_id=1;
2ERROR 1054 (42S22): Unknown column 'user_id' in 'where clause'
優化器
分析器對用戶的SQL語義進行分析之後，則說明SQL語句本身是沒有任何問題，並且是可以被正常執行的。此時，MySQL已經知道了用戶的意圖，用戶是想查詢還是更新，MySQL都心知肚明。
優化器主要是對SQL進行優化，會根據執行計划進行最優的選擇來匹配合適的索引，並選擇最佳的執行方案。在丁奇《MySQL實戰45講》中提到，一個語句有多表關聯（join）的時候，會決定各個表的連接順序。比如你執行下面這樣的語句，這個語句是執行兩個表的join：
1mysql> select * from t1 join t2 using(ID) where t1.c=10 and t2.d=20;
- 既可以先從表t1裏面取出c=10的記錄的ID值，再根據ID值關聯到表t2，再判斷t2裏面d的值是否等於20。
- 也可以先從表t2裏面取出d=20的記錄的ID值，再根據ID值關聯到t1，再判斷t1裏面c的值是否等於10。
這兩種執行方式的邏輯結果是一樣的，但是執行的效率會有不同，而優化器的作用就是決定選擇使用較佳的方案來執行。為了更好的理解丁奇大大的例子，我們可以對其進行類別：我們要想在中國上海找到年齡在20-30歲之間的人。第一種方案是先鎖定上海，然後再查詢年齡；而第二種是先在全國查詢年齡區間在20-30的人，然後再鎖定上海。至於哪種方案較優，相信各位看官都明白吧。
當然了，優化器對SQL進行優化的地方不僅僅局限於以上場景。在我們創建聯合索引，並對數據表進行查詢的時候，優化器同樣可能會對其進行優化。且看下面一個例子：
1mysql> create index oid_ctime_pid on tb_comment(openid, comment_time, problem_id);
2mysql> explain select * from tb_comment where openid="1" and problem_id=401 and comment_time=3;
3+----+-------------+------------+------+---------------+---------------+---------+-------------------+------+-----------------------+
4| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
5+----+-------------+------------+------+---------------+---------------+---------+-------------------+------+-----------------------+
6| 1 | SIMPLE | tb_comment | ref | oid_ctime_pid | oid_ctime_pid | 778 | const,const,const | 1 | Using index condition |
7+----+-------------+------------+------+---------------+---------------+---------+-------------------+------+-----------------------+
在上面，我們首先根據tb_comment數據表中的openid、comment_time、problem_id建立一個組合索引oid_ctime_pid，然後根據索引字段進行查詢。然而在查詢的時候，我們有意地將篩選的字段順序打亂，與索引字段的創建順序不一致。（索引創建字段順序：openid、comment_time、problem_id，查詢條件字段順序：openid、problem_id、comment_time）但是我們採用explain執行計劃檢索的時候發現key屬性為oid_ctime_pid，換句話講，即使我們不遵循最左匹配原則，此時依然是走了oid_ctime_pid組合索引的。之所以能夠達到這樣的效果，正式因為優化器的存在，在其內部對查詢條件的順序進行了優化，從而利用索引提高了數據的查詢效率。
注意：以上內容提前涉及到了數據庫當中索引的概念，索引是非常非常重要的，我們日後會單獨的詳細講講索引。另外，還有一點值得注意的是，雖然說優化器在一定程度上能夠優化SQL，但是這種優化程度僅僅是MySQL所認為的一個較佳狀態，而不一定能夠滿足DBA所需要的標準。
執行器
OK，MySQL已經對用戶發出的SQL語句進行了分析，也進行了優化，現在就要進入執行階段來執行SQL語句了。
執行器在對SQL語句進行執行的時候，會根據表的引擎定義，去使用這個引擎所提供的接口，這些接口都是引擎中已經定義好的，執行器直接通過「拿來主義」進行調用即可。引擎還有一個名字叫做「表處理器」，這個名字應該說更能體現出它所存在的意義。
我們同樣以一條簡單的SQL語句來說明下這個執行過程：
1mysql > select * from tb_comment where openid=123;
假設這張表採用的是InnoDB存儲引擎，則首先InnoDB存儲引擎接口會去查詢的表數據中的第一行，對該行數據中的openid字段所對應的值進行判斷，假如該值為123，則將這行數據加入到結果集當中，否則引擎接口繼續查詢下一行，如此不斷循環重複相同的判斷邏輯，直至對數據表中最後一行的數據判斷結束為止。最後，將結果集當中的所有滿足條件的數據反饋給用戶。
以上便是執行器在查詢操作時候的大體功能，但是在進行更新操作（增、刪、改）的時候，執行器還會將具體的操作記錄到binlog日誌當中，另外，update會採取兩階段的提交方式，記錄到redolog中，也就是我們接下來所要講的MySQL日誌系統。
MySQL的日誌系統
日誌日誌，在生活中，顧名思義就是記錄生活的點點滴滴，把自己的內心世界記錄下來，更好的詮釋自己當時寫日誌時候的心情感受。待到將來的某一天，我們回翻之前所寫的日誌時，我們能夠瞬間回憶起自己身邊所發生的事以及內心世界。
同理，在計算機領域裏，日誌也是一個相當重要的概念。而MySQL的日誌系統會將我們對數據庫的修改操作全部自動記錄下來，這樣就能夠通過日誌文件隨時對誤操作的數據進行恢復。說道這裡，有了解過Git或是讀過Taoye之前寫的Git文章的朋友，應該會有點聯想，在Git中，我們可以在任意時間點對項目的版本進行恢復（回退），雖然結果與MySQL的日誌系統有點類似，但是它們的實現原理是不一樣的。關於Git，想了解的讀者可以暫且跳轉學習：哪些年，我們玩過的Git
redo log（重做日誌）和binlog（歸檔日誌）是MySQL當中非常重要的兩種日誌，尤其是binlog，在我們之後進行數據恢復或是搭建主從的時候會頻繁的接觸到。
關於對redo log和binlog的理解，丁奇在《MySQL實戰45講》中已經講解的非常明白了，其中以一種通俗易懂的方式來介紹了晦澀難懂的redo log和binlog。說實話，我記得第一次學習這兩種日誌的時候，總是對某些小的知識點細節理解的不夠透徹，甚至會出現理解偏差的情況，好在後來通過不斷重複閱讀和查詢資料的形式一個個解決了當時的疑問。關於對redo log和binlog基本概念的理解，這裡就不再多說了，大家可以去看看《MySQL實戰45講》中該部分的內容，或是閱讀：//www.cnblogs.com/sunshineliulu/p/10905483.html
下面主要是介紹下binlog二進制日誌的常見操作，及如何通過binlog搭建主從和進行數據的恢復：
binlog日誌的常見操作
配置binlog
在MySQL中，binlog日誌默認是處於關閉的狀態，我們要想開啟並設置二進制日誌，就需要修改MySQL的配置文件，即my.cnf，配置完成之後再重啟mysql即可生效systemctl restart mysql:
1log-bin = /www/server/data/mysql-bin # binlog日誌的存放路徑
2expire_logs_days = 7 # 日誌文件的過期時間，過期之後會自動刪除，0表示不刪除
3binlog_format = mixed # 日誌的存儲格式
4max_binlog_size = 100M # 每個binlog日誌文件的大小
- log-bin：binlog日誌的存放路徑
- expire_logs_days：表示日誌文件的過期時間，過期之後MySQL就會自動將日誌文件刪除，如果設置為0則表示不刪除
- binlog_format：表示的是binlog日誌文件的存儲格式。
該屬性的值總共有三種：statement、row和mixed，當將日誌格式配置為statement的時候，表示每一次修改數據的SQL語句本身都會記錄到binlog日誌當中，而row並非記錄SQL語句，而是記錄被修改的那行數據。mixed格式則是前兩總形式的混合，一般對庫或表結構發生修改採用的是statement，而數據本身發生修改採用row格式。
- max_binlog_size： 每個binlog日誌文件的大小，默認是1G。因為binlog日誌並不是像redo log那樣循環寫，而是採用追加的形式記錄日誌，所以當一個binlog日誌文件寫滿之後，就會進行一次滾動，也就是創建一個新的binlog日誌文件用於記錄。
以上關於binlog日誌的配置只是一部分，其他的配置我們用到的時候再來補充。當該部分配置完成之後，我們就可以通過show variables like命令來查看二進制文件的設置：
1mysql> show variables like "log_bin%";
2+---------------------------------+----------------------------------+
3| Variable_name | Value |
4+---------------------------------+----------------------------------+
5| log_bin | ON |
6| log_bin_basename | /www/server/data/mysql-bin |
7| log_bin_index | /www/server/data/mysql-bin.index |
8| log_bin_trust_function_creators | OFF |
9| log_bin_use_v1_row_events | OFF |
10+---------------------------------+----------------------------------+
查看目前所存在的所有二進制日誌，以及當前正在使用的二進制日誌：
1mysql> show master logs;
2+------------------+-----------+
3| Log_name | File_size |
4+------------------+-----------+
5| mysql-bin.000022 | 120 |
6| mysql-bin.000023 | 45529 |
7| mysql-bin.000024 | 24284 |
8+------------------+-----------+
9
10mysql> show master status;
11+------------------+----------+--------------+------------------+-------------------+
12| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
13+------------------+----------+--------------+------------------+-------------------+
14| mysql-bin.000028 | 120 | | | |
15+------------------+----------+--------------+------------------+-------------------+
清除binlog日誌文件
每當我們重啟MySQL服務的時候，都會自動創建一個新的binglog二進制日誌文件，並且還會生成mysql-bin.index文件，該文件存儲所有二進制日誌文件的清單，也就是二進制日誌文件的索引。在上面，我們配置在my.cnf中配置binlog的時候，設置了expire_logs_days=7，也就是說超過7天的日誌文件會被自動刪除。但是如果我們不對binlog日誌文件進行處理的時候，大量的binlog文件會佔據太多的磁盤空間，從而在一定程度上影響了磁盤的IO性能，所以定期清理binlog日誌是很有必要的。
我們既可以清除所有的binlog日誌文件，也可以對指定的日誌文件進行清除，部分具體操作如下：
1# 清除mysql-bin.000023之前的所有日誌文件
2mysql> purge master logs to "mysql-bin.000023";
3# 清除指定時間之前的日誌文件
4mysql> purge master logs before "2020-06-12 12:12:12";
5# 清除所有的日誌文件
6mysql> reset master;
7mysql> show master logs;
8+------------------+-----------+
9| Log_name | File_size |
10+------------------+-----------+
11| mysql-bin.000001 | 120 |
12+------------------+-----------+
我們在執行reset master命令的時候，會清除所有的binlog日誌，並且會自動重新創建新的二進制日誌文件，其其編號為000001開始。
binlog的切換（滾動）
此外，也可以通過flush logs命令實現日誌文件的滾動，即會生成的一個新的日誌文件作為當前的記錄日誌，之後對數據庫的修改操作都會記錄在該日誌文件當中：
1mysql> flush logs;
2Query OK, 0 rows affected (0.04 sec)
3
4mysql> show master logs;
5+------------------+-----------+
6| Log_name | File_size |
7+------------------+-----------+
8| mysql-bin.000001 | 167 |
9| mysql-bin.000002 | 120 |
10+------------------+-----------+
11
binlog日誌文件的查看與分析
前面有提到，binlog中有statement、row和mixed三種存儲格式，它們的存儲內容都是二進制的形式，我們是無法通過常規的方式瀏覽其內容的，一般可以以命令的形式或是工具來查看並分析二進制日誌文件的內容：
1mysql> create database db_test;
2# 查看日誌文件所記錄的事件
3mysql> show binlog events in "mysql-bin.000002";
4+------------------+-----+-------------+-----------+-------------+---------------------------------------+
5| Log_name | Pos | Event_type | Server_id | End_log_pos | Info |
6+------------------+-----+-------------+-----------+-------------+---------------------------------------+
7| mysql-bin.000002 | 4 | Format_desc | 1 | 120 | Server ver: 5.6.47-log, Binlog ver: 4 |
8| mysql-bin.000002 | 120 | Query | 1 | 223 | create database db_test |
9+------------------+-----+-------------+-----------+-------------+---------------------------------------+
10
除了採用show binlog events命令之外，我們還可以使用mysqlbinlog來讀取日誌文件或轉化為自己想要輸出的文件形式，這樣便於在誤操作的情況下對日誌文件進行分析。這裡需要注意的一點是，在使用mysqlbinlog工具的時候，一般要進入到其所在目錄，默認存在於MySQL的bin目錄當中，並且在指定具體解析的日誌文件時，同樣要定位文件所在位置。
1# 直接查看日誌文件的內容
2$ ./mysqlbinlog --no-defaults ../../data/mysql-bin.000002
3
4# 將binlog日誌文件內容以txt或log文件輸出
5$ ./mysqlbinlog ../../data/mysql-bin.000002 > ./my-log.log
6
7# 將binlog日誌文件轉化為sql文件
8$ ./mysqlbinlog --base64-output=decode-rows -v ../../data/mysql-bin.000002 > ./my-log.sql
下面我們從0開始建庫、建表，並實現數據的增刪改操作，來詳細分析一下日誌文件中所記錄的信息：
- 日誌滾動、建庫db_test、建表tb_test，對數據表執行增刪改操作
1# 日誌滾動、建庫db_test、建表tb_test
2mysql> flush logs;
3mysql> create database db_test;
4mysql> use db_test;
5mysql> create table tb_test(id int primary key, age int, username varchar(25));
6
7# 對數據表執行增刪改操作
8mysql> insert into tb_test values(1, 1, "me");
9mysql> update tb_test set username="you" where id=1;
10mysql> delete from tb_test where id=1;
- 查看日誌文件、將日誌文件輸出為log、sql文件，以便之後的分析
1$ ./mysqlbinlog --no-defaults ../../data/mysql-bin.000003
2$ ./mysqlbinlog ../../data/mysql-bin.000003 > ./my-log.log
3$ ./mysqlbinlog --base64-output=decode-rows -v ../../data/mysql-bin.000003 > ./my-log.sql
- 日誌分析
下圖是Taoye使用Sublime text打開my-log.sql文件時所展現的內容（部分內容略過），從圖中可以看到，binlog日誌當中已經記錄了我們對數據庫所做出的所有操作，包括建庫、建表、增刪改，並且該日誌文件是存儲在磁盤當中，換句話說，及時我們的MySQL服務宕機了，但是重啟之後依然可以根據該文件進行數據的恢復。
根據binlog日誌實現數據的恢復
在上面，已經介紹了在實際使用過程中binlog日誌的常見操作，其中包括binlog日誌的配置、查看、滾動、清除和分析等，接下來就是利用binlog日誌實現數據的恢復了。
在一個伸手不見五指的黑夜，Taoye的同事Yetao坐在辦公桌前，左手捧着一杯Java（咖啡），右手提着一塊板磚。白天不斷隱忍了產品經理的折磨之後，此時的他心情非常的憤懣，最終它做出了一個明智的選擇：「刪庫，跑路！」
第二天，產品經理髮現數據庫已然被Yetao這臭小子清空了，於是把心中的那把火全部灑在Taoye身上，並且命令Taoye在一天之內恢複數據庫中所有的數據，否則滾蛋。
Shit，Yetao這臭小子居然做了這麼愚蠢的騷操作，居然還讓我背鍋、擦屁股。好在我的binlog日誌玩的賊溜，否則還真的得滾蛋。下面我們來看看在實際的過程中，假如出現了數據的誤操作，我們該如何利用binlog二進制日誌文件對數據進行恢復？
- 基於單個binlog實現數據的恢復
1# 日誌滾動、建庫db_test、建表tb_test
2mysql> flush logs;
3mysql> create if not exists database db_test;
4mysql> use db_test;
5mysql> create table if not exists tb_test(id int primary key, age int, username varchar(25));
6
7mysql> insert into tb_test values(1, 1, "taoye");
8mysql> insert into tb_test values(2, 2, "yetao");
9mysql> delete from tb_test where id=1;
首先，我們需要通過以上一些SQL語句來自定一個場景，以便我們實現數據的恢復：flush logs;進行日誌文件滾動，使得之後對數據庫的操作都能記錄在一個新的binlog日誌文件當中。之後創建db_test數據庫以及tb_test數據表，並在表中的插入兩條數據，最後刪除id=1所對應的數據。
假如我們最後刪除的那一條數據是一個誤操作，我們應當如何通過binlog日誌文件來恢復該條數據呢？
1.查看以上所有操作被記錄在哪一個binlog當中，也就是當前所使用的binlog日誌
1mysql> show master status;
2+------------------+----------+--------------+------------------+-------------------+
3| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
4+------------------+----------+--------------+------------------+-------------------+
5| mysql-bin.000002 | 1074 | | | |
6+------------------+----------+--------------+------------------+-------------------+
2.由show master status命令我們可以知道，當前使用的是mysql-bin.000002日誌，並且以上所有操作都已經被記錄在該日誌文件當中。對此，我們可以繼續使用show binlog events命令來指定該日誌文件，查看一下該文件所發生的事件：
1mysql> show binlog events in "mysql-bin.000002";
通過執行以上命令，我們可以得到日誌文件所記錄每個事件的信息，其中包括日誌文件名、起始位置、事件類型、服務id、終止位置、描述信息，其中比較重要的是log_name、pos、end_log_pos、info字段的信息，我們進行數據的恢復也是的根據這些信息來實現的。
3.分析及數據的恢復
既然我們是想對最後的delete語句所刪除的數據進行恢復，那麼根據上面的執行結果可以發現，倒數第二行中的Info字段的值為：use 'db_test'; delete from tb_test where id=1，也就是說我們的需求是想要得到執行該行之前的數據庫狀態。
對此，我們繼續往上查找，發現倒數第四行的Info是一個commit，也就是insert事務的提交。至此，我們基本可以確定，我們只需要將數據庫恢復到從起始位置到該commit為止即可，這樣就完美的跳過了delete操作。而指定數據恢復的位置便是pos和end_log_pos兩個字段所決定的，從圖中我們就能明白：只需要執行binlog日誌文件中pos=4，end_log_pos=848的內容即可恢復。
所以我們可以通過如下方式實現該數據的恢復：
1# 刪除之前舊的數據庫
2mysql> drop database db_test;
3# 恢複數據
4$ ./mysqlbinlog --start-position 4 --stop-position 848 ../../data/mysql-bin.000002 | mysql -uroot -p;
以上是我們通過mysqlbinlog工具實現數據恢復的一般步驟，在其中指定的了–start-position和stop-position兩個參數來確定恢復的起始位置和終止位置。當然，在前面我們也有介紹過將binlog日誌轉化為sql文件方法，所以我們同樣可以基於sql文件來實現數據的恢復，這個比較簡單，這裡就不多說了。
- 基於多個binlog實現數據的恢復
以上是我們基於單個binlog日誌文件實現數據恢復的操作，但是假如我們的數據操作信息被記錄到多個binlog日誌文件當中，在誤操作的情況下應該如何實現日誌的恢復呢？
1mysql> flush logs;
2mysql> insert into tb_test values(3, 3, "taoye03");
3mysql> insert into tb_test values(4, 4, "taoye04");
4mysql> insert into tb_test values(5, 5, "taoye05");
5
6mysql> flush logs;
7mysql> insert into tb_test values(6, 6, "taoye06");
8msyql> delete from tb_test where id=4;
9mysql> insert into tb_test values(7, 7, "taoye07");
10
11mysql> flush logs;
12mysql> insert into tb_test values(8, 8, "taoye08");
13mysql drop table tb_test;
14mysql drop database db_test;
在上面我們執行了三次flush logs命令，也就是滾動了三次，並生成了三個日誌binlog日誌文件。第一個文件中記錄了三次insert操作，第二個日誌文件中記錄了insert、delete、insert，第三個日誌記錄了insert、刪表、刪庫的操作。假如我們現在的需求是恢復所有delete以及刪表刪庫的操作，我們應該怎麼做？
有了前一個例子的鋪墊，該數據的恢復應該不難，思路就是：第一個日誌文件進行全部恢復，第二個日誌文件進行兩次部分恢復（以delete為分界）、第三日誌文件進行一次部分恢復（即除去刪表、刪庫操作）。
具體的命令操作，大家可以參考刪一個例子，基本是一致的，這裡就不做過多贅述了。
參考資料：
[1] 丁奇.MySQL實戰45講
[2] 詳細分析MySQL事務日誌(redo log和undo log)
//www.cnblogs.com/f-ck-need-u/archive/2018/05/08/9010872.html#auto_id_11
[3] MySQL二進制日誌詳解
//www.cnblogs.com/jkin/p/10124182.html
總結
至此，本文基本就結束了。
該文詳細講解了MySQL的體系結構，其中包括連接器、緩存、分析器、優化器和執行器等各個功能部件的作用及所扮演的角色。再者，介紹了MySQL的日誌系統，日誌系統在MySQL當中是非常重要的，其應用場景主要體現在主從搭建以及數據恢復，在實際生產過程中使用的還是非常頻繁的，畢竟誰也保證不了我們的數據庫沒有出現問題的時候。
這篇文章是《大話數據庫》的第一篇，也是從宏觀的角度來對MySQL進行一個整體性的分析，這樣對後面的事務、索引等知識的理解都是非常有幫助的，我們只有了解了MySQL的底層工作原理，才能在出現問題時直擊問題的本質。
認真讀到這裡的讀者，相信都是和Taoye一樣懷揣着一顆想要不斷提高自己的心，想要成為一位優秀的Coder。原創不易啊，如果本文對各位有所幫助，還請關注+轉發+再看，【三連】走一走啊，就當是給Taoye堅持肝文的鼓勵和支持了。
《大話數據庫》，我們下期再見！
