2020年最新Java面試中常問的資料庫方面問題

MySQL

為什么用自增列作為主鍵

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來重建表并優化填充頁面，

為什么使用資料索引能提高效率

1. 資料索引的存盤是有序的
2. 在有序的情況下，通過索引查詢一個資料是無需遍歷索引記錄的
3. 極端情況下，資料索引的查詢效率為二分法查詢效率，趨近于 log2(N)

大家覺得本次面試題總結的寫得不錯的朋友，大家可以轉發+關注，然后掃描下方二維碼獲取更多面試題以及答案— 掃描添加暗號：【CSDN】

B+樹索引和哈希索引的區別

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

哈希索引就是采用一定的哈希演算法，把鍵值換算成新的哈希值，檢索時不需要類似B+樹那樣從根節點到葉子節點逐級查找，只需一次哈希演算法即可,是無序的

哈希索引的優勢：

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

哈希索引不適用的場景：

1. 不支持范圍查詢
2. 不支持索引完成排序
3. 不支持聯合索引的最左前綴匹配規則

通常，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. 經常和主欄位一塊查詢但主欄位索引值比較多的表欄位

MySQL磁區

一. 什么是表磁區？

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

二. 表磁區與分表的區別

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

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

三. 表磁區有什么好處？

1. 磁區表的資料可以分布在不同的物理設備上，從而高效地利用多個硬體設備， 2. 和單個磁盤或者檔案系統相比，可以存盤更多資料
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支持的磁區型別有哪些？

1. RANGE磁區： 這種模式允許將資料劃分不同范圍，例如可以將一個表通過年份劃分成若干個磁區
2. LIST磁區： 這種模式允許系統通過預定義的串列的值來對資料進行分割，按照List中的值磁區，與RANGE的區別是，range磁區的區間范圍值是連續的，
3. HASH磁區 ：這中模式允許通過對表的一個或多個列的Hash Key進行計算，最后通過這個Hash碼不同數值對應的資料區域進行磁區，例如可以建立一個對表主鍵進行磁區的表，
4. KEY磁區 ：上面Hash模式的一種延伸，這里的Hash Key是MySQL系統產生的，

四種隔離級別

1. Serializable (串行化)：可避免臟讀、不可重復讀、幻讀的發生，
2. Repeatable read (可重復讀)：可避免臟讀、不可重復讀的發生，
3. Read committed (讀已提交)：可避免臟讀的發生，
4. Read uncommitted (讀未提交)：最低級別，任何情況都無法保證，

關于MVVC

MySQL InnoDB存盤引擎，實作的是基于多版本的并發控制協議——MVCC (Multi-Version Concurrency Control) (注：與MVCC相對的，是基于鎖的并發控制，Lock-Based Concurrency Control)，MVCC最大的好處：讀不加鎖，讀寫不沖突，在讀多寫少的OLTP應用中，讀寫不沖突是非常重要的，極大的增加了系統的并發性能，現階段幾乎所有的RDBMS，都支持了MVCC，

1. LBCC：Lock-Based Concurrency Control，基于鎖的并發控制，
2. MVCC：Multi-Version Concurrency Control，基于多版本的并發控制協議，純粹基于鎖的并發機制并發量低，MVCC是在基于鎖的并發控制上的改進，主要是在讀操作上提高了并發量，

在MVCC并發控制中，讀操作可以分成兩類：

1. 快照讀 (snapshot read)：讀取的是記錄的可見版本 (有可能是歷史版本)，不用加鎖（共享讀鎖s鎖也不加，所以不會阻塞其他事務的寫），
2. 當前讀 (current read)：讀取的是記錄的最新版本，并且，當前讀回傳的記錄，都會加上鎖，保證其他事務不會再并發修改這條記錄，

行級鎖定的優點：

1. 當在許多執行緒中訪問不同的行時只存在少量鎖定沖突，
2. 回滾時只有少量的更改
3. 可以長時間鎖定單一的行，

行級鎖定的缺點：

1. 比頁級或表級鎖定占用更多的記憶體，
2. 當在表的大部分中使用時，比頁級或表級鎖定速度慢，因為你必須獲取更多的鎖，
3. 如果你在大部分資料上經常進行GROUP BY操作或者必須經常掃描整個表，比其它鎖定明顯慢很多，
4. 用高級別鎖定，通過支持不同的型別鎖定，你也可以很容易地調節應用程式，因為其鎖成本小于行級鎖定，

MySQL觸發器簡單實體

1. CREATE TRIGGER <觸發器名稱> --觸發器必須有名字，最多64個字符，可能后面會附有分隔符.它和MySQL中其他物件的命名方式基本相象.
2. { BEFORE | AFTER } --觸發器有執行的時間設定：可以設定為事件發生前或后，
3. { INSERT | UPDATE | DELETE } --同樣也能設定觸發的事件：它們可以在執行insert、update或delete的程序中觸發，
4. ON <表名稱> --觸發器是屬于某一個表的:當在這個表上執行插入、 更新或洗掉操作的時候就導致觸發器的激活. 我們不能給同一張表的同一個事件安排兩個觸發器，
5. FOR EACH ROW --觸發器的執行間隔：FOR EACH ROW子句通知觸發器 每隔一行執行一次動作，而不是對整個表執行一次，
6. <觸發器SQL陳述句> --觸發器包含所要觸發的SQL陳述句：這里的陳述句可以是任何合法的陳述句， 包括復合陳述句，但是這里的陳述句受的限制和函式的一樣，

什么是存盤程序

簡單的說，就是一組SQL陳述句集，功能強大，可以實作一些比較復雜的邏輯功能，類似于JAVA語言中的方法；

ps:存盤程序跟觸發器有點類似，都是一組SQL集，但是存盤程序是主動呼叫的，且功能比觸發器更加強大，觸發器是某件事觸發后自動呼叫；

有哪些特性

1. 有輸入輸出引數，可以宣告變數，有if/else, case,while等控制陳述句，通過撰寫存盤程序，可以實作復雜的邏輯功能；
2. 函式的普遍特性：模塊化，封裝，代碼復用；
3. 速度快，只有首次執行需經過編譯和優化步驟，后續被呼叫可以直接執行，省去以上步驟；

MySQL優化

1. 開啟查詢快取，優化查詢
2. explain你的select查詢，這可以幫你分析你的查詢陳述句或是表結構的性能瓶頸，EXPLAIN 的查詢結果還會告訴你你的索引主鍵被如何利用的，你的資料表是如何被搜索和排序的
3. 當只要一行資料時使用limit 1，MySQL資料庫引擎會在找到一條資料后停止搜索，而不是繼續往后查少下一條符合記錄的資料
4. 為搜索欄位建索引
5. 使用 ENUM 而不是 VARCHAR，如果你有一個欄位，比如“性別”，“國家”，“民族”，“狀態”或“部門”，你知道這些欄位的取值是有限而且固定的，那么，你應該使用 ENUM 而不是VARCHAR，
6. Prepared Statements Prepared Statements很像存盤程序，是一種運行在后臺的SQL陳述句集合，我們可以從使用 prepared statements 獲得很多好處，無論是性能問題還是安全問題，Prepared Statements 可以檢查一些你系結好的變數，這樣可以保護你的程式不會受到“SQL注入式”攻擊
7. 垂直分表
8. 選擇正確的存盤引擎

key和index的區別

1. key 是資料庫的物理結構，它包含兩層意義和作用，一是約束（偏重于約束和規范資料庫的結構完整性），二是索引（輔助查詢用的），包括primary key, unique key, foreign key 等
2. 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要高；

如何選擇：

1. 是否要支持事務，如果要請選擇innodb，如果不需要可以考慮MyISAM；
2. 如果表中絕大多數都只是讀查詢，可以考慮MyISAM，如果既有讀寫也挺頻繁，請使用InnoDB，
3. 系統奔潰后，MyISAM恢復起來更困難，能否接受；
4. MySQL5.5版本開始Innodb已經成為Mysql的默認引擎(之前是MyISAM)，說明其優勢是有目共睹的，如果你不知道用什么，那就用InnoDB，至少不會差，

大家覺得本次面試題總結的寫得不錯的朋友，大家可以轉發+關注，然后掃描下方二維碼獲取更多面試題以及答案— 掃描添加暗號：【CSDN】

資料庫表創建注意事項

一、欄位名及欄位配制合理性

1. 剔除關系不密切的欄位
2. 欄位命名要有規則及相對應的含義（不要一部分英文，一部分拼音，還有類似a.b.c這樣不明含義的欄位）
3. 欄位命名盡量不要使用縮寫（大多數縮寫都不能明確欄位含義）
4. 欄位不要大小寫混用（想要具有可讀性，多個英文單詞可使用下劃線形式連接）
5. 欄位名不要使用保留字或者關鍵字
6. 保持欄位名和型別的一致性
7. 慎重選擇數字型別
8. 給文本欄位留足余量

二、系統特殊欄位處理及建成后建議

1. 添加洗掉標記（例如操作人、洗掉時間）
2. 建立版本機制

三、表結構合理性配置

1. 多型欄位的處理，就是表中是否存在欄位能夠分解成更小獨立的幾部分（例如：人可以分為男人和女人）
2. 多值欄位的處理，可以將表分為三張表，這樣使得檢索和排序更加有調理，且保證資料的完整性！

四、其它建議

1. 對于大資料欄位，獨立表進行存盤，以便影響性能（例如：簡介欄位）
2. 使用varchar型別代替char，因為varchar會動態分配長度，char指定長度是固定的，
3. 給表創建主鍵，對于沒有主鍵的表，在查詢和索引定義上有一定的影響，
4. 避免表欄位運行為null，建議設定默認值（例如：int型別設定默認值為0）在索引查詢上，效率立顯！
5. 建立索引，最好建立在唯一和非空的欄位上，建立太多的索引對后期插入、更新都存在一定的影響（考慮實際情況來創建），

Redis

Redis單執行緒問題

單執行緒指的是網路請求模塊使用了一個執行緒（所以不需考慮并發安全性），即一個執行緒處理所有網路請求，其他模塊仍用了多個執行緒，

為什么說Redis能夠快速執行

1. 絕大部分請求是純粹的記憶體操作（非常快速）
2. 采用單執行緒,避免了不必要的背景關系切換和競爭條件
3. 非阻塞IO - IO多路復用

Redis的內部實作

內部實作采用epoll，采用了epoll+自己實作的簡單的事件框架，epoll中的讀、寫、關閉、連接都轉化成了事件，然后利用epoll的多路復用特性，不在io上浪費一點時間 這3個條件不是相互獨立的，特別是第一條，如果請求都是耗時的，采用單執行緒吞吐量及性能很差，redis為特殊的場景選擇了合適的技術方案，

Redis關于執行緒安全問題

redis實際上是采用了執行緒封閉的觀念，把任務封閉在一個執行緒，自然避免了執行緒安全問題，不過對于需要依賴多個redis操作的復合操作來說，依然需要鎖，而且有可能是分布式鎖，

使用Redis有哪些好處？

1. 速度快，因為資料存在記憶體中，類似于HashMap，HashMap的優勢就是查找和操作的時間復雜度都是O(1)
2. 支持豐富資料型別，支持string，list，set，sorted set，hash
3. 支持事務，操作都是原子性，所謂的原子性就是對資料的更改要么全部執行，要么全部不執行
4. 豐富的特性：可用于快取，訊息，按key設定過期時間，過期后將會自動洗掉

redis相比memcached有哪些優勢？

1. memcached所有的值均是簡單的字串，redis作為其替代者，支持更為豐富的資料型別
2. redis的速度比memcached快很多
3. redis可以持久化其資料
4. Redis支持資料的備份，即master-slave模式的資料備份，
5. 使用底層模型不同，它們之間底層實作方式 以及與客戶端之間通信的應用協議不一樣，Redis直接自己構建了VM 機制 ，因為一般的系統呼叫系統函式的話，會浪費一定的時間去移動和請求，
6. value大小：redis最大可以達到1GB，而memcache只有1MB

Redis主從復制

程序原理：

1. 當從庫和主庫建立MS關系后,會向主資料庫發送SYNC命令
2. 主庫接收到SYNC命令后會開始在后臺保存快照(RDB持久化程序),并將期間接收到的寫命令快取起來
3. 當快照完成后,主Redis會將快照檔案和所有快取的寫命令發送給從Redis
4. 從Redis接收到后,會載入快照檔案并且執行收到的快取的命令
5. 之后,主Redis每當接收到寫命令時就會將命令發送從Redis，從而保證資料的一致

缺點：所有的slave節點資料的復制和同步都由master節點來處理,會照成master節點壓力太大,使用主從從結構來解決

Redis兩種持久化方式的優缺點

1. RDB 持久化可以在指定的時間間隔內生成資料集的時間點快照（point-in-time snapshot）
2. AOF 持久化記錄服務器執行的所有寫操作命令，并在服務器啟動時，通過重新執行這些命令來還原資料集，
3. Redis 還可以同時使用 AOF 持久化和 RDB 持久化，當redis重啟時,它會有限使用AOF檔案來還原資料集,因為AOF檔案保存的資料集通常比RDB檔案所保存的資料集更加完整

RDB的優點：

1. RDB 是一個非常緊湊（compact）的檔案，它保存了 Redis 在某個時間點上的資料集， 這種檔案非常適合用于進行備份： 比如說，你可以在最近的 24 小時內，每小時備份一次 RDB 檔案，并且在每個月的每一天，也備份一個 RDB 檔案， 這樣的話，即使遇上問題，也可以隨時將資料集還原到不同的版本，
2. RDB 非常適用于災難恢復（disaster recovery）：它只有一個檔案，并且內容都非常緊湊，可以（在加密后）將它傳送到別的資料中心，或者亞馬遜 S3 中，
3. RDB 可以最大化 Redis 的性能：父行程在保存 RDB 檔案時唯一要做的就是 fork 出一個子行程，然后這個子行程就會處理接下來的所有保存作業，父行程無須執行任何磁盤 I/O 操作，
4. RDB 在恢復大資料集時的速度比 AOF 的恢復速度要快

Redis常見的性能問題都有哪些？如何解決？

1. Master寫記憶體快照，save命令調度rdbSave函式，會阻塞主執行緒的作業，當快照比較大時對性能影響是非常大的，會間斷性暫停服務，所以Master最好不要寫記憶體快照，
2. Master AOF持久化，如果不重寫AOF檔案，這個持久化方式對性能的影響是最小的，但是AOF檔案會不斷增大，AOF檔案過大會影響Master重啟的恢復速度，Master最好不要做任何持久化作業，包括記憶體快照和AOF日志檔案，特別是不要啟用記憶體快照做持久化,如果資料比較關鍵，某個Slave開啟AOF備份資料，策略為每秒同步一次，
3. Master呼叫BGREWRITEAOF重寫AOF檔案，AOF在重寫的時候會占大量的CPU和記憶體資源，導致服務load過高，出現短暫服務暫停現象，
4. Redis主從復制的性能問題，為了主從復制的速度和連接的穩定性，Slave和Master最好在同一個局域網內

Redis提供6種資料淘汰策略

1. volatile-lru：從已設定過期時間的資料集（server.db[i].expires）中挑選最近最少使用的資料淘汰
2. volatile-ttl：從已設定過期時間的資料集（server.db[i].expires）中挑選將要過期的資料淘汰
3. volatile-random：從已設定過期時間的資料集（server.db[i].expires）中任意選擇資料淘汰
4. allkeys-lru：從資料集（server.db[i].dict）中挑選最近最少使用的資料淘汰
5. allkeys-random：從資料集（server.db[i].dict）中任意選擇資料淘汰
6. no-enviction（驅逐）：禁止驅逐資料

以上便是此次分享的面試題以及答案，如果覺得還不過癮，大家可以關注我的公眾號-【Java爛豬皮】，里面有往期的面試題以及最新的面試分享，關注后回復：【666】即可免費獲取更多的Java架構進階vip學習資料