MySQL索引與最佳化

標籤：mysq   資料   順序   複合   索引   返回   參數   部分   基礎   

1. 效能下降，sql執行時間長原因：查詢語句沒寫好，索引失效，關聯太多join，伺服器參數設定不合理（JoinBuffer大小,SortBuffer大小,最大串連數）
2. 使用join時應該小表驅動大表，小資料集驅動大資料集
3. 索引：索引是協助MySQL高效擷取資料的一種資料結構，即索引的本質是資料結構。除了資料本身之外，MySQL資料庫還維護著一個滿足特定尋找演算法的資料結構，這些資料結構以某種方式指向資料，這樣就可以在這些資料結構的而基礎上實現高效的資料尋找演算法，這種資料結構就是索引。

4. 一把來說索引本身也很大，因此往往以索引檔案的形式儲存在磁碟上。我們通常使用的索引一般都是B樹(多路搜尋樹，不一定二叉樹)結構組織的索引。
5. 索引的優缺點：
    優點：使用索引查詢可以提高資料檢索效率，降低資料庫IO成本；使用索引排序

              可以降低資料排序的成本，降低了CPU的消耗。

    缺點：索引實際上也是一張表，該表儲存了主鍵與索引欄位，並指向實體表的記

              錄，所以索引也會佔用空間，雖然索引大大提高了查詢速度，但是會降低

              表的更新速度，例如insert，update，delete操作時除了更新表資料，還

              需要更新索引。

6. 索引分為單值索引，唯一索引，複合索引
7. mysql常見瓶頸：
    CPU:CPU飽和一般發生在資料裝入記憶體或者從磁碟上讀取資料的時候
    IO:磁碟IO瓶頸一般發生在裝入資料遠大於記憶體容量的時候
    伺服器(linux)硬體效能的瓶頸：top,free,iostat和vmstat來查看系統的效能狀態

8. 通過explain查看sql執行計畫，分析是否可以最佳化。執行計畫包含的資訊有：

(1). id:select 查詢的序號，包含一組數字，表示查詢中執行select子句或動作表

　　的順序:如果id相同，執行順序自上至下;如果id不同，如果是子查詢，id的

　　序號會遞增，id值越大優先順序越高，越先被執行
(2). select_type:查詢的類型，主要用於區別普通查詢，聯集查詢，子查詢等複雜

　　的查詢。有以下可能值:
　　simple：簡單查詢，不包含子查詢和union
　　primary：查詢中若包含任何複雜的子部分，最外層的查詢被標記為primary
　　subquery：select或者where中有子查詢
　　derived：from中有子查詢
　　union：union之後的查詢
　　union result：從union表擷取結果的select
(3). table 顯示這一行的資料來自哪張表
(4). type:顯示查詢使用了哪種訪問類型，從最好到最差依次為：

　　system>const>eq_ref>ref>range>index>All。
　　system : 表只有一行記錄，const類型特例，基本不會出現
　　const : 表示索引依次就找到了。const用於比較primary key或者unique索引。

　　　　　  因為只匹配一行資料，所以能很快找到，例如將主鍵置於where條件

                   中，mysql就能將查詢轉換為一個常量
　　eq_ref : 唯一性索引掃描，對於每個索引鍵，表中只有一條記錄與之匹配，

　　　　　　常見於主鍵或唯一性索引掃描
　　ref : 非唯一性索引掃描，返回匹配某個單獨值的所有行。
　　range ：檢索給定範圍。一般出現在where字句中有between,<,>,in等的查詢
　　index : full index scan，遍曆全部索引。雖然和下面出現的All都會掃描全表，

　　　　    但是因為索引檔案通常都比資料小，而且index是從索引檔案中讀取的，

　　　　    而All是從硬碟中讀取的
　　all : full table scan
(5).possible_keys:可能用在這張表中的索引,查詢的欄位上存在索引就會被列出來
(6).key:實際使用的索引。查詢中如果使用了覆蓋索引，則該索引和查詢的欄位重合
(7).key_len:索引使用的位元組數
(8).ref:顯示索引的哪一列被使用了，如果可能的話是一個常數。哪些列或常量被用於尋找索引列上的值
(9).rows:根據表統計資訊以及索引使用方式，大致算出找到所需的記錄所需要讀取的行數
(10).Extra:額外的重要訊息。有以下可能值：
　　using filesort:無法利用索引完成的排序，出現這種情況很影響效能
　　using temporary:使用了暫存資料表儲存中間結果，對查詢結果排序的時候使用了

　　　　　　　　　　暫存資料表，常見於order by和group by,十分影響效能,必須最佳化
　　using index:使用了覆蓋索引，避免訪問了資料行，效率不錯
　　using where：使用了where過濾
9. 索引最佳化：
 (1).全職匹配：
 (2).最佳左首碼原則：如果查詢了多列，查詢應該從索引的最左前列開始並且不要不要跳過索引中的列
 (3).不在索引上做任何操作(計算，函數，類型轉換(自動或手動))，否則會導致索引失效而轉向全表掃描
 (4).儲存引擎不能使用索引中範圍條件右邊的列
 (5).盡量使用覆蓋索引(只訪問索引的查詢(索引列和查詢列一致))，減少或避免select *
 (6).mysql在使用不等於(!=或者<>)，is null,is not null 的時候無法使用索引會導致全表掃描
 (7).like以萬用字元開頭(like "%abc")會導致索引失效，但是百分比符號結尾不會(like "abc%"),解決like "%"開頭的可以使用覆蓋索引解決
 (8).字串不加引號會導致索引失效
 (9).少用or,用它來串連會導致索引失效
 (10).使用group by需謹慎，因為分組之前必先排序，有可能會導致暫存資料表的產生
10. sql最佳化步驟：先找到查詢慢的sql(通過慢查詢日誌抓取超過一定時間的sql)->explain分析sql->最佳化索引與查詢語句->如果問題還沒解決，使用show profile查看sql執行的資源消耗情況，然後如果sql已經最優，判斷是否mysql伺服器問題，根據情況調整相關參數。

 

MySQL索引與最佳化