環境：
服務器：騰訊云2H4G
數據庫：騰訊云TDSQL-C MySQL 1H1G
前段時間對某個網站進行了改版，然后某天發現網站內頁訪問有時候會等待很久，阿湯博客就分享一下解決這一問題的歷程。
首先登錄監控查看服務器各項指標都正常，然后懷疑是數據庫的問題，登錄數據庫管理平臺，實時查看各項指標都比較正常，但是每隔一段時間就會出現CPU100%的情況。
騰訊云的DBbrain也一直告警：致命 高并發/壓力請求
但是當MySQL CPU100%時，我查看了QPS、TPS沒有明顯變化，慢sql也無異常。
當我查看innoDB監控指標時，有大量的innoDB行插入和行讀取和邏輯讀和邏輯寫相對于前幾天有明顯的上升趨勢。
初步懷疑是頻繁創建臨時表導致，這種操作在數據庫CPU 100%時應該有大量的慢sql才正常，于是去查看數據庫配置文件，發現long_query_time設置的是10s，馬上改成了0.5S，過了幾分鐘大量的慢sql就出來了。
然后查看這些慢sql列表，主要就是四類sql。
1、UNION多表連接sql
SELECT t1.aa, t1.bb, t1.cc, c.dd, c.ee, FROM tabxx1 t1 INNER JOIN tt c ON c.xx = ? UNION SELECT t2.aa, t2.bb, t2.cc, c.dd, c.ee FROM tabxx2 t2 INNER JOIN tt c ON c.xx = ? UNION SELECT t3.aa, t3.bb, t3.cc, c.dd, c.ee FROM tabxx3 t3 INNER JOIN tt c ON c.xx = ? ORDER BY cc DESC LIMIT ?
2、使用了MySQL DATE_SUB() 函數的sql
SELECT aa, bb FROM xx WHERE t = ? AND date_sub(curdate(), INTERVAL ? DAY) <= date(creat_time) ORDER BY cc DESC LIMIT ?
3、使用了!=的sql
SELECT a FROM xx WHERE id != ? ORDER BY id DESC LIMIT ?
4、sql簡單，返回字段過多和未創建索引的sql
這些慢sql單次執行的時候，并不會慢，但是并發執行的時候，就會消耗數據庫大量的CPU資源。既然找到的罪魁禍首，就開始優化。
慢sql一：
第一個sql 通過EXPLAIN 查看結果，發現全部都沒有走索引，全部都是全表掃描。
網上查詢說，除非確實需要去掉重復的行，否則盡量使用union all而不是union。因為union會自帶distinct操作，代價很大。所以改成union all。
然后把sql語句簡化，去掉INNER JOIN ，每張表只返回需要的邏輯條目，優化以后如下：
( SELECT id, bb, cc, 'xx1' AS c1, 'yy1' AS c2 FROM tabxx1 ORDER BY id DESC LIMIT ？ ) UNION ALL ( SELECT id, bb, cc, 'xx2' AS c1, 'yy2' AS c2 FROM tabxx2 ORDER BY id DESC LIMIT ？ ) UNION ALL ( SELECT id, bb, cc, 'xx3' AS c1, 'yy3' AS c2 FROM tabxx3 ORDER BY id DESC LIMIT ？ ) ORDER BY cc DESC LIMIT ？
慢sql二：
因為使用了DATE_SUB函數，沒辦法走索引，每次都是全表掃描，設置了where第一個參數的索引，雖然EXPLAIN 顯示走了索引，但是慢sql詳情顯示也是全表查詢了。
于是就只能不使用DATE_SUB()函數，通過程序就行判斷和排序。
因為需要進行兩個字段排序。首選通過sql查詢id字典和需要排序的字段一，篩選符合條件得到的結果，再通過程序進行第2個字段進行排序獲取需要的條目，再通過ID去獲取。
當然網上也有其他方案，但是我考慮盡量讓sql語句簡單化，因為本身mysql只有1核CPU，把更多邏輯運行交給程序。
慢sql三：
網上說盡量避免在WHERE子句中使用!=或<>操作符，否則將引擎放棄使用索引而進行全表掃描。
所以這里刪除了id != ?條件，然后得到的數組，再去數組中剔除id=?的值。
慢sql四：
把一些簡單sql where條件加上索引。
然后把一些返回字段多的sql，精簡只獲取id和需要進行邏輯運行的字段，通過程序邏輯運行以后獲取符合條件的條目，再通過id去獲取這些條目其他字段。
所有優化完以后，觀察了半小時，發現MySQL CPU已經正常，慢sql也沒有了。
innoDB相關監控指標也恢復了正常。
其實優化方案不是唯一的，需要根據實際情況，因為我的MySQL CPU只有1核，所以只能盡量把sql簡單，少做一些邏輯運行，把邏輯交給程序，這樣最大程度降低數據庫的CPU消耗。
推薦閱讀：
