大家可以叫我老張,網名superZS!一直從事資料庫行業10餘年,工作於某資料庫服務公司,兼資料庫資深講師,就面試中大家遇到的比較困惑的資料庫問題,和剛進入資料庫領域的同學們,我在這裡給大家做一個詳細的總結,希望對大家在工作或者面試中有所幫助,老師會傾囊相授,道行尚淺,大家相互學習!讓我們努力學習技術,為了拿到高薪,追到心儀的姑娘,而奮鬥吧!
葵花寶典
Question 1:
你目前接觸的mysql版本是什麼?除了官方版本,還接觸過其他的mysql分支版本嘛?
產生分支的原因
許多開發人員認為有必要將其拆分成其他專案,並且每個分支專案都有自己的專長。該需求以及Oracle對核心產品增長緩慢的擔憂,導致出現了許多開發人員感興趣的子專案和分支
三個流行MySQL分支:Drizzle、MariaDB和Percona Server(包括XtraDB引擎)
MariaDB不僅是mysql的替代品,主要還是創新和提高mysql自有技術。
新功能介紹
-
multi-source replication 多源複製
-
表的並行複製
-
galera cluster叢集
-
spider水平分片
-
tokuDB儲存引擎
XtraDB是innodb儲存引擎的增強版,可用來更好地發揮最新的計算機硬體系統效能,還包含在高效能模式下的新特性。它可以向下相容,因為它是在innodb基礎上構建,所以他有更多的指標和擴充套件功能。而且它在cpu多核的條件下,可以更好地使用記憶體,時資料庫效能提到更高!
Drizzle與mysql的差別就比較大了,並且不能相容,如果想執行此環境,就需要重寫一些程式碼了!
Question 2:
mysql主要的儲存引擎myisam和innodb的不同之處?
-
事務的支援不同(innodb支援事務,myisam不支援事務)
-
鎖粒度(innodb行鎖應用,myisam表鎖)
-
儲存空間(innodb既快取索引檔案又快取資料檔案,myisam只能快取索引檔案)
-
儲存結構
(myisam:資料檔案的副檔名為.MYD myData ,索引檔案的副檔名是.MYI myIndex)
(innodb:所有的表都儲存在同一個資料檔案裡面 即為.Ibd)
5. 統計記錄行數
(myisam:儲存有表的總行數,select count(*) from table;會直接取出出該值)
(innodb:沒有儲存表的總行數,select count(*) from table;就會遍歷整個表,消耗相當大)
Question 3:
Innodb的體系結構簡單介紹一下?
談及到innodb的體系結構,首先要考慮mysql的體系結構,分為兩部分mysql的server層和儲存引擎層
先要跟面試官聊清楚mysql的整體方向,然後再去涉及innodb體系結構
建議從三方面介紹innodb體系結構:記憶體—-執行緒—–磁碟
記憶體中包含insert_buffer,data_buffer,index_buffer,redo_log_buffer,double_write
記憶體重新整理到磁碟的機制,redo,髒頁,binlog的重新整理條件
各種執行緒的作用,master_thread,purge_thread,redo log thread,read thread,write thread,page cleaner thread
磁碟中存放著資料檔案,redo log,undo log,binlog
Question 4:
mysql有哪些索引型別:
-
資料結構角度上可以分:B+tree索引,hash索引,fulltext索引(innodb,myisam都支援)
-
儲存角度上可以分:聚集索引,非聚集索引
-
邏輯角度上可以分:primary key,normal key,單列,複合,覆蓋索引
Question 5:
mysql binlog有幾種格式:
1. statement
優點:不需要記錄每一行的變化,減少了binlog日誌量,節約了IO,提高效能
缺點:當使用一些特殊函式的時候,或者跨庫操作的時候容易丟失資料
注:在生產中不建議使用
2. row
優點:清晰記錄每行的資料資訊,不會出現跨庫丟資料的情況
缺點:內容當記錄到日誌中的時候,都將以每行記錄的修改來記錄,但就會產生大量的binlog,對於網路開銷也比較大
注:生產中推薦使用
3. mixed
是mysql5.1的時候,一個過渡版本,DDL語句會記錄成statement,DML會記錄row。
注:生產中不建議使用
Qusetion 6:
mysql主從複製的具體原理是什麼?
主伺服器把資料更新記錄到二進位制日誌中,從伺服器通過io thread向主庫發起binlog請求,主伺服器通過IO dump thread把二進位制日誌傳遞給從庫,從庫通過io thread記錄到自己的中繼日誌中。然後再通過sql thread應用中繼日誌中sql的內容。
Qusetion 7:
資料庫中雙一是什麼?
sync_binlog=1
innodb_flush_log_at_trx_commit=1
innodb_flush_log_at_trx_commit和sync_binlog 兩個引數是控制MySQL 磁碟寫入策略以及資料安全性的關鍵引數
innodb_flush_log_at_trx_commit設定為1,每次事務提交時MySQL都會把log buffer的資料寫入log file,並且刷到磁碟中去。
sync_binlog =N (N>0) ,MySQL 在每寫 N次 二進位制日誌binary log時,會使用fdatasync()函式將它的寫二進位制日誌binary log同步到磁碟中去
Qusetion 8:
如何監控mysql replication複製延遲?
-
可以通過第三方工具 業界中的瑞士×××percona-toolkit中的命令,pt-heartbeat進行主從延遲監控。
-
傳統方法,通過比較主從伺服器之間的position號的差異值。
-
還可以通過檢視seconds_behind_master估算一下主從延遲時間
Qusetion 9:
大表DDL語句,如何實施,才能把效能影響降到最低?
-
可以通過傳統方法匯入匯出資料,新建一張與原表一樣的表結構,把需要執行的ddl語句在無資料的新表執行,然後把老表中的資料匯入到新表中,把新表改成老表的名字
-
通過第三方工具 業界中的瑞士×××percona-toolkit中的命令,pt-online-schema-change進行線上操作
-
對於新版本的mysql(5.7)可以直接線上online ddl
Qusetion 10:
為什麼要為innodb表設定自增列做主鍵?
1.使用自增列做主鍵,寫入順序是自增的,和B+數葉子節點分裂順序一致
2.表不指定自增列做主鍵,同時也沒有可以被選為主鍵的唯一索引,InnoDB就會選擇內建的rowid作為主鍵,寫入順序和rowid增長順序一致
所以InnoDB表的資料寫入順序能和B+樹索引的葉子節點順序一致的話,這時候存取效率是最高
Qusetion 11:
如何優化一條有問題的sql語句?
針對sql語句的優化,我們不要上來就回答新增索引,這樣顯得太不專業。我們可以從如下幾個角度去分析
-
迴歸到表的設計層面,資料型別選擇是否合理
-
大表碎片的整理是否完善
-
表的統計資訊,是不是準確的
-
審查表的執行計劃,判斷欄位上面有沒有合適的索引
-
針對索引的選擇性,建立合適的索引(就又涉及到大表DDL的操作問題)
Qusetion 12:
伺服器負載過高或者網頁開啟緩慢,簡單說說你的優化思路 ?
-
首先我們要發現問題的過程,通過作業系統,資料庫,程式設計,硬體角度四個維度找到問題所在
-
找到瓶頸點的位置
-
制定好優化方案,形成處理問題的體系
-
體系制定好之後,在測試環境進行優化方案的測試
-
測試環境如果優化效果很好,再實施到生產環境
-
做好處理問題的記錄
Qusetion 13:
接觸過哪些mysql的主流架構?架構應用中有哪些問題需要考慮?
-
M-S
-
MHA
-
MM keepalived
-
PXC
共同存在的問題:主從延遲問題的存在,在主庫當機,切換過程中要考慮資料一致性的問題,避免出現主從複製不一致
Qusetion14:
什麼是死鎖?鎖等待?如何優化這類問題?通過資料庫哪些表可以監控?
死鎖是指兩個或多個事務在同一資源上互相佔用,並請求加鎖時,而導致的惡性迴圈現象。當多個事務以不同順序試圖加鎖同一資源時,就會產生死鎖。
鎖等待:mysql資料庫中,不同session在更新同行資料中,會出現鎖等待
重要的三張鎖的監控表innodb_trx,innodb_locks,innodb_lock_waits
Qusetion 15:
處理過mysql哪些案例
我們可以簡單從mysql四個知識模組跟他聊聊mysql體系結構,資料備份恢復,優化,高可用叢集架構
-
mysql版本的升級
-
處理mysql叢集的各種坑和問題
-
根據公司業務型別,設計合理mysql庫,表,架構。
-
定期進行災備恢復演練
-
誤刪除資料之後,恢復資料
簡單先從這幾個方向說說,每個問題再展開分析。
當然還會有一些人事上面的問題,例如為啥選我們的公司,你覺得你自己的優勢是什麼?你期望的薪資大概是多少?這些問題,就很簡單了。我們只要過了技術面試,這些都不是啥問題了!
今後還會逐漸展開某個問題的具體剖析,和詳細步驟處理方法!望大家繼續關注後期創作
_____________________________________________________________
打個小廣告,最近老張我開了視訊課程,希望大家多多支援,和我的文章一樣,定會讓你有不小的收穫!
通過對 MySQL 體系結構深入剖析講解,配合生產環境備份恢復,主從複製,高可用叢集架構和優化等實戰演練,讓同學們可以對 MySQL 資料庫有個由淺到深的認識。最後的課程部分還會對面試題總結進行講解,有利於同學們可以找到理想的 MySQL DBA 的工作。