MySQL 慢查詢之慢 SQL 定位、日誌分析與優化方案

你好，我是田哥，主要從事 Java 面試輔導和技術分享。

儘管咱們不是 DBA，但是，面試中被問到 MySQL 慢查詢的概率還是非常高的。

說你沒有經歷過就是理由嗎？顯然不是。

一般來說一句SQL語句執行超過5s就能夠算是慢SQL，需要進行優化了。

爲何要對慢 SQL 進行治理

每一個 SQL 都需要消耗一定的 I/O 資源，SQL 執行的快慢直接決定了資源被佔用時間的長短。假設業務要求每秒需要完成 100 條 SQL 的執行，而其中 10 條 SQL 執行時間過長，從而導致每秒只能完成 90 條 SQL，所有新的 SQL 將進入排隊等待，直接影響業務，然後用戶就各種投訴來了。

治理的優先級

  1. master 數據庫 ->slave 數據庫 採用讀寫分離架構，讀在從庫 slave 上執行，寫在主庫 master 上執行。但由於從庫的數據都是在主庫複製過去的，主庫如果等待較多的情況，會加大從庫的複製延時

  2. 執行 SQL 次數多的優先治理

  3. 某張表被高併發集中訪問的優先治理

MySQL 執行原理

爲了更好的優化慢 SQL，我們來簡單瞭解下 MySQL 的執行原理

綠色部分爲 SQL 實際執行部分，主要分爲兩步：

  1. 解析：詞法解析 -> 語法解析 -> 邏輯計劃 ->查詢優化-> 物理執行計劃，過程中會檢查緩存是否可用，如果沒有可用緩存則進入下一步 mysql_execute_command 執行

  2. 執行：檢查用戶、表權限 -> 表加上共享讀鎖 -> 取數據到 query_cache-> 取消共享讀鎖

如何發現慢查詢 SQL

-- 修改慢查詢時間,只能當前會話有效;
set long_query_time=1; ­
-- 啓用慢查詢 ,加上global，不然會報錯的;
set global slow_query_log='ON';­­ 
-- 是否開啓慢查詢;
show variables like "%slow%";­­ 
-- 查詢慢查詢SQL狀況;
show status like "%slow%"; ­­ 
-- 慢查詢時間（默認情況下MySQL認位10秒以上纔是慢查詢）
show variables like "long_query_time"; ­­

除了 sql 的方式，我們也可以在配置文件（my.ini）中修改，加入配置時必須要在 [mysqld] 後面加入

-- 開啓日誌;
slow_query_log = on
-- 記錄日誌的log文件（注意:window上必須寫絕對路徑）
slow_query_log_file = D:/mysql5.5.16/data/showslow.log
-- 最長查詢的秒數;
long_query_time = 2
-- 表示記錄沒有使用索引的查詢
log­queries­not­using­indexes ­­

特別注意：開啓慢查詢會帶來 CPU 損耗與日誌記錄的 IO 開銷，所以建議間斷性的打開慢查詢日誌來觀察 MySQL 運行狀態

慢查詢分析示例

假設我們有一條 SQL

SELECT * FROM `emp` where ename like '%mQspyv%';

執行時間爲 1.163s，而我們設置的慢查詢時間爲 1s，這時我們可以打開慢查詢日誌進行日誌分析：

## Time: 150530 15:30:58 ­­ -- 該查詢發生在2015­5­30 15:30:58
## User@Host: root[root] @ localhost [127.0.0.1] ­­ --是誰,在什麼主機上發生的查詢
## Query_time: 1.134065 Lock_time: 0.000000 Rows_sent: 8 Rows_examined: 4000000 Query_time: --查詢總共用了多少時間,Lock_time: 在查詢時鎖定表的時間,Rows_sent: 返回多少rows數據,Rows_examined: 表掃描了400W行數據纔得到的結果;

如果我們的慢 SQL 很多，人工分析肯定分析不過來，這時候我們就需要藉助一些分析工具，MySQL 自帶了一個慢查詢分析工具 mysqldumpslow，以下是常見使用示例

mysqldumpslow ­s c ­t 10 /var/run/mysqld/mysqld­slow.log # 取出使用最多的10條慢查詢
mysqldumpslow ­s t ­t 3 /var/run/mysqld/mysqld­slow.log # 取出查詢時間最慢的3條慢查詢
mysqldumpslow ­s t ­t 10 ­g “left join” /database/mysql/slow­log #得到按照時間排序的前10條裏面含有左連接的查詢語句
mysqldumpslow ­s r ­t 10 ­g 'left join' /var/run/mysqld/mysqldslow.log # 按照掃描行數最多的

SQL 語句常見優化

只要簡單瞭解過 MySQL 內部優化機制，就很容易寫出高性能的 SQL

  1. 不使用子查詢：
SELECT * FROM t1 WHERE id (SELECT id FROM t2 WHERE name='hechunyang');

在 MySQL5.5 版本中，內部執行計劃器是先查外表再匹配內表，如果外表數據量很大，查詢速度會非常慢

再 MySQL5.6 中，有對內查詢做了優化，優化後 SQL 如下

SELECT t1.* FROM t1 JOIN t2 ON t1.id = t2.id;

但也僅針對 select 語句有效，update、delete 子查詢無效，所以生成環境不建議使用子查詢

避免函數索引

SELECT * FROM t WHERE YEAR(d) >= 2016;

即使 d 字段有索引，也會全盤掃描，應該優化爲：

SELECT * FROM t WHERE d >= '2016-01-01';

使用 IN 替換 OR

SELECT * FROM t WHERE LOC_ID = 10 OR LOC_ID = 20 OR LOC_ID = 30;

非聚簇索引走了 3 次，使用 IN 之後只走一次：

SELECT * FROM t WHERE LOC_IN IN (10,20,30);

LIKE 雙百分號無法使用到索引

SELECT * FROM t WHERE name LIKE '%de%';

應優化爲右模糊

SELECT * FROM t WHERE name LIKE 'de%';

增加 LIMIT M,N 限制讀取的條數

避免數據類型不一致

SELECT * FROM t WHERE id = '19';

應優化爲

SELECT * FROM t WHERE id = 19;

分組統計時可以禁止排序

SELECT goods_id,count(*) FROM t GROUP BY goods_id;

默認情況下 MySQL 會對所有 GROUP BY co1，col2 … 的字段進行排序，我們可以對其使用

ORDER BY NULL

禁止排序，避免排序消耗資源

SELECT goods_id,count(*) FROM t GROUP BY goods_id ORDER BY NULL;

去除不必要的 ORDER BY 語句

總結

總的來說，我們知道曼查詢的 SQL 後，優化方案可以做如下嘗試：

  1. SQL 語句優化，儘量精簡，去除非必要語句

  2. 索引優化，讓所有 SQL 都能夠走索引

  3. 如果是表的瓶頸問題，則分表，單表數據量維持在 1000W（理論上）以內

  4. 如果是單庫瓶頸問題，則分庫，讀寫分離

  5. 如果是物理機器性能問題，則分多個數據庫節點

歡迎加入 我的 Java 面試輔導知識星球（點擊瞭解詳情），分享面試經驗和麪試技巧，帶你一起學編程做項目（電商項目：快樂購物項目，造輪子：手寫 RPC 框架 mink）。

想加入的同學，可添加田哥微信（tj20120622）給你驚喜價 ， 備註【星球優惠價】，非誠勿擾謝謝。

本文由 Readfog 進行 AMP 轉碼，版權歸原作者所有。
來源：https://mp.weixin.qq.com/s/ok6yylJxalkeB8oyRo6LSg

猜你喜歡