Mysql數(shù)據(jù)庫分庫分表

數(shù)據(jù)庫——MySQL分庫分表的演進(jìn)以及引申

https://blog.csdn.net/wolf_love666/article/details/82773300

1袖裕、了解幾個問題?

1别凤、分庫分表相關(guān)術(shù)語

讀寫分離: 不同的數(shù)據(jù)庫梆掸,同步相同的數(shù)據(jù)榕茧,分別只負(fù)責(zé)數(shù)據(jù)的讀和寫授帕;

分區(qū): 指定分區(qū)列表達(dá)式鸭栖,把記錄拆分到不同的區(qū)域中(必須是同一服務(wù)器巷查,可以是不同硬盤)有序,應(yīng)用看來還是同一張表,沒有變化岛请;

分庫:一個系統(tǒng)的多張數(shù)據(jù)表旭寿,存儲到多個數(shù)據(jù)庫實例中;

分表: 對于一張多行(記錄)多列(字段)的二維數(shù)據(jù)表崇败,又分兩種情形:

(1) 垂直分表: 豎向切分盅称,不同分表存儲不同的字段,可以把不常用或者大容量僚匆、或者不同業(yè)務(wù)的字段拆分出去微渠;

(2) 水平分表(最復(fù)雜): 橫向切分,按照特定分片算法咧擂,不同分表存儲不同的記錄逞盆。

2、MySQL為什么分庫分表

當(dāng)一張表的數(shù)據(jù)達(dá)到幾千萬時松申,你查詢一次所花的時間會變多云芦,如果有聯(lián)合查詢的話,可能會死在那兒贸桶。分表的目的就在于此舅逸,減小數(shù)據(jù)庫的負(fù)擔(dān),縮短查詢時間皇筛。目前我們系統(tǒng)將近20億數(shù)據(jù)每張表最大的接近519w條/表琉历,每條數(shù)據(jù)大約3k,1131M將近1G的表數(shù)據(jù)。查詢經(jīng)常超時旗笔,單條SQLcount(*)查詢時間達(dá)到最大260ms彪置,0.26s。標(biāo)準(zhǔn)是超過0.1s的數(shù)據(jù)為慢SQL蝇恶。

3拳魁、分庫分表會遺留的問題

事務(wù)問題

跨表join,聚合查詢order by撮弧,group by等問題

數(shù)據(jù)傾斜問題

分庫分表下的主鍵id問題

業(yè)務(wù)上漲潘懊,伸縮性問題

2、根據(jù)分庫分表遺留的問題對應(yīng)的case

2.1贿衍、事務(wù)問題

前提了解:單條SQL即為一個事務(wù)

Spring下的事務(wù)的傳播性(7)

Spring下的默認(rèn)超時設(shè)置(TIMEOUT_DEFAULT)

PROPAGATION_REQUIRED(默認(rèn)事務(wù)傳播機(jī)制)

支持當(dāng)前交易; 如果不存在則創(chuàng)建一個新的

PROPAGATION_REQUIRES_NEW

創(chuàng)建一個新事務(wù)授舟,暫停當(dāng)前事務(wù)(如果存在)

PROPAGATION_NESTED

如果存在當(dāng)前事務(wù),則在嵌套事務(wù)中執(zhí)行,異趁潮玻回滾到上個事務(wù)的事務(wù)點

PROPAGATION_SUPPORT

支持當(dāng)前交易; 如果不存在則執(zhí)行非事務(wù)性岂却。

PROPAGATION_NOT_SUPPORT

不支持當(dāng)前交易; 而是總是以非交易方式執(zhí)行

PROPAGATION_NEVER

不支持當(dāng)前交易; 如果當(dāng)前事務(wù)存在則拋出異常

PROPAGATION_MANDATORY

支持當(dāng)前交易; 如果不存在當(dāng)前事務(wù)則拋出異常。

Spring下的事務(wù)5種隔離級別:

ISOLATION_DEFAULT

使用基礎(chǔ)數(shù)據(jù)存儲的默認(rèn)隔離級別裙椭。

ISOLATION_READ_COMMITTED

表示禁止臟讀; 可以發(fā)生不可重復(fù)的讀取和幻像讀取躏哩。

ISOLATION_READ_UNCOMMITTED

表示可能發(fā)生臟讀,不可重復(fù)讀和幻像讀揉燃。

ISOLATION_REPEATABLE_READ

表示防止臟讀和不可重復(fù)讀; 可以發(fā)生幻像讀取扫尺。

ISOLATION_SERIALIZABLE

表示禁止臟讀,不可重復(fù)讀和幻像讀

酸(ACID)堿(BASE)平衡理論

了解單表下的事務(wù)符合ACID模型炊汤。

A(Atomicity)原子性

事務(wù)通常由多個語句組成正驻。原子性保證每個事務(wù)被視為單個“單元”,它可以完全成功抢腐,也可以完全失敼檬铩:如果構(gòu)成事務(wù)的任何語句都無法完成,整個事務(wù)將失敗并且數(shù)據(jù)庫保持不變迈倍。原子系統(tǒng)必須保證每種情況下的原子性伤靠,包括電源故障,錯誤和崩潰啼染。

C(Consistency)一致性

一致性確保事務(wù)只能將數(shù)據(jù)庫從一個有效狀態(tài)帶到另一個有效狀態(tài)宴合,維護(hù)數(shù)據(jù)庫不變量:寫入數(shù)據(jù)庫的任何數(shù)據(jù)必須根據(jù)所有定義的規(guī)則有效,包括約束迹鹅,級聯(lián)卦洽,觸發(fā)器及其任何組合。這可以防止非法事務(wù)導(dǎo)致數(shù)據(jù)庫損壞斜棚,但不能保證事務(wù)正確阀蒂。

分布式環(huán)境下如何保證一致性该窗?

一致性,可用性蚤霞,容錯性------其實考察CAP原理挪捕。

基本可用性、軟狀態(tài)争便、最終一致性-----------BASE理論

I(Isolation)隔離性

事務(wù)通常同時執(zhí)行(例如,同時讀取和寫入多個表)断医。隔離確保事務(wù)的并發(fā)執(zhí)行使數(shù)據(jù)庫處于與按順序執(zhí)行事務(wù)時獲得的狀態(tài)相同的狀態(tài)滞乙。隔離是并發(fā)控制的主要目標(biāo); 根據(jù)使用的方法,不完整交易的影響甚至可能對其他交易不可見鉴嗤。

隔離性的四種和造成的三種影響斩启?

mysql四鐘隔離級別

默認(rèn)的是可重復(fù)讀,這樣就會導(dǎo)致幻讀醉锅,那么我們實際應(yīng)用中又是如何避免幻讀的呢兔簇?

多個事務(wù)提交執(zhí)行如何保證隔離性?

如果是單臺應(yīng)用則spring事務(wù)控制就可以了參考上面的spring事務(wù)申明傳播特性和隔離性控制硬耍。

如果是分布式應(yīng)用則這里需要考慮的是分布式事務(wù)垄琐。

暫時引用下鏈接:https://www.e-learn.cn/content/qita/1659193

D(Durability)持久性

持久性保證一旦提交了事務(wù),即使系統(tǒng)出現(xiàn)故障(例如停電或崩潰)经柴,它也將保持提交狀態(tài)狸窘。這通常意味著已完成的事務(wù)(或其影響)記錄在非易失性存儲器中。

引申mysql如何保證的持久性坯认?

redo log緩沖區(qū)

redo log緩沖區(qū)是一塊內(nèi)存區(qū)域翻擒,保存將要寫入redo log的數(shù)據(jù)。

mysql 崩潰恢復(fù)是需要redo log的牛哺。

redo log緩沖區(qū)大小由innodb_log_buffer_size配置選項定義陋气。

redo log緩沖區(qū)會定期把內(nèi)存中的回滾日志刷到磁盤上。一個大的redo log緩沖區(qū)意味著允許大事務(wù)運行引润,而無需在事務(wù)提交之前將redo log寫入磁盤巩趁。因此,如果您有更新淳附,插入或刪除多行的事務(wù)晶渠,則使用更大的redo log緩沖區(qū)可節(jié)省磁盤I/O。

redo log緩存
innodb_flush_log_at_commit過程

2.2燃观、跨表join褒脯,聚合查詢order by,group by等問題

將原本處于mysql執(zhí)行的跨表查詢以及聚合查詢等操作提前到網(wǎng)關(guān)層進(jìn)行聚合缆毁。比如說現(xiàn)在你有這樣一條SQL: select * from tableXX order by? create_time? desc limit 0,10;

則會從512張表中每張表都獲取10條數(shù)據(jù)番川,然后再網(wǎng)關(guān)層就會出現(xiàn)512*10條數(shù)據(jù),然后重新排序聚合提取10條數(shù)據(jù)返回給應(yīng)用。帶來的就是性能響應(yīng)時間增加颁督。

2.3践啄、數(shù)據(jù)傾斜問題

數(shù)據(jù)傾斜來源于固定hash算法,一般如果設(shè)定值比如M條數(shù)據(jù)沉御,N張表那么每張表的合理數(shù)據(jù)應(yīng)該為M/N屿讽。但是我們知道往往有些現(xiàn)實問題就不是那么理想的狀態(tài)。比如我這個例子:

固定Hash算法:

為了確保唯一值吠裆,采用來活動id和運營人員id取模這樣伐谈,運營人員如果下面報名的商品很多,則導(dǎo)致該商品表下面的商品數(shù)量會急劇增多试疙,而這個世界往往經(jīng)常又是那么巧合诵棵,你越優(yōu)秀,什么事情都越順利祝旷。你越倒霉履澳,什么破事都會找你。而數(shù)據(jù)庫這里也存在這個問題怀跛,假設(shè)A運營人員一直優(yōu)秀距贷,那么他的數(shù)據(jù)量只能越來越多,這樣就會導(dǎo)致某個表的數(shù)據(jù)量占據(jù)第一名與最后一名表的數(shù)據(jù)量偏差可能相差60%甚至更多吻谋。

一致性hash算法:

定義:

關(guān)于一致性Hash算法储耐,先構(gòu)造一個長度為2的32次方的整數(shù)環(huán)(這個環(huán)被稱之為一致性Hsah環(huán)),根據(jù)節(jié)點名稱的Hash值將服務(wù)器節(jié)點放置在這個Hash環(huán)上滨溉,然后根據(jù)數(shù)據(jù)的Key值計算得到其Hash值(其分布也為0-2的32次方)什湘,接著在Hash環(huán)上順時針查找距離這個key的Hash值最近的服務(wù)器接到哪,完成Key到服務(wù)器的映射查找晦攒。

普通的余數(shù)hash(hash(比如用戶id)%服務(wù)器機(jī)器數(shù))算法伸縮性很差闽撤,當(dāng)新增或者下線服務(wù)器機(jī)器時候,用戶id與服務(wù)器的映射關(guān)系會大量失效脯颜。一致性hash則利用hash環(huán)對其進(jìn)行了改進(jìn)哟旗。

為了能直觀的理解一致性hash原理,這里結(jié)合一個簡單的例子來講解栋操,512張表的問題闸餐,地址為ip0~ip511,由于太多則以ip1/ip2/ip3/ip4來說明矾芙。

一致性hash是首先計算四個ip地址對應(yīng)的hash值

hash(ip1),hash(ip2),hash(ip3),hash(ip3)舍沙,計算出來的hash值是0~最大正整數(shù)直接的一個值,這四個值在一致性hash環(huán)上呈現(xiàn)如下圖:

一致性hash

hash環(huán)上順時針從整數(shù)0開始剔宪,一直到最大正整數(shù)拂铡,我們根據(jù)四個ip計算的hash值肯定會落到這個hash環(huán)上的某一個點壹无,至此我們把服務(wù)器的四個ip映射到了一致性hash環(huán)

當(dāng)用戶在客戶端進(jìn)行請求時候,首先根據(jù)hash(用戶id)計算路由規(guī)則(hash值)感帅,然后看hash值落到了hash環(huán)的那個地方斗锭,根據(jù)hash值在hash環(huán)上的位置順時針找距離最近的ip作為路由ip.

一致性hash過程

如上圖可知user1,user2的請求會落到服務(wù)器ip2進(jìn)行處理,User3的請求會落到服務(wù)器ip3進(jìn)行處理失球,user4的請求會落到服務(wù)器ip4進(jìn)行處理岖是,user5,user6的請求會落到服務(wù)器ip1進(jìn)行處理。

下面考慮當(dāng)ip2的服務(wù)器掛了的時候會出現(xiàn)什么情況实苞?

當(dāng)ip2的服務(wù)器掛了的時候豺撑,一致性hash環(huán)大致如下圖:

根據(jù)順時針規(guī)則可知user1,user2的請求會被服務(wù)器ip3進(jìn)行處理,而其它用戶的請求對應(yīng)的處理服務(wù)器不變硬梁,也就是只有之前被ip2處理的一部分用戶的映射關(guān)系被破壞了,并且其負(fù)責(zé)處理的請求被順時針下一個節(jié)點委托處理胞得。

hash過程

下面考慮當(dāng)新增機(jī)器的時候會出現(xiàn)什么情況荧止?

當(dāng)新增一個ip5的服務(wù)器后,一致性hash環(huán)大致如下圖:

根據(jù)順時針規(guī)則可知之前user1的請求應(yīng)該被ip1服務(wù)器處理阶剑,現(xiàn)在被新增的ip5服務(wù)器處理跃巡,其他用戶的請求處理服務(wù)器不變,也就是新增的服務(wù)器順時針最近的服務(wù)器的一部分請求會被新增的服務(wù)器所替代牧愁。

一致性hash環(huán)

一致性hash的特性

單調(diào)性(Monotonicity)素邪,單調(diào)性是指如果已經(jīng)有一些請求通過哈希分派到了相應(yīng)的服務(wù)器進(jìn)行處理,又有新的服務(wù)器加入到系統(tǒng)中時候猪半,應(yīng)保證原有的請求可以被映射到原有的或者新的服務(wù)器中去兔朦,而不會被映射到原來的其它服務(wù)器上去。 這個通過上面新增服務(wù)器ip5可以證明磨确,新增ip5后沽甥,原來被ip1處理的user6現(xiàn)在還是被ip1處理,原來被ip1處理的user5現(xiàn)在被新增的ip5處理乏奥。

分散性(Spread):分布式環(huán)境中摆舟,客戶端請求時候可能不知道所有服務(wù)器的存在,可能只知道其中一部分服務(wù)器邓了,在客戶端看來他看到的部分服務(wù)器會形成一個完整的hash環(huán)恨诱。如果多個客戶端都把部分服務(wù)器作為一個完整hash環(huán),那么可能會導(dǎo)致骗炉,同一個用戶的請求被路由到不同的服務(wù)器進(jìn)行處理照宝。這種情況顯然是應(yīng)該避免的,因為它不能保證同一個用戶的請求落到同一個服務(wù)器句葵。所謂分散性是指上述情況發(fā)生的嚴(yán)重程度硫豆。好的哈希算法應(yīng)盡量避免盡量降低分散性龙巨。 一致性hash具有很低的分散性

平衡性(Balance):平衡性也就是說負(fù)載均衡,是指客戶端hash后的請求應(yīng)該能夠分散到不同的服務(wù)器上去熊响。一致性hash可以做到每個服務(wù)器都進(jìn)行處理請求旨别,但是不能保證每個服務(wù)器處理的請求的數(shù)量大致相同,如下圖

服務(wù)器ip1,ip2,ip3經(jīng)過hash后落到了一致性hash環(huán)上汗茄,從圖中hash值分布可知ip1會負(fù)責(zé)處理大概80%的請求秸弛,而ip2和ip3則只會負(fù)責(zé)處理大概20%的請求,雖然三個機(jī)器都在處理請求洪碳,但是明顯每個機(jī)器的負(fù)載不均衡递览,這樣稱為一致性hash的傾斜,虛擬節(jié)點的出現(xiàn)就是為了解決這個問題瞳腌。

一致性hash環(huán)

當(dāng)服務(wù)器節(jié)點比較少的時候會出現(xiàn)上節(jié)所說的一致性hash傾斜的問題绞铃,一個解決方法是多加機(jī)器,但是加機(jī)器是有成本的嫂侍,那么就加虛擬節(jié)點儿捧,比如上面三個機(jī)器,每個機(jī)器引入1個虛擬節(jié)點后的一致性hash環(huán)的圖如下:

其中ip1-1是ip1的虛擬節(jié)點挑宠,ip2-1是ip2的虛擬節(jié)點菲盾,ip3-1是ip3的虛擬節(jié)點。

一致性hash環(huán)

可知當(dāng)物理機(jī)器數(shù)目為M各淀,虛擬節(jié)點為N的時候懒鉴,實際hash環(huán)上節(jié)點個數(shù)為M*N。比如當(dāng)客戶端計算的hash值處于ip2和ip3或者處于ip2-1和ip3-1之間時候使用ip3服務(wù)器進(jìn)行處理碎浇。

我們使用虛擬節(jié)點后的圖看起來比較均衡临谱,但是如果生成虛擬節(jié)點的算法不夠好很可能會得到下面的環(huán):

可知每個服務(wù)節(jié)點引入1個虛擬節(jié)點后,情況相比沒有引入前均衡性有所改善奴璃,但是并不均衡吴裤。

一致性hash環(huán)

均衡的一致性hash應(yīng)該是如下圖:

均勻一致性hash的目標(biāo)是如果服務(wù)器有N臺,客戶端的hash值有M個溺健,那么每個服務(wù)器應(yīng)該處理大概M/N個用戶的麦牺。也就是每臺服務(wù)器負(fù)載盡量均衡

均衡的一致性hash

引申Q:一致性Hash算法不足以及如何修改。

上面的一致性Hash傾斜以及虛擬節(jié)點鞭缭,以及如果節(jié)點非常龐大的情況

引申Q:數(shù)據(jù)節(jié)點再增加的情況下剖膳,幾十萬幾百萬的節(jié)點,一致性hash算法如何優(yōu)化岭辣?如何在動態(tài)的網(wǎng)絡(luò)拓?fù)渲蟹植即鎯吐酚芍ㄉ埂C總€節(jié)點僅需維護(hù)少量相鄰節(jié)點的信息,并且在節(jié)點加入/退出系統(tǒng)時沦童,僅有相關(guān)的少量節(jié)點參與到拓?fù)涞木S護(hù)中仑濒。所有這一切使得一致性哈希成為第一個實用的DHT算法叹话。

但是一致性哈希的路由算法尚有不足之處。在查詢過程中墩瞳,查詢消息要經(jīng)過O(N)步(O(N)表示與N成正比關(guān)系驼壶,N代表系統(tǒng)內(nèi)的節(jié)點總數(shù))才能到達(dá)被查詢的節(jié)點。不難想象喉酌,當(dāng)系統(tǒng)規(guī)模非常大時热凹,節(jié)點數(shù)量可能超過百萬,這樣的查詢效率顯然難以滿足使用的需要泪电。換個角度來看般妙,即使用戶能夠忍受漫長的時延,查詢過程中產(chǎn)生的大量消息也會給網(wǎng)絡(luò)帶來不必要的負(fù)荷相速。

使用二分查找算法可以將時間復(fù)雜度降低為O(log2n)

2.4碟渺、分庫分表下的主鍵id問題

分庫分表下的hash數(shù)據(jù)到每個表杰妓,會存在兩種情況哀蘑,一種是數(shù)據(jù)庫自增id,一種是分布式全局共用一處生成主鍵id阳仔。

先說數(shù)據(jù)庫自增id攒霹,會導(dǎo)致我們剛才提到的假設(shè)我們現(xiàn)在要聚合查詢怯疤,這樣可能導(dǎo)致會出現(xiàn)512條id一致的數(shù)據(jù)浆洗,這樣前端應(yīng)用就會出現(xiàn)困擾催束。因為id是必須唯一的才能保證我們獲取數(shù)據(jù),那么我們不使用自增id伏社,我們必須通過每條數(shù)據(jù)的某個值能夠確定該行唯一數(shù)據(jù)抠刺,并使512張表的主鍵id都不一致但是有序,為什么需要補充有序摘昌?主鍵id不一致大家都知道防止沖突速妖。是因為我們?nèi)绻褂肐nnodb數(shù)據(jù)存儲引擎的話底層是紅黑樹,那么對于連續(xù)存儲的key值可以有效減少隨機(jī)訪問次數(shù)和IO次數(shù)提升我們查詢的性能聪黎,達(dá)到每次讀取page頁可以預(yù)讀取罕容。

接下來說如何實現(xiàn)分布式全局主鍵id的幾種方式:

Sequence ID

數(shù)據(jù)庫自增長序列或字段,最常見的方式稿饰。由數(shù)據(jù)庫維護(hù)锦秒,數(shù)據(jù)庫唯一。

優(yōu)點:

簡單喉镰,代碼方便旅择,性能可以接受。

數(shù)字ID天然排序侣姆,對分頁或者需要排序的結(jié)果很有幫助生真。

缺點:

不同數(shù)據(jù)庫語法和實現(xiàn)不同沉噩,數(shù)據(jù)庫遷移的時候或多數(shù)據(jù)庫版本支持的時候需要處理。

在單個數(shù)據(jù)庫或讀寫分離或一主多從的情況下柱蟀,只有一個主庫可以生成川蒙。有單點故障的風(fēng)險。

在性能達(dá)不到要求的情況下产弹,比較難于擴(kuò)展派歌。

如果遇見多個系統(tǒng)需要合并或者涉及到數(shù)據(jù)遷移會相當(dāng)痛苦。

分表分庫的時候會有麻煩痰哨。

優(yōu)化方案:

針對主庫單點胶果,如果有多個Master庫,則每個Master庫設(shè)置的起始數(shù)字不一樣斤斧,步長一樣早抠,可以是Master的個數(shù)。

比如:Master1 生成的是 1撬讽,4蕊连,7,10游昼,Master2生成的是2,5,8,11 Master3生成的是 3,6,9,12甘苍。這樣就可以有效生成集群中的唯一ID,也可以大大降低ID生成數(shù)據(jù)庫操作的負(fù)載烘豌。

UUID

常見的方式,128位载庭。可以利用數(shù)據(jù)庫也可以利用程序生成廊佩,一般來說全球唯一囚聚。

優(yōu)點:

簡單,代碼方便标锄。

全球唯一顽铸,在遇見數(shù)據(jù)遷移,系統(tǒng)數(shù)據(jù)合并料皇,或者數(shù)據(jù)庫變更等情況下谓松,可以從容應(yīng)對。

缺點:

沒有排序践剂,無法保證趨勢遞增鬼譬。

UUID往往是使用字符串存儲,查詢的效率比較低舷手。

存儲空間比較大拧簸,如果是海量數(shù)據(jù)庫,就需要考慮存儲量的問題男窟。

傳輸數(shù)據(jù)量大

不可讀盆赤。

優(yōu)化方案:

為了解決UUID不可讀贾富,可以使用UUID to Int64的方法。

GUID

GUID:是微軟對UUID這個標(biāo)準(zhǔn)的實現(xiàn)牺六。UUID還有其它各種實現(xiàn)颤枪,不止GUID一種。優(yōu)缺點同UUID淑际。

COMB

COMB(combine)型是數(shù)據(jù)庫特有的一種設(shè)計思想畏纲,組合的方式,保留UniqueIdentifier的前10個字節(jié)春缕,用后6個字節(jié)表示GUID生成的時間(DateTime)盗胀,將時間信息與UniqueIdentifier組合起來,在保留UniqueIdentifier的唯一性的同時增加了有序性锄贼,以此來提高索引效率票灰。

優(yōu)點:

解決UUID無序的問題,在其主鍵生成方式中提供了Comb算法(combined guid/timestamp)宅荤。保留GUID的10個字節(jié)屑迂,用另6個字節(jié)表示GUID生成的時間(DateTime)。

性能優(yōu)于UUID冯键。

Twitter的snowflake算法

使用41bit作為毫秒數(shù)惹盼,10bit作為機(jī)器的ID(5個bit是數(shù)據(jù)中心,5個bit的機(jī)器ID)惫确,12bit作為毫秒內(nèi)的流水號(意味著每個節(jié)點在每毫秒可以產(chǎn)生 4096 個 ID)手报,最后還有一個符號位,永遠(yuǎn)是0雕薪。snowflake算法可以根據(jù)自身項目的需要進(jìn)行一定的修改昧诱。比如估算未來的數(shù)據(jù)中心個數(shù)晓淀,每個數(shù)據(jù)中心的機(jī)器數(shù)以及統(tǒng)一毫秒可以能的并發(fā)數(shù)來調(diào)整在算法中所需要的bit數(shù)所袁。

優(yōu)點:

不依賴于數(shù)據(jù)庫,靈活方便凶掰,且性能優(yōu)于數(shù)據(jù)庫燥爷。

ID按照時間在單機(jī)上是遞增的。

缺點:

在單機(jī)上是遞增的懦窘,但是由于涉及到分布式環(huán)境前翎,每臺機(jī)器上的時鐘不可能完全同步,也許有時候也會出現(xiàn)不是全局遞增的情況畅涂。

2.5港华、業(yè)務(wù)上漲伸縮性問題Case

201809201216創(chuàng)建

一、背景:

聯(lián)合營銷活動中心負(fù)責(zé)收品,伴隨業(yè)務(wù)線上化的推廣午衰,使用率覆蓋面的增高立宜,原有數(shù)據(jù)庫商品表不滿足業(yè)務(wù)支撐冒萄。

特于2017年雙十一前進(jìn)行了垂直切分商品表分別到4個數(shù)據(jù)庫,通過活動ID和提報人ID取模存放512張表中橙数,迎接報名sku數(shù)量加劇情況尊流。

二、目前現(xiàn)狀與問題:

【目前的數(shù)據(jù)量與未來發(fā)展問題】

目前4個數(shù)據(jù)庫數(shù)據(jù)量統(tǒng)計如下(時間截止20180919):

總數(shù)據(jù)量(包含有效和無效sku) 大約19.6億條數(shù)據(jù),

失效大約SKU 1.37億條數(shù)據(jù)

平均每張表 20億/512張表=3906250條/表,Max表數(shù)據(jù)量達(dá)到519w條/表

每張表目前由于索引和字段一致灯帮,數(shù)據(jù)內(nèi)容一致崖技,采取最高數(shù)據(jù)量表計算其大小為:1131M,每條數(shù)據(jù)大約3k.

Mysql并沒有定義一個大表的定義钟哥,根據(jù)Google團(tuán)隊的一些帖子迎献,我查到達(dá)到2個億數(shù)據(jù)量占用9G的時候查詢是非常煎熬耗時的事情。

通過和DBA溝通腻贰,根據(jù)DBA建議單表范圍保證性能高應(yīng)該每張表大小控制在2G以內(nèi)忿晕,以及數(shù)據(jù)量控制在千萬級以下,防止產(chǎn)生億級大表問題(表結(jié)構(gòu)也會有影響,我們這里的表結(jié)構(gòu)比較簡單可以忽略)银受。

億級大表主要危害有如下:

維護(hù)的不便利践盼,查詢的性能低

計算 sum、count過于集中

由于我們使用的索引目前是(InnoDB)索引及表數(shù)據(jù)都是放在 innodb_buffer_pool里面, 數(shù)據(jù)區(qū)間太大宾巍,讀寫熱點不交集咕幻,造成命中率下降。

表數(shù)據(jù)那么多顶霞,總是冷不丁去查詢時老數(shù)據(jù)肄程,那么這種不頻繁的page就會被擠出innodb_buffer_pool之外,使得之后的SQL查詢會產(chǎn)生磁盤IO选浑,從而導(dǎo)致響應(yīng)速度變慢 蓝厌。

另外根據(jù)索引(B+tree)查詢的問題,索引涉及到聚焦索引也就是我們常用的主鍵索引古徒,左面聚集索引拓提,右面非聚集索引,聚集索引通過B+tree的查詢直接拿到row數(shù)據(jù)隧膘,而非聚集索引只能拿到他的主鍵標(biāo)記代态,然后通過主鍵才能查詢到數(shù)據(jù)(圖片來自百度的谷歌圖片)

B-Tree

【查詢慢SQL問題以及使用情況】:

查詢慢sql

【QPS查詢過高問題】

QPS過高

【CPU超過10%問題】

【磁盤IO次數(shù)】

三、排查與分析

1)數(shù)據(jù)量問題:目前來看按照去年雙十一和今年6.18數(shù)據(jù)量的增速疹吃,今年雙十一數(shù)據(jù)量應(yīng)對沒有問題蹦疑。

2)慢SQL舉例:

可以從圖中看出是由于單次查詢占用行數(shù)接近27w數(shù)據(jù),雖然SQL簡單外加索引機(jī)制在數(shù)據(jù)量問題上依然是個雞肋萨驶。

慢查詢

3)QPS偶爾查詢過高的問題

可以定位到接口排查到調(diào)用數(shù)據(jù)庫之前歉摧,根據(jù)冷熱數(shù)據(jù)進(jìn)行redis緩存處理。

4)CPU超過10%的問題:

根據(jù)前面的排查,應(yīng)該是慢SQL的索引過濾性不強導(dǎo)致的全表掃描外加order by排序叁温,具體還需要長時間觀察與DBA溝通

5)磁盤IO次數(shù)目前還正扯雇欤基本在75%以下。

四券盅、6.18和去年雙十一數(shù)據(jù)統(tǒng)計綜合統(tǒng)計分布圖

五帮哈、方案參考首頁banner圖

banner
?著作權(quán)歸作者所有,轉(zhuǎn)載或內(nèi)容合作請聯(lián)系作者
  • 序言:七十年代末,一起剝皮案震驚了整個濱河市锰镀,隨后出現(xiàn)的幾起案子娘侍,更是在濱河造成了極大的恐慌,老刑警劉巖泳炉,帶你破解...
    沈念sama閱讀 222,681評論 6 517
  • 序言:濱河連續(xù)發(fā)生了三起死亡事件憾筏,死亡現(xiàn)場離奇詭異,居然都是意外死亡花鹅,警方通過查閱死者的電腦和手機(jī)氧腰,發(fā)現(xiàn)死者居然都...
    沈念sama閱讀 95,205評論 3 399
  • 文/潘曉璐 我一進(jìn)店門,熙熙樓的掌柜王于貴愁眉苦臉地迎上來刨肃,“玉大人古拴,你說我怎么就攤上這事≌嬗眩” “怎么了黄痪?”我有些...
    開封第一講書人閱讀 169,421評論 0 362
  • 文/不壞的土叔 我叫張陵,是天一觀的道長盔然。 經(jīng)常有香客問我桅打,道長,這世上最難降的妖魔是什么愈案? 我笑而不...
    開封第一講書人閱讀 60,114評論 1 300
  • 正文 為了忘掉前任挺尾,我火速辦了婚禮,結(jié)果婚禮上站绪,老公的妹妹穿的比我還像新娘遭铺。我一直安慰自己,他們只是感情好崇众,可當(dāng)我...
    茶點故事閱讀 69,116評論 6 398
  • 文/花漫 我一把揭開白布掂僵。 她就那樣靜靜地躺著航厚,像睡著了一般顷歌。 火紅的嫁衣襯著肌膚如雪。 梳的紋絲不亂的頭發(fā)上幔睬,一...
    開封第一講書人閱讀 52,713評論 1 312
  • 那天眯漩,我揣著相機(jī)與錄音,去河邊找鬼。 笑死赦抖,一個胖子當(dāng)著我的面吹牛舱卡,可吹牛的內(nèi)容都是我干的。 我是一名探鬼主播队萤,決...
    沈念sama閱讀 41,170評論 3 422
  • 文/蒼蘭香墨 我猛地睜開眼轮锥,長吁一口氣:“原來是場噩夢啊……” “哼!你這毒婦竟也來了要尔?” 一聲冷哼從身側(cè)響起舍杜,我...
    開封第一講書人閱讀 40,116評論 0 277
  • 序言:老撾萬榮一對情侶失蹤,失蹤者是張志新(化名)和其女友劉穎赵辕,沒想到半個月后既绩,有當(dāng)?shù)厝嗽跇淞掷锇l(fā)現(xiàn)了一具尸體,經(jīng)...
    沈念sama閱讀 46,651評論 1 320
  • 正文 獨居荒郊野嶺守林人離奇死亡还惠,尸身上長有42處帶血的膿包…… 初始之章·張勛 以下內(nèi)容為張勛視角 年9月15日...
    茶點故事閱讀 38,714評論 3 342
  • 正文 我和宋清朗相戀三年饲握,在試婚紗的時候發(fā)現(xiàn)自己被綠了。 大學(xué)時的朋友給我發(fā)了我未婚夫和他白月光在一起吃飯的照片蚕键。...
    茶點故事閱讀 40,865評論 1 353
  • 序言:一個原本活蹦亂跳的男人離奇死亡救欧,死狀恐怖,靈堂內(nèi)的尸體忽然破棺而出锣光,到底是詐尸還是另有隱情颜矿,我是刑警寧澤,帶...
    沈念sama閱讀 36,527評論 5 351
  • 正文 年R本政府宣布嫉晶,位于F島的核電站骑疆,受9級特大地震影響,放射性物質(zhì)發(fā)生泄漏替废。R本人自食惡果不足惜箍铭,卻給世界環(huán)境...
    茶點故事閱讀 42,211評論 3 336
  • 文/蒙蒙 一、第九天 我趴在偏房一處隱蔽的房頂上張望椎镣。 院中可真熱鬧诈火,春花似錦、人聲如沸状答。這莊子的主人今日做“春日...
    開封第一講書人閱讀 32,699評論 0 25
  • 文/蒼蘭香墨 我抬頭看了看天上的太陽惊科。三九已至拍摇,卻和暖如春,著一層夾襖步出監(jiān)牢的瞬間馆截,已是汗流浹背充活。 一陣腳步聲響...
    開封第一講書人閱讀 33,814評論 1 274
  • 我被黑心中介騙來泰國打工蜂莉, 沒想到剛下飛機(jī)就差點兒被人妖公主榨干…… 1. 我叫王不留,地道東北人混卵。 一個月前我還...
    沈念sama閱讀 49,299評論 3 379
  • 正文 我出身青樓映穗,卻偏偏與公主長得像,于是被迫代替她去往敵國和親幕随。 傳聞我的和親對象是個殘疾皇子蚁滋,可洞房花燭夜當(dāng)晚...
    茶點故事閱讀 45,870評論 2 361

推薦閱讀更多精彩內(nèi)容

  • 轉(zhuǎn) # https://www.cnblogs.com/easypass/archive/2010/12/ 08/...
    呂品?閱讀 9,739評論 0 44
  • ORA-00001: 違反唯一約束條件 (.) 錯誤說明:當(dāng)在唯一索引所對應(yīng)的列上鍵入重復(fù)值時,會觸發(fā)此異常赘淮。 O...
    我想起個好名字閱讀 5,343評論 0 9
  • --- layout: post title: "如果有人問你關(guān)系型數(shù)據(jù)庫的原理枢赔,叫他看這篇文章(轉(zhuǎn))" date...
    藍(lán)墜星閱讀 796評論 0 3
  • 今天看到一位朋友寫的mysql筆記總結(jié),覺得寫的很詳細(xì)很用心拥知,這里轉(zhuǎn)載一下踏拜,供大家參考下,也希望大家能關(guān)注他原文地...
    信仰與初衷閱讀 4,737評論 0 30
  • 曾經(jīng)那句傳爛的“好久不見低剔,十分想念”如今想來速梗,好像沒什么意義。不是說我沒有人情味襟齿,也不是說我對朋友不不好姻锁。而是...
    一顆李子閱讀 317評論 0 0