測試面試題集-MySQL數(shù)據(jù)庫靈魂拷問加強版
22
2020-10
今天距2021年70天
這是ITester軟件測試小棧第168次推文
點擊上方藍字“ITester軟件測試小棧“關(guān)注我，每周一、三、五早上?08:30準時推送，每月不定期贈送技術(shù)書籍。
微信公眾號后臺回復“資源”、“測試工具包”領取測試資源，回復“微信群”一起進群打怪。
本文4948字，閱讀約需13分鐘
A=原子性：undo log來保證原子性，異?；驁?zhí)行失敗后進行回滾；
C=一致性：事務的最終目的，即需要數(shù)據(jù)庫層面保證，又需要應用層面進行保證；
I=隔離性：事務間的讀寫靠MySQL的鎖機制來保證隔離，事務間的寫操作靠MVCC機制（快照讀、當前讀）來保證隔離性；
D=持久性：redo log和binlog來保證持久性，確保當MySQL宕機或停電后，可以通過redo log最終將數(shù)據(jù)保存至磁盤中；
MySQL 5.7 已經(jīng)支持原生在線DDL語句，但是涉及到一些參數(shù)配置，并可能不知道配置多少合適，所以一般大表還是使用percona-tools。
主從復制是指數(shù)據(jù)可以從一個MySQL數(shù)據(jù)庫服務器主節(jié)點復制到一個或多個從節(jié)點。MySQL 默認采用異步復制方式，這樣從節(jié)點不用一直訪問主服務器來更新自己的數(shù)據(jù)，數(shù)據(jù)的更新可以在遠程連接上進行，從節(jié)點可以復制主數(shù)據(jù)庫中的所有數(shù)據(jù)庫或者特定的數(shù)據(jù)庫，或者特定的表。
MySQL主從復制的好處：
在業(yè)務復雜的系統(tǒng)中，假如有一句sql語句需要鎖表，導致暫時不能使用讀的服務，那么就很影響運行中的業(yè)務，使用主從復制，讓主庫負責寫，從庫負責讀，這樣，即使主庫出現(xiàn)了鎖表的情景，通過讀從庫也可以保證業(yè)務的正常運作。
做數(shù)據(jù)的熱備。
有利于架構(gòu)的擴展。業(yè)務量越來越大，I/O訪問頻率過高，單機無法滿足，此時做多庫的存儲，降低磁盤I/O訪問的頻率，提高單個機器的I/O性能。
MySQL主從復制的原理：
從庫IO線程請求主庫的binlog，主庫binlog dump線程寫binlog，推送給從庫，從庫IO 線程接受binlog并把寫入本地的relay log， 從庫SQL線程回訪relay log中的內(nèi)容。?
master主線程：innodb1.2 版本 負責插入緩沖；
purge線程：負責undo頁回收；
page clean線程：負責臟頁刷新；
redo log線程：將日志緩存的內(nèi)容刷新到redo log文件中；
change buffer線程：將插入緩沖中的內(nèi)容刷新到磁盤；
purge線程：刪除無用的undo頁；
error monitor線程：負責數(shù)據(jù)庫報錯的監(jiān)控線程；
lock monitor線程：負責鎖的監(jiān)控線程；
type：查詢表聯(lián)接類型，從這里可以看到本次查詢大概的效率；
key：最終選擇的索引，如果沒有索引的話，本次查詢效率通常很差；
key_len：實際用上的索引長度（很多情況下，索引不一定會全部使用上，通過長度判斷）；
rows：預計需要掃描的記錄數(shù)，預計需要掃描的記錄數(shù)越小越好；
extra：額外附加信息，主要確認是否出現(xiàn) Using filesort、Using temporary 類似情況；
主從復制延遲的原因：
一個服務器開放Ｎ個鏈接給客戶端來連接，這樣有會有大并發(fā)的更新操作, 但是從服務器的里面讀取binlog 的線程僅有一個，當某個SQL在從服務器上執(zhí)行的時間稍長或者由于某個SQL要進行鎖表就會導致，主服務器的SQL大量積壓，未被同步到從服務器里。這就導致了主從不一致，?即主從延遲。MySQL提供了從服務器狀態(tài)命令，可以通過 show slave status 進行查看，比如可以查看Seconds_Behind_Master參數(shù)的值來判斷，是否有發(fā)生主從延時。
主從復制延遲的表現(xiàn)：
網(wǎng)絡延遲：Read_Master_Log_Pos 變化非常慢，Seconds_Behind_Master逐步加大；
大表DDL：Read_Master_Log_Pos不變，status 顯示正在alter 表，Seconds_Behind_Master逐步加大；
大事務：Read_Master_Log_Pos；
首先記錄開始的LSN（全備的話就是從0開始，增備的話從指定路徑，或者從表中獲?。┎右粋€xtrabackup_log后臺檢測的 fork進程，實時檢測mysql redo的變化，一旦發(fā)現(xiàn)redo有新的日志寫入，立刻將日志寫入到日志文件xtrabackup_log中；
?復制innodb的數(shù)據(jù)文件和系統(tǒng)表空間文件idbdata1到對應的以默認時間戳為備份目錄的地方(流式備份就沒有這個目錄咯)；
?復制結(jié)束后，執(zhí)行flush table with read lock操作（5.7以及之前） 8.0使用備份鎖，所以percona工具xtrabackup8.0只能備份mysql8.0；
?復制.frm .myd .myi文件，并在這一時刻獲得binary log 的位置；
?將表進行解鎖unlock tables （8.0 使用 UNLOCK INSTANCE）；
?停止xtrabackup_log進程；
MySQL中的鎖機制是為了解決共享資源并發(fā)訪問的問題，從不同程度控制資源的讀寫，以保證數(shù)據(jù)庫的完整性和一致性，MySQL同時鎖住了主鍵與輔助索引。
MySQL鎖超時排查：
查出的線程殺死：
kill?SELECT?trx_MySQL_thread_id?FROM?information_schema.INNODB_TRX;設置鎖的超時時間：Innodb 行鎖的等待時間（單位秒）?？稍跁捈墑e設置，RDS 實例該參數(shù)的默認值為 50（秒）。生產(chǎn)環(huán)境不推薦使用過大的 innodb_lock_wait_timeout參數(shù)值，該參數(shù)支持在會話級別修改，方便應用在會話級別單獨設置某些特殊操作的行鎖等待超時時間，如下：
set?innodb_lock_wait_timeout=1000;?設置當前會話 Innodb 行鎖等待超時時間，單位秒。
MySQL避免死鎖：
如果不同程序會并發(fā)存取多個表，盡量約定以相同的順序訪問表，可以大大降低死鎖機會。
在同一個事務中，盡可能做到一次鎖定所需要的所有資源，減少死鎖產(chǎn)生概率；
對于非常容易產(chǎn)生死鎖的業(yè)務部分，可以嘗試使用升級鎖定顆粒度，通過表級鎖定來減少死鎖產(chǎn)生的概率；
讀寫分離；
分段加鎖；
減少鎖持有的時間；
多個線程盡量以相同的順序去獲取資源；
重做日志（redo log）：確保事務的持久性。redo日志記錄事務執(zhí)行后的狀態(tài)，用來恢復未寫入data file的已成功事務更新的數(shù)據(jù)。防止在發(fā)生故障的時間點，尚有臟頁未寫入磁盤，在重啟mysql服務的時候，根據(jù)redo log進行重做，從而達到事務的持久性這一特性。回滾日志（undo log）：保證數(shù)據(jù)的原子性，保存了事務發(fā)生之前的數(shù)據(jù)的一個版本，可以用于回滾，同時可以提供多版本并發(fā)控制下的讀（MVCC），也即非鎖定讀。二進制日志（binlog）：用于復制，在主從復制中，從庫利用主庫上的binlog進行重播，實現(xiàn)主從同步。錯誤日志（errorlog）：記錄mysql服務的啟停時正確和錯誤的信息，還記錄啟動、停止、運行過程中的錯誤信息。在默認情況下，系統(tǒng)記錄錯誤日志的功能是關(guān)閉的，錯誤信息被輸出到標準錯誤輸出。慢查詢?nèi)罩?/span>（slow query log）：記錄所有執(zhí)行時間超過long_query_time的所有查詢或不使用索引的查詢。報錯select、update、delete以及insert語句，慢日志只會記錄執(zhí)行成功的語句。一般查詢?nèi)罩?/span>（general log）：記錄了服務器接收到的每一個查詢或是命令，無論這些查詢或是命令是否正確甚至是否包含語法錯誤，general log 都會將其記錄下來 ，記錄的格式為 {Time ，Id ，Command，Argument }。也正因為mysql服務器需要不斷地記錄日志，開啟General log會產(chǎn)生不小的系統(tǒng)開銷。因此，Mysql默認是把General log關(guān)閉的。中繼日志（relay log）：主從復制時使用的日志。
涉及存儲引擎不一樣：binlog記錄的是所有存儲引擎的操作記錄 redo log只記錄innodb存儲引擎的日志；
記錄內(nèi)容不一樣：binlog記錄的是關(guān)于一個事務的具體操作內(nèi)容。為邏輯日志 而redo log記錄的是每個頁更改的物理情況；
寫的時間不一樣：binlog文件僅在事務提交前進行提交，即只寫磁盤一次 而在事務進行過程中，卻不斷有重做日志條目被寫入到重做日志文件中；
MySQL通過兩階段提交(內(nèi)部XA的兩階段提交)很好地解決了這一問題：
第一階段：協(xié)調(diào)者向所有參與者發(fā)送事務內(nèi)容，詢問是否可以執(zhí)行事務提交操作，并開始等待各參與者響應。事務操作，資源管理器此時會將undo日志和redo日志計入事務日志中。如果參與者成功執(zhí)行了事務操作，那么就反饋給協(xié)調(diào)者Yes響應，表示事務可以執(zhí)行；如果參與者沒有成功執(zhí)行事務，那么就反饋給協(xié)調(diào)者No響應，表示事務不可以執(zhí)行。
第二階段：提交或者中斷事務。
5.6在半同步的時候，采用的是After Commit策略。即在主庫上commit了之后，等待從庫返回確認。
5.7在半同步的時候，采用的是AFTER_SYNC，先等待從庫返回確認，然后主庫在提交。
InnoDB支持事務、行級鎖、支持外鍵約束，主要面向OLTP的應用，使用next-key locking 的策略來避免幻讀現(xiàn)象的產(chǎn)生。MyISAM不支持事務、表鎖設計、支持全文索引、讀寫互相阻塞、不支持外鍵約束；主要面向OLAP應用場景；緩存池只緩存索引文件，不緩存數(shù)據(jù)文件。Memory將所有數(shù)據(jù)保存在RAM中，在需要快速查找引用和其他類似數(shù)據(jù)的環(huán)境下，可提供極快的訪問。如果數(shù)據(jù)庫重啟或者奔潰，數(shù)據(jù)都將丟失。TokuDB支持事務、高壓縮、告訴讀寫、基于稀疏樹索引設計；支持大多數(shù)在線修改索引、添加字段。Inforbright/infinidb列式存儲、高壓縮、單列查詢快。?
?回復"資源"取干貨個人微信：Cc2015123
添加請注明來意 :)
