MySQL常用性能分析方法-profile，explain，索引
1.查版本號
無論做什么都要確認版本號，不同的版本號下會有各種差異。
>Select ?version();
2.執(zhí)行狀態(tài)分析
顯示哪些線程正在運行
>show processlist;
下面是完整的信息
3.show profile
show profile默認的是關閉的，但是會話級別可以開啟這個功能，開啟它可以讓MySQL收集在執(zhí)行語句的時候所使用的資源。
顯示數(shù)據(jù)庫列表
>show databases;
切換數(shù)據(jù)庫
>use test;
>SELECT @@profiling;
輸出0說明profiles功能是關閉的
開啟profiles功能
> set profiling=1;
需要注意為了明確看到查詢性能，我們啟用profiling并關閉query cache：
>SET profiling = 1;
>SET query_cache_type = 0;
>SET GLOBAL query_cache_size = 0;
執(zhí)行2條SQL語句
> show profiles;
根據(jù)query_id 查看某個查詢的詳細時間耗費
> show profile for query 3;
ALL
顯示所有性能信息
>show profile all for query 3;
ALL
顯示所有性能信息
BLOCK IO
顯示塊IO（塊的輸入輸出）的次數(shù)
CONTEXT SWITCHES
顯示自動和被動的上下文切換數(shù)量
IPC
顯示發(fā)送和接收的消息數(shù)量。
MEMORY
MySQL5.6中還未實現(xiàn)，只是計劃實現(xiàn)。
SWAPS
顯示swap的次數(shù)。
show profile for cpu只顯示cpu信息
show profile for cpu,block io 顯示cpu和io信息
參考：http://dev.mysql.com/doc/refman/5.7/en/show-profile.html
http://wing324.github.io/2016/02/02/MySQL的SHOW-PROFILE詳解/?utm_source=tuicool&utm_medium=referral
4.分析執(zhí)行計劃和最左前綴原理
>explain + sql
關于分析結果需要注意索引有沒有用到。如果顯示type=ALL就是全表掃描了。
當用Explain查看SQL的執(zhí)行計劃時，里面有列顯示了 key_len 的值，根據(jù)這個值可以判斷索引的長度，在組合索引里面可以更清楚的了解到了哪部分字段使用到了索引。
如果只用到一個int那么就是4，比如上面的聯(lián)合索引只用到一個就是4，用到2個就是8以此類推。
聯(lián)合索引需要注意最左前綴原理，就是說匹配最左邊的字段，如果你的索引使用到abc三個字段，那么查a,ab,abc都可以用到索引，查ac和bc和b和c是不行的，必須從左到右逐漸增多。
最左前綴參考：http://blog.codinglabs.org/articles/theory-of-mysql-index.html
聯(lián)合索引優(yōu)化策略：
如何選擇索引列的順序
1.經(jīng)常會被使用到的列優(yōu)先
2.選擇性高的列優(yōu)先
3.寬度小的列優(yōu)先
如果不是int型數(shù)據(jù)，對于varchar，假如設定長度是255，由于你使用的是UTF-8字符集占3個字節(jié)，255*3+2=767，如上圖所示。
具體公式如下：
key_len的長度計算公式：
varchr(10)變長字段且允許NULL??? =? 10 * ( character set：utf8=3,gbk=2,latin1=1)+1(NULL)+2(變長字段)
varchr(10)變長字段且不允許NULL =? 10 *( character set：utf8=3,gbk=2,latin1=1)+2(變長字段)
char(10)固定字段且允許NULL????????=? 10 * ( character set：utf8=3,gbk=2,latin1=1)+1(NULL)
char(10)固定字段且不允許NULL????????=? 10 * ( character set：utf8=3,gbk=2,latin1=1)
關于key_len的計算參考：http://www.cnblogs.com/gomysql/p/4004244.html
explain小技巧，可以加 G以按行來顯示分析結果，避免因為按列顯示不下的情況：
5.索引選擇性與前綴索引
因為索引雖然加快了查詢速度，但索引也是有代價的，另外，MySQL在運行時也要消耗資源維護索引，因此索引并不是越多越好。
一般兩種情況下不建議建索引。
1.表記錄比較少，超過2000條可以酌情考慮索引。
2.索引的選擇性較低。所謂索引的選擇性（Selectivity），是指不重復的索引值（也叫基數(shù)，Cardinality）與表記錄數(shù)（#T）的比值：
Index Selectivity = Cardinality / #T
顯然選擇性的取值范圍為(0, 1]，選擇性越高的索引價值越大，這是由B+Tree的性質決定的。
參考：http://blog.codinglabs.org/articles/theory-of-mysql-index.html
其實就是算索引的不重復度
SELECT count(DISTINCT(ID))/count(*) AS Selectivity FROM xxxx;
SELECT count(DISTINCT(xx))/count(*) AS Selectivity FROM xxxx;
SELECT count(DISTINCT(concat(xx, xxx)))/count(*) AS Selectivity FROM xxxx;
SELECT count(DISTINCT(concat(xxx, left(xx, 2))))/count(*) AS Selectivity FROM xxxx;
很明顯因為主鍵id是不會重復的所以不重復度是100%也就是1.
6.覆蓋索引
覆蓋索引是指索引的葉子節(jié)點已包含所有要查詢的列，因此不需要訪問表數(shù)據(jù)所謂“回表”。
覆蓋索引的優(yōu)點
1.優(yōu)化緩存，減少磁盤IO
2.減少隨機IO，變隨機IO為順序IO
3.避免對Innodb主鍵索引的二次查詢
4.避免MyISAM表進行系統(tǒng)調用
下面是《高性能MySQL(第3版)》中關于explain的Extra列的信息：
下面舉例
第一條sql和第二條sql都是where帶相同的查詢條件，這個已經(jīng)建了索引，但是select里第一條是都覆蓋索引的，而第二條是*，自然有不覆蓋的，所以需要回表。
這樣性能就差很多了。
再開啟profile，很明顯回表的要慢0.012秒，也就是12毫秒。
7.文件排序和索引排序
使用索引掃描來優(yōu)化排序條件
1.索引的列順序和Order by子句的順序完全一致
2.索引中所有列的方向（升序，降序）和Order by子句完全一致
3.Order by中的字段全部在關聯(lián)表中的第一張表中
上面是一些不同的場景，其實就是如果排序可以利用索引就可以避免文件排序。
要想知道真實的時間還是需要執(zhí)行SQL，然后比較。
上圖中使用主鍵索引id排序的查詢是最快的，而使用二級索引，或者無索引的排序是非常耗時的需要10秒和17秒。使用了二級索引稍微好點，但是效果也不好。
下載文檔
