從MySQL大量資料清洗到TiBD說起
一、業務場景：
公司主要做的業務是類似貝殼的二手房租售，資料庫中存了上億級別的房源資料，之前資料庫使用的是 mysql，後面需要將 mysql 資料庫切換成了 Tidb，在切換的過程中，需要將老庫的資料經過資料清洗後再存入新庫（因為有一些表結構的設計變了），其中我們處理的一個邏輯就是將房間下業主資訊從老庫清洗到新庫：我們需要按照城市維度，查詢新庫所有的房間，然後拿著新老庫的房間對應關係，再到老庫中找到所有對應的房間，然後通過房間再找到每個房間對應的業主資訊，最後將業主的不同維度資訊（一共5個維度資訊）清洗到新庫的不同資料表中。下面我就簡單描述一下資料清洗過程中遇到的各種問題以及解決方案，所有問題都會附上真實的案例和說明
二、從問題入手選定處理方案：
問題：
1、在清洗過程工，我們無法將某個城市的幾百萬甚至上千萬的房間資訊一次性查詢出來，再去找所有房間的業主資訊，這樣記憶體肯定會撐爆；
2、資料清洗過程中肯定不能一條一條的新增資料，這樣的話幾百萬（舉例300W）的房間資料，有5個維度需要新增資料，那麼就會一條一條的新增300W房間對應維度的資料，就會操作 300W*5 次，效率低下；
3、資料清洗後批量插入新表的時候也不能一次性插入300W（每個表插入300W，5個維度分別插入5個表，即插入5次300W的資料）。
處理方案：
先查詢出需要清洗的資料總量，然後按照某個量（比如：1000條）進行分頁查詢出具體的資料，然後清洗這1000條房間對應維度的資料並插入新庫中，再清洗下一個1000條資料，直到把所有資料清洗完成。
三、按照上面選定的處理方案，分頁進行清洗資料
在上面處理方案出來後，下面就是在程式開發過程中遇到的一些具體問題，分頁查詢的邏輯其實就是常用的 SQL 的 limit m, n，通過 page 和 pageSize 來進行分頁查詢，再使用 limit m,n 進行分頁查詢的時候又遇到下面幾個問題：
a、分頁查詢查詢和處理新增資料，按照多大的量來進行分頁查詢，是一次性查詢5000條房間還是1000條房間來處理對應資料的清洗，使得查詢和處理的效率最高效？
b、如果一次性查詢和處理較少的資料量，比如每次分頁查詢出100條資料來進行清洗，如果某城市有800W的資料，分頁查詢需要查詢處理80000次，這個處理次數是否過多？
c、使用常規的 limit m,n 的方式進行分頁查詢，那麼越查詢到靠後的頁數（ limit m,n 語句的查詢時間與起始記錄的 m 位置成正比）查詢就會變得越慢，如何處理？
解決方案：
注：下面所有的資料都是在公司的機器上面得出的效率資料，大家在使用的時候以實際為準，這裡只是提供解決思路
1、下面先附上一張我們和DBA的聊天來引出問題：
公司大量業務都開始使用TiDB，很多資料都需要從MySQL遷移到TiDB，在遷移過程中，批量新增都會遇到一個問題，就是隨著批量新增的資料量變大，耗時巨慢，DBA說的是100條以內就非常快，那麼這個條數多少條對於我們業務處理是最合理的啦？下面就是一個論證過程。
下面直接上資料，後面會對資料進行說明：
關注（注意橫縱座標的含義）：
批量新增409條資料變成539條資料，耗時卻從0.535變到2.026秒，多了130資料，耗時卻多了1.5秒左右；
批量新增536條資料到689條，多了150條資料，耗時確多了3秒多；
批量新增689條到793條，資料多了104條資料，耗時確多了5秒；
批量新增793條到997條，資料多了204條，耗時確多了17秒。
根據上圖生成的資料耗時效能座標圖，斜率越低說明效能越好。
從圖中粗略的可以看出，在409到539條之間，應該有一個合理的效能保證值，我們姑且認為大概在批量處理500條的時候，效能是一個分水嶺，即：在批量處理500條房間以內對應的資料，效能較好，超過500條後效能開始按照指數增長的方式下降。（注：為了表明資料的真實性，不是我自己瞎編亂造的資料，附上公司資料清洗的幾張 log 日誌圖，用於說明情況。）
批量處理1條房間對應的資料，耗時0.121秒
批量處理54條房間對應的資料，耗時0.262秒
批量處理276條房間對應的資料，耗時0.481秒
批量處理409條房間對應的資料，耗時0.535秒
批量處理539條房間對應的資料，耗時2.026秒
批量處理793條房間對應的資料，耗時10.451秒
批量處理997條房間對應的資料，耗時27.124秒
說明：從日誌可以看出，我們批量新增對應 m 個房間對應的資料，實際上需要處理6個維度（5個業務維度+1個清洗記錄維度）的資料：
1、房間對應【業主檔案維度】；
2、房間對應【檢視電話記錄資訊維度】；
3、房間對應【跟進記錄資訊維度】；
4、房間對應【業主基本資訊維度】；
5、房間對應【業主詳情資訊維度】；
6、房間對應【匯入記錄維度】（防止重複匯入）。
即：一個房間資訊，可能下面沒有聯絡人的資訊，所以該房間就沒有業主檔案，也可能有多個聯絡人，那麼這時就會有業主檔案，並且該業主檔案就對應多個業主資訊（業主詳情資訊要根據查詢看是否存在業主詳情資訊），並且該房間下的業主，如果經濟人跟進維護及時，那麼就會有多條檢視電話記錄資訊和跟進記錄。
所以在查詢1000條的房間資訊的時候，實際匯入資料的效率取決於我們剩下5個業務維度的資料量，在此次文件中我們暫且按照1個房間資訊分別對應1條業務維度來說明，實際業務可以根據自身實際匯入時間來處理。我們來看下面這兩個1000條房間對應998條資料的匯入時間：
從上面也可以看出來，處理998條房間時，對應5個業務維度（業務維度資料欄位較多）都比較耗時，其中批量新增檢視電話記錄耗時10秒+（這個日誌當時沒有記錄插入多少條記錄檢視電話記錄資料，後期優化一下以便更加清楚的檢視插入資料的耗時情況），而批量新增業主檔案資訊也是在1300+條資料，耗時也是在10秒左右。就這個也不難看出，單獨批量插入1000條左右的資料，效能也比較低。
總結如下，在批量新增資料的時候，插入資料的耗時：
（1）和你的業務資料複雜度有關，插入1000條2個欄位肯定比你插入1000條同級別型別的20個欄位資料快很多（大家這時回看上面的所有日誌，從1條到998條，會發現倒數第二行插入imported表的資料都比較快，都在是150毫秒以內，是因為我們imported和業務無關，是用來記錄我們哪些房間資料已經被清洗了，下一次清洗的時候防止重複清洗，所以插入的資料欄位較少，效能從1到998變化不大，但不大並不表示沒有，觀察發現隨著資料量增多耗時也在增多，如果單獨統計imported的批量新增效能變化點可能是在5000，也可能是在8000，但是這個對於我們業務沒有意義，也不是我們這個清洗的瓶頸點，所有我們整篇討論是建立處理對應N條房間的維度，而不是某一個業務維度的耗時，因為單個維度耗時對於我們業務是沒有統計意義，也無法對我們整體資料清洗效能優化提供太多的幫助）。
（2）插入的資料在某個值的時候效能會變低，那麼我們在批量新增的時候儘量不要超過這個值，按照我們業務測試來看是在500條左右。
2、上面討論的是每一次處理，即批量處理大概在多少條資料比較合理，下面討論的就是我們在處理分頁查詢的問題。
在分頁查詢時，我們使用普通的 limit m,n 每次查詢1000條房間資料來處理，整個過程如下：
使用常規的 limit m,n 的方式進行分頁查詢，那麼越查詢到靠後的頁數查詢就會變得越慢（ limit m,n 語句的查詢時間與起始記錄的 m 位置成正比），日誌分頁查詢變慢截圖如下：
從上面幾張圖可以明顯看出，當查詢到後面分頁的資料的時候，耗時明顯增加（這個是最早開始清洗的時候，已經沒有日誌檔案了，截圖是之前和DBA聊天的截圖，所以比較模糊，系統只保留最近一個月的日誌記錄，從截圖看查詢速度還比較快，是因為剛開始清洗到系統的房間較少，所以查詢前幾十頁都是幾百毫秒內）。
那麼如何解決這種查詢啦？那麼我們可以通過主鍵來限制每次查詢的資料集，即後一次查詢的查詢範圍應該排除之前已經查詢過的資料，這種思想有點類似於移動遊標，每次查詢通過主鍵 rid，查詢的範圍保證 rid> m ,這個 m 是上一次查詢記錄的最大值（所以在查詢的時候需要主鍵排序），於是查詢就變成了where rid > m limit 0 , n ,其中 m 就充當了遊標點，通過移動遊標，查詢指定 n 條資料，這時遊標的作用就有兩個：1、定位查詢的資料 2、縮小查詢資料集範圍。
總資料量是3430173條資料，1000條清洗一個批次，需要清洗3431批次，從0開始計數要清洗到3430批次結束
加入遊標後查詢的速度加快，日誌截圖如下（查詢的資料最開始是在6秒左右，是因為現在清洗過來的房間資料已經好1000W+的資料，所以大家看到查詢的資料是從6秒開始）
總結：通過主鍵id值的移動來實現遊標的方式控制查詢的資料集的大小，將查詢耗費時間隨著查詢分頁的後移來而變得越來越短。
3、我們雖然優化了分頁查詢效率的問題，但是從上圖我們不難看出，如果100W的資料量進行分頁查詢還是會經歷1000次的查詢，那麼我們如何解決多次查詢的問題？我們最早的問題告知我們不能一次性查詢百萬或者千萬條資料，因為這樣記憶體吃不消，但是我們換種思路，也不是說一次只能查詢最優分頁查詢的資料量（解決方案第1步中統計出來是500條左右），於是有了下面的演進：
通過記憶體儲存減少查詢次數，我們給到外部的貌似是1000條一頁一頁的查詢，實際我們會一次查詢大於1000條資料，下面以2W舉例，就是分頁查詢的時候我們是一次性查詢2W條資料，然後遍歷每1000條處理一次，直到2W條資料處理完成，在分頁查詢下一個2W條到內初進行處理，依次類推，直到處理完成。
總結：減少查詢次數，不能一次性查詢出上百萬的資料，那我一次性查詢出1W或者2W的資料，然後利用記憶體再將這2W的資料進行1000條按照一批次處理，這樣就將20次分頁查詢變成了1次分頁查詢+20次記憶體運算處理。從而大大加快資料清洗的效率。
四、資料清洗說明
在實際資料清洗過程中，還有很多複雜邏輯，不過都是偏業務層面，沒有分享出來的必要性；其次上面的資料，如各種效能的值需要根據各自業務資料的複雜性自行測試，找到各自效能的最優處理值。
思考：
下面貼一張截止到2021年8月22日靠前的幾個城市的房間資料：
1、由於我們業主檔案列表分頁查詢，查詢過程因為是以房間為維度，關聯專案表、分期表、樓棟表、單元表、業主檔案表（後面還加了7天聯絡記錄）等，從上圖大家可以看到成都、上海的房間資料都超過了700W+的資料，那麼在關聯多個表查詢，固然會存在查詢效率慢的原因，我們除了在SQL層面本身進行優化，是否可以利用到上面的一些思想？比如使用者查詢1、2、3、4...等分頁（假如每頁20展示條）資料，我們是否只需要真實的查詢出第一頁資料（快速響應），然後利用假分頁，如查詢出200條資料放入快取，並設定過期時間，假如使用者翻頁到9頁（一共快取10頁資料）的時候（預估使用者行為，提前到第9頁就進行下一次查詢，防止使用者翻到第10頁在進行查詢花費大量時間），提前把下一個200條資料查詢出來。對於客戶來說，他的分頁是正常分頁，而我們在底層程式碼通過假分頁和真分頁呈現資料，並且因為我每次只快取了200條資料，多個使用者操作，不會影響我快取的記憶體，其次因為我快取資料較少，且都設定過期時間，資料實時性也能得以保障。另外如果有統計功能，其實第一頁也是可以通過分析使用者的查詢行為進行定期快取。這樣就在滿足業務複雜業務需求的同時，保證客戶的使用體驗。
2、資料清洗的量進行動態配置，下面先來兩張圖片說明情況：
這個是某次清洗房間關聯表（新庫房間id和老庫房間id的對應關係）84W+的資料，而其中存在業主檔案的資料量卻只有28974，大概就是每1000個房間有34條對應業主檔案可能需要清洗。
而第二張圖是另外一個城市，房間關聯表只有27171條資料，對應的可能存在業主檔案的資料有22635條，就是大約1000條房間資料有833條對應業主檔案可能需要清洗。
因為我們預設是每1000條房間清洗一次，那麼上面第一種情況每一批次1000條資料只有34條左右的業主檔案被清洗到新庫中，離效能最優點500差的有點遠，故效能有點浪費。第二種情況每一批次1000條資料又有833條左右的業主檔案需要被清洗到新庫中，離效能最優點500也差的有點遠，這時我們這個每批次清洗1000條對應房間的資料就有點設定不太合理，於是我們可以通過這幾個值動態生成需要清洗每批次的資料量，來保證每批次清洗資料在500條左右，使得每次清洗都可以在最優的效率下執行。