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

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

連接器

連接器負責(zé)跟客戶端建立連接膨俐、獲取權(quán)限抵窒、維持和管理連接萨咕。在完成經(jīng)典的 TCP 握手后丑孩，連接器就要開始認證你的身份冀宴，這個時候用的就是你輸入的用戶名和密碼。如果用戶名密碼認證通過温学，連接器會到權(quán)限表里面查出你擁有的權(quán)限略贮。之后，這個連接里面的權(quán)限判斷邏輯枫浙，都將依賴于此時讀到的權(quán)限刨肃。這就意味著，一個用戶成功建立連接后箩帚，即使你用管理員賬號對這個用戶的權(quán)限做了修改真友，也不會影響已經(jīng)存在連接的權(quán)限。修改完成后紧帕，只有再新建的連接才會使用新的權(quán)限設(shè)置盔然。客戶端如果太長時間沒動靜是嗜，連接器就會自動將它斷開愈案。這個時間是由參數(shù) wait_timeout 控制的，默認值是 8 小時鹅搪。

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

分析器

優(yōu)化器

執(zhí)行器

MySQL 通過分析器知道了你要做什么站绪，通過優(yōu)化器知道了該怎么做，于是就進入了執(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

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

redo log.PNG

write pos 是當前記錄的位置蚕键，一邊寫一邊后移，寫到第 3 號文件末尾后就回到 0 號文件開頭衰粹。checkpoint 是當前要擦除的位置锣光，也是往后推移并且循環(huán)的，擦除記錄前要把對應(yīng)數(shù)據(jù)記錄更新到數(shù)據(jù)文件铝耻。write pos 和 checkpoint 之間的是“粉板”上還空著的部分誊爹，可以用來記錄新的操作。如果 write pos 追上 checkpoint瓢捉，表示“粉板”滿了频丘，這時候不能再執(zhí)行新的更新，得停下來先進行刷盤泡态，然后擦掉對應(yīng)的redo log記錄搂漠，把 checkpoint 推進一下。有了 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進程崩潰了位谋，那么服務(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：存儲當前活躍的事務(wù)ID
min_trx_id：m_ids中最小的事務(wù)ID
max_trx_id：下一個即將分配的事務(wù)ID
creator_trx_id：生成當前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ù)啟動時的視圖可以認為是靜態(tài)的宇姚，不受其他事務(wù)更新的影響。

查看當前隔離級別：show variables like 'transaction_isolation';

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

tip：更新數(shù)據(jù)都是先讀后寫的桶现，而這個讀躲雅，只能讀當前的值，稱為“當前讀”（current read）骡和；當前讀總是讀取已經(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)。當你需要讓整個庫處于只讀狀態(tài)的時候可帽，可以使用這個命令，之后其他線程的以下語句會被阻塞：數(shù)據(jù)更新語句（數(shù)據(jù)的增刪改）窗怒、數(shù)據(jù)定義語句（包括建表映跟、修改表結(jié)構(gòu)等）和更新類事務(wù)的提交語句。

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

MySQL自帶的邏輯備份工具是 mysqldump。當 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 語法除了會限制別的線程的讀寫外番挺，也限定了本線程接下來的操作對象：對表加讀鎖后，自己也不能對其進行修改屯掖；自己和其他線程只能讀取該表玄柏。 當對某個表執(zhí)加上寫鎖后（lock table t2 write），該線程可以對這個表進行讀寫贴铜，其他線程對該表的讀和寫都受到阻塞粪摘；

元數(shù)據(jù)鎖（meta data lock，MDL)
在 MySQL 5.5 版本中引入了 MDL绍坝，當對一個表做增刪改查操作的時候徘意，加 MDL 讀鎖；當要對表做結(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)寫鎖等待临庇，不但當前操作會被阻塞，同時還會阻塞后續(xù)該表的所有操作昵慌。

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

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

意向鎖

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

行鎖

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

死鎖和死鎖檢測
當出現(xià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 的默認值是 50s革为，意味著如果采用第一個策略，當出現(xiàn)死鎖以后舵鳞，第一個被鎖住的線程要過 50s 才會超時退出震檩，然后其他線程才有可能繼續(xù)執(zhí)行。對于在線服務(wù)來說蜓堕，這個等待時間往往是無法接受的抛虏。

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

漫日志查詢配置：

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

相關(guān)問題

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

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

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

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

唯一索引與普通索引

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

change buffer
當需要更新一個數(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 這個工具來測試，下面的語句是我用來測試磁盤隨機讀寫的命令：

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是臟頁比例上限惕它，默認值是 75%怕午。InnoDB 會根據(jù)當前的臟頁比例（假設(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在刷臟頁時還有一個機制：在準備刷一個臟頁的時候郁惜，如果這個數(shù)據(jù)頁旁邊的數(shù)據(jù)頁剛好是臟頁，就會把這個“鄰居”也帶著一起刷掉甲锡；而且這個把“鄰居”拖下水的邏輯還可以繼續(xù)蔓延兆蕉，也就是對于每個鄰居數(shù)據(jù)頁，如果跟它相鄰的數(shù)據(jù)頁也還是臟頁的話缤沦，也會被放到一起刷恨樟。innodb_flush_neighbors 參數(shù)就是用來控制這個行為的，值為 1 的時候會有上述的“連坐”機制疚俱，值為 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)閉的時候強制刷新臟頁四苇。
而第一種情況是要避免的，因為出現(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ù)頁標記為了“可復(fù)用”恭金，但磁盤文件的大小是不會變的操禀。也可以認為是一種邏輯刪除，所以物理空間沒有實際釋放横腿，只是標記為可復(fù)用颓屑，表文件的大小當然是不變的！

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

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

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

重建表命令：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 認為內(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賬號，并進行相關(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 負責(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
# 啟用二進制日志
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 的話，那基本上就可以認為這是一個不合理的設(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胰蝠，用于表示當前備庫延遲了多少秒。網(wǎng)絡(luò)正常情況下震蒋，主備延遲的主要來源是備庫從接收完 binlog起到執(zhí)行完這個事務(wù)之間的時間差茸塞。造成主備延遲的原因可能有一下：

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

在官方的 5.6 版本之前，MySQL 只支持單線程復(fù)制籍嘹，由此在主庫并發(fā)高闪盔、TPS 高時就會出現(xiàn)嚴重的主備延遲問題弯院。之后就是多線程復(fù)制，線程個數(shù)由參數(shù)slave_parallel_workers決定泪掀，根據(jù)經(jīng)驗听绳，把這個值設(shè)置為 8~16 之間最好（32 核物理機的情況），畢竟備庫還有可能要提供讀查詢异赫，不能把 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)中垦搬，比較耗費時間的是步驟 3，可能需要耗費好幾秒的時間艳汽。這也是為什么需要在步驟 1 先做判斷猴贰，確保 seconds_behind_master 的值足夠小。試想如果一開始主備延遲就長達 30 分鐘河狐，而不先做判斷直接切換的話米绕，系統(tǒng)的不可用時間就會長達 30 分鐘，這種情況一般業(yè)務(wù)都是不可接受的馋艺。

GTID

GTID 的全稱是 Global Transaction Identifier栅干，也就是全局事務(wù) ID，是一個事務(wù)在提交的時候生成的捐祠，是這個事務(wù)的唯一標識碱鳞。它由兩部分組成，格式是：
GTID=server_uuid:transaction_id
其中：server_uuid 是一個實例第一次啟動時自動生成的踱蛀，是一個全局唯一的值窿给；transaction_id是一個整數(shù)贵白，初始值是 1，每次提交事務(wù)的時候分配給這個事務(wù)崩泡，并加 1戒洼。

在 GTID 模式下，每個提交了的事務(wù)都會跟一個 GTID 一一對應(yīng)允华。這樣圈浇，每個 MySQL 實例都維護了一個 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)簡單，排查問題更方便忽肛。但是這種方案村砂，由于要了解后端部署細節(jié)，所以在出現(xiàn)主備切換屹逛、庫遷移等操作的時候础废，客戶端都會感知到，并且需要調(diào)整數(shù)據(jù)庫連接信息煎源。你可能會覺得這樣客戶端也太麻煩了色迂，信息大量冗余，架構(gòu)很丑手销。其實也未必歇僧，一般采用這樣的架構(gòu)，一定會伴隨一個負責(zé)管理后端的組件，比如 Zookeeper诈悍，盡量讓業(yè)務(wù)端只專注于業(yè)務(wù)邏輯開發(fā)祸轮。
2. 帶 proxy 的架構(gòu)，對客戶端比較友好侥钳∈释啵客戶端不需要關(guān)注后端細節(jié)，連接維護舷夺、后端信息維護等工作苦酱，都是由 proxy 完成的。但這樣的話给猾，對后端維護團隊的要求會更高疫萤。而且，proxy 也需要有高可用架構(gòu)敢伸。因此扯饶，帶 proxy 架構(gòu)的整體就相對比較復(fù)雜。

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

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

1. 強制走主庫方案矿微；

• 對于必須要拿到最新結(jié)果的請求，強制將其發(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ā)連接牺弄。而“當前正在執(zhí)行”的語句，才是我們所說的并發(fā)查詢宜狐。

innodb_thread_concurrency參數(shù)的目的是势告，控制 InnoDB 的并發(fā)線程上限。也就是說抚恒，一旦并發(fā)線程數(shù)達到這個值咱台，InnoDB 在接收到新請求的時候，就會進入等待狀態(tài)俭驮，直到有線程退出回溺。在 InnoDB 中innodb_thread_concurrency這個參數(shù)的默認值是 0，表示不限制并發(fā)線程數(shù)量。但是馅而，不限制并發(fā)線程數(shù)肯定是不行的祥诽。因為，一個機器的 CPU 核數(shù)有限瓮恭，線程全沖進來雄坪，上下文切換的成本就會太高。

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

SELECT 1方法：：
問題：select 1 成功返回，只能說明這個庫的進程還在登澜，并不能說明主庫沒問題阔挠。
更新判斷方法：

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)問題晃跺。而且揩局，如果你的運氣不夠好的話，可能第一次輪詢還不能發(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è)這個庫是一天一備翁逞，上次備份是當天 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 命令。