1.查版本號(hào)

無(wú)論做什么都要確認(rèn)版本號(hào)，不同的版本號(hào)下會(huì)有各種差異。

>Select ?version();

2.執(zhí)行狀態(tài)分析

顯示哪些線程正在運(yùn)行

>show processlist;

下面是完整的信息

3.show profile

show profile默認(rèn)的是關(guān)閉的，但是會(huì)話級(jí)別可以開(kāi)啟這個(gè)功能，開(kāi)啟它可以讓MySQL收集在執(zhí)行語(yǔ)句的時(shí)候所使用的資源。

顯示數(shù)據(jù)庫(kù)列表

>show databases;

切換數(shù)據(jù)庫(kù)

>use test;

>SELECT @@profiling;

輸出0說(shuō)明profiles功能是關(guān)閉的

開(kāi)啟profiles功能

> set profiling=1;

需要注意為了明確看到查詢(xún)性能，我們啟用profiling并關(guān)閉query cache：

>SET profiling = 1;
>SET query_cache_type = 0;
>SET GLOBAL query_cache_size = 0;

執(zhí)行2條SQL語(yǔ)句

> show profiles;

根據(jù)query_id 查看某個(gè)查詢(xún)的詳細(xì)時(shí)間耗費(fèi)
> show profile for query 3;

ALL
顯示所有性能信息

>show profile all for query 3;

ALL
顯示所有性能信息
BLOCK IO
顯示塊IO（塊的輸入輸出）的次數(shù)
CONTEXT SWITCHES
顯示自動(dòng)和被動(dòng)的上下文切換數(shù)量
IPC
顯示發(fā)送和接收的消息數(shù)量。
MEMORY
MySQL5.6中還未實(shí)現(xiàn)，只是計(jì)劃實(shí)現(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í)行計(jì)劃和最左前綴原理

>explain + sql

關(guān)于分析結(jié)果需要注意索引有沒(méi)有用到。如果顯示type=ALL就是全表掃描了。

當(dāng)用Explain查看SQL的執(zhí)行計(jì)劃時(shí)，里面有列顯示了 key_len 的值，根據(jù)這個(gè)值可以判斷索引的長(zhǎng)度，在組合索引里面可以更清楚的了解到了哪部分字段使用到了索引。

如果只用到一個(gè)int那么就是4，比如上面的聯(lián)合索引只用到一個(gè)就是4，用到2個(gè)就是8以此類(lèi)推。

聯(lián)合索引需要注意最左前綴原理，就是說(shuō)匹配最左邊的字段，如果你的索引使用到abc三個(gè)字段，那么查a,ab,abc都可以用到索引，查ac和bc和b和c是不行的，必須從左到右逐漸增多。

最左前綴參考：http://blog.codinglabs.org/articles/theory-of-mysql-index.html

聯(lián)合索引優(yōu)化策略：
如何選擇索引列的順序
1.經(jīng)常會(huì)被使用到的列優(yōu)先
2.選擇性高的列優(yōu)先
3.寬度小的列優(yōu)先

如果不是int型數(shù)據(jù)，對(duì)于varchar，假如設(shè)定長(zhǎng)度是255，由于你使用的是UTF-8字符集占3個(gè)字節(jié)，255*3+2=767，如上圖所示。

具體公式如下：

key_len的長(zhǎng)度計(jì)算公式：
varchr(10)變長(zhǎng)字段且允許NULL??? =? 10 * ( character set：utf8=3,gbk=2,latin1=1)+1(NULL)+2(變長(zhǎng)字段)
varchr(10)變長(zhǎng)字段且不允許NULL =? 10 *( character set：utf8=3,gbk=2,latin1=1)+2(變長(zhǎng)字段)

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)

關(guān)于key_len的計(jì)算參考：http://www.cnblogs.com/gomysql/p/4004244.html

explain小技巧，可以加 G以按行來(lái)顯示分析結(jié)果，避免因?yàn)榘戳酗@示不下的情況：

5.索引選擇性與前綴索引

因?yàn)樗饕m然加快了查詢(xún)速度，但索引也是有代價(jià)的，另外，MySQL在運(yùn)行時(shí)也要消耗資源維護(hù)索引，因此索引并不是越多越好。

一般兩種情況下不建議建索引。
1.表記錄比較少，超過(guò)2000條可以酌情考慮索引。
2.索引的選擇性較低。所謂索引的選擇性（Selectivity），是指不重復(fù)的索引值（也叫基數(shù)，Cardinality）與表記錄數(shù)（#T）的比值：
Index Selectivity = Cardinality / #T

顯然選擇性的取值范圍為(0, 1]，選擇性越高的索引價(jià)值越大，這是由B+Tree的性質(zhì)決定的。

參考：http://blog.codinglabs.org/articles/theory-of-mysql-index.html

其實(shí)就是算索引的不重復(fù)度

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;

很明顯因?yàn)橹麈Iid是不會(huì)重復(fù)的所以不重復(fù)度是100%也就是1.

6.覆蓋索引

覆蓋索引是指索引的葉子節(jié)點(diǎn)已包含所有要查詢(xún)的列，因此不需要訪問(wèn)表數(shù)據(jù)所謂“回表”。

覆蓋索引的優(yōu)點(diǎn)
1.優(yōu)化緩存，減少磁盤(pán)IO
2.減少隨機(jī)IO，變隨機(jī)IO為順序IO
3.避免對(duì)Innodb主鍵索引的二次查詢(xún)
4.避免MyISAM表進(jìn)行系統(tǒng)調(diào)用

下面是《高性能MySQL(第3版)》中關(guān)于explain的Extra列的信息：

下面舉例

第一條sql和第二條sql都是where帶相同的查詢(xún)條件，這個(gè)已經(jīng)建了索引，但是select里第一條是都覆蓋索引的，而第二條是*，自然有不覆蓋的，所以需要回表。

這樣性能就差很多了。

再開(kāi)啟profile，很明顯回表的要慢0.012秒，也就是12毫秒。

7.文件排序和索引排序

使用索引掃描來(lái)優(yōu)化排序條件
1.索引的列順序和Order by子句的順序完全一致
2.索引中所有列的方向（升序，降序）和Order by子句完全一致
3.Order by中的字段全部在關(guān)聯(lián)表中的第一張表中

上面是一些不同的場(chǎng)景，其實(shí)就是如果排序可以利用索引就可以避免文件排序。

要想知道真實(shí)的時(shí)間還是需要執(zhí)行SQL，然后比較。

上圖中使用主鍵索引id排序的查詢(xún)是最快的，而使用二級(jí)索引，或者無(wú)索引的排序是非常耗時(shí)的需要10秒和17秒。使用了二級(jí)索引稍微好點(diǎn)，但是效果也不好。