環(huán)境：

服務(wù)器：騰訊云2H4G

數(shù)據(jù)庫：騰訊云TDSQL-C MySQL 1H1G

前段時間對某個網(wǎng)站進行了改版，然后某天發(fā)現(xiàn)網(wǎng)站內(nèi)頁訪問有時候會等待很久，阿湯博客就分享一下解決這一問題的歷程。

首先登錄監(jiān)控查看服務(wù)器各項指標(biāo)都正常，然后懷疑是數(shù)據(jù)庫的問題，登錄數(shù)據(jù)庫管理平臺，實時查看各項指標(biāo)都比較正常，但是每隔一段時間就會出現(xiàn)CPU100%的情況。

騰訊云的DBbrain也一直告警：致命  高并發(fā)/壓力請求

但是當(dāng)MySQL CPU100%時，我查看了QPS、TPS沒有明顯變化，慢sql也無異常。

當(dāng)我查看innoDB監(jiān)控指標(biāo)時，有大量的innoDB行插入和行讀取和邏輯讀和邏輯寫相對于前幾天有明顯的上升趨勢。

初步懷疑是頻繁創(chuàng)建臨時表導(dǎo)致，這種操作在數(shù)據(jù)庫CPU 100%時應(yīng)該有大量的慢sql才正常，于是去查看數(shù)據(jù)庫配置文件，發(fā)現(xiàn)long_query_time設(shè)置的是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() 函數(shù)的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簡單，返回字段過多和未創(chuàng)建索引的sql

這些慢sql單次執(zhí)行的時候，并不會慢，但是并發(fā)執(zhí)行的時候，就會消耗數(shù)據(jù)庫大量的CPU資源。既然找到的罪魁禍?zhǔn)祝烷_始優(yōu)化。

慢sql一：

第一個sql 通過EXPLAIN 查看結(jié)果，發(fā)現(xiàn)全部都沒有走索引，全部都是全表掃描。

網(wǎng)上查詢說，除非確實需要去掉重復(fù)的行，否則盡量使用union all而不是union。因為union會自帶distinct操作，代價很大。所以改成union all。

然后把sql語句簡化，去掉INNER JOIN ，每張表只返回需要的邏輯條目，優(yōu)化以后如下：

(
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函數(shù)，沒辦法走索引，每次都是全表掃描，設(shè)置了where第一個參數(shù)的索引，雖然EXPLAIN 顯示走了索引，但是慢sql詳情顯示也是全表查詢了。

于是就只能不使用DATE_SUB()函數(shù)，通過程序就行判斷和排序。

因為需要進行兩個字段排序。首選通過sql查詢id字典和需要排序的字段一，篩選符合條件得到的結(jié)果，再通過程序進行第2個字段進行排序獲取需要的條目，再通過ID去獲取。

當(dāng)然網(wǎng)上也有其他方案，但是我考慮盡量讓sql語句簡單化，因為本身mysql只有1核CPU，把更多邏輯運行交給程序。

慢sql三：

網(wǎng)上說盡量避免在WHERE子句中使用!=或<>操作符，否則將引擎放棄使用索引而進行全表掃描。

所以這里刪除了id != ?條件，然后得到的數(shù)組，再去數(shù)組中剔除id=?的值。

慢sql四：

把一些簡單sql where條件加上索引。

然后把一些返回字段多的sql，精簡只獲取id和需要進行邏輯運行的字段，通過程序邏輯運行以后獲取符合條件的條目，再通過id去獲取這些條目其他字段。

所有優(yōu)化完以后，觀察了半小時，發(fā)現(xiàn)MySQL CPU已經(jīng)正常，慢sql也沒有了。

innoDB相關(guān)監(jiān)控指標(biāo)也恢復(fù)了正常。

其實優(yōu)化方案不是唯一的，需要根據(jù)實際情況，因為我的MySQL CPU只有1核，所以只能盡量把sql簡單，少做一些邏輯運行，把邏輯交給程序，這樣最大程度降低數(shù)據(jù)庫的CPU消耗。

推薦閱讀：

五條SQL語句優(yōu)化法則，提高SQL執(zhí)行效率