大數(shù)據(jù)量下mysql實(shí)踐

大數(shù)據(jù)mysql實(shí)踐楊青2015.7.18內(nèi)容如何保證數(shù)據(jù)庫可用性？

解決思路--數(shù)據(jù)冗余（復(fù)制）如何提高數(shù)據(jù)庫讀性能？三種方式提高讀性能如何保證數(shù)據(jù)一致性？主從一致性、數(shù)據(jù)庫與緩存一致性數(shù)據(jù)庫如何擴(kuò)展？

n庫變2n庫、增加字段、數(shù)據(jù)庫遷移數(shù)據(jù)庫拆庫四中場(chǎng)景單key、1對(duì)多、多對(duì)多、單key分庫后業(yè)務(wù)的查詢?nèi)绾伪ＷC數(shù)據(jù)庫可用性？可用性，數(shù)據(jù)冗余（復(fù)制）1，保證“讀”高可用的方法：復(fù)制從庫，冗余數(shù)據(jù)，如圖：帶來的問題：主從數(shù)據(jù)不一致，主從延遲解決方案：a)忽略錯(cuò)誤后，繼續(xù)同步b)重新做主從，完全同步

2，保證“寫”高可用方法：雙主模式，即復(fù)制主庫，冗余數(shù)據(jù)，如圖：帶來的問題：雙主同步key沖突，引發(fā)數(shù)據(jù)不一致解決方案：a）方案一：由業(yè)務(wù)層保證key在兩個(gè)主上不沖突b）方案二：數(shù)據(jù)庫自增id奇偶數(shù)3，雙主當(dāng)主從用，不做讀寫分離，在主庫掛掉的情況下，從庫當(dāng)主庫頂上，如圖：優(yōu)點(diǎn)：讀寫都到主，解決了一致性問題；雙主當(dāng)主從用，解決了可用性問題帶來的問題：如何提高讀性能？

如何提高數(shù)據(jù)庫讀性能？一，建立索引建立非常多的索引，副作用是：a）降低了寫性能b）內(nèi)存空間有限，索引占用大量內(nèi)存，其內(nèi)存中數(shù)據(jù)減少，查詢數(shù)據(jù)命中率降低，查詢時(shí)掃描磁盤，IO增多；但是否想到，不同的庫可以建立不同的索引呢？如圖不同的庫可以建立不同索引，主庫只提供寫，不建立索引二，增加從庫這種方法會(huì)引發(fā)主從不一致問題，從庫越多，主從時(shí)延越長，數(shù)據(jù)不一致問題越嚴(yán)重解決辦法：如上所述如何提高數(shù)據(jù)庫讀性能？三，增加緩存?zhèn)鹘y(tǒng)緩存的用法是：a）寫請(qǐng)求時(shí)，先淘汰緩存，再寫數(shù)據(jù)庫；b）讀請(qǐng)求時(shí)，先讀緩存，緩存命中則返回，失敗則讀數(shù)據(jù)庫并將數(shù)據(jù)入緩存；

帶來的問題：a）所有業(yè)務(wù)層都要關(guān)注緩存，“主從”數(shù)據(jù)不一致，造成緩存臟數(shù)據(jù)問題；b）數(shù)據(jù)復(fù)制引發(fā)一致性問題，由于主從延時(shí)的存在，可能引發(fā)緩存與數(shù)據(jù)庫數(shù)據(jù)不一致；

解決思路：a）增加服務(wù)端緩存，如圖好處是：1）引入服務(wù)層屏蔽“數(shù)據(jù)庫+緩存”2）不做讀寫分離，讀寫都到主的模式，不會(huì)引發(fā)不一致b）不做讀寫分離，讀寫都到主的模式，不會(huì)引發(fā)不一致如何保證數(shù)據(jù)一致性？一，主從不一致解決方案方案一：引入中間件，如圖中間件將寫數(shù)據(jù)路由到主，在一定時(shí)間范圍內(nèi)（主從同步完成），該數(shù)據(jù)上的讀也路由到主；方案二：讀寫都到主，不做讀寫分離，不會(huì)不一致，如圖二，數(shù)據(jù)庫與緩存不一致解決方案：兩次淘汰異常數(shù)據(jù)的讀寫，導(dǎo)致舊數(shù)據(jù)寫入緩存，一次淘汰不夠，要進(jìn)行二次淘汰a）寫請(qǐng)求時(shí)，先淘汰緩存，再寫數(shù)據(jù)庫，額外增加一個(gè)定時(shí)機(jī)制，一定時(shí)間（主從同步完成）后再次淘汰；b）讀請(qǐng)求時(shí)，先讀緩存，命中返回，失敗則讀數(shù)據(jù)庫并將數(shù)據(jù)入緩存（此時(shí)可能舊數(shù)據(jù)入緩存，但會(huì)被二次淘汰淘汰掉，最終不會(huì)引發(fā)不一致）數(shù)據(jù)庫擴(kuò)展--數(shù)據(jù)庫n庫變2n庫？需求：原來N個(gè)庫，現(xiàn)在要擴(kuò)充為2N個(gè)庫，希望不影響服務(wù)，快速完成數(shù)據(jù)庫擴(kuò)展最開始，分為2個(gè)庫，分別0庫和1庫，均采用“雙主當(dāng)主從用”的模式保證可用性，如圖：接下來，將從庫提升主庫，并修改服務(wù)端配置，快速完成數(shù)據(jù)庫擴(kuò)庫，由于是2擴(kuò)4，不會(huì)存在數(shù)據(jù)遷移，原來的0庫變?yōu)?庫+2庫，原來的1庫變?yōu)?庫和3庫，如圖：最后，解除舊的雙主同步（0庫和2庫不會(huì)數(shù)據(jù)沖突），為了保證可用性增加新的雙主同步，并刪除掉多余的數(shù)據(jù)這種方案可以秒級(jí)完成N庫到2N庫的擴(kuò)容。存在的問題：只能完成N庫擴(kuò)2N庫的擴(kuò)容（不需要數(shù)據(jù)遷移），非通用擴(kuò)容方案（例如3庫擴(kuò)4庫就無法完成）擴(kuò)展后均采用“雙主當(dāng)主從用”的模式，如圖：數(shù)據(jù)庫擴(kuò)展--增加字段、數(shù)據(jù)遷移、增加索引一，追日志方案a）記錄寫日志b）倒庫c）倒庫完畢d）追日志e）追日志完畢+數(shù)據(jù)校驗(yàn)f）切庫二，雙寫方案a）服務(wù)雙寫b）倒庫c）倒庫完畢+數(shù)據(jù)校驗(yàn)d）切庫mysql5.6版本Innodb存儲(chǔ)引擎實(shí)現(xiàn)OnlineDDL原理：將DML操作日志寫到一個(gè)緩存中，待完成索引創(chuàng)建后再將數(shù)據(jù)寫到數(shù)據(jù)表上，達(dá)到數(shù)據(jù)的一致性Facebook的OnlineSchemaChange原理：1,創(chuàng)建需要實(shí)行alter操作的臨時(shí)表，然后再臨時(shí)表中更改表結(jié)構(gòu)；2，在源表上創(chuàng)建觸發(fā)器(3個(gè))分別對(duì)應(yīng)insert、update、delete操作；3，從源表拷貝數(shù)據(jù)導(dǎo)臨時(shí)表，拷貝過程中對(duì)源表進(jìn)行的寫操作都會(huì)更新到新建的臨時(shí)表；4，rename源表，把臨時(shí)表改為源表，將源表刪除，觸發(fā)器刪除數(shù)據(jù)庫拆庫

四類場(chǎng)景覆蓋99%拆庫業(yè)務(wù)a）“單key”--用戶庫如何拆分：user(uid,xxxx)b）“1對(duì)多”--帖子庫如何拆分：tiezi(tid,uid,xxxx)c）“多對(duì)多”--好友庫如何拆分：friend(uid,friend_uid,xxxx)d）“多key”--訂單庫如何拆分：order(oid,buyer_id,seller_id,xxxx)1，“單key”--用戶庫如何拆分？用戶庫，10億數(shù)據(jù)量user(uid,uname,passwd,age,sex,create_time);業(yè)務(wù)需求如下:a）1%登錄請(qǐng)求=>whereuname=xxxandpasswd=xxxb）99%查詢請(qǐng)求=>whereuid=xxx結(jié)論：“單key”使用“單key”拆庫2，“1對(duì)多”--帖子庫如何拆分？帖子庫，15億數(shù)據(jù)量tiezi(tid,uid,title,content,time);業(yè)務(wù)需求如下:a）查詢帖子詳情（90%請(qǐng)求）select*fromtieziwheretid=xxxb）查詢用戶所有發(fā)帖（10%請(qǐng)求）select*fromtieziwhereuid=xxx結(jié)論：“1對(duì)多”使用“1”分庫，如帖子庫1個(gè)uid對(duì)應(yīng)多個(gè)tid，使用uid分庫，tid生成時(shí)加入分庫標(biāo)記數(shù)據(jù)庫拆庫3，“多對(duì)多”--好友庫如何拆分？好友庫，1億數(shù)據(jù)量friend(uid,friend_uid,nick,memo,xxxx);業(yè)務(wù)需求如下:a）查詢我的好友（50%請(qǐng)求）selectfriend_uidfromfriendwhereuid=xxxxb）查詢加我為好友的用戶（50%請(qǐng)求）selectuidfromfriendwherefriend_uid=xxxx結(jié)論：“多對(duì)多”，使用數(shù)據(jù)冗余方案，多份數(shù)據(jù)使用多種分庫手段4，“多key”--帖子庫如何拆分訂單庫，10億數(shù)據(jù)量order(oid,buyer_id,seller_id,order_info,xxxx);業(yè)務(wù)需求如下a）查詢訂單信息（80%請(qǐng)求）select*fromorderwhereoid=xxxxb）查詢我買的東東（19%請(qǐng)求）select*fromorderwherebuyer_id=xxxxc）查詢我賣出的東東（1%請(qǐng)求）select*fromorderwhereseller_id=xxxx結(jié)論：“多key”一般有兩種方案a）方案一，使用2和3綜合的方案b）方案二，1%的請(qǐng)求采用多庫查詢分庫后業(yè)務(wù)查詢分庫后出現(xiàn)的問題：單庫時(shí)mysql的sql功能不再支持了1，海量數(shù)據(jù)下，禁止以下方法a）各種聯(lián)合查詢b）子查詢c）觸發(fā)器d）用戶自定義函數(shù)e）“事務(wù)”都用的很少2，分庫后，in查詢?nèi)绾尾?？用戶庫如何進(jìn)行uid的in查詢user(uid,uname,passwd,age,sex,photo,create_time,...);partitionkey：uid查詢需求：in查詢：whereuidin(1,2,3,4,5,6)解決方案：服務(wù)做uid處理方案一：直接分發(fā)方案二：拼裝成不同sql，定位不同的庫分庫后業(yè)務(wù)查詢3，分庫后，非partitionkey的查詢?nèi)绾尾?？方案一：業(yè)務(wù)方不關(guān)心數(shù)據(jù)來自哪個(gè)庫，可以只定位一個(gè)庫例如：有頭像的用戶查詢，如圖:方案二：結(jié)果集只有一條數(shù)據(jù)，業(yè)務(wù)層做分發(fā)，只有一條記錄返回就返回結(jié)果例如：用戶登錄時(shí)，使用username和passwd的查詢?cè)儯鐖D:4，分庫后，夸庫分頁如何查？問題：orderbyxxxoffsetxxxlimitxxxa）按時(shí)間排序b）每頁100條記錄c）取第100頁的記錄單機(jī)方案orderbytimeoffset10000limit100分庫后傳統(tǒng)解決方案，查詢改寫+內(nèi)存排序a）orderbytimeoffset0limit10000+100b）對(duì)20200條記錄進(jìn)行排序c）返回第10000至10100條記錄分庫后業(yè)務(wù)查詢優(yōu)化方案一：a）技術(shù)上，引入特殊id，作為查詢條件b）業(yè)務(wù)上，盡量禁止跨頁查詢單庫情況a）第一頁，直接查b）得到第一頁的max(id)=123（一般是最后一條記錄）c）第二頁，帶上id>123查詢：WHEREid>123LIMIT100多庫情況a）將whereid>888limit100分發(fā)b）將300條結(jié)果排序c）返回前100條優(yōu)點(diǎn)：避免了全局排序，只對(duì)小量記錄進(jìn)行排序分庫后業(yè)務(wù)查詢優(yōu)化方案二：模糊查詢a）業(yè)務(wù)上：禁止查詢XX頁之后的數(shù)據(jù)b）業(yè)務(wù)上：允許模糊返回=>第100頁數(shù)據(jù)的精確性真這么重要么？優(yōu)化方案三：查詢改寫與兩段查詢需求：orderbyxoffset10000limit4;如何在分庫下實(shí)現(xiàn)（假設(shè)分3庫）步驟一、查詢改寫：orderbyxoffset3333limit4[4,7,9,10]<=1庫返回[3,5,6,7]<=2庫返回[6,8,9,11]<=3庫返回步驟二、找到步驟一返回的min和max，即3和11步驟三、通過min和max二次查詢：orderbyxwherexbetween3and11[3,4,7,9,10]<=1庫返回，4在1庫offset是3333，于是3在1庫