目錄

• 慢查詢日志概念
• 一、數據庫中設置SQL慢查詢
• 1、mysql慢查詢相關配置參數介紹
• 2、實現配置步驟
• 二、分析慢查詢日志
• 三、常見的慢查詢優化
• 1、索引沒起作用的情況
• 2、優化數據庫結構
• 3、分解關聯查詢
• 4、優化LIMIT分頁
• 四、常用優化方法
• 1． SQL語句的優化
• 2． 表結構的優化
• 總結

慢查詢日志概念

MySQL的慢查詢日志是MySQL提供的一種日志記錄，它用來記錄在MySQL中響應時間超過閥值的語句，具體指運行時間超過long_query_time值的SQL，則會被記錄到慢查詢日志中。long_query_time的默認值為10，意思是運行10S以上的語句。默認情況下，Mysql數據庫并不啟動慢查詢日志，需要我們手動來設置這個參數，當然，如果不是調優需要的話，一般不建議啟動該參數，因為開啟慢查詢日志會或多或少帶來一定的性能影響。慢查詢日志支持將日志記錄寫入文件，也支持將日志記錄寫入數據庫表。

一、數據庫中設置SQL慢查詢

1、mysql慢查詢相關配置參數介紹

• slow_query_log ：是否開啟慢查詢日志，1表示開啟，0表示關閉。
• log-slow-queries：舊版（5.6以下版本）MySQL數據庫慢查詢日志存儲路徑。可以不設置該參數，系統則會默認給一個缺省的文件host_name-slow.log
• slow-query-log-file：新版（5.6及以上版本）MySQL數據庫慢查詢日志存儲路徑。可以不設置該參數，系統則會默認給一個缺省的文件host_name-slow.log
• long_query_time ：慢查詢閾值，當查詢時間多于設定的閾值時，記錄日志。
• log_queries_not_using_indexes：未使用索引的查詢也被記錄到慢查詢日志中（可選項）。
• log_output：日志存儲方式。log_output=‘FILE’表示將日志存入文件，默認值是’FILE’。log_output='TABLE’表示將日志存入數據庫，這樣日志信息就會被寫入到mysql.slow_log表中。MySQL數據庫支持同時兩種日志存儲方式，配置的時候以逗號隔開即可，如：log_output=‘FILE,TABLE’。日志記錄到系統的專用日志表中，要比記錄到文件耗費更多的系統資源，因此對于需要啟用慢查詢日志，又需要能夠獲得更高的系統性能，那么建議優先記錄到文件。

2、實現配置步驟

第一步.開啟mysql慢查詢

方式一：

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

方法二：通過MySQL數據庫開啟慢查詢:

二、分析慢查詢日志

直接分析mysql慢查詢日志 ,利用explain關鍵字可以模擬優化器執行SQL查詢語句，來分析sql慢查詢語句

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

得到如下結果： 顯示結果分析：

table | type | possible_keys | key |key_len | ref | rows | Extra EXPLAIN列的解釋：

table 顯示這一行的數據是關于哪張表的

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

rows 顯示需要掃描行數

key 使用的索引

三、常見的慢查詢優化

1、索引沒起作用的情況

1）. 使用LIKE關鍵字的查詢語句

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

2）. 使用多列索引的查詢語句

MySQL可以為多個字段創建索引。一個索引最多可以包括16個字段。對于多列索引，只有查詢條件使用了這些字段中的第一個字段時，索引才會被使用。

2、優化數據庫結構

合理的數據庫結構不僅可以使數據庫占用更小的磁盤空間，而且能夠使查詢速度更快。數據庫結構的設計，需要考慮數據冗余、查詢和更新的速度、字段的數據類型是否合理等多方面的內容。

1）. 將字段很多的表分解成多個表

對于字段比較多的表，如果有些字段的使用頻率很低，可以將這些字段分離出來形成新表。因為當一個表的數據量很大時，會由于使用頻率低的字段的存在而變慢。

2）. 增加中間表

對于需要經常聯合查詢的表，可以建立中間表以提高查詢效率。通過建立中間表，把需要經常聯合查詢的數據插入到中間表中，然后將原來的聯合查詢改為對中間表的查詢，以此來提高查詢效率。

3、分解關聯查詢

將一個大的查詢分解為多個小查詢是很有必要的。

很多高性能的應用都會對關聯查詢進行分解，就是可以對每一個表進行一次單表查詢，然后將查詢結果在應用程序中進行關聯，很多場景下這樣會更高效，例如：

SELECT * FROM tagJOIN tag_post ON tag_id = tag.idJOIN post ON tag_post.post_id = post.idWHERE 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、優化LIMIT分頁

在系統中需要分頁的操作通常會使用limit加上偏移量的方法實現，同時加上合適的order by 子句。如果有對應的索引，通常效率會不錯，否則MySQL需要做大量的文件排序操作。

一個非常令人頭疼問題就是當偏移量非常大的時候，例如可能是limit 10000,20這樣的查詢，這是mysql需要查詢10020條然后只返回最后20條，前面的10000條記錄都將被舍棄，這樣的代價很高。

優化此類查詢的一個最簡單的方法是盡可能的使用索引覆蓋掃描，而不是查詢所有的列。然后根據需要做一次關聯操作再返回所需的列。對于偏移量很大的時候這樣做的效率會得到很大提升。

對于下面的查詢：select id,title from collect limit 90000,10;

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

常用的方法

1）、篩選字段上加索引

2）、先查出主鍵id值：

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

原理：先查詢出90000條數據對應的主鍵id的值，然后直接通過該id的值直接查詢該id后面的數據。

3）、關延遲聯

如果這個表非常大，那么這個查詢可以改寫成如下的方式：

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

這里的“關延遲聯”將大大提升查詢的效率，它讓MySQL掃描盡可能少的頁面，獲取需要的記錄后再根據關聯列回原表查詢需要的所有列。

這個技術也可以用在優化關聯查詢中的limit。

4）、建立復合索引acct_id和create_time

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

5）、查詢語句使用Group by和Order by會影響數據查詢效率

四、常用優化方法

1． 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的記錄。

2． 表結構的優化

這里主要指如何正確的建立索引，因為不合理的索引會導致查詢全表掃描，同時過多的索引會帶來插入和更新的性能開銷；

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對應的記錄數據不多，所以其他字段使用不用索引基本無影響，同時也可以避免建立過多的索引帶來的插入和更新的性能開銷；

參考地址：

1、https://www.jb51.net/article/283072.htm

2、https://www.jb51.net/article/283082.htm

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

總結

到此這篇關于Mysql數據庫慢查詢常用優化方式的文章就介紹到這了,更多相關Mysql慢查詢優化方式內容請搜索以前的文章或繼續瀏覽下面的相關文章希望大家以后多多支持！