一、什麼是慢 SQL 什麼是慢SQL？顧名思義，運行時間較長的 SQL 語句即為慢 SQL！ 那問題來了，多久才算慢呢？ 這個慢其實是一個相對值，不同的業務場景下，標準要求是不一樣的。 我們都知道，我們每執行一次 SQL，資料庫除了會返回執行結果以外，還會返回 SQL 執行耗時，以 MySQL 數據 ...
一、什麼是慢 SQL
什麼是慢SQL？顧名思義，運行時間較長的 SQL 語句即為慢 SQL！
那問題來了，多久才算慢呢？
這個慢其實是一個相對值，不同的業務場景下，標準要求是不一樣的。
我們都知道，我們每執行一次 SQL，資料庫除了會返回執行結果以外，還會返回 SQL 執行耗時，以 MySQL 資料庫為例，當我們開啟了慢 SQL 監控開關後，預設配置下，當 SQL 的執行時長大於 10 秒，會被記錄到慢 SQL 的日誌文件中。
當然，這個值還可以重新設置，生產環境慢 SQL 一般會設置為0.1~0.2s。當我們將其設置為0.2s時，當前資料庫所有 SQL 的執行時長超過0.2s的都會被視為慢 SQL。
可能有的同學會發出疑問，我們為什麼要追蹤慢 SQL，有什麼意義呢？
二、慢 SQL 危害
這裡要從慢 SQL 的危害談起，以 MySQL 資料庫為例，總結起來有以下幾點：
- 當出現慢查詢，DDL 操作都會被阻塞，也就是說創建表、修改表、刪除表、執行數據備份等操作都需要等待，這對實時備份重要數據的系統來說是不可容忍的
- 慢查可能會占用 mysql 的大量記憶體，嚴重的時候會導致伺服器直接掛掉，整個系統直接癱瘓
- 慢 SQL 的執行時間過長，可能會導致應用的進程因超時被 kill，無法返回結果給到客戶端
- 造成資料庫幻讀、不可重覆讀的概率更大，假設該慢 SQL 是一個更新操作但因執行時間過長未提交，而另一條 SQL 也在更新數據並且已提交，用戶再次查詢的時候，看到的數據可能與實際結果不符
- 嚴重影響用戶體驗，SQL 的執行時間越長，頁面載入數據耗時也就越長
以千萬級的訂單表為例，未優化的情況下，單表分頁查詢 10 條數據，耗時：39s
首先不說可能對資料庫伺服器造成的潛在壓力，沒有任何一個用戶會在頁面查詢訂單查詢等待 39 秒！
三、如何定位慢 SQL
說了這麼多，我們如何去定位慢 SQL 呢？
3.1、開啟慢 SQL 監控
以 MySQL 為例，我們可以通過如下方式，查詢是否開啟慢 SQL 的監控。
show variables like 'slow_query_log%';
通過如下命令，開啟慢 SQL 監控，執行成功之後，客戶端需要重新連接才能生效。
-- 開啟慢 SQL 監控
set global slow_query_log = 1;
如果想關閉慢 SQL 監控，將其配置為0就可以了。
-- 關閉慢 SQL 監控
set global slow_query_log = 0;
需要特別註意的是，當伺服器重啟之後，當前配置會失效！
3.2、配置慢 SQL 閥值
預設的慢 SQL 閥值是10秒，可以通過如下語句查詢慢 SQL 的閥值。
-- 查詢慢 SQL 的閥值
show variables like "long_query_time";
我們可以通過如下方式，將慢 SQL 閥值配置成0.2秒。
-- 修改慢 SQL 的閥值
set global long_query_time = 0.2;
然後，退出客戶端，重新連接伺服器，就生效了！
與之類似，當伺服器重啟之後，當前配置會失效！
3.3、永久開啟慢 SQL 監控
以上的操作，當伺服器不重啟會一直有效，但是當伺服器一單重啟之後，配置就會失效，如果想永久生效，可以通過修改全局配置文件my.cnf使之永久生效。
以 CentOS 為例，打開my.cnf配置文件，添加如下配置變數。
[mysqld]
slow_query_log = ON
slow_query_log_file = /var/lib/mysql/ecs-203056-slow.log
long_query_time = 1
重啟 mysql 伺服器
systemctl restart mysqld
3.4、慢 SQL 監控測試
初始化一張日誌表，數據量在 10 萬左右就夠了，然後我們來執行 SQL，看看是不是被正常抓取到。
很清晰的看到，慢 SQL 已經被抓取記錄。
日誌內容詳解：
- Time：表示客戶端查詢時間
- root[root]：表示客戶端查詢用戶和IP
- Query_time：表示查詢耗時
- Lock_time：表示等待 table lock 的時間，註意InnoDB的行鎖等待是不會反應在這裡的
- Rows_sent：表示返回了多少行記錄（結果集）。
- Rows_examined：表示檢查了多少條記錄。
除此之外，我們還可以藉助mysqldumpslow命令工具，分析慢 SQL 的數據情況，可以通過如下參數進行組合分析
-s 表示按何種方式排序，支持的參數如下
al: 平均鎖定時間
ar: 平均返回記錄數
at: 平均查詢時間
c: 訪問次數
l: 鎖定時間
r: 返回記錄
t: 查詢時間
-t NUM 返回前面多少條的數據
-g PATTERN 後邊搭配一個正則匹配模式，大小寫不敏感
常見的用法如下：
- 查詢返回記錄集最多的10個 SQL
mysqldumpslow -s r -t 10 /var/lib/mysql/ecs-203056-slow.log
- 查詢訪問次數最多的10個SQL
mysqldumpslow -s c -t 10 /var/lib/mysql/ecs-203056-slow.log
- 查詢按照時間排序的前10條裡面含有左連接的查詢語句
mysqldumpslow -s t -t 10 -g "LEFT JOIN" /var/lib/mysql/ecs-203056-slow.log
四、慢 SQL 是怎麼發生的
面對這種耗時巨長的 SQL，我們不禁會發出一個疑問，它是怎麼發生的呢？
這得從 SQL 的執行過程說起，我們先簡單的看看下麵這個圖。
一條 SQL 語句執行時，總結起來大概分為以下幾個步驟：
- 1.若查詢緩存打開則會優先查詢緩存，若命中則直接返回結果給客戶端。
- 2.若緩存未命中，此時 MySQL 需要搞清楚這條語句需要做什麼，則通過分析器進行詞法分析、語法分析。
- 3.搞清楚要做什麼之後，MySQL 會通過優化器對 SQL 進行優化，生成一個最優的執行計劃
- 4.最後通過執行器與存儲引擎提供的介面進行交互，將結果返回給客戶端
在 MySQL 執行過程中，優化器可能會對我們即將要執行的 SQL 進行改造，改造思路如下：
- 1.根據搜索條件，找出 SQL 中所有可能使用的索引
- 2.然後計算全表掃描的成本開銷
- 3.接著計算使用不同索引執行查詢的成本開銷
- 4.最後會對比各種執行方案的成本開銷，找出開銷值最小的那一個
其中影響成本開銷值的計算，主要是I/O成本和CPU成本這兩個指標。
從I/O成本視角看：
- 當表的數據量越大，需要的 I/O 次數也就越多
- 從磁碟讀取數據比從緩存讀取數據，I/O 消耗的時間更多
- 全表掃描比通過索引快速查找，I/O 消耗的時間和次數更多
從CPU成本視角看：
- 當 SQL 中有排序、子查詢等複雜的操作時，CPU 需要先把數據存到臨時表中，再對數據進行加工，需要的 CPU 資源更多
- 全表掃描相比於通過索引快速查找，需要的 CPU 資源也更多
因此我們不難發現，在沒有開啟緩存的情況下，當表的數據量越大，如果 SQL 又沒有走索引，很容易發生查詢慢的問題。
五、小結
本文主要圍繞慢 SQL 的定位和可能存在的風險進行了簡單的介紹，整篇介紹的算是一個入門級的知識，文章內容難免有些理解不到位的地方，歡迎網友留言指出！
由於篇幅的原因，我們會在下篇文章中介紹慢 SQL 的優化思路。
六、參考
作者：程式員志哥
出處：www.pzblog.cn
資源：微信搜【Java極客技術】關註我，回覆 【cccc】有我準備的一線程式必備電腦書籍、大廠面試資料和免費電子書。 希望可以幫助大家提升技術和能力。
