MySQL 慢查詢優化

zhoutianyuan發表於2019-06-23
MySql優化

為什麼查詢速度會慢

　　1.慢是指一個查詢的響應時間長。一個查詢的過程：

  • 客戶端傳送一條查詢給伺服器
  • 伺服器端先檢查查詢快取，如果命中了快取，則立可返回儲存在快取中的結果。否則進入下一個階段
  • 伺服器端進行SQL解析、預處理，再由優化器生成對應的執行計劃。
  • MySQL根據優化器生成的執行計劃，呼叫儲存引擎的API來執行查詢。
  • 將結果返回給客戶端

　　2.資料訪問

  • 是否向資料庫請求了不需要的資料
  • 是否掃描額外的記錄

　　3.查詢的方式

  • 一個複雜的查詢還是多個簡單的查詢
  • 切分查詢（將大查詢切分成小查詢，迴圈完成小查詢）
  • 分解關聯查詢

慢查詢分析

　　問題SQL

　　　　把複雜的SQL分成多個簡單SQL並執行，檢視具體那個欄位會慢，區分度不高。

　　EXPLAIN

　　　　顯示SQL如何使用索引的執行計劃。

　　　　執行計劃的引數：

table 顯示這一行的資料是關於哪張表的

type 顯示連線使用了何種型別。從最好到最差的連線型別為const、eq_reg、ref、range、indexhe和ALL

possible_keys 顯示可能應用在這張表中的索引。如果為空，沒有可能的索引。可以為相關的域從WHERE語句中選擇一個合適的語句 

key 實際使用的索引。如果為NULL，則沒有使用索引。很少的情況下，MYSQL會選擇優化不足的索引。這種情況下，可以在SELECT語句中使用USE INDEX（indexname）來強制使用一個索引或者用IGNORE INDEX（indexname）來強制MYSQL忽略索引 

key_len  使用的索引的長度。在不損失精確性的情況下，長度越短越好 

ref 顯示索引的哪一列被使用了，如果可能的話，是一個常數

rows 掃描請求資料的行數 

Extra 關於MYSQL如何解析查詢的額外資訊

　　PROFILE

　　　　顯示SQL執行消耗系統資源的資訊。

查詢執行的過程

MySQL客戶端/伺服器通訊協議是“半雙工”的。客服端/伺服器端都可以向對方傳送資料，但不能同時發生。所以我們無法也無須將一個訊息切成小塊獨立來傳送。

這種協議沒辦法進行流量控制。

客戶端傳送請求的資料包大小由引數max_allowed_packet限制。如果查詢太大，服務端會拒絕接受更多的資料並丟擲相應的錯誤。

伺服器端返回的多個資料包，客戶端必須完整接受。

　　1.查詢狀態 SHOW FULL PROCESSLIST　　　　　　

mysql>SHOW FULL PROCESSLIST;
    Id  User    Host             db            Command    Time  State   Info                   
------  ------  ---------------  ------------  -------  ------  ------  -----------------------
     1  root    localhost:61316  laravel_blog  Query         0  (NULL)  show FULL processlist  
     2  root    localhost:61319  (NULL)        Sleep        94          (NULL)

 　　　　對於一個連線，或者說一個執行緒，任何時刻都有一個狀態，該狀態表示了MySQL當前正在做什麼。

 

　　2.查詢快取　　

-- 檢視快取是否開啟 （query_cache_type 為 ON 表示已經開啟
mysql> show variables like '%query_cache%'; 

+------------------------------+----------+
| Variable_name                | Value    |
+------------------------------+----------+
| have_query_cache             | YES      |
| query_cache_limit            | 1048576  |
| query_cache_min_res_unit     | 4096     |
| query_cache_size             | 20971520 |
| query_cache_type             | ON       |
| query_cache_wlock_invalidate | OFF      |
+------------------------------+----------+

　　　　檢查sql是否命中快取。命中則檢查一次使用者許可權後返回，這個檢查是通過一個對大小寫敏感的雜湊查詢實現的。兩次查詢只要有一個位元組的不同就會失敗。否則將進入下一個階段。

　　　　當sql中有不確定的資料時，則不會被快取。例如使用者自定義函式、儲存函式、使用者變數、臨時表、mysql庫中的系統表，其查詢結果都不會被快取。

　　

　3.查詢優化

　　　　語法解析器和預處理

　　　　　　MySQL通過關鍵字將sql語句進行解析，並生成一顆對應的解析樹。這個過程解析器主要通過語法規則來驗證和解析。比如sql中是否使用了錯誤的關鍵字或者關鍵字的順序是否正確等。預處理則會根據MySQL規則進一步檢查解析樹是否合法。比如檢查要查詢的資料表和資料列是否存在等。

　　　　查詢優化器

　　　　　　經過前面的步驟生成的語法樹被認為是合法的了，並且由優化器將其轉化成查詢計劃。多數情況下，一條查詢可以有很多種執行方式，最後都返回相應的結果。優化器的作用就是找到這其中最好的執行計劃。

　　　　　　MySQL使用基於成本的優化器，通過計算成本選擇其中最小的一個。通過SHOW STATUS LIKE 'Last_query_cost';檢視成本。成本的最小單位是隨機讀取一個4K資料頁的成本。

　　　　　　MySQL的查詢優化器是一個非常複雜的部件，它使用了非常多的優化策略來生成一個最優的執行計劃：

      • 重新定義關聯表的順序
      • 將外連線轉化成內連線
      • 使用等價變換規則
      • 優化count（）、min（）、max（）
      • 預估並轉化為常數表示式
      • 覆蓋索引描述
      • 子查詢優化
      • 提前終止查詢
      • 等值傳播
      • 列表IN（）的比較　　　　

　　　　　　上面列舉了一些，隨著MySQL的不斷髮展，優化器使用的優化策略也在不斷的進化。　

　　　　查詢執行引擎

　　　　　　在完成解析和優化階段以後，MySQL會生成對應的執行計劃，查詢執行引擎根據執行計劃給出的指令逐步執行得出結果。整個執行過程的大部分操作均是通過呼叫儲存引擎實現的介面來完成，這些介面被稱為handler API。查詢過程中的每一張表由一個handler例項表示。實際上，MySQL在查詢優化階段就為每一張表建立了一個handler例項，優化器可以根據這些例項的介面來獲取表的相關資訊，包括表的所有列名、索引統計資訊等。儲存引擎介面提供了非常豐富的功能，但其底層僅有幾十個介面，這些介面像搭積木一樣完成了一次查詢的大部分操作。

　　　　返回結果

　　　　　　查詢執行的最後一個階段就是將結果返回給客戶端。即使查詢不到資料，MySQL仍然會返回這個查詢的相關資訊，比如該查詢影響到的行數以及執行時間等。

　　　　　　如果查詢快取被開啟且這個查詢可以被快取，MySQL也會將結果存放到快取中。

　　　　　　結果集返回客戶端是一個增量且逐步返回的過程。有可能MySQL在生成第一條結果時，就開始向客戶端逐步返回結果集了。這樣服務端就無須儲存太多結果而消耗過多記憶體，也可以讓客戶端第一時間獲得返回結果。需要注意的是，結果集中的每一行都會以一個滿足①中所描述的通訊協議的資料包傳送，再通過TCP協議進行傳輸，在傳輸過程中，可能對MySQL的資料包進行快取然後批量傳送。

 

效能優化

　　優化count()查詢

　　　　count（）是一個特殊的函式。可以統計行數、某個列值的數量。在統計列值時要求列值是非空的（不統計NULL）。在統計行數時count（*）不會被擴充套件成所有的列，而是忽略所有的列。這樣寫意義清晰，效能好。

    • 在不要求完全精確時，EXPLAIN返回一個優化器估算的近似值
    • 快速，精確和實現簡單，三者永遠只能滿足其二，必須舍掉其中一個。增加一個彙總表也是

　　

　　優化LIMIT分頁

　　　　LIMIT 10000，20這樣的查詢，MySQL需要查詢10020條記錄後返回最後20記錄。一般優化為WHERE id>10000 LIMIT 20。其他優化關聯一個冗餘表，冗餘表只包含主鍵列和需要排序的資料列。

　　

　　資料型別優化

　　　　選擇資料型別的原則：更小的通常更好、簡單就好、儘量避免NULL。

　　　　更小的資料型別通常會更快，因為佔用更少的磁碟、記憶體和CPU快取。

　　　　簡單的資料型別需要更少的CPU週期。例：int比char的操作代價低。

　　這裡總結幾個可能容易理解錯誤的技巧：

    1. 通常來說把可為NULL的列改為NOT NULL不會對效能提升有多少幫助，只是如果計劃在列上建立索引，就應該將該列設定為NOT NULL。

    2. 對整數型別指定寬度，比如INT(11)，沒有任何卵用。INT使用32位（4個位元組）儲存空間，那麼它的表示範圍已經確定，所以INT(1)和INT(20)對於儲存和計算是相同的。

    3. UNSIGNED表示不允許負值，大致可以使正數的上限提高一倍。比如TINYINT儲存範圍是-128 ~ 127，而UNSIGNED TINYINT儲存的範圍卻是0 - 255。

    4. 通常來講，沒有太大的必要使用DECIMAL資料型別。即使是在需要儲存財務資料時，仍然可以使用BIGINT。比如需要精確到萬分之一，那麼可以將資料乘以一百萬然後使用BIGINT儲存。這樣可以避免浮點數計算不準確和DECIMAL精確計算代價高的問題。

    5. TIMESTAMP使用4個位元組儲存空間，DATETIME使用8個位元組儲存空間。因而，TIMESTAMP只能表示1970 - 2038年，比DATETIME表示的範圍小得多，而且TIMESTAMP的值因時區不同而不同。

    6. 大多數情況下沒有使用列舉型別的必要，其中一個缺點是列舉的字串列表是固定的，新增和刪除字串（列舉選項）必須使用ALTER TABLE（如果只只是在列表末尾追加元素，不需要重建表）。

    7. schema的列不要太多。原因是儲存引擎的API工作時需要在伺服器層和儲存引擎層之間通過行緩衝格式拷貝資料，然後在伺服器層將緩衝內容解碼成各個列，這個轉換過程的代價是非常高的。如果列太多而實際使用的列又很少的話，有可能會導致CPU佔用過高。

    8. 大表ALTER TABLE非常耗時，MySQL執行大部分修改表結果操作的方法是用新的結構建立一個張空表，從舊錶中查出所有的資料插入新表，然後再刪除舊錶。尤其當記憶體不足而表又很大，而且還有很大索引的情況下，耗時更久。當然有一些奇技淫巧可以解決這個問題，有興趣可自行查閱。

 　　索引的設計

　　　　索引的優點：大大減少了伺服器需要掃描的資料量、幫主伺服器避免排序和臨時表、可以將隨機I/O變為順序I/O；

“三星系統”：索引將相關的記錄放到一起則獲得一星；如果索引中的資料順序和查詢中的排序順序一致則獲得二星；如果索引中的列包含了查詢中需要的全部列則獲得三星。

    • 獨立的列：索引列不能是表示式的一部分，也不能是函式的引數。索引選擇區分度高。
    • 字首索引：如果列很長，通常索引開始的部分字元，可以大大節約索引空間，從而提高索引效率。
    • 聯合索引：當多個索引and時，通常是一個包含所有相關列的索引好過多個單獨索引；當多個索引or時，通常是分開查詢好過單次查詢。

 　　　　注：如果在EXPLAIN中看到有索引合併（Extra欄位出現Using union），應該好好檢查一下查詢和表的結構，看是不是已經是最優的。

 

參考資料

　　Baron Scbwartz 等著；寧海元 周振興等譯；高效能MySQL（第三版）; 電子工業出版社， 2013

　　

相關文章