性能優(yōu)化：減少或者消除那些對獲得查詢結(jié)果來說不必要的工作

程序性能瓶頸可能有很多因素：
①、外部資源，比如調(diào)用了外部的WEB服務或者搜索引擎。
②、應用需要處理大量的數(shù)據(jù)，比如分析一個超大的XML文件。
③、在循環(huán)中執(zhí)行昂貴的操作，比如濫用正則表達式。
④、使用了低效率算法等。

對MySQL查詢進行性能剖析有兩種方式：

1.剖析整個數(shù)據(jù)庫服務器，這樣可以分析出哪些查詢是主要的壓力來源。
2.定位具體需要優(yōu)化的查詢后，可以對這些查詢進行單獨的剖析，分析哪些子任務是影響時間的主要消耗者。

慢查詢?nèi)罩?/h4>

#是否開啟慢查詢?nèi)罩荆?/on表示開啟，0/off表示關(guān)閉。
show VARIABLES like 'slow_query_log';
#未使用索引的查詢也被記錄到慢查詢?nèi)罩局?，on表示開啟，off表示關(guān)閉(默認值)。
show VARIABLES like 'log_queries_not_using_indexes';
#慢查詢閾值（秒級），當查詢時間大于設定的閾值時，記錄日志。
show VARIABLES like 'long_query_time';
#慢查詢?nèi)罩敬鎯β窂?show variables like 'slow_query_log_file';
set global slow_query_log = on;
set global log_queries_not_using_indexes = on;
set global long_query_time = 0;

pt-query-digest

第一部分：總體統(tǒng)計結(jié)果

• Exec time：執(zhí)行時間
• Lock time：鎖定時間
• Rows sent：發(fā)送行數(shù)
• Rows examine：掃描行數(shù)
• Query size：查詢字符數(shù)

第二部分：查詢分組統(tǒng)計結(jié)果

• Rank：所有語句的排名，默認按查詢時間降序排列，通過--order-by指定
• Query ID：語句的ID，（去掉空格和查詢條件中的文本值，計算hash值）
• Response：總的響應時間
• time：該查詢在本次分析中總的時間占比
• calls：執(zhí)行次數(shù)，即本次分析總共有多少條這種類型的查詢語句
• R/Call：平均每次執(zhí)行的響應時間
• V/M：方差均值比（Variance-to-mean），也就是常說的離差指數(shù)。
• Item：查詢對象

第三部分：每一種查詢的詳細統(tǒng)計結(jié)果

查詢各項數(shù)據(jù)的百分比、總數(shù)、最小、最大、平均、95%等各項目的統(tǒng)計，包括SQL執(zhí)行次數(shù)、執(zhí)行時間、鎖占用時間、發(fā)送行數(shù)、掃描行數(shù)、查詢字符數(shù)，表格中也統(tǒng)計了查詢涉及的數(shù)據(jù)庫、查詢時間直方圖等信息。

掃描的行數(shù)(Rows Examine)遠遠大于發(fā)送的行數(shù)(Rows sent) , 有問題， 需要優(yōu)化， 索引利用差
Query_time distribution：查詢時間分布圖——————直方圖

哪些SQL需要優(yōu)化：

1.查詢次數(shù)多，且每次查詢占用時間長的SQL：通常為pt-query-digest分析的前幾個查詢
2.IO大的SQL：注意pt-query-digest分析中的Rows examine
3.未使用索引的SQL：通過pt-query-digest分析中的Rows examine與Rows Send對比

剖析單條查詢

使用SHOW PROFILE

#開啟：
SET profiling = 1;
#查看開啟工具后的每條SQL執(zhí)行總體情況
SHOW PROFILES;
#根據(jù)query_id查看某個查詢的詳細時間耗費
SHOW PROFILE FOR QUERY 1;
#查看cpu、IO等信息
SHOW PROFILE BLOCK IO,CPU FOR QUERY 1;
#對每一個子任務的花費時間進行已統(tǒng)計排序
SELECT state, SUM(duration) AS Total_R, 
  ROUND(100 * SUM(duration) / (SELECT SUM(duration) FROM information_schema.profiling WHERE query_id = 1), 2) AS Pct_R, 
  COUNT(*) as Calls, SUM(duration) /COUNT(*) AS "R/Call" 
  FROM information_schema.profiling
WHERE query_id = 1 GROUP BY state ORDER BY total_r DESC;

• Creating sort index：當前的SELECT中需要用到臨時表在進行ORDER BY排序。建議：創(chuàng)建適當?shù)乃饕?/li>
• Sending data：發(fā)送數(shù)據(jù)
• table lock：表鎖。
• System lock：系統(tǒng)鎖。建議確認是由于哪個鎖引起的，通常是因為MySQL或InnoDB內(nèi)核級的鎖引起的
• Sorting result：結(jié)果的排序
• copying to tmp table：將數(shù)據(jù)復制到臨時表
• Creating tmp table：創(chuàng)建臨時表

執(zhí)行計劃：Explain

• table：對應的表
• type：連接類型（system、const、eq_ref、ref、range、index、all）
• possible_keys：可能使用的索引
• key：實際使用的索引
• key_len：使用索引長度
• rows：預計掃描行數(shù)
• Extra：解析查詢的額外信息（using index、using where、using temporary、using filesort）

連接類型（type）

#all  全表掃描
explain select * from address;
#index 全索引掃描
explain select city_id from address;
#range   < >    in()  between   根據(jù)索引范圍查找
explain select * from address where city_id>2;
#ref  根據(jù)索引 查詢匹配某個值的行
explain select * from address where city_id=200;
#eq_ref
explain select a.* from store a INNER JOIN address b using(address_id) where b.address='47 MySakila Drive';
#const
explain select * from address where address_id=1;

MySQL解析額外信息（Extra）

1、Using index：列數(shù)據(jù)僅僅使用了索引中的信息而沒有讀取實際的表
Select address_id from address where address_id=1
2、Using where：MySQL服務器將在存儲引擎檢索行后，通過Where子句條件進行過濾
Select * from address where city_id>12;
3、Using temporary：MYSQL需要創(chuàng)建一個臨時表來存儲結(jié)果，用于排序
Select DISTINCT district from address;
4、Using filesort：MySQL將對結(jié)果進行外部排序
Select * from address order by district;

MySQL執(zhí)行計劃的局限

• EXPLAIN不會告訴你關(guān)于觸發(fā)器、存儲過程的信息或用戶自定義函數(shù)對查詢的影響情況
• EXPLAIN不考慮各種Cache
• EXPLAIN不能顯示MySQL在執(zhí)行查詢時所作的優(yōu)化工作
  部分統(tǒng)計信息是估算的，并非精確值
• EXPALIN只能解釋SELECT操作，其他操作要重寫為SELECT后查看執(zhí)行計劃
• LooseScan：利用索引來掃描一個子查詢表，可以從每個子查詢的值群組中選出一個單一的值。