?數據存儲引擎

存儲引擎是 MYSQL 的核心技術，不同的存儲引擎使用不同的存儲機制、索引技巧、鎖定水平并最終提供不同的功能和能力。常見的引擎分為三種：InnoDB 存儲引擎（MYSQL 默認的事務性引擎）、MyISAM 存儲引擎、Memory 存儲引擎。三種存儲引擎的功能對比如下表所示：

總結三種引擎的使用選擇如下：

InnoDB：適合要提供提交、回滾和崩潰后的安全恢復的事務安全能力，并要求實現并發(fā)控制；

MyISAM：適合于只讀的數據，或者表比較小、可以忍受修復操作數據庫；

Memory：適用于快速查找數據，用于數據分析中產生的中間數據。

數據庫三大范式? ?java初學者組團學習:737251827

第一范式：數據庫表的每一列都是不可分割的基本數據項，即同一列不能有多個值；

第二范式：在第一范式的基礎上，非主鍵列完全依賴于主鍵，而不能是依賴于主鍵的一部分。如：設計訂單信息表時，商品名稱、商品價格等商品信息與表的主鍵不相關，而只與商品編號相關，因此可將表設計為訂單信息表和商品信息表。

第三范式：在第二范式的基礎上，非主鍵列只依賴于主鍵，不依賴于其他非主鍵。如果一張表中出現另一張表的非主鍵，可以將這兩張表用外鍵關聯，而不是將另一張表的非主鍵直接寫在當前表中。設計數據庫表的時候，要盡量遵守三范式，如果不遵守，必須有足夠的理由。

數據庫索引類型及原理

索引是一種對數據庫表中一列或多列的值進行排序的一種數據結構，指向表中特定的數據內容，從而提高查詢效率。

一、為什么要用索引

假設有一張存儲了 10 萬個數據（每條數據包含姓名、年齡、身份證號等信息）的表，若沒有索引，要想查找姓名為"張三”的身份信息，需要從上到下依次對表中的所有數據進行掃描，找到所有名為張三的數據，這也叫全表查詢。

可以看出，全表查詢的效率非常低，需要逐條對比，因此就需要通過對每條數據建立索引，從而直接通過索引快速查詢到數據信息，大大提高了查詢效率。

二、數據庫索引的類型及原理

1、B+樹索引：適合范圍查詢、順序查詢，不適合插入、刪除數據，是 InnoDB、MyISAM 的索引方式。

B 樹與 B+樹都是用于大量數據查詢的一種數據結構，二者有以下特點：

B 樹：

由二叉搜索轉變?yōu)?N 叉搜索，樹的高度大大降低，查詢次數少；

葉子節(jié)點、非葉子節(jié)點都可以存儲多個數據，每次可以讀取一頁數據，IO 次數更少；

通過中序遍歷，可以訪問樹上所有節(jié)點，但需要多次往返各個節(jié)點之間，效率有待提升；

由 B 樹的特點可知，對于范圍查詢而言，B 樹需要通過中序遍歷來進行查找，不夠完美，B+樹在此基礎上進行了改進。

B+樹的特點為：

依舊為 N 叉樹，但是非葉子只保存索引不存儲數據，所有數據存儲在同一層的葉子節(jié)點上，查詢性能更穩(wěn)定；

非葉子節(jié)點可保存更多索引，相同的數據，B+樹的高度更低，查詢的 IO 次數更少；

所有葉子節(jié)點形成一個有序鏈表，不需要通過中序遍歷進行順序查詢，更適合范圍查詢。

對比可知，B+樹最大的優(yōu)點就是適合范圍查詢，這在實際應用中是非常廣泛的，因此 InnoDB 選用的就是 B+Tree。B+樹的缺點就是插入、刪除操作非常復雜，一般只用在數據庫的查詢操作中。

2、哈希索引：適合單一數據的查找、刪除、插入，不適合范圍查找，是 Memory 的索引方式。

哈希表的查詢、刪除、插入的平均時間復雜度都是 O(1)，適合每次只查詢一條信息。但是對于需要排序查詢（對查詢的數據進行排序輸出）、范圍查詢（如：大于或小于某值的范圍查詢），采用哈希索引的時間復雜度會從 O(1)退化為 O(n)，相當于全表查詢，效率極低。總結哈希索引的特點如下：

只支持等值比較查詢，不支持范圍查詢；

訪問數據的速度非?？欤敼_突較多時，查詢效率會大大降低；

哈希索引數據不是按順序存儲的，即無序的，無法用于排序查詢。因此，哈希索引只適用于特定的場合，不要輕易使用。

三、索引分類

1、聚簇索引：葉子節(jié)點存儲整行數據

按照每張表的主鍵構造一顆 B+樹，葉子節(jié)點存放整張表的行數據。每張表只能有一個聚簇索引，如果沒有主鍵，InnoDB 會選擇非空的唯一索引代替。如果沒有這樣的索引，InnoDB 會隱式的定義一個主鍵來作為聚簇索引。

優(yōu)點：

索引和數據保存在同一顆 B+樹中，數據訪問更快；

聚簇索引對于主鍵的范圍查找速度非?？?。

2、非聚簇索引（輔助索引）：葉子節(jié)點存儲主鍵值

在聚簇索引之上創(chuàng)建的索引稱之為非聚簇索引，其葉子節(jié)點存儲的數據為主鍵值，訪問數據通常需要二次查找。

假設一張表中有 id（主鍵），order_id（唯一鍵）兩個字段。若使用“where id=14"的條件進行查詢，就會走聚簇索引，直接可以查詢出對應行數據；若使用“where order_id= 1100202"的條件進行查詢，就會走非聚簇索引，會先根據 order_id 查詢到所在行的主鍵 id，再根據主鍵 id 走聚簇索引就查到行數據。如下圖所示：

回表與覆蓋索引

所謂回表查詢，就是先通過非聚簇索引定位到主鍵，再通過聚簇索引定位到數據行。

而索引覆蓋則是一種避免回表查詢的優(yōu)化策略，即：一個索引包含了所有需要查詢的字段值，查詢時直接返回索引的數據，而不需要回表查詢。

舉例：假設在學生表的年齡上建立了索引，那么當進行 select age from student_table where age < 20 的查詢時，在索引的葉子節(jié)點上，已經包含了 age 信息，不會再次進行回表查詢。

覆蓋索引的優(yōu)點：

覆蓋索引只需要讀取索引，極大減小了數據訪問量；

避免了回表查詢，提高了查詢效率。

四、索引失效的情況

五、聯合索引最左前綴匹配原則

最左前綴匹配：最左優(yōu)先，以最左邊為起點的任何連續(xù)的索引都可以匹配上。遇到范圍查詢(>、<、between、like)就會停止匹配。

假設有聯合索引（a,b,c）：

where 條件為（a,b,c）、（b,a,c）、（c,a,b）等，會走聯合索引；

where 條件為（a）、（a,b）、（a,b,c），會走聯合索引；

where 條件為（b）、（c）、（b,c），不會走索引，會全表掃描；

where 條件為（a,c）時，會走索引，但只使用 a 的索引。

為什么要遵循最左匹配原則

假設有如下 B+樹，聯合索引為（a,b)：

由圖可知，最左邊的 a 都是有序的，分別是 ： 1、1、2、2、3、3， 但是右邊的 b 不一定有序： 1、2、1、4、3、2。但是在 a 相同的情況下 b 是有序的， 如： a=1 時 b =1，2 ； a=2 時， b= 1，4； a=3 時 ，b=1，2。

因此，在篩選數據的時候， 若直接篩選 b ，整個就是無序的，需要做全表掃描，此時索引失效；若先篩選 a 再篩選 b ，就可以利用 B+樹的有序性來加快查找速度。綜上，在使用聯合索引時需要遵循最左匹配原則。

六、創(chuàng)建索引的原則

1. 遵循最左前綴匹配原則

為頻繁作為查詢條件的字段創(chuàng)建索引（如：訂單 id)

更新頻繁的字段不適合創(chuàng)建索引（如：訂單狀態(tài)）

不能有效區(qū)分數據或重復值比較多的字段不適合創(chuàng)建索引（如：性別）

盡量拓展索引，不要新建索引。

定義有外鍵的字段一定要創(chuàng)建索引。

（外鍵：一個表中存放的另一個表的主鍵。）

鎖

當多個用戶對數據庫并發(fā)操作時，會存在數據讀取不一致的問題，造成數據混亂。數據庫中鎖的作用就是保證數據的一致性，與線程同步含義相同。數據庫中的鎖分為兩大類：悲觀鎖和樂觀鎖。

悲觀鎖（Pessimistic Lock） ：適用于多寫的應用類型

總是假設最壞的情況，每次有事務去拿數據時都會覺得別人會修改，所以每次使用時都會給該數據上鎖，而其他事務就會阻塞，直到這個事務釋放鎖把數據轉讓給下一個用戶。

悲觀鎖按使用性質可劃分為以下幾類：

共享鎖（Share Lock）：也叫讀鎖（S 鎖），允許多個事務對同一數據共享一把鎖，都能訪問到數據，但 只能讀不能修改。

排他鎖（Exclusive Lock）：也叫寫鎖（X 鎖），一個事務獲取了某數據的排他鎖，其它事務就不能獲取其它鎖，只有獲取排他鎖的事務能對數據進行讀取和修改。（獨占式鎖）

更新鎖：簡稱 U 鎖，在數據修改操作的初始化階段鎖定可能要被修改的資源，從而避免共享鎖競爭排他鎖造成的死鎖現象。

悲觀鎖按作用范圍可劃分為：

行鎖：鎖的作用范圍是行級別。對于 UPDATE、INSERT、DELETE 語句，會自動加排他鎖。InnoDB 默認采用行鎖。 數據庫能夠確對哪些行進行操作的情況下使用行鎖（如使用主鍵時），如果不知道就使用表鎖（不使用主鍵時）。

行鎖的優(yōu)勢：鎖的粒度小，發(fā)生鎖沖突的概率低，并發(fā)處理的能力高。

行鎖的劣勢：開銷大，加鎖慢。

2、表鎖：鎖的作用范圍是整張表。表鎖的優(yōu)勢：開銷小，加鎖快。表鎖的劣勢：鎖的粒度大，發(fā)生鎖沖突的概率高，并發(fā)處理的能力低。

樂觀鎖（Optimistic Lock）：適用于多讀的應用類型

與悲觀鎖相反，總是假設最好的情況，每次有事務去拿數據的時候都認為別人不會修改，所以不會給該數據上鎖。但在更新的時候會判斷在此期間有沒有事務更新了該數據。樂觀鎖兩種常見的實現方式：

版本號機制：一般是在數據表中加上一個數據版本號 version 字段，表示數據被修改的次數，當數據被修改時，version 值會加一。當線程 A 要更新數據值時，在讀取數據的同時也會讀取 version 值，在提交更新時，若剛才讀取到的 version 值為當前數據庫中的 version 值相等時才更新，否則重試更新操作，直到更新成功。

CAS 算法（compare and swap）：一種無鎖算法，即在不使用鎖的情況下實現多線程之間的變量同步。CAS 采用自旋的模式，會浪費 CPU 資源。原理：通過原子操作來更新數據的值的，比較讀取的當前值 V 與當前線程先前取出的值 A 是否一樣，若一樣表示該值在此期間未被其它線程修改，則更新該值，否則重新從數據表讀取數據賦給 A，再進行 V 和 A 的比較，直到更新成功。

數據庫事務隔離級別

事務就是訪問數據庫進行的一組數據操作，所有操作必須成功，否則就會回滾所有操作導致失敗。

一、事務的四大特性（ACID）

原子性（Atomicity）：事務開始后的所有操作要么全部完成，要么全部不完成，不能只完成一部分。事務執(zhí)行過程中發(fā)生錯誤，會回滾已有操作并恢復到事務開始前的狀態(tài)。

一致性（Consistency）：事務開始前和結束后，數據庫的完整性沒有被破壞。比如：A 向 B 轉賬 1000 元，A 的賬戶中會減少 1000 元，而 B 的賬戶中會增加 1000 元。

隔離性（Isolation）：多個事務并發(fā)執(zhí)行時，同一時間只允許一個事務請求同一數據，不同的事務之前不會互相干擾。如：A 在從一張銀行卡取款的過程中，其他人不能向這張銀行卡轉賬。

持久性（Durability）：事務完成之后，事務對數據庫的所有更改應該保存在數據庫中，不能回滾。

二、事務并發(fā)的三大問題

臟讀：一個事務讀取到了另一個事務未提交的數據。比如：事務 A 讀取了事務 B 更新但尚未提交的數據，B 提交失敗發(fā)生回滾操作，那么 A 讀取的數據是臟數據。

不可重復讀：一個事務多次讀取同一數據，另一事務在其讀取過程中對該數據進行了修改（update 操作）并提交，導致這個事務前后讀取的數據結果不一致。

幻讀：一個事務多次讀取同一數據，另一事務在其讀取過程中對該數據進行了插入或刪除（insert 操作）并提交，導致這個事務前后讀取的數據結果不一致。

三、事務隔離級別及實現原理

事務隔離是通過加鎖來實現的，鎖的競爭會帶來性能的損失。事務隔離級別分為以下四種：

讀未提交（READ UNCOMMITTED）：不加鎖，性能最好，但是無法解決臟讀、不可重復讀、幻讀問題；實現：不加鎖，可看作無隔離。

讀提交（READ COMMITTED）：一個事務只能讀取其它事務已經提交的數據，但不能解決不可重復讀、幻讀問題；實現：事務每次操作數據時都會重新生成一次快照，來記錄當前數據的版本，在快照時間之前提交的數據版本則可以被讀到。（MVCC）

可重復讀（REPEATABLE READ）：一個事務在開始后直到提交前的任意時刻讀取的數據都是一樣的，不會讀到其它事務對已有數據的修改，但可以讀取其它事務插入的新數據，即無法解決幻讀問題。（mysql 中默認的隔離級別，MVCC）實現：事務開始時生成一個當前事務全局性的快照，后面每次讀取的數據都是該次快照的數據版本。

串行化（SERIALIZABLE）：隔離效果就好，可以解決臟讀、不可重復讀、幻讀問題，但需要加鎖，性能較差。實現：一個事務讀的時候會加共享鎖，其他事務可以并發(fā)讀，但不能寫；該事務寫的時候加排它鎖，其他事務不能寫也不能讀。

MVCC（多版本并發(fā)控制）實現機制

MVCC 是一種多版本并發(fā)控制機制，通過保存數據在某個時間點的快照來實現的。不同的存儲引擎的 MVCC 實現是不同的，典型的有樂觀（Optimistic）并發(fā)控制和悲觀（pessimistic）并發(fā)控制。

InnoDB 的 MVCC，是通過在每行記錄后面保存兩個隱藏的列來實現的。這兩個列，一個保存了行的創(chuàng)建時間（創(chuàng)建版本號），一個保存行的刪除時間（刪除版本號）。每開始一個新的事務，系統版本號都會自動遞增。事務開始時刻的系統版本號會作為事務版本號，用來和查詢到的每行記錄的版本號進行比較。保存這兩個額外的系統版本號，使大多數讀操作都可以不用加鎖。這樣設計使得讀數據操作簡單，性能好。不足之處就是每行記錄都需要額外的存儲空間，需要更多的行檢查和維護工作。

在 READ COMMITTED 隔離級別下，一個事務多次輸入 SELECT 查找語句時，InnoDB 每次查詢時都會生成一個快照，記錄當前已提交的數據版本，在這之前的數據版本都可以被讀取到。

在 REPEATABLE READ 隔離級別下，一個事務多次輸入 SELECT 查找語句時，InnoDB 只在事務開始時生成一個當前事務全局性的快照，每次查找都是讀取早于當前事務版本的數據行。

MySQL 數據庫性能優(yōu)化的方法

硬件優(yōu)化。提升服務器的硬件配置，如 CPU、內存大小等。

數據庫調優(yōu)，如增加索引。

引入緩存，減小數據庫壓力。

讀寫分離。增加從庫，抗住更多的讀請求。

分庫分表。單表數據超千萬時，考慮分庫分表。

MySQL 主從復制，讀寫分離

一、為什么要主從復制

高可用性：若主庫發(fā)生故障，可快速切換到其中一個從庫，從而保證系統業(yè)務的可用性。

負載均衡：主庫用于寫數據，各個從庫用于讀數據，實現讀寫分離，將流量分布到各個庫上，從而實現負載均衡。

可擴展性好：當業(yè)務量很大的時候，為了抗住更多的讀請求，可以增加從庫，從而分擔流量。

二、主從復制的原理

MySQL 主從復制是一個異步的復制過程，主庫發(fā)送更新事件到從庫，從庫讀取更新記錄，并執(zhí)行更新記錄，使得從庫的內容與主庫保持一致。

主從復制的流程為：

當主庫進行 insert、update、delete 操作時，會按順序寫入到 binlog（二進制日志）中；

從庫啟動 I/O 線程，跟主庫建立客戶端連接；

主庫啟動 binlog dump 線程，讀取主庫上 binlog 的內容發(fā)送給從庫的 I/O 線程；

從庫的 I/O 線程接收到 binlog 內容后，將內容寫入到本地的 relay log（中繼日志）；

從庫啟動 SQL 線程，讀取 relay log 的內容，并完成對從庫數據的更新。

上圖為一個從庫的流程，實際中，有 N 個從庫，主庫就會對應有 N 個 binlog dump 線程，而每個從庫都會有自己的 I/O 線程和 SQL 線程。

MySQL 分庫分表

一、為什么要分庫分表

MySQL 單表最多能存儲 5000w 數據，但是單表數據表達 1000w 以后，即使添加從庫、優(yōu)化索引，查詢的性能依舊很差。這時候就需要通過分庫分表，從而有效減小單臺數據庫的壓力。

二、數據表的兩種拆分方式

1、垂直拆分

數據表列的拆分，把一張列比較多的表拆分為多張表，如：把主鍵和常用的列放一張表，把主鍵和不常用的列放另一張表。如下圖所示：

垂直拆分還有一種理解，即從業(yè)務的角度進行拆分，如：一個數據中既存在用戶表，又存在訂單表，那么就可以把用戶表存在用戶庫，訂單表存在訂單庫中。如下圖所示：

優(yōu)點：使每條數據變小，一個數據塊 block 可以存儲更多數據，查詢時可減小 I/O 次數；

缺點：

主鍵出現冗余，需要管理冗余列，查詢所有數據時需要關聯查詢 JOIN 操作；

依舊會出現單表數據量過大的情況。

應用場景：數據表中某些列常用，而某些列不常用的情況。

2、水平拆分

數據表行的拆分，數據數量超過千萬級別時，數據表的查詢效率就會很慢，就可以把一張表的數據按行拆分成多個表來存放。如下圖所示：

優(yōu)點：不存在單表大數據造成的性能瓶頸；

缺點：邏輯復雜，通常查詢時需要多個表名；

應用場景：單表數據量達百萬級別甚至千萬級別。

上圖所示為庫內分表，僅僅單純的解決了單一表數據過大的問題，而沒有把表的數據分布到不同的機器上，因此對于減輕 MySQL 服務器的壓力來說，并沒有太大的作用，大家還是競爭同一個物理機上的 IO、CPU、網絡，這個就要通過分庫來解決，即分庫分表。

三、數據表水平拆分的兩種方案

水平分表最主要的就是路由算法，即把路由的 key 按照指定的算法進行路由存放。常用的水平分表方案有兩種：range 范圍路由、hash 路由。

1、range 范圍路由：按照數據范圍進行拆分數據

range 方案比較簡單，就是把一定范圍內的訂單，存放到一個表中；如上圖 id=12 放到 0 表中，id=1300 萬的放到 1 表中。設計這個方案時就是前期把表的范圍設計好。通過 id 進行路由存放。

優(yōu)點：數據擴容方便，不需要數據遷移；

缺點：有熱點問題，由于 id 的值一般遞增的，某段時間的數據會集中在某一張表中，就會導致該表壓力過大，而其它表沒有壓力。（熱點問題是指某段時間對數據的操作集中在一個表中，而其他表的操作很少。）

2、hash 路由：指定路由 key 對分表總數進行取模

在設計系統之前，假設未來幾年的訂單量為 4000 萬。每張表我們可以容納 1000 萬，也我們可以設計 4 張表進行存儲。

hash 路由的具體方法為：對指定的路由 key（如：id）對分表總數進行取模，上圖中，id=12 的訂單，對 4 進行取模，也就是會得到 0，那此訂單會放到 0 表中。id=13 的訂單，取模得到為 1，就會放到 1 表中。為什么對 4 取模，是因為分表總數是 4。

優(yōu)點：數據可以均勻的放到每張表中，對數據進行操作時，就不會有熱點問題。

缺點：若數據量繼續(xù)增大，需要增加分表數，數據的遷移和擴容，很會麻煩。

四、分庫分表方案

上述的兩種水平分表方案中，hash 可以解決數據均勻問題，range 可以解決數據遷移問題，因此可以將兩者結合在一起，實現分庫分表的方案。

實現思路為：先用 range 路由方案讓數據落地到一個范圍內，這樣需要擴容時以前的數據不需要遷移；再在這個范圍內，使用 hash 路由方案讓數據均勻分配在幾個表中，這樣就解決了數據熱點問題，保證每個表壓力一樣；最后把這些表分配到幾臺數據庫機器上，實現分庫。以上就實現了分庫分表。

具體實現，我們一起看一個例子：

假設數據量為 4000 萬，定義一個 Group01 組，組內有三個 DB 庫，DB_0 中有 4 張表，DB_1 中有 3 張表，DB_2 中有 3 張表。每張表內存儲的路由 key（id）的范圍如上圖所示。這里假設 DB_0 的服務器性能更好，所以存儲 4 張表，從而可以存儲更多的數據。

存儲路由 key 的具體流程為：

擴容的時候，只需要新增加一個 group02 組，而不需要遷移之前的數據。

實際設計的時候，我們只需要維護 group、db、table 的對應關系，就可以將數據存儲在對應的表中。如下圖所示，圖中 table 表字段有些小錯誤，僅做示例。

實際開發(fā)的時候，這三張表可以保存在緩存，而不是 MySQL 中。