數(shù)據(jù)庫優(yōu)化

1. 為什么要優(yōu)化

（1）系統(tǒng)的吞吐量瓶頸往往出現(xiàn)在數(shù)據(jù)庫的訪問速度上

（2）隨著應(yīng)用程序的運(yùn)行五垮，數(shù)據(jù)庫的中的數(shù)據(jù)會越來越多睬棚，處理時間會相應(yīng)變慢

（3）數(shù)據(jù)是存放在磁盤上的第煮，讀寫速度無法和內(nèi)存相比

（4）優(yōu)化原則：減少系統(tǒng)瓶頸，減少資源占用抑党，增加系統(tǒng)的反應(yīng)速度包警。

2. 數(shù)據(jù)庫結(jié)構(gòu)優(yōu)化

一個好的數(shù)據(jù)庫設(shè)計方案對于數(shù)據(jù)庫的性能往往會起到事半功倍的效果。

需要考慮數(shù)據(jù)冗余底靠、查詢和更新的速度害晦、字段的數(shù)據(jù)類型是否合理等多方面的內(nèi)容。

將字段很多的表分解成多個表

對于字段較多的表暑中，如果有些字段的使用頻率很低壹瘟，可以將這些字段分離出來形成新表。

因?yàn)楫?dāng)一個表的數(shù)據(jù)量很大時鳄逾，會由于使用頻率低的字段的存在而變慢稻轨。

a.增加中間表

對于需要經(jīng)常聯(lián)合查詢的表，可以建立中間表以提高查詢效率雕凹。

通過建立中間表殴俱，將需要通過聯(lián)合查詢的數(shù)據(jù)插入到中間表中政冻，然后將原來的聯(lián)合查詢改為對中間表的查詢。

b.增加冗余字段

設(shè)計數(shù)據(jù)表時應(yīng)盡量遵循范式理論的規(guī)約粱挡，盡可能的減少冗余字段赠幕，讓數(shù)據(jù)庫設(shè)計看起來精致、優(yōu)雅询筏。但是榕堰，合理的加入冗余字段可以提高查詢速度。

表的規(guī)范化程度越高嫌套，表和表之間的關(guān)系越多逆屡，需要連接查詢的情況也就越多，性能也就越差踱讨。

c.注意：

冗余字段的值在一個表中修改了魏蔗，就要想辦法在其他表中更新，否則就會導(dǎo)致數(shù)據(jù)不一致的問題痹筛。

3. MySQL數(shù)據(jù)庫cpu飆升到500%的話他怎么處理莺治？

當(dāng) cpu 飆升到 500%時，先用操作系統(tǒng)命令 top 命令觀察是不是 mysqld 占用導(dǎo)致的帚稠，如果不是谣旁，找出占用高的進(jìn)程，并進(jìn)行相關(guān)處理滋早。

如果是 mysqld 造成的榄审， show processlist，看看里面跑的 session 情況杆麸，是不是有消耗資源的 sql 在運(yùn)行搁进。找出消耗高的 sql，看看執(zhí)行計劃是否準(zhǔn)確昔头， index 是否缺失饼问，或者實(shí)在是數(shù)據(jù)量太大造成。

一般來說揭斧，肯定要 kill 掉這些線程(同時觀察 cpu 使用率是否下降)匆瓜，等進(jìn)行相應(yīng)的調(diào)整(比如說加索引、改 sql未蝌、改內(nèi)存參數(shù))之后，再重新跑這些 SQL茧妒。

也有可能是每個 sql 消耗資源并不多萧吠，但是突然之間，有大量的 session 連進(jìn)來導(dǎo)致 cpu 飆升桐筏，這種情況就需要跟應(yīng)用一起來分析為何連接數(shù)會激增纸型，再做出相應(yīng)的調(diào)整，比如說限制連接數(shù)等

4. 大表怎么優(yōu)化？某個表有近千萬數(shù)據(jù)狰腌，CRUD比較慢除破，如何優(yōu)化？分庫分表了是怎么做的琼腔？分表分庫了有什么問題瑰枫？有用到中間件么？他們的原理知道么丹莲？

當(dāng)MySQL單表記錄數(shù)過大時光坝，數(shù)據(jù)庫的CRUD性能會明顯下降，一些常見的優(yōu)化措施如下：

限定數(shù)據(jù)的范圍：務(wù)必禁止不帶任何限制數(shù)據(jù)范圍條件的查詢語句甥材。比如：我們當(dāng)用戶在查詢訂單歷史的時候盯另，我們可以控制在一個月的范圍內(nèi)。洲赵；

讀/寫分離：經(jīng)典的數(shù)據(jù)庫拆分方案鸳惯，主庫負(fù)責(zé)寫，從庫負(fù)責(zé)讀叠萍；

緩存：使用MySQL的緩存芝发，另外對重量級、更新少的數(shù)據(jù)可以考慮使用應(yīng)用級別的緩存俭令；

還有就是通過分庫分表的方式進(jìn)行優(yōu)化后德，主要有垂直分表和水平分表

a.垂直分區(qū)：

根據(jù)數(shù)據(jù)庫里面數(shù)據(jù)表的相關(guān)性進(jìn)行拆分。例如抄腔，用戶表中既有用戶的登錄信息又有用戶的基本信息瓢湃，可以將用戶表拆分成兩個單獨(dú)的表，甚至放到單獨(dú)的庫做分庫赫蛇。

簡單來說垂直拆分是指數(shù)據(jù)表列的拆分绵患，把一張列比較多的表拆分為多張表。如下圖所示悟耘，這樣來說大家應(yīng)該就更容易理解了落蝙。

垂直拆分的優(yōu)點(diǎn)：可以使得行數(shù)據(jù)變小，在查詢時減少讀取的Block數(shù)暂幼，減少I/O次數(shù)筏勒。此外，垂直分區(qū)可以簡化表的結(jié)構(gòu)旺嬉，易于維護(hù)管行。

垂直拆分的缺點(diǎn)：主鍵會出現(xiàn)冗余，需要管理冗余列邪媳，并會引起Join操作捐顷，可以通過在應(yīng)用層進(jìn)行Join來解決荡陷。此外，垂直分區(qū)會讓事務(wù)變得更加復(fù)雜迅涮；

b.垂直分表

把主鍵和一些列放在一個表废赞，然后把主鍵和另外的列放在另一個表中

c.適用場景

缺點(diǎn)

有些分表的策略基于應(yīng)用層的邏輯算法，一旦邏輯算法改變叮姑，整個分表邏輯都會改變唉地，擴(kuò)展性較差

對于應(yīng)用層來說，邏輯算法增加開發(fā)成本

管理冗余列戏溺，查詢所有數(shù)據(jù)需要join操作

1渣蜗、如果一個表中某些列常用，另外一些列不常用

2旷祸、可以使數(shù)據(jù)行變小耕拷，一個數(shù)據(jù)頁能存儲更多數(shù)據(jù)，查詢時減少I/O次數(shù)

d.水平分區(qū)：

保持?jǐn)?shù)據(jù)表結(jié)構(gòu)不變托享，通過某種策略存儲數(shù)據(jù)分片骚烧。這樣每一片數(shù)據(jù)分散到不同的表或者庫中，達(dá)到了分布式的目的闰围。水平拆分可以支撐非常大的數(shù)據(jù)量赃绊。

水平拆分是指數(shù)據(jù)表行的拆分，表的行數(shù)超過200萬行時羡榴，就會變慢碧查，這時可以把一張的表的數(shù)據(jù)拆成多張表來存放。舉個例子：我們可以將用戶信息表拆分成多個用戶信息表校仑，這樣就可以避免單一表數(shù)據(jù)量過大對性能造成影響忠售。

水品拆分可以支持非常大的數(shù)據(jù)量。需要注意的一點(diǎn)是:分表僅僅是解決了單一表數(shù)據(jù)過大的問題迄沫，但由于表的數(shù)據(jù)還是在同一臺機(jī)器上稻扬，其實(shí)對于提升MySQL并發(fā)能力沒有什么意義，所以 水平拆分最好分庫 羊瘩。

水平拆分能夠 支持非常大的數(shù)據(jù)量存儲泰佳，應(yīng)用端改造也少，但 分片事務(wù)難以解決 尘吗，跨界點(diǎn)Join性能較差逝她，邏輯復(fù)雜。

《Java工程師修煉之道》的作者推薦 盡量不要對數(shù)據(jù)進(jìn)行分片睬捶，因?yàn)椴鸱謺磉壿嬊稹⒉渴稹⑦\(yùn)維的各種復(fù)雜度 侧戴，一般的數(shù)據(jù)表在優(yōu)化得當(dāng)?shù)那闆r下支撐千萬以下的數(shù)據(jù)量是沒有太大問題的宁昭。如果實(shí)在要分片，盡量選擇客戶端分片架構(gòu)酗宋，這樣可以減少一次和中間件的網(wǎng)絡(luò)I/O积仗。

水平分表：

表很大，分割后可以降低在查詢時需要讀的數(shù)據(jù)和索引的頁數(shù)蜕猫，同時也降低了索引的層數(shù)寂曹，提高查詢次數(shù)

e.適用場景

水平切分的缺點(diǎn)

下面補(bǔ)充一下數(shù)據(jù)庫分片的兩種常見方案：

客戶端代理：分片邏輯在應(yīng)用端，封裝在jar包中回右，通過修改或者封裝JDBC層來實(shí)現(xiàn)隆圆。當(dāng)當(dāng)網(wǎng)的 Sharding-JDBC 、阿里的TDDL是兩種比較常用的實(shí)現(xiàn)翔烁。

中間件代理：在應(yīng)用和數(shù)據(jù)中間加了一個代理層渺氧。分片邏輯統(tǒng)一維護(hù)在中間件服務(wù)中。我們現(xiàn)在談的 Mycat 蹬屹、360的Atlas侣背、網(wǎng)易的DDB等等都是這種架構(gòu)的實(shí)現(xiàn)。

1慨默、給應(yīng)用增加復(fù)雜度贩耐，通常查詢時需要多個表名，查詢所有數(shù)據(jù)都需UNION操作

2厦取、在許多數(shù)據(jù)庫應(yīng)用中潮太，這種復(fù)雜度會超過它帶來的優(yōu)點(diǎn)，查詢時會增加讀一個索引層的磁盤次數(shù)

3虾攻、表中的數(shù)據(jù)本身就有獨(dú)立性铡买，例如表中分表記錄各個地區(qū)的數(shù)據(jù)或者不同時期的數(shù)據(jù)，特別是有些數(shù)據(jù)常用台谢，有些不常用寻狂。

4、需要把數(shù)據(jù)存放在多個介質(zhì)上朋沮。

分庫分表后面臨的問題

事務(wù)支持 分庫分表后蛇券，就成了分布式事務(wù)了。如果依賴數(shù)據(jù)庫本身的分布式事務(wù)管理功能去執(zhí)行事務(wù)樊拓，將付出高昂的性能代價纠亚；如果由應(yīng)用程序去協(xié)助控制，形成程序邏輯上的事務(wù)筋夏，又會造成編程方面的負(fù)擔(dān)肯适。

f.跨庫join

只要是進(jìn)行切分莲绰，跨節(jié)點(diǎn)Join的問題是不可避免的。但是良好的設(shè)計和切分卻可以減少此類情況的發(fā)生硫椰。解決這一問題的普遍做法是分兩次查詢實(shí)現(xiàn)。在第一次查詢的結(jié)果集中找出關(guān)聯(lián)數(shù)據(jù)的id,根據(jù)這些id發(fā)起第二次請求得到關(guān)聯(lián)數(shù)據(jù)妻率。分庫分表方案產(chǎn)品

跨節(jié)點(diǎn)的count,order by,group by以及聚合函數(shù)問題 這些是一類問題，因?yàn)樗鼈兌夹枰谌繑?shù)據(jù)集合進(jìn)行計算。多數(shù)的代理都不會自動處理合并工作指蚜。解決方案：與解決跨節(jié)點(diǎn)join問題的類似，分別在各個節(jié)點(diǎn)上得到結(jié)果后在應(yīng)用程序端進(jìn)行合并涨椒。和join不同的是每個結(jié)點(diǎn)的查詢可以并行執(zhí)行摊鸡，因此很多時候它的速度要比單一大表快很多。但如果結(jié)果集很大蚕冬，對應(yīng)用程序內(nèi)存的消耗是一個問題免猾。

數(shù)據(jù)遷移，容量規(guī)劃囤热，擴(kuò)容等問題 來自淘寶綜合業(yè)務(wù)平臺團(tuán)隊(duì)猎提，它利用對2的倍數(shù)取余具有向前兼容的特性（如對4取余得1的數(shù)對2取余也是1）來分配數(shù)據(jù)，避免了行級別的數(shù)據(jù)遷移赢乓，但是依然需要進(jìn)行表級別的遷移忧侧，同時對擴(kuò)容規(guī)模和分表數(shù)量都有限制∨朴螅總得來說蚓炬，這些方案都不是十分的理想，多多少少都存在一些缺點(diǎn)躺屁，這也從一個側(cè)面反映出了Sharding擴(kuò)容的難度肯夏。

g.ID問題

一旦數(shù)據(jù)庫被切分到多個物理結(jié)點(diǎn)上，我們將不能再依賴數(shù)據(jù)庫自身的主鍵生成機(jī)制犀暑。一方面驯击，某個分區(qū)數(shù)據(jù)庫自生成的ID無法保證在全局上是唯一的；另一方面耐亏，應(yīng)用程序在插入數(shù)據(jù)之前需要先獲得ID,以便進(jìn)行SQL路由. 一些常見的主鍵生成策略

UUID 使用UUID作主鍵是最簡單的方案徊都，但是缺點(diǎn)也是非常明顯的。由于UUID非常的長广辰，除占用大量存儲空間外暇矫，最主要的問題是在索引上，在建立索引和基于索引進(jìn)行查詢時都存在性能問題择吊。Twitter的分布式自增ID算法Snowflake 在分布式系統(tǒng)中李根，需要生成全局UID的場合還是比較多的，twitter的snowflake解決了這種需求几睛，實(shí)現(xiàn)也還是很簡單的房轿，除去配置信息，核心代碼就是毫秒級時間41位 機(jī)器ID 10位 毫秒內(nèi)序列12位。

h.跨分片的排序分頁

一般來講囱持，分頁時需要按照指定字段進(jìn)行排序夯接。當(dāng)排序字段就是分片字段的時候，我們通過分片規(guī)則可以比較容易定位到指定的分片纷妆，而當(dāng)排序字段非分片字段的時候钻蹬，情況就會變得比較復(fù)雜了。為了最終結(jié)果的準(zhǔn)確性凭需，我們需要在不同的分片節(jié)點(diǎn)中將數(shù)據(jù)進(jìn)行排序并返回，并將不同分片返回的結(jié)果集進(jìn)行匯總和再次排序肝匆，最后再返回給用戶粒蜈。如下圖所示：

最后，小編分類整理了許多java進(jìn)階學(xué)習(xí)材料和BAT面試給熱愛IT行業(yè)的你旗国，如果需要資料的請轉(zhuǎn)發(fā)此文章后再私聊小編回復(fù)【java】就能領(lǐng)取2019年java進(jìn)階學(xué)習(xí)資料和BAT面試題以及《Effective Java》（第3版）電子版書籍枯怖。也可以加群：712263501領(lǐng)取海量學(xué)習(xí)資料進(jìn)行學(xué)習(xí)。