一、前言

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

二、慢查詢

2.1 什么是慢查詢？

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

2.2 慢查詢配置

以MySQL數(shù)據(jù)庫為例，默認(rèn)慢查詢功能是關(guān)閉的，當(dāng)慢查詢開關(guān)打開后，并且執(zhí)行的SQL語句達(dá)到參數(shù)設(shè)定的閾值后，就會(huì)觸發(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í)間設(shè)置

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

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

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

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

如果要永久生效，就必須修改配置文件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)罩荆褂妹畈樵兟齋QL語句。

–查詢用時(shí)最多的10條慢：

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

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

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

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

四、慢查詢解決方案

4.1 索引失效

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

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

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

使用多列索引的查詢語句

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

4.2 SQL語句優(yōu)化

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

2）應(yīng)盡量使用EXIST和NOT EXIST代替 IN和NOT IN，因?yàn)楹笳吆苡锌赡軐?dǎo)致全表掃描放棄使用索引；

3）應(yīng)盡量避免在Where子句中對(duì)字段進(jìn)行NULL判斷，因?yàn)镹ULL判斷會(huì)導(dǎo)致全表掃描；

4）應(yīng)盡量避免在Where子句中使用or作為連接條件，因?yàn)橥瑯訒?huì)導(dǎo)致全表掃描；

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

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

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

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

9）應(yīng)盡量避免在Where子句中對(duì)字段使用函數(shù)，因?yàn)橥瑯訒?huì)導(dǎo)致全表掃描

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

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

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

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

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

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

這里主要指如何正確的建立索引，因?yàn)椴缓侠淼乃饕龝?huì)導(dǎo)致查詢?nèi)頀呙?，同時(shí)過多的索引會(huì)帶來插入和更新的性能開銷；

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

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

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

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

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

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

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

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

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

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

五、總結(jié)

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

參考文章地址：

http://www.dbjr.com.cn/article/283067.htm

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

最新評(píng)論