使用Kafka,如何成功遷移SQL資料庫中超過20億條記錄?

2021-01-20 騰訊網

作者 | Kamil Char ampowicz

譯者 | 王者

策劃 | Tina

使用 Kafka，如何成功遷移 SQL 資料庫中超過 20 億條記錄？我們的一個客戶遇到了一個 MySQL 問題，他們有一張大表，這張表有 20 多億條記錄，而且還在不斷增加。如果不更換基礎設施，就有磁碟空間被耗盡的風險，最終可能會破壞整個應用程式。而且，這麼大的表還存在其他問題：糟糕的查詢性能、糟糕的模式設計，因為記錄太多而找不到簡單的方法來進行數據分析。我們希望有這麼一個解決方案，既能解決這些問題，又不需要引入高成本的維護時間窗口，導致應用程式無法運行以及客戶無法使用系統。在這篇文章中，我將介紹我們的解決方案，但我還想提醒一下，這並不是一個建議：不同的情況需要不同的解決方案，不過也許有人可以從我們的解決方案中得到一些有價值的見解。

雲解決方案會是解藥嗎？

在評估了幾個備選解決方案之後，我們決定將數據遷移到雲端，我們選擇了 Google Big Query。我們之所以選擇它，是因為我們的客戶更喜歡谷歌的雲解決方案，他們的數據具有結構化和可分析的特點，而且不要求低延遲，所以 BigQuery 似乎是一個完美的選擇。經過測試，我們確信 Big Query 是一個足夠好的解決方案，能夠滿足客戶的需求，讓他們能夠使用分析工具，可以在幾秒鐘內進行數據分析。但是，正如你可能已經知道的那樣，對 BigQuery 進行大量查詢可能會產生很大的開銷，因此我們希望避免直接通過應用程式進行查詢，我們只將 BigQuery 作為分析和備份工具。

將數據流到雲端

說到流式傳輸數據，有很多方法可以實現，我們選擇了非常簡單的方法。我們使用了 Kafka，因為我們已經在項目中廣泛使用它了，所以不需要再引入其他的解決方案。Kafka 給了我們另一個優勢——我們可以將所有的數據推到 Kafka 上，並保留一段時間，然後再將它們傳輸到目的地，不會給 MySQL 集群增加很大的負載。如果 BigQuery 引入失敗（比如執行請求查詢的成本太高或太困難），這個辦法為我們提供了某種退路。這是一個重要的決定，它給我們帶來了很多好處，而開銷很小。

將數據從 MySQL 流到 Kafka

關於如何將數據從 MySQL 流到 Kafka，你可能會想到 Debezium（https://debezium.io）或 Kafka Connect。這兩種解決方案都是很好的選擇，但在我們的案例中，我們沒有辦法使用它們。MySQL 伺服器版本太老了，Debezium 不支持，升級 MySQL 升級也不是辦法。我們也不能使用 Kafka Connect，因為表中缺少自增列，Kafka Connect 就沒辦法保證在傳輸數據時不丟失數據。我們知道有可能可以使用時間戳，但這種方法有可能會丟失部分數據，因為 Kafka 查詢數據時使用的時間戳精度低於表列中定義的精度。當然，這兩種解決方案都很好，如果在你的項目中使用它們不會導致衝突，我推薦使用它們將資料庫裡的數據流到 Kafka。在我們的案例中，我們需要開發一個簡單的 Kafka 生產者，它負責查詢數據，並保證不丟失數據，然後將數據流到 Kafka，以及另一個消費者，它負責將數據發送到 BigQuery，如下圖所示。

將數據流到 BigQuery

通過分區來回收存儲空間

我們將所有數據流到 Kafka(為了減少負載，我們使用了數據過濾)，然後再將數據流到 BigQuery，這幫我們解決了查詢性能問題，讓我們可以在幾秒鐘內分析大量數據，但空間問題仍然存在。我們想設計一個解決方案，既能解決現在的問題，又能在將來方便使用。我們為數據表準備了新的 schema，使用序列 ID 作為主鍵，並將數據按月份進行分區。對大表進行分區，我們就能夠備份舊分區，並在不再需要這些分區時將其刪除，回收一些空間。因此，我們用新 schema 創建了新表，並使用來自 Kafka 的數據來填充新的分區表。在遷移了所有記錄之後，我們部署了新版本的應用程式，它向新表進行插入，並刪除了舊錶，以便回收空間。當然，為了將舊數據遷移到新表中，你需要有足夠的空閒可用空間。不過，在我們的案例中，我們在遷移過程中不斷地備份和刪除舊分區，確保有足夠的空間來存儲新數據。

將數據流到分區表中

通過整理數據來回收存儲空間

在將數據流到 BigQuery 之後，我們就可以輕鬆地對整個數據集進行分析，並驗證一些新的想法，比如減少資料庫中表所佔用的空間。其中一個想法是驗證不同類型的數據是如何在表中分布的。後來發現，幾乎 90% 的數據是沒有必要存在的，所以我們決定對數據進行整理。我開發了一個新的 Kafka 消費者，它將過濾掉不需要的記錄，並將需要留下的記錄插入到另一張表。我們把它叫作整理表，如下所示。

經過整理，類型 A 和 B 被過濾掉了：

將數據流入新表

整理好數據之後，我們更新了應用程式，讓它從新的整理表讀取數據。我們繼續將數據寫入之前所說的分區表，Kafka 不斷地從這個表將數據推到整理表中。正如你所看到的，我們通過上述的解決方案解決了客戶所面臨的問題。因為使用了分區，存儲空間不再是個問題，數據整理和索引解決了應用程式的一些查詢性能問題。最後，我們將所有數據流到雲端，讓我們的客戶能夠輕鬆對所有數據進行分析。由於我們只對特定的分析查詢使用 BigQuery，而來自用戶其他應用程式的相關查詢仍然由 MySQL 伺服器處理，所以開銷並不會很高。另一點很重要的是，所有這些都是在沒有停機的情況下完成的，因此客戶不會受到影響。

總 結

總的來說，我們使用 Kafka 將數據流到 BigQuery。因為將所有的數據都推到了 Kafka，我們有了足夠的空間來開發其他的解決方案，這樣我們就可以為我們的客戶解決重要的問題，而不需要擔心會出錯。

今日薦文

相關焦點

  • 資料庫遷移搞炸了!沒用這款開源神器的鍋?
    相關概念工作原理使用Flyway時我們需要編寫好資料庫遷移的SQL腳本，比如V1__Initial_Setup.sql中初始化了三種表，V2__First_Changes.sql中又新增了兩種表。Flyway會創建flyway_schema_history表，用於存儲這些SQL腳本的執行情況，從而對資料庫進行版本控制。
  • 真的,關於 Kafka 入門看這一篇就夠了
    包括收集各種分布式應用的數據，生產各種操作的集中反饋，比如報警和報告；日誌記錄：Kafka 的基本概念來源於提交日誌，比如我們可以把資料庫的更新發送到 Kafka 上，用來記錄資料庫的更新時間，通過kafka以統一接口服務的方式開放給各種consumer，例如hadoop、Hbase、Solr等；流式處理：流式處理是有一個能夠提供多種應用程式的領域
  • eclipse如何使用JDBC向資料庫插入數據!
    eclipse如何使用JDBC向資料庫插入數據！1.在工程中新建InsertTest.java類2.向資料庫中插入數據總共分為4步   1.獲取資料庫連接   2.準備sql語句   3.執行插入      3.1使用connection的createStatement()方法獲取Statement對象      3.2調用Statement對象的executeUpdate(sql)方法執行插入操作   4.關閉資料庫連接
  • Qt5如何備份mariaDB資料庫
    資料庫對比Mariadb是mysql的分支，所以說qt如何使用mysql,那麼就如何使用mariadb了。以前備份資料庫都是用資料庫管理軟體。那該如何在應用軟體中備份資料庫呢？這裡我是用資料庫自帶的命令：mysqldump.
  • SQL是如何在資料庫中執行的?
    SQL是如何在執行器中執行的 ？我們通過一個例子來看一下，執行器是如何來解析執行一條 SQL 的。這個 SQL 語義是，查詢用戶 ID 大於 50 的用戶的所有訂單，這是很簡單的一個聯查，需要查詢 users 和 orders 兩張表，WHERE 條件就是，用戶 ID 大於 50。
  • 比Postgre快10倍的開源資料庫CrateDB
    一張擁有20多個欄位的表，記錄大約有3億條，需要查詢某個時間範圍內的數據，並做分組，排序，聚合統計操作，並需要即時響應結果，大家看到這個需求，一定深有體會，傳統的關係型資料庫不能滿足需求，肯定能想到的方案是hbase，ElasticSearch了，hbase方案稍微有點重，ElasticSearch
  • R+SQL Server的大數據管理
    筆者最近手頭上有一個10多G的csv，大概有2千5百萬條的記錄，這真的讓我以及我的R語言感到亞歷山大。Excel不用說更是打不開了(Excel 2003及以前的版本最大僅支持65,536行)，SAS軟體花費一些時間勉強能打開，但是對某些中文的格式不兼容(特別是某些變量包含逗號的時候)。該怎麼辦呢？這是大數據的問題嗎？怎麼那麼不小心就被我碰上了？
  • Python連接MySQL資料庫方法介紹(超詳細!手把手項目案例操作)
    print (i)cur.close() # 關閉遊標conn.close() # 關閉連接上述代碼中，實現了通過Python連接MySQL查詢所有的數據，並輸出前2條數據的功能。>print (i)cur.close() # 關閉遊標conn.close() # 關閉連接上述代碼中，實現了通過Python連接MySQL查詢所有的數據，並輸出前2條數據的功能。
  • c使用sql server專題及常見問題 - CSDN
    為php添加 sqlsrv 擴展去微軟官網 https://www.microsoft.com/en-us/download/ 搜索php ,點擊 Microsoft Drivers for PHP for SQL Server 下載最新版的 sqlsrvXX.exe( 我下載的是SQLSRV32.EXE )運行解壓, 得到一堆DLL, 選取適合自己
  • 如何用Eclipse中插件SQLExplorer開發資料庫
    Eclipse插件SQLExplorer在Eclipse中連接各種資料庫進行操作使用。　　這樣，如果你下載了多個插件就可以如法炮製建立多個Link文件，想加載哪個插件就把哪個插件的Link文件放到%ECLIPSE_HOME%\links的目錄中即可，使用與管理都很方便，建議千萬不要放在默認的安裝目錄中；　　3、如果你的%ECLIPSE_HOME%與此不同，請修改XYZ.link文件裡的路徑　　刪除，關閉Eclipse
  • NET開發-如何通過SSMS工具或SQL語句給SQL Server資料庫添加數據
    對於SQL Server資料庫中的表，當給表創建好列(欄位)之後，就可以添加數據了，由於數據表是二維表格，每一行數據都包含所有的列。在SQL Server中，即可以使用SSMS工具可視化處理數據，也可以使用標準的SQL語句處理數據。
  • 大數據分析工程師面試集錦3-SQL/SparkSql/HiveQL
    可以將資料庫想像為一個文件櫃，這個文件櫃是一個存放數據的物理位置，不管數據是什麼，也不管數據是如何組織的。通常容易將資料庫和資料庫軟體的概念相混淆，資料庫軟體應稱為資料庫管理系統（DBMS）。資料庫是通過DBMS創建和操縱的容器，而具體它究竟是什麼，形式如何，各種資料庫都不一樣。
  • 一文帶你了解 Kafka 基本原理
    2）每個segment中存儲多條消息（見下圖），消息id由其邏輯位置決定，即從消息id可直接定位到消息的存儲位置，避免id到位置的額外映射。 3）每個part在內存中對應一個index，記錄每個segment中的第一條消息偏移。
  • 最詳細的SQL注入相關的命令整理
    偶然間我想到了在SQL語言中可以使用"in"關鍵字進行查詢，例如"select * from mytable where id in(1)"，括號中的值就是我們提交的數據，它的結果與使用"select * from mytable where id=1"的查詢結果完全相同。
  • 如何使用SQL Server遊標
    【IT168 技術文檔】在資料庫開發過程中，當你檢索的數據只是一條記錄時，你所編寫的事務語句代碼往往使用SELECT INSERT 語句。但是我們常常會遇到這樣情況，即從某一結果集中逐一地讀取一條記錄。那麼如何解決這種問題呢？遊標為我們提供了一種極為優秀的解決方案。
  • 初識資料庫分庫分表框架Dbsplit
    另外，擴展的Dbsplit保持與原有JdbcTemplate完全兼容，對於特殊需求，完全可以回溯到原有JdbcTemplate提供的功能，即使用JDBC的方式來解決，這裡面體現了通用和專用原則，通用原則解決80%的事情，而專用原則解決剩餘的20%的事情。此項目也提供了一個方便的腳本，可以一次性的建立多庫多表。
  • SQL server資料庫存儲過程測試方法
    此次介紹如何使用原始的SQL環境來測試SQL存儲過程。　　問題　　如何創建SQL資料庫以及表結構以用來保存測試用例的輸入數據和測試結果。　　設計　　編寫T-SQL腳本，創建一個資料庫然後創建一些表用來保存測試用例的輸入數據和測試結果。
  • 使用Flink進行實時日誌聚合:第一部分
    例如，行業中廣泛使用的著名日誌記錄框架是ELK stack和Graylog。 不幸的是，沒有適用於每個應用程式的明確解決方案，不同的日誌記錄解決方案可能更適合某些用例。例如，實時應用程式的日誌處理也應實時進行，否則，我們會丟失及時信息，而這些信息可能無法成功運行系統。
  • 如何使用 SQL Server 資料庫查詢累計值
    首頁 > 語言 > 關鍵詞 > 資料庫最新資訊 > 正文 如何使用 SQL Server 資料庫查詢累計值
  • SQL語句性能調整之ORACLE的執行計劃
    這個語句的優點就是它的缺點，這樣在用該方法查看執行時間較長的sql語句時，需要等待該語句執行成功後，才返回執行計劃，使優化的周期大大增長。　　如果不想執行語句而只是想得到執行計劃可以採用：　　Sql> set autotrace traceonly　　這樣，就只會列出執行計劃，而不會真正的執行語句，大大減少了優化時間。