面試中老生常談的MySQL問答集錦夯實基礎

 更新時間:2022年03月10日 11:00:22   作者:Q.E.D  
這篇文章主要為大家介紹了面試中老生常談的MySQL問答集錦,不僅可以幫助大家順利通過面試更可以夯實大家的基礎,有需要的朋友可以借鑒參考下

1、數據庫架構

1.1、MySQL 的基礎架構圖

給面試官講一下 MySQL 的邏輯架構,有白板可以把下面的圖畫一下,圖片來源于網絡。

圖片

Mysql邏輯架構圖主要分三層:

(1)第一層負責連接處理,授權認證,安全等等 

(2)第二層負責編譯并優化SQL 

(3)第三層是存儲引擎。

1.2、一條SQL查詢語句在MySQL中如何執行的?

先檢查該語句是否有權限,如果沒有權限,直接返回錯誤信息,如果有權限會先查詢緩存(MySQL8.0 版本以前)。

如果沒有緩存,分析器進行詞法分析,提取 sql 語句中 select 等關鍵元素,然后判斷 sql 語句是否有語法錯誤,比如關鍵詞是否正確等等。

最后優化器確定執行方案進行權限校驗,如果沒有權限就直接返回錯誤信息,如果有權限就會調用數據庫引擎接口,返回執行結果。

2、SQL 優化

2.1、日常工作中你是怎么優化SQL的?

可以從這幾個維度回答這個問題:

2.1.1、優化表結構

(1)盡量使用數字型字段

若只含數值信息的字段盡量不要設計為字符型,這會降低查詢和連接的性能,并會增加存儲開銷。這是因為引擎在處理查詢和連接時會逐個比較字符串中每一個字符,而對于數字型而言只需要比較一次就夠了。

(2)盡可能的使用 varchar 代替 char

變長字段存儲空間小,可以節省存儲空間。

(3)當索引列大量重復數據時,可以把索引刪除掉

比如有一列是性別,幾乎只有男、女、未知,這樣的索引是無效的。

2.1.2、優化查詢

  • 應盡量避免在 where 子句中使用!=或<>操作符
  • 應盡量避免在 where 子句中使用 or 來連接條件
  • 任何查詢也不要出現select *
  • 避免在 where 子句中對字段進行 null 值判斷

2.1.3、索引優化

對作為查詢條件和 order by的字段建立索引

避免建立過多的索引,多使用組合索引

2.2、怎么看執行計劃(explain),如何理解其中各個字段的含義?

在 select 語句之前增加 explain 關鍵字,會返回執行計劃的信息。

圖片

(1)id 列:是 select 語句的序號,MySQL將 select 查詢分為簡單查詢和復雜查詢。

(2)select_type列:表示對應行是是簡單還是復雜的查詢。

(3)table 列:表示 explain 的一行正在訪問哪個表。

(4)type 列:最重要的列之一。表示關聯類型或訪問類型,即 MySQL 決定如何查找表中的行。從最優到最差分別為:system > const > eq_ref > ref > fulltext > ref_or_null > index_merge > unique_subquery > index_subquery > range > index > ALL

(5)possible_keys 列:顯示查詢可能使用哪些索引來查找。

(6)key 列:這一列顯示 mysql 實際采用哪個索引來優化對該表的訪問。

(7)key_len 列:顯示了mysql在索引里使用的字節數,通過這個值可以算出具體使用了索引中的哪些列。

(8)ref 列:這一列顯示了在key列記錄的索引中,表查找值所用到的列或常量,常見的有:const(常量),func,NULL,字段名。

(9)rows 列:這一列是 mysql 估計要讀取并檢測的行數,注意這個不是結果集里的行數。

(10)Extra 列:顯示額外信息。比如有 Using index、Using where、Using temporary等。

2.3、關心過業務系統里面的sql耗時嗎?統計過慢查詢嗎?對慢查詢都怎么優化過?

我們平時寫Sql時,都要養成用explain分析的習慣。慢查詢的統計,運維會定期統計給我們

優化慢查詢思路:

  • 分析語句,是否加載了不必要的字段/數據
  • 分析 SQL 執行句話,是否命中索引等
  • 如果 SQL 很復雜,優化 SQL 結構
  • 如果表數據量太大,考慮分表

3、索引

3.1、聚集索引與非聚集索引的區別

可以按以下四個維度回答:

(1)一個表中只能擁有一個聚集索引,而非聚集索引一個表可以存在多個。

(2)聚集索引,索引中鍵值的邏輯順序決定了表中相應行的物理順序;非聚集索引,索引中索引的邏輯順序與磁盤上行的物理存儲順序不同。

(3)索引是通過二叉樹的數據結構來描述的,我們可以這么理解聚簇索引:索引的葉節點就是數據節點。而非聚簇索引的葉節點仍然是索引節點,只不過有一個指針指向對應的數據塊。

(4)聚集索引:物理存儲按照索引排序;非聚集索引:物理存儲不按照索引排序;

3.2、為什么要用 B+ 樹,為什么不用普通二叉樹?

可以從幾個維度去看這個問題,查詢是否夠快,效率是否穩定,存儲數據多少,以及查找磁盤次數,為什么不是普通二叉樹,為什么不是平衡二叉樹,為什么不是B樹,而偏偏是 B+ 樹呢?

3.2.1、為什么不是普通二叉樹?

如果二叉樹特殊化為一個鏈表,相當于全表掃描。平衡二叉樹相比于二叉查找樹來說,查找效率更穩定,總體的查找速度也更快。

3.2.2、為什么不是平衡二叉樹呢?

我們知道,在內存比在磁盤的數據,查詢效率快得多。如果樹這種數據結構作為索引,那我們每查找一次數據就需要從磁盤中讀取一個節點,也就是我們說的一個磁盤塊,但是平衡二叉樹可是每個節點只存儲一個鍵值和數據的,如果是B樹,可以存儲更多的節點數據,樹的高度也會降低,因此讀取磁盤的次數就降下來啦,查詢效率就快啦。

3.2.3、為什么不是 B 樹而是 B+ 樹呢?

B+ 樹非葉子節點上是不存儲數據的,僅存儲鍵值,而B樹節點中不僅存儲鍵值,也會存儲數據。innodb中頁的默認大小是16KB,如果不存儲數據,那么就會存儲更多的鍵值,相應的樹的階數(節點的子節點樹)就會更大,樹就會更矮更胖,如此一來我們查找數據進行磁盤的IO次數有會再次減少,數據查詢的效率也會更快。

B+ 樹索引的所有數據均存儲在葉子節點,而且數據是按照順序排列的,鏈表連著的。那么 B+ 樹使得范圍查找,排序查找,分組查找以及去重查找變得異常簡單。

3.3、Hash 索引和 B+ 樹索引區別是什么?你在設計索引是怎么抉擇的?

B+ 樹可以進行范圍查詢,Hash 索引不能。

B+ 樹支持聯合索引的最左側原則,Hash 索引不支持。

B+ 樹支持 order by 排序,Hash 索引不支持。

Hash 索引在等值查詢上比 B+ 樹效率更高。

B+ 樹使用 like 進行模糊查詢的時候,like 后面(比如%開頭)的話可以起到優化的作用,Hash 索引根本無法進行模糊查詢。

3.4、什么是最左前綴原則?什么是最左匹配原則?

最左前綴原則,就是最左優先,在創建多列索引時,要根據業務需求,where 子句中使用最頻繁的一列放在最左邊。

當我們創建一個組合索引的時候,如 (a1,a2,a3),相當于創建了(a1)、(a1,a2)和(a1,a2,a3)三個索引,這就是最左匹配原則。

3.5、索引不適合哪些場景?

數據量少的不適合加索引

更新比較頻繁的也不適合加索引 = 區分度低的字段不適合加索引(如性別)

3.6、索引有哪些優缺點?

(1) 優點:

唯一索引可以保證數據庫表中每一行的數據的唯一性

索引可以加快數據查詢速度,減少查詢時間

(2)缺點:

創建索引和維護索引要耗費時間

索引需要占物理空間,除了數據表占用數據空間之外,每一個索引還要占用一定的物理空間

以表中的數據進行增、刪、改的時候,索引也要動態的維護。

4、鎖

4.1、MySQL 遇到過死鎖問題嗎,你是如何解決的?

遇到過。我排查死鎖的一般步驟是醬紫的:

(1)查看死鎖日志 show engine innodb status;

(2)找出死鎖Sql

(3)分析sql加鎖情況

(4)模擬死鎖案發

(5)分析死鎖日志

(6)分析死鎖結果

4.2、說說數據庫的樂觀鎖和悲觀鎖是什么以及它們的區別?

(1)悲觀鎖:

悲觀鎖她專一且缺乏安全感了,她的心只屬于當前事務,每時每刻都擔心著它心愛的數據可能被別的事務修改,所以一個事務擁有(獲得)悲觀鎖后,其他任何事務都不能對數據進行修改啦,只能等待鎖被釋放才可以執行。

(2)樂觀鎖:

樂觀鎖的“樂觀情緒”體現在,它認為數據的變動不會太頻繁。因此,它允許多個事務同時對數據進行變動。

實現方式:樂觀鎖一般會使用版本號機制或CAS算法實現。

4.3、MVCC 熟悉嗎,知道它的底層原理?

MVCC (Multiversion Concurrency Control),即多版本并發控制技術。

MVCC在MySQL InnoDB中的實現主要是為了提高數據庫并發性能,用更好的方式去處理讀-寫沖突,做到即使有讀寫沖突時,也能做到不加鎖,非阻塞并發讀。

5、事務

5.1、MySQL事務得四大特性以及實現原理

  • 原子性:事務作為一個整體被執行,包含在其中的對數據庫的操作要么全部被執行,要么都不執行。
  • 一致性:指在事務開始之前和事務結束以后,數據不會被破壞,假如A賬戶給B賬戶轉10塊錢,不管成功與否,A和B的總金額是不變的。
  • 隔離性:多個事務并發訪問時,事務之間是相互隔離的,即一個事務不影響其它事務運行效果。簡言之,就是事務之間是進水不犯河水的。
  • 持久性:表示事務完成以后,該事務對數據庫所作的操作更改,將持久地保存在數據庫之中。

5.2、事務的隔離級別有哪些?MySQL的默認隔離級別是什么?

  • 讀未提交(Read Uncommitted)
  • 讀已提交(Read Committed)
  • 可重復讀(Repeatable Read)
  • 串行化(Serializable)

Mysql默認的事務隔離級別是可重復讀(Repeatable Read)

5.3、什么是幻讀,臟讀,不可重復讀呢?

事務A、B交替執行,事務A被事務B干擾到了,因為事務A讀取到事務B未提交的數據,這就是臟讀。

在一個事務范圍內,兩個相同的查詢,讀取同一條記錄,卻返回了不同的數據,這就是不可重復讀。

事務A查詢一個范圍的結果集,另一個并發事務B往這個范圍中插入/刪除了數據,并靜悄悄地提交,然后事務A再次查詢相同的范圍,兩次讀取得到的結果集不一樣了,這就是幻讀。

6、實戰

6.1、MySQL數據庫cpu飆升的話,要怎么處理呢?

排查過程:

(1)使用top 命令觀察,確定是mysqld導致還是其他原因。

(2)如果是mysqld導致的,show processlist,查看session情況,確定是不是有消耗資源的sql在運行。

(3)找出消耗高的 sql,看看執行計劃是否準確, 索引是否缺失,數據量是否太大。

處理:

(1)kill 掉這些線程(同時觀察 cpu 使用率是否下降)

(2)進行相應的調整(比如說加索引、改 sql、改內存參數)

(3)重新跑這些 SQL。

其他情況:

也有可能是每個 sql 消耗資源并不多,但是突然之間,有大量的 session 連進來導致 cpu 飆升,這種情況就需要跟應用一起來分析為何連接數會激增,再做出相應的調整,比如說限制連接數等

6.2、MYSQL的主從延遲,你怎么解決?

主從復制分了五個步驟進行:(圖片來源于網絡)

圖片

步驟一:主庫的更新事件(update、insert、delete)被寫到binlog

步驟二:從庫發起連接,連接到主庫。

步驟三:此時主庫創建一個binlog dump thread,把binlog的內容發送到從庫。

步驟四:從庫啟動之后,創建一個I/O線程,讀取主庫傳過來的binlog內容并寫入到relay log

步驟五:還會創建一個SQL線程,從relay log里面讀取內容,從Exec_Master_Log_Pos位置開始執行讀取到的更新事件,將更新內容寫入到slave的db

主從同步延遲的原因

一個服務器開放N個鏈接給客戶端來連接的,這樣有會有大并發的更新操作, 但是從服務器的里面讀取binlog的線程僅有一個,當某個SQL在從服務器上執行的時間稍長 或者由于某個SQL要進行鎖表就會導致,主服務器的SQL大量積壓,未被同步到從服務器里。這就導致了主從不一致, 也就是主從延遲。

主從同步延遲的解決辦法

主服務器要負責更新操作,對安全性的要求比從服務器要高,所以有些設置參數可以修改,比如sync_binlog=1,innodb_flush_log_at_trx_commit = 1 之類的設置等。

選擇更好的硬件設備作為slave。

把一臺從服務器當度作為備份使用, 而不提供查詢, 那邊他的負載下來了, 執行relay log 里面的SQL效率自然就高了。

增加從服務器嘍,這個目的還是分散讀的壓力,從而降低服務器負載。

6.3、如果讓你做分庫與分表的設計,簡單說說你會怎么做?

分庫分表方案:

水平分庫:以字段為依據,按照一定策略(hash、range等),將一個庫中的數據拆分到多個庫中。

水平分表:以字段為依據,按照一定策略(hash、range等),將一個表中的數據拆分到多個表中。

垂直分庫:以表為依據,按照業務歸屬不同,將不同的表拆分到不同的庫中。

垂直分表:以字段為依據,按照字段的活躍性,將表中字段拆到不同的表(主表和擴展表)中。

常用的分庫分表中間件:

sharding-jdbc

Mycat

分庫分表可能遇到的問題

事務問題:需要用分布式事務啦

跨節點Join的問題:解決這一問題可以分兩次查詢實現

跨節點的count,order by,group by以及聚合函數問題:分別在各個節點上得到結果后在應用程序端進行合并。

數據遷移,容量規劃,擴容等問題

ID問題:數據庫被切分后,不能再依賴數據庫自身的主鍵生成機制啦,最簡單可以考慮UUID

跨分片的排序分頁問題

結術

以上就是面試中老生常談的MySQL問答集錦夯實基礎的詳細內容,更多關于MySQL面試問答集錦的資料請關注腳本之家其它相關文章!

相關文章

  • MySQL無法啟動、無法停止解決方法(安全設置后容易出現)

    MySQL無法啟動、無法停止解決方法(安全設置后容易出現)

    最近在Win2003上的MySQL出現過多次正常運行時無法連接數據庫故障,根本原因就是因為安全設置以后容易出現的問題,其實很簡單的解決
    2012-03-03
  • mysql學習筆記之數據引擎

    mysql學習筆記之數據引擎

    插件式存儲引擎是MySQL數據庫最重要的特征之一,用戶可以根據應用的需要尋找如何存儲和索引數據、是否使用事務等。MySQL默認支持多種存儲引擎,以適用于不同領域的數據庫應用需求,用戶可以通過選擇選擇不同的存儲引擎提供應用的效率,提供靈活的存儲
    2017-02-02
  • Starting MySQL.Manager of pid-file quit without updating file.[FAILED]的解決方法

    Starting MySQL.Manager of pid-file quit without updating fil

    因為硬盤滿了,Starting MySQL.Manager of pid-file quit without updating file.[FAILED]
    2010-02-02
  • centos 6.9安裝mysql的詳細教程

    centos 6.9安裝mysql的詳細教程

    這篇文章主要介紹了centos 6.9安裝mysql的詳細教程,非常不錯,具有參考借鑒價值,需要的朋友可以參考下
    2017-07-07
  • MySQL拋出Incorrect string value異常分析

    MySQL拋出Incorrect string value異常分析

    從上至下統一用上UTF-8就高枕無憂,今天還是遇到字符的異常,本文將介紹解決方法
    2012-11-11
  • MySQL中有哪些情況下數據庫索引會失效詳析

    MySQL中有哪些情況下數據庫索引會失效詳析

    這篇文章主要給大家介紹了關于MySQL中有哪些情況下數據庫索引會失效的相關資料,文中通過圖文介紹的非常詳細,對大家學習或者使用mysql具有一定的參考學習價值,需要的朋友們下面隨著小編來一起學習學習吧
    2018-07-07
  • linux下mysql自動備份數據庫與自動刪除臨時文件

    linux下mysql自動備份數據庫與自動刪除臨時文件

    mysql自動備份數據庫與自動刪除臨時文件,有需要的朋友可以參考下
    2013-02-02
  • 分享101個MySQL調試與優化技巧

    分享101個MySQL調試與優化技巧

    隨著越來越多的數據庫驅動的應用程序,人們一直在推動MySQL發展到它的極限。這里是101條調節和優化MySQL安裝的技巧。一些技巧是針對特定的安裝環境的,但這些思路是通用的。我已經把他們分成幾類,來幫助你掌握更多MySQL的調節和優化技巧
    2017-05-05
  • 詳解MySQL日期 字符串 時間戳互轉

    詳解MySQL日期 字符串 時間戳互轉

    本篇文章主要介紹了詳解MySQL日期 字符串 時間戳互轉,詳解date轉字符串、date轉時間戳、字符串轉date、字符串轉時間戳、時間戳轉date,時間戳轉字符串,有興趣的可以了解一下。
    2017-01-01
  • 關于MySQL報警的一次分析處理詳解

    關于MySQL報警的一次分析處理詳解

    這篇文章主要給大家介紹了關于MySQL報警的一次分析處理的相關資料,文中通過示例代碼介紹的非常詳細,對大家的學習或者工作具有一定的參考學習價值,需要的朋友們下面隨著小編來一起學習學習吧
    2021-03-03

最新評論

免费人成视频在线观看