一個七年的Java程序員從業總結：比起禿頭，我更怕數據庫底層設計原理結構

前言

說到數據庫這個詞，我只能用愛恨交加這個詞來形容它。

兩年前在自己還單純懵懂的時候進了數據庫的課堂，聽完數據庫的課，覺得這是一門再簡單不過的課程，任何一門編程語言都比SQL要晦澀難懂，任何一門理論課程都比數據庫關系要復雜得多。

直到從被面試官按在地上摩擦，到工作中那一條條令人發指的慢查詢SQL，這就已經完全顛覆了我對數據庫的看法。

在有各種數據庫工具的今天，我們是看不到那簡單到不能再簡單的一張表的背后，隱藏著多少數據結構的支撐，也看不到我們隨手敲的一條SELECT，背后會有多少算法和數據結構在給我們做優化，作為一個有技術熱情的coder，應該需要對我們每日在用的數據庫做一次深入了解。

數據庫架構

如何設計一個關系型數據庫

這個問題很寬泛，需要我們對于整體有一個掌控，對我們平時所用的數據庫要有足夠的了解，對整個數據庫做模塊劃分是這道題的關鍵，這就更需要我們足夠了解數據庫，對數據庫做一個合理的模塊設計。

設計

從開始，首先要明白，數據庫的最最根本的作用是什么——存儲數據，所以我們需要一個存儲模塊來存儲我們的數據，它可以是一個文件系統（機械硬盤？SSD固態硬盤？）。

但光有存儲模塊是不夠的，我們還需要一個程序實例，來組織或者獲取這些數據，在程序實例中我們需要提供獲取和組織這些數據的方式，所以我們需要在程序實例中實現存儲管理模塊。我們還可以在存儲管理模塊中做一些提升效能的工作，例如同時讀取多行、分塊分頁存儲等。數據庫作為一款對性能要求極高的軟件，我們應該加入緩存機制，來提高其速度，當查詢到緩存中已存在的數據，我們應該直接將其從緩存中讀取，這樣可以減少硬盤IO次數，提高效能。

到這里為止，我們已經完成了對數據庫的存儲方面的功能設計，但是作為數據庫，應該需要提供給用戶對數據進行增刪改查的接口，即平時所寫的SQL，所以我們應該提供一個SQL解析模塊，來對日常用戶所寫的SQL語句進行解析，轉換成機器可識別的指令，我們也可以直接將編譯過的SQL加入緩存，下次再有同樣的SQL就直接從緩存中讀取，同樣可以提高效能。作為一款成熟的數據庫，需要應對各種復雜的環境，要時刻記錄數據庫的狀態，所以我們還需要一個日志管理模塊，對操作和錯誤信息進行記錄。

數據庫中需要支持多用戶操作，但每個用戶都能操作所有的數據，這是不現實的，所以還需要權限劃分模塊對數據庫用戶進行權限管理。當然數據庫說到底也只是一款軟件，是軟件就會有bug，就會出故障，故障不可怕，可怕的是在數據庫這種敏感軟件下對故障沒有特殊的處理方式，導致數據丟失，畢竟數據是無價的，所以數據庫應該引入容災機制，在數據庫掛了的時候，對數據進行恢復。還有作為數據庫最重要的兩個模塊，也是現今任何一個數據庫都需要考慮的問題——并發和查找效率，所以還需引入索引和鎖這兩個模塊，這就是實現一個最基礎的數據庫所必需的幾大模塊。

歸納

綜上對數據庫設計模塊做一個匯總：

1.存儲模塊

2.程序實例

2.1 存儲管理模塊

2.2 緩存機制

2.3 SQL解析模塊

2.4 日志管理模塊

2.5 權限劃分模塊

2.6 容災機制

2.7 索引模塊

2.8 鎖模塊

索引

為什么要使用索引

要考慮這個問題，首先要從最基礎的查找表中數據的過程開始說起。通常我們在查找一個序列中的某一個元素時，用到的最簡單的方式就是遍歷，數據庫也是一樣，在一張表中查找某一行數據時，如果不考慮索引的狀況下，也會采用一個逐行掃描的方式，只不過數據庫通常以塊或者頁為單位，所以它通常將整個塊或者頁加載進內存，然后逐塊輪詢查找到結果并返回。如果數據庫中只有少量數據，那么進行全表掃描，速度還是會很快，但是如果在數據量很大的表中，這種方法就不再適用了，在數據量很大的表中，由于逐行掃描代價變大，通常需要避免采用這種逐行掃描的方式進行數據查找，數據庫為了使查詢變得高效，所以引入了索引這種方式對數據進行查找。

什么樣的信息能成為索引

1.主鍵、唯一鍵、普通鍵

索引的數據結構

眾所周知，二叉查找樹是每個節點最多由兩個子樹的樹結構，而其還有一個特點是，在任意一顆樹中，根節點的左孩子永遠小于根節點，根節點的右孩子永遠大于根節點，用二叉查找樹作為索引，確實可以提高查找效率，其可以使用二分查找將時間復雜度控制在O(lgn)，但是二叉查找樹有一個顯而易見的缺陷，當某種特殊情況（按照某個特定順序插入樹）發生時，二叉查找樹將變為下圖右側（線性二叉樹）的狀況：

此時二叉查找樹查找任意某個元素時，其查找順序與逐行查找無異，查詢時間復雜度又將回到O(n)，查詢效率無法保持。

B-Tree，平衡多路查找樹，如果每個節點，最多有N個孩子，那么這樣的樹就叫N階B-Tree，每個節點中主要包含關鍵字和指向孩子的指針，最多能有幾個孩子，取決于節點的容量和數據庫的相關配置，通常情況下這個N是很大的。

B-Tree作為一種數據結構，有如下特征：

1.根節點至少包含兩個孩子

2.樹中每個節點至多含有N個孩子（N>=2)

3.除根節點和葉節點外，其它每個節點至少有ceil(N/2)個孩子。（ceil表示取上限，例如1.2的上限為2，1.1的上限也為2，非四舍五入）

4.所有葉子節點都位于同一層，即葉子節點的高度都是一樣的。

5.假設每個非終端節點包含n個關鍵字信息（P0,P1…Pn,k1…kn）

( a )ki(i=1…n)為關鍵字，且關鍵字按順序升序排序k(i-1)

( b )關鍵字的個數必須滿足：[ceil(m/2)-1]<=n<=m-1]。

( c )非葉子節點的指針：P[1],P[2]…P[M];其中P[1]指向關鍵字小于K[1]的子樹，P[N]指向關鍵字大于K[N-1]的子樹，其它P[i]指向關鍵字屬于(K[i-1],K[i])的子樹。

遵守上述規則，其目的就是盡量使每個索引塊都盡可能多的存儲數據，盡可能減少查找次數以提升效率。

舉個例子，模擬一下查找過程，以便于理解：假設我們要查詢關鍵字為10的數據，則從根節點出發，10<17，于是通過P1進入其孩子節點，10>8且10<12，于是通過P2進入其孩子節點，最后尋找到10。如果不使用索引，而使用逐行掃描的方式進行查找，則從0開始至少掃描10次才能查找到10號數據，有了索引之后可以看到，查找次數從10變為3，大大提高了查找效率。

如果這里是二叉查找樹，會出現極端情況，使得查找時間復雜度為O(n),而如果是B-Tree，由于上述五個約束，可以讓節點通過合并、分裂、上移、下移等操作，使得樹高度較二叉查找樹小，查找效率顯然更高。

B+ -Tree是B-Tree的一個變體，其定義基本與B樹相同，除了:

1.非葉子節點的子樹指針與關鍵字個數相同，其表明B+樹能存儲更多的關鍵字

2.非葉子節點的子樹指針P[i]，指向關鍵字值[K[i],K[i+1])的子樹。

3.非葉子節點僅用來做索引，數據到保存在葉子節點中。（B+樹的所有檢索都是從根部開始，直到搜索到葉子節點結束。）

4.所有葉子節點均有一個鏈指針，指向下一個葉子節點。（方便直接在葉子節點直接做范圍統計）

B+樹相較于B樹的優勢：

1.B+樹的磁盤讀寫代價更低。

2.B+樹的查詢效率更加穩定。

3.B+樹更有利于對數據庫的掃描。

Hash索引是根據Hash結構的定義，只需要一次運算便可以找到數據所在位置，不像B+樹或者B樹需要從根結點出發尋找數據，所以Hash索引的查詢效率理論上要高于B+樹索引，但是MySQL中并沒有采用這一種索引，這是由于這種索引除查詢效率之外的缺陷是十分明顯的。

1.僅僅只能滿足"=",“IN”,不能使用范圍查詢。由于其是由Hash運算獲取的數據存放位置，每次Hash運算獲取的是一個確定的值，且這個值并不與數據本身的大小有關系，所以其并不能滿足范圍查詢。

2.無法被用來避免數據的排序操作。和1的意思差不多，Hash的索引值是由Hash運算獲取的，其索引值與數據本身的大小并無明顯關系。

3.不能利用部分索引鍵查詢。

4.不能避免表掃描。由于Hash索引會產生Hash沖突，存在Hash沖突的數據會被連接到同一個鏈表上，當大量數據被連接到相同鏈表上時，查詢某條數據就變成了掃描該鏈表，時間復雜度并不能保證在O(1)。

5.遇到大量Hash值相等的情況后性能并不一定就會比B-Tree索引高。

位圖索引，當表中的某個字段只有幾種值的時候，例如：性別，此時用位圖索引是一個最佳的選擇。目前使用位圖索引的比較主流的數據庫有Oracle數據庫。

密集索引和稀疏索引的區別

1.密集索引文件中的每個搜索碼都對應一個索引值，稀疏索引文件只為索引碼的某些值建立索引項。

2.密集索引將數據存儲與索引放到了一塊，找到索引也就找到了數據，稀疏索引將數據和索引分開存儲，索引結構的葉子節點指向數據的對應行。

MyISAM不論是主鍵索引、唯一鍵索引、還是普通索引，都采用的是稀疏索引，而InnoDB必須有且僅有一個密集索引。

InnoDB密集索引規則：

1.若一個主鍵被定義，該主鍵則作為密集索引。

2.若沒有主鍵被定義，該表的第一個唯一非空索引則作為密集索引。

3.若不滿足以上條件，InnoDB內部會生成一個隱藏主鍵（密集索引）。

4.非主鍵索引存儲相關鍵位和其對應的主鍵值，包含兩次查找。

注：InnoDB如果查詢條件為主鍵索引，則只需查詢一次，但是輔助索引需要查詢兩次，先通過輔助索引查詢到主鍵索引，再查詢到數據。

從上圖中可以看到，如果一個索引是聚集索引，則其葉子節點上存放的即是數據本身，而如果一個索引是稀疏索引，葉子節點存放的僅是地址，指向將要查找的數據。

如何定位并優化慢查詢SQL

首先先建立一張表

CREATE DATABASE sqltest; use sqltest; create table tb_test( test_id int primary key auto_increment, test_name varchar(1024), test_date datetime, test_desc varchar(1024) );

1

2

3

4

5

6

7

8

在這張表中灌入200w數據。

1.根據慢日志定位慢查詢SQL

#查找慢日志 slow_query_log

1

2

記錄慢日志SQL運行時間閾值

設置慢查詢閾值為1秒，重連數據庫

set global long_query_time = 1;

1

制造慢查詢：

2.使用explain工具分析SQL

explain select test_name from tb_test order by test_name desc

1

找到數據的方式，根據效率從高到低排序有如下幾種

system>const>eq_ref>ref>fulltext>ref_or_null>index_merge>unique_subquery>index_subquery>range>index>all

如果type為index或all，表示本次掃描為全表掃描，說明這個語句是需要優化的。

可以用來輔助type幫助我們進行SQL優化，extra中出現以下兩項，意味著MySQL根本不能使用索引，效率會受到重大影響，應該盡可能對此進行優化。

Using filesort:表示MySQL會對結果使用一個外部索引排序，而不是從表里按索引次序讀到相關內容，可能在內存或者磁盤上進行排序。MySQL中無法例用索引完成的排序操作稱為“文件排序”。

表示MySQL在對查詢結果排序時使用臨時表，常見于排序order by和分組查詢group by。

3.修改SQL，盡量讓SQL走索引

我們可以知道，創建表時，我們將id設為主鍵，那么id也就自然稱為了索引，所以我們只要修改排序字段為id，即可以通過索引排序。

explain select test_id from tb_test order by test_id desc

1

key:PRIMARY 代表使用了主鍵索引

另一種情況，在特定狀況下一定需要使用name進行排序，那還有一種做法，就是將name字段加索引。

#加索引 ALTER TABLE tb_test add index index_name(test_name); #再次分析 explain select test_name from tb_test order by test_name desc;

1

2

3

4

結果：

聯合索引的最左匹配原則的成因

上文中只是用了單一索引對表進行排序，如果使用聯合索引又會是什么樣的一種狀況？

最左匹配原則：假設數據表中有兩列，AandB,我們將A、B設置為聯合索引，然后在where語句中調用where A= ? AND B=?，該查詢語句會使用AB聯合索引，調用where A=?，該查詢語句也會使用AB聯合索引，但當調用where B=？時，它將不會使用AB聯合索引。

官方定義：

1.最左前綴匹配原則，MySQL會一直向右匹配直到遇到范圍查詢（>、<、between、like）就停止匹配，比如 a=3 and b=4 and c>5 and d=6,如果建立(a,b,c,d)順序的索引，d是無法使用索引的，如果建立(a,b,d,c)的索引則都可以使用到，a、b、d的順序可以任意調整。

2.=和in可以亂序，比如 a=1 and b=2 and c=3 建立(a,b,c)索引可以任意順序，MySQL的查詢優化器會幫你優化成索引可以識別的形式。

索引是建立的越多越好嗎

答：NO，數據量小的表不需要建立索引，建立會增加額外的索引開銷。另外數據變更需要維護索引，因此更多的索引意味著更多的維護成本。更多的索引還需要消耗更多的空間。

鎖and鎖and鎖

MyISAM與InnoDB關于鎖方面的區別是什么

1.MyISAM默認使用表級鎖，不支持行級鎖。

2.InnoDB默認使用行級鎖，也支持表級鎖。

3.InnoDB在使用索引時，默認使用行級鎖，但當其沒有用到索引時，默認使用表級鎖。

當一條數據庫語句對某條數據進行操作時，默認將整張表進行加鎖，其余操作無法對表進行更改，需等到當前操作執行完畢釋放鎖后，才能對該表進行更改。

當一條數據庫語句對某條數據或多條數據進行操作時，默認將正在操作的這幾條數據進行加鎖，其余操作無法對當前語句正在操作的這幾條數據進行操作，但可以操作其他數據。

共享鎖：當對某行或某張表上了共享鎖之后，其它任意語句依然支持上共享鎖，但不支持上排他鎖。（MySQL使用lock in share mode加共享鎖）

排他鎖：當對某行或某張表上了排他鎖之后，其它任意語句對這一行或者這一張表進行讀或寫都是不允許的。（MySQL使用 for update 加排他鎖）

悲觀鎖：總是假設最壞的情況，認為競爭總是存在，認為每次對數據的修改總會產生沖突，因此每次都會先上鎖，其他線程阻塞等待釋放鎖。

樂觀鎖：總是假設最好的情況，認為競爭總是不存在，認為每次對數據的修改都不會產生沖突，因此不會先上鎖，再最后更新的時候，比較數據是否已經被更新，可用版本號或CAS實現。

答：不一定，鎖的粒度越細，其消耗的資源代價越高。行級鎖在上鎖的時候需要掃描到某行再進行上鎖，這樣的代價是較大的。

MyISAM適合的場景：1.頻繁執行全表count語句。2.對數據進行增刪改的頻率不高，查詢非常頻繁。3.沒有事務

InnoDB適合的場景：1.數據增刪改查都相當頻繁。2.可靠性要求比較高，存在事務。

數據庫事務四大特性ACID

事務：作為單個邏輯單元執行的一個操作，要么全部完成，要么全部失敗。

事務包含的所有操作，要么全部執行，要么全部失敗。

多個事務并發執行時，一個事務的執行不應該影響其它事務的執行。

事務應保證數據庫狀態從一個一致性狀態轉換到另一個一致性狀態。（一致性狀態：數據庫的數據應滿足完整性約束）

一個事務一旦提交，它的數據應該永久地保存在數據庫中。

事務隔離級別以及各級別下的并發訪問問題

1.更新丟失—— 一個事務的更新，引起另一個事務提交的丟失，MySQL所有事務隔離級別在數據庫層面上均可避免。

2.臟讀—— 一個事務讀到另一個事務未提交的數據，READ-COMMITTED事務隔離級別以上可避免。（ORACLE默認隔離級別為READ-COMMITTED）

3.不可重復讀——事務A多次讀取同一數據時，事務B修改該數據，導致事務A每次讀取到的數據結果不一致，REPEATABLE-READ隔離級別下可避免。

4.幻讀——事務A多次讀取同一數據時，事務B對事務A的影響區間內進行增刪操作，導致事務A讀取到的數據一會多、一會少，就像產生幻覺了一樣。SERIALIZABLE事務隔離級別可避免。（但實際上MySQL的InnoDB在REPEATABLE-READ隔離級別下避免了幻讀的發生）

InnoDB可重復讀隔離級別下如何避免幻讀

快照讀（非阻塞讀）–偽MVCC。使用此種機制避免使我們看到“幻行”。

當前讀：select …lock in share mode,select …for update,update,delete,insert.即加了鎖的增刪改查語句。由于其讀取的是記錄的最新版本，所以稱為當前讀。

快照讀：不加鎖的非阻塞讀（非SERIALIZABLE），select。基于提升并發性能的考慮，基于多版本并發控制。既然是基于多版本，也就是說快照讀有可能讀到非最新版本的數據。

next-key鎖（行鎖+gap鎖）：next-key鎖由兩部分組成，行鎖+gap鎖，行鎖即對單個行記錄上的鎖。Gap鎖，即對插入索引間的空隙上鎖，即鎖定一個范圍，但不包括記錄本身。Gap鎖的目的，是為了防止一個記錄兩次當前讀出現幻讀的情況。Gap鎖只存在與Read-Committed（不包括Read-committed）以上的隔離級別存在。如果查詢時，where條件全部命中（精確查詢時），則不會用Gap鎖，只會加記錄鎖。因為在精確查詢的狀況下，即使在讀結果集的過程中，另一個事務增加一條數據，也不會增加到當前結果集下，只會在where條件的范圍之外，所以并不會產生幻讀現象，加行鎖就足夠了。如果where條件部分命中或者全不命中，則會加Gap鎖。

RC、RR級別下的InnoDB的非阻塞讀（快照讀）如何實現

1.數據行里的DB_TRX_ID、DB_ROLL_PTR、DB_ROW_ID字段，DB_TRX_ID標識最近一次對本行記錄做修改的事務ID，DB_ROLL_PTR回滾指針，當事務回滾時，去往undo日志尋找上一版本的數據，DB_ROW_ID行號（MySQL自動創建的隱藏自增主鍵）。

2.undo日志：存儲歷史版本的數據。當某行的某個字段進行修改時，首先用排他鎖鎖住該行，然后將該行數據拷貝一份放入undolog中，通過行中的DB_ROLL_PTR指針，指向undolog中的這條數據，然后修改當前行的值，并填寫DB_TRX_ID字段為當前事務的ID。

3.read view：可見性判斷。決定當前事務能看到的是哪個版本的數據。

我這邊整理了一份：Mybatis相關資料文檔、Spring全家桶系列，Java的系統化資料，（包括Java核心知識點、面試專題和20年最新的互聯網真題、電子書等）有需要的朋友可以關注公眾號【程序媛小琬】即可獲取。

Java 開發者 數據庫

版權聲明：本文內容由網絡用戶投稿，版權歸原作者所有，本站不擁有其著作權，亦不承擔相應法律責任。如果您發現本站中有涉嫌抄襲或描述失實的內容，請聯系我們jiasou666@gmail.com 處理，核實后本網站將在24小時內刪除侵權內容。