大錶怎麼優化?某個錶有近千萬數據,CRUD比較慢,如何優化?分庫分錶了是怎麼做的?

阿裏雲問答 2022-01-07 08:39:49 阅读数:76

千萬 crud 做的

大錶怎麼優化?某個錶有近千萬數據,CRUD比較慢,如何優化?分庫分錶了是怎麼做的?分錶分庫了有什麼問題?有用到中間件麼?他們的原理知道麼?




采納答案1:

當MySQL單錶記錄數過大時,數據庫的CRUD性能會明顯下降,一些常見的優化措施如下:

  1. 限定數據的範圍: 務必禁止不帶任何限制數據範圍條件的查詢語句。比如:我們當用戶在查詢訂單曆史的時候,我們可以控制在一個月的範圍內。;
  2. 讀/寫分離: 經典的數據庫拆分方案,主庫負責寫,從庫負責讀;
  3. 緩存: 使用MySQL的緩存,另外對重量級、更新少的數據可以考慮使用應用級別的緩存; 還有就是通過分庫分錶的方式進行優化,主要有垂直分錶和水平分錶

垂直分區

根據數據庫裏面數據錶的相關性進行拆分。 例如,用戶錶中既有用戶的登錄信息又有用戶的基本信息,可以將用戶錶拆分成兩個單獨的錶,甚至放到單獨的庫做分庫。

簡單來說垂直拆分是指數據錶列的拆分,把一張列比較多的錶拆分為多張錶。 如下圖所示,這樣來說大家應該就更容易理解了。

1.jpg

垂直拆分的優點: 可以使得行數據變小,在查詢時减少讀取的Block數,减少I/O次數。此外,垂直分區可以簡化錶的結構,易於維護。

垂直拆分的缺點: 主鍵會出現冗餘,需要管理冗餘列,並會引起Join操作,可以通過在應用層進行Join來解决。此外,垂直分區會讓事務變得更加複雜;

垂直分錶

把主鍵和一些列放在一個錶,然後把主鍵和另外的列放在另一個錶中

22.PNG

適用場景 1、如果一個錶中某些列常用,另外一些列不常用 2、可以使數據行變小,一個數據頁能存儲更多數據,查詢時减少I/O次數 缺點 有些分錶的策略基於應用層的邏輯算法,一旦邏輯算法改變,整個分錶邏輯都會改變,擴展性較差 對於應用層來說,邏輯算法增加開發成本 管理冗餘列,查詢所有數據需要join操作

水平分區:

保持數據錶結構不變,通過某種策略存儲數據分片。這樣每一片數據分散到不同的錶或者庫中,達到了分布式的目的。 水平拆分可以支撐非常大的數據量。

水平拆分是指數據錶行的拆分,錶的行數超過200萬行時,就會變慢,這時可以把一張的錶的數據拆成多張錶來存放。舉個例子:我們可以將用戶信息錶拆分成多個用戶信息錶,這樣就可以避免單一錶數據量過大對性能造成影響。

3.jpg

水品拆分可以支持非常大的數據量。需要注意的一點是:分錶僅僅是解决了單一錶數據過大的問題,但由於錶的數據還是在同一臺機器上,其實對於提昇MySQL並發能力沒有什麼意義,所以 水平拆分最好分庫 。

水平拆分能够 支持非常大的數據量存儲,應用端改造也少,但 分片事務難以解决 ,跨界點Join性能較差,邏輯複雜。

《Java工程師修煉之道》的作者推薦 盡量不要對數據進行分片,因為拆分會帶來邏輯、部署、運維的各種複雜度 ,一般的數據錶在優化得當的情况下支撐千萬以下的數據量是沒有太大問題的。如果實在要分片,盡量選擇客戶端分片架構,這樣可以减少一次和中間件的網絡I/O。

水平分錶: 錶很大,分割後可以降低在查詢時需要讀的數據和索引的頁數,同時也降低了索引的層數,提高查詢次數

4.jpg

適用場景 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路由. 一些常見的主鍵生成策略


版权声明:本文为[阿裏雲問答]所创,转载请带上原文链接,感谢。 https://gsmany.com/2022/01/202201070839487133.html