免費五行缺失查詢表
前言
本文具體探討 MySQL 數據實時同步到 Elasticsearch (以下簡稱 ES ) 技術方案和思考，同時使用一定篇幅介紹一些前置知識，從理論到實踐，讓讀者更好的理解這塊內容和相關問題。包括：
為什麼我們要將數據從 MySQL 實時同步到 ES ，本質是什麼？
為什麼是 ES，而不是其他 OLAP 引擎？
MySQL 到 ES 數據實時同步方案中有哪些細節需要注意？
MySQL 到 ES 數據實時同步方案可以有哪些選擇，優缺點是什麼？
相信看完本文，你會對 MySQL 數據實時同步到 ES 有更多的了解。
數據庫去規范化
Database normalization is the process of structuring a database, usually a relational database, in accordance with a series of so-called normal forms in order to reduce data redundancy and improve data integrity. It was first proposed by Edgar F. Codd as part of his relational model.
數據庫規范化是指關系型數據庫中通過一系列數據庫范式來減少數據冗余、增強數據一致性的策略。例如我們平時使用的關系型數據庫的關系模型可以認為是 Database Normalization 的一種實現方式，這些關系型數據庫基本都至少遵循了數據庫第三范式，可以稱之為 Normalized Database。 數據庫范式的內容，本文不再展開。
Denormalization is a strategy used alt="免費五行缺失查詢表，免費生辰八字五行分析" src="https://jixiaotop.com/zb_users/upload/2022/04/20220425131905165086394529568.webp" width="100%" height="100%" />
使用規范化數據查詢,是一個 3 表聯查操作，而在數據庫中，大體分三步：
通過學生學號獲取學生信息，得到班級編號
通過班級編號獲取班級信息，得到班主任工號
通過班主任工號得師信息，得到班主任的名字
如果在數據量較大，有一定并發要求，并且涉及更多表關聯時候，這種計算就不能滿足需求，這個時候去規范化的優化方式就登場了。
列級處理——主查詢表冗余字段
通過在主表冗余計算好的數據，可避免頻繁重復計算數據。如下場景適合在主數據表內冗余數據：
應用系統需要經常獲取計算好的數據
冗余的原始數據不經常變化
在學生表冗余班主任的名字信息，表的設計變為如下：
這時候查詢就只有一步了：
根據序號獲取學生信息，同時也得到了其班主任名字
優點：較為簡單易懂，容易實現。
缺點：侵入業務邏輯，拖慢業務代碼性能的同時，長期迭代所產生的變化可能會有穩定性風險。
表級處理——寬表預構建/Cube預構建
表級處理主要操作就是構建寬表，或者構建數據立方體(Data Cube)。構建好的寬表 了用戶查詢時需要的所有維度、度量信息。以上面學生查找班主任的問題為例，構建的寬表結構如下。
表級處理常見實現方式包括 應用多寫、數據庫自身實現的物化視圖、數據遷移同步。
應用多寫
在主數據相同數據庫內創建寬表，應用寫入數據的時候同時也向寬表寫入數據(事務保證一致性)，復雜查詢即可從該表進行。
優點：實現簡單、低成本
缺點：對主數據庫造成更大的讀寫壓力，外加業務改造成本。
RDBMS 物化視圖
Oracle、SqlServer 等數據庫物化視圖方案，通過數據冗余與預計算減少 join、聚合，從而提升查詢性能。例如，在 Oracle 上完成學生查找班主任這個查詢，可以構建一張“學生管理表”的物化視圖，查詢請求直接請求物化視圖即可得到查詢結果，避免 join ，顯著改善該 SQL 執行效率。
優點：數據庫引擎自身支持，使用成本較低
缺點：RDBMS 實現的方式有自己的局限性，比如生成物化視圖的數據需要做一些業務緊相關變換就無法滿足，或者某些數據庫并沒有完整實現該能力(物化視圖在 2000 年左右是數據庫學術圈研究的重點)。
數據遷移同步
借助數據同步工具，準實時將主數據表數據變換(包括按照業務邏輯變換)形成普通表或大寬表，寫入 存儲引擎(如 OLAP 存儲引擎或者搜索系統)。復雜查詢直接在預構建好的表上或者 cube 上執行，從而達到良好的性能。數據遷移工具的選擇較多，總體上按照其側重點，可以分為如下幾類：
大數據類：為大數據產品流入數據提供服務，因為大數據產品本身特點，側重批量定時的遷移，實時同步一般需要用特別的，往往和業務特征緊耦合。常見的數據遷移同步工具有 sqoop、datax 等
流計算類：為自身流計算框架生態服務，側重計算，遷移同步更多是類似數據連接器的角色，代表的產品如 Flink
消息類：為自身消息產品生態服務，如豐富的 kafka connector、debezium 等
數據庫類：數據庫廠家一般都會提供原廠工具，典型如 Oracle 的 GoldenGate
云廠商類：云廠商提供的數據遷移同步工具，主要側重自身云上數據庫生態產品之間的互融互通和將線下自建數據庫的數據上云，例如阿里云 DTS, 騰訊云 DTS , AWS 的 DMS 等
專業數據遷移同步工具: 包括部分開源產品或 獨立公司提供的數據遷移同步工具，例如 c、streamsets、maxwell、cloudc、striim、fivetran ，以及老牌數據集成廠商 Informatica 、Qlik 等所提供的產品
優點:
主庫更穩定：異步化解耦業務系統事務查詢和復雜查詢，避免復雜查詢對主數據庫產生影響
易運維、鏈路穩定：數據遷移同步鏈路有標準化產品支撐，和主業務系統、主庫讀寫解耦。整體架構上職責清晰，易于維護和問題追蹤
缺點: 需要對紛繁多樣的數據遷移同步工具、承載復雜查詢數據庫產品選型，對技術同學能力有一定要求
MySQL 到 ES 數據實時同步技術架構
我們已經討論了數據去規范化的幾種實現方式。MySQL 到 ES 數據同步本質上是數據去規范化的一種。本節我們展開討論“MySQL 到 ES 數據遷移同步”的技術解決方案，通過比較他們的優缺點和應用場景給讀者提供一些思路。
為什麼是 MySQL
MySQL 在關系型數據庫歷史上并沒有特別優勢的位置，Oracle/DB2/PostgreSQL(Ingres) 三 MySQL 早了 20 來年, 但是乘著 2000 年的互聯 東風, LAMP 架構得到迅速的使用，特別在中國，大部分新興企業的 IT 系統主數據沉淀于 MySQL 中。
高并發能力：MySQL 內核特征特別適合高并發簡單 SQL 操作 ，鏈接輕量化(線程)，優化器、執行器、事務引擎相對簡單，存儲引擎做得比較細致
穩定性好：主數據庫更大的要求就是穩定、不丟數據，MySQL 內核特征反倒讓其特點鮮明，從而達到很好的穩定性，主備系統也很早就 ready ,應對崩潰情況下的快速切換，innodb 存儲引擎也保障了 MySQL 下盤穩定
操作便捷：良好、便捷的用戶體驗(相比 PostgreSQL) , 讓應用者非常容易上手 ,學習成本較低
開源生態：MySQL 是一款開源產品，讓上下游廠商圍繞其構建工具相對簡單，HA proxy、分庫分表中間件讓其實用性大大加強，同時開源的特質讓其有大量的用戶
為什麼是 ES
ES 幾個顯著的特點，能夠有效補足 MySQL 在企業級數據操作場景的缺陷，而這也是我們將其選擇作為下游數據源重要原因
文本搜索能力：ES 是基于倒排索引實現的搜索系統，配合多樣的分詞器，在文本模糊匹配搜索上表現得比較好，業務場景廣泛
柵選性能好：億級規模數據使用寬表預構建(消除 join)，配合全字段索引，使 ES 在刪選能力上具備壓倒性優勢，而這個能力是諸如 CRM, BOSS, MIS 等企業運營系統核心訴求，加上文本搜索能力，獨此一家
開源和商業并行：ES 開源生態非常活躍，具備大量的用戶群體，同時其背后也有獨立的商業公司支撐，而這讓用戶根據自身特點有了更加多樣、漸進的選擇
為什麼是數據遷移同步方式
相對于數據去規范化的其他幾種方案，數據遷移同步方式存在以下幾個優點，也是其成為目前業界主流方式的原因
穩定性好：遷移同步對主數據庫的操作主要是進行數據和日志的順序讀取，同時并發小，對主數據庫穩定性影響較小(較多的下游訂閱可能在 絡層面存在影響，一般用消息解決)。另外日志(Binlog/WAL/Redo等)可重放特質，讓下游丟數據的可能性大大減小(處理好冪等的情況下)
業務解耦：一般而言主數據庫更多承載事務型操作，而下游數據系統承載運營等層面的業務, 典型如電商的買家側和賣家側業務
業務侵入小：數據遷移同步對業務無侵入，雙端對接標準數據庫(源)，可以便利地找到開源、商業、云等各個方向的成熟解決方案或產品
業務適配性好：某些數據遷移同步產品能夠嵌入業務邏輯，讓下游獲取到更加貼近業務的數據，從而讓數據服務更加有效和便捷
數據遷移同步模型選擇訂閱消費
優點
堆積能力：由于引入了消息隊列，所以整個鏈路是具備變更數據的堆積能力的。假設變更數據消費的比較慢，MySQL 本地較老的 binlog 文件由于磁盤空間的不足而被 時，消息隊列中的數據仍然存在，數據同步仍然可以正常進行
數據分發能力：引入消息隊列后可以支持多方訂閱。如果下游多個應用都依賴源端的變更數據，可以訂閱同一份 topic 即可
數據加工能力：由于變更數據是由下游消費者訂閱，因此訂閱后可以靈活的做一些數據加工。例如從外部調用微服務接口或者反查一些數據來做數據加工都是比較方便的
缺點
運維成本相對較高： 了較多的組件和應用，運維保障相對復雜。
穩定性風險較高：一環出問題會導致整個數據同步鏈路的穩定性受到影響。而且排查和問題也會比較困難。
端到端直連
優點：
低延遲：端到端的直接同步，鏈路較短，延遲低
穩定性好：鏈路組件少，出問題概率較低，排查均比較容易。適合對數據精確性高的嚴苛場景。
功能拓展性強：對端寫入消息系統，模擬訂閱，可擴展性強
運維部署簡單：鏈路組件少，部署運維更簡單
缺點：
無
數據遷移同步模型選擇總結
從筆者以往的經驗來看，如果沒有眾多的下游數據訂閱，建議采用直連。數據同步鏈路往往置于在線業務中，隨著業務規模以及重要性逐漸加大，鏈路 穩定性 更為重要些。另外 端到端 只要支持對端數據源為消息中間件，可立刻實現訂閱，數據加工能力在某些數據遷移同步產品上可通過上傳業務代碼運行的方式解決。
數據架構在滿足業務需求的同時，簡潔和清晰能夠讓系統更加易于維護和排查，當遇到鏈路每天同步幾千萬條上億條數據、偶發丟幾條需要排查，或同步鏈路卡住不同步等情況，端到端方式往往體現出相當大的優勢。
MySQL 到 ES 數據實時同步核心挑戰
關系型數據庫中不同表之間的數據常存在關聯，同步到 ES 之后，這種關聯關系該如何去，同時又能夠很好的匹配到 ES 的更佳實踐 ? 本小節會展開討論這個問題，并對常見的數據同步工具選型提供一些參考對比。
MySQL 關聯表在 ES 上的設計
關系型數據庫庫中的表 join 關系在 ES 可以用幾種數據類型來表達，包括 objected,nested,join 三種。
objected
object 類型可以存儲嵌套結構.
優點：
表示主 field 和 object 內部 field 之間的一對多關系，支持 doc 的 join 查詢。由于所有查詢時依賴的關聯數據也都在一個文檔內，避免了 ES 內部的 join，查詢效率較高
缺點：
一對多關系只能保留一層，多于一層的會被打平，會丟失嵌套 field 內部的關聯關系。下面的例子中，之一幅圖看到寫入 ES 的是一條訂單數據，其中 producets 這個 field 是 object 類型，其中 了多個產品的記錄。
當采用 objected 字段存儲 products 信息時，原本存儲 如下：
“order_id” : 123,”products” : [ { “price” : 10, “sku” : “SKU_10”, }, { “price” : 20, “sku” : “SKU_20”, }]
在ES中存儲的樣子為：
{ “order_id”: [ 123 ], “products.price”: [ 10, 20], “products.sku”: [ SKU_10, SKU_20 ],}
可以看到在ES的存儲中，producets 中每個字段的值都已經被打平處理。如果我們查詢訂單 ID 為 123，價格 price 為 10，SKU 為 “SKU_20” 的文檔，我們同樣可以搜索到結果，但這樣顯然就丟失了其內部之間的關系了。
nested
nested 類型可以存儲嵌套結構，表示一對多關系，是 object 類型的拓展
優點：
不會出現 object 的缺點，整個嵌套關系是完整維護的，子文檔內部的關聯關系保存是完整的
關聯數據通過實現上自然關聯到主文檔上，搜索時性能較好（相對于 join 類型）
缺點：
一個 nested field 只能屬于一個主文檔
在 nested 類型中，子文檔和主文檔之間是強綁定，主文檔更新的時候會強制更新子文檔。在寫多讀少的場景，性能開銷較大
child 文檔的查詢必須通過父文檔再找到子文檔
子文檔頻繁修改的話會影響別的子文檔和父文檔，因為本質上在 lucence 實現上是父文檔下的冗余存儲
join
join 類型可以配置父子文檔，通過父子文檔來實現一對多的能力，一個索引只能建一個。相比 nested 類型，該類型更加靈活。父子文檔之間通過 parentId 來關聯，實際實現上他們就是獨立的文檔。因此帶來的好處主要是
優點：
子文檔更新不影響父文檔和其他子文檔
一個子文檔可以單獨搜索
一個文檔在作為子文檔時可以自己選擇屬于哪個父文檔。通過relation可以指定不同的join列
缺點：
需要建個全局序數，用于服務于父子文檔的關聯關系，這個會影響搜索性能
join 和 nested 類型比較
join 適合寫多讀少場景，更加適合 索引性能的場景。這意味著更新的生效會更快，但是搜索時的開銷也相對大些
nested 適合讀多寫少的場景，更加 搜索的性能
MySQL 到 ES 實時數據同步實現去規范化
在了解 ES 的一些關鍵類型之后，我們就可以描述通過數據同步去規范化的幾種實現方式。
主表冗余數據
業務側將一些查詢時需要的關系數據提前冗余在源表的一個字段中。例如序列化成json存儲在源表的一個冗余字段內，利用數據同步工具寫入對端 ES 的 join/nested 類型字段。例如我們有訂單表和商品表如下圖所示。假設我們的搜索需求是，給定一個訂單ID，同時將這個訂單的訂單明細以及所有 的多件商品的明細全部搜索出來。
如果采用這種列級處理，我們在訂單表新增一個冗余列，然后將商品表的所有明細信息，按照kv成json寫入冗余列即可，如下圖所示。對端 ES 的 mapping 結構按照如式定義。數據同步工具直接將該保函關聯表數據的訂單表直接同步到對端 ES ，即可在 ES 上搜索合我們需求的數據。
<img src=http://www.51docs.com/jcxzx/"https://p5.toutiaoimg.com/large/pgc-image/SSND0Hi7wlQx7z" }, } } }}
優點：
處理能應對各種一對多的關聯關系，對數據同步工具的功能要求低，配置簡單，只需要支持單表同步到 ES 即可。
缺點：
索引、搜索性能非更佳：提供給 ES 的不是預構建好的寬表數據。例如例子中，訂單關聯的商品信息，全部存儲在主表的一個object/nested/join 字段內，這種實現方式會有索引、搜索性能方面的額外開銷，不是性能更佳的實現方式
業務系統侵入：業務系統寫主數據的時候需要額外寫入信息
主數據庫表冗余過多數據：關系型數據庫的表冗余了過多其他表 ，可能存在存儲和性能開銷
總結
不太推薦該方式
多表訂閱合并預構建寬表數據
數據同步工具同時訂閱搜索時依賴的所有表，先到的數據先進到 ES，沒有數據過來的字段為空。以上面提到的訂單和商品表的例子來說，即同時同步訂單表和商品表到對端索引。對端索引的 mapping 定義如下所示， 訂單和商品表的所有字段，定義的索引是一張寬表。流計算中多流匯聚配合時間窗口 join 多表的方式與該種方式有異曲同工之處。
優點：
數據同步工具配置同步任務較為簡單，無業務入侵，不耦合業務系統邏輯
對數據同步工具要求低，除了同步以外，不需要其他額外的功能特性
基于預構建寬表的方式在 ES 上也有較好的索引和查詢性能。
同步鏈路不會因為寬表某些列缺失數據阻塞整個數據鏈路的同步(是否有該優點取決于數據同步工具本身設計，如果引入時間窗口，則同步鏈路會因為等待列數據影響同步時效性)。
缺點：
基于事實表主動觸發式的方式來進行寬表的構建。源端訂閱的表，如果更新很少或者從來不更新產生 binlog，則對端的文檔中的列值可能一直不完整，導致時效性會比較差。搜索的時候有一些列的數據會缺少
總結
適合構成寬表的事實表數據寫入有事務保證一起落盤的場景，這樣可以避免對端ES搜索到不完整的數據。
適合構建寬表不需要業務加工處理的場景，構建寬表只是單純的將多張表的列拼接在一起，形成寬表。
{ “mappings”: { “_doc”: { “properties”: { “order_id”: { “type”: “long” }, “order_price”: { “type”: “long” }, “product_count”: { “type”: “long” }, “discount”: { “type”: “long” }, “product_id”: { “type”: “long” }, “product_unit_price”: { “type”: “long” }, “product_name”: { “type”: “text” }, } } }}同步過程回查預構建
數據同步工具訂閱的表稱為主表。數據同步過程中，反查數據庫查詢的表稱為從表。利用數據同步工具自身的能力，在訂閱主表期間，自動通過回查的方式，填補寬表中的列，形成完整的寬表行數據。對端 ES 的mapping 定義例子與“多表訂閱合并預構建寬表數據”中的相同。
優點：
基于反查的方式構建寬表靈活性好，可以在生成寬表前基于主表的數據對從表數據做一些輕度的數據加工
一條主表的數據，通過反查生成寬表行，可以配合數據加工生成多條寬表行數據
基于反查的方式可以比較的實現跨實例的 join ，從而生成寬表行(相對好實現，具體要看數據同步工具本身是否支持)
基于寬表預構建的方式在 ES 上有較好的索引、查詢性能。
缺點：
反查時數據可能沒有準備好，導致數據缺失（這里具體的影響取決于數據同步工具本身設計，可以引入時間窗口配合超時等待，也可以沒有數據時直接同步到對端）
需要數據同步工具在數據反查、數據加工方面進行支持
總結
對于構建寬表涉及數據加工的場景，該方式比較適合。
由于該方式的回查機制、預構建前數據加工的能力支持，能力上是“多表訂閱合并預構建寬表數據”這種方式的超集。如果有比較好的數據同步工具支持，這種方式是比較推薦的。
數據遷移同步工具選型
數據遷移同步工具的選擇比較多樣，下表僅從 MySQL 同步 ES 這個場景下，對一些筆者深度使用研究過的數據同步工具進行對比(不一定精確，如有錯誤請 筆者更正)，用戶可以根據自己的實際需要選取適合自己的產品。
特性\產品
C
DTS
CloudC
是否支持自建ES
是
否
是
ES對端版本支持豐富度
中
支持ES6和ES7
高
支持ES5,ES6和ES7
中
支持ES6和ES7
嵌套類型支持
join/nested/object
object
nested/object
join支持方式
基于join父子文檔反查
無
基于寬表預構建反查
是否支持結構遷移
否
是
是
是否支持全量遷移
是
是
是
是否支持增量遷移
是
是
是
數據過濾能力
中
僅全量可添加where條件
高
全增量階段where條件
高
全增量階段where條件
是否支持時區轉換
否
是
是
同步限流能力
無
有
有
任務編輯能力
無
有
無
數據源支持豐富度
中
高
中
架構
訂閱消費
需先寫入消息隊列
直連
直連
指標豐富度
中
性能指標
中
性能指標
高
性能指標、指標
能力
無
針對延遲、異常的
針對延遲、異常的釘釘、、郵件
任務可視化創建配置管理能力
無
有
有
是否開源
是
否
否
是否免費
是
否
是
社區版、SAAS版免費
是否支持獨立輸出
是
否
依賴云整體輸出
是
是否支持SAAS化使用
否
是
是
寫在最后
MySQL 到 ES 數據同步構建數據檢索服務給中小企業帶來了穩定且實用的在線數據方案，在滿足業務訴求(高并發業務與企業級應用常態化)的同時 ，易上手且具備不錯的可維護性，在適當的場景下，值得嘗試和實踐。
最后感謝各位的閱讀，內容相對淺顯且直接，希望對你有所幫助和啟發。在此也簡單介紹下筆者自己，本人在阿里巴巴中間件和云智能團隊從事過幾年數據相關工作，在該領域具備一定的經驗，對這個方向感興趣的朋友可以一起探討相關技術問題，我們專門開設了一個問答社區 askcug.com 以便大家探討，歡迎加入探討。
參考資料
[1]：Database normalization
[2]：Denormalization
[3] When and How You Should Denormalize a Relational Database
[4] 愛奇藝|海量數據實時分析服務技術架構演進
[5] 從 ES 到 Kylin，斗魚客戶端性能分析進化之旅
[6] 常見開源OLAP技術架構對比
[7] Elasticsearch:Tune for search speed
[8] Elasticsearch:Field data types
[9] Designing Data-Intensive Applications
[10] Materialized Views
[11] A Relational Model of Data for Large Shared Data Banks
參考閱讀
不要以 DRY 之名，發明低代碼 DSL 去殘害你的同事
如何編寫 C++ 20 協程(Coroutines)
領域驅動設計(DDD)在愛奇藝打賞業務的實踐
Redis 日志篇：無畏宕機實現高可用的锏
喜馬拉雅自研 關架構演進過程
技術 及架構實踐 ，歡迎通過 菜單「 我們」進行投稿。
高可用架構改變互聯 的構建方式
以上就是與免費五行缺失查詢表相關內容，是關于數據遷移的分享。看完免費生辰八字五行分析后，希望這對大家有所幫助！
