MySQL最佳化(二)：SQL最佳化

一、SQL最佳化

1、最佳化SQL一般步驟

1.1 查看SQL執行頻率

      SHOW STATUS LIKE 'Com_%';

      Com_select：執行SELECT操作的次數，一次查詢累加1。其他類似

       以下參數只針對InnoDB儲存引擎，累加演算法略有不同

       Innodb_rows_read：SELECT查詢操作插入的行數

       Innodb_rows_inserted/updated/deleted：執行INSERT/UPDATE/DELETE操作的行數

       通過以上參數，可以瞭解當前資料庫應用是查詢為主還是寫入資料為主。

       對於事務型的應用。通過Com_commit和Com_rollback可以瞭解事務提交和復原的情況，對於復原操作非常的頻繁的資料庫，可能意味著應用編寫存在問題。

       基本情況瞭解：

       Connections：試圖串連MySQL伺服器的次數。

       Uptime：伺服器工作時間 

       Slow_queries：慢查詢的次數

1.2 定位執行效率比較低的SQL語句

       - 通過慢查詢日誌定位慢SQL，用--log-slow-queries[=file_name]選項啟動時，mysqld會寫一個所有執行時間超過long_query_time秒的SQL語句的記錄檔。

       - 使用SHOW FULL PROCESSLIST; 查看當前MySQL在進行的線程，同時對一些鎖表操作進行最佳化。      

1.3 通過EXPLAIN分析慢SQL

      文法：EXPLAIN SQL語句

      結果：

      

      - select_type：表示SELECT的類型，常見的取值有SIMPLE(簡單表，即不使用表串連或者子查詢)、PRIMARY(主查詢，即外層的查詢)、UNION(UNION中的第二個或者後面的查詢語句)、SUBQUERY(子查詢中的第一個SELECT)等。

      - table：輸出結果的表名

      - type：表示MySQL在表中找到所需行的方式，或者叫訪問類型

        常見的有：ALL index range ref eq_ref const,system NULL，從左至右，效能由最差到最好。

        type=ALL：全表掃描。

        type=index：索引全掃描，MySQL遍曆整個索引來查詢。

        type=range：索引範圍掃描，常見於<、<=、>、 >=、 between。

        type=ref：使用非唯一索引掃描或唯一索引的首碼掃描，返回匹配某個單獨值的記錄。

        type=eq_ref：類似ref，區別就在使用的索引是唯一索引，對於每個索引索引值，表中只有一條記錄匹配，簡單來說，就是多表串連中使用primary key或者unique index作為關聯條件。

        type=const/system：單表中最多有一個匹配行，查詢起來非常迅速，一般主鍵primary key或者唯一索引unique index進行的查詢，通過唯一索引uk_email訪問的時候，類型type為const；而從我們構造的僅有一條記錄的a表中檢索時，類型type為system。

        type=NULL：MySQL不用訪問表或者索引，就能直接得到結果。

        類型type還有其他值，如ref_or_null(與ref類似，區別在於條件中包含對NULL的查詢)、index_merge(索引合并最佳化)、unique_subquery(in的後面是一個查詢主鍵欄位的子查詢)、index_subquery(與unique_subquery類似，區別在於in的後面是查詢非唯一索引欄位的子查詢)

       - possible_keys：表示查詢時可能使用的索引。

       - key：表示實際使用的索引。

       - key_len：使用到索引欄位的長度。

       - rows：掃描行的數量

       - Extra：執行情況的說明和描述，包含不適合在其他列中顯示但是對執行計畫非常重要的額外資訊。

         Using where：表示最佳化器除了利用索引來加速訪問之外，還需要根據索引回表查詢資料。

1.4 通過show profile分析SQL

      查看當前MySQL是否支援profile

      

      預設profiling是關閉的，可以通過set語句在Session層級開啟profiling：set profiling=1;

      使用方法：

      - 執行統計查詢：

      

      - 尋找上述SQL的query ID：

      

 

      - 尋找上述SQL執行過程中每個線程的狀態和消耗時間：

      

      Sending data狀態表示MySQL線程開始訪問資料並行把結果返回給用戶端，而不僅僅是返回結果給用戶端。由於在Sending data狀態下，MySQL線程往往需要做大量的磁碟讀取操作，所以經常是整個查詢中耗時最長的狀態。

       - 查看詳細資料並排序：

SELECT    STATE,    SUM(DURATION) AS TR,    ROUND(        100 * SUM(DURATION) / (            SELECT                SUM(DURATION)            FROM                information_schema.PROFILING            WHERE                QUERY_ID = 3        ),        2    ) AS PR,    COUNT(*) AS Calls,    SUM(DURATION) / COUNT(*) AS "R/Call"FROM    information_schema.PROFILINGWHERE    QUERY_ID = 3GROUP BY    STATEORDER BY    TR DESC;

      

      進一步擷取all、cpu、block io、context switch、page faults等明細類型來查看MySQL在使用什麼資源上耗費了過高的時間，例如，選擇查看CPU的耗費時間。

      此時可擷取到sending data時間主要消耗在CPU上

      

      提示：InnoDB引擎count(*)沒有MyISAM執行速度快，就是因為InnoDB引擎經曆了Sending data狀態，存在訪問資料的過程，而MyISAM引擎的表在executing之後直接就結束查詢，完全不需要訪問資料。

2、索引問題

      索引是資料庫最佳化中最常用也是最重要的手段之一，通過索引通常可以協助使用者解決大多數的SQL效能問題。

2.1 儲存引擎的分類

      - B-Tree索引：最常見的索引類型，大部分引擎都支援B樹索引。

      - HASH索引：只有Memory引擎支援。

      - R-Tree索引：空間索引是MyISAM的一個特殊索引類型，主要用於地理空間資料類型。

      - Full-text：全文索引是MyISAM的一個特殊索引類型，主要用於全文索引，InnoDB從MySQL5.6版本開始對其支援。

      MySQL目前不支援函數索引，但是能對列的前面某一部分進行索引，例如標題title欄位，可以只取title的前10個字元進行索引，但是在排序Order By和分組Group By操作的時候無法使用。首碼索引建立例子：create index idx_title on film(title(10))。

      常用的索引是B-Tree和Hash。Hash只有Memory/Heap引擎支援。適用於Key-Value查詢，通過Hash比B-Tree更迅速。Hash索引不使用範圍查詢。Memory/Heap引擎只有在=條件下才會使用索引。

2.2 MySQL如何使用索引

      建立一個複合索引：ALTER TABLE rental ADD INDEX idx_rental_date (rental_date, inventory_id, customer_id);

2.2.1 MySQL中能夠使用索引的典型情境

      - 匹配全值，對索引中所有列都指定具體值，即是對索引中的所有列都有等值匹配條件。

        比如上述建立的idx_rental_date，包含rental_date, inventory_id, customer_id，此時如果where子句中包含三者，即為全值匹配。

        

        欄位key為idx_rental_date，表示最佳化器使用的是索引idx_rental_date進行掃描。

      - 匹配值的範圍查詢，對索引的值能夠進行範圍尋找。

        

        類型type為range說明最佳化器選擇範圍查詢，索引key為idx_fk_customer_id說明最佳化器選擇索引idx_fk_customer_id來加速訪問。

      - 匹配最左首碼，意思是在複合索引中，索引是從左邊第一個開始尋找，不會跨過第一個從第二個尋找，比如一個聯合索引包含(c1, c2, c3)三個欄位，可是不能被c2或者c2+c3等值查詢利用到。

        添加索引：ALTER TABLE payment ADD INDEX idx_payment_date(payment_date, amount, last_update);此時第一個欄位是payment_date

        如果查詢條件包含索引的第一列支付日期，能夠使用複合索引idx_payment_date進行過濾。

        比如：

        

        如果使用的是第二個支付金額不包含第一個，則不會使用索引。

        比如：(此時key為空白)

        

      - 僅僅對索引進行查詢，意思是查詢的資料都在索引欄位中時，查詢的效率更高。

        比如此時查詢last_update且last_update欄位被包含在索引欄位中

        

        那麼直接存取索引就可以擷取所需的資料，不需要通過索引回表，此時的Extra也變成了Using index，Using index指的是覆蓋索引掃描。

        查詢結果：

        

      - 匹配列首碼，僅僅使用索引中的第一列，並且只包含索引第一列的開頭一部分進行尋找。

        例如尋找標題title是以AFRICAN開頭的電影資訊。

        首先建立索引：CREATE INDEX idx_title_desc_part ON film_text (title(10), description(20));

        查詢可以看到idx_title_desc_part 被使用，Using where表示最佳化器需要通過索引回表查詢資料：

        

      - 匹配部分精確，其他部分範圍匹配。

        指定日期，不同客戶編碼

        

        類型type為range說明最佳化器選擇範圍查詢，索引key為idx_rental_date說明最佳化器選擇索引idx_rental_date協助加速查詢，同時所查詢的欄位在索引中，索引Extra能看見Using index。

      - 列名 is null，此種情況下會使用索引。

        例如：

        

 2.2.2 存在索引但不能使用的典型情境

      - 以%開頭的LIKE查詢不能利用B-Tree索引。

        如下：

        

        B-Tree索引結構，以%開頭的查詢無法利用索引，一般可使用全文索引(Fulltext)來解決類似問題。或者使用InnoDB表上的二級索引，首先擷取滿足條件的列表的id，之後再根據主鍵回表去檢索記錄。

        

      - 資料類型出現隱式轉換不會使用索引，有些列類型是字串，在寫where條件時，需要將常量值用引號括起來。

      - 複合索引，查詢條件需要包括最左邊部分，否則不會使用複合索引。即leftmost

      - MySQL執行語句時會有最佳化器選擇的過程，當全表掃描的代價小於索引的代價時，會使用全表掃描，所以此時需要更換一個篩選性更高的條件。

      - 用or分開的條件，如果or前的列有索引，後面的沒有索引，則不會使用索引。

2.3 查看索引的使用方式

      

      如果索引正在工作，Handler_read_key的值將很高，這個值代表了一個行被索引值讀的次數，如果很低，說明增加索引得到的效能改善不高，因為索引並沒有被經常使用。

      Handler_read_rnd_next的值高則意味著查詢運行低效，並且應該建立索引補救。這個值的含義是在資料檔案中讀下一行的請求數。如果值比較大，說明進行中大量的表掃描，則通常說明表索引不正確或寫入的查詢沒有利用索引。

 

3、常用SQL最佳化

3.1 大批量插入資料(load)

      - MyISAM

        - 開啟或者關閉MyISAM表非唯一索引的更新，可以提高匯入效率(匯入資料到非空MyISAM表)。

          步驟：ALTER TABLE tab_name DISABLE KEYS; 匯入資料; ALTER TABLE tab_name ENABLE KEYS;

          匯入資料到一個空的MyISAM表，預設是先匯入資料然後才建立索引的，所以不用設定。

      - InnoDB

        - 因為InnoDB類型的表是按照主鍵順序儲存的，所以降匯入的資料按主鍵的順序排列，可以有效提高匯入資料的效率。

        - 關閉唯一性校正，SET UNIQUE_CHECKS = 0,匯入結束後開啟。

        - 如果使用的是自動認可的方式，在匯入前使用SET AUTOCOMMIT = 0，匯入結束後在恢複。 

3.2 最佳化INSERT語句

        - 同一用戶端插入很多行，應盡量使用多個值的INSERT語句。比如：INSERT INTO tab_name values(),(),()...

        - 不同用戶端插入很多行，可以使用INSERT DELAYED，DELAYED含義是讓INSERT語句放置到記憶體的隊列中，並沒有寫入磁碟。LOW_PRIORITY是在所有其他使用者對錶的讀寫完成後才進行插入。

        - 將索引檔案和資料檔案分別放置在不同的磁碟上。

        - MyISAM如果進行批量插入，增加bulk_insert_buffer_size的值。

        - 從檔案裝載一個表時，使用LOAD DATA INFILE，比INSERT語句快20倍。

3.3 最佳化ORDER BY 語句

3.3.1 MySQL排序方式

      - 通過有序索引順序掃描直接返回有序資料。

        在表customer上有索引idx_fk_store_id，指向欄位store_id

        

        此時order by使用store_id排序時，Extra為Using index，不需要額外的排序，操作效率較高。

        

      - 通過Filesort排序，所有不是通過索引直接返回排序結果的排序都叫Filesort排序。MySQL伺服器對排序參數的設定和需要排序資料的大小決定排序操作是否使用磁碟檔案或暫存資料表。

        Filesort是通過演算法，將取得的資料在sort_buffer_size系統變數設定的記憶體排序區中進行排序，如果記憶體裝不下，就會將磁碟上的資料進行分塊，再對各個資料區塊進行排序，然後合并。sort_buffer_size的排序區為線程獨佔，可能同時存在多個。

        比如通過store_id排序所有客戶記錄時，此時為全表掃描，並且使用filesort。

        

        一般最佳化方式：減少額外的排序，通過索引直接返回有序資料。盡量使WHERE條件和ORDER BY使用相同的索引，並且ORDER BY的順序和索引資料相同，並且ORDER BY的欄位都是升序或者都是降序，否則肯定會出現Filesort。

        - 不會使用索引情況：

          - order by的欄位混合ASC和DESC：SELECT * FROM TAB_NAME ORDER BY KEY_PART1 DESC, KEY_PART2 ASC;

          - 用於查詢的關鍵字與ORDER BY 中所使用的不相同：SELECT * FROM TAB_NAME WHERE KEY2=CONSTANT ORDER BY KEY1;

          - 對不同的關鍵字使用ORDER BY：SELECT * FROM TAB_NAME ORDER BY KEY1, KEY2;

3.3.2 最佳化Filesort

      Filesort有兩種排序演算法：

      - 兩次掃描演算法：首先根據條件取出排序欄位和行指標資訊，之後在排序區sort buffer中排序。如果排序區sort buffer不夠，則在暫存資料表Temporary Table中儲存排序結果，完成排序後根據行指標回表讀取記錄。需要兩次訪問資料，第一次擷取排序欄位和行指標資訊，第二次根據行指標擷取記錄，第二次讀取操作可能導致大量隨機I/O操作，優點是排序的時候記憶體開銷較少。

      - 一次掃描演算法：一次性取出滿足條件的行的所有欄位，然後在排序區sort buffer中排序後直接輸出結果集，排序的時候記憶體開銷比較大，但是排序效率比兩次掃描要高。

      MySQL通過比較系統變數max_length_for_sort_data的大小和Query語句取出的欄位總大小來判斷使用哪種演算法。max_length_for_sort_data大使用第二種演算法，否則第一種。

      適當加大系統變數max_length_for_sort_data的值，能夠讓MySQL選擇更最佳化的Filesort排序演算法。但是過大會引起CPU利用率過低和磁碟I/O過高。

      適當加大sort_buffer_size排序區，盡量讓排序在記憶體中完成，而不是通過建立暫存資料表放在檔案中進行；該大小需要考慮資料庫活動串連數和伺服器記憶體的大小來適當設定排序區。因為這個參數是每個線程獨佔的，如果設定過大，會導致伺服器SWAP嚴重。盡量只使用必要的欄位，而不是SELECT *。

 3.3.3 最佳化GROUP BY

      預設情況下，MySQL對所有的GROUP BY欄位進行排序，如果查詢包括GROUP BY但是使用者想要避免排序結果的消耗，則可以指定ORDER BY NULL禁止排序。

      SELECT XXX FROM XXX GROUP BY XXX ORDER BY NULL

3.3.4 最佳化巢狀查詢

      使用子查詢可以一次性的完成很多邏輯上需要多個步驟才能完成的SQL操作，同時也可以避免事務或者表鎖死。子查詢可以被更有效串連JOIN替代。

3.3.5 最佳化OR條件

      對於含有OR的查詢子句，如果要利用索引，則OR之間的每個條件列都必須要用到索引。

3.3.6 最佳化分頁查詢

       一般分頁查詢時，通過建立覆蓋索引能夠比較好地提高效能，但是當分頁為1000 20時，此時會排序前1020條記錄後返回1001到1020條記錄，前1000條記錄都會被拋棄，查詢和排序的代價非常高。

       - 第一種最佳化思路：從索引完成排序分頁的操作，最後根據主鍵關聯回原表查詢所需的其他列內容。

         例如：對電影表film根據標題title排序後取某一頁資料

         - 直接查詢

           

           按照索引分頁後回表方式改寫SQL

           

      - 第二種最佳化思路：把limit查詢轉換成某個位置的查詢。

        假設需要查詢第100頁，則可以記錄99頁最後一行的id(倒序或者正序)，然後重新查詢時使用where取99也最後一行的id進行大於或者小於，然後在直接使用limit n即可。n為每頁顯示的行數。

        比如以每頁十行查詢第100頁的資料，可以使用一下步驟：

        首先查詢到第99行最後一行的id：

        

        在通過擷取到的id取小於它的值，取10行，即為第100頁：

        

        與直接查詢結果相比較：

        

        explain比較：

        

       

3.3.7 使用SQL提示

        - USE INDEX

          提示MySQL參考使用的索引，可以讓MySQL不再考慮其他可用的索引。

          比如：select count(1) from tab_name use index(index_name) where xxx; 此時查詢會用index_name所以，而忽略其他。

        - IGNORE INDEX

          提示MySQL忽略一個或者索引。

          比如：select count(1) from tab_name ignore index (index_name); 此時查詢會忽略index_name索引。

        - FORCE INDEX

           強制MySQL使用某個索引，使用方式：當where子句取id>1的值，因為資料庫中大部分庫表都是大於1的，所以會全盤掃描，此時使用use index不可用，所以使用force index。

           比如：select * from tab_name force index(index_name) where id > 1;

4、常用SQL技巧

4.1 Regex的使用

     

      -  ^ 在字串的開始處進行匹配

         匹配是否已a開頭

         

      -  $ 在字串的末尾處進行匹配。

      -  . 匹配任意單個字元，包括分行符號。

         

      -  [...] 匹配出括弧內的任一字元。

         

      -  [^...] 不匹配[]內的任一字元

      真執行個體子：

      

      使用like格式如下：SELECT first_name, email FROM customer WHERE email LIKE "%@163.com" OR email LIKE "%@163,com";

 4.2 利用RAND()提取隨機行

      隨機抽取n條資料：SELECT * FROM tab_name ORDER BY RAND() LIMIT n;

4.3 GROUP BY的WITH ROLLUP

      WITH ROLLUP可以檢索出更多的分組彙總資訊。

      比如查詢經手員工每日的支付金額的統計。不使用WITH ROLLUP如下：

      

        加入 WITH ROLLUP如下：

        

        WITH ROLLUP反映的是一種OLAP思想，可以滿足使用者想要得到的任何一個分組以及分組組合的彙總資訊值。上個例子中，WITH ROLLUP幫使用者統計了每日的總金額和所有的總金額。注意：ROLLUP不能和ORDER BY使用，且limit在ROLLUP後面。

4.4 資料庫名、表名大小寫問題

      由於Windows、Mac OS、Unix對庫表名已經查詢使用的大小寫敏感不一致，所以最好將庫表進行規範儲存，且查詢語句也規範使用。