最近，在運維部及DBA同事的幫助和大家的共同努力下，對專案中的慢SQL進行了優化和修正，效果還是很明顯的，在此給大家點一個大大的贊。為了讓我們在SQL的處理上更為合理，形成可實踐、可借鑑、可參考優化的方案，我在這裡梳理一下慢SQL的解決思路，供大家參考。
慢SQL的系統表現
首先，我們如何判別系統中遇到了SQL慢查詢問題？個人認為慢SQL有如下三個特徵：
1，資料庫CPU負載高。一般是查詢語句中有很多計算邏輯，導致資料庫cpu負載。
2，IO負載高導致伺服器卡住。這個一般和全表查詢沒索引有關係。
3，查詢語句正常，索引正常但是還是慢。如果表面上索引正常，但是查詢慢，需要看看是否索引沒有生效。
開啟SQL慢查詢的日誌
如果你的系統出現了上述情況，並且你不是用的阿里雲的RDS這樣的產品，那麼下一步就需要開啟Mysql的慢查詢日誌來進一步定位問題。MySQL 提供了慢查詢日誌，這個日誌會記錄所有執行時間超過 long_query_time（預設是10s）的 SQL 及相關的資訊。
要開啟日誌，需要在 MySQL 的配置檔案 my.cnf 的 [mysqld] 項下配置慢查詢日誌開啟，如下所示：
[mysqld]slow_query_log=1
slow_query_log_file=/var/log/mysql/log-slow-queries.log
long_query_time=2複製程式碼在實際專案中，由於生成的慢查詢的日誌可能會特別大，分析起來不是很
方便，所以Mysql官方也提供了 mysqldumpslow 這個工具，方便我們分析慢查詢日誌，感興趣的同學可以自行到Mysql官方進行查閱。
SQL調優
有些SQL雖然出現在慢查詢日誌中，但未必是其本身的效能問題，可能是因為鎖等待，伺服器壓力高等等。需要分析SQL語句真實的執行計劃，而不是看重新執行一遍SQL時，花費了多少時間，由自帶的慢查詢日誌或者開源的慢查詢系統定位到具體的出問題的SQL，然後使用Explain工具來逐步調優，瞭解 MySQL 在執行這條資料時的一些細節，比如是否進行了優化、是否使用了索引等等。基於 Explain 的返回結果我們就可以根據 MySQL 的執行細節進一步分析是否應該優化搜尋、怎樣優化索引。
關於索引的建立及優化原則，個人特別推薦美團點評技術團隊的幾點總結，講得特別好，特地引用一下：
最左字首匹配原則，非常重要的原則，mysql會一直向右匹配直到遇到範圍查詢(>、<、between、like)就停止匹配，比如a = 1 and b = 2 and c > 3 and d = 4 如果建立(a,b,c,d)順序的索引，d是用不到索引的，如果建立(a,b,d,c)的索引則都可以用到，a,b,d的順序可以任意調整；
=和in可以亂序，比如a = 1 and b = 2 and c = 3 建立(a,b,c)索引可以任意順序，mysql的查詢優化器會幫你優化成索引可以識別的形式；
儘量選擇區分度高的列作為索引,區分度的公式是count(distinct col)/count(*)，表示欄位不重複的比例，比例越大我們掃描的記錄數越少，唯一鍵的區分度是1，而一些狀態、性別欄位可能在大資料面前區分度就是0，那可能有人會問，這個比例有什麼經驗值嗎？使用場景不同，這個值也很難確定，一般需要join的欄位我們都要求是0.1以上，即平均1條掃描10條記錄；
索引列不能參與計算，保持列“乾淨”，比如from_unixtime(create_time) = ’2014-05-29’就不能使用到索引，原因很簡單，b+樹中存的都是資料表中的欄位值，但進行檢索時，需要把所有元素都應用函式才能比較，顯然成本太大。所以語句應該寫成create_time = unix_timestamp(’2014-05-29’);
儘量的擴充套件索引，不要新建索引。比如表中已經有a的索引，現在要加(a,b)的索引，那麼只需要修改原來的索引即可。
一點總結
基於本文的思路，關於SQL慢查詢的解決可以按照以下的步驟執行：
1. 開啟慢日誌查詢，確定是否有SQL語句佔用了過多資源，如果是，在不改變業務原意的前提下，對insert、group by、order by、join等語句進行優化。
2. 考慮調整MySQL的系統引數： innodb_buffer_pool_size、innodb_log_file_size、table_cache等。
3. 確定是否是因為高併發引起行鎖的超時問題。
4. 如果資料量過大，需要考慮進一步的分庫分表，可以參見之前的文章1和 文章2 。
掃描二維碼或手動搜尋微信公眾號【架構棧】： ForestNotes
歡迎轉載，帶上以下二維碼即可
