MySQL索引最佳化分析和SQL最佳化

標籤：

1 配置環境的說明            MySQL的版本資訊：                      系統版本資訊：          
2 索引的分析         2.1資料準備                  2.1.1資料庫建表SQL                     表的說明： id是自增主鍵，name是唯一索引，age 是非唯一索引，desc無索引                    

CREATE TABLE `index_test` (  `id` int(11) unsigned NOT NULL AUTO_INCREMENT COMMENT '自增ID',  `name` varchar(128) COLLATE utf8_bin NOT NULL DEFAULT '' COMMENT '名字',  `age` int(11) NOT NULL COMMENT '年齡',  `desc` varchar(128) CHARACTER SET utf8 NOT NULL DEFAULT '' COMMENT '描述',  `status` tinyint(4) NOT NULL COMMENT '狀態',  PRIMARY KEY (`id`),  UNIQUE KEY `uniq_name` (`name`),  KEY `idx_age` (`age`)) ENGINE=InnoDB DEFAULT CHARSET=utf8 ;

                2.1.2 表中測試資料                          2.2 索引分析                2.2.1 使用explain查看sql的執行計畫     

                  在MySQL中可以在sql前面加上explain語句，來顯示該條SQL的執行計畫，輸出內容如下：

                 

                2.2.2 explain詳解                            2.2.2.1 select_type     

                              select_type表示查詢語句的類型，取值主要有以下幾種：

                              simple：表示是簡單的單表查詢

                              primary：表示子查詢的外表

                              derived：派生表的查詢

                                  

                             subquery: 子查詢的內部第一個SQL

                                 

                             union：表示union操作被串連的表  

                            union result：表示串連操作之後的結果表                                                                

                                

                             depend union 表示子查詢中union語句

                             depend subquery 表示子查詢中產生的結果

 

                         2.2.2.2 table

                            當前SQL查詢涉及到的表的表名，注意 這裡有時候是中間結果表的表名，MySQL會按照自己的規則產生

                         2.2.2.3 type                              type的取值在很大的程度上反應了SQL的執行效能，                              按照效能由高到底，type的取值依次為：NULL，system，const，eq_reg，ref，range，index，ALL                              NULL 不用查表，速度最快                                                                system當表中只有一條資料的時候 type為system                               const常數查詢 一般是根據唯一鍵或者主鍵等值查詢                                                               eq_reg 表串連的時候 在b表查詢出來的結果在a表這中按照唯一索引值查詢一行

                               

                             ref非唯一索引查詢

                              

                            range 使用唯一索引返回掃描

                              

                          index 掃描整個索引檔案，例如覆蓋索引的查詢，效率只是比全表查詢略快，因為索引檔案一般比資料檔案小，所以一次讀入記憶體的索引資料更多，這樣磁碟IO                             就會更少

                           

                         All表示全表掃描，是效率最低的一種查詢

                         2.2.2.4 possible key                            表示可能使用的索引，顯示的順序與表串連的順序無關                         2.2.2.5 key                             表示MySQL執行本條sql選的索引的名字，可以通過force idex 和 ignore index 來強制改變sql執行所需要的索引                         2.2.2.6 key_len                            表示該條索引的佔用的自己樹，是根據索引欄位的類型計算出來的，                               例如  int(11)  索引長度是4                                        varchar(128)並且編碼是U8  索引長度的計算方法為 ： 128*3+2                          2.2.2.7 ref                            表示使用哪個列從表中選擇行，取值有科恩個是const                         2.2.2.8 rows                           表示執行該條SQL必須掃描的行數                         2.2.2.8 extra                           包含了MySQL產生執行計畫的詳細資料：

                           distinct 尋找唯一值，一旦找到就不在繼續尋找了（暫時沒有想好例子）

                           record 沒有找到理想的索引

                           use file sort 使用外排來排序  效率比較低

                           use index 使用覆蓋索引返回資料，沒有掃描表

                           use tempoary 使用暫存資料表來組合返回資料 效率較低

                           use where 使用where條件過濾返回的資料，在MySQL的儲存引擎層沒有過濾完資料，只能在MySQL服務層去過濾資料

        2.3 profiling詳解                2.3.1 開啟profiling                    因為profiling是比較消耗資源的，所以一般的MySQL預設都關閉了profiling功能，並且profiling只是針對當前session有效，目前不支援全域的profiling，可以通過如下的命令查看並開發profiling功能： 

SELECT @@profiling  返回的結果如果是0 表示當前的session的profiling功能是關閉的set profiling=1 開啟當前session的profiling功能

                2.3.2 profiling的使用                         2.3.2.1 查詢當前session的profiling的概要資訊                        可以使用 show profiles命令擷取當前session所執行的sql的概要資訊                                                        2.3.2.2 profiling詳解                            profiling的文法如下： 

SHOW PROFILE [type [, type] ... ]    [FOR QUERY n]    [LIMIT row_count [OFFSET offset]]type:    ALL  | BLOCK IO  | CONTEXT SWITCHES  | CPU  | IPC  | MEMORY  | PAGE FAULTS  | SOURCE  | SWAPS

                           使用樣本：                    

結果說明：   

                在使用profiling查看sql的詳細執行計畫的時候，主要關注的是前兩列即：status和duration

                 status 表示sql的執行狀態和 show full process list 查看到的狀態一致

                 duration 表示每個狀態執行的時間 可以看到sql的主要執行時間消耗在哪裡

                其次需要關注的是cup，io,swap的詳細資料

                cup表示 cpu的消耗時間

                swap表示機器的swap情況

                io表示io的消耗情況

        3 無效索引           在很多時候MySQL的表建立了索引，並且在查詢條件中也使用了索引進行篩選，但是並不一定會使用到索引，例如下面的幾種情況
         3.1篩選條件包含了隱式轉換                  下面的例子中，name欄位添加了唯一索引，但是name欄位的類型是varchar類型的，而篩選添加時int類型，發生了隱式轉換，所以走全表掃描。這裡比較隱晦。在上周有一個項目分析酒店訂單的時候，本來hive中的酒店訂單包含了酒店項目的所有訂單，訂單id是varchar類型的，而我們需要統計QTA中參加某一個活動的訂單，需要查詢QTA的訂單詳情庫，（QTA訂單詳情是hive中訂單的子集）裡面的訂單ID是long類型的，最開始查詢的時候就直接在一個表查詢完後再另外一個表查詢，結果看到一條簡單的sql執行起來巨慢。最後分析原因就定位到了這個上面。                 

         3.2 不支援函數式索引                age欄位上面添加了非唯一索引，但是使用了絕對值函數，所以age欄位上面的索引就無法使用了。這個在處理日期的時候經常遇到這樣的坑                        3.3 索引掃描的代價大於直接全表掃描                 如果只有索引過濾的資料比較少，那麼會直接走全表掃描，因為使用索引的時候會先掃描一遍索引，然後根據掃描到的索引回表找到所需要的資料，這樣掃描的效率其實更低，所以直接走全表掃描                               3.4 使用“%”首碼匹配的時候                name欄位添加了唯一索引 但是使用‘%’作為首碼匹配條件，所以不使用索引，直接走全表掃描                       3.5 複合索引非左首碼匹配                在使用複合索引的時候 如果不是使用的左首碼篩選條件 則不會使用索引，還是會全表掃描         3.5 or篩選添加前後都有索引的時候才會走索引

                在使用or作為篩選條件的時候，or的前後篩選條件都必須添加索引 這樣才能使用索引 否則 整條sql都無法使用索引

                
              

                           

MySQL索引最佳化分析和SQL最佳化