MySQL 優化實戰記錄

閱讀本文大概需要 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·

程式員的成長之路

路雖遠，行則必至