資料庫優化
為什么要優化
系統的吞吐量瓶頸往往出現在資料庫的訪問速度上
隨著應用程式的運行，資料庫的中的資料會越來越多，處理時間會相應變慢
資料是存放在磁盤上的，讀寫速度無法和記憶體相比
優化原則：減少系統瓶頸，減少資源占用，增加系統的反應速度。
資料庫結構優化
一個好的資料庫設計方案對于資料庫的性能往往會起到事半功倍的效果。
需要考慮資料冗余、查詢和更新的速度、欄位的資料型別是否合理等多方面的內容。
將欄位很多的表分解成多個表
對于欄位較多的表，如果有些欄位的使用頻率很低，可以將這些欄位分離出來形成新表。
因為當一個表的資料量很大時，會由于使用頻率低的欄位的存在而變慢。
增加中間表
對于需要經常聯合查詢的表，可以建立中間表以提高查詢效率。
通過建立中間表，將需要通過聯合查詢的資料插入到中間表中，然后將原來的聯合查詢改為對中間表的查詢。
增加冗余欄位
設計資料表時應盡量遵循范式理論的規約，盡可能的減少冗余欄位，讓資料庫設計看起來精致、優雅。但是，合理的加入冗余欄位可以提高查詢速度。
表的規范化程度越高，表和表之間的關系越多，需要連接查詢的情況也就越多，性能也就越差。
注意：
冗余欄位的值在一個表中修改了，就要想辦法在其他表中更新，否則就會導致資料不一致的問題。
MySQL資料庫cpu飆升到500%的話他怎么處理？
當 cpu 飆升到 500%時，先用作業系統命令 top 命令觀察是不是 mysqld 占用導致的，如果不是，找出占用高的行程，并進行相關處理。
如果是 mysqld 造成的， show processlist，看看里面跑的 session 情況，是不是有消耗資源的 sql 在運行。找出消耗高的 sql，看看執行計劃是否準確， index 是否缺失，或者實在是資料量太大造成。
一般來說，肯定要 kill 掉這些執行緒(同時觀察 cpu 使用率是否下降)，等進行相應的調整(比如說加索引、改 sql、改記憶體引數)之后，再重新跑這些 SQL。
也有可能是每個 sql 消耗資源并不多，但是突然之間，有大量的 session 連進來導致 cpu 飆升，這種情況就需要跟應用一起來分析為何連接數會激增，再做出相應的調整，比如說限制連接數等
大表怎么優化？某個表有近千萬資料，CRUD比較慢，如何優化？分庫分表了是怎么做的？分表分庫了有什么問題？有用到中間件么？他們的原理知道么？
當MySQL單表記錄數過大時，資料庫的CRUD性能會明顯下降，一些常見的優化措施如下：
限定資料的范圍： 務必禁止不帶任何限制資料范圍條件的查詢陳述句。比如：我們當用戶在查詢訂單歷史的時候，我們可以控制在一個月的范圍內。；
讀/寫分離： 經典的資料庫拆分方案，主庫負責寫，從庫負責讀；
快取： 使用MySQL的快取，另外對重量級、更新少的資料可以考慮使用應用級別的快取；
還有就是通過分庫分表的方式進行優化，主要有垂直分表和水平分表
垂直磁區：
根據資料庫里面資料表的相關性進行拆分。 例如，用戶表中既有用戶的登錄資訊又有用戶的基本資訊，可以將用戶表拆分成兩個單獨的表，甚至放到單獨的庫做分庫。
簡單來說垂直拆分是指資料表列的拆分，把一張列比較多的表拆分為多張表。 如下圖所示，這樣來說大家應該就更容易理解了。
img
垂直拆分的優點： 可以使得行資料變小，在查詢時減少讀取的Block數，減少I/O次數。此外，垂直磁區可以簡化表的結構，易于維護。
垂直拆分的缺點： 主鍵會出現冗余，需要管理冗余列，并會引起Join操作，可以通過在應用層進行Join來解決。此外，垂直磁區會讓事務變得更加復雜；
垂直分表
把主鍵和一些列放在一個表，然后把主鍵和另外的列放在另一個表中
img
適用場景
1、如果一個表中某些列常用，另外一些列不常用
2、可以使資料行變小，一個資料頁能存盤更多資料，查詢時減少I/O次數
缺點
有些分表的策略基于應用層的邏輯演算法，一旦邏輯演算法改變，整個分表邏輯都會改變，擴展性較差
對于應用層來說，邏輯演算法增加開發成本
管理冗余列，查詢所有資料需要join操作
水平磁區：
保持資料表結構不變，通過某種策略存盤資料分片。這樣每一片資料分散到不同的表或者庫中，達到了分布式的目的。 水平拆分可以支撐非常大的資料量。
水平拆分是指資料表行的拆分，表的行數超過200萬行時，就會變慢，這時可以把一張的表的資料拆成多張表來存放。舉個例子：我們可以將用戶資訊表拆分成多個用戶資訊表，這樣就可以避免單一表資料量過大對性能造成影響。
資料庫水平拆分
水品拆分可以支持非常大的資料量。需要注意的一點是:分表僅僅是解決了單一表資料過大的問題，但由于表的資料還是在同一臺機器上，其實對于提升MySQL并發能力沒有什么意義，所以 水平拆分最好分庫 。
水平拆分能夠 支持非常大的資料量存盤，應用端改造也少，但 分片事務難以解決 ，跨界點Join性能較差，邏輯復雜。
《Java工程師修煉之道》的作者推薦 盡量不要對資料進行分片，因為拆分會帶來邏輯、部署、運維的各種復雜度 ，一般的資料表在優化得當的情況下支撐千萬以下的資料量是沒有太大問題的。如果實在要分片，盡量選擇客戶端分片架構，這樣可以減少一次和中間件的網路I/O。
水平分表：
表很大，分割后可以降低在查詢時需要讀的資料和索引的頁數，同時也降低了索引的層數，提高查詢次數
img
適用場景
1、表中的資料本身就有獨立性，例如表中分表記錄各個地區的資料或者不同時期的資料，特別是有些資料常用，有些不常用。
2、需要把資料存放在多個介質上。
水平切分的缺點
1、給應用增加復雜度，通常查詢時需要多個表名，查詢所有資料都需UNION操作
2、在許多資料庫應用中，這種復雜度會超過它帶來的優點，查詢時會增加讀一個索引層的磁盤次數
下面補充一下資料庫分片的兩種常見方案：
客戶端代理： 分片邏輯在應用端，封裝在jar包中，通過修改或者封裝JDBC層來實作。 當當網的 Sharding-JDBC 、阿里的TDDL是兩種比較常用的實作。
中間件代理： 在應用和資料中間加了一個代理層。分片邏輯統一維護在中間件服務中。 我們現在談的 Mycat 、360的Atlas、網易的DDB等等都是這種架構的實作。
分庫分表后面臨的問題
事務支持 分庫分表后，就成了分布式事務了。如果依賴資料庫本身的分布式事務管理功能去執行事務，將付出高昂的性能代價； 如果由應用程式去協助控制，形成程式邏輯上的事務，又會造成編程方面的負擔。
跨庫join
只要是進行切分，跨節點Join的問題是不可避免的。但是良好的設計和切分卻可以減少此類情況的發生。解決這一問題的普遍做法是分兩次查詢實作。在第一次查詢的結果集中找出關聯資料的id,根據這些id發起第二次請求得到關聯資料。 分庫分表方案產品
跨節點的count,order by,group by以及聚合函式問題 這些是一類問題，因為它們都需要基于全部資料集合進行計算。多數的代理都不會自動處理合并作業。解決方案：與解決跨節點join問題的類似，分別在各個節點上得到結果后在應用程式端進行合并。和join不同的是每個結點的查詢可以并行執行，因此很多時候它的速度要比單一大表快很多。但如果結果集很大，對應用程式記憶體的消耗是一個問題。
資料遷移，容量規劃，擴容等問題 來自淘寶綜合業務平臺團隊，它利用對2的倍數取余具有向前兼容的特性（如對4取余得1的數對2取余也是1）來分配資料，避免了行級別的資料遷移，但是依然需要進行表級別的遷移，同時對擴容規模和分表數量都有限制。總得來說，這些方案都不是十分的理想，多多少少都存在一些缺點，這也從一個側面反映出了Sharding擴容的難度。
ID問題
一旦資料庫被切分到多個物理結點上，我們將不能再依賴資料庫自身的主鍵生成機制。一方面，某個磁區資料庫自生成的ID無法保證在全域上是唯一的；另一方面，應用程式在插入資料之前需要先獲得ID,以便進行SQL路由. 一些常見的主鍵生成策略
UUID 使用UUID作主鍵是最簡單的方案，但是缺點也是非常明顯的。由于UUID非常的長，除占用大量存盤空間外，最主要的問題是在索引上，在建立索引和基于索引進行查詢時都存在性能問題。 Twitter的分布式自增ID演算法Snowflake 在分布式系統中，需要生成全域UID的場合還是比較多的，twitter的snowflake解決了這種需求，實作也還是很簡單的，除去配置資訊，核心代碼就是毫秒級時間41位 機器ID 10位 毫秒內序列12位。
跨分片的排序分頁
般來講，分頁時需要按照指定欄位進行排序。當排序欄位就是分片欄位的時候，我們通過分片規則可以比較容易定位到指定的分片，而當排序欄位非分片欄位的時候，情況就會變得比較復雜了。為了最終結果的準確性，我們需要在不同的分片節點中將資料進行排序并回傳，并將不同分片回傳的結果集進行匯總和再次排序，最后再回傳給用戶。如下圖所示：
在這里插入圖片描述
uj5u.com熱心網友回復：
轉載請註明出處，本文鏈接：https://www.uj5u.com/houduan/58267.html
標籤：Java EE
上一篇：springboot結合dubbo，消費端啟動的問題，詳情見下圖
下一篇：求救啊兄弟萌