慢查詢(xún)?nèi)罩靖拍?/h2>

MySQL的慢查詢(xún)?nèi)罩臼荕ySQL提供的一種日志記錄，它用來(lái)記錄在MySQL中響應(yīng)時(shí)間超過(guò)閥值的語(yǔ)句，具體指運(yùn)行時(shí)間超過(guò)long_query_time值的SQL，則會(huì)被記錄到慢查詢(xún)?nèi)罩局?。long_query_time的默認(rèn)值為10，意思是運(yùn)行10S以上的語(yǔ)句。默認(rèn)情況下，Mysql數(shù)據(jù)庫(kù)并不啟動(dòng)慢查詢(xún)?nèi)罩?，需要我們手?dòng)來(lái)設(shè)置這個(gè)參數(shù)，當(dāng)然，如果不是調(diào)優(yōu)需要的話(huà)，一般不建議啟動(dòng)該參數(shù)，因?yàn)殚_(kāi)啟慢查詢(xún)?nèi)罩緯?huì)或多或少帶來(lái)一定的性能影響。慢查詢(xún)?nèi)罩局С謱⑷罩居涗泴?xiě)入文件，也支持將日志記錄寫(xiě)入數(shù)據(jù)庫(kù)表。

一、數(shù)據(jù)庫(kù)中設(shè)置SQL慢查詢(xún)

1、mysql慢查詢(xún)相關(guān)配置參數(shù)介紹

• slow_query_log ：是否開(kāi)啟慢查詢(xún)?nèi)罩荆?表示開(kāi)啟，0表示關(guān)閉。
• log-slow-queries：舊版（5.6以下版本）MySQL數(shù)據(jù)庫(kù)慢查詢(xún)?nèi)罩敬鎯?chǔ)路徑?？梢圆辉O(shè)置該參數(shù)，系統(tǒng)則會(huì)默認(rèn)給一個(gè)缺省的文件host_name-slow.log
• slow-query-log-file：新版（5.6及以上版本）MySQL數(shù)據(jù)庫(kù)慢查詢(xún)?nèi)罩敬鎯?chǔ)路徑。可以不設(shè)置該參數(shù)，系統(tǒng)則會(huì)默認(rèn)給一個(gè)缺省的文件host_name-slow.log
• long_query_time ：慢查詢(xún)閾值，當(dāng)查詢(xún)時(shí)間多于設(shè)定的閾值時(shí)，記錄日志。
• log_queries_not_using_indexes：未使用索引的查詢(xún)也被記錄到慢查詢(xún)?nèi)罩局校蛇x項(xiàng)）。
• log_output：日志存儲(chǔ)方式。log_output=‘FILE’表示將日志存入文件，默認(rèn)值是’FILE’。log_output='TABLE’表示將日志存入數(shù)據(jù)庫(kù)，這樣日志信息就會(huì)被寫(xiě)入到mysql.slow_log表中。MySQL數(shù)據(jù)庫(kù)支持同時(shí)兩種日志存儲(chǔ)方式，配置的時(shí)候以逗號(hào)隔開(kāi)即可，如：log_output=‘FILE,TABLE’。日志記錄到系統(tǒng)的專(zhuān)用日志表中，要比記錄到文件耗費(fèi)更多的系統(tǒng)資源，因此對(duì)于需要啟用慢查詢(xún)?nèi)罩荆中枰軌颢@得更高的系統(tǒng)性能，那么建議優(yōu)先記錄到文件。

2、實(shí)現(xiàn)配置步驟

第一步.開(kāi)啟mysql慢查詢(xún)

方式一：

修改配置文件 在 my.ini 增加幾行: 主要是慢查詢(xún)的定義時(shí)間（超過(guò)2秒就是慢查詢(xún)），以及慢查詢(xún)log日志記錄（ slow_query_log）

方法二：通過(guò)MySQL數(shù)據(jù)庫(kù)開(kāi)啟慢查詢(xún):

二、分析慢查詢(xún)?nèi)罩?/h2>

直接分析mysql慢查詢(xún)?nèi)罩?,利用explain關(guān)鍵字可以模擬優(yōu)化器執(zhí)行SQL查詢(xún)語(yǔ)句，來(lái)分析sql慢查詢(xún)語(yǔ)句

例如：執(zhí)行EXPLAIN SELECT * FROM erp_bill_index ORDER BYmodifiedtime LIMIT 0,1000

得到如下結(jié)果： 顯示結(jié)果分析：

table | type | possible_keys | key |key_len | ref | rows | Extra EXPLAIN列的解釋?zhuān)?/p>

table 顯示這一行的數(shù)據(jù)是關(guān)于哪張表的

type 這是重要的列，顯示連接使用了何種類(lèi)型。從最好到最差的連接類(lèi)型為const、eq_reg、ref、range、indexhe和ALL

rows 顯示需要掃描行數(shù)

key 使用的索引

三、常見(jiàn)的慢查詢(xún)優(yōu)化

1、索引沒(méi)起作用的情況

1）. 使用LIKE關(guān)鍵字的查詢(xún)語(yǔ)句

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

2）. 使用多列索引的查詢(xún)語(yǔ)句

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

2、優(yōu)化數(shù)據(jù)庫(kù)結(jié)構(gòu)

合理的數(shù)據(jù)庫(kù)結(jié)構(gòu)不僅可以使數(shù)據(jù)庫(kù)占用更小的磁盤(pán)空間，而且能夠使查詢(xún)速度更快。數(shù)據(jù)庫(kù)結(jié)構(gòu)的設(shè)計(jì)，需要考慮數(shù)據(jù)冗余、查詢(xún)和更新的速度、字段的數(shù)據(jù)類(lèi)型是否合理等多方面的內(nèi)容。

1）. 將字段很多的表分解成多個(gè)表

對(duì)于字段比較多的表，如果有些字段的使用頻率很低，可以將這些字段分離出來(lái)形成新表。因?yàn)楫?dāng)一個(gè)表的數(shù)據(jù)量很大時(shí)，會(huì)由于使用頻率低的字段的存在而變慢。

2）. 增加中間表

對(duì)于需要經(jīng)常聯(lián)合查詢(xún)的表，可以建立中間表以提高查詢(xún)效率。通過(guò)建立中間表，把需要經(jīng)常聯(lián)合查詢(xún)的數(shù)據(jù)插入到中間表中，然后將原來(lái)的聯(lián)合查詢(xún)改為對(duì)中間表的查詢(xún)，以此來(lái)提高查詢(xún)效率。

3、分解關(guān)聯(lián)查詢(xún)

將一個(gè)大的查詢(xún)分解為多個(gè)小查詢(xún)是很有必要的。

很多高性能的應(yīng)用都會(huì)對(duì)關(guān)聯(lián)查詢(xún)進(jìn)行分解，就是可以對(duì)每一個(gè)表進(jìn)行一次單表查詢(xún)，然后將查詢(xún)結(jié)果在應(yīng)用程序中進(jìn)行關(guān)聯(lián)，很多場(chǎng)景下這樣會(huì)更高效，例如：

SELECT * FROM tag
JOIN tag_post ON tag_id = tag.id
JOIN post ON tag_post.post_id = post.id
WHERE tag.tag = ‘mysql';

分解為:

SELECT * FROM tag WHERE tag = ‘mysql';
SELECT * FROM tag_post WHERE tag_id = 1234;
SELECT * FROM post WHERE post.id in (123,456,567);

4、優(yōu)化LIMIT分頁(yè)

在系統(tǒng)中需要分頁(yè)的操作通常會(huì)使用limit加上偏移量的方法實(shí)現(xiàn)，同時(shí)加上合適的order by 子句。如果有對(duì)應(yīng)的索引，通常效率會(huì)不錯(cuò)，否則MySQL需要做大量的文件排序操作。

一個(gè)非常令人頭疼問(wèn)題就是當(dāng)偏移量非常大的時(shí)候，例如可能是limit 10000,20這樣的查詢(xún)，這是mysql需要查詢(xún)10020條然后只返回最后20條，前面的10000條記錄都將被舍棄，這樣的代價(jià)很高。

優(yōu)化此類(lèi)查詢(xún)的一個(gè)最簡(jiǎn)單的方法是盡可能的使用索引覆蓋掃描，而不是查詢(xún)所有的列。然后根據(jù)需要做一次關(guān)聯(lián)操作再返回所需的列。對(duì)于偏移量很大的時(shí)候這樣做的效率會(huì)得到很大提升。

對(duì)于下面的查詢(xún)：select id,title from collect limit 90000,10;

該語(yǔ)句存在的最大問(wèn)題在于limit M,N中偏移量M太大（我們暫不考慮篩選字段上要不要添加索引的影響），導(dǎo)致每次查詢(xún)都要先從整個(gè)表中找到滿(mǎn)足條件 的前M條記錄，之后舍棄這M條記錄并從第M+1條記錄開(kāi)始再依次找到N條滿(mǎn)足條件的記錄。如果表非常大，且篩選字段沒(méi)有合適的索引，且M特別大那么這樣的代價(jià)是非常高的。 試想，如我們下一次的查詢(xún)能從前一次查詢(xún)結(jié)束后標(biāo)記的位置開(kāi)始查找，找到滿(mǎn)足條件的100條記錄，并記下下一次查詢(xún)應(yīng)該開(kāi)始的位置，以便于下一次查詢(xún)能直接從該位置 開(kāi)始，這樣就不必每次查詢(xún)都先從整個(gè)表中先找到滿(mǎn)足條件的前M條記錄，舍棄，在從M+1開(kāi)始再找到100條滿(mǎn)足條件的記錄了。

常用的方法

1）、篩選字段上加索引

2）、先查出主鍵id值：

select id,title from collect where id>=(select id from collect order by id limit 90000,1) limit 10;

原理：先查詢(xún)出90000條數(shù)據(jù)對(duì)應(yīng)的主鍵id的值，然后直接通過(guò)該id的值直接查詢(xún)?cè)搃d后面的數(shù)據(jù)。

3）、關(guān)延遲聯(lián)

如果這個(gè)表非常大，那么這個(gè)查詢(xún)可以改寫(xiě)成如下的方式：

Select news.id, news.description from news inner join (select id from news order by title limit 50000,5) as myNew using(id);

這里的“關(guān)延遲聯(lián)”將大大提升查詢(xún)的效率，它讓MySQL掃描盡可能少的頁(yè)面，獲取需要的記錄后再根據(jù)關(guān)聯(lián)列回原表查詢(xún)需要的所有列。

這個(gè)技術(shù)也可以用在優(yōu)化關(guān)聯(lián)查詢(xún)中的limit。

4）、建立復(fù)合索引acct_id和create_time

select * from acct_trans_log WHERE acct_id = 3095 order by create_time desc limit 0,10

5）、查詢(xún)語(yǔ)句使用Group by和Order by會(huì)影響數(shù)據(jù)查詢(xún)效率

四、常用優(yōu)化方法

1． SQL語(yǔ)句的優(yōu)化

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

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來(lái)代替，因?yàn)閁nion操作符在進(jìn)行結(jié)果合并時(shí)，會(huì)對(duì)產(chǎn)生的結(jié)果進(jìn)行排序運(yùn)算，刪除重復(fù)記錄，對(duì)于沒(méi)有該需求的應(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ǔ)句中盡量 避免使用“*”，因?yàn)樵赟QL語(yǔ)句在解析的過(guò)程中，會(huì)將“”轉(zhuǎn)換成所有列的列名，而這個(gè)工作是通過(guò)查詢(xún)數(shù)據(jù)字典完成的，有一定的開(kāi)銷(xiāo)；

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

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

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

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

2． 表結(jié)構(gòu)的優(yōu)化

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

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

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

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

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

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

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

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

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

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

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

參考地址：

1、http://www.dbjr.com.cn/article/283072.htm

2、http://www.dbjr.com.cn/article/283082.htm

3、https://blog.csdn.net/kris1025/article/details/80085020

總結(jié)

到此這篇關(guān)于Mysql數(shù)據(jù)庫(kù)慢查詢(xún)常用優(yōu)化方式的文章就介紹到這了,更多相關(guān)Mysql慢查詢(xún)優(yōu)化方式內(nèi)容請(qǐng)搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持腳本之家！

最新評(píng)論