前言

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

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

現(xiàn)在從記錄項目中的一點點做起。

（1）數(shù)據(jù)庫中設(shè)置SQL慢查詢

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

方式一：

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

方法二：通過MySQL數(shù)據(jù)庫開啟慢查詢:

（2）分析慢查詢?nèi)罩?nbsp;        

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

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

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

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

• table                 顯示這一行的數(shù)據(jù)是關(guān)于哪張表的           
• type                  這是重要的列，顯示連接使用了何種類型。從最好到最差的連接類型為const、eq_reg、ref、range、indexhe和ALL 
• rows                顯示需要掃描行數(shù)
• key                   使用的索引

（3）常見的慢查詢優(yōu)化

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

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

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

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

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

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

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

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

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

2. 增加中間表

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

（3）分解關(guān)聯(lián)查詢

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

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

 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分頁

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

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

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

對于下面的查詢：

select id,title from collect limit 90000,10;

該語句存在的最大問題在于limit M,N中偏移量M太大（我們暫不考慮篩選字段上要不要添加索引的影響），導(dǎo)致每次查詢都要先從整個表中找到滿足條件 的前M條記錄，之后舍棄這M條記錄并從第M+1條記錄開始再依次找到N條滿足條件的記錄。如果表非常大，且篩選字段沒有合適的索引，且M特別大那么這樣的代價是非常高的。 試想，如我們下一次的查詢能從前一次查詢結(jié)束后標記的位置開始查找，找到滿足條件的100條記錄，并記下下一次查詢應(yīng)該開始的位置，以便于下一次查詢能直接從該位置 開始，這樣就不必每次查詢都先從整個表中先找到滿足條件的前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條數(shù)據(jù)對應(yīng)的主鍵id的值，然后直接通過該id的值直接查詢該id后面的數(shù)據(jù)。

方法三：“關(guān)延遲聯(lián)”

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

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)”將大大提升查詢的效率，它讓MySQL掃描盡可能少的頁面，獲取需要的記錄后再根據(jù)關(guān)聯(lián)列回原表查詢需要的所有列。這個技術(shù)也可以用在優(yōu)化關(guān)聯(lián)查詢中的limit。

方法四：建立復(fù)合索引 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、兩個表選哪個為驅(qū)動表，表面是可以以數(shù)據(jù)量的大小作為依據(jù)，但是實際經(jīng)驗最好交給mysql查詢優(yōu)化器自己去判斷。   例如：  select * from a where id in (select id from b );  

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

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

exists查詢有什么弊端？

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

如何優(yōu)化？

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

這樣優(yōu)化夠了嗎？還差一些。

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

為什么要反過來？

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

該如何進一步優(yōu)化？

把查詢修改成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，但實際執(zhí)行的順序是跟寫法的順序沒有半毛錢關(guān)系的，最終執(zhí)行也可能會是b連接a，順序不是固定死的。如果on條件字段有索引的情況下，同樣可以使用上索引。

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

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

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

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

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

總結(jié)

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