閱讀本文大概需要 2 分鐘。
背景
本次SQL優化是針對javaweb中的表格查詢做的。
部分網路架構圖
業務簡單說明
N個機臺將業務資料傳送至伺服器，伺服器程式將資料入庫至MySQL資料庫。伺服器中的javaweb程式將資料展示到網頁上供使用者檢視。
原資料庫設計
1、windows單機主從分離
2、已分表分庫，按年分庫，按天分表
3、每張表大概20w左右的資料
原查詢效率
3天資料查詢70-80s
目標
3-5s
業務缺陷
無法使用sql分頁，只能用java做分頁。
問題排查
前臺慢 or 後臺慢
1、如果你配置了druid，可在druid頁面中直接檢視sql執行時間和uri請求時間
2、在後臺程式碼中用System.currentTimeMillis計算時間差。
結論 ： 後臺慢，且查詢sql慢
sql有什麼問題
1、sql拼接過長，達到了3000行，有的甚至到8000行，大多都是union all的操作，且有不必要的巢狀查詢和查詢了不必要的欄位
2、利用explain檢視執行計劃，where條件中除時間外只有一個欄位用到了索引
備註 ： 因優化完了，之前的sql實在找不到了，這裡只能YY了。
查詢優化
去除不必要的欄位
效果沒那麼明顯
去除不必要的巢狀查詢
效果沒那麼明顯
分解sql
將union all的操作分解，例如(一個union all的sql也很長)
將如上sql分解成若干個sql去執行，最終彙總資料，最後快了20s左右。
將分解的sql非同步執行
利用java非同步程式設計的操作，將分解的sql非同步執行並最終彙總資料。這裡用到了CountDownLatch和ExecutorService，示例程式碼如下：
結果又快了20-30s
優化MySQL配置
以下是我的配置示例。加了skip-name-resolve,快了4-5s。其他配置自行斷定
根據業務，再加上篩選條件
快4-5s
將where條件中除時間條件外的欄位建立聯合索引
效果沒那麼明顯
將where條件中索引條件使用inner join的方式去關聯
針對這條，我自身覺得很詫異。原sql,b為索引
應該之前有union all，union all是一個一個的執行，最後彙總的結果。修改為
結果快了3-4s
效能瓶頸
根據以上操作，3天查詢效率已經達到了8s左右，再也快不了了。檢視mysql的cpu使用率和記憶體使用率都不高，到底為什麼查這麼慢了，3天最多才60w資料，關聯的也都是一些字典表，不至於如此。繼續根據網上提供的資料，一系列騷操作，基本沒用，沒轍。
環境對比
因分析過sql優化已經ok了，試想是不是磁碟讀寫問題。將優化過的程式，分別部署於不同的現場環境。一個有ssd，一個沒有ssd。發現查詢效率懸殊。用軟體檢測過發現ssd讀寫速度在700-800M/s,普通機械硬碟讀寫在70-80M/s。
優化結果及結論
優化結果：達到預期。
優化結論：sql優化不僅僅是對sql本身的優化，還取決於本身硬體條件，其他應用的影響，外加自身程式碼的優化。
小結
優化的過程是自身的一個歷練和考驗，珍惜這種機會，不做只寫業務程式碼的程式設計師。希望以上可以有助於你的思考，不足之處望指正。
原文連結：
https://my.oschina.net/xiaozhutefannao/blog/2243432
·END·
程式設計師的成長之路
路雖遠，行則必至
微信ID:cxydczzl