Mysql常見的慢查詢優化方式總結

目錄

• 前言
• （1）數據庫中設置SQL慢查詢
• （2）分析慢查詢日志
• （3）常見的慢查詢優化
• 總結

前言

這篇文章主要是就在公司實習的時候，對SQL優化工作作出的一些整理。

在公司實習的時候，導師分配了SQL慢查詢優化的任務，任務是這樣的：每周從平臺中導出生產數據庫的慢查詢文件進行分析。進行SQL優化的手段也主要是修改SQL寫法，或者新增索引。

現在從記錄項目中的一點點做起。

（1）數據庫中設置SQL慢查詢

一、第一步.開啟mysql慢查詢

方式一：

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

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

（2）分析慢查詢日志

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

例如：執行EXPLAIN SELECT * FROM res_user 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 使用的索引

（3）常見的慢查詢優化

（1）索引沒起作用的情況

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

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

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

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

（2）優化數據庫結構

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

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

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

2. 增加中間表

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

（3）分解關聯查詢

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

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

 SELECT * FROM tag JOIN 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條滿足條件的記錄了。

方法一：慮篩選字段（title）上加索引

title字段加索引 （此效率如何未加驗證）

方法二：先查詢出主鍵id值

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

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

方法三：“關延遲聯”

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

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。

方法四：建立復合索引 acct_id和create_time

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

注意sql查詢慢的原因都是:引起filesort

（5）分析具體的SQL語句 1、兩個表選哪個為驅動表，表面是可以以數據量的大小作為依據，但是實際經驗最好交給mysql查詢優化器自己去判斷。 例如： select * from a where id in (select id from b );

對于這條sql語句它的執行計劃其實并不是先查詢出b表的所有id,然后再與a表的id進行比較。
mysql會把in子查詢轉換成exists相關子查詢，所以它實際等同于這條sql語句：select * from a where exists(select * from b where b.id=a.id );

而exists相關子查詢的執行原理是: 循環取出a表的每一條記錄與b表進行比較，比較的條件是a.id=b.id . 看a表的每條記錄的id是否在b表存在，如果存在就行返回a表的這條記錄。

exists查詢有什么弊端？

由exists執行原理可知，a表(外表)使用不了索引，必須全表掃描，因為是拿a表的數據到b表查。而且必須得使用a表的數據到b表中查（外表到里表中），順序是固定死的。

如何優化？

建索引。但是由上面分析可知，要建索引只能在b表的id字段建，不能在a表的id上，mysql利用不上。

這樣優化夠了嗎？還差一些。

由于exists查詢它的執行計劃只能拿著a表的數據到b表查（外表到里表中），雖然可以在b表的id字段建索引來提高查詢效率。
但是并不能反過來拿著b表的數據到a表查，exists子查詢的查詢順序是固定死的。

為什么要反過來？

因為首先可以肯定的是反過來的結果也是一樣的。這樣就又引出了一個更細致的疑問：在雙方兩個表的id字段上都建有索引時，到底是a表查b表的效率高，還是b表查a表的效率高？

該如何進一步優化？

把查詢修改成inner join連接查詢：select * from a inner join b on a.id=b.id; （但是僅此還不夠，接著往下看）

為什么不用left join 和 right join？

這時候表之間的連接的順序就被固定住了，比如左連接就是必須先查左表全表掃描，然后一條一條的到另外表去查詢，右連接同理。仍然不是最好的選擇。

為什么使用inner join就可以？

inner join中的兩張表，如： a inner join b，但實際執行的順序是跟寫法的順序沒有半毛錢關系的，最終執行也可能會是b連接a，順序不是固定死的。如果on條件字段有索引的情況下，同樣可以使用上索引。

那我們又怎么能知道a和b什么樣的執行順序效率更高？

你不知道，我也不知道。誰知道？mysql自己知道。讓mysql自己去判斷（查詢優化器）。具體表的連接順序和使用索引情況，mysql查詢優化器會對每種情況做出成本評估，最終選擇最優的那個做為執行計劃。

在inner join的連接中,mysql會自己評估使用a表查b表的效率高還是b表查a表高，如果兩個表都建有索引的情況下，mysql同樣會評估使用a表條件字段上的索引效率高還是b表的。

利用explain字段查看執行時運用到的key（索引）

而我們要做的就是：把兩個表的連接條件的兩個字段都各自建立上索引，然后explain 一下，查看執行計劃，看mysql到底利用了哪個索引，最后再把沒有使用索引的表的字段索引給去掉就行了。

總結

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