?數(shù)據(jù)存儲引擎

存儲引擎是 MYSQL 的核心技術(shù)，不同的存儲引擎使用不同的存儲機制姿搜、索引技巧笤昨、鎖定水平并最終提供不同的功能和能力。常見的引擎分為三種：InnoDB 存儲引擎（MYSQL 默認的事務(wù)性引擎）停士、MyISAM 存儲引擎挖帘、Memory 存儲引擎完丽。三種存儲引擎的功能對比如下表所示：

總結(jié)三種引擎的使用選擇如下：

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

MyISAM：適合于只讀的數(shù)據(jù)，或者表比較小骄崩、可以忍受修復(fù)操作數(shù)據(jù)庫聘鳞；

Memory：適用于快速查找數(shù)據(jù)，用于數(shù)據(jù)分析中產(chǎn)生的中間數(shù)據(jù)要拂。

數(shù)據(jù)庫三大范式? ?java初學(xué)者組團學(xué)習(xí):737251827

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

第二范式：在第一范式的基礎(chǔ)上脱惰，非主鍵列完全依賴于主鍵搏嗡，而不能是依賴于主鍵的一部分。如：設(shè)計訂單信息表時枪芒，商品名稱彻况、商品價格等商品信息與表的主鍵不相關(guān)，而只與商品編號相關(guān)舅踪，因此可將表設(shè)計為訂單信息表和商品信息表纽甘。

第三范式：在第二范式的基礎(chǔ)上，非主鍵列只依賴于主鍵抽碌，不依賴于其他非主鍵悍赢。如果一張表中出現(xiàn)另一張表的非主鍵，可以將這兩張表用外鍵關(guān)聯(lián)货徙，而不是將另一張表的非主鍵直接寫在當(dāng)前表中左权。設(shè)計數(shù)據(jù)庫表的時候，要盡量遵守三范式痴颊，如果不遵守赏迟，必須有足夠的理由。

數(shù)據(jù)庫索引類型及原理

索引是一種對數(shù)據(jù)庫表中一列或多列的值進行排序的一種數(shù)據(jù)結(jié)構(gòu)蠢棱，指向表中特定的數(shù)據(jù)內(nèi)容锌杀，從而提高查詢效率。

一泻仙、為什么要用索引

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

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

二捺癞、數(shù)據(jù)庫索引的類型及原理

1夷蚊、B+樹索引：適合范圍查詢、順序查詢髓介，不適合插入惕鼓、刪除數(shù)據(jù)，是 InnoDB唐础、MyISAM 的索引方式箱歧。

B 樹與 B+樹都是用于大量數(shù)據(jù)查詢的一種數(shù)據(jù)結(jié)構(gòu)，二者有以下特點：

B 樹：

由二叉搜索轉(zhuǎn)變?yōu)?N 叉搜索一膨，樹的高度大大降低呀邢，查詢次數(shù)少；

葉子節(jié)點豹绪、非葉子節(jié)點都可以存儲多個數(shù)據(jù)价淌，每次可以讀取一頁數(shù)據(jù)，IO 次數(shù)更少瞒津；

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

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

B+樹的特點為：

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

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

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

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

2真屯、哈希索引：適合單一數(shù)據(jù)的查找脸候、刪除、插入绑蔫，不適合范圍查找运沦，是 Memory 的索引方式。

哈希表的查詢配深、刪除携添、插入的平均時間復(fù)雜度都是 O(1)，適合每次只查詢一條信息凉馆。但是對于需要排序查詢（對查詢的數(shù)據(jù)進行排序輸出）薪寓、范圍查詢（如：大于或小于某值的范圍查詢），采用哈希索引的時間復(fù)雜度會從 O(1)退化為 O(n)澜共，相當(dāng)于全表查詢向叉，效率極低∴露總結(jié)哈希索引的特點如下：

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

訪問數(shù)據(jù)的速度非尘└铮快奇唤，但當(dāng)哈希沖突較多時，查詢效率會大大降低匹摇；

哈希索引數(shù)據(jù)不是按順序存儲的咬扇，即無序的，無法用于排序查詢廊勃。因此懈贺，哈希索引只適用于特定的場合经窖，不要輕易使用。

三梭灿、索引分類

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

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

優(yōu)點：

索引和數(shù)據(jù)保存在同一顆 B+樹中伏尼，數(shù)據(jù)訪問更快佑钾；

聚簇索引對于主鍵的范圍查找速度非常快烦粒。

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

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

假設(shè)一張表中有 id（主鍵）芭碍，order_id（唯一鍵）兩個字段徒役。若使用“where id=14"的條件進行查詢，就會走聚簇索引窖壕，直接可以查詢出對應(yīng)行數(shù)據(jù)忧勿；若使用“where order_id= 1100202"的條件進行查詢，就會走非聚簇索引瞻讽，會先根據(jù) order_id 查詢到所在行的主鍵 id鸳吸，再根據(jù)主鍵 id 走聚簇索引就查到行數(shù)據(jù)。如下圖所示：

回表與覆蓋索引

所謂回表查詢速勇，就是先通過非聚簇索引定位到主鍵晌砾，再通過聚簇索引定位到數(shù)據(jù)行。

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

舉例：假設(shè)在學(xué)生表的年齡上建立了索引呕乎，那么當(dāng)進行 select age from student_table where age < 20 的查詢時，在索引的葉子節(jié)點上陨晶，已經(jīng)包含了 age 信息猬仁，不會再次進行回表查詢。

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

覆蓋索引只需要讀取索引，極大減小了數(shù)據(jù)訪問量逐虚；

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

四叭爱、索引失效的情況

五撮躁、聯(lián)合索引最左前綴匹配原則

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

假設(shè)有聯(lián)合索引（a,b,c）：

where 條件為（a,b,c）、（b,a,c）晃危、（c,a,b）等叙赚，會走聯(lián)合索引；

where 條件為（a）僚饭、（a,b）震叮、（a,b,c），會走聯(lián)合索引鳍鸵；

where 條件為（b）苇瓣、（c）、（b,c）偿乖，不會走索引击罪，會全表掃描；

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

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

假設(shè)有如下 B+樹画切，聯(lián)合索引為（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假褪。

因此，在篩選數(shù)據(jù)的時候近顷， 若直接篩選 b 生音，整個就是無序的，需要做全表掃描窒升，此時索引失效久锥；若先篩選 a 再篩選 b ，就可以利用 B+樹的有序性來加快查找速度异剥。綜上，在使用聯(lián)合索引時需要遵循最左匹配原則絮重。

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

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

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

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

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

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

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

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

鎖

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

悲觀鎖（Pessimistic Lock） ：適用于多寫的應(yīng)用類型

總是假設(shè)最壞的情況立帖，每次有事務(wù)去拿數(shù)據(jù)時都會覺得別人會修改眼溶，所以每次使用時都會給該數(shù)據(jù)上鎖，而其他事務(wù)就會阻塞晓勇，直到這個事務(wù)釋放鎖把數(shù)據(jù)轉(zhuǎn)讓給下一個用戶堂飞。

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

共享鎖（Share Lock）：也叫讀鎖（S 鎖）灌旧，允許多個事務(wù)對同一數(shù)據(jù)共享一把鎖，都能訪問到數(shù)據(jù)绰筛，但 只能讀不能修改枢泰。

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

更新鎖：簡稱 U 鎖薄榛，在數(shù)據(jù)修改操作的初始化階段鎖定可能要被修改的資源讳窟，從而避免共享鎖競爭排他鎖造成的死鎖現(xiàn)象。

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

行鎖：鎖的作用范圍是行級別敞恋。對于 UPDATE丽啡、INSERT、DELETE 語句硬猫，會自動加排他鎖补箍。InnoDB 默認采用行鎖。 數(shù)據(jù)庫能夠確對哪些行進行操作的情況下使用行鎖（如使用主鍵時）啸蜜，如果不知道就使用表鎖（不使用主鍵時）坑雅。

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

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

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

樂觀鎖（Optimistic Lock）：適用于多讀的應(yīng)用類型

與悲觀鎖相反，總是假設(shè)最好的情況子眶，每次有事務(wù)去拿數(shù)據(jù)的時候都認為別人不會修改瀑凝，所以不會給該數(shù)據(jù)上鎖。但在更新的時候會判斷在此期間有沒有事務(wù)更新了該數(shù)據(jù)臭杰。樂觀鎖兩種常見的實現(xiàn)方式：

版本號機制：一般是在數(shù)據(jù)表中加上一個數(shù)據(jù)版本號 version 字段猜丹，表示數(shù)據(jù)被修改的次數(shù)，當(dāng)數(shù)據(jù)被修改時硅卢，version 值會加一射窒。當(dāng)線程 A 要更新數(shù)據(jù)值時藏杖，在讀取數(shù)據(jù)的同時也會讀取 version 值，在提交更新時脉顿，若剛才讀取到的 version 值為當(dāng)前數(shù)據(jù)庫中的 version 值相等時才更新蝌麸，否則重試更新操作，直到更新成功艾疟。

CAS 算法（compare and swap）：一種無鎖算法来吩，即在不使用鎖的情況下實現(xiàn)多線程之間的變量同步。CAS 采用自旋的模式蔽莱，會浪費 CPU 資源弟疆。原理：通過原子操作來更新數(shù)據(jù)的值的，比較讀取的當(dāng)前值 V 與當(dāng)前線程先前取出的值 A 是否一樣盗冷，若一樣表示該值在此期間未被其它線程修改怠苔，則更新該值，否則重新從數(shù)據(jù)表讀取數(shù)據(jù)賦給 A仪糖，再進行 V 和 A 的比較柑司，直到更新成功。

數(shù)據(jù)庫事務(wù)隔離級別

事務(wù)就是訪問數(shù)據(jù)庫進行的一組數(shù)據(jù)操作锅劝，所有操作必須成功攒驰，否則就會回滾所有操作導(dǎo)致失敗。

一故爵、事務(wù)的四大特性（ACID）

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

一致性（Consistency）：事務(wù)開始前和結(jié)束后剥纷，數(shù)據(jù)庫的完整性沒有被破壞。比如：A 向 B 轉(zhuǎn)賬 1000 元呢铆，A 的賬戶中會減少 1000 元晦鞋，而 B 的賬戶中會增加 1000 元。

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

持久性（Durability）：事務(wù)完成之后，事務(wù)對數(shù)據(jù)庫的所有更改應(yīng)該保存在數(shù)據(jù)庫中纱皆，不能回滾湾趾。

二芭商、事務(wù)并發(fā)的三大問題

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

不可重復(fù)讀：一個事務(wù)多次讀取同一數(shù)據(jù)艺普，另一事務(wù)在其讀取過程中對該數(shù)據(jù)進行了修改（update 操作）并提交簸州，導(dǎo)致這個事務(wù)前后讀取的數(shù)據(jù)結(jié)果不一致。

幻讀：一個事務(wù)多次讀取同一數(shù)據(jù)歧譬，另一事務(wù)在其讀取過程中對該數(shù)據(jù)進行了插入或刪除（insert 操作）并提交岸浑，導(dǎo)致這個事務(wù)前后讀取的數(shù)據(jù)結(jié)果不一致。

三瑰步、事務(wù)隔離級別及實現(xiàn)原理

事務(wù)隔離是通過加鎖來實現(xiàn)的矢洲，鎖的競爭會帶來性能的損失。事務(wù)隔離級別分為以下四種：

讀未提交（READ UNCOMMITTED）：不加鎖面氓，性能最好玉工，但是無法解決臟讀、不可重復(fù)讀邪意、幻讀問題忘蟹；實現(xiàn)：不加鎖，可看作無隔離呻拌。

讀提交（READ COMMITTED）：一個事務(wù)只能讀取其它事務(wù)已經(jīng)提交的數(shù)據(jù)葱轩，但不能解決不可重復(fù)讀、幻讀問題藐握；實現(xiàn)：事務(wù)每次操作數(shù)據(jù)時都會重新生成一次快照靴拱，來記錄當(dāng)前數(shù)據(jù)的版本，在快照時間之前提交的數(shù)據(jù)版本則可以被讀到猾普。（MVCC）

可重復(fù)讀（REPEATABLE READ）：一個事務(wù)在開始后直到提交前的任意時刻讀取的數(shù)據(jù)都是一樣的袜炕，不會讀到其它事務(wù)對已有數(shù)據(jù)的修改，但可以讀取其它事務(wù)插入的新數(shù)據(jù)初家，即無法解決幻讀問題偎窘。（mysql 中默認的隔離級別，MVCC）實現(xiàn)：事務(wù)開始時生成一個當(dāng)前事務(wù)全局性的快照溜在，后面每次讀取的數(shù)據(jù)都是該次快照的數(shù)據(jù)版本陌知。

串行化（SERIALIZABLE）：隔離效果就好，可以解決臟讀掖肋、不可重復(fù)讀仆葡、幻讀問題，但需要加鎖志笼，性能較差沿盅。實現(xiàn)：一個事務(wù)讀的時候會加共享鎖把篓，其他事務(wù)可以并發(fā)讀，但不能寫嗡呼；該事務(wù)寫的時候加排它鎖纸俭，其他事務(wù)不能寫也不能讀。

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

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

InnoDB 的 MVCC窒悔，是通過在每行記錄后面保存兩個隱藏的列來實現(xiàn)的。這兩個列敌买，一個保存了行的創(chuàng)建時間（創(chuàng)建版本號）简珠，一個保存行的刪除時間（刪除版本號）。每開始一個新的事務(wù)虹钮，系統(tǒng)版本號都會自動遞增聋庵。事務(wù)開始時刻的系統(tǒng)版本號會作為事務(wù)版本號，用來和查詢到的每行記錄的版本號進行比較芙粱。保存這兩個額外的系統(tǒng)版本號祭玉，使大多數(shù)讀操作都可以不用加鎖。這樣設(shè)計使得讀數(shù)據(jù)操作簡單春畔，性能好脱货。不足之處就是每行記錄都需要額外的存儲空間，需要更多的行檢查和維護工作律姨。

在 READ COMMITTED 隔離級別下振峻，一個事務(wù)多次輸入 SELECT 查找語句時，InnoDB 每次查詢時都會生成一個快照择份，記錄當(dāng)前已提交的數(shù)據(jù)版本扣孟，在這之前的數(shù)據(jù)版本都可以被讀取到。

在 REPEATABLE READ 隔離級別下荣赶，一個事務(wù)多次輸入 SELECT 查找語句時凤价，InnoDB 只在事務(wù)開始時生成一個當(dāng)前事務(wù)全局性的快照，每次查找都是讀取早于當(dāng)前事務(wù)版本的數(shù)據(jù)行讯壶。

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

硬件優(yōu)化料仗。提升服務(wù)器的硬件配置湾盗，如 CPU伏蚊、內(nèi)存大小等。

數(shù)據(jù)庫調(diào)優(yōu)格粪，如增加索引躏吊。

引入緩存氛改，減小數(shù)據(jù)庫壓力。

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

分庫分表赁项。單表數(shù)據(jù)超千萬時葛躏，考慮分庫分表。

MySQL 主從復(fù)制悠菜，讀寫分離

一舰攒、為什么要主從復(fù)制

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

負載均衡：主庫用于寫數(shù)據(jù)，各個從庫用于讀數(shù)據(jù)芬骄，實現(xiàn)讀寫分離猾愿，將流量分布到各個庫上，從而實現(xiàn)負載均衡账阻。

可擴展性好：當(dāng)業(yè)務(wù)量很大的時候蒂秘，為了抗住更多的讀請求，可以增加從庫宰僧，從而分擔(dān)流量材彪。

二、主從復(fù)制的原理

MySQL 主從復(fù)制是一個異步的復(fù)制過程琴儿，主庫發(fā)送更新事件到從庫段化，從庫讀取更新記錄，并執(zhí)行更新記錄造成，使得從庫的內(nèi)容與主庫保持一致显熏。

主從復(fù)制的流程為：

當(dāng)主庫進行 insert、update晒屎、delete 操作時喘蟆，會按順序?qū)懭氲?binlog（二進制日志）中；

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

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

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

從庫啟動 SQL 線程，讀取 relay log 的內(nèi)容棘脐，并完成對從庫數(shù)據(jù)的更新斜筐。

上圖為一個從庫的流程，實際中蛀缝，有 N 個從庫顷链，主庫就會對應(yīng)有 N 個 binlog dump 線程，而每個從庫都會有自己的 I/O 線程和 SQL 線程屈梁。

MySQL 分庫分表

一嗤练、為什么要分庫分表

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

二遮婶、數(shù)據(jù)表的兩種拆分方式

1蝗碎、垂直拆分

數(shù)據(jù)表列的拆分，把一張列比較多的表拆分為多張表旗扑，如：把主鍵和常用的列放一張表蹦骑，把主鍵和不常用的列放另一張表。如下圖所示：

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

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

缺點：

主鍵出現(xiàn)冗余嫌蚤，需要管理冗余列辐益，查詢所有數(shù)據(jù)時需要關(guān)聯(lián)查詢 JOIN 操作；

依舊會出現(xiàn)單表數(shù)據(jù)量過大的情況脱吱。

應(yīng)用場景：數(shù)據(jù)表中某些列常用智政，而某些列不常用的情況。

2箱蝠、水平拆分

數(shù)據(jù)表行的拆分续捂，數(shù)據(jù)數(shù)量超過千萬級別時猜年，數(shù)據(jù)表的查詢效率就會很慢，就可以把一張表的數(shù)據(jù)按行拆分成多個表來存放疾忍。如下圖所示：

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

缺點：邏輯復(fù)雜床三，通常查詢時需要多個表名一罩；

應(yīng)用場景：單表數(shù)據(jù)量達百萬級別甚至千萬級別。

上圖所示為庫內(nèi)分表撇簿，僅僅單純的解決了單一表數(shù)據(jù)過大的問題聂渊，而沒有把表的數(shù)據(jù)分布到不同的機器上，因此對于減輕 MySQL 服務(wù)器的壓力來說四瘫，并沒有太大的作用汉嗽，大家還是競爭同一個物理機上的 IO、CPU找蜜、網(wǎng)絡(luò)饼暑，這個就要通過分庫來解決，即分庫分表洗做。

三弓叛、數(shù)據(jù)表水平拆分的兩種方案

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

1畦徘、range 范圍路由：按照數(shù)據(jù)范圍進行拆分數(shù)據(jù)

range 方案比較簡單毕籽，就是把一定范圍內(nèi)的訂單，存放到一個表中井辆；如上圖 id=12 放到 0 表中关筒，id=1300 萬的放到 1 表中。設(shè)計這個方案時就是前期把表的范圍設(shè)計好杯缺。通過 id 進行路由存放平委。

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

缺點：有熱點問題廉赔，由于 id 的值一般遞增的，某段時間的數(shù)據(jù)會集中在某一張表中匾鸥，就會導(dǎo)致該表壓力過大蜡塌，而其它表沒有壓力。（熱點問題是指某段時間對數(shù)據(jù)的操作集中在一個表中勿负，而其他表的操作很少馏艾。）

2劳曹、hash 路由：指定路由 key 對分表總數(shù)進行取模

在設(shè)計系統(tǒng)之前，假設(shè)未來幾年的訂單量為 4000 萬琅摩。每張表我們可以容納 1000 萬铁孵，也我們可以設(shè)計 4 張表進行存儲。

hash 路由的具體方法為：對指定的路由 key（如：id）對分表總數(shù)進行取模房资，上圖中蜕劝，id=12 的訂單，對 4 進行取模轰异，也就是會得到 0岖沛，那此訂單會放到 0 表中。id=13 的訂單搭独，取模得到為 1婴削，就會放到 1 表中。為什么對 4 取模牙肝，是因為分表總數(shù)是 4唉俗。

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

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

四乓序、分庫分表方案

上述的兩種水平分表方案中寺酪，hash 可以解決數(shù)據(jù)均勻問題，range 可以解決數(shù)據(jù)遷移問題替劈，因此可以將兩者結(jié)合在一起寄雀，實現(xiàn)分庫分表的方案。

實現(xiàn)思路為：先用 range 路由方案讓數(shù)據(jù)落地到一個范圍內(nèi)陨献，這樣需要擴容時以前的數(shù)據(jù)不需要遷移盒犹；再在這個范圍內(nèi)，使用 hash 路由方案讓數(shù)據(jù)均勻分配在幾個表中眨业，這樣就解決了數(shù)據(jù)熱點問題急膀，保證每個表壓力一樣；最后把這些表分配到幾臺數(shù)據(jù)庫機器上龄捡，實現(xiàn)分庫卓嫂。以上就實現(xiàn)了分庫分表。

具體實現(xiàn)聘殖，我們一起看一個例子：

假設(shè)數(shù)據(jù)量為 4000 萬晨雳，定義一個 Group01 組行瑞，組內(nèi)有三個 DB 庫，DB_0 中有 4 張表餐禁，DB_1 中有 3 張表血久，DB_2 中有 3 張表。每張表內(nèi)存儲的路由 key（id）的范圍如上圖所示帮非。這里假設(shè) DB_0 的服務(wù)器性能更好氧吐，所以存儲 4 張表，從而可以存儲更多的數(shù)據(jù)喜鼓。

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

擴容的時候，只需要新增加一個 group02 組衔肢，而不需要遷移之前的數(shù)據(jù)庄岖。

實際設(shè)計的時候，我們只需要維護 group角骤、db隅忿、table 的對應(yīng)關(guān)系，就可以將數(shù)據(jù)存儲在對應(yīng)的表中邦尊。如下圖所示背桐，圖中 table 表字段有些小錯誤，僅做示例蝉揍。

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