[靈魂拷問]MySQL面試高頻一百問(工程師方向)

目錄

• 目錄
• 前言
• 索引相關
• 事務相關
• 表結構設計
• 儲存引擎相關
• 零散問題
• 參考文章

前言

本文主要受眾為開發人員,所以不涉及到MySQL的服務部署等操作,且內容較多,大家準備好耐心和瓜子礦泉水.

前一陣系統的學習了一下MySQL,也有一些實際操作經驗,偶然看到一篇和MySQL相關的面試文章,發現其中的一些問題自己也回答不好,雖然知識點大部分都知道,但是無法將知識串聯起來.

因此決定搞一個MySQL靈魂100問,試著用回答問題的方式,讓自己對知識點的理解更加深入一點.

此文不會事無鉅細的從select的用法開始講解mysql,主要針對的是開發人員需要知道的一些MySQL的知識點,主要包括索引,事務,優化等方面,以在面試中高頻的問句形式給出答案.如果您有其他的MySQL面試題,覺得題目尚有意思或者難度,可以評論題目或者傳送郵件至huyanshi2580@gmail.com,我會將其收錄進此文並標註您的姓名.

本文大部分內容,在其他文章中做過詳細的解釋,尤其是索引以及事務兩塊內容,分別在於下面的文章中,有興趣加深理解的朋友們可以移步.

MySQL索引原理極其優化

mysql的事務極其隔離級別

索引相關

關於MySQL的索引,曾經進行過一次總結,文章連結在這裡 Mysql索引原理及其優化.

1. 什麼是索引?

索引是一種資料結構,可以幫助我們快速的進行資料的查詢.

2. 索引是個什麼樣的資料結構呢?

索引的資料結構和具體儲存引擎的實現有關, 在MySQL中使用較多的索引有Hash索引,B+樹索引等,而我們經常使用的InnoDB儲存引擎的預設索引實現為:B+樹索引.

3. Hash索引和B+樹所有有什麼區別或者說優劣呢?

首先要知道Hash索引和B+樹索引的底層實現原理:

hash索引底層就是hash表,進行查詢時,呼叫一次hash函式就可以獲取到相應的鍵值,之後進行回表查詢獲得實際資料.B+樹底層實現是多路平衡查詢樹.對於每一次的查詢都是從根節點出發,查詢到葉子節點方可以獲得所查鍵值,然後根據查詢判斷是否需要回表查詢資料.

那麼可以看出他們有以下的不同:

• hash索引進行等值查詢更快(一般情況下),但是卻無法進行範圍查詢.

因為在hash索引中經過hash函式建立索引之後,索引的順序與原順序無法保持一致,不能支援範圍查詢.而B+樹的的所有節點皆遵循(左節點小於父節點,右節點大於父節點,多叉樹也類似),天然支援範圍.

• hash索引不支援使用索引進行排序,原理同上.

• hash索引不支援模糊查詢以及多列索引的最左字首匹配.原理也是因為hash函式的不可預測.AAAA和AAAAB的索引沒有相關性.

• hash索引任何時候都避免不了回表查詢資料,而B+樹在符合某些條件(聚簇索引,覆蓋索引等)的時候可以只通過索引完成查詢.

• hash索引雖然在等值查詢上較快,但是不穩定.效能不可預測,當某個鍵值存在大量重複的時候,發生hash碰撞,此時效率可能極差.而B+樹的查詢效率比較穩定,對於所有的查詢都是從根節點到葉子節點,且樹的高度較低.

因此,在大多數情況下,直接選擇B+樹索引可以獲得穩定且較好的查詢速度.而不需要使用hash索引.

4. 上面提到了B+樹在滿足聚簇索引和覆蓋索引的時候不需要回表查詢資料,什麼是聚簇索引?

在B+樹的索引中,葉子節點可能儲存了當前的key值,也可能儲存了當前的key值以及整行的資料,這就是聚簇索引和非聚簇索引. 在InnoDB中,只有主鍵索引是聚簇索引,如果沒有主鍵,則挑選一個唯一鍵建立聚簇索引.如果沒有唯一鍵,則隱式的生成一個鍵來建立聚簇索引.

當查詢使用聚簇索引時,在對應的葉子節點,可以獲取到整行資料,因此不用再次進行回表查詢.

5. 非聚簇索引一定會回表查詢嗎?

不一定,這涉及到查詢語句所要求的欄位是否全部命中了索引,如果全部命中了索引,那麼就不必再進行回表查詢.

舉個簡單的例子,假設我們在員工表的年齡上建立了索引,那麼當進行select age from employee where age < 20的查詢時,在索引的葉子節點上,已經包含了age資訊,不會再次進行回表查詢.

6. 在建立索引的時候,都有哪些需要考慮的因素呢?

建立索引的時候一般要考慮到欄位的使用頻率,經常作為條件進行查詢的欄位比較適合.如果需要建立聯合索引的話,還需要考慮聯合索引中的順序.此外也要考慮其他方面,比如防止過多的所有對錶造成太大的壓力.這些都和實際的表結構以及查詢方式有關.

7. 聯合索引是什麼?為什麼需要注意聯合索引中的順序?

MySQL可以使用多個欄位同時建立一個索引,叫做聯合索引.在聯合索引中,如果想要命中索引,需要按照建立索引時的欄位順序挨個使用,否則無法命中索引.

具體原因為:

MySQL使用索引時需要索引有序,假設現在建立了"name,age,school"的聯合索引,那麼索引的排序為: 先按照name排序,如果name相同,則按照age排序,如果age的值也相等,則按照school進行排序.

當進行查詢時,此時索引僅僅按照name嚴格有序,因此必須首先使用name欄位進行等值查詢,之後對於匹配到的列而言,其按照age欄位嚴格有序,此時可以使用age欄位用做索引查詢,,,以此類推.因此在建立聯合索引的時候應該注意索引列的順序,一般情況下,將查詢需求頻繁或者欄位選擇性高的列放在前面.此外可以根據特例的查詢或者表結構進行單獨的調整.

8. 建立的索引有沒有被使用到?或者說怎麼才可以知道這條語句執行很慢的原因?

MySQL提供了explain命令來檢視語句的執行計劃,MySQL在執行某個語句之前,會將該語句過一遍查詢優化器,之後會拿到對語句的分析,也就是執行計劃,其中包含了許多資訊. 可以通過其中和索引有關的資訊來分析是否命中了索引,例如possilbe_key,key,key_len等欄位,分別說明了此語句可能會使用的索引,實際使用的索引以及使用的索引長度.

9. 那麼在哪些情況下會發生針對該列建立了索引但是在查詢的時候並沒有使用呢?

• 使用不等於查詢,
• 列參與了數學運算或者函式
• 在字串like時左邊是萬用字元.類似於'%aaa'.
• 當mysql分析全表掃描比使用索引快的時候不使用索引.
• 當使用聯合索引,前面一個條件為範圍查詢,後面的即使符合最左字首原則,也無法使用索引.

以上情況,MySQL無法使用索引.

事務相關

1. 什麼是事務?

理解什麼是事務最經典的就是轉賬的栗子,相信大家也都瞭解,這裡就不再說一邊了.

事務是一系列的操作,他們要符合ACID特性.最常見的理解就是:事務中的操作要麼全部成功,要麼全部失敗.但是隻是這樣還不夠的.

2. ACID是什麼?可以詳細說一下嗎?

A=Atomicity

原子性,就是上面說的,要麼全部成功,要麼全部失敗.不可能只執行一部分操作.

C=Consistency

系統(資料庫)總是從一個一致性的狀態轉移到另一個一致性的狀態,不會存在中間狀態.

I=Isolation

隔離性: 通常來說:一個事務在完全提交之前,對其他事務是不可見的.注意前面的通常來說加了紅色,意味著有例外情況.

D=Durability

永續性,一旦事務提交,那麼就永遠是這樣子了,哪怕系統崩潰也不會影響到這個事務的結果.

3. 同時有多個事務在進行會怎麼樣呢?

多事務的併發進行一般會造成以下幾個問題:

• 髒讀: A事務讀取到了B事務未提交的內容,而B事務後面進行了回滾.
• 不可重複讀: 當設定A事務只能讀取B事務已經提交的部分,會造成在A事務內的兩次查詢,結果竟然不一樣,因為在此期間B事務進行了提交操作.
• 幻讀: A事務讀取了一個範圍的內容,而同時B事務在此期間插入了一條資料.造成"幻覺".

4. 怎麼解決這些問題呢?MySQL的事務隔離級別瞭解嗎?

MySQL的四種隔離級別如下:

• 未提交讀(READ UNCOMMITTED)

這就是上面所說的例外情況了,這個隔離級別下,其他事務可以看到本事務沒有提交的部分修改.因此會造成髒讀的問題(讀取到了其他事務未提交的部分,而之後該事務進行了回滾).

這個級別的效能沒有足夠大的優勢,但是又有很多的問題,因此很少使用.

• 已提交讀(READ COMMITTED)

其他事務只能讀取到本事務已經提交的部分.這個隔離級別有 不可重複讀的問題,在同一個事務內的兩次讀取,拿到的結果竟然不一樣,因為另外一個事務對資料進行了修改.

• REPEATABLE READ(可重複讀)

可重複讀隔離級別解決了上面不可重複讀的問題(看名字也知道),但是仍然有一個新問題,就是 幻讀,當你讀取id> 10 的資料行時,對涉及到的所有行加上了讀鎖,此時例外一個事務新插入了一條id=11的資料,因為是新插入的,所以不會觸發上面的鎖的排斥,那麼進行本事務進行下一次的查詢時會發現有一條id=11的資料,而上次的查詢操作並沒有獲取到,再進行插入就會有主鍵衝突的問題.

• SERIALIZABLE(可序列化)

這是最高的隔離級別,可以解決上面提到的所有問題,因為他強制將所以的操作序列執行,這會導致併發效能極速下降,因此也不是很常用.

5. Innodb使用的是哪種隔離級別呢?

InnoDB預設使用的是可重複讀隔離級別.

6. 對MySQL的鎖瞭解嗎?

當資料庫有併發事務的時候,可能會產生資料的不一致,這時候需要一些機制來保證訪問的次序,鎖機制就是這樣的一個機制.

就像酒店的房間,如果大家隨意進出,就會出現多人搶奪同一個房間的情況,而在房間上裝上鎖,申請到鑰匙的人才可以入住並且將房間鎖起來,其他人只有等他使用完畢才可以再次使用.

7. MySQL都有哪些鎖呢?像上面那樣子進行鎖定豈不是有點阻礙併發效率了?

從鎖的類別上來講,有共享鎖和排他鎖.

共享鎖: 又叫做讀鎖. 當使用者要進行資料的讀取時,對資料加上共享鎖.共享鎖可以同時加上多個.

排他鎖: 又叫做寫鎖. 當使用者要進行資料的寫入時,對資料加上排他鎖.排他鎖只可以加一個,他和其他的排他鎖,共享鎖都相斥.

用上面的例子來說就是使用者的行為有兩種,一種是來看房,多個使用者一起看房是可以接受的. 一種是真正的入住一晚,在這期間,無論是想入住的還是想看房的都不可以.

鎖的粒度取決於具體的儲存引擎,InnoDB實現了行級鎖,頁級鎖,表級鎖.

他們的加鎖開銷從大大小,併發能力也是從大到小.

表結構設計

1. 為什麼要儘量設定一個主鍵?

主鍵是資料庫確保資料行在整張表唯一性的保障,即使業務上本張表沒有主鍵,也建議新增一個自增長的ID列作為主鍵.設定了主鍵之後,在後續的刪改查的時候可能更加快速以及確保運算元據範圍安全.

2. 主鍵使用自增ID還是UUID?

推薦使用自增ID,不要使用UUID.

因為在InnoDB儲存引擎中,主鍵索引是作為聚簇索引存在的,也就是說,主鍵索引的B+樹葉子節點上儲存了主鍵索引以及全部的資料(按照順序),如果主鍵索引是自增ID,那麼只需要不斷向後排列即可,如果是UUID,由於到來的ID與原來的大小不確定,會造成非常多的資料插入,資料移動,然後導致產生很多的記憶體碎片,進而造成插入效能的下降.

總之,在資料量大一些的情況下,用自增主鍵效能會好一些.

圖片來源於《高效能MySQL》: 其中預設字尾為使用自增ID,_uuid為使用UUID為主鍵的測試,測試了插入100w行和300w行的效能.

關於主鍵是聚簇索引,如果沒有主鍵,InnoDB會選擇一個唯一鍵來作為聚簇索引,如果沒有唯一鍵,會生成一個隱式的主鍵.

If you define a PRIMARY KEY on your table, InnoDB uses it as the clustered index.

If you do not define a PRIMARY KEY for your table, MySQL picks the first UNIQUE index that has only NOT NULL columns as the primary key and InnoDB uses it as the clustered index.

3. 欄位為什麼要求定義為not null?

MySQL官網這樣介紹:

NULL columns require additional space in the rowto record whether their values are NULL. For MyISAM tables, each NULL columntakes one bit extra, rounded up to the nearest byte.

null值會佔用更多的位元組,且會在程式中造成很多與預期不符的情況.

4. 如果要儲存使用者的密碼雜湊,應該使用什麼欄位進行儲存?

密碼雜湊,鹽,使用者身份證號等固定長度的字串應該使用char而不是varchar來儲存,這樣可以節省空間且提高檢索效率.

儲存引擎相關

1. MySQL支援哪些儲存引擎?

MySQL支援多種儲存引擎,比如InnoDB,MyISAM,Memory,Archive等等.在大多數的情況下,直接選擇使用InnoDB引擎都是最合適的,InnoDB也是MySQL的預設儲存引擎.

16. InnoDB和MyISAM有什麼區別?

• InnoDB支援事物，而MyISAM不支援事物
• InnoDB支援行級鎖，而MyISAM支援表級鎖
• InnoDB支援MVCC, 而MyISAM不支援
• InnoDB支援外來鍵，而MyISAM不支援
• InnoDB不支援全文索引，而MyISAM支援。

零散問題

1. MySQL中的varchar和char有什麼區別.

char是一個定長欄位,假如申請了char(10)的空間,那麼無論實際儲存多少內容.該欄位都佔用10個字元,而varchar是變長的,也就是說申請的只是最大長度,佔用的空間為實際字元長度+1,最後一個字元儲存使用了多長的空間.

在檢索效率上來講,char > varchar,因此在使用中,如果確定某個欄位的值的長度,可以使用char,否則應該儘量使用varchar.例如儲存使用者MD5加密後的密碼,則應該使用char.

2. varchar(10)和int(10)代表什麼含義?

varchar的10代表了申請的空間長度,也是可以儲存的資料的最大長度,而int的10只是代表了展示的長度,不足10位以0填充.也就是說,int(1)和int(10)所能儲存的數字大小以及佔用的空間都是相同的,只是在展示時按照長度展示.

3. MySQL的binlog有有幾種錄入格式?分別有什麼區別?

有三種格式,statement,row和mixed.

• statement模式下,記錄單元為語句.即每一個sql造成的影響會記錄.由於sql的執行是有上下文的,因此在儲存的時候需要儲存相關的資訊,同時還有一些使用了函式之類的語句無法被記錄複製.
• row級別下,記錄單元為每一行的改動,基本是可以全部記下來但是由於很多操作,會導致大量行的改動(比如alter table),因此這種模式的檔案儲存的資訊太多,日誌量太大.
• mixed. 一種折中的方案,普通操作使用statement記錄,當無法使用statement的時候使用row.

此外,新版的MySQL中對row級別也做了一些優化,當表結構發生變化的時候,會記錄語句而不是逐行記錄.

4. 超大分頁怎麼處理?

超大的分頁一般從兩個方向上來解決.

• 資料庫層面,這也是我們主要集中關注的(雖然收效沒那麼大),類似於select * from table where age > 20 limit 1000000,10這種查詢其實也是有可以優化的餘地的. 這條語句需要load1000000資料然後基本上全部丟棄,只取10條當然比較慢. 當時我們可以修改為select * from table where id in (select id from table where age > 20 limit 1000000,10).這樣雖然也load了一百萬的資料,但是由於索引覆蓋,要查詢的所有欄位都在索引中,所以速度會很快. 同時如果ID連續的好,我們還可以select * from table where id > 1000000 limit 10,效率也是不錯的,優化的可能性有許多種,但是核心思想都一樣,就是減少load的資料.
• 從需求的角度減少這種請求....主要是不做類似的需求(直接跳轉到幾百萬頁之後的具體某一頁.只允許逐頁檢視或者按照給定的路線走,這樣可預測,可快取)以及防止ID洩漏且連續被人惡意攻擊.

解決超大分頁,其實主要是靠快取,可預測性的提前查到內容,快取至redis等k-V資料庫中,直接返回即可.

在阿里巴巴《Java開發手冊》中,對超大分頁的解決辦法是類似於上面提到的第一種.

5. 關心過業務系統裡面的sql耗時嗎?統計過慢查詢嗎?對慢查詢都怎麼優化過?

在業務系統中,除了使用主鍵進行的查詢,其他的我都會在測試庫上測試其耗時,慢查詢的統計主要由運維在做,會定期將業務中的慢查詢反饋給我們.

慢查詢的優化首先要搞明白慢的原因是什麼? 是查詢條件沒有命中索引?是load了不需要的資料列?還是資料量太大?

所以優化也是針對這三個方向來的,

• 首先分析語句,看看是否load了額外的資料,可能是查詢了多餘的行並且拋棄掉了,可能是載入了許多結果中並不需要的列,對語句進行分析以及重寫.

• 分析語句的執行計劃,然後獲得其使用索引的情況,之後修改語句或者修改索引,使得語句可以儘可能的命中索引.

• 如果對語句的優化已經無法進行,可以考慮表中的資料量是否太大,如果是的話可以進行橫向或者縱向的分表.

6. 上面提到橫向分表和縱向分表,可以分別舉一個適合他們的例子嗎?

橫向分表是按行分表.假設我們有一張使用者表,主鍵是自增ID且同時是使用者的ID.資料量較大,有1億多條,那麼此時放在一張表裡的查詢效果就不太理想.我們可以根據主鍵ID進行分表,無論是按尾號分,或者按ID的區間分都是可以的. 假設按照尾號0-99分為100個表,那麼每張表中的資料就僅有100w.這時的查詢效率無疑是可以滿足要求的.

縱向分表是按列分表.假設我們現在有一張文章表.包含欄位id-摘要-內容.而系統中的展示形式是重新整理出一個列表,列表中僅包含標題和摘要,當使用者點選某篇文章進入詳情時才需要正文內容.此時,如果資料量大,將內容這個很大且不經常使用的列放在一起會拖慢原表的查詢速度.我們可以將上面的表分為兩張.id-摘要,id-內容.當使用者點選詳情,那主鍵再來取一次內容即可.而增加的儲存量只是很小的主鍵欄位.代價很小.

當然,分表其實和業務的關聯度很高,在分表之前一定要做好調研以及benchmark.不要按照自己的猜想盲目操作.

7. 什麼是儲存過程？有哪些優缺點？

儲存過程是一些預編譯的SQL語句。 1、更加直白的理解：儲存過程可以說是一個記錄集，它是由一些T-SQL語句組成的程式碼塊，這些T-SQL語句程式碼像一個方法一樣實現一些功能（對單表或多表的增刪改查），然後再給這個程式碼塊取一個名字，在用到這個功能的時候呼叫他就行了。 2、儲存過程是一個預編譯的程式碼塊，執行效率比較高,一個儲存過程替代大量T_SQL語句 ，可以降低網路通訊量，提高通訊速率,可以一定程度上確保資料安全

但是,在網際網路專案中,其實是不太推薦儲存過程的,比較出名的就是阿里的《Java開發手冊》中禁止使用儲存過程,我個人的理解是,在網際網路專案中,迭代太快,專案的生命週期也比較短,人員流動相比於傳統的專案也更加頻繁,在這樣的情況下,儲存過程的管理確實是沒有那麼方便,同時,複用性也沒有寫在服務層那麼好.

8. 說一說三個正規化

第一正規化: 每個列都不可以再拆分. 第二正規化: 非主鍵列完全依賴於主鍵,而不能是依賴於主鍵的一部分. 第三正規化: 非主鍵列只依賴於主鍵,不依賴於其他非主鍵.

在設計資料庫結構的時候,要儘量遵守三正規化,如果不遵守,必須有足夠的理由.比如效能. 事實上我們經常會為了效能而妥協資料庫的設計.

9. MyBatis中的#和$有什麼區別?

亂入了一個奇怪的問題.....我只是想單獨記錄一下這個問題,因為出現頻率太高了.

# 會將傳入的內容當做字串,而$會直接將傳入值拼接在sql語句中.

所以#可以在一定程度上預防sql注入攻擊.

完。

ChangeLog

2019-05-19 完成

以上皆為個人所思所得，如有錯誤歡迎評論區指正。

歡迎轉載，煩請署名並保留原文連結。

聯絡郵箱：huyanshi2580@gmail.com

更多學習筆記見個人部落格或者關注上面微信公眾號------>呼延十