傳統(tǒng)數(shù)據(jù)庫遷移到MySQL常見問題

前 言

目前，出于對數(shù)據(jù)庫產(chǎn)品高安全和高可用的要求，銀行業(yè)在現(xiàn)有核心業(yè)務(wù)系統(tǒng)中選用的一般為國際大型廠商的成熟產(chǎn)品，如IBM的DB2、甲骨文的Oracle、微軟的SQL Server等。但隨著業(yè)務(wù)的不斷發(fā)展，銀行業(yè)對數(shù)據(jù)庫產(chǎn)品的需求已經(jīng)逐漸多樣化：一方面要能滿足業(yè)務(wù)系統(tǒng)的基本需求，另一方面對于數(shù)據(jù)安全、自主掌控的要求也越來越高。鑒于此，不少銀行業(yè)已經(jīng)開始了自己的轉(zhuǎn)型嘗試，并取得了一定的成果。

MySQL作為當(dāng)前最熱門的開源數(shù)據(jù)庫，已被互聯(lián)網(wǎng)公司廣泛應(yīng)用?；趯?shù)據(jù)庫安全可控的考慮，銀行業(yè)也正在進行較大規(guī)模的推廣，用于替代傳統(tǒng)數(shù)據(jù)庫產(chǎn)品。

我行在替換和使用、改造的過程中遇到了不少問題，下面總結(jié)了最常被開發(fā)、運維問到的問題，我們做了最精簡的解答，希望對大家能有所幫助。

01

為什么首選MySQL數(shù)據(jù)庫作為替換Oracle的數(shù)據(jù)庫產(chǎn)品？

近年來MySQL的蓬勃發(fā)展及其在互聯(lián)網(wǎng)行業(yè)的豐富實踐，使得其替換商業(yè)數(shù)據(jù)庫成為了可能，尤其是阿里等行業(yè)巨頭已成功地使用MySQL替換Oracle并支撐了龐大的業(yè)務(wù)。MySQL作為世界上最流行的數(shù)據(jù)庫還具備如下優(yōu)勢：

1)豐富的文檔資料，大量的從業(yè)人員和蓬勃的生態(tài)都使MySQL成為首選。

2)支持行鎖和事務(wù)的Innodb存儲引擎在官方的強力支持下越來越強大，對于高并發(fā)下OLTP優(yōu)勢明顯。

3)靈活的邏輯復(fù)制搭建主從可以在架構(gòu)設(shè)計上有更多的空間。組復(fù)制（MGR）技術(shù)可以保證數(shù)據(jù)的強一致，打通了最后一道技術(shù)壁壘，滿足了金融等領(lǐng)域?qū)?shù)據(jù)強一致性的要求。

4）當(dāng)單機成為性能瓶頸的時候，豐富的開源中間件搭配MySQL做數(shù)據(jù)拆分實現(xiàn)了分布式數(shù)據(jù)庫改造方案，可以提供更高的業(yè)務(wù)需求。

02

MySQL對比Oracle有哪些語法、數(shù)據(jù)類型、對象類型兼容性問題？

MySQL支持 Oracle 絕大部分的基本 SQL 語法及數(shù)據(jù)類型、對象類型。部分不支持的如下：

1）數(shù)據(jù)類型方面MySQL不支持序列、自定義類型、XML數(shù)據(jù)類型及偽列。

2）MySQL不支持對象包括物化視圖、包管理及同義詞。

3）索引方面MySQL不支持位圖索引、位圖連接索引、函數(shù)索引、在線重建索引。

4）觸發(fā)器方面MySQL不支持DDL事件觸發(fā)器、系統(tǒng)事件觸發(fā)器、時間觸發(fā)器。

5）高級功能方面MySQL不支持外部數(shù)據(jù)庫鏈接、面向?qū)ο?、閃回查詢等。

6）函數(shù)方面MySQL不支持COSH(x)、CHR(n1)、LAG()、RANK()等函數(shù)。

03

采用中間件+MySQL開發(fā)和直連MySQL開發(fā)有哪些限制？

在應(yīng)用連接池配置部分與直連MySQL相同，對應(yīng)用而言引入中間件屏蔽了后端拆分細(xì)節(jié)，可理解為中間件即數(shù)據(jù)庫，采用中間件方式的具體限制如下：

1）存在部分語法限制，包括DDL、DML及管理語句，如不?持create table ... like ...、INSERT... SELECT...等語法。

2）性能方面，需要基于相同分片規(guī)則的分片鍵進行查詢與關(guān)聯(lián)查詢。

3）不支持外鍵關(guān)聯(lián)、臨時表、觸發(fā)器、分布式級別存儲過程和自定義函數(shù)等。

04

如何將數(shù)據(jù)從Oracle遷移到MySQL？

從Oracle遷移到MySQL屬于異構(gòu)遷移，需要依賴第三方開源工具或者商業(yè)工具進行遷移。數(shù)據(jù)量大小和業(yè)務(wù)停機時間決定了遷移的方式。

1）當(dāng)數(shù)據(jù)量很小，停機時間完全可以操作完成的時候，可以采用直接文本導(dǎo)出導(dǎo)入操作，這種方式簡單并且高效。

2）當(dāng)停機時間要求特別短，此時我們將采用OGG（Oracle Golden Gate）或者類似工具進行全量+增量保持Oracle到MySQL實時同步。等到業(yè)務(wù)停止準(zhǔn)備切換時，停掉Oracle到MySQL同步。驗證數(shù)據(jù)無誤后，業(yè)務(wù)代碼對接到MySQL數(shù)據(jù)庫完成數(shù)據(jù)遷移過程。

05

為什么MySQL不建議建立存儲過程、觸發(fā)器、自定義函數(shù)等對象？

對于數(shù)據(jù)庫的使用，我們強烈建議只參與數(shù)據(jù)存取，不參與業(yè)務(wù)邏輯。具體原因如下：

1）將業(yè)務(wù)邏輯的實現(xiàn)完全置于代碼中，易于集中維護和調(diào)試。

2）觸發(fā)器的嵌套，如果再涉及多個存儲過程、事務(wù)控制等時，很容易出現(xiàn)死鎖。

3）基于中間件實現(xiàn)的分布式數(shù)據(jù)庫對存儲過程、觸發(fā)器、自定義函數(shù)支持有限。

4）對DB保護，減少數(shù)據(jù)庫的壓力。

5）對于異構(gòu)數(shù)據(jù)庫可移植性較差，增加開發(fā)成本。

06

什么情況下使用分庫分表？數(shù)據(jù)庫拆分方式有哪些？如何選擇拆分方式？

對于MySQL而言，當(dāng)數(shù)據(jù)量過大、QPS或TPS過高，或者單機的硬件資源（CPU、磁盤、內(nèi)存、IO等）出現(xiàn)性能瓶頸，通過單方面增加硬件資源已經(jīng)無法滿足要求時需要考慮做分庫分表。一般情況下單表大小超過2000萬，數(shù)據(jù)庫大小超過100G需要考慮，具體根據(jù)實際應(yīng)用場景而定。

數(shù)據(jù)庫拆分方式分為水平拆分和垂直拆分。

垂直拆分是指按照功能模塊、關(guān)系密切度拆分到不同的表或者庫，垂直拆分相對簡單，不同的業(yè)務(wù)訪問自己的庫和表就可以實現(xiàn)。

水平拆分是指把表的數(shù)據(jù)按照某種規(guī)則進行劃分，存儲到結(jié)構(gòu)相同的不同表中，水平拆分相對復(fù)雜一些，需要把一張表的數(shù)據(jù)做物理拆分，拆分的時候要根據(jù)數(shù)據(jù)的增長預(yù)測拆分的粒度，并且也要盡可能的保證數(shù)據(jù)和負(fù)載的平均。

在選擇拆分方式的時候，要評估出現(xiàn)瓶頸的原因，如果是因為數(shù)據(jù)庫表過多導(dǎo)致數(shù)據(jù)量過大，并且數(shù)據(jù)庫中業(yè)務(wù)邏輯清晰，那么就選擇垂直拆分。如果是單表的數(shù)據(jù)量比較大，就應(yīng)該選擇水平拆分。

07

有哪些MySQL管理工具?

常用的MySQL管理工具有：Navicat for MySQL、SQLyog、PhpMyAdmin、MySQLWorkBench等。

開源工具推薦采用MySQLWorkBench，付費推薦采用：SQLyog。

08

修改MySQL大表的風(fēng)險和性能如何？

MySQL大表修改會產(chǎn)生死鎖，所以一般情況下會采用以下兩種方式進行修改：

1）在業(yè)務(wù)低峰期停止服務(wù)后直接ALTER修改。此方式的安全性較高，但是每次修改都需要停止業(yè)務(wù)，對于某些核心業(yè)務(wù)系統(tǒng)是不可接受的。而且對于比較大的表，停止業(yè)務(wù)時間也較長，成本會較高。

2） 采用第三方工具pt-online-schema-change。該工具可以直接進行修改，其操作原理是：首先對表加鎖(表此時只讀)，然后復(fù)制原表物理結(jié)構(gòu)創(chuàng)建一個中間表，接下來修改中間表的物理結(jié)構(gòu)，隨后把原表數(shù)據(jù)導(dǎo)入中間表中，數(shù)據(jù)同步完后，鎖定中間表，并刪除原表，接下來rename中間表為原表，最后刷新數(shù)據(jù)字典并釋放鎖。該工具修改過程中所修改的表必須有主鍵，且不能是聯(lián)合主鍵。同時也存在一定的風(fēng)險，該工具在做change修改的時候不會提示錯誤，但是結(jié)果會發(fā)現(xiàn)數(shù)據(jù)會有部分丟失。在性能方面也有一定的瓶頸：如在并發(fā)比較高的情況下會對業(yè)務(wù)的訪問速度有一定影響。

基于分布式數(shù)據(jù)庫中間件產(chǎn)品，暫不支持pt-osc、gh-ost第三方工具，建議使用MySQL5.7以上版本，依靠原生Online DDL進行表結(jié)構(gòu)變更。

09

MySQL分區(qū)表使用原則是什么？

MySQL實現(xiàn)分區(qū)表的方式是對底層表的封裝，意味著索引也是按照分區(qū)的子表定義的，沒有全局索引。這和Oracle不同，在Oracle中可以更加靈活地定義索引和表是否進行分區(qū)。

MySQL分區(qū)表在使用的時候常規(guī)的CRUD操作以及返回結(jié)果和普通表沒有任何區(qū)別。MySQL的分區(qū)表的類型主要包括RANGE、LIST、HASH、KEY四種，不支持自建分區(qū)。

某些特定場景下可以考慮采用分區(qū)表，如歷史數(shù)據(jù)有明確的分區(qū)范圍、訪問不跨分區(qū)、極少的變更操作、查詢語句邏輯簡單、無性能瓶頸等。

對于Oracle這些商業(yè)數(shù)據(jù)庫，由于商業(yè)授權(quán)導(dǎo)致橫向擴展成本較高，且分區(qū)表功能穩(wěn)定，因此可通過硬件擴展和分區(qū)來承擔(dān)大數(shù)據(jù)量帶來的負(fù)載。而對于MySQL開源數(shù)據(jù)庫，企業(yè)有資源有能力將很多需求遷移到數(shù)據(jù)庫外通過代碼邏輯或者其它替代方式來實現(xiàn)，因此更追求MySQL使用過程中的簡單、穩(wěn)定和可靠，且通過增加服務(wù)器以及分庫分表更能處理由于數(shù)據(jù)量爆炸式增長所帶來的性能問題。因此不建議大量使用MySQL分區(qū)表，尤其是在重要的業(yè)務(wù)上。

10

如何做MySQL架構(gòu)選型？

可以參考如下表格：

注：

數(shù)據(jù)量大小依據(jù)：以單表2000萬以內(nèi)，單庫100G以內(nèi)劃分，具體可以根據(jù)實際情況而定。

集中式：即直連MySQL單機數(shù)據(jù)庫。

分布式：通過中間件+MySQL做數(shù)據(jù)拆分。

三中心架構(gòu)：同城雙中心+異地中心。

兩中心架構(gòu)：本地單中心+異地中心。

單中心架構(gòu)：本地單中心。

11

MySQL如何保障數(shù)據(jù)一致性？

單機：

通過雙1參數(shù)設(shè)置，強制日志寫入磁盤后提交事務(wù)。

復(fù)制：

1）主從：主從通過增強半同步實現(xiàn)：主庫提交事務(wù)，從庫需要接收到主庫的日志并寫入relay log，返回給主庫ack消息后，主庫才可以提交。基于這個原理可以最大限度的保障從庫數(shù)據(jù)不丟失，主從數(shù)據(jù)的一致性，但在極端情況下會出現(xiàn)丟失的情況。

2）MGR：MySQL組復(fù)制由若干個成員共同組成一個復(fù)制組。一個事務(wù)的提交，必須經(jīng)過組內(nèi)大多數(shù)成員（N / 2 + 1）確認(rèn)收到消息后，才能進行決議并提交。對比傳統(tǒng)的主從復(fù)制，增加了一致性協(xié)議層和沖突認(rèn)證，這是保證數(shù)據(jù)一致性和多主復(fù)制的關(guān)鍵所在。組復(fù)制解決了主從復(fù)制極端情況下出現(xiàn)數(shù)據(jù)丟失、不一致的問題，保障了數(shù)據(jù)的強一致。

12

如何降低MySQL主從延遲？

主從延遲直接決定了RTO的時間，因此低延遲對于數(shù)據(jù)庫切換、恢復(fù)時間非常重要。具體實現(xiàn)方法如下：

1）適當(dāng)提高從庫配置，要大于等于主庫的配置。

2）使用更高的數(shù)據(jù)庫版本，MySQL5.7開啟并行復(fù)制。

3）表結(jié)構(gòu)設(shè)計時，一定要有主鍵，而且主鍵要短小。

4）使用新型硬件：PCI-E & SSD類設(shè)備。

5）應(yīng)用端適當(dāng)?shù)厥褂镁彺?，減少數(shù)據(jù)庫的壓力。

6）盡量避免大事務(wù)，建議在業(yè)務(wù)低峰期進行批量DML操作，并且小批量多次執(zhí)行操作。

13

Oracle和MySQL如何實現(xiàn)相互抽數(shù)？

1）雙向抽數(shù)都可以通過程序?qū)崿F(xiàn)，通過JDBC分別建立到Oracle和MySQL數(shù)據(jù)庫的連接，在源數(shù)據(jù)庫上執(zhí)行查詢返回ResultSet對象，然后通過ResultSet.next()方法逐條獲取數(shù)據(jù)后，使用到目標(biāo)數(shù)據(jù)庫連接將數(shù)據(jù)逐條插入或批量緩存N行后插入，針對Oracle數(shù)據(jù)庫查詢的內(nèi)存消耗為單行或N行數(shù)據(jù)大小，針對MySQL數(shù)據(jù)庫查詢的內(nèi)存消耗為結(jié)果集大小，因此建議分頁查詢處理。

2）MySQL to Oracle：通過工具或select* from table_name into outfile ‘文件路徑的方式將MySQL的數(shù)據(jù)導(dǎo)出為符合MySQL語法的SQL文件、CSV格式文件、數(shù)據(jù)文本文件，在通過Oracle的sqlldr或其他工具進行導(dǎo)入。

3）Oracle to MySQL：可以采用開源的工具sqluldr2，它能夠?qū)racle中的數(shù)據(jù)導(dǎo)出成為符合MySQL語法的SQL文本，然后灌入到MySQL數(shù)據(jù)庫中。

4）另外可以采用第三方ETL工具或OGG軟件實現(xiàn)，具體實現(xiàn)原理本文不做贅述。

14

單體MySQL、中間件+MySQL與Oracle性能對比如何？

下面以我行某業(yè)務(wù)場景單表9千萬數(shù)據(jù)數(shù)據(jù)量為例（共三張不同的業(yè)務(wù)表），

其中單表為某一張表，兩表為某兩張表。

1）文本導(dǎo)入由于中間件+MySQL做了拆分，性能要明顯好于其他單機數(shù)據(jù)。

2）單表count MySQL8.0做了優(yōu)化，性能比Oracle還要好，但多表關(guān)聯(lián)略差于5.7。

3）單機的MySQL無論5.7，還是8.0在關(guān)聯(lián)查詢上性能還是遠差于Oracle的。雖然8.0支持了hash join，但也有一定的限制要求，比如關(guān)聯(lián)字段不能建立索引，必須有等值條件。

4）帶條件的關(guān)聯(lián)查詢性能表現(xiàn)一樣，需要說明這里關(guān)聯(lián)字段必須是分片健，查詢條件也是分片健，中間件+MySQL的優(yōu)勢才可以體現(xiàn)出來。

5）中間件+MySQL8分片采用虛擬機（8c16G）和單庫Oracle物理機（32C64G）性能上基本持平，但對語句有比較嚴(yán)格的要求，必須要結(jié)合分片健做關(guān)聯(lián)過濾條件。

15

如何申請MySQL主機資源，MySQL主從數(shù)量？

資源申請：

采用單機數(shù)據(jù)庫、中間件配置不低于16C32G，采用分片數(shù)據(jù)單節(jié)點不低于8C16G，具體根據(jù)實際情況而定。

主從數(shù)量：

單中心部署：1主2從。

雙中心部署：1主3從。

三中心部署：1主4從。

總 結(jié)

以上即為我行在使用過程中最常見的15個問題，其中部分解決方案及參數(shù)選擇和設(shè)置與我行的實際應(yīng)用情況相關(guān)，未必適合大家各自的場景。但他山之石可以攻玉，希望我們的解決方案能夠拓展大家的思路，讓我們一起在數(shù)據(jù)庫轉(zhuǎn)型之路上共同進步！

以上任何一個問題都可以作為一個課題進行研究，大家可以關(guān)注我室的公眾號，里面有相關(guān)問題的詳細(xì)解答。