我司系統中有一個專門存車流量的庫(沒有主鍵)，其中一個歷史表資料量太大，表空間佔據太大，每天有 500w 的資料寫入。
圖片來自 Pexels
詳細需求
表空間和資料量如下圖：
實現思路
實現做法流程，如圖：
實現虛擬碼(刪減了部分程式碼)：
/** * 轉移資料 每天凌晨3點 每次只能轉移一天的資料 */ @Scheduled(cron = "0 0 3 * * ?") public void dataTransfer()throws Exception{ System.out.println("定時器開始執行------------------------------------------"); String tabaleName = "XXX"; String isTable = getTableName(tabaleName); // 當返回為空時,代表該表不存在,則建立 if(ObjectUtils.isNull(isTable)){ createHistoryDate(names); } // 得到最遠的時間段 Map<String, Object> orderTime = orderByTime(); // 得到開始和結束時間 if(SysFun.isNotEmpty(orderTime) && orderTime.size() > 0){ orderTime.put("startTime",startTime); orderTime.put("endTime",endTime); orderTime.put("tableName",tabaleName); int i=0; for (;;) { System.out.println("進入迴圈"); // 轉移資料 int rst = dataTransfer(orderTime); // 刪除重複資料 int delt = deleteDataTransfer(orderTime); // 當今天資料轉移完成時,退出本次迴圈 if(rst<=0 && delt <=0){ break; } i++; System.out.println("轉移資料表為："+tabaleName+" 轉移資料次數: "+i); } } System.out.println("定時器結束執行------------------------------------------"); } 心路歷程
方法完成之後，上週五去伺服器正式實測，實測時方法用 @PostConstruct 修飾，會在伺服器載入 Servlet 的時候執行，並且只會被伺服器執行一次。
當時控制檯列印：(“定時器開始執行”)卡住，去庫中看到表已成功建立。
開始以為是某個地方異常了，後面逐一列印步驟發現是得到最遠時間段是卡住了，也就是被一條 SQL 查詢卡住了(直接用這條 SQL 去庫裡查詢 300s+ 也沒查詢出來)。
然後維護這個庫的小夥跟我說：要不直接 limit 1 吧，它的插入是根據時間順序插入的，當時也想到了會出問題，時間順序肯定不可能完全按照順序寫入。
週末程式走了 2 天果然有問題，如圖：
limit 1 是行不通了，那就只能來查詢優化了，講查詢最佳化之前，先說說為什麼我們使用ORDER BY 為什麼會這麼慢?
深入分析
MySQL 有兩種方式可以實現 ORDER BY 這裡只做簡單介紹：
①透過索引掃描生成有序的結果 舉個例子。
假設 history 表有 id 欄位上有主鍵索引，且 id 目前的範圍在 1001-1006 之間，則 id 的索引 B+Tree 如下：
現在當我們想按照 id 從小到大的順序中取出資料時，執行以下 SQL：
select * from history order by id MySQL 會直接遍歷上圖 id 索引的葉子節點連結串列，不需要進行額外的排序操作。這就是用索引掃描來排序。
②使用檔案排序(filesort)
但如果 id 欄位沒有任何索引，上圖的 B+Tree 結構不存在，MySQL 就只能先掃表篩選出符合條件的資料，再將篩選結果根據 id 排序。這個排序過程就是 filesort。
我們要讓 ORDER BY 字句使用索引來避免 filesort(用“避免”可能有些欠妥，某些場景下全表掃描、filesort 未必比走索引慢)，以提高查詢效率。
進行最佳化之前我們還需要學會看 SQL 的執行計劃(EXPLAIN)分別為(這裡著重講解 type、rows、Extra，其它的這裡不做講解，可自己私下進行了解)：
id、select_type、table、type、possible_keys、key、key_len、ref、rows、Extra type：對錶訪問方式，表示 MySQL 在表中找到所需行的方式，又稱“訪問型別”。
MySQL 找到資料行的方式，效率排名 NULL>system>const>eq_ref>ref>range>index>All。
range 只檢索給定範圍的行，使用一個索引來選擇行，一般是在 where 中出現 between、<、>、in 等查詢，範圍掃描好於全表掃描。
index Full Index Scan，Index 與 All 區別為 index 型別只遍歷索引樹。
通常比 All 快，因為索引檔案通常比資料檔案小。也就是說，雖然 all 和 index 都是讀全表，但是 index 是從索引中讀取的，而 all 是從硬碟讀取的。
ALL Full Table Scan，將遍歷全表以找到匹配的行。
rows：根據表統計資訊及索引選用情況，大致估算出找到所需的記錄所需要讀取的行數，也就是說，用的越少越好。
extra：包含不適合在其他列中顯式但十分重要的額外資訊。
Using Index：表示相應的 select 操作中使用了覆蓋索引(Covering Index)，避免訪問了表的資料行，效率不錯。
如果同時出現 using where，表明索引被用來執行索引鍵值的查詢;如果沒有同時出現 using where，表明索引用來讀取資料而非執行查詢動作。
Using filesort：當 Query 中包含 order by 操作，而且無法利用索引完成的排序操作稱為“檔案排序”。
下面我們透過一張真實資料圖來分析：
PS：由於正式庫的未最佳化之前的執行計劃圖片忘記儲存，這裡用本地環境代替一下，內容相同 執行 SQL：
EXPLAIN SELECT updateTime FROM historydata ORDER BY updateTime LIMIT 1 透過我們那種查詢是沒有辦法命中索引的，遵循最左原則，為 updateTime 新建一個普通索引(index)NORMAL：
新增索引的過程中再提一嘴：因為這個歷史庫時時刻刻都有資料寫入，所以我當時建索引時擔心鎖表。
後面查詢相關資料就知道了，MySQL 5.6 之後的版本不影響讀寫，不會鎖表，前提儲存引擎為 InnoDB，MyISAM 加索引鎖表，讀寫會全部堵塞。
如果表資料量過多，可能建立索引的時間會過長，以我舉例 6000w 差不多建了 4h，下面為索引效果圖：
結尾