$1.WHY : 找到MySQL Query執行慢的原因

1.1 EXPLAIN

通過Explain查看SQL Query語句的執行情況，從中找出導致MySQL查詢性能差的原因EXPLAIN + QUERY語句

【字段解釋】

<1> id -- 表的讀取順序id相同時，按照從上至下的順序執行

id不同時，id值越大，則執行優先級越高，執行順序越靠前

<2> select_type -- 數據讀取操作的操作類型SIMPLE：簡單的select查詢，不包含子查詢或者UNION操作

PRIMARY：若查詢中包含復雜的子部分，如子查詢，則最外層的查詢則被標記為PRIMARY，最后執行

SUBQUERY：在SELECT或者WHERE語句中包含了子查詢

DERIVED：在FROM語句中包含的子查詢則會被標記為DERIVED(即衍生表)，其結果會被存放在臨時表中

UNION：若第二個SELECT語句出現在UNION后面，則被標記為UNION；若UNION出現在FROM語句中的子查詢中，則外層SELECT語句會被標記為DERIVED

UNION RESULT：從UNION表獲取結果的SELECT

<3> table -- 顯示當前執行計劃是針對哪張表

<4> type -- 訪問類型system：表中只有一行記錄，等價于系統表， 這是const類型的特例，實際生產中基本不會出現

const：只通過一次索引就能找到，只匹配一條記錄，const用于比較primary key或者unique索引。

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

ref：非唯一性索引掃描，返回匹配某個單獨值的所有行。本質上也是索引訪問，它可能找到多個符合條件的行，因此屬于查找和掃描的混合體

range：只檢索給定范圍的行，使用一個索引來選擇行，key鍵顯示使用了哪個索引。一般出現在where語句中包含between、、in等查詢，比全表掃描要好，因為他相當于全表索引的子集，不需要掃描所有

index：Full Index Scan，index類型遍歷全表索引樹，而ALL類型要遍歷全部表的數據，因此Index類型一般要比ALL更快，因為索引數據量一般小于實際表的數據量

ALL：掃描全表的行匹配到需要的記錄

一般來說，盡量能夠優化到ref或者range

<5> possible keys -- 顯示可能應用在這張表中的索引，但不一定被實際查詢使用

<6> key -- 實際使用的索引NULL：表示當前查詢沒有用到索引(可優化點)

查詢中如果使用了覆蓋索引，則該索引僅出現在key字段中

<7> key_len

表示索引中使用的字節數，可通過該列就按查詢中使用的索引長度，在不損失精度前提下，長度越短越好。注意：key_len顯示的值為索引字段最大可能長度，并非實際使用長度

<8> ref

顯示索引的哪一列被使用了，如果是等值判斷的話，該字段也可能是一個常數(const)，顯示哪些列或常量被用于查找索引列上的值

<9> rows

根據表統計信息即索引選用情況，大致估算出找到所需記錄所需要讀取的行數

<10> Extra -- 包含不在其他列中顯示卻又重要的額外信息Using filesort：MySQL會對數據進行一個外部的索引排序，而不是按照表中的索引進行排序。這種無法利用索引完成的排序操作成為"文件排序"，實際中應該盡量避免，出現了需要及時優化

Using temporary：使用了臨時表保存中間結果，MySQL在對查詢結果排序時使用了臨時表，常見于排序操作 order by 和分組操作 group by ---- 必須避免該情況！！會嚴重影響MySQL性能

Using index：表明select操作使用了覆蓋索引，避免了全表掃描 ；如果同時出現了Using where，表明索引被用來執行索引鍵值的查找；如果沒有同時出現Using where，表明索引用來讀取數據而非執行查找

Using where：見上

Using join buffer：使用了表的連接緩存

impossible where：where子句的值總是false，不能實際獲取記錄

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

distinct：優化distinct操作，在找到第一匹配的元組之后即立即停止找同樣值的動作

1.2 SLOW_QUERY_LOG(慢查詢日志)

慢查詢日志功能開啟后，MySQL會自動收集那些執行時間超過設置閾值的QUERY語句，優化人員便能夠通過查看日志系統地分析影響MySQL性能的因素。默認MySQL是關閉慢查詢日志功能的，因為開啟此功能會增加判斷和日志收集操作，或多或少會影響MySQL性能## 開啟慢查詢日志功能，只對當前服務有效，即MySQL服務器重啟后失效

set global slow_query_log=1

## 查看慢查詢日志的判斷閾值參數long_query_time，默認為10s

show variables like '%long_query_time%';

## 設置閾值為3s，需要重新連接客戶端才能生效

set global long_query_time=3;

## 查看當前系統中有多少條慢查詢日志記錄，可判斷系統性能狀態

show global status like '%Slow_queries%';

PLUS：慢查詢日志官方分析工具 -- mysqldumpslow

1.3 SHOW PROFILE

與慢查詢日志一樣，MySQL也是默認關閉SHOW PROFILE功能，需要設置參數手動打開## 開啟SHOW PROFILE功能

show variables like "profiling";

set profiling=on;## 查詢記錄的所有Query指令及其各環節的執行時間

show profiles;

##對某條查詢指令單獨進行深度分析，可以查詢Query指令的整個生命周期每個環節的運行時間和開銷，針對性地進行分析優化

show profile cpu, block io for query 10;

其中，可以查詢的item有：

WARNING: 四個主要比較拖慢性能的項，查詢結果中如果有任意一個，則需要盡可能優化converting HEAP to MyISAM：查詢結果太大，內存不夠，需要寫到磁盤上

Creating tmp table：創建了臨時表，即會將數據拷貝到臨時表，用完再刪除

Copying to tmp table on disk：把內存中的臨時表復制到磁盤上，非常危險

locked

1.4 GENERAL_LOG

該功能一般只在測試環境中啟用，會收集全局的查詢日志，即每一條查詢語句都會被記錄。實際開發生產環境中一般不要啟用set global general_log=1; ? ? ## 開啟全局日志功能

set global log_output='TABLE'; ? ## 設置日志輸出為表的格式

select * from mysql.general_log; ? ## 查詢日志記錄

$2.HOW:如何優化

2.1 表的Join

<1> 多表Join情況

兩表情況：驅動表一方全部保存，因此相當于在被驅動表中查詢數據左連接 LEFT JOIN -- 右表外鍵建索引

右連接 RIGHT JOIN -- 左表外鍵建索引

多表情況：優先用小表驅動大表保證Join語句中被驅動表的Join條件字段已經建立索引

當無法保證被驅動表join條件字段被索引情況下，如果內存資源充足，可以啟用更大的JoinBuffer

2.2 避免索引失效盡量保證全值匹配，即索引字段和select字段相同且順序一致

最佳左前綴法則：如果索引多列，則查詢要從索引的最左列開始，且中間不跳過索引中的列## 建立聯合索引 a_b_c

## 不走索引：WHERE b AND c 、 WHERE c

## 走部分索引：WHERE a AND c、WHERE a AND b

## 走全部索引：WHERE a AND b AND c不在索引列上做任何操作(計算、函數、類型轉換、特別注意！注意！注意！不要出現隱式轉換)，會導致索引失效而全表掃描## 假設目標行 name = 'july'

select * from info where name='july'; ## 走索引

select * from info where left(name,4)='july'; ? ## 不走索引一旦出現非等值字段條件判斷，則該字段后的索引列皆失效select * from info where a=10 and b=100 and c=1000; ? ## 全索引 a_b_c

select * from info where a=10 and b>100 and c=1000; ? ## 部分索引 a_b

## 非等值條件包括：in < > != like 等

## 注意：當like 'aaa%' 通配符在右時，仍然能夠走全索引

select * from info where a=10 and b like '100%' and c=1000; ? ## 全索引 a_b_c

select * from info where a=10 and b like '%100' and c=1000; ? ## 部分索引 a盡量使用覆蓋索引，即查詢列為索引列的子集，減少select * 的使用

MySQL在使用不等于(!=或者<>)時無法使用索引，會導致全表掃描select * from info where a=100; ? ## 走索引

select * from info where a!=100; ## 不走索引，全表掃描查詢條件為 is NULL 和 is not NULL情況時也無法使用索引select * from info where a is null; ## 不走索引

select * from info where a is not null; ## 不走索引like以通配符開頭('%abc...')時索引也會失效，變為全表掃描；但通配符結尾依然會走索引，但該字段后的索引依然失效select name, age from info where name like "%aaa"; ?## 索引失效

select name, age from info where name like "aaa%"; ?## 索引有效

## 當業務要求必須使用左通配符時，可使用覆蓋索引的方法來避免索引失效

## 在上面例子中即建立聯合索引 name_age字符串不加單引號會導致索引失效 -- 原因：隱式轉換## id為varchar類型

select * from info where id='2000';

select * from info where id=2000; ## 會有隱式類型轉換盡量少用or，用它來連接查詢條件可能會導致索引失效

group by基本上都需要進行排序，當group by的字段順序和索引順序不一致的時候，就會導致臨時表的產生，即同時出現 Using temporary 和 Using filesort，因此一定要極力避免## 索引為 A_B_C

select * from info where A=10 group by C, B; ## 走索引A，產生臨時表

2.3 索引優化小結對于單值索引，盡量選擇針對當前查詢過濾性更好的索引字段

在選擇聯合索引時，當前查詢中過濾性最好的字段在索引字段順序中位置越靠前越好

在選擇聯合索引時，盡可能選擇可以包含當前查詢的where子句中更多字段的索引，即如果可能的話，盡量達到索引覆蓋，這樣不僅能夠避免索引失效，也能夠避免回表等影響查詢性能等操作

盡可能通過分析統計信息和調整查詢語句的寫法來達到適應選擇的索引

REFERENCR