你好,歡迎來到js代碼網。

微信登錄

首頁>數據庫> MySQL數據庫性能優化之分區分表分庫

MySQL數據庫性能優化之分區分表分庫

  • 分類:數據庫
  • 時間:02-27
  • 閱讀:1382

分表是分散數據庫壓力的好方法。

分表,最直白的意思,就是將一個表結構分為多個表,然后,可以再同一個庫里,也可以放到不同的庫。
當然,首先要知道什么情況下,才需要分表。個人覺得單表記錄條數達到百萬到千萬級別時就要使用分表了。

分表的分類

1、縱向分表
將本來可以在同一個表的內容,人為劃分為多個表。(所謂的本來,是指按照關系型數據庫的第三范式要求,是應該在同一個表的。)
分表理由:根據數據的活躍度進行分離,(因為不同活躍的數據,處理方式是不同的)

案例:
對于一個博客系統,文章標題,作者,分類,創建時間等,是變化頻率慢,查詢次數多,而且最好有很好的實時性的數據,我們把它叫做冷數據。而博客的瀏覽量,回復數等,類似的統計信息,或者別的變化頻率比較高的數據,我們把它叫做活躍數據。所以,在進行數據庫結構設計的時候,就應該考慮分表,首先是縱向分表的處理。

這樣縱向分表后:
首先存儲引擎的使用不同,冷數據使用MyIsam 可以有更好的查詢數據。活躍數據,可以使用Innodb ,可以有更好的更新速度。
其次,對冷數據進行更多的從庫配置,因為更多的操作時查詢,這樣來加快查詢速度。對熱數據,可以相對有更多的主庫的橫向分表處理。
其實,對于一些特殊的活躍數據,也可以考慮使用memcache ,redis之類的緩存,等累計到一定量再去更新數據庫。或者mongodb 一類的nosql 數據庫,這里只是舉例,就先不說這個。

2、橫向分表
字面意思,就可以看出來,是把大的表結構,橫向切割為同樣結構的不同表,如,用戶信息表,user_1,user_2等。表結構是完全一樣,但是,根據某些特定的規則來劃分的表,如根據用戶ID來取模劃分。

分表理由:根據數據量的規模來劃分,保證單表的容量不會太大,從而來保證單表的查詢等處理能力。

案例:同上面的例子,博客系統。當博客的量達到很大時候,就應該采取橫向分割來降低每個單表的壓力,來提升性能。例如博客的冷數據表,假如分為100個表,當同時有100萬個用戶在瀏覽時,如果是單表的話,會進行100萬次請求,而現在分表后,就可能是每個表進行1萬個數據的請求(因為,不可能絕對的平均,只是假設),這樣壓力就降低了很多很多。


延伸:為什么要分表和分區?

日常開發中我們經常會遇到大表的情況,所謂的大表是指存儲了百萬級乃至千萬級條記錄的表。這樣的表過于龐大,導致數據庫在查詢和插入的時候耗時太長,性能低下,如果涉及聯合查詢的情況,性能會更加糟糕。分表和表分區的目的就是減少數據庫的負擔,提高數據庫的效率,通常點來講就是提高表的增刪改查效率。

什么是分表?
分表是將一個大表按照一定的規則分解成多張具有獨立存儲空間的實體表,我們可以稱為子表,每個表都對應三個文件,MYD數據文件,.MYI索引文件,.frm表結構文件。這些子表可以分布在同一塊磁盤上,也可以在不同的機器上。app讀寫的時候根據事先定義好的規則得到對應的子表名,然后去操作它。

什么是分區?
分區和分表相似,都是按照規則分解表。不同在于分表將大表分解為若干個獨立的實體表,而分區是將數據分段劃分在多個位置存放,可以是同一塊磁盤也可以在不同的機器。分區后,表面上還是一張表,但數據散列到多個位置了。app讀寫的時候操作的還是大表名字,db自動去組織分區的數據。

MySQL分表和分區有什么聯系呢?
1、都能提高mysql的性高,在高并發狀態下都有一個良好的表現。
2、分表和分區不矛盾,可以相互配合的,對于那些大訪問量,并且表數據比較多的表,我們可以采取分表和分區結合的方式(如果merge這種分表方式,不能和分區配合的話,可以用其他的分表試),訪問量不大,但是表數據很多的表,我們可以采取分區的方式等。
3、分表技術是比較麻煩的,需要手動去創建子表,app服務端讀寫時候需要計算子表名。采用merge好一些,但也要創建子表和配置子表間的union關系。
4、表分區相對于分表,操作方便,不需要創建子表。

我們知道對于大型的互聯網應用,數據庫單表的數據量可能達到千萬甚至上億級別,同時面臨這高并發的壓力。Master-Slave結構只能對數據庫的讀能力進行擴展,寫操作還是集中在Master中,Master并不能無限制的掛接Slave庫,如果需要對數據庫的吞吐能力進行進一步的擴展,可以考慮采用分庫分表的策略。

1、分表
在分表之前,首先要選中合適的分表策略(以哪個字典為分表字段,需要將數據分為多少張表),使數據能夠均衡的分布在多張表中,并且不影響正常的查詢。在企業級應用中,往往使用org_id(組織主鍵)做為分表字段,在互聯網應用中往往是userid。在確定分表策略后,當數據進行存儲及查詢時,需要確定到哪張表里去查找數據,

數據存放的數據表 = 分表字段的內容 % 分表數量

2、分庫
分表能夠解決單表數據量過大帶來的查詢效率下降的問題,但是不能給數據庫的并發訪問帶來質的提升,面對高并發的寫訪問,當Master無法承擔高并發的寫入請求時,不管如何擴展Slave服務器,都沒有意義了。我們通過對數據庫進行拆分,來提高數據庫的寫入能力,即所謂的分庫。分庫采用對關鍵字取模的方式,對數據庫進行路由。

數據存放的數據庫=分庫字段的內容%數據庫的數量

3、即分表又分庫
數據庫分表可以解決單表海量數據的查詢性能問題,分庫可以解決單臺數據庫的并發訪問壓力問題。

當數據庫同時面臨海量數據存儲和高并發訪問的時候,需要同時采取分表和分庫策略。一般分表分庫策略如下:

中間變量 = 關鍵字%(數據庫數量*單庫數據表數量)

庫 = 取整(中間變量/單庫數據表數量)

表 = (中間變量%單庫數據表數量)


實例:

1、分庫分表 

很明顯,一個主表(也就是很重要的表,例如用戶表)無限制的增長勢必嚴重影響性能,分庫與分表是一個很不錯的解決途徑,也就是性能優化途徑,現在的案例是我們有一個1000多萬條記錄的用戶表members,查詢起來非常之慢,同事的做法是將其散列到100個表中,分別從members0到members99,然后根據mid分發記錄到這些表中,牛逼的代碼大概是這樣子:
復制代碼 代碼如下:
<?php 
for($i=0;$i< 100; $i++ ){ 
//echo "CREATE TABLE db2.members{$i} LIKE db1.members<br>"; 
echo "INSERT INTO members{$i} SELECT * FROM members WHERE mid%100={$i}<br>"; 

?>

2、不停機修改mysql表結構 

同樣還是members表,前期設計的表結構不盡合理,隨著數據庫不斷運行,其冗余數據也是增長巨大,同事使用了下面的方法來處理: 

先創建一個臨時表: 
/*創建臨時表*/ 
CREATE TABLE members_tmp LIKE members 

然后修改members_tmp的表結構為新結構,接著使用上面那個for循環來導出數據,因為1000萬的數據一次性導出是不對的,mid是主鍵,一個區間一個區間的導,基本是一次導出5萬條吧,這里略去了 
接著重命名將新表替換上去: 

/*這是個頗為經典的語句哈*/ 
RENAME TABLE members TO members_bak,members_tmp TO members; 

就是這樣,基本可以做到無損失,無需停機更新表結構,但實際上RENAME期間表是被鎖死的,所以選擇在線少的時候操作是一個技巧。經過這個操作,使得原先8G多的表,一下子變成了2G多。


相關文章

北京 28开奖走势图