一、前言

對于生產(chǎn)業(yè)務(wù)系統(tǒng)來說，慢查詢也是一種故障和風(fēng)險，一旦出現(xiàn)故障將會造成系統(tǒng)不可用影響到生產(chǎn)業(yè)務(wù)。當(dāng)有大量慢查詢并且SQL執(zhí)行得越慢，消耗的CPU資源或IO資源也會越大，因此，要解決和避免這類故障，關(guān)注慢查詢本身是關(guān)鍵。

二、慢查詢

2.1 什么是慢查詢？

慢查詢，顧名思義，執(zhí)行很慢的查詢。當(dāng)執(zhí)行SQL超過long_query_time參數(shù)設(shè)定的時間閾值（默認10s）時，就被認為是慢查詢，這個SQL語句就是需要優(yōu)化的。慢查詢被記錄在慢查詢?nèi)罩纠?。慢查詢?nèi)罩灸J是不開啟的。如果需要優(yōu)化SQL語句，就可以開啟這個功能，它可以讓你很容易地知道哪些語句是需要優(yōu)化的。

2.2 慢查詢配置

以MySQL數(shù)據(jù)庫為例，默認慢查詢功能是關(guān)閉的，當(dāng)慢查詢開關(guān)打開后，并且執(zhí)行的SQL語句達到參數(shù)設(shè)定的閾值后，就會觸發(fā)慢查詢功能打印出日志。

1、慢查詢?nèi)罩?/h4>

查詢是否開啟慢查詢?nèi)罩荆簊how variables like ‘slow_query_log’;

• 開啟慢查詢sql：set global slow_query_log = 1/on;
• 關(guān)閉慢查詢sql：set global slow_query_log = 0/off;

如圖所示已是開啟狀態(tài) ON

2、未使用索引是否開啟日志

查詢未使用索引是否開啟記錄慢查詢?nèi)罩荆?show variables like ‘log_queries_not_using_indexes’;

• 開啟記錄未使用索引sql：set global log_queries_not_using_indexes=1/on
• 關(guān)閉記錄未使用索引sql：set global log_queries_not_using_indexes=0/off

如圖所示是關(guān)閉狀態(tài)OFF

3、慢查詢時間設(shè)置

查詢超過多少秒的記錄到慢查詢?nèi)罩局校簊how variables like ‘long_query_time’;

設(shè)置超X秒就記錄慢查詢sql：set global long_query_time= X;

如下圖所示，設(shè)置的慢查詢時間為0.3秒

注：上述這些參數(shù)設(shè)置都是在當(dāng)前數(shù)據(jù)庫生效，當(dāng)MySQL重啟后則會失效。

如果要永久生效，就必須修改配置文件my.cnf

4、慢查詢路徑

查詢MySQL慢查詢?nèi)罩镜穆窂剑簊how variables like ‘slow_query_log_file%’;

如下為查詢出的路徑在：/apps/log/mysql/slow3306.log

三、慢查詢?nèi)罩痉治?/h2>

3.1 mysqldumpslow工具

以MySQL為例，一般使用mysqldumpslow工具分析慢查詢?nèi)罩?，使用命令查詢慢SQL語句。

–查詢用時最多的10條慢：

sql mysqldumpslow -s t -t 10 -g 'select' /data/mysql/data/dcbi-3306/log/slow.log

得到其中一條如下圖所示的結(jié)果：

• Count：代表這個 SQL 語句執(zhí)行了多少次
• Time：代表執(zhí)行的時間，括號是累計時間
• Lock：表示鎖定的時間，括號是累計時間
• Rows：表示返回的記錄數(shù)，括號是累計記錄數(shù)

有了這樣清晰的慢查詢?nèi)罩痉治鲋?，我們可以更加有針對性和更快捷的處理出現(xiàn)慢查詢SQL語句的問題，直接找到對應(yīng)程序位置優(yōu)化代碼從而避免慢查詢出現(xiàn)。

四、慢查詢解決方案

4.1 索引失效

之所以會出現(xiàn)慢查詢，無疑是SQL語句的問題，一般都是掃描數(shù)據(jù)量過大、沒有使用索引、索引失效等導(dǎo)致。如下是一些索引失效的情況：

使用LIKE關(guān)鍵字的查詢語句

在使用LIKE關(guān)鍵字進行查詢的查詢語句中，如果匹配字符串的第一個字符為“%”，索引不會起作用。只有“%”不在第一個位置索引才會起作用。

使用多列索引的查詢語句

MySQL可以為多個字段創(chuàng)建索引。一個索引最多可以包括16個字段。對于多列索引，只有查詢條件使用了這些字段中的第一個字段時，索引才會被使用，也就是左匹配原則。

4.2 SQL語句優(yōu)化

1） 查詢語句應(yīng)該盡量避免全表掃描，首先應(yīng)該考慮在Where子句以及OrderBy子句上建立索引，但是每一條SQL語句最多只會走一條索引，而建立過多的索引會帶來插入和更新時的開銷，同時對于區(qū)分度不大的字段，應(yīng)該盡量避免建立索引，可以在查詢語句前使用explain關(guān)鍵字，查看SQL語句的執(zhí)行計劃，判斷該查詢語句是否使用了索引；

2）應(yīng)盡量使用EXIST和NOT EXIST代替 IN和NOT IN，因為后者很有可能導(dǎo)致全表掃描放棄使用索引；

3）應(yīng)盡量避免在Where子句中對字段進行NULL判斷，因為NULL判斷會導(dǎo)致全表掃描；

4）應(yīng)盡量避免在Where子句中使用or作為連接條件，因為同樣會導(dǎo)致全表掃描；

5）應(yīng)盡量避免在Where子句中使用！=或者<>操作符，同樣會導(dǎo)致全表掃描；

6）使用like “%abc%” 或者like “%abc” 同樣也會導(dǎo)致全表掃描，而like “abc%”會使用索引。

7）在使用Union操作符時，應(yīng)該考慮是否可以使用Union ALL來代替，因為Union操作符在進行結(jié)果合并時，會對產(chǎn)生的結(jié)果進行排序運算，刪除重復(fù)記錄，對于沒有該需求的應(yīng)用應(yīng)使用Union ALL，后者僅僅只是將結(jié)果合并返回，能大幅度提高性能；

8）應(yīng)盡量避免在Where子句中使用表達式操作符，因為會導(dǎo)致全表掃描；

9）應(yīng)盡量避免在Where子句中對字段使用函數(shù)，因為同樣會導(dǎo)致全表掃描

10）Select語句中盡量 避免使用“*”，因為在SQL語句在解析的過程中，會將“”轉(zhuǎn)換成所有列的列名，而這個工作是通過查詢數(shù)據(jù)字典完成的，有一定的開銷；

11）Where子句中，表連接條件應(yīng)該寫在其他條件之前，因為Where子句的解析是從后向前的，所以盡量把能夠過濾到多數(shù)記錄的限制條件放在Where子句的末尾；

12）若數(shù)據(jù)庫表上存在諸如index(a,b,c)之類的聯(lián)合索引，則Where子句中條件字段的出現(xiàn)順序應(yīng)該與索引字段的出現(xiàn)順序一致，否則將無法使用該聯(lián)合索引；

13）From子句中表的出現(xiàn)順序同樣會對SQL語句的執(zhí)行性能造成影響，F(xiàn)rom子句在解析時是從后向前的，即寫在末尾的表將被優(yōu)先處理，應(yīng)該選擇記錄較少的表作為基表放在后面，同時如果出現(xiàn)3個及3個以上的表連接查詢時，應(yīng)該將交叉表作為基表；

14）盡量使用>=操作符代替>操作符，例如，如下SQL語句，select dbInstanceIdentifier from DBInstance where id > 3，該語句應(yīng)該替換成 select dbInstanceIdentifier from DBInstance where id >=4 ，兩個語句的執(zhí)行結(jié)果是一樣的，但是性能卻不同，后者更加 高效，因為前者在執(zhí)行時，首先會去找等于3的記錄，然后向前掃描，而后者直接定位到等于4的記錄。

4.3 表結(jié)構(gòu)優(yōu)化

這里主要指如何正確的建立索引，因為不合理的索引會導(dǎo)致查詢?nèi)頀呙?，同時過多的索引會帶來插入和更新的性能開銷；

1）首先要明確每一條SQL語句最多只可能使用一個索引，如果出現(xiàn)多個可以使用的索引，系統(tǒng)會根據(jù)執(zhí)行代價，選擇一個索引執(zhí)行；

2）對于Innodb表，雖然如果用戶不指定主鍵，系統(tǒng)會自動生成一個主鍵列，但是自動產(chǎn)生的主鍵列有多個問題1. 性能不足，無法使用cache讀?。?. 并發(fā)不足，系統(tǒng)所有無主鍵表，共用一個全局的Auto_Increment列。因此，InnoDB的所有表，在建表同時必須指定主鍵。

3）對于區(qū)分度不大的字段，不要建立索引；

4）一個字段只需建一種索引即可，無需建立了唯一索引，又建立INDEX索引。

5）對于大的文本字段或者BLOB字段，不要建立索引；

6）連接查詢的連接字段應(yīng)該建立索引；

7）排序字段一般要建立索引；

8）分組統(tǒng)計字段一般要建立索引；

9）正確使用聯(lián)合索引，聯(lián)合索引的第一個字段是可以被單獨使用的，例如有如下聯(lián)合索引index(userID,dbInstanceID),一下查詢語句是可以使用該索引的，select dbInstanceIdentifier from DBInstance where userID=? ，但是語句select dbInstanceIdentifier from DBInstance where dbInstanceID=?就不可以使用該索引；

10）索引一般用于記錄比較多的表，假如有表DBInstance，所有查詢都有userID條件字段，目前已知該字段已經(jīng)能夠很好的區(qū)分記錄，即每一個userID下記錄數(shù)量不多，所以該表只需在userID上建立一個索引即可，即使有使用其他條件字段，由于每一個userID對應(yīng)的記錄數(shù)據(jù)不多，所以其他字段使用不用索引基本無影響，同時也可以避免建立過多的索引帶來的插入和更新的性能開銷；

五、總結(jié)

在日常寫SQL和寫程序的時候多關(guān)注基本的SQL語句，在業(yè)務(wù)復(fù)雜的系統(tǒng)中，除了上述基本的點外，盡管使用了索引，也還需要從業(yè)務(wù)本身出發(fā)，如：當(dāng)查詢的數(shù)量過大時，時間索引已經(jīng)不滿足了，可以改為分批次來查詢控制數(shù)量等。

參考文章地址：

http://m.dhdzp.com/article/283067.htm

到此這篇關(guān)于MySQL慢查詢以及解決方案詳解的文章就介紹到這了,更多相關(guān)MySQL慢查詢解決內(nèi)容請搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持腳本之家！