內(nèi)容摘要：

開啟慢查詢?nèi)罩静东@慢SQL

使用explain分析慢SQL

使用show profile查詢SQL執(zhí)行細(xì)節(jié)

常見的SQL語句優(yōu)化

一、開啟慢查詢?nèi)罩静东@慢SQL

① 查詢mysql是否開啟慢日志捕獲：SHOW VARIABLES LIKE '%slow_query_log%';

如果還沒開啟的話，開啟：SET GLOBAL slow_query_log=1;

②? 查看慢查詢的時間闕值：SHOW GLOBAL VARIABLES LIKE '%long_query_time%';

可以根據(jù)實際情況去調(diào)整時間：SET GLOBAL long_query_time=2;

③ 查詢多少SQL超過了慢查詢時間的闕值：?SHOW GLOBAL STATUS LIKE '%Slow_queries%';

④ 使用MySQL提供的日志分析工具mysqldumpslow，獲取差慢SQL

舉例：

--獲取慢日志中最多的10個SQL

./mysqldumpslow -s r -t 10 /PATH/TO/慢日志文件

--獲取按照時間排序的前10條里面含有左連接的查詢語句

mysqldumpslow -s t -t 10 -g "left join" 慢日志文件

更多用法可以使用：./mysqldumpslow --help 查看

二、使用explain分析慢SQL

舉例：EXPLAIN SELECT a.username FROM tb_admin a LEFT JOIN tb_group p ON a.groupId =?p.id?WHERE a.username = 'xiaophai' LIMIT 1

1、id

SELECT查詢的序列號，包含一組數(shù)字，表示查詢中執(zhí)行SELECT語句或操作表的順序

包含三種情況：

① id相同，執(zhí)行順序由上至下

② id不同，如果是子查詢，id序號會遞增，id值越大優(yōu)先級越高，越先被執(zhí)行

③ 既有相同的，又有不同的。id如果相同認(rèn)為是一組，執(zhí)行順序由上至下； 在所有組中，id值越大優(yōu)先級越高，越先執(zhí) 行。

2、select_type

SIMPLE:? ?簡單SELECT查詢，查詢中不包含子查詢或者UNION

PRIMARY: 查詢中包含任何復(fù)雜的子部分，最外層的查詢

SUBQUERY: SELECT或WHERE中包含的子查詢部分

DERIVED: 在FROM中包含的子查詢被標(biāo)記為DERIVER(衍生)， MySQL會遞歸執(zhí)行這些子查詢，把結(jié)果放到臨時表中

UNION: 若第二個SELECT出現(xiàn)UNION，則被標(biāo)記為UNION, 若UNION包含在FROM子句的子查詢中，外層子查詢將被標(biāo)記為DERIVED

UNION RESULT: 從UNION表獲取結(jié)果的SELECT

3、table

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

4、type

type顯示的是訪問類型，是較為重要的一個指標(biāo)，結(jié)果值從最好到最壞依次是：

system>const>eq_ref>ref>fulltext>ref_or_null>index_merge>unique_subquery>index_subquery>range>index>ALL

一般來說，得保證查詢至少達到range級別，最好能達到ref。

system：表只有一行記錄(等于系統(tǒng)表)，這是const類型的特例，平時不會出現(xiàn)

const：如果通過索引依次就找到了，const用于比較主鍵索引或者unique索引。 因為只能匹配一行數(shù)據(jù)，所以很快。如果將主鍵置于where列表中，MySQL就能將該查詢轉(zhuǎn)換為一個常量

eq_ref：唯一性索引掃描，對于每個索引鍵，表中只有一條記錄與之匹配。常見于主鍵或唯一索引掃描

ref：非唯一性索引掃描，返回匹配某個單獨值的所有行。本質(zhì)上也是一種索引訪問，它返回所有匹配 某個單獨值的行，然而它可能會找到多個符合條件的行，所以它應(yīng)該屬于查找和掃描的混合體

range：只檢索給定范圍的行，使用一個索引來選擇行。key列顯示使用了哪個索引，一般就是在你的where語句中出現(xiàn)between、、in等的查詢，這種范圍掃描索引比全表掃描要好，因為只需要開始于縮印的某一點，而結(jié)束于另一點，不用掃描全部索引

index：Full Index Scan ，index與ALL的區(qū)別為index類型只遍歷索引樹，這通常比ALL快，因為索引文件通常比數(shù)據(jù)文件小。 (也就是說雖然ALL和index都是讀全表， 但index是從索引中讀取的，而ALL是從硬盤讀取的)

all：Full Table Scan，遍歷全表獲得匹配的行

5、possible_keys

顯示可能應(yīng)用在這張表中的索引，一個或多個。 查詢涉及到的字段上若存在索引，則該索引將被列出，但不一定被查詢實際使用

6、key

實際使用的索引。如果為NULL，則沒有使用索引。

查詢中若出現(xiàn)了覆蓋索引，則該索引僅出現(xiàn)在key列表中。

7、key_len

表示索引中使用的字節(jié)數(shù)，可通過該列計算查詢中使用的索引的長度。在不損失精度的情況下，長度越短越好。

key_len顯示的值為索引字段的最大可能長度，并非實際使用長度，即key_len是根據(jù)表定義計算而得，不是通過表內(nèi)檢索出的。

8、ref

顯示索引的哪一列被使用了，哪些列或常量被用于查找索引列上的值。

9、rows

根據(jù)表統(tǒng)計信息及索引選用情況，大致估算出找到所需記錄多需要讀取的行數(shù)。

10、Extra

包含不適合在其他列中顯示但十分重要的額外信息：

① Using filesort： 說明MySQL會對數(shù)據(jù)使用一個外部的索引排序，而不是按照表內(nèi)的索引順序進行讀取。MySQL中無法利用索引完成的排序操作稱為“文件排序”

② Using temporary： 使用了臨時表保存中間結(jié)果，MySQL在對查詢結(jié)果排序時使用臨時表。常見于排序order by和分組查詢group by

③ Using index： 表示相應(yīng)的SELECT操作中使用了覆蓋索引(Covering Index)，避免訪問了表的數(shù)據(jù)行，效率不錯。 如果同時出現(xiàn)using where，表明索引被用來執(zhí)行索引鍵值的查找； 如果沒有同時出現(xiàn)using where，表明索引用來讀取數(shù)據(jù)而非執(zhí)行查找動作 覆蓋索引(Covering Index)： 理解方式1：SELECT的數(shù)據(jù)列只需要從索引中就能讀取到，不需要讀取數(shù)據(jù)行，MySQL可以利用索引返回SELECT列表中 的字段，而不必根據(jù)索引再次讀取數(shù)據(jù)文件，換句話說查詢列要被所建的索引覆蓋 理解方式2：索引是高效找到行的一個方法，但是一般數(shù)據(jù)庫也能使用索引找到一個列的數(shù)據(jù)，因此他不必讀取整個行。 畢竟索引葉子節(jié)點存儲了他們索引的數(shù)據(jù)；當(dāng)能通過讀取索引就可以得到想要的數(shù)據(jù)，那就不需要讀取行了，一個索引 包含了(覆蓋)滿足查詢結(jié)果的數(shù)據(jù)就叫做覆蓋索引 注意： 如果要使用覆蓋索引，一定要注意SELECT列表中只取出需要的列，不可SELECT *, 因為如果所有字段一起做索引會導(dǎo)致索引文件過大查詢性能下降

④ impossible where： WHERE子句的值總是false，不能用來獲取任何元組

⑤ select tables optimized away： 在沒有GROUP BY子句的情況下基于索引優(yōu)化MIN/MAX操作或者對于MyISAM存儲引擎優(yōu)化COUNT(*)操作， 不必等到執(zhí)行階段再進行計算，查詢執(zhí)行計劃生成的階段即完成優(yōu)化

⑥ distinct： 優(yōu)化distinct操作，在找到第一匹配的元祖后即停止找同樣值的操作

三、使用show profile查詢SQL執(zhí)行細(xì)節(jié)

Show Profile是MySQL提供可以用來分析當(dāng)前會話中語句執(zhí)行的資源消耗情況，可以用于SQL的調(diào)優(yōu)測量

分析步驟

1、查看狀態(tài)：SHOW VARIABLES LIKE 'profiling';

2、開啟：set profiling=on;

3、查看結(jié)果：show profiles;

4、診斷SQL：show profile cpu,block io for query 上一步SQL數(shù)字號碼;

ALL：顯示所有開銷信息

BLOCK IO：顯示IO相關(guān)開銷

CONTEXT SWITCHES：顯示上下文切換相關(guān)開銷

CPU：顯示CPU相關(guān)開銷

IPC：顯示發(fā)送接收相關(guān)開銷

MEMORY：顯示內(nèi)存相關(guān)開銷

PAGE FAULTS：顯示頁面錯誤相關(guān)開銷

SOURCE：顯示和Source_function，Source_file，Source_line相關(guān)開銷

SWAPS：顯示交換次數(shù)相關(guān)開銷

注意(遇到這幾種情況要優(yōu)化)

converting HEAP to MyISAM： 查詢結(jié)果太大，內(nèi)存不夠用往磁盤上搬

Creating tmp table：創(chuàng)建臨時表

Copying to tmp table on disk：把內(nèi)存中的臨時表復(fù)制到磁盤

locked

四、常見的SQL語句優(yōu)化

1、盡量避免在where字句中使用or來連接條件，否則將導(dǎo)致放棄使用索引而進行全表掃描。

select id from user where username='15623695987' or mobile='15623695987';

可以這樣查詢:

select id from user where username='15623695987'?union all? select id from user where mobile='15623695987';

2、正確使用like查詢。

%xx%查詢會導(dǎo)致索引使用不上：

select id from user where username like '%test%'

正確使用索引:

select id from user where username like 'test%'

3、盡量避免在where字句中對字段進行表達式操作

錯誤做法：

select id from user where score/2 =100;

應(yīng)改為：

select id from user where score =100*2;

4、如果確認(rèn)查詢結(jié)果數(shù)量，應(yīng)盡可能加上limit

select id from user where username='test' limit 1;

5、不用要使用隱式轉(zhuǎn)換

錯誤例子：

select id from user where mobile=15689764359 limit 1;

select username from user where id='15' limit 1;

正確做法：

select id from user where mobile='15689764359'?limit 1;

select username from user where id=15?limit 1;

6、正確使用復(fù)合索引

舉例：user表，index? idx_username(username,group_id)

能使用索引：

select id from user where username='test' and group_id=1;

select id from user where?username='test'

select id from user where?group_id=1 and?username='test'

不能使用索引：

select id from user where group_id=1;

總結(jié)： 是否使用索引與idx_username(username,group_id)，這兩個字段的先后順序有關(guān)

7、如果使用了join，請盡量使用小表join大表

8、正確使用exists和in

① in

select `user`.id,`user`.username from `user` where `user`.id in (select `order`.user_id from `order`)

② exists

select `user`.id,`user`.username from `user` where exists (select`order`.user_id from `order` where `user`.id = `order`.user_id)

應(yīng)用場景參考：如果子查詢得出的結(jié)果集記錄較少，主查詢中的表較大且又有索引時應(yīng)該用in, 反之如果外層的主查詢記錄較少，子查詢中的表大，又有索引時使用exists

總結(jié)

以上是生活随笔為你收集整理的mysql 慢sql分析_如何分析Mysql慢SQL的全部內(nèi)容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網(wǎng)站內(nèi)容還不錯，歡迎將生活随笔推薦給好友。