24個必須掌握的資料庫面試問題~

作者：寰宇

my.oschina.net/u/3803405/blog/3068179

一、為什麼用自增列作為主鍵

1、如果我們定義了主鍵(PRIMARY KEY)，那麼InnoDB會選擇主鍵作為聚集索引。

如果沒有顯式定義主鍵，則InnoDB會選擇第一個不包含有NULL值的唯一索引作為主鍵索引。

如果也沒有這樣的唯一索引，則InnoDB會選擇內建6位元組長的ROWID作為隱含的聚集索引(ROWID隨著行記錄的寫入而主鍵遞增，這個ROWID不像ORACLE的ROWID那樣可引用，是隱含的)。

2、資料記錄本身被存於主索引（一顆B+Tree）的葉子節點上，這就要求同一個葉子節點內（大小為一個記憶體頁或磁碟頁）的各條資料記錄按主鍵順序存放

因此每當有一條新的記錄插入時，MySQL會根據其主鍵將其插入適當的節點和位置，如果頁面達到裝載因子（InnoDB預設為15/16），則開闢一個新的頁（節點）

3、如果表使用自增主鍵，那麼每次插入新的記錄，記錄就會順序新增到當前索引節點的後續位置，當一頁寫滿，就會自動開闢一個新的頁

4、如果使用非自增主鍵（如果身份證號或學號等），由於每次插入主鍵的值近似於隨機，因此每次新紀錄都要被插到現有索引頁得中間某個位置

此時MySQL不得不為了將新記錄插到合適位置而移動資料，甚至目標頁面可能已經被回寫到磁碟上而從快取中清掉，此時又要從磁碟上讀回來，這增加了很多開銷

同時頻繁的移動、分頁操作造成了大量的碎片，得到了不夠緊湊的索引結構，後續不得不通過OPTIMIZE TABLE來重建表並優化填充頁面。

二、為什麼使用資料索引能提高效率

• 資料索引的儲存是有序的

• 在有序的情況下，通過索引查詢一個資料是無需遍歷索引記錄的

• 極端情況下，資料索引的查詢效率為二分法查詢效率，趨近於 log2(N)

三、B+樹索引和雜湊索引的區別

B+樹是一個平衡的多叉樹，從根節點到每個葉子節點的高度差值不超過1，而且同層級的節點間有指標相互連結，是有序的，如下圖：

雜湊索引就是採用一定的雜湊演算法，把鍵值換算成新的雜湊值，檢索時不需要類似B+樹那樣從根節點到葉子節點逐級查詢，只需一次雜湊演算法即可,是無序的，如下圖所示：

四、雜湊索引的優勢：

等值查詢，雜湊索引具有絕對優勢（前提是：沒有大量重複鍵值，如果大量重複鍵值時，雜湊索引的效率很低，因為存在所謂的雜湊碰撞問題。）

五、雜湊索引不適用的場景：

• 不支援範圍查詢

• 不支援索引完成排序

• 不支援聯合索引的最左字首匹配規則

通常，B+樹索引結構適用於絕大多數場景，像下面這種場景用雜湊索引才更有優勢：

在HEAP表中，如果儲存的資料重複度很低（也就是說基數很大），對該列資料以等值查詢為主，沒有範圍查詢、沒有排序的時候，特別適合採用雜湊索引，例如這種SQL：

# 僅等值查詢

select id, name from table where name='李明'; 

而常用的 InnoDB 引擎中預設使用的是B+樹索引，它會實時監控表上索引的使用情況。

如果認為建立雜湊索引可以提高查詢效率，則自動在記憶體中的“自適應雜湊索引緩衝區”建立雜湊索引（在InnoDB中預設開啟自適應雜湊索引）。

通過觀察搜尋模式，MySQL會利用index key的字首建立雜湊索引，如果一個表幾乎大部分都在緩衝池中，那麼建立一個雜湊索引能夠加快等值查詢。

注意：在某些工作負載下，通過雜湊索引查詢帶來的效能提升遠大於額外的監控索引搜尋情況和保持這個雜湊表結構所帶來的開銷。

但某些時候，在負載高的情況下，自適應雜湊索引中新增的read/write鎖也會帶來競爭，比如高併發的join操作。like操作和%的萬用字元操作也不適用於自適應雜湊索引，可能要關閉自適應雜湊索引。

六、B樹和B+樹的區別

1、B樹，每個節點都儲存key和data，所有節點組成這棵樹，並且葉子節點指標為nul，葉子結點不包含任何關鍵字資訊。

2、B+樹，所有的葉子結點中包含了全部關鍵字的資訊，及指向含有這些關鍵字記錄的指標，且葉子結點本身依關鍵字的大小自小而大的順序連結

所有的非終端結點可以看成是索引部分，結點中僅含有其子樹根結點中最大（或最小）關鍵字。(而B 樹的非終節點也包含需要查詢的有效資訊)

七、為什麼說B+比B樹更適合實際應用中作業系統的檔案索引和資料庫索引？

1、B+的磁碟讀寫代價更低。

B+的內部結點並沒有指向關鍵字具體資訊的指標，因此其內部結點相對B樹更小。

如果把所有同一內部結點的關鍵字存放在同一盤塊中，那麼盤塊所能容納的關鍵字數量也越多。一次性讀入記憶體中的需要查詢的關鍵字也就越多。相對來說IO讀寫次數也就降低了。

2、B+-tree的查詢效率更加穩定。

由於非終結點並不是最終指向檔案內容的結點，而只是葉子結點中關鍵字的索引。所以任何關鍵字的查詢必須走一條從根結點到葉子結點的路。所有關鍵字查詢的路徑長度相同，導致每一個資料的查詢效率相當。

八、MySQL聯合索引

1、聯合索引是兩個或更多個列上的索引。

對於聯合索引:Mysql從左到右的使用索引中的欄位，一個查詢可以只使用索引中的一部份，但只能是最左側部分。

例如索引是key index (a,b,c). 可以支援a 、 a,b 、 a,b,c 3種組合進行查詢，但不支援 b,c進行查詢 .當最左側欄位是常量引用時，索引就十分有效。

2、利用索引中的附加列，您可以縮小搜尋的範圍，但使用一個具有兩列的索引不同於使用兩個單獨的索引。

複合索引的結構與電話簿類似，人名由姓和名構成，電話簿首先按姓氏對進行排序，然後按名字對有相同姓氏的人進行排序。

如果您知道姓，電話簿將非常有用；如果您知道姓和名，電話簿則更為有用，但如果您只知道名不知道姓，電話簿將沒有用處。

九、什麼情況下應不建或少建索引

1、表記錄太少

2、經常插入、刪除、修改的表

3、資料重複且分佈平均的表欄位，假如一個表有10萬行記錄，有一個欄位A只有T和F兩種值，且每個值的分佈概率大約為50%，那麼對這種表A欄位建索引一般不會提高資料庫的查詢速度。

4、經常和主欄位一塊查詢但主欄位索引值比較多的表欄位

十、什麼是表分割槽？

表分割槽，是指根據一定規則，將資料庫中的一張表分解成多個更小的，容易管理的部分。從邏輯上看，只有一張表，但是底層卻是由多個物理分割槽組成。

十一、表分割槽與分表的區別

分表：指的是通過一定規則，將一張表分解成多張不同的表。比如將使用者訂單記錄根據時間成多個表。

分表與分割槽的區別在於：分割槽從邏輯上來講只有一張表，而分表則是將一張表分解成多張表。

十二、表分割槽有什麼好處？

1、儲存更多資料。分割槽表的資料可以分佈在不同的物理裝置上，從而高效地利用多個硬體裝置。和單個磁碟或者檔案系統相比，可以儲存更多資料

2、優化查詢。在where語句中包含分割槽條件時，可以只掃描一個或多個分割槽表來提高查詢效率；涉及sum和count語句時，也可以在多個分割槽上並行處理，最後彙總結果。

3、分割槽表更容易維護。例如：想批量刪除大量資料可以清除整個分割槽。

4、避免某些特殊的瓶頸，例如InnoDB的單個索引的互斥訪問，ext3問價你係統的inode鎖競爭等。

十三、分割槽表的限制因素

1、一個表最多隻能有1024個分割槽

2、MySQL5.1中，分割槽表示式必須是整數，或者返回整數的表示式。在MySQL5.5中提供了非整數表示式分割槽的支援。

3、如果分割槽欄位中有主鍵或者唯一索引的列，那麼多有主鍵列和唯一索引列都必須包含進來。即：分割槽欄位要麼不包含主鍵或者索引列，要麼包含全部主鍵和索引列。

4、分割槽表中無法使用外來鍵約束

5、MySQL的分割槽適用於一個表的所有資料和索引，不能只對表資料分割槽而不對索引分割槽，也不能只對索引分割槽而不對錶分割槽，也不能只對表的一部分資料分割槽。

十四、如何判斷當前MySQL是否支援分割槽？

命令：show variables like '%partition%' 執行結果:

mysql> show variables like '%partition%';
+-------------------+-------+| Variable_name | Value |+-------------------+-------+| have_partitioning | YES |+-------------------+-------+1 row in set (0.00 sec)

have_partintioning 的值為YES，表示支援分割槽。

十五、MySQL支援的分割槽型別有哪些？

RANGE分割槽：這種模式允許將資料劃分不同範圍。例如可以將一個表通過年份劃分成若干個分割槽

LIST分割槽：這種模式允許系統通過預定義的列表的值來對資料進行分割。按照List中的值分割槽，與RANGE的區別是，range分割槽的區間範圍值是連續的。

HASH分割槽 ：這中模式允許通過對錶的一個或多個列的Hash Key進行計算，最後通過這個Hash碼不同數值對應的資料區域進行分割槽。例如可以建立一個對錶主鍵進行分割槽的表。

KEY分割槽 ：上面Hash模式的一種延伸，這裡的Hash Key是MySQL系統產生的。

十六、四種隔離級別

• Serializable (序列化)：可避免髒讀、不可重複讀、幻讀的發生。

• Repeatable read (可重複讀)：可避免髒讀、不可重複讀的發生。

• Read committed (讀已提交)：可避免髒讀的發生。

• Read uncommitted (讀未提交)：最低階別，任何情況都無法保證。

十七、關於MVVC

MySQL InnoDB儲存引擎，實現的是基於多版本的併發控制協議——MVCC (Multi-Version Concurrency Control)

注：與MVCC相對的，是基於鎖的併發控制，Lock-Based Concurrency Control

MVCC最大的好處：讀不加鎖，讀寫不衝突。在讀多寫少的OLTP應用中，讀寫不衝突是非常重要的，極大的增加了系統的併發效能，現階段幾乎所有的RDBMS，都支援了MVCC。

• LBCC：Lock-Based Concurrency Control，基於鎖的併發控制

• MVCC：Multi-Version Concurrency Control

基於多版本的併發控制協議。純粹基於鎖的併發機制併發量低，MVCC是在基於鎖的併發控制上的改進，主要是在讀操作上提高了併發量。

十八、在MVCC併發控制中，讀操作可以分成兩類：

快照讀 (snapshot read)：讀取的是記錄的可見版本 (有可能是歷史版本)，不用加鎖（共享讀鎖s鎖也不加，所以不會阻塞其他事務的寫）

當前讀 (current read)：讀取的是記錄的最新版本，並且，當前讀返回的記錄，都會加上鎖，保證其他事務不會再併發修改這條記錄

十九、行級鎖定的優點：

1、當在許多執行緒中訪問不同的行時只存在少量鎖定衝突。

2、回滾時只有少量的更改

3、可以長時間鎖定單一的行。

二十、行級鎖定的缺點：

比頁級或表級鎖定佔用更多的記憶體。

當在表的大部分中使用時，比頁級或表級鎖定速度慢，因為你必須獲取更多的鎖。

如果你在大部分資料上經常進行GROUP BY操作或者必須經常掃描整個表，比其它鎖定明顯慢很多。

用高階別鎖定，通過支援不同的型別鎖定，你也可以很容易地調節應用程式，因為其鎖成本小於行級鎖定。

二十一、MySQL優化

• 開啟查詢快取，優化查詢

• explain你的select查詢，這可以幫你分析你的查詢語句或是表結構的效能瓶頸。EXPLAIN 的查詢結果還會告訴你你的索引主鍵被如何利用的，你的資料表是如何被搜尋和排序的

• 當只要一行資料時使用limit 1，MySQL資料庫引擎會在找到一條資料後停止搜尋，而不是繼續往後查少下一條符合記錄的資料

• 為搜尋欄位建索引

• 使用 ENUM 而不是 VARCHAR。如果你有一個欄位，比如“性別”，“國家”，“民族”，“狀態”或“部門”，你知道這些欄位的取值是有限而且固定的，那麼，你應該使用 ENUM 而不是VARCHAR

• Prepared StatementsPrepared Statements很像儲存過程，是一種執行在後臺的SQL語句集合，我們可以從使用 prepared statements 獲得很多好處，無論是效能問題還是安全問題。
  Prepared Statements 可以檢查一些你繫結好的變數，這樣可以保護你的程式不會受到“SQL隱碼攻擊式”攻擊

• 垂直分表

• 選擇正確的儲存引擎

二十二、key和index的區別

key 是資料庫的物理結構，它包含兩層意義和作用，一是約束（偏重於約束和規範資料庫的結構完整性），二是索引（輔助查詢用的）。包括primary key, unique key, foreign key 等

index是資料庫的物理結構，它只是輔助查詢的，它建立時會在另外的表空間（mysql中的innodb表空間）以一個類似目錄的結構儲存。索引要分類的話，分為字首索引、全文字索引等；

二十三、Mysql 中 MyISAM 和 InnoDB 的區別有哪些？

區別：

1、InnoDB支援事務，MyISAM不支援

對於InnoDB每一條SQL語言都預設封裝成事務，自動提交，這樣會影響速度，所以最好把多條SQL語言放在begin和commit之間，組成一個事務；

2、InnoDB支援外來鍵，而MyISAM不支援。

對一個包含外來鍵的InnoDB錶轉為MYISAM會失敗；

3、InnoDB是聚集索引，資料檔案是和索引綁在一起的，必須要有主鍵，通過主鍵索引效率很高。

但是輔助索引需要兩次查詢，先查詢到主鍵，然後再通過主鍵查詢到資料。因此主鍵不應該過大，因為主鍵太大，其他索引也都會很大。

而MyISAM是非聚集索引，資料檔案是分離的，索引儲存的是資料檔案的指標。主鍵索引和輔助索引是獨立的。

4、InnoDB不儲存表的具體行數，執行select count(*) from table時需要全表掃描。而MyISAM用一個變數儲存了整個表的行數，執行上述語句時只需要讀出該變數即可，速度很快；

5、Innodb不支援全文索引，而MyISAM支援全文索引，查詢效率上MyISAM要高；

如何選擇：

• 是否要支援事務，如果要請選擇innodb，如果不需要可以考慮MyISAM；

• 如果表中絕大多數都只是讀查詢，可以考慮MyISAM，如果既有讀寫也挺頻繁，請使用InnoDB

• 系統奔潰後，MyISAM恢復起來更困難，能否接受；

• MySQL5.5版本開始Innodb已經成為Mysql的預設引擎(之前是MyISAM)，說明其優勢是有目共睹的，如果你不知道用什麼，那就用InnoDB，至少不會差。

二十四、資料庫表建立注意事項

1、欄位名及欄位配製合理性

• 剔除關係不密切的欄位；

• 欄位命名要有規則及相對應的含義（不要一部分英文，一部分拼音，還有類似a.b.c這樣不明含義的欄位）；

• 欄位命名儘量不要使用縮寫（大多數縮寫都不能明確欄位含義）；

• 欄位不要大小寫混用（想要具有可讀性，多個英文單詞可使用下劃線形式連線）；

• 欄位名不要使用保留字或者關鍵字；

• 保持欄位名和型別的一致性；

• 慎重選擇數字型別；
  -給文字欄位留足餘量；

2、系統特殊欄位處理及建成後建議

• 新增刪除標記（例如操作人、刪除時間）；

• 建立版本機制；

3、表結構合理性配置

• 多型欄位的處理，就是表中是否存在欄位能夠分解成更小獨立的幾部分（例如：人可以分為男人和女人）；

• 多值欄位的處理，可以將表分為三張表，這樣使得檢索和排序更加有調理，且保證資料的完整性！

4、其它建議

• 對於大資料欄位，獨立表進行儲存，以便影響效能（例如：簡介欄位）；

• 使用varchar型別代替char，因為varchar會動態分配長度，char指定長度是固定的；

• 給表建立主鍵，對於沒有主鍵的表，在查詢和索引定義上有一定的影響；

• 避免表欄位執行為null，建議設定預設值（例如：int型別設定預設值為0）在索引查詢上，效率立顯；

• 建立索引，最好建立在唯一和非空的欄位上，建立太多的索引對後期插入、更新都存在一定的影響（考慮實際情況來建立）；

- END -

如果看到這裡，說明你喜歡這篇文章，請轉發、點贊。掃描下方二維碼或者微信搜尋「perfect_iscas」，新增好友後即可獲得10套程式設計師全棧課程+1000套PPT和簡歷模板，向我私聊「進群」二字即可進入高質量交流群。

↓掃描二維碼進群↓

喜歡文章，點個在看