轉自

《葉問》是知數堂新設計的互動欄目，不定期給大家提供技術知識小貼士，形式不限，或提問、或討論均可，並在當天釋出答案，讓大家輕輕鬆鬆利用碎片時間就可以學到最實用的知識點。

知數堂 - 最靠譜、最有品質的培訓品牌

葉問專輯 https://mp.weixin.qq.com/mp/homepage?__biz=MzI1OTU2MDA4NQ%3D%3D&hid=15&sn=8a530aa309c1fe6e4d99b3a0d49a9695

2018年6月10日，週日

MySQL主從複製什麼原因會造成不一致，如何預防及解決？

一、導致主從不一致的原因主要有：

人為原因導致從庫與主庫資料不一致（從庫寫入）
主從複製過程中，主庫異常當機
設定了ignore/do/rewrite等replication等規則
binlog非row格式
非同步複製本身不保證，半同步存在提交讀的問題，增強半同步起來比較完美。但對於異常重啟（Replication Crash Safe），從庫寫資料（GTID）的防範，還需要策略來保證。
從庫中斷很久，binlog應用不連續，監控並及時修復主從
從庫啟用了諸如儲存過程，從庫禁用儲存過程等
資料庫大小版本/分支版本導致資料不一致？，主從版本統一
備份的時候沒有指定引數例如mysqldump --master-data=2 等
主從sql_mode 不一致
一主二從環境，二從的server id一致
MySQL自增列主從不一致
主從資訊儲存在檔案裡面，檔案本身的重新整理是非事務的，導致從庫重啟後開始執行點大於實際執行點
採用5.6的after_commit方式半同步，主庫當機可能會引起主從不一致，要看binlog是否傳到了從庫
啟用增強半同步了（5.7的after_sync方式），但是從庫延遲超時自動切換成非同步複製

二、預防和解決的方案有：

master:innodb_flush_log_at_trx_commit=1&sync_binlog=1
slave:master_info_repository="TABLE"&relay_log_info_repository="TABLE"&relay_log_recovery=1
設定從庫庫為只讀模式
可以使用5.7增強半同步避免資料丟失等
binlog row格式
必須引定期的資料校驗機制
當使用延遲複製的時候，此時主從資料也是不一致的（計劃內），但在切換中，不要把延遲從提升為主庫哦~
mha在主從切換的過程中，因主庫系統當機，可能造成主從不一致（mha本身機制導致這個問題）

2018年6月11日，週一

你為什麼會決定進行分庫分表，分庫分表過程中遇到什麼難題，如何解決的？

一、為什麼決定進行分庫分表？

根據業務型別，和業務容量的評估，來選擇和判斷是否使用分庫分表
當前資料庫本事具有的能力，壓力的評估
資料庫的物理隔離，例如減少鎖的爭用、資源的消耗和隔離等
熱點表較多,並且資料量大，可能會導致鎖爭搶，效能下降
資料庫的高併發，資料庫的讀寫壓力過大，可能會導致資料庫或系統當機
資料庫（MySQL5.7以下）連線數過高，會增加系統壓力
單表資料量大，如SQL使用不當，會導致io隨機讀寫比例高。查詢慢（大表上的B+樹太大，掃描太慢，甚至可能需要4層B+樹）
備份和恢復時間比較長

二、都遇到什麼問題?

全域性pk（主鍵和唯一索引）的衝突檢測不準確，全域性的自增主鍵支援不夠好
分片鍵的選擇。如沒有選擇好，可能會影響SQL執行效率
分散式事務，中間價產品對分散式事務的支援力度
對於開發來說，需要進行業務的拆分
對於開發來說，部分SQL不相容則需要程式碼重構，工作量的評估
對於開發來說，跨庫join，跨庫查詢

三、如何解決?

使用全域性分號器。或者使用全域性唯一id，（應用生成順序唯一int型別做為全域性主鍵）
應用層來判斷唯一索引
配合應用選擇合適的分片鍵，並加上索引
配合應用，配合開發，對不相容SQL的進行整改

2018年6月12日，週二

MySQL高可用架構應該考慮什麼? 你認為應該如何設計？

一、MySQL高可用架構應該考慮什麼？

對業務的瞭解，需要考慮業務對資料庫一致性要求的敏感程度，切換過程中是否有事務會丟失
對於基礎設施的瞭解，需要了解基礎設施的高可用的架構。例如單網線，單電源等情況
對於資料庫故障時間掌握，業務方最多能容忍時間範圍，因為高可用切換導致的應用不可用時間
需要了解主流的高可用的優缺點：例如 MHA/PXC/MGR 等。
考慮多IDC多副本分佈，支援IDC級別節點全部掉線後，業務可以切到另一個機房

二、你認為應該如何設計?

基礎層和基礎運維部門配合，瞭解和避免網路/ 硬碟/ 電源等是否會出現單點故障
應用層和應用開發同學配合，在關鍵業務中記錄SQL日誌，可以做到即使切換，出現丟事務的情況，也可以透過手工補的方式保證資料一致性，例如：交易型的業務引入狀態機，事務狀態，應對資料庫切換後事務重做
業務層瞭解自己的應用，根據不同的應用制定合理的高可用策略。
單機多例項環境及基於虛擬機器或容器的設計不能分佈在同一臺物理機上。
最終大招在資料庫不可用，可以把已提及的事務先儲存到佇列或者其他位置，等資料庫恢復，重新應用

2018年6月13日，週三

MySQL備份，使用xtrabackup備份全例項資料時，會造成鎖等待嗎？那麼如果使用mysqldump進行備份呢？

一、xtrabackup和mysqldump會造成鎖等待嗎？

xtrabackup會，它在備份時會產生短暫的全域性讀鎖FTWL(flush table with read lock)，用於複製frm/MYD/MYI等檔案，以及記錄binlog資訊。如果MyISAM表的資料量非常大，則複製時間就越長，加鎖的時間也越長
mysqldump有可能會。如果只是新增 --single-transacton 選項用於保證備份資料一致性，這時就不會產生FTWL鎖了。但通常我們為了讓備份檔案和binlog保持一致，通常也會設定 --master-data 選項用於獲得當前binlog資訊，這種情況也會短暫加鎖
資料量特別大的話，建議優先用 xtrabackup，提高備份/恢復速度。而如果資料量不是太大或者想備份單表，則建議用mysqldump了，方便邏輯恢復。各有利弊，注意其適用場景

二、xtrabackup冷知識

基於MySQL 5.6版本開發的xtrabackup，會在備份過程中生成內部通訊檔案 suspend file，用於 xtrabackup 和 innobackupex 的通訊，備份結束後檔案刪除，預設檔案位置 /tmp/xtrabackup_suspended
如果在備份過程中，修改了 /tmp 的訪問許可權或該檔案的許可權，則兩個程式間直接不能通訊，會造成 xtrabackup hang 住，正在備份的表不能正常釋放鎖，會造成鎖等待，此時需要強制 kill 掉 xtrabackup 程式

2018年6月15日，週五

MySQL 5.7開始支援JSON，那還有必要使用MongoDB存JSON嗎？請列出你的觀點/理由。

一、觀點A：支援MySQL儲存JSON

1.MongoDB不支援事務，而MySQL支援事務

2.MySQL相對MongoDB而言，MySQL的穩定性要優於MongoDB

3.MySQL支援多種儲存引擎

二、觀點B:支援MongoDB儲存JSON

1.從效能的角度考慮，對於JSON讀寫效率MongoDB要優於MySQL

2.MongoDB相對MySQL而言，MongoDB的擴充套件性要優於MySQL

3.MongoDB支援更多的JSON函式

三、總結

1.如果應用程式無事務要求，儲存資料表結構複雜並且經常被修改，例如遊戲中裝備等場景用MongoDB比較適合

2.如果應用程式有事務要求，儲存資料的"表"之間相互有關聯，例如有訂單系統等場景用MySQL比較適合

3.整體來看相對看好MySQL的JSON功能，在未來官方的努力下MySQL的JSON功能有機會反超MongoDB

2018年6月17日，週日

當資料被誤刪除/誤操作後造成資料丟失。你嘗試過用什麼手段來挽救資料/損失？

一、前提
1.當資料被誤刪除/誤操作後，第一時間要關閉資料庫。業務方需要緊急掛停機公告，避免資料二次汙染，用於保護資料的一致性

2.BINLOG格式為ROW格式，不討論其他格式的BINLOG

二、資料被誤操作(update/delete/drop)造成資料丟失，可以用哪些手段來恢復？

1.BINLOG恢復：可以使用逆向解析BINLOG工具來恢復。例如：binlog2SQL等

2.延遲從庫: 可以透過解除延遲從庫，並指定BINLOG結束位置點，可以實現資料恢復

三、資料被誤刪除(rm/物理檔案損壞)造成資料丟失，可以用哪些手段來恢復？

1.如果有備份，可以透過備份恢復 mysqldump/xtrabackup + binlog 來實現全量+增量恢復

2.如果無備份但是有從庫，可以透過主從切換，提升從庫為主庫，從而實現資料恢復

3.如果無備份並且無從庫，但MySQL沒有重啟，可以透過複製/proc/$pid/fd中的檔案，來進行嘗試恢復

4.如果無備份並且無從庫，但MySQL有重啟，可以透過extundelete或undrop-for-innodb來恢復

2018年6月19日，週二

MySQL 5.7的複製架構,在有非同步複製、半同步、增強半同步、MGR等的生產中，該如何選擇？

一、生產環境中：

幾種複製場景都有存在的價值。下面分別描述一下：

從成熟度上來選擇，推薦：非同步複製(GTID+ROW)
從資料安全及更高效能上選擇：增強半同步（在這個結構下也可以把innodb_flush_log_trx_commit調整到非1，從而獲得更好的效能)
對於主從切換控制覺的不好管理，又對資料一致性要求特別高的場景，可以使用MGR

二、理由：

非同步複製，相對來講非常成熟，對於環境運維也比較容易上手
增強半同步複製，可以安全的保證資料傳輸到從庫上，對於單節點的配置上不用要求太嚴格，特別從庫上也可以更寬鬆一點，而且在一致性和效能有較高的提升，但對運維上有一定的要求
MGR組複製。相對增強半同步複製，MGR更能確保資料的一致性，事務的提交，必須經過組內大多數節點（n/2+1）決議並透過，才能得以提交。MGR架構對運維難度要更高，不過它也更完美

總的來講，從技術實現上來看：MGR> 增強半同步>非同步複製。

未來可能見到更多的MGR在生產中使用，對於MySQL的運維的要求也會更上一層樓。

2018年6月20日，週三

為什麼說pt-osc可能會引起主從延遲，有什麼好辦法解決或規避嗎？

若複製中binlog使用row格式，對大表使用pt-osc把資料從舊錶複製到臨時表，期間會產生大量的binlog，從而導致延時
pt-osc在搬資料過程中insert...select是有行鎖的，會降低事務並行度；且pt-osc搬資料過程中生成的binlog不是並行的，所以在slave不能並行回放
可以透過設定引數 --chunk-size、--chunk-time控制每次複製資料大小，也可以設定--max-log、check-interval、check-slave-lag等引數控制主從複製延遲程度（但這樣可能會造成pt-osc工作耗時太久，需要自行權衡）

2018年6月21日，週四

你遇到過哪些原因造成MySQL非同步複製延遲？

master上多為併發事務，salve上則多為單執行緒回放（MySQL 5.7起，支援真正的並行回放，有所緩解）
非同步複製，本來就是有一定延遲的（否則也不叫做非同步了，介意的話可以改成半同步複製）
slave機器一般效能比master更弱（這是很常見的誤區，其實slave對機器效能要求並不低）
有時為了節省機器資源，會在slave上執行多個例項
表結構設計不合理，尤其是在MySQL 5.6之前沒主鍵，幾乎會造成所有更新都全表掃描一遍，效率非常低
slave上執行大量只讀低效率的SQL
大量大事務，也會造成slave無法並行回放
業務設計缺陷，或網路延遲等導致延遲

2018年6月22日，週五

MySQL每天產生了多大容量的binlog，用SQL語句能查到嗎？

首先，這是個假設性命題（又一個釣魚題）。
這個需求完全可以透過系統層命令，配合MySQL中的“FLUSH BINARY LOGS”快速完成。
執行SHOW MASTER/BINARY LOGS命令能檢視全部binlog列表，但沒辦法區別哪些是當天內生成的。

2018年6月23日，週六

用什麼方法可以防止誤刪資料？

以下幾個措施可以防止誤刪資料，如下：

生產環境中，業務程式碼儘量不明文儲存資料庫連線賬號密碼資訊
重要的DML、DDL透過平臺型工具自動實施，減少人工操作
部署延遲複製從庫，萬一誤刪除時用於資料回檔，且從庫設定為read-only
確認備份制度及時有效
啟用SQL審計功能，養成良好SQL習慣
啟用 sql_safe_updates 選項，不允許沒 WHERE 條件的更新/刪除
將系統層的rm改為mv
線上不進行物理刪除，改為邏輯刪除（將row data標記為不可用）
啟用堡壘機，遮蔽高危SQL
降低資料庫中普通賬號的許可權級別
務必開啟binlog

2018年6月24日，週日

MySQL 8.0相對於5.7的複製改進，都有哪些呢 ？

宋利兵老師：《MySQL 8.0相對於5.7的複製改進》的公開課也討論了這個命題，簡單概括主要有兩部分：

一、普通複製功能改進

新增WRITESET並行複製模式，提高並行度，降低延遲
在多源複製中，可線上動態修改每個channel的filter rule，並且能在P_S中檢視/監控
Binary Log中儲存更多後設資料，並支援毫秒級別的延遲監控
對JSON Documents的複製效率更高了
支援DDL Crashsafe
增加caching_sha2_password安全策略，提高複製安全性

二、MGR功能改進:

支援設定節點權重，且權重最大的線上節點將被選舉為主
每個節點中儲存更多的狀態資訊，如版本、角色等
可根據從節點的事務狀態，自動化流控
離開叢集的伺服器自動被設定為read only，避免被誤操作更新資料
可監控MGR的記憶體使用情況

2018年6月25日，週一

跑truncate table，4億條資料會不會造成長時間鎖表呢?有什麼更好的方法嗎?

最好是create新表，然後交叉rename對調，再drop/truncate table或其他方式清除資料。

一、可操作步驟：

建立新的 tmp 表，正式表與tmp表表名交換（注意在一個SQL裡完成，並鎖表）
對 tmp 表建立硬連結 ln tmp.ibd tmp.ibd.hdlk
mysql中刪除表tmp（truncate / drop 都行）
然後找個業務不繁忙的時間刪除資料檔案或者用coreutils 的truncate慢慢搞

二、關於truncate table，官檔解釋：

Logically, TRUNCATE TABLE is similar to a DELETE statement that deletes all rows, or a sequence of DROP TABLE and CREATE TABLE statements
When a table is truncated, it is dropped and re-created in a new .ibd file, and the freed space is returned to the operating system

2018年6月26日，週二

明明有個索引“感覺”應該被選中，EXPLAIN時在possible_keys也有它，但最後沒被選中，可能的原因有哪些？

一、執行計劃如下：

desc select * from t1 where c2 >= 2;
key: NULL
key_len: NULL
rows: 14
filtered: 92.86
Extra: Using where

二、可能的原因如下：

隱式轉換
表碎片，因為表的碎片率過高
根據索引讀取到的資料在整個表中的資料佔比超過30%
統計資訊沒有及時更新

三、上述執行計劃的結果是 ：

預計掃描的行數為14行，filtered（是指返回結果的行佔需要讀到的行的百分比）的值為92%。

當前執行計劃中filtered值92% 說明根據索引查詢獲取的結果佔整張表的92%，在MySQL中根據索引查詢的結果佔整張表的資料30%則不會走索，所以不會走索引。
另外，也有可能是表的碎片率過高或隱式轉換導致的。

2018年6月27日，週三

主從複製執行緒均正常（為Yes，也沒報錯），Master的binlog已到binlog.000100，但slave上看到Master_Log_File卻只到binlog.000090，可能的原因有哪些?

首先要注意，這是Master_Log_File IO執行緒延遲，並不是Relay_Master_Log_File SQL執行緒延遲。

一、可能的原因如下：

由於sync_relay_log值過低，導致Slave頻繁重新整理relay_log檔案，使 Slave的硬碟資源消耗過高，所以導致SlaveIO Thread很慢。
Master/Slave壓力過大導致Slave IO Thread不能及時響應，無法及時獲得Master的event。
網路丟包嚴重。小包可以連線並且保持連線不斷，但是大包就無法傳送。可能是Master和Slave關於TCP MTU值設定不一致導致。
Master和Slave網路連結已經斷開。但slave_net_timeout值等於0（表示完全禁用心跳）或者slave_net_timeout和Slave_heartbeat_period非常大（表示檢測主從心跳的時間）。
Master的binlog非常大，io執行緒的file很長時間都在讀同一個。

二、總結
本次案例是在主庫進行壓力測試，在壓力測試的過程中，因為Master本身的壓力就很大Master來不及把binlog傳送給Slave。所以表面上看起來沒有延遲，但實際上已經產生了延遲。

。
2018年7月4日，週三

如何最佳化Linux作業系統用於MySQL環境？

一、初級玩法

1. 在BIOS及核心層面關閉NUMA

2. 在BIOS層面將CPU、記憶體均設定最大效能模式

3. 在BIOS層面關閉CPU節能模式

4. 修改IO Scheduler為deadline 或 noop

5. 使用xfs檔案系統，掛載選項noatime、nodiratime、nobarrier

6. 在核心層面設定vm.swappiness<=5，vm.dirty_ratio<=10， vm.dirty_background_rati<=5

7. 在核心層面修改使用者可最大開啟檔案數和執行緒數為65535

8. 禁用SWAP分割槽

二、高階玩法

1. 使用最新穩定Linux發行版

2. 升級各個硬體裝置到最新穩定firmware版本

3. 使用SSD時，開啟TRIM功能，並且可以的話檔案系統block size和SSD對齊

4. 當磁碟I/O存在瓶頸時，除了常規因素外，還需要關注中斷不均衡的可能性

2018年7月5日，週四

MySQL 8.0 InnoDB哪些新特性你最期待，為什麼？

1. 資料字典全部採用InnoDB引擎儲存，支援DDL原子性、crash safe，metadata管理更完善

2. 快速線上加新列（騰訊互娛DBA團隊貢獻）
3. 並行redo log，並提升redo log的I/O效能
4. 新增倒序索引
5. 增強CBO特性
6. 消除了buffer pool mutex（Percona的貢獻）
7. 自增ID持久化
8. 行鎖增加SKIP LOCKED和NOWAIT特性選項
9. 新增事務CATS特性，大大提升事務效能（Michigan大學貢獻）
10. memcached plugin增強
11. 增強JSON效能、功能
12. 新增智慧選項 innodb_dedicated_server

2018年7月10日，週二

MySQL hang的原因有哪些？

1. MySQL使用資源過高導致伺服器太累扛不住。例如CPU、記憶體、 I/O等開銷。

2. 磁碟無可用空間。

3. MySQL頻繁的建立和銷燬連線。

4. MySQL使用的最大檔案開啟數和連線數，超過了作業系統的限制。

5. MySQL的鎖不能有效的釋放。例如持有行鎖或者表鎖，造成了MDL等待。

6. MySQL的bug導致的。

導致MySQL hang住的原因有很多，不侷限於上述因素，還需要機智的你來挖掘。

2018年7月12日，週四

專訪王曉偉老師，MySQL資料匯入資料倉儲（Hadoop）有哪幾種方式？

1. 傳統方式，採用mysqldump等工具將資料檔案上傳至HDFS

2. 使用Sqoop Kettle等ETL工具，將資料表對應匯入Hive的資料表

3. 使用kafka+flume方案，將mysql binlog透過流式採集的方式匯入Hadoop

4. 設計實現Hive的快照表、增量表、全量表，實現MySQL到Hive資料的增量匯入，並支援分庫分表等特性。

葉問【轉自知數堂微信公眾號】

相關文章