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

轉自

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

知數堂 - 最靠譜、最有品質的培訓品牌 http://www.3wedu.net/

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

2018年6月10日，周日

MySQL主從復制什么原因會造成不一致，如何預防及解決？

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

1. 人為原因導致從庫與主庫數據不一致（從庫寫入）

2. 主從復制過程中，主庫異常宕機

3. 設置了ignore/do/rewrite等replication等規則

4. binlog非row格式

5. 異步復制本身不保證，半同步存在提交讀的問題，增強半同步起來比較完美。 但對于異常重啟（Replication Crash Safe），從庫寫數據（GTID）的防范，還需要策略來保證。

6. 從庫中斷很久，binlog應用不連續，監控并及時修復主從

7. 從庫啟用了諸如存儲過程，從庫禁用存儲過程等

8. 數據庫大小版本/分支版本導致數據不一致？，主從版本統一

9. 備份的時候沒有指定參數 例如mysqldump --master-data=2 等

10. 主從sql_mode 不一致

11. 一主二從環境，二從的server id一致

12. MySQL自增列 主從不一致

13. 主從信息保存在文件里面，文件本身的刷新是非事務的，導致從庫重啟后開始執行點大于實際執行點

14. 采用5.6的after_commit方式半同步，主庫當機可能會引起主從不一致，要看binlog是否傳到了從庫

15. 啟用增強半同步了（5.7的after_sync方式），但是從庫延遲超時自動切換成異步復制

     

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

1. master:innodb_flush_log_at_trx_commit=1&sync_binlog=1

2. slave:master_info_repository="TABLE"&relay_log_info_repository="TABLE"&relay_log_recovery=1

3. 設置從庫庫為只讀模式

4. 可以使用5.7增強半同步避免數據丟失等

5. binlog row格式

6. 必須引定期的數據校驗機制

7. 當使用延遲復制的時候，此時主從數據也是不一致的（計劃內），但在切換中，不要把延遲從提升為主庫哦~

8. mha在主從切換的過程中，因主庫系統宕機，可能造成主從不一致（mha本身機制導致這個問題）

2018年6月11日，周一

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

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

1. 根據業務類型，和業務容量的評估，來選擇和判斷是否使用分庫分表

2. 當前數據庫本事具有的能力，壓力的評估

3. 數據庫的物理隔離，例如減少鎖的爭用、資源的消耗和隔離等

4. 熱點表較多,并且數據量大，可能會導致鎖爭搶，性能下降

5. 數據庫的高并發，數據庫的讀寫壓力過大，可能會導致數據庫或系統宕機

6. 數據庫（MySQL5.7以下）連接數過高，會增加系統壓力

7. 單表數據量大，如SQL使用不當，會導致io隨機讀寫比例高。查詢慢（大表上的B+樹太大，掃描太慢，甚至可能需要4層B+樹）

8. 備份和恢復時間比較長

    

二、都遇到什么問題?

1. 全局pk（主鍵和唯一索引）的沖突檢測不準確，全局的自增主鍵支持不夠好

2. 分片鍵的選擇。如沒有選擇好，可能會影響SQL執行效率

3. 分布式事務，中間價產品對分布式事務的支持力度

4. 對于開發來說，需要進行業務的拆分

5. 對于開發來說，部分SQL不兼容則需要代碼重構，工作量的評估

6. 對于開發來說，跨庫join，跨庫查詢

三、如何解決?

1. 使用全局分號器。或者使用全局唯一id，（應用生成順序唯一int類型做為全局主鍵）

2. 應用層來判斷唯一索引

3. 配合應用選擇合適的分片鍵，并加上索引

4. 配合應用，配合開發，對不兼容SQL的進行整改

2018年6月12日，周二

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

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

1. 對業務的了解，需要考慮業務對數據庫一致性要求的敏感程度，切換過程中是否有事務會丟失

2. 對于基礎設施的了解，需要了解基礎設施的高可用的架構。例如 單網線，單電源等情況 

3. 對于數據庫故障時間掌握，業務方最多能容忍時間范圍，因為高可用切換導致的應用不可用時間

4. 需要了解主流的高可用的優缺點：例如 MHA/PXC/MGR 等。

5. 考慮多IDC多副本分布，支持IDC級別節點全部掉線后，業務可以切到另一個機房

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

1. 基礎層 和基礎運維部門配合，了解和避免網絡/ 硬盤/ 電源等是否會出現單點故障 

2. 應用層 和應用開發同學配合，在關鍵業務中記錄SQL日志，可以做到即使切換，出現丟事務的情況，也可以通過手工補的方式保證數據一致性，例如：交易型的業務引入狀態機，事務狀態，應對數據庫切換后事務重做 

3. 業務層 了解自己的應用，根據不同的應用制定合理的高可用策略。 

4. 單機多實例 環境及基于虛擬機或容器的設計不能分布在同一臺物理機上。 

5. 最終大招 在數據庫不可用 ，可以把已提及的事務先存儲到隊列或者其他位置，等數據庫恢復，重新應用

2018年6月13日，周三

MySQL備份，使用xtrabackup備份全實例數據時，會造成鎖等待嗎？那么如果使用mysqldump進行備份呢？

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

1. xtrabackup會，它在備份時會產生短暫的全局讀鎖FTWL(flush table with read lock)，用于拷貝frm/MYD/MYI等文件，以及記錄binlog信息。如果MyISAM表的數據量非常大，則拷貝時間就越長，加鎖的時間也越長

2. mysqldump有可能會。如果只是添加 --single-transacton 選項用于保證備份數據一致性，這時就不會產生FTWL鎖了。但通常我們為了讓備份文件和binlog保持一致，通常也會設置 --master-data 選項用于獲得當前binlog信息，這種情況也會短暫加鎖

3. 數據量特別大的話，建議優先用 xtrabackup，提高備份/恢復速度。而如果數據量不是太大或者想備份單表，則建議用mysqldump了，方便邏輯恢復。各有利弊，注意其適用場景

二、xtrabackup冷知識

1. 基于MySQL 5.6版本開發的xtrabackup，會在備份過程中生成內部通信文件 suspend file，用于 xtrabackup 和 innobackupex 的通信，備份結束后文件刪除，默認文件位置 /tmp/xtrabackup_suspended 

2. 如果在備份過程中，修改了 /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等的生產中，該如何選擇？

一、生產環境中：  

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

1. 從成熟度上來選擇，推薦：異步復制(GTID+ROW)

2. 從數據安全及更高性能上選擇：增強半同步 （在這個結構下也可以把innodb_flush_log_trx_commit調整到非1， 從而獲得更好的性能)

3. 對于主從切換控制覺的不好管理，又對數據一致性要求特別高的場景，可以使用MGR

二、理由：

1. 異步復制，相對來講非常成熟，對于環境運維也比較容易上手 

2. 增強半同步復制，可以安全的保證數據傳輸到從庫上，對于單節點的配置上不用要求太嚴格，特別從庫上也可以更寬松一點，而且在一致性和性能有較高的提升，但對運維上有一定的要求

3. 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異步復制延遲？

1. master上多為并發事務，salve上則多為單線程回放（MySQL 5.7起，支持真正的并行回放，有所緩解）

2. 異步復制，本來就是有一定延遲的（否則也不叫做異步了，介意的話可以改成半同步復制）

3. slave機器一般性能比master更弱（這是很常見的誤區，其實slave對機 器性能要求并不低）

4. 有時為了節省機器資源，會在slave上運行多個實例

5. 表結構設計不合理，尤其是在MySQL 5.6之前沒主鍵，幾乎會造成所有更新都全表掃描一遍，效率非常低

6. slave上運行大量只讀低效率的SQL

7. 大量大事務，也會造成slave無法并行回放 

8. 業務設計缺陷，或網絡延遲等導致延遲

2018年6月22日，周五

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

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

2018年6月23日，周六

用什么方法可以防止誤刪數據？

以下幾個措施可以防止誤刪數據，如下： 

1. 生產環境中，業務代碼盡量不明文保存數據庫連接賬號密碼信息

2. 重要的DML、DDL通過平臺型工具自動實施，減少人工操作

3. 部署延遲復制從庫，萬一誤刪除時用于數據回檔，且從庫設置為read-only

4. 確認備份制度及時有效

5. 啟用SQL審計功能，養成良好SQL習慣

6. 啟用 sql_safe_updates 選項，不允許沒 WHERE 條件的更新/刪除

7. 將系統層的rm改為mv

8. 線上不進行物理刪除，改為邏輯刪除（將row data標記為不可用）

9. 啟用堡壘機，屏蔽高危SQL

10. 降低數據庫中普通賬號的權限級別

11. 務必開啟binlog

2018年6月24日，周日

MySQL 8.0相對于5.7的復制改進，都有哪些呢 ？

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

一、普通復制功能改進 

1. 新增WRITESET并行復制模式，提高并行度，降低延遲 

2. 在多源復制中，可在線動態修改每個channel的filter rule，并且能在P_S中查看/監控 

3. Binary Log中存儲更多元數據，并支持毫秒級別的延遲監控 

4. 對JSON Documents的復制效率更高了 

5. 支持DDL Crashsafe 

6. 增加caching_sha2_password安全策略，提高復制安全性

二、MGR功能改進:

1. 支持設置節點權重，且權重最大的在線節點將被選舉為主 

2. 每個節點中存儲更多的狀態信息，如版本、角色等 

3. 可根據從節點的事務狀態，自動化流控 

4. 離開集群的服務器自動被設置為read only，避免被誤操作更新數據 

5. 可監控MGR的內存使用情況

2018年6月25日，周一

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

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

一、可操作步驟： 

1. 創建新的 tmp 表，正式表與tmp表表名交換（注意在一個SQL里完成，并鎖表） 

2. 對 tmp 表創建硬鏈接 ln tmp.ibd tmp.ibd.hdlk 

3. mysql中刪除表tmp（truncate / drop 都行）

4. 然后找個業務不繁忙的時間刪除數據文件或者用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

二、可能的原因如下： 

1. 隱式轉換

2. 表碎片，因為表的碎片率過高

3. 根據索引讀取到的數據在整個表中的數據占比超過30%

4. 統計信息沒有及時更新

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

預計掃描的行數為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線程延遲。

一、可能的原因如下： 

1. 由于sync_relay_log值過低，導致Slave頻繁刷新relay_log文件，使 Slave的硬盤資源消耗過高，所以導致SlaveIO Thread很慢。 

2. Master/Slave壓力過大導致Slave IO Thread不能及時響應， 無法及時獲得Master的event。 

3. 網絡丟包嚴重。小包可以連接并且保持連接不斷，但是大包就無法發送。可能是Master和Slave關于TCP MTU值設置不一致導致。 

4. Master和Slave網絡鏈接已經斷開。但slave_net_timeout值等于0（表示完全禁用心跳）或者slave_net_timeout和Slave_heartbeat_period非常大（表示檢測主從心跳的時間）。 

5. 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數據的增量導入，并支持分庫分表等特性。