MySQL數(shù)據(jù)庫架構(gòu)優(yōu)化方案

1、 MySQL數(shù)據(jù)庫架構(gòu)優(yōu)化方案目 錄 TOC o 1-3 h z u HYPERLINK l _Toc533532619 一、引入讀寫分離，優(yōu)化初見成效 PAGEREF _Toc533532619 h 3 HYPERLINK l _Toc533532620 二、引入列式存儲，優(yōu)化統(tǒng)計性能 PAGEREF _Toc533532620 h 10 HYPERLINK l _Toc533532621 三、引入動態(tài)調(diào)度，解決統(tǒng)計延遲問題 PAGEREF _Toc533532621 h 16 HYPERLINK l _Toc533532622 四、引入業(yè)務路由，平滑支持業(yè)務擴容 PAGEREF _Toc5

2、33532622 h 21最近有一個業(yè)務庫的負載比往常高了很多，最直觀的印象就是原來的負載最高是100%，現(xiàn)在不是翻了幾倍或者指數(shù)級增長，而是突然翻了100倍，導致業(yè)務后端的數(shù)據(jù)寫入劇增，產(chǎn)生了嚴重的性能阻塞。一、引入讀寫分離，優(yōu)化初見成效這類問題引起了我的興趣和好奇心，經(jīng)過和業(yè)務方溝通了解，這個業(yè)務是記錄回執(zhí)數(shù)據(jù)的，簡單來說就好比你發(fā)送了一條微博，想看看有多少人已讀，有多少人留言等。所以這類場景不存在事務，會有數(shù)據(jù)的密集型寫入，會有明確的統(tǒng)計需求。目前的統(tǒng)計頻率是每7分鐘做一次統(tǒng)計，會有幾類統(tǒng)計場景，目前基本都是全表掃描級別的查詢語句。當前數(shù)據(jù)庫的架構(gòu)很簡單，是一個主從，外加MHA高可用。問

3、題的改進方向是減少主庫的壓力，分別是讀和寫的壓力。寫入的壓力來自于業(yè)務的并發(fā)寫入壓力，而讀的壓力來自于于全表掃描的壓力，對于CPU和IO壓力都很大。這兩個問題的解決還是存在優(yōu)先級，首先統(tǒng)計的SQL導致了系統(tǒng)資源成為瓶頸，結(jié)果原本簡單的Insert也成為了慢日志SQL，相比而言，寫入需求是硬需求，而統(tǒng)計需求是輔助需求，所以在這種場景下和業(yè)務方溝通，快速的響應方式就是把主庫的統(tǒng)計需求轉(zhuǎn)移到從庫端。轉(zhuǎn)移了讀請求的負載，寫入壓力得到了極大緩解，后來也經(jīng)過業(yè)務方的應用層面的優(yōu)化，整體的負載情況就相對樂觀了：主庫的監(jiān)控負載如下，可以看到有一個明顯降低的趨勢，CPU負載從原來的90%以上降到了不到10%。I

4、O的壓力也從原來的近100%降到了25%左右。從庫的監(jiān)控負載如下，可以看到壓力有了明顯的提升。CPU層面的體現(xiàn)不夠明顯，主要的壓力在于IO層面，即全表數(shù)據(jù)的掃描代價極高。這個算是優(yōu)化的第一步改進，在這個基礎上，開始做索引優(yōu)化，但是通過對比，發(fā)現(xiàn)效果很有限。因為從庫端的是統(tǒng)計需求，添加的索引只能從全表掃描降級為全索引掃描，對于系統(tǒng)整體的負載改進卻很有限，所以我們需要對已有的架構(gòu)做一些改進和優(yōu)化。方案1：考慮到資源的成本和使用場景，所以我們暫時把架構(gòu)調(diào)整為如下的方式：即添加兩個數(shù)據(jù)節(jié)點，然后打算啟用中間件的方式來做分布式的架構(gòu)設計。對于從庫，暫時為了節(jié)省成本，就對原來的服務器做了資源擴容，即單機多

5、實例的模式，這樣一來寫入的壓力就可以完全支撐住了。但是這種方式有一個潛在的隱患，那就是從庫的中間件層面來充當數(shù)據(jù)統(tǒng)計的角色，一旦出現(xiàn)性能問題，對于中間件的壓力極大，很可能導致原本的統(tǒng)計任務會阻塞。同時從庫端的資源瓶頸除了磁盤空間外就是IO壓力，目前通過空間擴容解決不了這個硬傷。在和業(yè)務同學進一步溝通后，發(fā)現(xiàn)他們對于這一類表的創(chuàng)建是動態(tài)配置的方式，在目前的中間件方案中很難以落實。而且對于業(yè)務來說，統(tǒng)計需求變得更加不透明了。方案2：一種行之有效的改進方式就是從應用層面來做數(shù)據(jù)路由，比如有10個業(yè)務：業(yè)務1、業(yè)務2在第一個節(jié)點，業(yè)務3、業(yè)務5在第二個節(jié)點等等，按照這種路由的配置方式來映射數(shù)據(jù)源，相對

6、可控，更容易擴展，所以架構(gòu)方式改為了這種：而整個的改進中，最關鍵的一環(huán)是對于統(tǒng)計SQL性能的改進，如果SQL統(tǒng)計性能的改進能夠初見成效，后續(xù)的架構(gòu)改進就會更加輕松。二、引入列式存儲，優(yōu)化統(tǒng)計性能后續(xù)有開始有了業(yè)務的爆發(fā)式增長，使得統(tǒng)計需求的優(yōu)化成為本次優(yōu)化的關鍵所在。原來的主庫讀寫壓力都很大，通過讀寫分離，使得讀節(jié)點的壓力開始激增，而且隨著業(yè)務的擴展，統(tǒng)計查詢的需求越來越多。比如原來是有10個查詢，現(xiàn)在可能變成了30個，這樣一來統(tǒng)計壓力變大，導致系統(tǒng)響應降低，從而導致從庫的延遲也開始變大。最大的時候延遲有3個小時，按照這種情況，統(tǒng)計的意義其實已經(jīng)不大了。對此我做了幾個方面的改進：首先是和業(yè)務方

7、進行了細致的溝通，對于業(yè)務的場景有了一個比較清晰的認識，其實這個業(yè)務場景是蠻適合Redis之類的方案來解決的，但是介于成本和性價比選擇了關系型的MySQL，結(jié)論：暫時保持現(xiàn)狀。對于讀壓力，目前不光支撐不了指數(shù)級壓力，連現(xiàn)狀都讓人擔憂。業(yè)務的每個統(tǒng)計需求涉及5個SQL，要對每個場景做優(yōu)化都需要取舍，最后達到的一個初步效果是字段有5個，索引就有3個，而且不太可控的是一旦某個表的數(shù)據(jù)量太大導致延遲，整個系統(tǒng)的延遲就會變大，從而造成統(tǒng)計需求都整體垮掉，所以添加索引來解決硬統(tǒng)計需求算是心有力而力不足。結(jié)論：索引優(yōu)化效果有限，需要尋求其他可行解決方案。對于寫壓力，后續(xù)可以通過分片的策略來解決，這里的分片策

8、略和我們傳統(tǒng)認為的邏輯不同，這是基于應用層面的分片，應用端來做這個數(shù)據(jù)路由。這樣分片對于業(yè)務的爆發(fā)式增長就很容易擴展了。有了這一層保障之后，業(yè)務的統(tǒng)計需求遷移到從庫，寫壓力就能夠平滑的對接了，目前來看寫壓力的空余空間很大，完全可以支撐指數(shù)級的壓力。結(jié)論：業(yè)務數(shù)據(jù)路由在統(tǒng)計壓力減緩后再開始改進。為了快速改進現(xiàn)狀，我寫了一個腳本自動采集和管理，會定時殺掉超時查詢的會話。但是延遲還是存在，查詢依舊是慢，很難想象在指數(shù)級壓力的情況下，這個延遲會有多大。在做了大量的對比測試之后，按照單表3500萬的數(shù)據(jù)量，8張同樣數(shù)據(jù)量的表，5條統(tǒng)計SQL，做完統(tǒng)計大約需要1718分鐘左右，平均每個表需要大約2分多鐘。

9、因為不是沒有事務關聯(lián)，所以這個場景的延遲根據(jù)業(yè)務場景和技術實現(xiàn)來說是肯定存在的，我們的改進方法是提高統(tǒng)計的查詢效率，同時保證系統(tǒng)的壓力在可控范圍內(nèi)。一種行之有效的方式就是借助于數(shù)據(jù)倉庫方案，MySQL原生不支持數(shù)據(jù)庫倉庫，但是有第三方的解決方案：一類是ColumStore，是在InfiniDB的基礎上改造的；一類是Infobright，除此之外還有其他大型的解決方案，比如Greenplum的MPP方案，ColumnStore的方案有點類似于這種MPP方案，需要的是分布式節(jié)點，所以在資源和架構(gòu)上Infobright更加輕量一些。我們的表結(jié)構(gòu)很簡單，字段類型也是基本類型，而且在團隊內(nèi)部也有大量的實

10、踐經(jīng)驗。改進之后的整體架構(gòu)如下，原生的主從架構(gòu)不受影響：需要在此基礎上擴展一個數(shù)據(jù)倉庫節(jié)點，數(shù)據(jù)量可以根據(jù)需要繼續(xù)擴容。表結(jié)構(gòu)如下：CREATE TABLE receipt_12149_428 ( id int(11) NOT NULL COMMENT 自增主鍵, userid int(11) NOT NULL DEFAULT 0 COMMENT 用戶ID, action int(11) NOT NULL DEFAULT 0 COMMENT 動作, readtimes int(11) NOT NULL DEFAULT 0 COMMENT 閱讀次數(shù), create_time datetime N

11、OT NULL COMMENT 創(chuàng)建時間) ;導出的語句類似于：select *from $tab_name where create_time between xxx and xxxx into outfile /data/dump_data/$tab_name.csv FIELDS TERMINATED BY ENCLOSED BY ;Infobright社區(qū)版是不支持DDL和DML的，后期Infobright官方宣布：不再發(fā)布ICE社區(qū)版，將專注于IEE的開發(fā)，所以后續(xù)的支持力度其實就很有限了。對于我們目前的需求來說是游刃有余。來簡單感受下Infobright的實力：select cou

12、nt( id) from testxxx where id2000;+| count( id) |+| 727686205 |+1 row in set (6.20 sec)select count( id) from testxxxx where idselect count( distinct id) from testxxxx where id2000;+| count( distinct id) |+| 1999 |+1 row in set (10.20 sec)所以對于幾千萬的表來說，這都不是事兒。我把3500萬的數(shù)據(jù)導入到Infobright里面，5條查詢語句總共的執(zhí)行時間維持在

13、14秒，相比原來的2分鐘多已經(jīng)改進很大了。我跑了下批量的查詢，原本要18分鐘，現(xiàn)在只需要不到3分鐘。三、引入動態(tài)調(diào)度，解決統(tǒng)計延遲問題通過引入Infobright方案對已有的統(tǒng)計需求可以做到完美支持，但是隨之而來的一個難點就是對于數(shù)據(jù)的流轉(zhuǎn)如何平滑支持。我們可以設定流轉(zhuǎn)頻率，比如10分鐘等或者半個小時，但是目前來看，這個是需要額外的腳本或工具來做的。在具體落地的過程中，發(fā)現(xiàn)有一大堆的事情需要提前搞定。其一：比如第一個頭疼的問題就是全量的同步，第一次同步肯定是全量的，這么多的數(shù)據(jù)怎么同步到Infobright里面。第二個問題，也是更為關鍵的，那就是同步策略是怎么設定的，是否可以支持的更加靈活。第

14、三個問題是基于現(xiàn)有的增量同步方案，需要在時間字段上添加索引。對于線上的操作而言又是一個巨大的挑戰(zhàn)。其二：從目前的業(yè)務需求來說，最多能夠允許一個小時的統(tǒng)計延遲，如果后期要做大量的運營活動，需要更精確的數(shù)據(jù)支持，要得到半個小時的統(tǒng)計數(shù)據(jù)，按照現(xiàn)有的方案是否能夠支持。這兩個主要的問題，任何一個解決不了，數(shù)據(jù)流轉(zhuǎn)能夠落地都是難題，這個問題留給我的時間只有一天。所以我準備把前期的準備和測試做得扎實一些，后期接入的時候就會順暢得多。部分腳本實現(xiàn)如下：腳本的輸入?yún)?shù)有兩個，一個是起始時間，一個是截止時間。第一次全量同步的時候，可以把起始時間給的早一些，這樣截止時間是固定的，邏輯上就是全量的。另外全量同步的時

15、候一定要確保主從延遲已經(jīng)最低或者暫時停掉查詢業(yè)務，使得數(shù)據(jù)全量抽取更加順利。所以需要對上述腳本再做一層保證，通過計算當前時間和上一次執(zhí)行的時間來得到任務可執(zhí)行的時間。這樣腳本就不需要參數(shù)了，這是一個動態(tài)調(diào)度的迭代過程?？紤]到每天落盤的數(shù)據(jù)量大概在10G左右，日志量在30G左右，所以考慮先使用客戶端導入Infobright的方式來操作。從實踐來看，涉及的表有600多個，我先導出了一個列表，按照數(shù)據(jù)量來排序，這樣小表就可以快速導入，大表放在最后，整個數(shù)據(jù)量有150G左右，通過網(wǎng)絡傳輸導入Infobright，從導出到導入完成，這個過程大概需要1個小時。而導入數(shù)據(jù)到Infobright之后的性能提升也是極為明顯的。原來的一組查詢持續(xù)時間在半個小時，現(xiàn)在在70秒鐘即可完成。對于業(yè)務的體驗來說大大提高。完成了第一次同步之后，后續(xù)的同步都可以根據(jù)實際的情況來靈活控制。所以數(shù)據(jù)增量同步暫時是“手動擋”控制。從整個數(shù)據(jù)架構(gòu)分離之后的效果來看，從庫的壓力大大降低，而效率也大大提高。四、引入業(yè)務路由，平滑支持業(yè)務擴容前面算是對現(xiàn)狀做到了最大程度的優(yōu)化，但是還有一個問題，目前的架構(gòu)暫時能夠支撐密集型數(shù)據(jù)寫入，但