# 項目1 根據年份分表, 每表1千萬左右 這個項目是接手另一個同事的二期項目, 二期新開發未上線, 預估(站點如果增加, 這個也會更大)數據每年1千萬左右 業務: 分為按月和按年查詢, 按年查詢就是全表查, 目前問題是整個表就30萬數據, 按月查詢時20多萬數據, 返回給前端用時超過20s, 按年查詢1千萬直接超時。 優化：使用 Java + SQL 對功能實現流程改造后，按月查詢1s內， 按年查詢17s。和公司業務太相關這里不去敘述了。 主要是之前返回太多字段，而且 按年查詢是全表查詢，返回的數據量也太多。后面是減少返回字段，使用覆蓋索引，同時使用max和group by 將返回數據量降低90多倍， 其他功能java再循環處理 再次優化：從 17 s 優化到 5s， 重點是優化了group by 使用 EXPLAIN 發現 type 是 index, 但是 extra 有這倆 ``` Using temporary; Using filesort ``` 經過排查 group by 使用了 date_format 導致的使用了臨時表, 而且聯合索引里也排序了 去掉 date_format 和排序 extra 則沒有這倆， 速度也大幅提高了， 大部分時候都是1秒內，加上網絡傳輸時間是2秒左右 解決流程： 1. 優化SQL，把沒必要的字段去掉，同時將查詢條件和返回值做成聯合索引，這樣就能使用MySQL覆蓋索引，同時使用max、count和group by 將返回數據量降低90多倍（每個設備每日就返回一條了）， 其他功能java再循環處理。 * max函數結果有2表示此設備本日有失敗，還有一個下載成功的數量，這個就可以確認設備每日下載狀態，這樣每個設備每天查詢一條數據就行 3. 像設備名稱這種，直接把所有設備緩存到Redis，返回時從Redis集合獲取名稱就行，減少連表，其他SQL不好解決的，也放到Java里 4. 專門寫一個類,把無關的字段全部刪除,減少返回的無效字段 使用 EXPLAIN 發現 type 是 index, 但是 extra 有這倆 Using temporary; Using filesort 經過排查 group by 使用了 date_format 導致的使用了臨時表, 而且聯合索引里也排序了，再次優化去掉date_format 和 排序, 將這些放到Java去處理了, 速度再次提升 最終結果是： 按月查詢全部站， 大部分時候都是1秒內，加上網絡傳輸時間接口是2秒左右 按年查詢全部站， 大部分時候都是17秒內，加上網絡傳輸時間接口是25-30秒了 按年查詢由于是全表查詢， 而且也不常用， 沒有再優化， 再優化是打算弄個統計表，但是由于有回補功能，改動比較大，就沒處理。 # 項目2 分頁查詢太慢, 數據2千萬,并且日增5萬 這個業務就是個普通查詢, 目前核心問題是要處理深度分頁, 也有走聯合索引, 返回的數據太多, 沒法使用覆蓋索引。 未處理深度分頁時, 查詢同一頁數據是 104s, 處理后是6秒 就這樣一個簡單查詢也要3s查詢（key_len 4, type = index） ``` SELECT id FROM 表 LIMIT 17323100, 10; ``` 實際我們會根據索引字段去倒序 這樣查詢也要5s查詢（key_len 13, type = index） ``` SELECT id FROM 表 ORDER BY 索引字段1 desc LIMIT 17323100, 10; ``` 目前是先使用類似上面的語句查詢到主鍵id，然后返回給Java，Java再in去查詢返回所有字段到前端， 速度也是大幅度提升 也可以一條SQL直接返回， 不能直接 in，需要嵌套一層， 類似 ``` SELECT 所有字段 FROM 表1 WHERE id IN ( SELECT t.id FROM ( SELECT id FROM 表1 WHERE is_delete = '0' ORDER BY 索引字段1 DESC, id DESC LIMIT 17322210, 10 ) AS t ) ```