前 言
目前,出于對(duì)數(shù)據(jù)庫(kù)產(chǎn)品高安全和高可用的要求,銀行業(yè)在現(xiàn)有核心業(yè)務(wù)系統(tǒng)中選用的一般為國(guó)際大型廠商的成熟產(chǎn)品,如IBM的DB2、甲骨文的Oracle、微軟的SQL Server等。但隨著業(yè)務(wù)的不斷發(fā)展,銀行業(yè)對(duì)數(shù)據(jù)庫(kù)產(chǎn)品的需求已經(jīng)逐漸多樣化:一方面要能滿足業(yè)務(wù)系統(tǒng)的基本需求,另一方面對(duì)于數(shù)據(jù)安全、自主掌控的要求也越來(lái)越高。鑒于此,不少銀行業(yè)已經(jīng)開始了自己的轉(zhuǎn)型嘗試,并取得了一定的成果。
MySQL作為當(dāng)前最熱門的開源數(shù)據(jù)庫(kù),已被互聯(lián)網(wǎng)公司廣泛應(yīng)用。基于對(duì)數(shù)據(jù)庫(kù)安全可控的考慮,銀行業(yè)也正在進(jìn)行較大規(guī)模的推廣,用于替代傳統(tǒng)數(shù)據(jù)庫(kù)產(chǎn)品。
我行在替換和使用、改造的過(guò)程中遇到了不少問(wèn)題,下面總結(jié)了最常被開發(fā)、運(yùn)維問(wèn)到的問(wèn)題,我們做了最精簡(jiǎn)的解答,希望對(duì)大家能有所幫助。
01
為什么首選MySQL數(shù)據(jù)庫(kù)作為替換Oracle的數(shù)據(jù)庫(kù)產(chǎn)品?
近年來(lái)MySQL的蓬勃發(fā)展及其在互聯(lián)網(wǎng)行業(yè)的豐富實(shí)踐,使得其替換商業(yè)數(shù)據(jù)庫(kù)成為了可能,尤其是阿里等行業(yè)巨頭已成功地使用MySQL替換Oracle并支撐了龐大的業(yè)務(wù)。MySQL作為世界上最流行的數(shù)據(jù)庫(kù)還具備如下優(yōu)勢(shì):
1)豐富的文檔資料,大量的從業(yè)人員和蓬勃的生態(tài)都使MySQL成為首選。
2)支持行鎖和事務(wù)的Innodb存儲(chǔ)引擎在官方的強(qiáng)力支持下越來(lái)越強(qiáng)大,對(duì)于高并發(fā)下OLTP優(yōu)勢(shì)明顯。
3)靈活的邏輯復(fù)制搭建主從可以在架構(gòu)設(shè)計(jì)上有更多的空間。組復(fù)制(MGR)技術(shù)可以保證數(shù)據(jù)的強(qiáng)一致,打通了最后一道技術(shù)壁壘,滿足了金融等領(lǐng)域?qū)?shù)據(jù)強(qiáng)一致性的要求。
4)當(dāng)單機(jī)成為性能瓶頸的時(shí)候,豐富的開源中間件搭配MySQL做數(shù)據(jù)拆分實(shí)現(xiàn)了分布式數(shù)據(jù)庫(kù)改造方案,可以提供更高的業(yè)務(wù)需求。
02
MySQL對(duì)比Oracle有哪些語(yǔ)法、數(shù)據(jù)類型、對(duì)象類型兼容性問(wèn)題?
MySQL支持 Oracle 絕大部分的基本 SQL 語(yǔ)法及數(shù)據(jù)類型、對(duì)象類型。部分不支持的如下:
1)數(shù)據(jù)類型方面MySQL不支持序列、自定義類型、XML數(shù)據(jù)類型及偽列。
2)MySQL不支持對(duì)象包括物化視圖、包管理及同義詞。
3)索引方面MySQL不支持位圖索引、位圖連接索引、函數(shù)索引、在線重建索引。
4)觸發(fā)器方面MySQL不支持DDL事件觸發(fā)器、系統(tǒng)事件觸發(fā)器、時(shí)間觸發(fā)器。
5)高級(jí)功能方面MySQL不支持外部數(shù)據(jù)庫(kù)鏈接、面向?qū)ο蟆㈤W回查詢等。
6)函數(shù)方面MySQL不支持COSH(x)、CHR(n1)、LAG()、RANK()等函數(shù)。
03
采用中間件+MySQL開發(fā)和直連MySQL開發(fā)有哪些限制?
在應(yīng)用連接池配置部分與直連MySQL相同,對(duì)應(yīng)用而言引入中間件屏蔽了后端拆分細(xì)節(jié),可理解為中間件即數(shù)據(jù)庫(kù),采用中間件方式的具體限制如下:
1)存在部分語(yǔ)法限制,包括DDL、DML及管理語(yǔ)句,如不?持create table ... like ...、INSERT... SELECT...等語(yǔ)法。
2)性能方面,需要基于相同分片規(guī)則的分片鍵進(jìn)行查詢與關(guān)聯(lián)查詢。
3)不支持外鍵關(guān)聯(lián)、臨時(shí)表、觸發(fā)器、分布式級(jí)別存儲(chǔ)過(guò)程和自定義函數(shù)等。
04
如何將數(shù)據(jù)從Oracle遷移到MySQL?
從Oracle遷移到MySQL屬于異構(gòu)遷移,需要依賴第三方開源工具或者商業(yè)工具進(jìn)行遷移。數(shù)據(jù)量大小和業(yè)務(wù)停機(jī)時(shí)間決定了遷移的方式。
1)當(dāng)數(shù)據(jù)量很小,停機(jī)時(shí)間完全可以操作完成的時(shí)候,可以采用直接文本導(dǎo)出導(dǎo)入操作,這種方式簡(jiǎn)單并且高效。
2)當(dāng)停機(jī)時(shí)間要求特別短,此時(shí)我們將采用OGG(Oracle Golden Gate)或者類似工具進(jìn)行全量+增量保持Oracle到MySQL實(shí)時(shí)同步。等到業(yè)務(wù)停止準(zhǔn)備切換時(shí),停掉Oracle到MySQL同步。驗(yàn)證數(shù)據(jù)無(wú)誤后,業(yè)務(wù)代碼對(duì)接到MySQL數(shù)據(jù)庫(kù)完成數(shù)據(jù)遷移過(guò)程。
05
為什么MySQL不建議建立存儲(chǔ)過(guò)程、觸發(fā)器、自定義函數(shù)等對(duì)象?
對(duì)于數(shù)據(jù)庫(kù)的使用,我們強(qiáng)烈建議只參與數(shù)據(jù)存取,不參與業(yè)務(wù)邏輯。具體原因如下:
1)將業(yè)務(wù)邏輯的實(shí)現(xiàn)完全置于代碼中,易于集中維護(hù)和調(diào)試。
2)觸發(fā)器的嵌套,如果再涉及多個(gè)存儲(chǔ)過(guò)程、事務(wù)控制等時(shí),很容易出現(xiàn)死鎖。
3)基于中間件實(shí)現(xiàn)的分布式數(shù)據(jù)庫(kù)對(duì)存儲(chǔ)過(guò)程、觸發(fā)器、自定義函數(shù)支持有限。
4)對(duì)DB保護(hù),減少數(shù)據(jù)庫(kù)的壓力。
5)對(duì)于異構(gòu)數(shù)據(jù)庫(kù)可移植性較差,增加開發(fā)成本。
06
什么情況下使用分庫(kù)分表?數(shù)據(jù)庫(kù)拆分方式有哪些?如何選擇拆分方式?
對(duì)于MySQL而言,當(dāng)數(shù)據(jù)量過(guò)大、QPS或TPS過(guò)高,或者單機(jī)的硬件資源(CPU、磁盤、內(nèi)存、IO等)出現(xiàn)性能瓶頸,通過(guò)單方面增加硬件資源已經(jīng)無(wú)法滿足要求時(shí)需要考慮做分庫(kù)分表。一般情況下單表大小超過(guò)2000萬(wàn),數(shù)據(jù)庫(kù)大小超過(guò)100G需要考慮,具體根據(jù)實(shí)際應(yīng)用場(chǎng)景而定。
數(shù)據(jù)庫(kù)拆分方式分為水平拆分和垂直拆分。
垂直拆分是指按照功能模塊、關(guān)系密切度拆分到不同的表或者庫(kù),垂直拆分相對(duì)簡(jiǎn)單,不同的業(yè)務(wù)訪問(wèn)自己的庫(kù)和表就可以實(shí)現(xiàn)。
水平拆分是指把表的數(shù)據(jù)按照某種規(guī)則進(jìn)行劃分,存儲(chǔ)到結(jié)構(gòu)相同的不同表中,水平拆分相對(duì)復(fù)雜一些,需要把一張表的數(shù)據(jù)做物理拆分,拆分的時(shí)候要根據(jù)數(shù)據(jù)的增長(zhǎng)預(yù)測(cè)拆分的粒度,并且也要盡可能的保證數(shù)據(jù)和負(fù)載的平均。
在選擇拆分方式的時(shí)候,要評(píng)估出現(xiàn)瓶頸的原因,如果是因?yàn)閿?shù)據(jù)庫(kù)表過(guò)多導(dǎo)致數(shù)據(jù)量過(guò)大,并且數(shù)據(jù)庫(kù)中業(yè)務(wù)邏輯清晰,那么就選擇垂直拆分。如果是單表的數(shù)據(jù)量比較大,就應(yīng)該選擇水平拆分。
07
有哪些MySQL管理工具?
常用的MySQL管理工具有:Navicat for MySQL、SQLyog、PhpMyAdmin、MySQLWorkBench等。
開源工具推薦采用MySQLWorkBench,付費(fèi)推薦采用:SQLyog。
08
修改MySQL大表的風(fēng)險(xiǎn)和性能如何?
MySQL大表修改會(huì)產(chǎn)生死鎖,所以一般情況下會(huì)采用以下兩種方式進(jìn)行修改:
1)在業(yè)務(wù)低峰期停止服務(wù)后直接ALTER修改。此方式的安全性較高,但是每次修改都需要停止業(yè)務(wù),對(duì)于某些核心業(yè)務(wù)系統(tǒng)是不可接受的。而且對(duì)于比較大的表,停止業(yè)務(wù)時(shí)間也較長(zhǎng),成本會(huì)較高。
2) 采用第三方工具pt-online-schema-change。該工具可以直接進(jìn)行修改,其操作原理是:首先對(duì)表加鎖(表此時(shí)只讀),然后復(fù)制原表物理結(jié)構(gòu)創(chuàng)建一個(gè)中間表,接下來(lái)修改中間表的物理結(jié)構(gòu),隨后把原表數(shù)據(jù)導(dǎo)入中間表中,數(shù)據(jù)同步完后,鎖定中間表,并刪除原表,接下來(lái)rename中間表為原表,最后刷新數(shù)據(jù)字典并釋放鎖。該工具修改過(guò)程中所修改的表必須有主鍵,且不能是聯(lián)合主鍵。同時(shí)也存在一定的風(fēng)險(xiǎn),該工具在做change修改的時(shí)候不會(huì)提示錯(cuò)誤,但是結(jié)果會(huì)發(fā)現(xiàn)數(shù)據(jù)會(huì)有部分丟失。在性能方面也有一定的瓶頸:如在并發(fā)比較高的情況下會(huì)對(duì)業(yè)務(wù)的訪問(wèn)速度有一定影響。
基于分布式數(shù)據(jù)庫(kù)中間件產(chǎn)品,暫不支持pt-osc、gh-ost第三方工具,建議使用MySQL5.7以上版本,依靠原生Online DDL進(jìn)行表結(jié)構(gòu)變更。
09
MySQL分區(qū)表使用原則是什么?
MySQL實(shí)現(xiàn)分區(qū)表的方式是對(duì)底層表的封裝,意味著索引也是按照分區(qū)的子表定義的,沒有全局索引。這和Oracle不同,在Oracle中可以更加靈活地定義索引和表是否進(jìn)行分區(qū)。
MySQL分區(qū)表在使用的時(shí)候常規(guī)的CRUD操作以及返回結(jié)果和普通表沒有任何區(qū)別。MySQL的分區(qū)表的類型主要包括RANGE、LIST、HASH、KEY四種,不支持自建分區(qū)。
某些特定場(chǎng)景下可以考慮采用分區(qū)表,如歷史數(shù)據(jù)有明確的分區(qū)范圍、訪問(wèn)不跨分區(qū)、極少的變更操作、查詢語(yǔ)句邏輯簡(jiǎn)單、無(wú)性能瓶頸等。
對(duì)于Oracle這些商業(yè)數(shù)據(jù)庫(kù),由于商業(yè)授權(quán)導(dǎo)致橫向擴(kuò)展成本較高,且分區(qū)表功能穩(wěn)定,因此可通過(guò)硬件擴(kuò)展和分區(qū)來(lái)承擔(dān)大數(shù)據(jù)量帶來(lái)的負(fù)載。而對(duì)于MySQL開源數(shù)據(jù)庫(kù),企業(yè)有資源有能力將很多需求遷移到數(shù)據(jù)庫(kù)外通過(guò)代碼邏輯或者其它替代方式來(lái)實(shí)現(xiàn),因此更追求MySQL使用過(guò)程中的簡(jiǎn)單、穩(wěn)定和可靠,且通過(guò)增加服務(wù)器以及分庫(kù)分表更能處理由于數(shù)據(jù)量爆炸式增長(zhǎng)所帶來(lái)的性能問(wèn)題。因此不建議大量使用MySQL分區(qū)表,尤其是在重要的業(yè)務(wù)上。
10
如何做MySQL架構(gòu)選型?
可以參考如下表格:
系統(tǒng)級(jí)別高 | 系統(tǒng)級(jí)別中 | 系統(tǒng)級(jí)別低 | |
---|---|---|---|
數(shù)據(jù)量小 | 集中式+三中心架構(gòu) | 集中式+兩中心架構(gòu) | 集中式+單中心架構(gòu) |
數(shù)據(jù)量大 | 分布式+三中心架構(gòu) | 分布式+兩中心架構(gòu) | 分布式+單中心架構(gòu) |
注:
數(shù)據(jù)量大小依據(jù):以單表2000萬(wàn)以內(nèi),單庫(kù)100G以內(nèi)劃分,具體可以根據(jù)實(shí)際情況而定。
集中式:即直連MySQL單機(jī)數(shù)據(jù)庫(kù)。
分布式:通過(guò)中間件+MySQL做數(shù)據(jù)拆分。
三中心架構(gòu):同城雙中心+異地中心。
兩中心架構(gòu):本地單中心+異地中心。
單中心架構(gòu):本地單中心。
11
MySQL如何保障數(shù)據(jù)一致性?
單機(jī):
通過(guò)雙1參數(shù)設(shè)置,強(qiáng)制日志寫入磁盤后提交事務(wù)。
復(fù)制:
1)主從:主從通過(guò)增強(qiáng)半同步實(shí)現(xiàn):主庫(kù)提交事務(wù),從庫(kù)需要接收到主庫(kù)的日志并寫入relay log,返回給主庫(kù)ack消息后,主庫(kù)才可以提交。基于這個(gè)原理可以最大限度的保障從庫(kù)數(shù)據(jù)不丟失,主從數(shù)據(jù)的一致性,但在極端情況下會(huì)出現(xiàn)丟失的情況。
2)MGR:MySQL組復(fù)制由若干個(gè)成員共同組成一個(gè)復(fù)制組。一個(gè)事務(wù)的提交,必須經(jīng)過(guò)組內(nèi)大多數(shù)成員(N / 2 + 1)確認(rèn)收到消息后,才能進(jìn)行決議并提交。對(duì)比傳統(tǒng)的主從復(fù)制,增加了一致性協(xié)議層和沖突認(rèn)證,這是保證數(shù)據(jù)一致性和多主復(fù)制的關(guān)鍵所在。組復(fù)制解決了主從復(fù)制極端情況下出現(xiàn)數(shù)據(jù)丟失、不一致的問(wèn)題,保障了數(shù)據(jù)的強(qiáng)一致。
12
如何降低MySQL主從延遲?
主從延遲直接決定了RTO的時(shí)間,因此低延遲對(duì)于數(shù)據(jù)庫(kù)切換、恢復(fù)時(shí)間非常重要。具體實(shí)現(xiàn)方法如下:
1)適當(dāng)提高從庫(kù)配置,要大于等于主庫(kù)的配置。
2)使用更高的數(shù)據(jù)庫(kù)版本,MySQL5.7開啟并行復(fù)制。
3)表結(jié)構(gòu)設(shè)計(jì)時(shí),一定要有主鍵,而且主鍵要短小。
4)使用新型硬件:PCI-E & SSD類設(shè)備。
5)應(yīng)用端適當(dāng)?shù)厥褂镁彺妫瑴p少數(shù)據(jù)庫(kù)的壓力。
6)盡量避免大事務(wù),建議在業(yè)務(wù)低峰期進(jìn)行批量DML操作,并且小批量多次執(zhí)行操作。
13
Oracle和MySQL如何實(shí)現(xiàn)相互抽數(shù)?
1)雙向抽數(shù)都可以通過(guò)程序?qū)崿F(xiàn),通過(guò)JDBC分別建立到Oracle和MySQL數(shù)據(jù)庫(kù)的連接,在源數(shù)據(jù)庫(kù)上執(zhí)行查詢返回ResultSet對(duì)象,然后通過(guò)ResultSet.next()方法逐條獲取數(shù)據(jù)后,使用到目標(biāo)數(shù)據(jù)庫(kù)連接將數(shù)據(jù)逐條插入或批量緩存N行后插入,針對(duì)Oracle數(shù)據(jù)庫(kù)查詢的內(nèi)存消耗為單行或N行數(shù)據(jù)大小,針對(duì)MySQL數(shù)據(jù)庫(kù)查詢的內(nèi)存消耗為結(jié)果集大小,因此建議分頁(yè)查詢處理。
2)MySQL to Oracle:通過(guò)工具或select* from table_name into outfile ‘文件路徑的方式將MySQL的數(shù)據(jù)導(dǎo)出為符合MySQL語(yǔ)法的SQL文件、CSV格式文件、數(shù)據(jù)文本文件,在通過(guò)Oracle的sqlldr或其他工具進(jìn)行導(dǎo)入。
3)Oracle to MySQL:可以采用開源的工具sqluldr2,它能夠?qū)racle中的數(shù)據(jù)導(dǎo)出成為符合MySQL語(yǔ)法的SQL文本,然后灌入到MySQL數(shù)據(jù)庫(kù)中。
4)另外可以采用第三方ETL工具或OGG軟件實(shí)現(xiàn),具體實(shí)現(xiàn)原理本文不做贅述。
14
單體MySQL、中間件+MySQL與Oracle性能對(duì)比如何?
下面以我行某業(yè)務(wù)場(chǎng)景單表9千萬(wàn)數(shù)據(jù)數(shù)據(jù)量為例(共三張不同的業(yè)務(wù)表),
其中單表為某一張表,兩表為某兩張表。
場(chǎng)景 | 耗時(shí)(秒) |
---|---|
Oracle配置:物理機(jī)32C+64G+SSD | |
文本導(dǎo)入9千萬(wàn)數(shù)據(jù) | 1517 |
單表count | 3.35 |
單表點(diǎn)查詢 | <0.01 |
兩表關(guān)聯(lián)(帶條件點(diǎn)查詢走索引,關(guān)聯(lián)字段、查詢條件為分片健) | <0.01 |
兩表關(guān)聯(lián)(全表) | 38 |
三表關(guān)聯(lián)(帶條件點(diǎn)查詢走索引,關(guān)聯(lián)字段、查詢條件為分片健) | <0.01 |
三表關(guān)聯(lián)(全表) | 72 |
1)文本導(dǎo)入由于中間件+MySQL做了拆分,性能要明顯好于其他單機(jī)數(shù)據(jù)。
2)單表count MySQL8.0做了優(yōu)化,性能比Oracle還要好,但多表關(guān)聯(lián)略差于5.7。
3)單機(jī)的MySQL無(wú)論5.7,還是8.0在關(guān)聯(lián)查詢上性能還是遠(yuǎn)差于Oracle的。雖然8.0支持了hash join,但也有一定的限制要求,比如關(guān)聯(lián)字段不能建立索引,必須有等值條件。
4)帶條件的關(guān)聯(lián)查詢性能表現(xiàn)一樣,需要說(shuō)明這里關(guān)聯(lián)字段必須是分片健,查詢條件也是分片健,中間件+MySQL的優(yōu)勢(shì)才可以體現(xiàn)出來(lái)。
5)中間件+MySQL8分片采用虛擬機(jī)(8c16G)和單庫(kù)Oracle物理機(jī)(32C64G)性能上基本持平,但對(duì)語(yǔ)句有比較嚴(yán)格的要求,必須要結(jié)合分片健做關(guān)聯(lián)過(guò)濾條件。
15
如何申請(qǐng)MySQL主機(jī)資源,MySQL主從數(shù)量?
資源申請(qǐng):
采用單機(jī)數(shù)據(jù)庫(kù)、中間件配置不低于16C32G,采用分片數(shù)據(jù)單節(jié)點(diǎn)不低于8C16G,具體根據(jù)實(shí)際情況而定。
主從數(shù)量:
單中心部署:1主2從。
雙中心部署:1主3從。
三中心部署:1主4從。
總 結(jié)
以上即為我行在使用過(guò)程中最常見的15個(gè)問(wèn)題,其中部分解決方案及參數(shù)選擇和設(shè)置與我行的實(shí)際應(yīng)用情況相關(guān),未必適合大家各自的場(chǎng)景。但他山之石可以攻玉,希望我們的解決方案能夠拓展大家的思路,讓我們一起在數(shù)據(jù)庫(kù)轉(zhuǎn)型之路上共同進(jìn)步!
以上任何一個(gè)問(wèn)題都可以作為一個(gè)課題進(jìn)行研究,大家可以關(guān)注我室的公眾號(hào),里面有相關(guān)問(wèn)題的詳細(xì)解答。
-
SQL
+關(guān)注
關(guān)注
1文章
762瀏覽量
44118 -
Oracle
+關(guān)注
關(guān)注
2文章
289瀏覽量
35125 -
數(shù)據(jù)安全
+關(guān)注
關(guān)注
2文章
681瀏覽量
29940
發(fā)布評(píng)論請(qǐng)先 登錄
相關(guān)推薦
評(píng)論