分析工具

Mysqldumpslow

mysqldumpslow是mysql自帶的用來分析慢查詢的工具宣吱，基于perl開發(fā)灌具。
Windows下需要下載安裝perl編譯器桑腮，下載地址：http://pan.baidu.com/s/1i3GLKAp
參考：https://www.cnblogs.com/moss_tan_jun/p/8025504.html

C:\Program Files\MySQL\MySQL Server 5.6\bin>perl mysqldumpslow.pl --help

image.png

perl mysqldumpslow.pl -r -s c -a -t 3 "C:\ProgramData\MySQL\MySQL Server 5.7\Data\bxg_mysql_slow.log"

image.png

Count: 4（執(zhí)行了多少次）  

Time=375.01s（每次執(zhí)行的時(shí)間） (1500s)（一共執(zhí)行了多少時(shí)間）  

Lock=0.00s (0s)（等待鎖的時(shí)間）

Rows=10200.3（每次返回的記錄數(shù)） (40801)（總共返回的記錄數(shù)）, username[password]@[10.194.172.41]

mysqlsla

Mysqlsla 是daniel-nichter 用perl 寫的一個(gè)腳本耻瑟，專門用于處理分析Mysql的日志而存在蚓土。通過Mysql的日志主要分為：General log本辐，slow log桥帆，binary log三種。通 過query日志慎皱，我們可以分析業(yè)務(wù)的邏輯环葵，業(yè)務(wù)特點(diǎn)。通過slow log宝冕，我們可以找到服務(wù)器的瓶頸张遭。通過binary log，我們可以恢復(fù)數(shù)據(jù)地梨。Mysqlsla可以處理其中的任意日志菊卷。

參考：https://yq.aliyun.com/articles/59260

pt-query-digest

pt-query-digest是用于分析mysql慢查詢的一個(gè)工具缔恳，它可以分析binlog、General log洁闰、slowlog歉甚，也可以通過SHOWPROCESSLIST或者通過tcpdump抓取的MySQL協(xié)議數(shù)據(jù)來進(jìn)行分析∑嗣迹可以把分析結(jié)果輸出到文件中纸泄，分析過程是先對(duì)查詢語(yǔ)句的條件進(jìn)行參數(shù)化，然后對(duì)參數(shù)化以后的查詢進(jìn)行分組統(tǒng)計(jì)腰素，統(tǒng)計(jì)出各查詢的執(zhí)行時(shí)間聘裁、次數(shù)、占比等弓千，可以借助分析結(jié)果找出問題進(jìn)行優(yōu)化衡便。

參考：https://blog.csdn.net/seteor/article/details/24017913

EXPLAIN執(zhí)行計(jì)劃

用法

1. EXPLAIN SELECT ……

經(jīng)常使用的方式，查看sql的執(zhí)行計(jì)劃

2. EXPLAIN EXTENDED SELECT ……

將執(zhí)行計(jì)劃"反編譯"成SELECT語(yǔ)句洋访，運(yùn)行SHOW WARNINGS 镣陕，可得到被MySQL優(yōu)化器優(yōu)化后的查詢語(yǔ)句。

3. EXPLAIN PARTITIONS SELECT ……

用于分區(qū)表的EXPLAIN生成QEP的信息姻政，用來查看索引是否正在被使用呆抑，并且輸出其使用的索引的信息。

EXPLAIN SELECT id,fname,lname FROM person WHERE lname='x8RJWmQX' AND

id in (select id from person where id BETWEEN 0 and 6000);

image.png

id

包含一組數(shù)字汁展，表示查詢中執(zhí)行select子句或操作表的順序鹊碍，id相同執(zhí)行順序由上至下。如果是子查詢善镰，id的序號(hào)會(huì)遞增妹萨，id值越大優(yōu)先級(jí)越高年枕，越先被執(zhí)行

select_type

所使用的SELECT查詢類型炫欺，包括以下常見類型：

a. SIMPLE：表示為簡(jiǎn)單的SELECT，查詢中不包含子查詢或者UNION

b. PRIMARY：查詢中若包含任何復(fù)雜的子部分熏兄，最外層查詢則被標(biāo)記為PRIMARY

c. SUBQUERY：在SELECT或WHERE列表中包含了子查詢品洛，該子查詢被標(biāo)記為SUBQUERY

d. UNION： 表連接中的第二個(gè)或后面的select語(yǔ)句，若第二個(gè)SELECT出現(xiàn)在UNION之后摩桶，則被標(biāo)記為UNION桥状。

e. DERIVED：DERIVED（衍生）用來表示包含在from子句中的子查詢的select。若UNION包含在FROM子句的子查詢中硝清，外層SELECT將被標(biāo)記為DERIVED辅斟。mysql會(huì)遞歸執(zhí)行并將結(jié)果放到一個(gè)臨時(shí)表中。服務(wù)器內(nèi)部稱為"派生表"芦拿，因?yàn)樵撆R時(shí)表是從子查詢中派生出來的

f.UNION RESULT：從UNION表獲取結(jié)果的SELECT被標(biāo)記為UNION RESULT

g.DEPENDENT：意味著select依賴于外層查詢中發(fā)現(xiàn)的數(shù)據(jù)士飒。

h.UNCACHEABLE：意味著select中的某些特性阻止結(jié)果被緩存于一個(gè)item_cache中查邢。

table

所使用的的數(shù)據(jù)表的名字，他們按被讀取的先后順序排列酵幕。

type

表示MySQL在表中找到所需行的方式扰藕，又稱“訪問類型”。取值按優(yōu)劣排序?yàn)?strong>NULL****>system > const > eq_ref > ref > fulltext > ref_or_null > index_merge > unique_subquery > index_subquery > range > index > ALL芳撒。一般來說邓深，得保證查詢至少達(dá)到range級(jí)別，最好能達(dá)到ref笔刹。

a.ALL：Full Table Scan全表掃描芥备，MySQL將遍歷全表以找到匹配的行。

b.index：Full Index Scan全索引掃描徘熔，index與ALL區(qū)別為index類型只遍歷索引樹

c. range：索引范圍掃描门躯，對(duì)索引的掃描開始于某一點(diǎn)，返回匹配值域的行酷师。顯而易見的索引范圍掃描是帶有between或者where子句里帶有<, >查詢讶凉。當(dāng)mysql使用索引去查找一系列值時(shí)，例如IN()和OR列表山孔，也會(huì)顯示range（范圍掃描）,當(dāng)然性能上面是有差異的懂讯。

d. ref_or_null：該聯(lián)接類型如同ref,但是添加了MySQL可以專門搜索包含NULL值的行。

e. index_merge：該聯(lián)接類型表示使用了索引合并優(yōu)化方法台颠。

f. unique_subquery：該類型替換了下面形式的IN子查詢的ref: value IN (SELECT primary_key FROM single_table WHERE some_expr) 褐望。unique_subquery是一個(gè)索引查找函數(shù),可以完全替換子查詢,效率更高。

g. index_subquery：該聯(lián)接類型類似于unique_subquery串前√崩铮可以替換IN子查詢,但只適合下列形式的子查詢中的非唯一索引: value IN (SELECT key_column FROM single_table WHERE some_expr)

h.ref：就是連接程序無法根據(jù)鍵值只取得一條記錄，使用索引的最左前綴或者索引不是 primary key 或 unique索引的情況荡碾。當(dāng)根據(jù)鍵值只查詢到少數(shù)幾條匹配的記錄時(shí)谨读，這就是一個(gè)不錯(cuò)的連接類型。

i.eq_ref：類似ref坛吁，區(qū)別就在使用的索引是唯一索引劳殖，對(duì)于每個(gè)索引鍵值，表中只有一條記錄匹配拨脉，簡(jiǎn)單來說哆姻，就是多表連接中使用primary key或者 unique key作為關(guān)聯(lián)條件.

j.const、system：當(dāng)MySQL對(duì)查詢某部分進(jìn)行優(yōu)化玫膀，并轉(zhuǎn)換為一個(gè)常量時(shí)矛缨，使用這些類型訪問。如將主鍵置于where列表中，MySQL就能將該查詢轉(zhuǎn)換為一個(gè)常量箕昭。

注：system是const類型的特例誉简，當(dāng)查詢的表只有一行的情況下，使用system

k.NULL：MySQL在優(yōu)化過程中分解語(yǔ)句盟广，執(zhí)行時(shí)甚至不用訪問表或索引闷串，例如從一個(gè)索引列里選取最小值可以通過單獨(dú)索引查找完成。

explain select * from address where id = (select min(id) from person);

image.png

possible_keys

指出MySQL能使用哪個(gè)索引在表中找到記錄筋量，查詢涉及到的字段上若存在索引烹吵，則該索引將被列出，但不一定被查詢使用

key

顯示MySQL在查詢中實(shí)際使用的索引桨武，若沒有使用索引肋拔，顯示為NULL

key_len

表示索引中使用的字節(jié)數(shù)，可通過該列計(jì)算查詢中使用的索引的長(zhǎng)度（key_len顯示的值為索引字段的最大可能長(zhǎng)度呀酸，并非實(shí)際使用長(zhǎng)度凉蜂。如果鍵是NULL,則長(zhǎng)度為NULL。

ref

顯示索引的哪一列被使用了性誉，有時(shí)候會(huì)是一個(gè)常量：表示哪些列或常量被用于用于查找索引列上的值窿吩，可能值為庫(kù).表.字段、常量错览、null纫雁。

rows

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

filtered

顯示了通過條件過濾出的行數(shù)的百分比估計(jì)值轧邪。

extra

包含不適合在其他列中顯示但十分重要的額外信息，提供了與關(guān)聯(lián)操作有關(guān)的信息羞海，沒有則什么都不寫忌愚。

a.Using index：該值表示相應(yīng)的select操作中使用了覆蓋索引（Covering Index）。

MySQL可以利用索引返回select列表中的字段却邓，而不必根據(jù)索引再次讀取數(shù)據(jù)文件包含所有滿足查詢需要的數(shù)據(jù)的索引稱為覆蓋索引（Covering Index）硕糊。注意：如果要使用覆蓋索引，一定要注意select列表中只取出需要的列申尤，不可select *癌幕，因?yàn)槿绻麑⑺凶侄我黄鹱鏊饕龝?huì)導(dǎo)致索引文件過大衙耕，查詢性能下降昧穿。

b.Using where：表示mysql服務(wù)器將在存儲(chǔ)引擎檢索行后再進(jìn)行過濾。許多where條件里涉及索引中的列橙喘，當(dāng)（并且如果）它讀取索引時(shí)时鸵，就能被存儲(chǔ)引擎檢驗(yàn)，因此不是所有帶where字句的查詢都會(huì)顯示"Using where"。有時(shí)"Using where"的出現(xiàn)就是一個(gè)暗示：查詢可受益與不同的索引饰潜。

c.Using temporary：表示MySQL需要使用臨時(shí)表來存儲(chǔ)結(jié)果集初坠，常見于排序和分組查詢。這個(gè)值表示使用了內(nèi)部臨時(shí)(基于內(nèi)存的)表彭雾。一個(gè)查詢可能用到多個(gè)臨時(shí)表碟刺。有很多原因都會(huì)導(dǎo)致MySQL在執(zhí)行查詢期間創(chuàng)建臨時(shí)表。兩個(gè)常見的原因是在來自不同表的上使用了DISTINCT,或者使用了不同的ORDER BY和GROUP BY列薯酝“牍粒可以強(qiáng)制指定一個(gè)臨時(shí)表使用基于磁盤的MyISAM存儲(chǔ)引擎。這樣做的原因主要有兩個(gè)：1)內(nèi)部臨時(shí)表占用的空間超過min(tmp_table_size吴菠，max_heap_table_size)系統(tǒng)變量的限制者填；2)使用了TEXT/BLOB 列。

d. Using filesort：MySQL中無法利用索引完成的排序操作稱為“文件排序”

e. Using join buffer：改值強(qiáng)調(diào)了在獲取連接條件時(shí)沒有使用索引做葵，并且需要連接緩沖區(qū)來存儲(chǔ)中間結(jié)果占哟。如果出現(xiàn)了這個(gè)值，那應(yīng)該注意酿矢，根據(jù)查詢的具體情況可能需要添加索引來改進(jìn)能榨乎。

f. Impossible where：這個(gè)值強(qiáng)調(diào)了where語(yǔ)句會(huì)導(dǎo)致沒有符合條件的行。

h. Select tables optimized away：這個(gè)值意味著僅通過使用索引瘫筐，優(yōu)化器可能僅從聚合函數(shù)結(jié)果中返回一行.

I. Index merges：當(dāng)MySQL 決定要在一個(gè)給定的表上使用超過一個(gè)索引的時(shí)候谬哀，就會(huì)出現(xiàn)以下格式中的一個(gè)，詳細(xì)說明使用的索引以及合并的類型严肪。

Using sort_union(...)

Using union(...)

Using intersect(...)

小結(jié)

? EXPLAIN不考慮各種Cache

? EXPLAIN不能顯示MySQL在執(zhí)行查詢時(shí)所作的優(yōu)化工作

? 部分統(tǒng)計(jì)信息是估算的史煎，并非精確值

? EXPALIN只能解釋SELECT操作，其他操作要重寫為SELECT后查看執(zhí)行計(jì)劃驳糯。

? EXPLAIN不會(huì)告訴你關(guān)于觸發(fā)器篇梭、存儲(chǔ)過程的信息或用戶自定義函數(shù)對(duì)查詢的影響情況

Profiling的使用

要想優(yōu)化一條Query，就須要清楚這條Query的性能瓶頸到底在哪里酝枢，是消耗的CPU計(jì)算太多恬偷，還是需要的IO操作太多？要想能夠清楚地了解這些信息帘睦，可以通過Query Profiler功能得到袍患。

Query Profiler是MYSQL自帶的一種query診斷分析工具，通過它可以分析出一條SQL語(yǔ)句的性能瓶頸在什么地方竣付。通常我們是使用的explain,以及slow query log都無法做到精確分析诡延，但是Query Profiler卻可以定位出一條SQL語(yǔ)句執(zhí)行的各種資源消耗情況，比如CPU古胆，IO等肆良，以及該SQL執(zhí)行所耗費(fèi)的時(shí)間等筛璧。

用法

（1）通過執(zhí)行“set profiling”命令，可以開啟關(guān)閉QueryProfiler功能

mysql> SET global profiling=on;

（2）查看相關(guān)變量

show VARIABLES like '%profiling%';

（3）設(shè)置保存數(shù)量默認(rèn)15條惹恃，最大值為100

mysql> set profiling_history_size=100;

（4）在開啟Query Profiler功能之后夭谤，MySQL就會(huì)自動(dòng)記錄所有執(zhí)行的Query的profile信息，下面執(zhí)行n條Query作為測(cè)試

select * from person limit 10000,100;

（3）獲取當(dāng)前系統(tǒng)中保存的多個(gè)Query的profile的概要信息

mysql> show profiles;

image.png

（4）針對(duì)單個(gè)Query獲取詳細(xì)的profile 信息。

可以根據(jù)概要信息中的Query_ID來獲取某個(gè)Query在執(zhí)行過程中詳細(xì)的profile信息。例如查看cpu和io的詳細(xì)信息

show profile cpu,block io for query 501;

image.png

 show profile ALL for query 501;

ALL ：顯示所有信息

|BLOCK IO ：塊設(shè)備IO輸入輸出次數(shù)

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

|CPU：用戶和系統(tǒng)的CPU使用情況

|IPC：顯示發(fā)送和接收消息的相關(guān)消耗

|MEMORY：內(nèi)存消耗情況（該版本is not currently implemented）

|PAGE FAULTS：顯示主要和次要頁(yè)面故障相關(guān)的開銷

|SOURCE：顯示和Source_function,Source_file,Source_line相關(guān)的開銷信息

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

注意：profiling被應(yīng)用在每一個(gè)會(huì)話中毫玖，當(dāng)前會(huì)話關(guān)閉后消玄，profiling統(tǒng)計(jì)的信息將丟失。

last_query_cost

查上一個(gè)查詢的代價(jià)，而且它是io_cost和cpu_cost的開銷總和，它通常也是我們?cè)u(píng)價(jià)一個(gè)查詢的執(zhí)行效率的一個(gè)常用指標(biāo)。last_query_cost對(duì)于簡(jiǎn)單的查詢可以精確的得到計(jì)算榆鼠，但于包含子查詢或union的復(fù)雜查詢值是0。

show  status  like  'last_query_cost';

timestampdiff查看執(zhí)行時(shí)間

這種方法有一點(diǎn)要注意亥鸠，就是三條sql語(yǔ)句要盡量連一起執(zhí)行妆够，不然誤差太大，根本不準(zhǔn)负蚊。

set @d=now();

select id from person where lname='x8RJWmQX';

select timestampdiff(second,@d,now());

如果是用命令行來執(zhí)行的話神妹，有一點(diǎn)要注意，就是在select timestampdiff(second,@d,now());后面家妆，一定要多copy一個(gè)空行鸵荠，不然最后一個(gè)sql要你自己按回車執(zhí)行，這樣就不準(zhǔn)了伤极。

第三方工具查看執(zhí)行時(shí)間

第三方MySQL客戶端工具都自帶sql執(zhí)行時(shí)間顯示功能蛹找，如navicat、sqlyog等等哨坪。

數(shù)據(jù)庫(kù)連接進(jìn)程列表

show processlist;

image.png