MySQL 優化實戰記錄

閱讀本文大概需要 2 分鐘。

背景

本次SQL優化是針對javaweb中的表格查詢做的。

部分網絡架構圖

業務簡單說明

N個機臺將業務數據發送至服務器，服務器程序將數據入庫至Mysql數據庫。服務器中的javaweb程序將數據展示到網頁上供用戶查看。

原數據庫設計

1、windows單機主從分離

2、已分表分庫，按年分庫，按天分表

3、每張表大概20w左右的數據

原查詢效率

3天數據查詢70-80s

目標

3-5s

業務缺陷

無法使用sql分頁，只能用java做分頁。

問題排查

1、如果你配置了druid，可在druid頁面中直接查看sql執行時間和uri請求時間

2、在后臺代碼中用System.currentTimeMillis計算時間差。

結論 ： 后臺慢，且查詢sql慢

1、sql拼接過長，達到了3000行，有的甚至到8000行，大多都是union all的操作，且有不必要的嵌套查詢和查詢了不必要的字段

2、利用explain查看執行計劃，where條件中除時間外只有一個字段用到了索引

備注 ： 因優化完了，之前的sql實在找不到了，這里只能YY了。

查詢優化

效果沒那么明顯

效果沒那么明顯

將union all的操作分解，例如(一個union all的sql也很長)

將如上sql分解成若干個sql去執行，最終匯總數據，最后快了20s左右。

利用java異步編程的操作，將分解的sql異步執行并最終匯總數據。這里用到了CountDownLatch和ExecutorService，示例代碼如下：

結果又快了20-30s

優化MySQL配置

以下是我的配置示例。加了skip-name-resolve,快了4-5s。其他配置自行斷定

快4-5s

效果沒那么明顯

針對這條，我自身覺得很詫異。原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·

程序員的成長之路

路雖遠，行則必至

版權聲明：本文內容由網絡用戶投稿，版權歸原作者所有，本站不擁有其著作權，亦不承擔相應法律責任。如果您發現本站中有涉嫌抄襲或描述失實的內容，請聯系我們jiasou666@gmail.com 處理，核實后本網站將在24小時內刪除侵權內容。