mysql分庫分表一般有如下場景
創(chuàng)新互聯(lián)公司堅持“要么做到,要么別承諾”的工作理念,服務(wù)領(lǐng)域包括:做網(wǎng)站、成都網(wǎng)站設(shè)計、企業(yè)官網(wǎng)、英文網(wǎng)站、手機(jī)端網(wǎng)站、網(wǎng)站推廣等服務(wù),滿足客戶于互聯(lián)網(wǎng)時代的臨朐網(wǎng)站設(shè)計、移動媒體設(shè)計的需求,幫助企業(yè)找到有效的互聯(lián)網(wǎng)解決方案。努力成為您成熟可靠的網(wǎng)絡(luò)建設(shè)合作伙伴!
其中1,2相對較容易實(shí)現(xiàn),本文重點(diǎn)講講水平拆表和水平拆庫,以及基于mybatis插件方式實(shí)現(xiàn)水平拆分方案落地。
在 《聊一聊擴(kuò)展字段設(shè)計》 一文中有講解到基于KV水平存儲擴(kuò)展字段方案,這就是非常典型的可以水平分表的場景。主表和kv表是一對N關(guān)系,隨著主表數(shù)據(jù)量增長,KV表最大N倍線性增長。
這里我們以分KV表水平拆分為場景
對于kv擴(kuò)展字段查詢,只會根據(jù)id + key 或者 id 為條件的方式查詢,所以這里我們可以按照id 分片即可
分512張表(實(shí)際場景具體分多少表還得根據(jù)字段增加的頻次而定)
分表后表名為kv_000 ~ kv_511
id % 512 = 1 .... 分到 kv_001,
id % 512 = 2 .... 分到 kv_002
依次類推!
水平分表相對比較容易,后面會講到基于mybatis插件實(shí)現(xiàn)方案
場景:以下我們基于博客文章表分庫場景來分析
目標(biāo):
表結(jié)構(gòu)如下(節(jié)選部分字段):
按照user_id sharding
假如分1024個庫,按照user_id % 1024 hash
user_id % 1024 = 1 分到db_001庫
user_id % 1024 = 2 分到db_002庫
依次類推
目前是2個節(jié)點(diǎn),假如后期達(dá)到瓶頸,我們可以增加至4個節(jié)點(diǎn)
最多可以增加只1024個節(jié)點(diǎn),性能線性增長
對于水平分表/分庫后,非shardingKey查詢首先得考慮到
基于mybatis分庫分表,一般常用的一種是基于spring AOP方式, 另外一種基于mybatis插件。其實(shí)兩種方式思路差不多。
為了比較直觀解決這個問題,我分別在Executor 和StatementHandler階段2個攔截器
實(shí)現(xiàn)動態(tài)數(shù)據(jù)源獲取接口
測試結(jié)果如下
由此可知,我們需要在Executor階段 切換數(shù)據(jù)源
對于分庫:
原始sql:
目標(biāo)sql:
其中定義了三個注解
@useMaster 是否強(qiáng)制讀主
@shardingBy 分片標(biāo)識
@DB 定義邏輯表名 庫名以及分片策略
1)編寫entity
Insert
select
以上順利實(shí)現(xiàn)mysql分庫,同樣的道理實(shí)現(xiàn)同時分庫分表也很容易實(shí)現(xiàn)。
此插件具體實(shí)現(xiàn)方案已開源:
目錄如下:
mysql分庫分表,首先得找到瓶頸在哪里(IO or CPU),是分庫還是分表,分多少?不能為了分庫分表而拆分。
原則上是盡量先垂直拆分 后 水平拆分。
以上基于mybatis插件分庫分表是一種實(shí)現(xiàn)思路,還有很多不完善的地方,
例如:
分表是分散數(shù)據(jù)庫壓力的好方法。
分表,最直白的意思,就是將一個表結(jié)構(gòu)分為多個表,然后,可以再同一個庫里,也可以放到不同的庫。
當(dāng)然,首先要知道什么情況下,才需要分表。個人覺得單表記錄條數(shù)達(dá)到百萬到千萬級別時就要使用分表了。
分表的分類
**1、縱向分表**
將本來可以在同一個表的內(nèi)容,人為劃分為多個表。(所謂的本來,是指按照關(guān)系型數(shù)據(jù)庫的第三范式要求,是應(yīng)該在同一個表的。)
分表理由:根據(jù)數(shù)據(jù)的活躍度進(jìn)行分離,(因為不同活躍的數(shù)據(jù),處理方式是不同的)
案例:
對于一個博客系統(tǒng),文章標(biāo)題,作者,分類,創(chuàng)建時間等,是變化頻率慢,查詢次數(shù)多,而且最好有很好的實(shí)時性的數(shù)據(jù),我們把它叫做冷數(shù)據(jù)。而博客的瀏覽量,回復(fù)數(shù)等,類似的統(tǒng)計信息,或者別的變化頻率比較高的數(shù)據(jù),我們把它叫做活躍數(shù)據(jù)。所以,在進(jìn)行數(shù)據(jù)庫結(jié)構(gòu)設(shè)計的時候,就應(yīng)該考慮分表,首先是縱向分表的處理。
這樣縱向分表后:
首先存儲引擎的使用不同,冷數(shù)據(jù)使用MyIsam 可以有更好的查詢數(shù)據(jù)。活躍數(shù)據(jù),可以使用Innodb ,可以有更好的更新速度。
其次,對冷數(shù)據(jù)進(jìn)行更多的從庫配置,因為更多的操作時查詢,這樣來加快查詢速度。對熱數(shù)據(jù),可以相對有更多的主庫的橫向分表處理。
其實(shí),對于一些特殊的活躍數(shù)據(jù),也可以考慮使用memcache ,redis之類的緩存,等累計到一定量再去更新數(shù)據(jù)庫。或者mongodb 一類的nosql 數(shù)據(jù)庫,這里只是舉例,就先不說這個。
**2、橫向分表**
字面意思,就可以看出來,是把大的表結(jié)構(gòu),橫向切割為同樣結(jié)構(gòu)的不同表,如,用戶信息表,user_1,user_2等。表結(jié)構(gòu)是完全一樣,但是,根據(jù)某些特定的規(guī)則來劃分的表,如根據(jù)用戶ID來取模劃分。
分表理由:根據(jù)數(shù)據(jù)量的規(guī)模來劃分,保證單表的容量不會太大,從而來保證單表的查詢等處理能力。
案例:同上面的例子,博客系統(tǒng)。當(dāng)博客的量達(dá)到很大時候,就應(yīng)該采取橫向分割來降低每個單表的壓力,來提升性能。例如博客的冷數(shù)據(jù)表,假如分為100個表,當(dāng)同時有100萬個用戶在瀏覽時,如果是單表的話,會進(jìn)行100萬次請求,而現(xiàn)在分表后,就可能是每個表進(jìn)行1萬個數(shù)據(jù)的請求(因為,不可能絕對的平均,只是假設(shè)),這樣壓力就降低了很多很多。
延伸:為什么要分表和分區(qū)?
日常開發(fā)中我們經(jīng)常會遇到大表的情況,所謂的大表是指存儲了百萬級乃至千萬級條記錄的表。這樣的表過于龐大,導(dǎo)致數(shù)據(jù)庫在查詢和插入的時候耗時太長,性能低下,如果涉及聯(lián)合查詢的情況,性能會更加糟糕。分表和表分區(qū)的目的就是減少數(shù)據(jù)庫的負(fù)擔(dān),提高數(shù)據(jù)庫的效率,通常點(diǎn)來講就是提高表的增刪改查效率。
什么是分表?
分表是將一個大表按照一定的規(guī)則分解成多張具有獨(dú)立存儲空間的實(shí)體表,我們可以稱為子表,每個表都對應(yīng)三個文件,MYD數(shù)據(jù)文件,.MYI索引文件,.frm表結(jié)構(gòu)文件。這些子表可以分布在同一塊磁盤上,也可以在不同的機(jī)器上。app讀寫的時候根據(jù)事先定義好的規(guī)則得到對應(yīng)的子表名,然后去操作它。
什么是分區(qū)?
分區(qū)和分表相似,都是按照規(guī)則分解表。不同在于分表將大表分解為若干個獨(dú)立的實(shí)體表,而分區(qū)是將數(shù)據(jù)分段劃分在多個位置存放,可以是同一塊磁盤也可以在不同的機(jī)器。分區(qū)后,表面上還是一張表,但數(shù)據(jù)散列到多個位置了。app讀寫的時候操作的還是大表名字,db自動去組織分區(qū)的數(shù)據(jù)。
**MySQL分表和分區(qū)有什么聯(lián)系呢?**
1、都能提高mysql的性高,在高并發(fā)狀態(tài)下都有一個良好的表現(xiàn)。
2、分表和分區(qū)不矛盾,可以相互配合的,對于那些大訪問量,并且表數(shù)據(jù)比較多的表,我們可以采取分表和分區(qū)結(jié)合的方式(如果merge這種分表方式,不能和分區(qū)配合的話,可以用其他的分表試),訪問量不大,但是表數(shù)據(jù)很多的表,我們可以采取分區(qū)的方式等。
3、分表技術(shù)是比較麻煩的,需要手動去創(chuàng)建子表,app服務(wù)端讀寫時候需要計算子表名。采用merge好一些,但也要創(chuàng)建子表和配置子表間的union關(guān)系。
4、表分區(qū)相對于分表,操作方便,不需要創(chuàng)建子表。
我們知道對于大型的互聯(lián)網(wǎng)應(yīng)用,數(shù)據(jù)庫單表的數(shù)據(jù)量可能達(dá)到千萬甚至上億級別,同時面臨這高并發(fā)的壓力。Master-Slave結(jié)構(gòu)只能對數(shù)據(jù)庫的讀能力進(jìn)行擴(kuò)展,寫操作還是集中在Master中,Master并不能無限制的掛接Slave庫,如果需要對數(shù)據(jù)庫的吞吐能力進(jìn)行進(jìn)一步的擴(kuò)展,可以考慮采用分庫分表的策略。
**1、分表**
在分表之前,首先要選中合適的分表策略(以哪個字典為分表字段,需要將數(shù)據(jù)分為多少張表),使數(shù)據(jù)能夠均衡的分布在多張表中,并且不影響正常的查詢。在企業(yè)級應(yīng)用中,往往使用org_id(組織主鍵)做為分表字段,在互聯(lián)網(wǎng)應(yīng)用中往往是userid。在確定分表策略后,當(dāng)數(shù)據(jù)進(jìn)行存儲及查詢時,需要確定到哪張表里去查找數(shù)據(jù),
數(shù)據(jù)存放的數(shù)據(jù)表 = 分表字段的內(nèi)容 % 分表數(shù)量
**2、分庫**
分表能夠解決單表數(shù)據(jù)量過大帶來的查詢效率下降的問題,但是不能給數(shù)據(jù)庫的并發(fā)訪問帶來質(zhì)的提升,面對高并發(fā)的寫訪問,當(dāng)Master無法承擔(dān)高并發(fā)的寫入請求時,不管如何擴(kuò)展Slave服務(wù)器,都沒有意義了。我們通過對數(shù)據(jù)庫進(jìn)行拆分,來提高數(shù)據(jù)庫的寫入能力,即所謂的分庫。分庫采用對關(guān)鍵字取模的方式,對數(shù)據(jù)庫進(jìn)行路由。
數(shù)據(jù)存放的數(shù)據(jù)庫=分庫字段的內(nèi)容%數(shù)據(jù)庫的數(shù)量
**3、即分表又分庫**
數(shù)據(jù)庫分表可以解決單表海量數(shù)據(jù)的查詢性能問題,分庫可以解決單臺數(shù)據(jù)庫的并發(fā)訪問壓力問題。
當(dāng)數(shù)據(jù)庫同時面臨海量數(shù)據(jù)存儲和高并發(fā)訪問的時候,需要同時采取分表和分庫策略。一般分表分庫策略如下:
中間變量 = 關(guān)鍵字%(數(shù)據(jù)庫數(shù)量*單庫數(shù)據(jù)表數(shù)量)
庫 = 取整(中間變量/單庫數(shù)據(jù)表數(shù)量)
表 = (中間變量%單庫數(shù)據(jù)表數(shù)量)
實(shí)例:
1、分庫分表
很明顯,一個主表(也就是很重要的表,例如用戶表)無限制的增長勢必嚴(yán)重影響性能,分庫與分表是一個很不錯的解決途徑,也就是性能優(yōu)化途徑,現(xiàn)在的案例是我們有一個1000多萬條記錄的用戶表members,查詢起來非常之慢,同事的做法是將其散列到100個表中,分別從members0到members99,然后根據(jù)mid分發(fā)記錄到這些表中,牛逼的代碼大概是這樣子:
復(fù)制代碼 代碼如下:
?php
for($i=0;$i 100; $i++ ){
//echo "CREATE TABLE db2.members{$i} LIKE db1.members
";
echo "INSERT INTO members{$i} SELECT * FROM members WHERE mid%100={$i}
";
}
?
2、不停機(jī)修改mysql表結(jié)構(gòu)
同樣還是members表,前期設(shè)計的表結(jié)構(gòu)不盡合理,隨著數(shù)據(jù)庫不斷運(yùn)行,其冗余數(shù)據(jù)也是增長巨大,同事使用了下面的方法來處理:
先創(chuàng)建一個臨時表:
/*創(chuàng)建臨時表*/
CREATE TABLE members_tmp LIKE members
然后修改members_tmp的表結(jié)構(gòu)為新結(jié)構(gòu),接著使用上面那個for循環(huán)來導(dǎo)出數(shù)據(jù),因為1000萬的數(shù)據(jù)一次性導(dǎo)出是不對的,mid是主鍵,一個區(qū)間一個區(qū)間的導(dǎo),基本是一次導(dǎo)出5萬條吧,這里略去了
接著重命名將新表替換上去:
/*這是個頗為經(jīng)典的語句哈*/
RENAME TABLE members TO members_bak,members_tmp TO members;
就是這樣,基本可以做到無損失,無需停機(jī)更新表結(jié)構(gòu),但實(shí)際上RENAME期間表是被鎖死的,所以選擇在線少的時候操作是一個技巧。經(jīng)過這個操作,使得原先8G多的表,一下子變成了2G多。
1
基本思想之什么是分庫分表?
從字面上簡單理解,就是把原本存儲于一個庫的數(shù)據(jù)分塊存儲到多個庫上,把原本存儲于一個表的數(shù)據(jù)分塊存儲到多個表上。
2
基本思想之為什么要分庫分表?
數(shù)據(jù)庫中的數(shù)據(jù)量不一定是可控的,在未進(jìn)行分庫分表的情況下,隨著時間和業(yè)務(wù)的發(fā)展,庫中的表會越來越多,表中的數(shù)據(jù)量也會越來越大,相應(yīng)地,數(shù)據(jù)操作,增刪改查的開銷也會越來越大;另外,由于無法進(jìn)行分布式式部署,而一臺服務(wù)器的資源(cpu、磁盤、內(nèi)存、io等)是有限的,最終數(shù)據(jù)庫所能承載的數(shù)據(jù)量、數(shù)據(jù)處理能力都將遭遇瓶頸。
3
分庫分表的實(shí)施策略。
分庫分表有垂直切分和水平切分兩種。
3.1
何謂垂直切分,即將表按照功能模塊、關(guān)系密切程度劃分出來,部署到不同的庫上。例如,我們會建立定義數(shù)據(jù)庫workdb、商品數(shù)據(jù)庫paydb、用戶數(shù)據(jù)庫userdb、日志數(shù)據(jù)庫logdb等,分別用于存儲項目數(shù)據(jù)定義表、商品定義表、用戶數(shù)據(jù)表、日志數(shù)據(jù)表等。
3.2
何謂水平切分,當(dāng)一個表中的數(shù)據(jù)量過大時,我們可以把該表的數(shù)據(jù)按照某種規(guī)則,例如userid散列,進(jìn)行劃分,然后存儲到多個結(jié)構(gòu)相同的表,和不同的庫上。例如,我們的userdb中的用戶數(shù)據(jù)表中,每一個表的數(shù)據(jù)量都很大,就可以把userdb切分為結(jié)構(gòu)相同的多個userdb:part0db、part1db等,再將userdb上的用戶數(shù)據(jù)表usertable,切分為很多usertable:usertable0、usertable1等,然后將這些表按照一定的規(guī)則存儲到多個userdb上。
3.3
應(yīng)該使用哪一種方式來實(shí)施數(shù)據(jù)庫分庫分表,這要看數(shù)據(jù)庫中數(shù)據(jù)量的瓶頸所在,并綜合項目的業(yè)務(wù)類型進(jìn)行考慮。
如果數(shù)據(jù)庫是因為表太多而造成海量數(shù)據(jù),并且項目的各項業(yè)務(wù)邏輯劃分清晰、低耦合,那么規(guī)則簡單明了、容易實(shí)施的垂直切分必是首選。
而如果數(shù)據(jù)庫中的表并不多,但單表的數(shù)據(jù)量很大、或數(shù)據(jù)熱度很高,這種情況之下就應(yīng)該選擇水平切分,水平切分比垂直切分要復(fù)雜一些,它將原本邏輯上屬于一體的數(shù)據(jù)進(jìn)行了物理分割,除了在分割時要對分割的粒度做好評估,考慮數(shù)據(jù)平均和負(fù)載平均,后期也將對項目人員及應(yīng)用程序產(chǎn)生額外的數(shù)據(jù)管理負(fù)擔(dān)。
在現(xiàn)實(shí)項目中,往往是這兩種情況兼而有之,這就需要做出權(quán)衡,甚至既需要垂直切分,又需要水平切分。我們的游戲項目便綜合使用了垂直與水平切分,我們首先對數(shù)據(jù)庫進(jìn)行垂直切分,然后,再針對一部分表,通常是用戶數(shù)據(jù)表,進(jìn)行水平切分。
4
分庫分表存在的問題。
4.1
事務(wù)問題。
在執(zhí)行分庫分表之后,由于數(shù)據(jù)存儲到了不同的庫上,數(shù)據(jù)庫事務(wù)管理出現(xiàn)了困難。如果依賴數(shù)據(jù)庫本身的分布式事務(wù)管理功能去執(zhí)行事務(wù),將付出高昂的性能代價;如果由應(yīng)用程序去協(xié)助控制,形成程序邏輯上的事務(wù),又會造成編程方面的負(fù)擔(dān)。
4.2
跨庫跨表的join問題。
在執(zhí)行了分庫分表之后,難以避免會將原本邏輯關(guān)聯(lián)性很強(qiáng)的數(shù)據(jù)劃分到不同的表、不同的庫上,這時,表的關(guān)聯(lián)操作將受到限制,我們無法join位于不同分庫的表,也無法join分表粒度不同的表,結(jié)果原本一次查詢能夠完成的業(yè)務(wù),可能需要多次查詢才能完成。
4.3
額外的數(shù)據(jù)管理負(fù)擔(dān)和數(shù)據(jù)運(yùn)算壓力。
額外的數(shù)據(jù)管理負(fù)擔(dān),最顯而易見的就是數(shù)據(jù)的定位問題和數(shù)據(jù)的增刪改查的重復(fù)執(zhí)行問題,這些都可以通過應(yīng)用程序解決,但必然引起額外的邏輯運(yùn)算,例如,對于一個記錄用戶成績的用戶數(shù)據(jù)表usertable,業(yè)務(wù)要求查出成績最好的100位,在進(jìn)行分表之前,只需一個order
by語句就可以搞定,但是在進(jìn)行分表之后,將需要n個order
by語句,分別查出每一個分表的前100名用戶數(shù)據(jù),然后再對這些數(shù)據(jù)進(jìn)行合并計算,才能得出結(jié)果。
上述整理于互聯(lián)網(wǎng)
我寫了一下你看看吧
select t1.*,t2.*,t3.*(我不知道你要什么字段,所以就全部顯示了,也可以根據(jù)你的需要添加字段)
from a1 t1
, a2 t2
, a3 t3
where t1.user_id = t2.user_id(主鍵或外鍵:根據(jù)實(shí)際表的字段連接)
and t2.user_id = t3.user_id(主鍵或外鍵:根據(jù)實(shí)際表的字段連接)
and t1.name = '張三'
;
上面我只是舉個例子,如果你連表的字段信息都不知道,那我也沒有辦法,本人只會這些。
根據(jù)業(yè)務(wù)邏輯,自己定一個規(guī)則去選擇不同的庫存儲。比方如計算機(jī)類書籍存儲A庫中的書籍表。經(jīng)濟(jì)類的書籍存儲B。最終構(gòu)造一個KEY(書籍類型)- value(庫標(biāo)識)。查詢時根據(jù)庫標(biāo)識取出對應(yīng)的數(shù)據(jù)源進(jìn)行查詢。
網(wǎng)站題目:mysql分庫怎么查,mysql分表后如何查詢
文章網(wǎng)址:http://m.newbst.com/article46/hsejeg.html
成都網(wǎng)站建設(shè)公司_創(chuàng)新互聯(lián),為您提供定制開發(fā)、關(guān)鍵詞優(yōu)化、靜態(tài)網(wǎng)站、小程序開發(fā)、動態(tài)網(wǎng)站、網(wǎng)站制作
聲明:本網(wǎng)站發(fā)布的內(nèi)容(圖片、視頻和文字)以用戶投稿、用戶轉(zhuǎn)載內(nèi)容為主,如果涉及侵權(quán)請盡快告知,我們將會在第一時間刪除。文章觀點(diǎn)不代表本網(wǎng)站立場,如需處理請聯(lián)系客服。電話:028-86922220;郵箱:631063699@qq.com。內(nèi)容未經(jīng)允許不得轉(zhuǎn)載,或轉(zhuǎn)載時需注明來源: 創(chuàng)新互聯(lián)