MySQL慢查詢以及解決方案詳解
目錄
- 一、前言
- 二、慢查詢
- 2.1 什么是慢查詢？
- 2.2 慢查詢配置
- 1、慢查詢日志
- 2、未使用索引是否開啟日志
- 3、慢查詢時間設置
- 4、慢查詢路徑
- 三、慢查詢日志分析
- 3.1 mysqldumpslow工具
- 四、慢查詢解決方案
- 4.1 索引失效
- 4.2 SQL語句優化
- 4.3 表結構優化
- 五、總結
一、前言
對于生產業務系統來說，慢查詢也是一種故障和風險，一旦出現故障將會造成系統不可用影響到生產業務。當有大量慢查詢并且SQL執行得越慢，消耗的CPU資源或IO資源也會越大，因此，要解決和避免這類故障，關注慢查詢本身是關鍵。
二、慢查詢
2.1 什么是慢查詢？
慢查詢，顧名思義，執行很慢的查詢。當執行SQL超過long_query_time參數設定的時間閾值（默認10s）時，就被認為是慢查詢，這個SQL語句就是需要優化的。慢查詢被記錄在慢查詢日志里。慢查詢日志默認是不開啟的。如果需要優化SQL語句，就可以開啟這個功能，它可以讓你很容易地知道哪些語句是需要優化的。
2.2 慢查詢配置
以MySQL數據庫為例，默認慢查詢功能是關閉的，當慢查詢開關打開后，并且執行的SQL語句達到參數設定的閾值后，就會觸發慢查詢功能打印出日志。
1、慢查詢日志
查詢是否開啟慢查詢日志：show variables like ‘slow_query_log’;
- 開啟慢查詢sql：set global slow_query_log = 1/on;
- 關閉慢查詢sql：set global slow_query_log = 0/off;
如圖所示已是開啟狀態 ON
2、未使用索引是否開啟日志
查詢未使用索引是否開啟記錄慢查詢日志： show variables like ‘log_queries_not_using_indexes’;
- 開啟記錄未使用索引sql：set global log_queries_not_using_indexes=1/on
- 關閉記錄未使用索引sql：set global log_queries_not_using_indexes=0/off
如圖所示是關閉狀態OFF
3、慢查詢時間設置
查詢超過多少秒的記錄到慢查詢日志中：show variables like ‘long_query_time’;
設置超X秒就記錄慢查詢sql：set global long_query_time= X;
如下圖所示，設置的慢查詢時間為0.3秒
注：上述這些參數設置都是在當前數據庫生效，當MySQL重啟后則會失效。
如果要永久生效，就必須修改配置文件my.cnf
4、慢查詢路徑
查詢MySQL慢查詢日志的路徑：show variables like ‘slow_query_log_file%’;
如下為查詢出的路徑在：/apps/log/mysql/slow3306.log
三、慢查詢日志分析
3.1 mysqldumpslow工具
以MySQL為例，一般使用mysqldumpslow工具分析慢查詢日志，使用命令查詢慢SQL語句。
–查詢用時最多的10條慢：
sql mysqldumpslow -s t -t 10 -g "select" /data/mysql/data/dcbi-3306/log/slow.log
得到其中一條如下圖所示的結果：
- Count：代表這個 SQL 語句執行了多少次
- Time：代表執行的時間，括號是累計時間
- Lock：表示鎖定的時間，括號是累計時間
- Rows：表示返回的記錄數，括號是累計記錄數
有了這樣清晰的慢查詢日志分析之后，我們可以更加有針對性和更快捷的處理出現慢查詢SQL語句的問題，直接找到對應程序位置優化代碼從而避免慢查詢出現。
四、慢查詢解決方案
4.1 索引失效
之所以會出現慢查詢，無疑是SQL語句的問題，一般都是掃描數據量過大、沒有使用索引、索引失效等導致。如下是一些索引失效的情況：
使用LIKE關鍵字的查詢語句
在使用LIKE關鍵字進行查詢的查詢語句中，如果匹配字符串的第一個字符為“%”，索引不會起作用。只有“%”不在第一個位置索引才會起作用。
使用多列索引的查詢語句
MySQL可以為多個字段創建索引。一個索引最多可以包括16個字段。對于多列索引，只有查詢條件使用了這些字段中的第一個字段時，索引才會被使用，也就是左匹配原則。
4.2 SQL語句優化
1） 查詢語句應該盡量避免全表掃描，首先應該考慮在Where子句以及OrderBy子句上建立索引，但是每一條SQL語句最多只會走一條索引，而建立過多的索引會帶來插入和更新時的開銷，同時對于區分度不大的字段，應該盡量避免建立索引，可以在查詢語句前使用explain關鍵字，查看SQL語句的執行計劃，判斷該查詢語句是否使用了索引；
2）應盡量使用EXIST和NOT EXIST代替 IN和NOT IN，因為后者很有可能導致全表掃描放棄使用索引；
3）應盡量避免在Where子句中對字段進行NULL判斷，因為NULL判斷會導致全表掃描；
4）應盡量避免在Where子句中使用or作為連接條件，因為同樣會導致全表掃描；
5）應盡量避免在Where子句中使用！=或者<>操作符，同樣會導致全表掃描；
6）使用like “%abc%” 或者like “%abc” 同樣也會導致全表掃描，而like “abc%”會使用索引。
7）在使用Union操作符時，應該考慮是否可以使用Union ALL來代替，因為Union操作符在進行結果合并時，會對產生的結果進行排序運算，刪除重復記錄，對于沒有該需求的應用應使用Union ALL，后者僅僅只是將結果合并返回，能大幅度提高性能；
8）應盡量避免在Where子句中使用表達式操作符，因為會導致全表掃描；
9）應盡量避免在Where子句中對字段使用函數，因為同樣會導致全表掃描
10）Select語句中盡量 避免使用“*”，因為在SQL語句在解析的過程中，會將“”轉換成所有列的列名，而這個工作是通過查詢數據字典完成的，有一定的開銷；
11）Where子句中，表連接條件應該寫在其他條件之前，因為Where子句的解析是從后向前的，所以盡量把能夠過濾到多數記錄的限制條件放在Where子句的末尾；
12）若數據庫表上存在諸如index(a,b,c)之類的聯合索引，則Where子句中條件字段的出現順序應該與索引字段的出現順序一致，否則將無法使用該聯合索引；
13）From子句中表的出現順序同樣會對SQL語句的執行性能造成影響，From子句在解析時是從后向前的，即寫在末尾的表將被優先處理，應該選擇記錄較少的表作為基表放在后面，同時如果出現3個及3個以上的表連接查詢時，應該將交叉表作為基表；
14）盡量使用>=操作符代替>操作符，例如，如下SQL語句，select dbInstanceIdentifier from DBInstance where id > 3，該語句應該替換成 select dbInstanceIdentifier from DBInstance where id >=4 ，兩個語句的執行結果是一樣的，但是性能卻不同，后者更加 高效，因為前者在執行時，首先會去找等于3的記錄，然后向前掃描，而后者直接定位到等于4的記錄。
4.3 表結構優化
這里主要指如何正確的建立索引，因為不合理的索引會導致查詢全表掃描，同時過多的索引會帶來插入和更新的性能開銷；
1）首先要明確每一條SQL語句最多只可能使用一個索引，如果出現多個可以使用的索引，系統會根據執行代價，選擇一個索引執行；
2）對于Innodb表，雖然如果用戶不指定主鍵，系統會自動生成一個主鍵列，但是自動產生的主鍵列有多個問題1. 性能不足，無法使用cache讀取；2. 并發不足，系統所有無主鍵表，共用一個全局的Auto_Increment列。因此，InnoDB的所有表，在建表同時必須指定主鍵。
3）對于區分度不大的字段，不要建立索引；
4）一個字段只需建一種索引即可，無需建立了唯一索引，又建立INDEX索引。
5）對于大的文本字段或者BLOB字段，不要建立索引；
6）連接查詢的連接字段應該建立索引；
7）排序字段一般要建立索引；
8）分組統計字段一般要建立索引；
9）正確使用聯合索引，聯合索引的第一個字段是可以被單獨使用的，例如有如下聯合索引index(userID,dbInstanceID),一下查詢語句是可以使用該索引的，select dbInstanceIdentifier from DBInstance where userID=? ，但是語句select dbInstanceIdentifier from DBInstance where dbInstanceID=?就不可以使用該索引；
10）索引一般用于記錄比較多的表，假如有表DBInstance，所有查詢都有userID條件字段，目前已知該字段已經能夠很好的區分記錄，即每一個userID下記錄數量不多，所以該表只需在userID上建立一個索引即可，即使有使用其他條件字段，由于每一個userID對應的記錄數據不多，所以其他字段使用不用索引基本無影響，同時也可以避免建立過多的索引帶來的插入和更新的性能開銷；
五、總結
在日常寫SQL和寫程序的時候多關注基本的SQL語句，在業務復雜的系統中，除了上述基本的點外，盡管使用了索引，也還需要從業務本身出發，如：當查詢的數量過大時，時間索引已經不滿足了，可以改為分批次來查詢控制數量等。
參考文章地址：
到此這篇關于MySQL慢查詢以及解決方案詳解的文章就介紹到這了,更多相關MySQL慢查詢解決內容請搜索以前的文章或繼續瀏覽下面的相關文章希望大家以后多多支持！
相關文章：
網公網安備