如何分析Mysql慢SQL？
內(nèi)容摘要：
開啟慢查詢?nèi)罩静东@慢SQL
使用explain分析慢SQL
使用show profile查詢SQL執(zhí)行細節(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?11、id
SELECT查詢的序列號，包含一組數(shù)字，表示查詢中執(zhí)行SELECT語句或操作表的順序
包含三種情況：
① id相同，執(zhí)行順序由上至下
② id不同，如果是子查詢，id序號會遞增，id值越大優(yōu)先級越高，越先被執(zhí)行
③ 既有相同的，又有不同的。id如果相同認為是一組，執(zhí)行順序由上至下；在所有組中，id值越大優(yōu)先級越高，越先執(zhí) 行。
2、select_type
SIMPLE:? ?簡單SELECT查詢，查詢中不包含子查詢或者UNION
? ? ?
PRIMARY: 查詢中包含任何復(fù)雜的子部分，最外層的查詢
? ??
SUBQUERY: SELECT或WHERE中包含的子查詢部分
? ??
DERIVED: 在FROM中包含的子查詢被標記為DERIVER(衍生)， MySQL會遞歸執(zhí)行這些子查詢，把結(jié)果放到臨時表中
? ??
UNION: 若第二個SELECT出現(xiàn)UNION，則被標記為UNION, 若UNION包含在FROM子句的子查詢中，外層子查詢將被標記為DERIVED
? ??
UNION RESULT: 從UNION表獲取結(jié)果的SELECT
3、table
? ??
顯示這一行數(shù)據(jù)是關(guān)于哪張表的
4、type?
? ??
type顯示的是訪問類型，是較為重要的一個指標，結(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í)行細節(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、如果確認查詢結(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
原文鏈接：cnblogs.com/linyue09/p/9869163.html