MySQL基礎(chǔ)架構(gòu)

MySQL的邏輯架構(gòu)圖.PNG

連接器

連接器負(fù)責(zé)跟客戶端建立連接、獲取權(quán)限、維持和管理連接。在完成經(jīng)典的 TCP 握手后，連接器就要開始認(rèn)證你的身份，這個時候用的就是你輸入的用戶名和密碼。如果用戶名密碼認(rèn)證通過，連接器會到權(quán)限表里面查出你擁有的權(quán)限。之后，這個連接里面的權(quán)限判斷邏輯，都將依賴于此時讀到的權(quán)限。這就意味著，一個用戶成功建立連接后，即使你用管理員賬號對這個用戶的權(quán)限做了修改，也不會影響已經(jīng)存在連接的權(quán)限。修改完成后，只有再新建的連接才會使用新的權(quán)限設(shè)置?？蛻舳巳绻L時間沒動靜，連接器就會自動將它斷開。這個時間是由參數(shù) wait_timeout 控制的，默認(rèn)值是 8 小時。

相關(guān)命令：show processlist

分析器

優(yōu)化器

執(zhí)行器

MySQL 通過分析器知道了你要做什么，通過優(yōu)化器知道了該怎么做，于是就進(jìn)入了執(zhí)行器階段，開始執(zhí)行語句。開始執(zhí)行的時候，要先判斷一下你對這個表 T 有沒有執(zhí)行查詢的權(quán)限，如果沒有，就會返回沒有權(quán)限的錯誤，如下所示 (在工程實現(xiàn)上，如果命中查詢緩存，會在查詢緩存返回結(jié)果的時候，做權(quán)限驗證。查詢也會在優(yōu)化器之前調(diào)用 precheck 驗證權(quán)限)。

日志系統(tǒng)

redo log

當(dāng)有一條記錄需要更新的時候，InnoDB 引擎就會先把記錄寫到 redo log里面，并更新內(nèi)存，這個時候更新就算完成了。同時，InnoDB 引擎會在適當(dāng)?shù)臅r候，將這個操作記錄更新到磁盤里面，而這個更新往往是在系統(tǒng)比較空閑的時候做。

redo log.PNG

write pos 是當(dāng)前記錄的位置，一邊寫一邊后移，寫到第 3 號文件末尾后就回到 0 號文件開頭。checkpoint 是當(dāng)前要擦除的位置，也是往后推移并且循環(huán)的，擦除記錄前要把對應(yīng)數(shù)據(jù)記錄更新到數(shù)據(jù)文件。write pos 和 checkpoint 之間的是“粉板”上還空著的部分，可以用來記錄新的操作。如果 write pos 追上 checkpoint，表示“粉板”滿了，這時候不能再執(zhí)行新的更新，得停下來先進(jìn)行刷盤，然后擦掉對應(yīng)的redo log記錄，把 checkpoint 推進(jìn)一下。有了 redo log，InnoDB 就可以保證即使數(shù)據(jù)庫發(fā)生異常重啟，之前提交的記錄都不會丟失，這個能力稱為 crash-safe。

bin log

為什么會有兩份日志呢？因為最開始 MySQL 里并沒有 InnoDB 引擎。MySQL 自帶的引擎是 MyISAM，但是 MyISAM 沒有 crash-safe 的能力，binlog 日志只能用于歸檔。而 InnoDB 是另一個公司以插件形式引入 MySQL 的，既然只依靠 binlog 是沒有 crash-safe 能力的，所以 InnoDB 使用另外一套日志系統(tǒng)——也就是 redo log 來實現(xiàn) crash-safe 能力。
這兩種日志有以下三點不同：

1. redo log 是 InnoDB 引擎特有的；binlog 是 MySQL 的 Server 層實現(xiàn)的，所有引擎都可以使用。
2. redo log 是物理日志；binlog 是邏輯日志，記錄的是這個語句的原始邏輯。
3. redo log 是循環(huán)寫的，空間固定會用完；binlog 是可以追加寫入的?！白芳訉憽笔侵?binlog 文件寫到一定大小后會切換到下一個，并不會覆蓋以前的日志。

有了對這兩個日志的概念性理解，我們再來看執(zhí)行器和 InnoDB 引擎在執(zhí)行這個簡單的 update 語句（update T set c=c+1 where ID=2）時的內(nèi)部流程。

1. 執(zhí)行器先找引擎取 ID=2 這一行。ID 是主鍵，引擎直接用樹搜索找到這一行。如果 ID=2 這一行所在的數(shù)據(jù)頁本來就在內(nèi)存中，就直接返回給執(zhí)行器；否則，需要先從磁盤讀入內(nèi)存，然后再返回。
2. 執(zhí)行器拿到引擎給的行數(shù)據(jù)，把這個值加上 1，比如原來是 N，現(xiàn)在就是 N+1，得到新的一行數(shù)據(jù)，再調(diào)用引擎接口寫入這行新數(shù)據(jù)。
3. 引擎將這行新數(shù)據(jù)更新到內(nèi)存中，同時將這個更新操作記錄到 redo log 里面，此時 redo log 處于 prepare 狀態(tài)。然后告知執(zhí)行器執(zhí)行完成了，隨時可以提交事務(wù)。
4. 執(zhí)行器生成這個操作的 binlog，并把 binlog 寫入磁盤。執(zhí)行器調(diào)用引擎的提交事務(wù)接口，引擎把剛剛寫入的 redo log 改成提交（commit）狀態(tài)，更新完成。

update語句的執(zhí)行流程.PNG

更新語句流程圖.png

如上圖，是更新一條數(shù)據(jù)的流程，紅色的圈中有三個異常點。

異常1發(fā)生時，也就是寫入redo log失敗,這個時候redo和binlog都沒有，事務(wù)未提交，無任何影響。

異常2發(fā)生時，redo log寫入成功，binlog寫入失敗，這個時候MySQL服務(wù)器重啟后，需要檢查binlog是否完整包含此條redo log的更新內(nèi)容（通過全局事務(wù)ID對應(yīng)),發(fā)現(xiàn)binlog中還未包含此事務(wù)變更，則丟棄此次變更。

異常3發(fā)生時，redo log和binlog都寫入成功，但提交事務(wù)時MySQL進(jìn)程崩潰了，那么服務(wù)器重啟后，檢測到redo log和binlog都有，重新提交事務(wù)。

更新語句的完整過程.png

相關(guān)參數(shù)：innodb_flush_log_at_trx_commit 這個參數(shù)設(shè)置成 1 的時候，表示每次事務(wù)的 redo log 都直接持久化到磁盤。這個參數(shù)建議設(shè)置成 1，這樣可以保證 MySQL 異常重啟之后數(shù)據(jù)不丟失，也可以設(shè)置為2。
sync_binlog這個參數(shù)設(shè)置成 1 的時候，表示每次事務(wù)的 binlog 都持久化到磁盤。這個參數(shù)建議設(shè)置成 1，這樣可以保證 MySQL 異常重啟之后 binlog 不丟失。

事務(wù)隔離

MVCC：
RC：在事務(wù)里，每次使用普通select時，會生成一個ReadView，里面有4個重要的字段：
m_ids：存儲當(dāng)前活躍的事務(wù)ID
min_trx_id：m_ids中最小的事務(wù)ID
max_trx_id：下一個即將分配的事務(wù)ID
creator_trx_id：生成當(dāng)前ReadView的事務(wù)ID
如果待查詢記錄的事務(wù)ID等于creator_trx_id，則該記錄可見；如果事務(wù)ID小于min_trx_id，則可見；如果事務(wù)ID大于等于max_trx_id，則不可見；除此之外，若事務(wù)ID存在于mid_s，則不可見，反之可見。
RR：只會在第一次select生成ReadView，而RC是每次select都會生成一個新的ReadView。使用“可重復(fù)讀”隔離級別，事務(wù)啟動時的視圖可以認(rèn)為是靜態(tài)的，不受其他事務(wù)更新的影響。

查看當(dāng)前隔離級別：show variables like 'transaction_isolation';

盡量不要使用長事務(wù)。長事務(wù)意味著系統(tǒng)里面會存在很老的事務(wù)視圖。由于這些事務(wù)隨時可能訪問數(shù)據(jù)庫里面的任何數(shù)據(jù)，所以這個事務(wù)提交之前，數(shù)據(jù)庫里面它可能用到的回滾記錄都必須保留，這就會導(dǎo)致大量占用存儲空間。除了對回滾段的影響，長事務(wù)還占用鎖資源，也可能拖垮整個庫。

tip：更新數(shù)據(jù)都是先讀后寫的，而這個讀，只能讀當(dāng)前的值，稱為“當(dāng)前讀”（current read）；當(dāng)前讀總是讀取已經(jīng)提交完成的最新版本。

MySQL 的事務(wù)啟動方式有以下幾種：

1. 顯式啟動事務(wù)語句， begin 或 start transaction。配套的提交語句是 commit，回滾語句是 rollback。
2. set autocommit=0，這個命令會將這個線程的自動提交關(guān)掉。意味著如果你只執(zhí)行一個 select 語句，這個事務(wù)就啟動了，而且并不會自動提交。這個事務(wù)持續(xù)存在直到你主動執(zhí)行 commit 或 rollback 語句，或者斷開連接。

建議總是使用 set autocommit=1, 通過顯式語句的方式來啟動事務(wù)。

可以在 information_schema 庫的 innodb_trx 這個表中查詢長事務(wù)，比如下面這個語句，用于查找持續(xù)時間超過 60s 的事務(wù)：select * from information_schema.innodb_trx where TIME_TO_SEC(timediff(now(),trx_started))>60

索引

三星索引：
第一顆星需要取出所有等值謂詞中的列，作為索引開頭的最開始的列（任意順序）；
第二顆星需要將 ORDER BY 列加入索引中；
第三顆星需要將查詢語句剩余的列全部加入到索引中；

最左前綴原則：

檢查索引列，從左到右依次檢查索引列，查看以下規(guī)則:

在where子句中，該列是否至少擁有一個等值謂詞與之對應(yīng)？如果有，則這個列就是匹配列。如果沒有，那么這個列及其后面的索引列都是非匹配列。

謂詞是否是一個范圍謂詞，如果是，那么剩余的索引列都是非匹配列。

對于最后一個匹配列之后的索引列，如果擁有一個足夠簡單的謂詞與其對應(yīng)，那么該列為過濾列。

索引匹配.PNG

鎖

全局鎖

全局鎖就是對整個數(shù)據(jù)庫實例加鎖。MySQL 提供了一個加全局讀鎖的方法，命令是 Flush tables with read lock (FTWRL)。當(dāng)你需要讓整個庫處于只讀狀態(tài)的時候，可以使用這個命令，之后其他線程的以下語句會被阻塞：數(shù)據(jù)更新語句（數(shù)據(jù)的增刪改）、數(shù)據(jù)定義語句（包括建表、修改表結(jié)構(gòu)等）和更新類事務(wù)的提交語句。

全局鎖的典型使用場景是，做全庫邏輯備份。也就是把整庫每個表都 select 出來存成文本。以前有一種做法，是通過 FTWRL 確保不會有其他線程對數(shù)據(jù)庫做更新，然后對整個庫做備份。注意，在備份過程中整個庫完全處于只讀狀態(tài)。

MySQL自帶的邏輯備份工具是 mysqldump。當(dāng) mysqldump 使用參數(shù)single-transaction的時候，導(dǎo)數(shù)據(jù)之前就會啟動一個事務(wù)，來確保拿到一致性視圖。而由于 MVCC 的支持，這個過程中數(shù)據(jù)是可以正常更新的。

全局鎖主要用在邏輯備份過程中。對于全部是 InnoDB 引擎的庫，建議選擇使用single-transaction參數(shù)，對應(yīng)用會更友好。

表級鎖

表鎖
表鎖的語法是 lock tables … read/write。與 FTWRL 類似，可以用 unlock tables 主動釋放鎖，也可以在客戶端斷開的時候自動釋放。需要注意，lock tables 語法除了會限制別的線程的讀寫外，也限定了本線程接下來的操作對象：對表加讀鎖后，自己也不能對其進(jìn)行修改；自己和其他線程只能讀取該表。 當(dāng)對某個表執(zhí)加上寫鎖后（lock table t2 write），該線程可以對這個表進(jìn)行讀寫，其他線程對該表的讀和寫都受到阻塞；

元數(shù)據(jù)鎖（meta data lock，MDL)
在 MySQL 5.5 版本中引入了 MDL，當(dāng)對一個表做增刪改查操作的時候，加 MDL 讀鎖；當(dāng)要對表做結(jié)構(gòu)變更操作的時候，加 MDL 寫鎖。讀鎖之間不互斥，因此你可以有多個線程同時對一張表增刪改查。讀寫鎖之間、寫鎖之間是互斥的，用來保證變更表結(jié)構(gòu)操作的安全性。因此，如果有兩個線程要同時給一個表加字段，其中一個要等另一個執(zhí)行完才能開始執(zhí)行。

元數(shù)據(jù)鎖是server層的鎖，表級鎖，主要用于隔離DML（Data Manipulation Language，數(shù)據(jù)操縱語言，如select）和DDL（Data Definition Language，數(shù)據(jù)定義語言，如改表頭新增一列）操作之間的干擾。每執(zhí)行一條DML、DDL語句時都會申請MDL鎖，DML操作需要MDL讀鎖，DDL操作需要MDL寫鎖（MDL加鎖過程是系統(tǒng)自動控制，無法直接干預(yù)，讀讀共享，讀寫互斥，寫寫互斥）

申請MDL鎖的操作會形成一個隊列，隊列中寫鎖獲取優(yōu)先級高于讀鎖。一旦出現(xiàn)寫鎖等待，不但當(dāng)前操作會被阻塞，同時還會阻塞后續(xù)該表的所有操作。

事務(wù)中的 MDL 鎖，在語句執(zhí)行開始時申請，但是語句結(jié)束后并不會馬上釋放，而會等到整個事務(wù)提交后再釋放。

如何安全地給小表加字段？：
首先我們要解決長事務(wù)，事務(wù)不提交，就會一直占著 MDL 鎖。在 MySQL 的 information_schema 庫的 innodb_trx 表中，你可以查到當(dāng)前執(zhí)行中的事務(wù)。如果你要做 DDL 變更的表剛好有長事務(wù)在執(zhí)行，要考慮先暫停 DDL，或者 kill 掉這個長事務(wù)。
但考慮一下這個場景。如果你要變更的表是一個熱點表，雖然數(shù)據(jù)量不大，但是上面的請求很頻繁，而你不得不加個字段，你該怎么做呢？這時候 kill 可能未必管用，因為新的請求馬上就來了。比較理想的機(jī)制是，在 alter table 語句里面設(shè)定等待時間，如果在這個指定的等待時間里面能夠拿到 MDL 寫鎖最好，拿不到也不要阻塞后面的業(yè)務(wù)語句，先放棄。之后開發(fā)人員或者 DBA 再通過重試命令重復(fù)這個過程。

意向鎖

意向鎖其實不會阻塞全表掃描之外的任何請求，它們的主要目的是為了表示是否有人請求鎖定表中的某一行數(shù)據(jù)。
可以舉一個例子：如果沒有意向鎖，當(dāng)已經(jīng)有人使用行鎖對表中的某一行進(jìn)行修改時，如果另外一個請求要對全表進(jìn)行修改，那么就需要對所有的行是否被鎖定進(jìn)行掃描，在這種情況下，效率是非常低的；不過，在引入意向鎖之后，當(dāng)有人使用行鎖對表中的某一行進(jìn)行修改之前，會先為表添加意向互斥鎖（IX），再為行記錄添加互斥鎖（X），在這時如果有人嘗試對全表進(jìn)行修改就不需要判斷表中的每一行數(shù)據(jù)是否被加鎖了，只需要通過等待意向互斥鎖被釋放就可以了。

行鎖

兩階段鎖
在 InnoDB 事務(wù)中，行鎖是在需要的時候才加上的，但并不是不需要了就立刻釋放，而是要等到事務(wù)結(jié)束時才釋放。這個就是兩階段鎖協(xié)議。
如果你的事務(wù)中需要鎖多個行，要把最可能造成鎖沖突、最可能影響并發(fā)度的鎖盡量往后放。

死鎖和死鎖檢測
當(dāng)出現(xiàn)死鎖以后，有兩種策略：
一種策略是，直接進(jìn)入等待，直到超時。這個超時時間可以通過參數(shù) innodb_lock_wait_timeout 來設(shè)置。
另一種策略是，發(fā)起死鎖檢測，發(fā)現(xiàn)死鎖后，主動回滾死鎖鏈條中的某一個事務(wù)，讓其他事務(wù)得以繼續(xù)執(zhí)行。將參數(shù) innodb_deadlock_detect設(shè)置為 on，表示開啟這個邏輯。

在 InnoDB 中，innodb_lock_wait_timeout 的默認(rèn)值是 50s，意味著如果采用第一個策略，當(dāng)出現(xiàn)死鎖以后，第一個被鎖住的線程要過 50s 才會超時退出，然后其他線程才有可能繼續(xù)執(zhí)行。對于在線服務(wù)來說，這個等待時間往往是無法接受的。

主動死鎖檢測，innodb_deadlock_detect 的默認(rèn)值是 on。主動死鎖檢測在發(fā)生死鎖的時候，是能夠快速發(fā)現(xiàn)并進(jìn)行處理的，但是它也是有額外負(fù)擔(dān)的。

漫日志查詢配置：

slow_query_log=on
long_query_time=1
slow_query_log_file=/path/to/log

相關(guān)問題

如何將數(shù)據(jù)庫恢復(fù)到指定的某一時刻?

1. 首先，找到最近的一次全量備份，如果你運(yùn)氣好，可能就是昨天晚上的一個備份，從這個備份恢復(fù)到臨時庫；
2. 然后，從備份的時間點開始，將備份的 binlog 依次取出來，重放到中午誤刪表之前的那個時刻。

定期全量備份的周期“取決于系統(tǒng)重要性，有的是一天一備，有的是一周一備”。那么在什么場景下，一天一備會比一周一備更有優(yōu)勢呢？或者說，它影響了這個數(shù)據(jù)庫系統(tǒng)的哪個指標(biāo)？
在一天一備的模式里，最壞情況下需要應(yīng)用一天的 binlog。比如，你每天 0 點做一次全量備份，而要恢復(fù)出一個到昨天晚上 23 點的備份。一周一備最壞情況就要應(yīng)用一周的 binlog 了。系統(tǒng)的對應(yīng)指標(biāo)就是 RTO（恢復(fù)目標(biāo)時間）。當(dāng)然這個是有成本的，因為更頻繁全量備份需要消耗更多存儲空間，所以這個 RTO 是成本換來的，就需要你根據(jù)業(yè)務(wù)重要性來評估了。

如何刪除一個表里面的前 10000 行數(shù)據(jù)？
在一個連接中循環(huán)執(zhí)行 20 次 delete from T limit 500

唯一索引與普通索引

查詢時普通索引跟唯一索引執(zhí)行上的區(qū)別： 普通索引的等值查詢，會繼續(xù)遍歷到第一個不相等的值才會結(jié)束，而唯一索引等值查詢，命中則結(jié)束（二者性能差距微乎其微）。

change buffer
當(dāng)需要更新一個數(shù)據(jù)頁時，如果數(shù)據(jù)頁在內(nèi)存中就直接更新，而如果這個數(shù)據(jù)頁還沒有在內(nèi)存中的話，在不影響數(shù)據(jù)一致性的前提下，InnoDB 會將這些更新操作緩存在 change buffer 中，這樣就不需要從磁盤中讀入這個數(shù)據(jù)頁了。在下次查詢需要訪問這個數(shù)據(jù)頁的時候，將數(shù)據(jù)頁讀入內(nèi)存，然后執(zhí)行 change buffer 中與這個頁有關(guān)的操作。通過這種方式就能保證這個數(shù)據(jù)邏輯的正確性。
需要說明的是，雖然名字叫作 change buffer，實際上它是可以持久化的數(shù)據(jù)。也就是說，change buffer 在內(nèi)存中有拷貝，也會被寫入到磁盤上。
將 change buffer 中的操作應(yīng)用到原數(shù)據(jù)頁，得到最新結(jié)果的過程稱為 merge。除了訪問這個數(shù)據(jù)頁會觸發(fā) merge 外，系統(tǒng)有后臺線程會定期 merge。在數(shù)據(jù)庫正常關(guān)閉（shutdown）的過程中，也會執(zhí)行 merge 操作。
change buffer 用的是 buffer pool 里的內(nèi)存，因此不能無限增大。change buffer 的大小，可以通過參數(shù) innodb_change_buffer_max_size來動態(tài)設(shè)置。這個參數(shù)設(shè)置為 50 的時候，表示 change buffer 的大小最多只能占用 buffer pool 的 50%。
change buffer 只限于用在普通索引的場景下，而不適用于唯一索引。
對于寫多讀少的業(yè)務(wù)來說，頁面在寫完以后馬上被訪問到的概率比較小，此時 change buffer 的使用效果最好。如果所有的更新后面，都馬上伴隨著對這個記錄的查詢，那么你應(yīng)該關(guān)閉 change buffer（innodb_change_buffer_max_size設(shè)置為0）。

InnoDB 刷臟頁

InnoDB刷臟頁算法.png

根據(jù)上述算得的 F1(M) 和 F2(N) 兩個值，取其中較大的值記為 R，之后引擎就可以按照 innodb_io_capacity 定義的能力乘以 R% 來控制刷臟頁的速度，這個參數(shù)建議設(shè)置成磁盤的 IOPS。
磁盤的 IOPS 可以通過 fio 這個工具來測試，下面的語句是我用來測試磁盤隨機(jī)讀寫的命令：

fio -filename=$filename -direct=1 -iodepth 1 -thread -rw=randrw -ioengine=psync -bs=16k -size=500M -numjobs=10 -runtime=10 -group_reporting -name=mytest 

參數(shù) innodb_max_dirty_pages_pct是臟頁比例上限，默認(rèn)值是 75%。InnoDB 會根據(jù)當(dāng)前的臟頁比例（假設(shè)為 M），算出一個范圍在 0 到 100 之間的數(shù)字，計算這個數(shù)字的偽代碼類似這樣：

F1(M)
{
  if M>=innodb_max_dirty_pages_pct then
      return 100;
  return 100*M/innodb_max_dirty_pages_pct;
}

InnoDB在刷臟頁時還有一個機(jī)制：在準(zhǔn)備刷一個臟頁的時候，如果這個數(shù)據(jù)頁旁邊的數(shù)據(jù)頁剛好是臟頁，就會把這個“鄰居”也帶著一起刷掉；而且這個把“鄰居”拖下水的邏輯還可以繼續(xù)蔓延，也就是對于每個鄰居數(shù)據(jù)頁，如果跟它相鄰的數(shù)據(jù)頁也還是臟頁的話，也會被放到一起刷。innodb_flush_neighbors 參數(shù)就是用來控制這個行為的，值為 1 的時候會有上述的“連坐”機(jī)制，值為 0 時表示不找鄰居，自己刷自己的。如果使用的是 SSD 這類 IOPS 比較高的設(shè)備的話，建議把 innodb_flush_neighbors 的值設(shè)置成 0。因為這時候 IOPS 往往不是瓶頸，而“只刷自己”，就能更快地執(zhí)行完必要的刷臟頁操作，減少 SQL 語句響應(yīng)時間。

什么時候會觸發(fā)flush：1.redo log日志寫滿了； 2.有新的內(nèi)存也需要讀入內(nèi)存而內(nèi)存不夠用了，這時候需要刷臟頁； 3.系統(tǒng)空閑的時候； 4.mysql正常關(guān)閉的時候強(qiáng)制刷新臟頁。
而第一種情況是要避免的，因為出現(xiàn)這種情況的時候，整個系統(tǒng)就不能再接受更新了，所有的更新都必須堵住。如果你從監(jiān)控上看，這時候更新數(shù)會跌為 0。第二種情況其實是常態(tài)，但如果一個查詢要淘汰的臟頁個數(shù)太多，會導(dǎo)致查詢的響應(yīng)時間明顯變長，也要盡量避免；

一個內(nèi)存配置為 128GB、innodb_io_capacity 設(shè)置為 20000 的大規(guī)格實例，正常會建議你將 redo log 設(shè)置成 4 個 1GB 的文件。

重建表

1：為啥刪除了表的一半數(shù)據(jù)，表文文件大小沒變化？

因為delete 命令其實只是把記錄的位置，或者數(shù)據(jù)頁標(biāo)記為了“可復(fù)用”，但磁盤文件的大小是不會變的。也可以認(rèn)為是一種邏輯刪除，所以物理空間沒有實際釋放，只是標(biāo)記為可復(fù)用，表文件的大小當(dāng)然是不變的！

2：表的數(shù)據(jù)信息存在哪里？

表數(shù)據(jù)信息可能較小也可能巨大無比，可以存儲在共享表空間里，也可以單獨存儲在一個以.ibd為后綴的文件里，由參數(shù)innodb_file_per_table來控制，建議總是作為一個單獨的文件來存儲，這樣非常容易管理，并且在不需要的時候，使用drop table命令也能直接把對應(yīng)的文件刪除，如果存儲在共享空間之中即使表刪除了空間也不會釋放。

實際上不只是刪除數(shù)據(jù)會造成“空洞”，如果插入數(shù)據(jù)不是按照聚集索引遞增的順序插入的，可能會造成數(shù)據(jù)頁分裂，進(jìn)而造成“空洞”。
也就是說，經(jīng)過大量增刪改的表，都是可能是存在空洞的。所以，如果能夠把這些空洞去掉，就能達(dá)到收縮表空間的目的。而重建表，就可以達(dá)到這樣的目的。

重建表命令：alter table A engine=InnoDB
在MySQL5.5之前，這個重建過程是離線的，也就是說在這個過程中不能有新數(shù)據(jù)寫入，否則會造成數(shù)據(jù)丟失。
而在MySQL5.6開始引入了Online DDL，對這個造作流程做了優(yōu)化。

COUNT(*)

按照效率排序的話，count(字段)<count(主鍵 id)<count(1)≈count()，所以盡量使用 count()。

oder by

MySQL 會給每個線程分配一塊內(nèi)存用于排序，稱為 sort_buffer。而按排序這個動作，可能在內(nèi)存中完成，也可能需要使用外部排序，這取決于排序所需的內(nèi)存和參數(shù)sort_buffer_size。如果要排序的數(shù)據(jù)量小于 sort_buffer_size，排序就在內(nèi)存中完成。但如果排序數(shù)據(jù)量太大，內(nèi)存放不下，則不得不利用磁盤臨時文件輔助排序。

如果待排序行的長度超過max_length_for_sort_data，則MySQL會采用rowid 排序。

如果 MySQL 實在是擔(dān)心排序內(nèi)存太小，會影響排序效率，才會采用 rowid 排序算法，這樣排序過程中一次可以排序更多行，但是需要再回到原表去取數(shù)據(jù)。如果 MySQL 認(rèn)為內(nèi)存足夠大，會優(yōu)先選擇全字段排序，把需要的字段都放到 sort_buffer 中，這樣排序后就會直接從內(nèi)存里面返回查詢結(jié)果了，不用再回到原表去取數(shù)據(jù)。

可以看出，排序是有消耗的，但如果數(shù)據(jù)在磁盤本身就是按序存儲的，則不需要再排序了，見三星索引。

Next Key

next_key.PNG

主從備份

從庫建議設(shè)置成只讀模式。因為 readonly 設(shè)置對超級 (super) 權(quán)限用戶是無效的，而用于同步更新的線程，就擁有超級權(quán)限，所以不用擔(dān)心從庫設(shè)置成只讀模式后無法同步更新。

主備流程圖

備庫 B 跟主庫 A 之間維持了一個長連接。主庫 A 內(nèi)部有一個線程，專門用于服務(wù)備庫 B 的這個長連接。一個事務(wù)日志同步的完整過程是這樣的：

1. 設(shè)置主庫相關(guān)配置文件，主要是server-id和log-bin，然后重啟。
2. 登錄主庫，創(chuàng)建slave賬號，并進(jìn)行相關(guān)授權(quán)：CREATE USER 'slave'@'%' IDENTIFIED BY '123456'; GRANT REPLICATION SLAVE, REPLICATION CLIENT ON *.* TO 'slave'@'%';
3. 設(shè)置備庫相關(guān)配置文件，主要是server-id和log-bin、relay_log，然后重啟。
4. 在備庫 B 上通過 change master 命令，設(shè)置主庫 A 的 IP、端口、用戶名、密碼，以及要從哪個位置開始請求 binlog，這個位置包含文件名和日志偏移量：change master to master_host='172.17.0.2', master_user='slave', master_password='123456', master_port=3306, master_log_file='mysql-bin.000001', master_log_pos= 2830, master_connect_retry=30;。
5. 在備庫 B 上執(zhí)行 start slave 命令，這時候備庫會啟動兩個線程，就是圖中的 io_thread 和 sql_thread。其中 io_thread 負(fù)責(zé)與主庫建立連接。
6. 主庫 A 校驗完用戶名、密碼后，開始按照備庫 B 傳過來的位置，從本地讀取 binlog，發(fā)給 B。
7. 備庫 B 拿到 binlog 后，寫到本地文件，稱為中轉(zhuǎn)日志（relay log）。
8. sql_thread 讀取中轉(zhuǎn)日志，解析出日志里的命令，并執(zhí)行。
   這里需要說明，后來由于多線程復(fù)制方案的引入，sql_thread 演化成為了多個線程。

==========以下是主庫配置文件中的信息=============
# 配置編碼為utf8
character_set_server=utf8mb4
init_connect='SET NAMES utf8mb4'

# 配置要給Slave同步的數(shù)據(jù)庫
binlog-do-db=test
# 不用給Slave同步的數(shù)據(jù)庫，一般是Mysql自帶的數(shù)據(jù)庫就不用給Slave同步了
binlog-ignore-db=mysql
binlog-ignore-db=information_schema
binlog-ignore-db=performance_schema
binlog-ignore-db=sys
# 自動清理30天前的log文件
expire_logs_days=30
# 啟用二進(jìn)制日志
log-bin=mysql-bin
# Master的id，這個要唯一，唯一是值，在主從中唯一
server-id=3

binlog 的三種格式：
statement、row、mixed

statement 格式的 binlog 可能會導(dǎo)致主備不一致，row 格式的缺點是很占空間，mixed是先判斷這條 SQL 語句是否可能引起主備不一致，如果有可能，就用 row 格式，否則就用 statement 格式。

如果線上 MySQL 設(shè)置的 binlog 格式是 statement 的話，那基本上就可以認(rèn)為這是一個不合理的設(shè)置，現(xiàn)在越來越多的場景要求把 MySQL 的 binlog 格式設(shè)置成 row。

實際生產(chǎn)上使用比較多的是互為主備。但互為主備有一個binlog循環(huán)復(fù)制的問題。（建議把參數(shù)log_slave_updates設(shè)置為 on，表示備庫執(zhí)行 relay log 后生成 binlog）。解決方法是：

1. 從節(jié)點 A 更新的事務(wù)，binlog 里面記的都是 A 的 server id；
2. 傳到節(jié)點 B 執(zhí)行一次以后，節(jié)點 B 生成的 binlog 的 server id 也是 A 的 server id；
3. 再傳回給節(jié)點 A，A 判斷到這個 server id 與自己的相同，就不會再處理這個日志。所以，死循環(huán)在這里就斷掉了。

主備延遲

可以在備庫上執(zhí)行 show slave status 命令，它的返回結(jié)果里面會顯示 seconds_behind_master，用于表示當(dāng)前備庫延遲了多少秒。網(wǎng)絡(luò)正常情況下，主備延遲的主要來源是備庫從接收完 binlog起到執(zhí)行完這個事務(wù)之間的時間差。造成主備延遲的原因可能有一下：

1. 大家都把查詢鏈接打到了備庫上，導(dǎo)致備庫cpu增高，同步binlog變慢。解決辦法可以是一主多從，除了備庫外，可以多接幾個從庫，讓這些從庫來分擔(dān)讀的壓力。或者增加緩存中間件。
2. 長事務(wù)也可能造成延遲，要避免長事務(wù)。
3. 如果在進(jìn)行大表的DDL也會造成延遲。

在官方的 5.6 版本之前，MySQL 只支持單線程復(fù)制，由此在主庫并發(fā)高、TPS 高時就會出現(xiàn)嚴(yán)重的主備延遲問題。之后就是多線程復(fù)制，線程個數(shù)由參數(shù)slave_parallel_workers決定，根據(jù)經(jīng)驗，把這個值設(shè)置為 8~16 之間最好（32 核物理機(jī)的情況），畢竟備庫還有可能要提供讀查詢，不能把 CPU 都吃光了。

主備切換：

1. 判斷備庫 B 現(xiàn)在的 seconds_behind_master，如果小于某個值（比如 5 秒）繼續(xù)下一步，否則持續(xù)重試這一步；
2. 把主庫 A 改成只讀狀態(tài)，即把 readonly 設(shè)置為 true（set global read_only=1;）；
3. 判斷備庫 B 的 seconds_behind_master 的值，直到這個值變成 0 為止；
4. 把備庫 B 改成可讀寫狀態(tài)，也就是把 readonly 設(shè)置為 false；
5. 把業(yè)務(wù)請求切到備庫 B。

可以看到，這個切換流程中是有不可用時間的。因為在步驟 2 之后，主庫 A 和備庫 B 都處于 readonly 狀態(tài)，也就是說這時系統(tǒng)處于不可寫狀態(tài)，直到步驟 5 完成后才能恢復(fù)。

在這個不可用狀態(tài)中，比較耗費(fèi)時間的是步驟 3，可能需要耗費(fèi)好幾秒的時間。這也是為什么需要在步驟 1 先做判斷，確保 seconds_behind_master 的值足夠小。試想如果一開始主備延遲就長達(dá) 30 分鐘，而不先做判斷直接切換的話，系統(tǒng)的不可用時間就會長達(dá) 30 分鐘，這種情況一般業(yè)務(wù)都是不可接受的。

GTID

GTID 的全稱是 Global Transaction Identifier，也就是全局事務(wù) ID，是一個事務(wù)在提交的時候生成的，是這個事務(wù)的唯一標(biāo)識。它由兩部分組成，格式是：
GTID=server_uuid:transaction_id
其中：server_uuid 是一個實例第一次啟動時自動生成的，是一個全局唯一的值；transaction_id是一個整數(shù)，初始值是 1，每次提交事務(wù)的時候分配給這個事務(wù)，并加 1。

在 GTID 模式下，每個提交了的事務(wù)都會跟一個 GTID 一一對應(yīng)。這樣，每個 MySQL 實例都維護(hù)了一個 GTID 集合，用來對應(yīng)“這個實例執(zhí)行過的所有事務(wù)”。

這里需要注意的一點是，在MVCC里面我們說transaction_id 就是指事務(wù) id，事務(wù) id 是在事務(wù)執(zhí)行過程中分配的，如果這個事務(wù)回滾了，事務(wù) id 也會遞增；而這里的transation_id是在事務(wù)提交的時候才會分配。

GTID 模式的啟動也很簡單，我們只需要在啟動一個 MySQL 實例的時候，加上參數(shù) gtid_mode=on 和 enforce_gtid_consistency=on 就可以了。

基于 GTID 的主備切換

image.png

捕獲.PNG

由于不需要找位點了，所以從庫 B、C、D 只需要分別執(zhí)行 change master 命令指向?qū)嵗?A’即可。

讀寫分離

讀寫分離基本結(jié)構(gòu)1

讀寫分離基本結(jié)構(gòu)2

1. 客戶端直連方案，因為少了一層 proxy 轉(zhuǎn)發(fā)，所以查詢性能稍微好一點兒，并且整體架構(gòu)簡單，排查問題更方便。但是這種方案，由于要了解后端部署細(xì)節(jié)，所以在出現(xiàn)主備切換、庫遷移等操作的時候，客戶端都會感知到，并且需要調(diào)整數(shù)據(jù)庫連接信息。你可能會覺得這樣客戶端也太麻煩了，信息大量冗余，架構(gòu)很丑。其實也未必，一般采用這樣的架構(gòu)，一定會伴隨一個負(fù)責(zé)管理后端的組件，比如 Zookeeper，盡量讓業(yè)務(wù)端只專注于業(yè)務(wù)邏輯開發(fā)。
2. 帶 proxy 的架構(gòu)，對客戶端比較友好?？蛻舳瞬恍枰P(guān)注后端細(xì)節(jié)，連接維護(hù)、后端信息維護(hù)等工作，都是由 proxy 完成的。但這樣的話，對后端維護(hù)團(tuán)隊的要求會更高。而且，proxy 也需要有高可用架構(gòu)。因此，帶 proxy 架構(gòu)的整體就相對比較復(fù)雜。

理解了這兩種方案的優(yōu)劣，具體選擇哪個方案就取決于數(shù)據(jù)庫團(tuán)隊提供的能力了。但目前看，趨勢是往帶 proxy 的架構(gòu)方向發(fā)展的。

不論使用哪種架構(gòu)，都可能會碰到一個問題：由于主從可能存在延遲，客戶端執(zhí)行完一個更新事務(wù)后馬上發(fā)起查詢，如果查詢選擇的是從庫的話，就有可能讀到剛剛的事務(wù)更新之前的狀態(tài)。
解決方案：

1. 強(qiáng)制走主庫方案；

• 對于必須要拿到最新結(jié)果的請求，強(qiáng)制將其發(fā)到主庫上。比如，在一個交易平臺上，賣家發(fā)布商品以后，馬上要返回主頁面，看商品是否發(fā)布成功。那么，這個請求需要拿到最新的結(jié)果，就必須走主庫。
• 對于可以讀到舊數(shù)據(jù)的請求，才將其發(fā)到從庫上。在這個交易平臺上，買家來逛商鋪頁面，就算晚幾秒看到最新發(fā)布的商品，也是可以接受的。那么，這類請求就可以走從庫。

2. sleep 方案；
   主庫更新后，讀從庫之前先 sleep 一下。具體的方案就是，類似于執(zhí)行一條 select sleep(1) 命令。
3. 判斷主備無延遲方案；
   第一種確保主備無延遲的方法是，每次從庫執(zhí)行查詢請求前，先判斷 seconds_behind_master 是否已經(jīng)等于 0。如果還不等于 0 ，那就必須等到這個參數(shù)變?yōu)?0 才能執(zhí)行查詢請求。
4. 配合 semi-sync 方案；
5. 等主庫位點方案；
6. 等 GTID 方案。

在一主一備的雙 M 架構(gòu)里，主備切換只需要把客戶端流量切到備庫；而在一主多從架構(gòu)里，主備切換除了要把客戶端流量切到備庫外，還需要把從庫接到新主庫上。

如何判斷一個數(shù)據(jù)庫是不是出問題了？

并發(fā)連接和并發(fā)查詢：并發(fā)連接和并發(fā)查詢，并不是同一個概念。你在show processlist的結(jié)果里，看到的幾千個連接，指的就是并發(fā)連接。而“當(dāng)前正在執(zhí)行”的語句，才是我們所說的并發(fā)查詢。

innodb_thread_concurrency參數(shù)的目的是，控制 InnoDB 的并發(fā)線程上限。也就是說，一旦并發(fā)線程數(shù)達(dá)到這個值，InnoDB 在接收到新請求的時候，就會進(jìn)入等待狀態(tài)，直到有線程退出。在 InnoDB 中innodb_thread_concurrency這個參數(shù)的默認(rèn)值是 0，表示不限制并發(fā)線程數(shù)量。但是，不限制并發(fā)線程數(shù)肯定是不行的。因為，一個機(jī)器的 CPU 核數(shù)有限，線程全沖進(jìn)來，上下文切換的成本就會太高。

通常情況下，建議把innodb_thread_concurrency設(shè)置為 64~128 之間的值。

SELECT 1方法：：
問題：select 1 成功返回，只能說明這個庫的進(jìn)程還在，并不能說明主庫沒問題。
更新判斷方法：

mysql> CREATE TABLE `health_check` (
  `id` int(11) NOT NULL,
  `t_modified` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB;

/* 檢測命令 */
insert into mysql.health_check(id, t_modified) values (@@server_id, now()) on duplicate key update t_modified=now();

問題：更新語句，如果失敗或者超時，就可以發(fā)起主備切換了，但會有判定慢的問題。外部檢測都需要定時輪詢，所以系統(tǒng)可能已經(jīng)出問題了，但是卻需要等到下一個檢測發(fā)起執(zhí)行語句的時候，我們才有可能發(fā)現(xiàn)問題。而且，如果你的運(yùn)氣不夠好的話，可能第一次輪詢還不能發(fā)現(xiàn)（因為檢測語句需要的資源十分少，所以有可能正常通過，但實際系統(tǒng)已經(jīng)不可用了），這就會導(dǎo)致切換慢的問題。

內(nèi)部統(tǒng)計方法：
MySQL 5.6 版本以后提供的performance_schema庫，就在file_summary_by_event_name表里統(tǒng)計了每次 IO 請求的時間。

可以通過 MAX_TIMER_WAIT 的值來判斷數(shù)據(jù)庫是否出問題了。比如，你可以設(shè)定閾值，單次 IO 請求時間超過 200 毫秒屬于異常，然后使用類似下面這條語句作為檢測邏輯:

mysql> select event_name,MAX_TIMER_WAIT  FROM performance_schema.file_summary_by_event_name where event_name in ('wait/io/file/innodb/innodb_log_file','wait/io/file/sql/binlog') and MAX_TIMER_WAIT>200*1000000000;

發(fā)現(xiàn)異常后，取到你需要的信息，再通過下面這條語句：

mysql> truncate table performance_schema.file_summary_by_event_name;

把之前的統(tǒng)計信息清空。這樣如果后面的監(jiān)控中，再次出現(xiàn)這個異常，就可以加入監(jiān)控累積值了。

誤刪數(shù)據(jù)庫、表

假如有人中午 12 點誤刪了一個庫，恢復(fù)數(shù)據(jù)的流程如下：

1. 取最近一次全量備份，假設(shè)這個庫是一天一備，上次備份是當(dāng)天 0 點；
2. 用備份恢復(fù)出一個臨時庫；
3. 從日志備份里面，取出凌晨 0 點之后的日志；
4. 把這些日志，除了12點誤刪除數(shù)據(jù)的語句外，全部應(yīng)用到臨時庫。

刪表規(guī)范：

1. 在刪除數(shù)據(jù)表之前，必須先對表做改名操作。然后，觀察一段時間，確保對業(yè)務(wù)無影響以后再刪除這張表。
2. 改表名的時候，要求給表名加固定的后綴（比如加 _to_be_deleted)，然后刪除表的動作必須通過管理系統(tǒng)執(zhí)行。并且，管理系刪除表的時候，只能刪除固定后綴的表。

權(quán)限

創(chuàng)建用戶.PNG

可以用show grants命令查看賬戶的權(quán)限。

全局權(quán)限.PNG

正常情況下，grant 命令之后，沒有必要跟著執(zhí)行 flush privileges 命令。