[一. 索引介紹]

[1.1 什么是Mysql索引]

• MySQL官方對于索引的定義：索引是幫助MySQL高效獲取數(shù)據(jù)的數(shù)據(jù)結(jié)構(gòu)。

• MySQL在存儲數(shù)據(jù)之外，數(shù)據(jù)庫系統(tǒng)中還維護(hù)著滿足特定查找算法的數(shù)據(jù)結(jié)構(gòu)，這些數(shù)據(jù)結(jié)構(gòu)以某種引用(指向)表中的數(shù)據(jù)，這樣我們就可以通過數(shù)據(jù)結(jié)構(gòu)上實現(xiàn)的高級查找算法來快速找到我們想要的數(shù)據(jù)。而這種數(shù)據(jù)結(jié)構(gòu)就是索引。

• 簡單理解為“排好序的可以快速查找數(shù)據(jù)的數(shù)據(jù)結(jié)構(gòu)”。

[1.2 索引數(shù)據(jù)結(jié)構(gòu)]

下圖是二叉樹的索引方式

• 二叉樹數(shù)據(jù)結(jié)構(gòu)的弊端：當(dāng)極端情況下，數(shù)據(jù)遞增插入時，會一直向右插入，形成鏈表，查詢效率會降低。

• MySQL中常用的的索引數(shù)據(jù)結(jié)構(gòu)有BTree索引(Myisam普通索引)，B+Tree索引（Innodb普通索引），Hash索引(memory存儲引擎)等等。

[1.3 索引優(yōu)勢]

• 提高數(shù)據(jù)檢索的效率，降低數(shù)據(jù)庫的IO成本。

• 通過索引對數(shù)據(jù)進(jìn)行排序，降低數(shù)據(jù)排序的成本，降低了CPU的消耗。

[1.4 索引劣勢]

• 索引實際上也是一張表，保存了主鍵和索引的字段，并且指向?qū)嶓w表的記錄，所以索引也是需要占用空間的。

• 在索引大大提高查詢速度的同時，卻會降低表的更新速度，在對表進(jìn)行數(shù)據(jù)增刪改的同時，MySQL不僅要更新數(shù)據(jù)，還需要保存一下索引文件。

• 每次更新添加了的索引列的字段，都會去調(diào)整因為更新帶來的減值變化后的索引的信息。

[1.5 索引使用場景]

哪些情況需要創(chuàng)建索引：

• 主鍵自動建立唯一索引

• 頻繁作為查詢條件的字段應(yīng)該創(chuàng)建索引(where 后面的語句)

• 查詢中與其它表關(guān)聯(lián)的字段，外鍵關(guān)系建立索引

• 多字段查詢下傾向創(chuàng)建組合索引

• 查詢中排序的字段，排序字段若通過索引去訪問將大大提高排序速度

• 查詢中統(tǒng)計或者分組字段

哪些情況不推薦建立索引:

• 表記錄太少

• 經(jīng)常增刪改的表

• Where條件里用不到的字段不建立索引

  
  

[三. 性能分析]

[3.1 MySQL常見瓶頸]

• SQL中對大量數(shù)據(jù)進(jìn)行比較、關(guān)聯(lián)、排序、分組時CPU的瓶頸。

• 實例內(nèi)存滿足不了緩存數(shù)據(jù)或排序等需要，導(dǎo)致產(chǎn)生大量的物理IO。查詢數(shù)據(jù)時掃描過多數(shù)據(jù)行，導(dǎo)致查詢效率低。

[3.2 Explain]

使用EXPLAIN關(guān)鍵字可以模擬優(yōu)化器執(zhí)行SQL查詢語句，從而知道MYSQL是如何處理SQL語句的?？梢杂脕矸治霾樵冋Z句或是表的結(jié)構(gòu)的性能瓶頸。其作用:

• 表的讀取順序

• 哪些索引可以使用

• 數(shù)據(jù)讀取操作的操作類型

• 那些索引被實際使用

• 表之間的引用

• 每張表有多少行被優(yōu)化器查詢

EXPLAIN關(guān)鍵字使用起來比較簡單： explain + SQL語句：

id字段介紹：

• select查詢的序列號，表示查詢中執(zhí)行select子句或操作表的順序。

• id相同時，執(zhí)行順序由上至下。

• id不同，如果是子查詢，id的序號會遞增，id值越大優(yōu)先級越高，則先被執(zhí)行。

• id相同和不同都存在時，id相同的可以理解為一組，從上往下順序執(zhí)行，所有組中，id值越大，優(yōu)先級越高越先執(zhí)行。

select_type字段介紹：

查詢的類型，常見值有：

• SIMPLE :簡單的 select 查詢,查詢中不包含子查詢或者UNION。

• PRIMARY:查詢中若包含任何復(fù)雜的子部分，最外層查詢則被標(biāo)記為Primary。

• DERIVED:在FROM列表中包含的子查詢被標(biāo)記為DERIVED(衍生),MySQL會遞歸執(zhí)行這些子查詢, 把結(jié)果放在臨時表里。(mysql5.7+過后)

• SUBQUERY: 在SELECT或WHERE列表中包含了子查詢。

table字段介紹：

顯示這一行的數(shù)據(jù)是關(guān)于哪張表的。

• type字段介紹：

• 訪問類型排序(從左往右索引效率越高)：

• System：表只有一行記錄（等于系統(tǒng)表），這是const類型的特列，平時不會出現(xiàn)，這個也可以忽略不計。

• Const：表示通過索引一次就找到了,const用于比較primary key或者unique索引。因為只匹配一行數(shù)據(jù)，所以很快，如將主鍵置于where列表中，MySQL就能將該查詢轉(zhuǎn)換為一個常量。

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

• ref：非唯一性索引掃描，返回匹配某個單獨值的所有行。本質(zhì)上也是一種索引訪問，它返回所有匹配某個單獨值的行，然而，它可能會找到多個符合條件的行，所以他應(yīng)該屬于查找和掃描的混合體。

• range：只檢索給定范圍的行,使用一個索引來選擇行。key 列顯示使用了哪個索引 一般就是在你的where語句中出現(xiàn)了between、<、>、in等的查詢這種范圍掃描索引掃描比全表掃描要好，因為它只需要開始于索引的某一點，而結(jié)束語另一點，不用掃描全部索引。

• Index：Full Index Scan，index與ALL區(qū)別為index類型只遍歷索引樹。這通常比ALL快，因為索引文件通常比數(shù)據(jù)文件小。也就是說雖然all和Index都是讀全表，但index是從索引中讀取的，而all是從硬盤中讀的。

• all：Full Table Scan，將遍歷全表以找到匹配的行。

從最好到最差依次是:system>const>eq_ref>ref>range>index>All 。一般來說，最好保證查詢能達(dá)到range級別，最好能達(dá)到ref。

possible_keys字段介紹：

顯示可能應(yīng)用在這張表中的索引，一個或多個。查詢涉及到的字段上如果存在索引，則該索引將會被列出來，但不一定會被查詢實際使用上。

key字段介紹：

查詢中實際使用的索引，如果為NULL，則沒有使用索引。

key_len字段介紹：

查詢中實際使用索引的性能，越大越好。

ref字段介紹：

顯示索引的哪一列被使用了。哪些列或常量被用于查找索引列上的值。

rows字段介紹：

rows列顯示MySQL認(rèn)為它執(zhí)行查詢時必須檢查的行數(shù)。一般越少越好。

extra字段介紹：

一些常見的重要的額外信息：

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

• Using temporary：Mysql在對查詢結(jié)果排序時使用臨時表，常見于排序order by和分組查詢group by。

• Using index：表示索引被用來執(zhí)行索引鍵值的查找，避免訪問了表的數(shù)據(jù)行，效率不錯。

• Using where：表示使用了where過濾。

盡量避免Using filesort!

[四. 查詢優(yōu)化]

[4.1 索引失效]

• 最佳左前綴法則：如果索引了多列，要遵循最左前綴法則，指的是查詢從索引的最左前列開始并且不跳過索引中的列。

• 不在索引列上做任何計算、函數(shù)操作，會導(dǎo)致索引失效而轉(zhuǎn)向全表掃描。

• 存儲引擎不能使用索引中范圍條件右邊的列。

• Mysql在使用不等于時無法使用索引會導(dǎo)致全表掃描。

• is null可以使用索引，但是is not null無法使用索引。

• like以通配符開頭會使索引失效導(dǎo)致全表掃描。

• 字符串不加單引號索引會失效。

• 使用or連接時索引失效。

[4.2 復(fù)合索引練習(xí)]

[4.4 關(guān)聯(lián)查詢優(yōu)化]

內(nèi)連接時，mysql會自動把小結(jié)果集的選為驅(qū)動表，所以大表的字段最好加上索引。左外連接時，左表會全表掃描，所以右邊大表字段最好加上索引，右外連接同理。我們最好保證被驅(qū)動表上的字段建立了索引。

[4.5 排序優(yōu)化]

• 盡量避免使用Using FileSort方式排序。

• order by語句使用索引最左前列或使用where子句與order by子句條件組合滿足索引最左前列。

• where子句中如果出現(xiàn)索引范圍查詢會導(dǎo)致order by索引失效。

[4.6 分組優(yōu)化]

[4.7 慢查詢?nèi)罩綸

介紹：MySQL的慢查詢?nèi)罩臼荕ySQL提供的一種日志記錄，他用來記錄在MySQL中響應(yīng)時間超過閥值的語句，具體指運行時間超過long_query_time值的SQL，則會被記錄到慢查詢?nèi)罩局?。可以由它來查看哪些SQL超出了我們最大忍耐時間值。

默認(rèn)情況下，MySQL數(shù)據(jù)庫沒有開啟慢查詢?nèi)罩?，需要手動設(shè)置參數(shù)：

• 查看是否開啟：show variables like '%slow_query_log%';

• 開啟日志：set global slow_query_log = 1;

• 設(shè)置時間: set global long_query_time = 1;

• 查看時間: SHOW VARIABLES LIKE 'long_query_time%';

查看超時的sql記錄日志：Mysql的數(shù)據(jù)文件夾下/data/...-slow.log;在Navicat中輸入show variables like '%slow_query_log%命令'，就可以得到文件目錄;

注意：非調(diào)優(yōu)場景下，一般不建議啟動改參數(shù)，慢查詢?nèi)罩局С謱⑷罩居涗泴懭胛募?，開啟慢查詢?nèi)罩緯蚨嗷蛏賻硪欢ǖ男阅苡绊憽?/span>