SQL最佳化 | MySQL問題處理案例分享三則

分享幾則MySQL問題處理案例，聊聊我的思路。處理問題的思路和角度各有不同，希望這篇文章可以拋磚引玉。

問題一、

問題描述：某功能模組涉及儲存和提交類操作慢，反映到資料庫環境為DML操作普遍偏慢。

處理過程：排查MySQL資料庫發現所有涉及批次插入的功能都有效能問題，資料庫選用的是微軟雲的RDS，對比生產環境和測試開發環境，生產環境的硬體配置比測試開發環境高很多，插入反而更慢。嘗試使用指令碼驗證兩個環境的插入速度：

同樣的網路環境，插入3萬條資料，測試環境用時6s，而生產環境用時9s；對比兩個環境資料庫引數的差異發現生產環境開啟了binlog，而測試環境未開啟：

經與雲廠商確認後，生產環境有災備和自動備份的功能，開啟此功能預設需要開啟binlog，而廠商為了確保資料的完整性，將sync_log的值設定為1，即每一個事務都需要重新整理資料到磁碟，這樣就導致資料庫的dml操作效能下降很多。

 

總結：開啟binlog之後建議合理規劃以下兩個引數的值來提高資料庫效能：sync_binlog = 0   ##控制多少事務重新整理一次binlog，0代表由檔案系統控制。

innodb_flush_log_at_trx_commit = 2  ##控制log buffer的羅盤機制，預設1s重新整理一次。以上的設定可以使用快取機制，增加資料庫插入和修改的速度，但是會帶來一定的風險，伺服器意外當機可能會丟失部分快取中的資料。

 

問題二、

 

問題描述：慢SQL導致資料庫CPU告警

解決過程：某功能模組慢SQL導致系統卡死，且SQL執行頻率較高。到MySQL資料庫發現如下SQL嚴重阻塞：SQL文字結構如下：

1	delete  from   表A  where  a.欄位1  in    ( select  b.欄位1     from  表B ，        表C     where  b.欄位2=xx     and  b.欄位2=c.欄位2     and  c.欄位3=0）

　　

此SQL在功能上迴圈呼叫執行，效率極差。先從索引層面最佳化，表B/C都缺失索引，刪除的效率極低。增加如下索引：

create 

index idx_name 

ON 表B(欄位

2);







create 

index idx_name 

ON 表C(欄位

3);

透過新增索引當然能有效的最佳化SQL執行效率。我們再來看一下SQL的邏輯，這麼簡單的邏輯有必要搞個子查詢嗎？來嘗試修改一下SQL寫法，修改後如下：

delete 表Afrom  表A ,  表B ,  表C where b.欄位2 = xx  and b.欄位2 = c.欄位2  and a.欄位1 = c.欄位1  and c.欄位3 = 0

修改後的SQL（0.5s以內）

總結：透過掃描SQL程式碼發現較多的SQL開發人員習慣使用exists和in的邏輯來過濾資料，但是在MySQL中，exists的效能並不是最高的，即使在欄位存在索引的情況下，在結果集比較大情況下，

exists的檢索速度遠不如inner join的hash連線，而且過多的使用exists容易導致SQL的執行計劃異常，而inner join邏輯相對更加直接，簡化。 我推薦的優先邏輯：join  >  exists  >  in。

 

問題三、 問題描述：再來看一個慢SQL最佳化案例。

解決過程：資料庫整體負載壓力較大，分析慢日誌最佳化了部分效能較差的SQL後有明顯改善，此處列舉出一個比較典型的最佳化案例：某功能模組更新文件閱讀數的一個定時任務，原SQL執行時間穩定在5秒左右。SQL文字結構如下（將就著看 ）：

檢視它的執行計劃：可以發現，此處的in條件中，MySQL選用了全表掃描的方法進行匹配，欄位的單列索引是有的， 與開發人員溝通後，in中的結果不會很大，我們可以將SQL進行分離：

將in的條件單獨拿出來查詢，然後將獲取到的結果拼接到後面的SQL中對比執行計劃，此時SQL執行速度可以達到毫秒級別

總結：SQL的邏輯越簡單越好，應儘量的簡化SQL邏輯，減少這種巢狀，SQL拼接的操作，儘量把一個大的SQL剝離成小的SQL去執行，不同資料庫對SQL的執行計劃有出入，越複雜的SQL帶過來的隱患就越大，簡潔的SQL邏輯總是最高效最健壯的。