十億級(jí)訂單系統(tǒng)的數(shù)據(jù)庫(kù)查詢性能優(yōu)化之路

作者：京東零售 崔健

0.前言

?系統(tǒng)概要：BIP采購(gòu)系統(tǒng)用于京東采銷(xiāo)部門(mén)向供應(yīng)商采購(gòu)商品，并且提供了多種創(chuàng)建采購(gòu)單的方式以及采購(gòu)單審批、回告、下傳回傳等業(yè)務(wù)功能

?系統(tǒng)價(jià)值：向供應(yīng)商采購(gòu)商品增加庫(kù)存，滿足庫(kù)存周轉(zhuǎn)及客戶訂單的銷(xiāo)售，供應(yīng)鏈最重要的第一環(huán)節(jié)

1.背景

采購(gòu)系統(tǒng)在經(jīng)歷了多年的迭代后，在數(shù)據(jù)庫(kù)查詢層面面臨巨大的性能挑戰(zhàn)。核心根因主要有以下幾方面：

?復(fù)雜查詢多，歷史上通過(guò)MySQL和JED承載了過(guò)多的檢索過(guò)濾條件，時(shí)至今日很難推動(dòng)接口使用方改變調(diào)用方式

?數(shù)據(jù)量大，隨著業(yè)務(wù)的持續(xù)發(fā)展，帶來(lái)了海量的數(shù)據(jù)增長(zhǎng)（日均150萬(wàn)單左右，訂單主表/子表/渠道表/擴(kuò)展表分別都是：6.5億行，訂單明細(xì)表/分配表：9.2億行，日志表：60億行）

?數(shù)據(jù)模型復(fù)雜，訂單完整數(shù)據(jù)分布在20+張表，經(jīng)常需要多表join

引入的主要問(wèn)題有：

?業(yè)務(wù)層面：

?訂單列表頁(yè)查詢/導(dǎo)出體驗(yàn)差，性能非常依賴(lài)輸入條件，尤其是在面對(duì)訂單數(shù)據(jù)傾斜的時(shí)候，部分用戶無(wú)法查詢/導(dǎo)出超過(guò)半個(gè)月以上的訂單

?查詢條件不合理，1.歸檔篩選條件，技術(shù)詞匯透?jìng)鞯綐I(yè)務(wù)，導(dǎo)致相同周期的單子無(wú)法一鍵查詢/導(dǎo)出，需要切換“是否歸檔”查詢?nèi)浚?.無(wú)法區(qū)分“需要倉(cāng)庫(kù)收貨”類(lèi)的單子，大部分業(yè)務(wù)同事主要關(guān)注這類(lèi)單子的履約情況

?技術(shù)層面：

?慢SQL多，各種多表關(guān)聯(lián)復(fù)雜條件查詢導(dǎo)致，索引、SQL已經(jīng)優(yōu)化道了瓶頸，經(jīng)常出現(xiàn)數(shù)據(jù)庫(kù)負(fù)載被拉高

?大表多，難在數(shù)據(jù)庫(kù)上做DDL，可能會(huì)引起核心寫(xiě)庫(kù)負(fù)載升高、主從延遲等問(wèn)題

?模型復(fù)雜，開(kāi)發(fā)、迭代成本高，查詢索引字段散落在多個(gè)表中，導(dǎo)致查詢性能下降

2.目標(biāo)

業(yè)務(wù)層面：提升核心查詢/導(dǎo)出體驗(yàn)，加強(qiáng)查詢性能，優(yōu)化不合理的查詢條件

技術(shù)層面：1.減少慢SQL，降低數(shù)據(jù)庫(kù)負(fù)載，提高系統(tǒng)穩(wěn)定性；2.降低單表數(shù)據(jù)量級(jí)；3.簡(jiǎn)化數(shù)據(jù)模型

3.挑戰(zhàn)

提升海量數(shù)據(jù)、復(fù)雜場(chǎng)景下的查詢性能！

?采購(gòu)訂單系統(tǒng) VS C端銷(xiāo)售訂單系統(tǒng)復(fù)雜度對(duì)比：

4.方案

思路

優(yōu)化前

優(yōu)化后

4.1 降低查詢數(shù)據(jù)量

4.1.1 前期調(diào)研

基于歷史數(shù)據(jù)、業(yè)務(wù)調(diào)研分析，采購(gòu)訂單只有8%的訂單屬于“需要實(shí)際送貨至京東庫(kù)房”的范圍，也就是擁有完整訂單履約流程、業(yè)務(wù)核心關(guān)注時(shí)效的。其余訂單屬于通過(guò)客戶訂單驅(qū)動(dòng)，在采購(gòu)系統(tǒng)的生命周期只有創(chuàng)建記錄

基于以上結(jié)論，在與產(chǎn)品達(dá)成共識(shí)后，提出新的業(yè)務(wù)領(lǐng)域概念：“入庫(kù)訂單”，在查詢時(shí)單獨(dú)異構(gòu)這部分訂單數(shù)據(jù)（前期也曾考慮過(guò)，直接從寫(xiě)入層面拆分入庫(kù)訂單，但是因?yàn)殚_(kāi)發(fā)成本、改動(dòng)范圍被pass了）。異構(gòu)這部分?jǐn)?shù)據(jù)實(shí)際也參考了操作系統(tǒng)、中間件的核心優(yōu)化思路，緩存訪問(wèn)頻次高的熱數(shù)據(jù)

4.1.2 入庫(kù)訂單異構(gòu)

執(zhí)行流程

?“入庫(kù)”訂單數(shù)據(jù)打標(biāo)

?增量訂單在創(chuàng)建訂單完成時(shí)寫(xiě)入；存量訂單通過(guò)離線表推數(shù)

?需要訂單創(chuàng)建模塊先完成改造上線，再同步歷史，保證數(shù)據(jù)不丟

?如果在【數(shù)據(jù)解析模塊】處理binlog時(shí)無(wú)法及時(shí)從JimKV獲取到訂單標(biāo)識(shí)，會(huì)補(bǔ)償反查數(shù)據(jù)庫(kù)并回寫(xiě)JimKV，提升其他表的binlog處理效率

?binlog監(jiān)聽(tīng)

?基于公司的【數(shù)據(jù)訂閱】任務(wù)，通過(guò)消費(fèi)JMQ實(shí)現(xiàn)。其中訂閱任務(wù)基于訂單號(hào)進(jìn)行MQ數(shù)據(jù)分區(qū)，并且在消費(fèi)端配置不允許消息重試，防止消息時(shí)序錯(cuò)亂

?其中，根據(jù)訂單號(hào)進(jìn)行各個(gè)表的MQ數(shù)據(jù)分區(qū)，第一版設(shè)計(jì)可能會(huì)引起熱分區(qū)，導(dǎo)致消費(fèi)速率變慢，基于這個(gè)問(wèn)題識(shí)別到熱分區(qū)主要是由于頻繁更新訂單明細(xì)數(shù)據(jù)導(dǎo)致（訂單(1)->明細(xì)(N)），于是將明細(xì)相關(guān)表基于自身id進(jìn)行分區(qū)，其他訂單緯度表還是基于訂單號(hào)。這樣既不影響訂單數(shù)據(jù)更新的先后順序，也不會(huì)造成熱分區(qū)、還可以保證明細(xì)數(shù)據(jù)的準(zhǔn)確性

?數(shù)據(jù)同步

?增量數(shù)據(jù)同步可以采用源庫(kù)的增量binlog進(jìn)行解析即可，存量數(shù)據(jù)通過(guò)申請(qǐng)新庫(kù)/表，進(jìn)行DTS的存量+增量同步寫(xiě)入，完成binlog生產(chǎn)

?以上是在上線前的臨時(shí)鏈路，上線后需要切換到源庫(kù)同步binlog的增量訂閱任務(wù)，此時(shí)依賴(lài)“位點(diǎn)回?fù)堋?“數(shù)據(jù)可重入”。位點(diǎn)回?fù)芑谟嗛喨蝿?wù)的binlog時(shí)間戳，數(shù)據(jù)可重入依賴(lài)上文提到的MQ消費(fèi)有序以及SQL覆蓋寫(xiě)

?數(shù)據(jù)校對(duì)

?以表為緯度，優(yōu)先統(tǒng)計(jì)總數(shù)，再隨機(jī)抽樣明細(xì)進(jìn)行比對(duì)

?目前入庫(kù)訂單量為穩(wěn)定在5000萬(wàn)，全部實(shí)時(shí)訂單量級(jí)6.5億，降低92%

4.2 提升復(fù)雜查詢能力

4.2.1 數(shù)據(jù)準(zhǔn)備

?考慮到異構(gòu)“入庫(kù)”訂單到JED，雖然數(shù)據(jù)查詢時(shí)效性可以有一定保障，但是在復(fù)雜查詢能力以及識(shí)別“非入庫(kù)”訂單還沒(méi)有支持

?其中，“非入庫(kù)”訂單業(yè)務(wù)對(duì)于訂單數(shù)據(jù)時(shí)效性要求并不高（1.訂單創(chuàng)建源于客戶訂單；2.沒(méi)有履約流程；3.無(wú)需手動(dòng)操作訂單關(guān)鍵節(jié)點(diǎn)）

?所以，考慮將這部分查詢能力轉(zhuǎn)移到ES上

ES數(shù)據(jù)異構(gòu)過(guò)程

?首先，同步到ES的數(shù)據(jù)的由“實(shí)時(shí)+歸檔”訂單組成，其中合計(jì)20億訂單，順帶優(yōu)化了先前歸檔ES大索引（所有訂單放在同一個(gè)索引）的問(wèn)題，改成基于“月份”存儲(chǔ)訂單，之所以改成月份是因?yàn)楦鶕?jù)條件查詢分兩種：1.一定會(huì)有查詢時(shí)間范圍（最多3個(gè)月）；2.指定單號(hào)查詢，這種會(huì)優(yōu)先檢索單號(hào)對(duì)應(yīng)的訂單創(chuàng)建時(shí)間，再路由到指定索引

?其次，簡(jiǎn)化了歸檔程序流程，歷史方案是程序中直接寫(xiě)入【歸檔JED+歸檔ES】，現(xiàn)在優(yōu)化成只寫(xiě)入JED，ES數(shù)據(jù)通過(guò)【數(shù)據(jù)解析模塊】完成，簡(jiǎn)化歸檔程序的同時(shí)，提高了歸檔能力的時(shí)效性

?再次，因?yàn)镋S是存儲(chǔ)全量訂單，需要支持復(fù)雜條件的查詢，所以在訂單沒(méi)有物理刪除的前提下，【數(shù)據(jù)解析模塊】會(huì)過(guò)濾所有delete語(yǔ)句，保證全量訂單數(shù)據(jù)的完整性

?接著，為了提升同步到ES數(shù)據(jù)的吞吐，在MQ消費(fèi)端，主要做了兩方面優(yōu)化：1.會(huì)根據(jù)表和具體操作進(jìn)行binlog的請(qǐng)求合并；2.降低對(duì)于ES內(nèi)部refresh機(jī)制的依賴(lài)，將2分鐘內(nèi)更新到ES的數(shù)據(jù)緩存到JimKV，更新前從緩存中獲取

?最后，上文提到，同步到入庫(kù)JED，有的表是根據(jù)訂單號(hào)，有的表是根據(jù)自身id。那么ES這里，因?yàn)镹oSQL的設(shè)計(jì)，和線程并發(fā)的問(wèn)題，為了防止數(shù)據(jù)錯(cuò)誤，只能將所有表數(shù)據(jù)根據(jù)單號(hào)路由到相同的MQ分區(qū)

4.2.2 查詢調(diào)度策略設(shè)計(jì)

優(yōu)化前，所有的查詢請(qǐng)求都會(huì)直接落到數(shù)據(jù)庫(kù)進(jìn)行查詢，可以高效查詢完全取決于用戶的篩選條件是否可以精準(zhǔn)縮小數(shù)據(jù)查詢范圍

優(yōu)化后，新增動(dòng)態(tài)路由層

?離線計(jì)算T-1的采銷(xiāo)/供應(yīng)商的訂單數(shù)據(jù)傾斜，將數(shù)據(jù)傾斜情況推送到JimDB集群

?根據(jù)登陸用戶、數(shù)據(jù)延遲要求、查詢數(shù)據(jù)范圍，自動(dòng)調(diào)度查詢的數(shù)據(jù)集群，實(shí)現(xiàn)高性能的查詢請(qǐng)求

查詢調(diào)度

5.ES主備機(jī)制&數(shù)據(jù)監(jiān)控

1.主/備ES可以通過(guò)DUCC開(kāi)關(guān)，實(shí)現(xiàn)動(dòng)態(tài)切換，提升數(shù)據(jù)可靠性

2.結(jié)合公司的業(yè)務(wù)監(jiān)控，完成訂單數(shù)據(jù)延遲監(jiān)控（數(shù)據(jù)同步模塊寫(xiě)入時(shí)間-訂單創(chuàng)建時(shí)間）

3.開(kāi)啟消息隊(duì)列積壓告警

5.1 ES集群主/備機(jī)制

1：1ES集群進(jìn)行互備，應(yīng)急預(yù)案快速切換，保證高可用

5.2 數(shù)據(jù)監(jiān)控

6.灰度上線

?第一步，優(yōu)先上線數(shù)據(jù)模塊，耗費(fèi)較多時(shí)間的原因：1.整體數(shù)據(jù)量級(jí)以及歷史數(shù)據(jù)復(fù)雜度的問(wèn)題；2.數(shù)據(jù)同步鏈路比較長(zhǎng)，中間環(huán)節(jié)多

?第二步，預(yù)發(fā)環(huán)境驗(yàn)證，流量回放并沒(méi)有做到長(zhǎng)周期的完全自動(dòng)化，根因：1.項(xiàng)目周期相對(duì)緊張；2.新老集群的數(shù)據(jù)還是有一些區(qū)別，回放腳本不夠完善

?第三步，用戶功能灰度，主要是借助JDOS的負(fù)載均衡策略結(jié)合用戶erp完成

?第四部，對(duì)外接口灰度，通過(guò)控制新代碼灰度容器個(gè)數(shù)，逐步放量

7.成果

平穩(wěn)切換，無(wú)線上問(wèn)題

8.待辦

?主動(dòng)監(jiān)控層面，新增按照天緯度進(jìn)行數(shù)據(jù)比對(duì)、異常告警的能力，提高問(wèn)題發(fā)現(xiàn)率

?優(yōu)化數(shù)據(jù)模型，對(duì)歷史無(wú)用訂單表進(jìn)行精簡(jiǎn)，降低開(kāi)發(fā)、運(yùn)維成本，提升需求迭代效率

?精簡(jiǎn)存儲(chǔ)集群

?逐步下線其他非核心業(yè)務(wù)存儲(chǔ)集群，減少外部依賴(lài)，提高系統(tǒng)容錯(cuò)度

?目前全量訂單ES集群已經(jīng)可以支持多場(chǎng)景的外部查詢，未來(lái)考慮是否可以逐步下線入庫(kù)訂單JED

?識(shí)別數(shù)據(jù)庫(kù)隱患，基于慢日志監(jiān)控，重新梳理引入模塊，逐步優(yōu)化，持續(xù)降低數(shù)據(jù)庫(kù)負(fù)載

?MySQL減負(fù)，探索其他優(yōu)化方案，減少數(shù)據(jù)量存儲(chǔ)，提升數(shù)據(jù)靈活性。優(yōu)先從業(yè)務(wù)層面出發(fā)，識(shí)別庫(kù)里進(jìn)行中的僵尸訂單的根因，進(jìn)行分類(lèi)，強(qiáng)制結(jié)束

?降級(jí)方案，當(dāng)數(shù)據(jù)同步或者數(shù)據(jù)庫(kù)存在異常時(shí)，可以做到秒級(jí)無(wú)感切換，提升業(yè)務(wù)可用率

9.寫(xiě)在最后

?為什么沒(méi)考慮Doris？因?yàn)镋S是團(tuán)隊(duì)?wèi)?yīng)用相對(duì)成熟的中間件，處于學(xué)習(xí)、開(kāi)發(fā)成本考慮

?未來(lái)入庫(kù)的JED相關(guān)表是否可以下掉，用ES完全替代？目前看可以，當(dāng)初設(shè)計(jì)冗余入庫(kù)JED也是出于對(duì)于ES不確定性以及數(shù)據(jù)延遲的角度考慮，而且歷史的一部分查詢就落在了異構(gòu)的全量實(shí)時(shí)訂單JED上。現(xiàn)在，JED官方也不是很推薦非route key的查詢。最后，現(xiàn)階段因?yàn)榻档土藬?shù)據(jù)量和拆分了業(yè)務(wù)場(chǎng)景，入庫(kù)JED的查詢性能還是非常不錯(cuò)的

?因?yàn)轫?xiàng)目排期、個(gè)人能力的因素，在方案設(shè)計(jì)上會(huì)有考慮不周的場(chǎng)景，本期只是優(yōu)化了最核心的業(yè)務(wù)、技術(shù)痛點(diǎn)，未來(lái)還有很大持續(xù)優(yōu)化的空間。中間件的使用并不是可以優(yōu)化數(shù)據(jù)庫(kù)性能的銀彈，最核心的還是要結(jié)合業(yè)務(wù)以及系統(tǒng)歷史背景，在不斷糾結(jié)當(dāng)中尋找balance

審核編輯 黃宇