4. 查詢效能優化

4.1 為什麼查詢速度會變慢

• 快速查詢取決於響應時間
• 如果把查詢看成是一個任務，那麼它由一系列子任務組成，每個子任務都會消耗一定的時間。如果要優化查詢，實際上要優化其子任務，要麼消除其中一些子任務，要麼減少子任務的執行次數，要麼讓子任務執行得更快(有時候需要修改一些查詢，減少這些查詢對系統中執行的其他查詢的影響，這時候是在減少一個查詢的資源消耗)。
• 查詢的生命週期：
  • 從客戶端
  • 到伺服器
  • 在伺服器上：
    • 解析
    • 生成執行計劃
    • 執行：最重要的階段。包括了大量為了檢索資料到儲存引擎的呼叫及呼叫後的資料處理，包括排序，分組等。
  • 返回結果給客戶端
• 完成這些任務，需要在不同的地方花費時間。包括網路，CPU計算，生成統計資訊和執行計劃、鎖等待(互斥等待)等操作，尤其是向底層儲存引擎檢索資料的呼叫操作，這些呼叫需要在記憶體操作、CPU操作和記憶體不足時導致的IO操作上消耗的時間。根據儲存引擎的不同，可能還會產生大量的上下文切換以及系統呼叫。

4.2 慢查詢基礎：優化資料訪問：

查詢效能低下最根本的原因是訪問的資料太多。某些查詢可能不可避免地需要篩選大量資料，但這並不常見。大部分效能低下的查詢都可以通過減少訪問的資料量進行優化。對於低效的查詢，可以通過下面兩個步驟分析：

• 確認應用程式是否在檢索大量超過需要的資料。這通常意味著訪問了太多的行，但有時候也可能是訪問了太多的列。
• 確認MySQL伺服器層是否在分析大量超過需要的資料行。

4.2.1 是否向資料庫請求了不需要的資料

有些查詢會請求超過實際需要的資料，然後這些多餘的資料會被應用程式丟棄。這會給MySQL伺服器帶來額外的負擔，並增加網路開銷，另外也會消耗應用伺服器的CPU和記憶體資源。

典型案例：

• 查詢不需要的記錄：一個常見錯誤是誤以為MySQL只會返回需要的資料，實際上MySQL確實先返回全部結果集再進行計算。最簡單有效的辦法是在這樣的查詢後面加上LIMIT。
• 多表關聯時返回全部列：正確做法是隻取需要的列
• 總是取出全部的列：
  • 取出全部的列，會讓優化其無法完成覆蓋掃描這類優化，還會給伺服器帶來額外的資源消耗。要慎重。
  • 但是這可以簡化開發，提供相同程式碼片段的複用性，或者應用程式使用了某種快取機制等其他有必要取出全部列的因素。如果清楚這麼做的效能影響，也是可以考慮的。
• 重複查詢相同的資料：建議初次查詢的時候將這個資料快取起來，需要的時候從快取中取出。

4.2.2 MySQL是否在掃描額外的記錄

對於MySQL，最簡單的衡量查詢開銷的三個指標：響應時間、掃描的行數和返回的行數。沒有哪個指標能夠完美地衡量查詢的開銷，但它們大致反映了MySQL在內部執行查詢時需要訪問多少資料，並可以大概推算出查詢執行的時間。這三個指標都會記錄到MySQL的慢日誌中，檢查慢日誌記錄是找出掃描行數過多的查詢的好辦法。

• 響應時間：
  • 響應時間=服務時間+排隊時間。實際上沒辦法細分，目前還沒有辦法測量。
    • 服務時間：資料庫處理這個查詢真正花的時間。
    • 排隊時間：伺服器因為等待某些資源而沒有真正執行查詢的時間。
  • 看到一個查詢的響應時間的時候，要評估是否合理。概括地說，瞭解這個查詢需要那些索引以及它的執行計劃是什麼，然後計算大概需要多少個順序和隨機IO，在用其乘以在具體硬體條件下一次IO消耗的時間，最後把這些消耗都加起來得到一個參考值。
• 掃描的行數和返回的行數
  • 分析查詢時，檢視該查詢掃描的行數是非常有幫助的。在一定程度上能夠說明該查詢找到需要的資料效率高不高。
  • 不過這個指標還不夠完美，因為並不是所有的行的訪問代價都是一樣的。
  • 理想情況下掃描的行數和返回的行數應該是相同的。但實際情況下很少存在，比如關聯查詢。一般掃描的行數與返回的行數比率通常在1:1和10:1之間。
• 掃描的行數和訪問型別
  • 在評估查詢開銷的時候，需要考慮從表中找到某一行資料的成本。
  • 訪問型別(EXPLAIN語句中的type列，row列顯示掃描的行數)：
    • ref
    • ALL(全表掃描)
  • MySQL應用WHERE條件的方式：
    • 在索引中使用WHERE條件來過濾不匹配的記錄，這是在儲存引擎完成的。
    • 使用覆蓋索引掃描(在EXTRA 列中出現了Using index)來返回記錄，直接從索引中過濾不需要的記錄並返回命中結果。這是在MySQL伺服器完成的，但無須再回表查詢
    • 從資料表中返回資料，然後過濾不滿足條件的記錄(在Extra列中出現Using Where)。這在MySQL伺服器層完成，MySQL需要先從資料表讀出記錄然後過濾。
  • 優化技巧：
    • 使用索引覆蓋掃描，把所有需要的列都放到索引中，這樣儲存引擎無須回表獲取對應的行就可以返回結果。
    • 改變庫表結構。例如使用單獨的彙總表
    • 重寫這個複雜的查詢，讓MySQL優化器能夠以更優化的方式執行這個查詢。

4.3 重構查詢方式

4.3.1 一個複雜的查詢還是多個簡單的查詢

• MySQL從設計上讓連線和斷開連線都很輕量級，在返回一個小的查詢結果方面很高效。
• MySQL內部每秒能夠掃描記憶體中上百萬行資料，相比之下，MySQL響應資料給客戶端就慢得多了。
• 使用盡可能少的查詢，但某些場景下將一個大查詢分解為多個小查詢是很有必要的。

4.3.2 切分查詢

• 有時候需要對一個大查詢切分成小查詢，每個查詢功能完全一樣，只完成一小部分，每次只返回一小部分查詢結果。
• 例如刪除舊的資料，切分該查詢可以儘可能小地影響效能，還可以減少MySQL複製的延遲。一次刪除一萬行資料一般來說是一個比較高效而且對伺服器影響也最小的做法，如果是事務型引擎，很多時候小事務能夠更高效。另外，每次刪除後都暫停一會，能夠將一次性的壓力分散到一個很長的時間段中，就可以大大降低對伺服器的影響，還可以大大減少刪除時鎖的持有時間。

4.3.3 分解關聯查詢

很多高效能的應用都會對關聯查詢進行分解。簡單地，可以對每一個表進行一次單表查詢，然後將結果在應用程式中進行管理

SELECT * FROM tag
    JOIN tag_post ON tag_post.tag_id=tag.id
    JOIN post ON tag_post.post_id=post.id
WHERE tag.tag='mysql';
-- 可以分解成：
SELECT * FROM tag WHERE tag='mysql';
SELECT * FROM tag_post where tag_id=1234;
SELECT * FROM post whre post.id in (123, 456);複製程式碼

• 優勢：
  • 讓快取效率更高。許多應用程式可以方便地快取單表查詢時對應的結果物件，這樣可以減少查詢時的條件。而對MySQL的查詢快取來說，如果關聯中的某個表發生了變化，那麼久無法使用查詢快取了，而拆分後，如果某個表很少改變，那麼基於該表的查詢就可以重複利用查詢快取結果。
  • 執行單個查詢可以減少鎖的競爭。
  • 在應用層做關聯，可以更容易對資料庫進行拆分，更容易做到高效能和可擴充套件。
  • 查詢本身效率也可能會有所提升。例如使用IN()代替關聯查詢，可以讓MySQL按照ID順序進行查詢，這可能比隨機的關聯要更高效。
  • 可以減少冗餘記錄的查詢。在應用層做關聯查詢，意味著對於某條記錄應用只需要查詢一次，而在資料庫中做關聯查詢，則可能需要重複地訪問一部分資料。從這點看，這樣的重構還可能會減少網路和記憶體的消耗。
  • 更進一步，這樣做相當於在應用中實現了雜湊關聯，而不是使用MySQL的巢狀迴圈關聯。某些場景雜湊關聯的效率要高得多。
• 通過重構查詢將關聯放到應用程式中更加高效的場景：
  • 當應用能夠方便地快取當個查詢的結果
  • 當可以將資料分佈到不同的MySQL伺服器上
  • 當能夠使用IN()的方式來代替關聯查詢
  • 當查詢中使用同一個資料表的時候

4.4 查詢執行的基礎

當向MySQL傳送一個請求的時候，MySQL的工作流程：

​

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

4.4.1 MySQL客戶端/伺服器通訊協議

• 客戶端與伺服器之間的通訊協議是”半雙工“的，這意味著在任何一個時刻，只能有客戶端或伺服器的其中一個傳送資料。
  • 限制：
    • 沒辦法進行流量控制，一旦一端開始傳送訊息，另一端要接收完整訊息才能響應它。
  • 客戶端用一個單獨的資料包將查詢傳給伺服器。如果查詢太大，伺服器會根據配置max_allowed_packet拒絕更多資料並丟擲相應錯誤。
  • 伺服器響應給使用者的資料通常更多，由多個資料包組成。當伺服器開始響應客戶端請求時，客戶端必須完整地接收整個返回結果。如果只取前面幾條結果，或者接收幾條結果後就直接斷開連線，建議在查詢中加上LIMIT限制。
  • 客戶端多數連線MySQL的庫函式(如Java，Python)都可以獲取全部結果集並快取到記憶體中，還可以逐行獲取需要的資料。預設一般是獲得全部結果集並快取到記憶體中。
    • MySQL通常需要等到所有的資料都已經傳送給客戶端才能釋放這條查詢所佔用的資源，所以接收全部結果並快取通過可以減少伺服器的壓力，讓查詢早點結束而釋放相應資源。
    • 當使用多數連線MySQL的庫函式從MySQL獲取資料時，其結果看起來都像是從MySQL伺服器獲取資料，而實際上都是從這個庫函式的快取獲取資料。但是當返回一個很大的結果集時，庫函式會很很多時間和記憶體來儲存所有的結果集，如果能夠儘早開始處理，就能大大減少記憶體消耗，這種情況下可以不使用快取來記錄結果而是直接處理。這樣做的缺點是，對伺服器來說，需要等到查詢完成後才能釋放資源，因此伺服器的資源都是被這個查詢佔用。
  • 查詢狀態，對一個MySQL連線或者說一個執行緒，任何時候都有一個狀態。最簡單辦法使用SHOW FULL PROCESSLIST命令檢視：
    • SLEEP：執行緒正在等待客戶端傳送新的請求
    • QUERY：執行緒正在執行查詢或者正在將查詢結果傳送給客戶端
    • LOCKED：在MySQL伺服器層，該執行緒正在等待表鎖。在儲存引擎級別實現的鎖，例如InnoDB的行鎖，並不會體現線上程狀態中。對於MyISAM來說這是一個比較典型的狀態，在其他沒有行鎖的引擎中也經常會出現。
    • Analyzing and statistics：執行緒正在收集儲存引擎的統計資訊，並生成查詢的執行計劃
    • Copying to tmp table [on disk]：執行緒正在執行操作，並且將其結果集都複製到一個臨時表中，這種狀態一般要麼是在做GROUP BY操作，要麼是檔案排序操作，或者是UNION操作。如果這個狀態後面還有”on disk“標記，那標識MySQL正在將一個臨時記憶體表放到磁碟上。
    • Sorting result：執行緒正在對結果集進行排序。
    • Sending data：執行緒可能在多個狀態間傳送資料，或者在生成結果集，或者在向客戶端返回資料。

4.4.2 查詢快取

• 在解析一個查詢語句之前，如果查詢快取是開啟的，那麼MySQL會優先檢查這個查詢是否命中查詢快取的資料。
  • 這個檢查是通過一個大小寫敏感的雜湊查詢來實現的。
  • 查詢與快取中的查詢即使只有一個位元組不同，那也不會匹配快取結果，這種情況下查詢就會進入下一階段的處理。
  • 如果當前的查詢恰好命中了查詢快取，那麼在返回查詢結果之前MySQL會檢查一次使用者許可權。這也不需要解析查詢SQL語句的，因為在查詢快取中已經存放了當前查詢需要訪問的表資訊。如果許可權沒有問題，MySQL會跳過所有其它的階段，直接從快取中拿到結果並返回給客戶端。

4.4.3 查詢優化處理

查詢的生命週期的下一步是將一個SQL轉換成一個執行計劃，MySQL再依照這個執行計劃和儲存引擎進行互動，這包括多個子階段：解析SQL、預處理、優化SQL執行計劃。這個過程中的任何錯誤(例如語法錯誤)都可能終止查詢，另外在實際執行中，這幾部分可能一起執行也可能單獨執行。

• 語法解析器和預處理：

  • 流程：
    • MySQL通過關鍵字將SQL進行解析，並生成一顆對應的”解析樹“。MySQL解析器將使用MySQL語法規則驗證和解析查詢。例如：驗證是否使用錯誤的關鍵詞，或者使用關鍵詞的順序是否正確等，再或者它還會驗證引號是否能前後正確匹配。
    • 前處理器則根據一些MySQL規則進一步檢查解析樹是否合法，例如，這裡將檢查資料表和資料列是否存在，還會解析名字和別名，看看它們是否有歧義。
    • 前處理器會驗證許可權。這通常很快，除非伺服器上有非常多的許可權配置。

• 查詢優化器：

  • 經過語法解析器和預處理後，語法樹被認為是合法的，並將由優化器將其轉化成執行計劃。

    • 一條查詢可以有很多執行方式，最後都返回相同的結果。
    • 優化器的作用就是找到這其中最好的執行計劃。

  • MySQL使用基於成本的優化器，它將嘗試預測一個查詢使用某種執行計劃時的成本，並選擇其中成本最小的一個。

    • 最初，成本的最小單位是隨機讀取一個4K資料頁的成本，後來成本計算公式變得更加複雜，並且引入了一些”因子“來估算這些操作的代價，比如當執行一次WHERE條件比較的成本。
    • 可以通過SHOW STATUS LIKE 'Last_query_cost';來查詢當前會話的當前查詢的成本，其值N為MySQL的優化器認為大概需要做N個資料頁的隨機查詢才能完成當前的查詢。
    • Last_query_cost根據一系列統計資訊計算得來：每個表或索引的頁面個數、索引的基數(索引中不同值的數量)、索引和資料行的長度、索引的分佈情況。優化器在評估成本的時候並不考慮任何層面的快取，它假設讀取任何資料都需要一次磁碟IO。

  • 導致MySQL選擇錯誤的執行計劃的原因:

    • 統計資訊不準確。MySQL依賴儲存引擎提供的統計資訊來評估成本，但是有的儲存引擎提供的資訊可能偏差非常大。例如，InnoDB因為其MVVC的架構，並不能維護一個資料表的行數的精確統計資訊。
    • 執行計劃中的成本估算不等同於實際執行的成本。所以即使統計資訊精確，優化器給出的執行計劃也可能不是最優的。例如有時候某個執行計劃雖然需要讀取更多的頁面，但是它的成本卻更小，因為這些頁面可能都是順序讀或者這些頁面已經在記憶體中，它的訪問成本將很小。
    • MySQL的最優可能和你想的最優不一樣。你可能希望執行時間儘可能短，但是MySQL只是基於其成本模型選擇最優的執行計劃，而有些時候這並不是最快的執行方式。所以，這裡我們看到根據執行成本來選擇執行計劃並不是完美的模型。
    • MySQL從不考慮其他併發執行的查詢，這可能會影響到當前查詢的速度。
    • MySQL也並不是任何時候都是基於成本的優化。有時也會基於一些固定的規則，例如，如果存在全文搜尋的MATCH()子句，則在存在全文索引的時候就使用全文索引，即使有時候使用別的索引和WHERE條件可以遠比這種方式要快，MySQL也仍然會使用對應的全文索引。
    • MySQL不會考慮不受其控制的操作成本，例如執行儲存過程或者使用者自定義函式的成本。
    • 優化器有時候無法去估算所有可能的執行計劃，所以它可能錯過實際上最優的執行計劃。

  • 優化策略：

    • 靜態優化：直接對解析樹進行分析，並完成優化。例如，優化器可以通過一些簡單的代數變換將WHERE條件轉換成另一種等價形式。靜態優化不依賴於特別的數值，如WHERE條件中帶入的一些常數等。靜態優化在第一次完成後就一直有效，即使使用不同的引數重複執行查詢也不會發生變化。可以認為這是一種”編譯時優化“。
    • 動態優化：與上下文有關，也可能和其他很多因素有關，例如WHER條件中的取值，索引中條目對應的資料行數等。這需要在每次查詢的執行時候都重新評估，甚至在其執行過程中也會重新優化，可以認為這是”執行時優化“。

  • MySQL能夠處理的優化型別：

    • 重新定義關聯表的順序：資料表的關聯並不總是按照在查詢中指定的順序執行。決定關聯的順序是優化器很重要的一部分功能。

    • 將外連線轉換為內連線：並不是所有的OUTER JOIN語句都必須以外連線的方式執行。例如WHERE條件，庫表結構都可能會讓外連線等價於一個內連線。

    • 使用等價變換規則：MySQL使用一些等價變換來簡化並規範表示式。它可以合併和減少一些比較，還可以移除一些恆成立和一些恆不成立的判斷。

    • 優化COUNT()、MIN()和MAX()：索引和列是否可為空可以幫助MySQL優化這類表示式。例如，要找到某一列的最小值，只需要查詢B-Tree索引最左端的記錄，MySQL可以直接獲取，並在優化器生成執行計劃的時候就可以利用這一點(優化器會將這個表示式作為一個常數對待，在EXPLAIN就可以看到"Select tables optimized away")。類似的，沒有任何WHERE條件的COUNT(*)查詢通常也可以使用儲存引擎提供的一些優化(MyISAM維護了一個變數來存放資料表的行數)

    • 預估並轉換為常數表示式：MySQL檢測到一個表示式可以轉換為常數的時候，就會一直把該表示式作為常數進行優化處理。例如：一個使用者自定義變數在查詢中沒有發生變化、數學表示式、某些特定的查詢(在索引列上執行MIN，甚至是主鍵或唯一鍵查詢語句)、通過等式將常數值從一個表傳到另一個表(通過WHERE、USING或ON來限制某列取值為常數)。

    • 覆蓋索引掃描：當索引中的列包含所有查詢中所有需要的列的時候，MySQL就可以使用索引返回需要的資料，而無須查詢對應的資料行。

    • 子查詢優化：在某些情況下可以將子查詢轉換成一種效率更高的形式，從而減少多個查詢多次對資料的訪問。

    • 提前終止查詢：當發現已經滿足查詢的需求，能夠立刻終止查詢。例如使用了LIMIT子句，或者發現一個不成立的條件(立即返回一個空結果)。當儲存引擎需要檢索”不同取值“或者判斷存在性的時候，例如DISTINCT，NOT EXIST()或者LEFT JOIN型別的查詢，MySQL都會使用這類優化。

    • 等值傳播：如果兩個列的值通過等式關聯，那麼就可以把其中一個列的WHERE條件傳遞到另一個列上。

      SELECT film.film_id
      FROM sakila.film
          INNER JOIN sakila.film_actor USING(film_id)
      WHERE film.file_id > 500;
      -- 如果手動通過一些條件來告知優化器這個WHERE條件適用於兩個表，在MySQL中反而讓查詢更難維護。
      ... WHERE film.file_id > 500 AND film_actor.film_id > 500;複製程式碼

    • 列表IN()的比較：不同於其它資料庫IN()完全等價於多個OR條件語句，MySQL將IN()列表中的資料先進行排序，然後通過二分查詢的方式來確定列表的值是否滿足條件，前者查詢複雜度為O(n)，後者為O(log n)。對於有大量取值的情況，MySQL這種處理速度會更快。

• 資料和索引的統計資訊：

  • MySQL架構在伺服器層有查詢優化器，但卻沒有儲存資料和索引的統計資訊。因為統計資訊由儲存引擎實現，不同的儲存引擎可能會儲存不同的統計資訊。
  • MySQL查詢優化器在生成查詢的執行計劃時，需要向儲存引擎獲取相應的統計資訊。儲存引擎則提供給優化器對應的統計資訊，包括：每個表或索引有多少個頁面、每個表的每個索引的基數是多少、資料行和索引的長度、索引的分佈資訊等。

• MySQL如何執行關聯查詢：

  • MySQL認為任何一個查詢都是管理，而不侷限於需要兩個表的匹配，包括每一個查詢，每一個片段(例如子查詢，甚至基於單表的SELECT)
    • UNION查詢的例子：MySQL先將一系列的單個查詢結果放到一個臨時表中，然後再重新讀出臨時表資料來完成UNION查詢。該讀取臨時表結果的操作也是關聯。
  • MySQL關聯執行的策略：對任何關聯都執行巢狀迴圈關聯操作，即MySQL先在一個表中迴圈取出單條資料，然後再巢狀迴圈到下一個表中尋找新配的行，依次下去，直到找到所有表中匹配的行為止。然後根據各個表匹配的行，返回查詢中需要的各個列。MySQL會嘗試在最後一個關聯表中找到所有匹配的行，如果最後一個關聯表無法找到更多的行以後，MySQL返回到上一層關聯表，看是否能夠找到更多的匹配記錄，依此類推迭代執行。
    • 從本質上來說，所有型別的查詢都以同樣的方式執行。例如，MySQL在FROM子句中遇到子查詢時，先執行子查詢並將其結果放到一個臨時表(MySQL的臨時表是沒有任何索引的，UNION查詢也一樣)，然後將這個臨時表當作一個普通表對待。簡而言之，所有的查詢型別都轉換成類似的執行計劃(在MySQL5.6和MariaDB有重大改變，兩個版本都引入更加複雜的執行計劃)
    • 不過不是所有的查詢都可以轉換。例如，全外連線就無法通過巢狀迴圈和回溯的方式完成，這是當發現關聯表中沒有找到任何匹配行的時候，則可能是因為關聯是恰好從一個沒有任何匹配的表開始。這大概也是MySQL並不支援全外連線的原因。

• 執行計劃：

  • MySQL生成查詢的一顆指令樹，然後通過儲存引擎執行完成這顆指令樹並返回結果，這點和其他生成查詢位元組碼來執行查詢的其它關聯式資料庫不同。
  • 最終的執行計劃包含了重構查詢的全部資訊。可以對查詢執行EXPLAIN EXTENDED後再執行SHOW WARNINGS看到重構的查詢(和原查詢有相同的語義，但是查詢語句可能並不完全相同)
  • 任何多表查詢都可以用一棵樹來表示，我們可以理解為一顆平衡樹，但是MySQL的執行計劃是一顆左側深度優先的樹。

• 關聯查詢優化器：

  • MySQL優化器最重要的一部分就是關聯查詢優化，它決定了多個表關聯時的順序。通常多表關聯的時候，可以有多張不同的關聯順序，而關聯查詢優化器則通過評估不同順序的成本來選擇一個代價最小的關聯順序。
  • 有的時候，優化器給出的並不是最優的關聯順序，這時可以使用STRAIGHT_JOIN關鍵字來重寫查詢，讓優化器按照你認為的最優關聯順序執行——絕大多數時候，優化器做出的選擇都更為準確。
  • 優化器會盡可能遍歷沒一個表然後逐個做巢狀迴圈計算每一顆可能的執行計劃的樹的成本，最後返回一個最優的執行計劃。
    • 如果有N個表關聯，那麼需要檢查n的階乘種關聯順序。我們稱之為所有可能的執行計劃的”搜尋空間“。如果搜尋空間非常大，當需要關聯的表超過optimizer_search_depth的限制，優化器會選擇使用”貪婪“搜尋的方式查詢”最優“的關聯方式。
  • 優化器偶爾也會選擇一個不是最優的執行計劃。
  • 有時，各個查詢的順序並不能隨意安排，這時關聯優化器可以根據這些規則大大減少搜尋空間，例如，左連線、相關子查詢。這是因為後面的表的查詢需要依賴於前面表的查詢結果，這種依賴關係通常可以幫助優化器大大減少需要掃描的執行計劃數量。

• 排序優化

  • 無論如何排序都是一個成本很高的操作，所以從效能角度考慮，應儘可能避免排序或者儘可能避免對大量資料進行排序。

  • 檔案排序：當不能使用索引生成排序結果的時候，MySQL需要自己進行排序，如果資料量小則在記憶體中進行，如果資料量大則需要使用磁碟。

    • 如果需要排序的資料量小於”排序緩衝區“，MySQL使用記憶體進行”快速排序“操作。
    • 如果記憶體不夠排序，MySQL會先將資料分塊，對每個獨立的塊使用”快速排序“進行排序，並將各個塊的排序結果存放在磁碟上，然後將各個排序好的塊進行合併，最後返回排序結果。

  • 排序演算法：

    • 兩次傳輸排序(舊版本使用)：讀取行指標和需要排序的欄位，對其進行排序，然後在根據排序結果讀取需要的資料行。這需要兩次資料傳輸，即需要從資料表中讀取兩次資料，第二次讀取資料的時候，因為讀取順序列進行排序後的所有記錄，這會產生大量的隨機IO，所以成本非常高。當使用的是MyISAM表的時候，成本可能會更高，因為MyISAM使用系統呼叫進行資料讀取(非常依賴作業系統對資料的快取)。不過這樣做的優點是：排序的時候儲存儘可能少的資料，讓”排序緩衝區“中儘可能容納更多的行數進行排序。
    • 單次傳輸排序(4.1後新版本使用)：先讀取查詢所需要的所有列，然後再根據給定列進行排序，最後直接返回排序結果。有點事只需要一次順序IO讀取所有的資料，而無須任何的隨機IO，缺點是如果需要返回的列非常大，會額外佔用大量的空間，而這些列對排序操作來說是沒有任何作用的。因為單條排序記錄很大，所以可能會有更多的排序塊需要合併。
    • 很難說以上兩個演算法效率更高，當查詢需要所有的列的總長度不超過max_lenght_for_sort_data時，MySQL使用“單次傳輸排序”，可以通過引數選擇來影響MySQL排序演算法的選擇。

  • 進行檔案排序的時候需要使用的臨時儲存空間可能會比想象的要大得多。原因在於MySQL排序時，對每一個排序記錄都會分配一個足夠長的定長空間來存放。

    • 這個定長空間必須足夠長以容納其中最長的字串。如果是VARCHAR列則需要分配其完整長度，如果使用UTF-8字符集，則為每個字元預留三個位元組。

  • 在關聯查詢的時候排序：

    • 如果ORDER BY子句中所有列都來自關聯的第一個表，那麼MySQL在關聯處理第一個表的時候進行檔案排序。可以在EXPLAIN看到Extra欄位有“Using filesort”

    • 除第一種場景，MySQL都會先將關聯的結果放到一個臨時表中，然後在所有的關聯都結束後，再進行檔案排序操作。用EXPLAIN可看到“Using temporary;Using filesort”。如果查詢中有LIMIT的話，LIMIT也會在排序之後應用，所以即使需要返回較少的資料，臨時表和需要排序的資料仍然非常大。

      5.6後版本在這裡做了些改進：當只需要返回部分排序結果的時候，例如使用了LIMIT子句，MySQL不再對所有的結果進行排序，而是根據實際情況，選擇拋棄不滿足條件的結果，然後在進行排序。

4.4.4 查詢執行引擎

• 在解析和優化階段，MySQL將生成查詢對應的執行計劃，MySQL的查詢執行引擎則根據這個執行計劃來完成整個查詢。

  • 這裡的執行計劃是一個資料結構，不同於其他關聯式資料庫生成的位元組碼。

• 查詢執行階段不是那麼複雜，MySQL只是簡單地根據執行計劃給出的指令逐步執行。在根據執行計劃逐步執行的過程中，又大量的操作需要呼叫儲存引擎實現的“handle API”介面來完成。

  • 查詢中的每一個表由一個handler例項來標識。實際上，MySQL在優化階段就為每個表建立了一個handler例項，優化器根據這些例項的介面可以獲取表的相關資訊。
  • 儲存引擎的介面有著非常豐富的功能，但是底層介面卻只有十幾個，這些介面相互配合能夠完成查詢的大部分操作

• 並不是所有的操作都有handler完成。例如，當MySQL需要進行表鎖的時候，handler可能會實現自己級別的、更細粒度的鎖，如InnoDB就實現了自己的行基本鎖，但這並不能代替伺服器層的表鎖。如果是所有儲存共有的特性則由伺服器層實現，如時間、日期函式、檢視和觸發器等。

4.4.5 返回結果給客戶端

• 查詢執行的最後一個階段。即使查詢不需要返回結果集給客戶端，MySQL仍然會返回這個查詢的一些資訊，如該查詢影響到的行數。
• 如果查詢可以被快取，那麼MySQL在該階段也會將結果放到查詢快取中。
• 結果集返回客戶端是一個增量、逐步返回的過程。例如，伺服器處理完關聯操作的最後一個關聯表，開始生成第一條結果時，MySQL就可以開始向客戶端逐步返回結果。
  • 好處：
    • 伺服器端無須儲存太多的結果，也就不會因為要返回太多的結果而消耗太多的記憶體。
    • 讓客戶端第一時間獲得返回的結果。可使用SQL_BUFFER_RESULT設定。
  • 結果集中的每一行都會以一個滿足MySQL客戶端/伺服器通訊協議的封包傳送，再通過TCP協議進行傳輸，在TCP傳輸的過程中，可能對MySQL的封包進行快取然後批量傳輸。

4.5 MySQL查詢優化器的侷限性

MySQL的萬能“巢狀迴圈”並不是對每種查詢都是最優的，但只對少部分查詢不適用，我們往往可以通過改寫查詢讓MySQL高效地完成工作。另外，5.6版本會消除很多原本的限制，讓更多的查詢能夠已儘可能高的效率完成。

4.5.1 關聯子查詢

MySQL的子查詢實現得非常糟糕，最糟糕的一類查詢是WHERE條件語句中包含IN()的子查詢。

SELECT * FROM sakila.film
WHERE film_id IN(
    SELECT film_id FROM sakil.film_actor WHERE actor_id =1 );
-- MySQL對IN()列表中的選項有專門的優化策略，但關聯子查詢並不是這樣的,MySQL會將相關的外層表壓到子查詢中，它認為這樣可以高效地查詢到資料行。也就是說，以上查詢會被MySQL更改成：
SELECT * FROM sakila.film
WHERE EXISTS(
    SELECT film_id FROM sakil.film_actor WHERE actor_id =1
    AND film_actor.film_id = film.film_id);
-- 這時子查詢需要根據film_id來關聯外部表的film，因為需要film_id欄位，所以MySQL認為無法先執行這個子查詢。通過EXPLIAN可以看到子查詢是一個相關子查詢(DEPENDENT SUBQUERY)，並且可以看到對film表進行全表掃描，然後根據返回的film_id逐個進行子查詢。如果外層是一個很大的表，查詢效能會很糟糕。
-- 優化重寫方式1：
SELECT film.* FROM sakila.film
    INNER JOIN sakil.film_actor USING(film_id) 
WHERE actor_id =1;
-- 優化重寫方式2：使用函式GROUP_CONCAT()在IN()中構造一個逗號分割的列表。
-- 優化重寫方式3，使用EXISTS()等效的改寫查詢：
SELECT * FROM sakila.film
WHERE EXISTS(
    SELECT film_id FROM sakil.film_actor WHERE actor_id =1
    AND film_actor.film_id = film.film_id);複製程式碼

• 如何用好關聯子查詢
  • 並不是所有的關聯子查詢效能都很差，需要先測試再做出判斷。很多時候，關聯子查詢是一種非常合理、自然、甚至是效能最好的寫法。

4.5.2 UNION的限制

• 有時，MySQL無法將限制條件從外層“下推”到內層，這使得原表能夠限制部分返回結果的條件無法應用到內層查詢的優化上。

• 如果希望UNION的各個子句能夠根據LIMIT只取部分結果集，或者希望能夠先排好序再合併結果集的話，就需要在UNION的各個子句中分別使用這些子句。另外，從臨時表取出資料的順序是不一定的，如果要獲得正確的順序，還需要加上一個全域性的ORDER BY 和 LIMIT

  (SELECT first_name, last_name
   FROM sakila.actor
   ORDER BY last_name)
  UNION ALL
  (SELECT first_name, last_name
   FROM sakila.customer
   ORDER BY last_name)
  LIMIT 20;
  -- 在UNION子句分別使用LIMIT
  (SELECT first_name, last_name
   FROM sakila.actor
   ORDER BY last_name
   LIMIT 20)
  UNION ALL
  (SELECT first_name, last_name
   FROM sakila.customer
   ORDER BY last_name
   LIMIT 20)
  LIMIT 20;複製程式碼

4.5.3 索引合併優化

• 5.0及其新版本，當WHERE條件包含多個複雜條件的時候，MySQL能夠訪問當個表的多個索引以合併和交叉過濾來定位需要查詢的行。

4.5.4 等值傳遞

• 某些時候，等值傳遞會帶來一些意想不到的額外消耗。例如，有一個非常大的IN()列表，而MySQL優化器發現存在WHERE、ON或者USING的子句，將這個列表的值和另一個表的某個列相關聯。
  • 優化器會將IN()列表都複製應用到關聯的各個表中。通常各個表因為新增了過濾條件，優化器可以更高效地從儲存引擎過濾記錄。但是如果列表非常大，則會導致優化和執行都會變得更慢。

4.5.5 並行執行

• MySQL無法利用多核特性來並行執行查詢，這點和其他關係型資料庫不一樣。

4.5.6 雜湊關聯

• MySQL並不支援雜湊關聯——所有的關聯都是巢狀迴圈關聯。不過，可以通過建立一個雜湊索引來曲線實現雜湊關聯。
• 如果使用的是Memory儲存引擎，則索引是雜湊索引，所以關聯的時候也類似於雜湊關聯。

4.5.7 鬆散索引掃描

• MySQL並不支援鬆散索引掃描，也就無法按照不連續的方式掃描一個索引。通常，MySQL的索引掃描需要先定義一個起點和終點，即使需要的資料只是這段索引中很少數的幾個，MySQL仍需要掃描這段索引中每一個欄位。

• 示例：假設我們有索引(a,b)，有以下查詢SELECT ... FROM tb1 WHERE b BETEWEEN 2 AND 3;，因為只使用了欄位b而不符合索引的最左字首，MySQL無法使用這個索引，從而只能通過全表掃描找到匹配的行。

  

  瞭解索引結構的話，會發現還有一個更快的辦法執行上面的查詢。索引的物理結構(不是儲存引擎API)使得可以先掃描a列第一個值對應的b列的範圍，然後在跳到a列第二個只掃描對應的b列的範圍，即鬆散索引掃描。這時就無須再使用WHERE過濾，因為已經跳過了所有不需要的記錄。MySQL並不支援鬆散索引掃描

  

• MySQL5.0 以後的版本，某些特殊的場景下是可以使用鬆散索引掃描的。例如，在一個分組查詢中需要找到分組的最大值和最小值：

  -- 在Extra欄位顯示“Using index for group-by”,表示使用鬆散索引掃描
  EXPLAIN SELECT actor_id, MAX(film_id)
  FROM sakila.film_actor
  GROUP BY actor\G;複製程式碼

• 在MySQL很好地支援鬆散索引掃描之前，一個簡單的繞過辦法就是給前面的列加上可能的常數值。5.6之後的版本，關於鬆散索引掃描的一些限制將會通過“索引條件下推(index condition pushdown)”的方式解決

4.5.8 最大值和最小值優化

• 對於MIN()和MAX()查詢，MySQL的優化做得並不好。

  SELECT MIN(actor_id) FROM sakila.actor WHERE first_name = 'PENELOPE';
  -- 因為在first_name上沒有索引，MySQL將會進行一次全表掃描。如果MySQL能夠進行主鍵掃描，那麼理論上當MySQL讀到第一個滿足條件的記錄，就是需要找到的最小值，因為主鍵是嚴格按照actor_id欄位的大小順序排列的。
  -- 曲線優化辦法：移除MIN()，然後使用LIMIT
  SELECT actor_id FROM sakila.actor USE INDEX(PRIMARY) WHERE first_name = 'PENNLOPE' LIMIT 1;
  -- 該SQL已經無法表達它的本意，一般我們通過SQL告訴伺服器需要什麼資料，再由伺服器決定如何最優地獲取資料。但有時候為了獲得更高的效能，需要放棄一些原則。複製程式碼

4.5.9 在同一個表查詢和更新

• MySQL不允許對同一張表同時進行查詢和更新。這其實並不是優化器的限制，如果清楚MySQL是如何執行查詢的，就可以避免這種情況。可以通過生成表來繞過該限制。

  -- 符合標準的SQL，但是無法執行
  mysql> UPDATE tbl AS outer_tbl
  -> SET cnt = (
  -> SELECT count(*) FROM tbl AS inner_tbl
  -> WHERE inner_tbl.type = outer_tbl.type
  -> );
  -- 生成表來繞過該限制：
  mysql> UPDATE tbl
  -> INNER JOIN(
  -> SELECT type, count(*) AS cnt
  -> FROM tbl
  -> GROUP BY type
  -> ) AS der USING(type)
  -> SET tbl.cnt = der.cnt;複製程式碼

4.6 查詢優化器的提示(hint)

如果對優化器選擇的執行計劃不滿意，可以使用優化器提供的幾個提示(hint)來控制最終的執行計劃。不過MySQL升級後可能會導致這些提示無效，需要重新審查。

• 部分提示型別：

  • HIGH_PRIORITY和LOW_PRIORITY:

    告訴MySQL當多個語句同時訪問某一個表的時候，這些語句的優先順序。只對使用表鎖的儲存引擎有效，但即使是在MyISAM中也要慎重，因為這兩個提示會導致併發插入被禁用，可能會導致嚴重降低效能

    • HIGH_PRIORITY：用於SELECT語句時，MySQL會將此語句重新排程到所有正在等待表鎖以便修改資料的語句之前。實際上是將其放在表的佇列的最前面，而不是按照常規順序等待。用於INSERT語句，其效果只是簡單地抵消了全域性LOW_PRIORITY設定對該語句的影響。
    • 用於SELECT、INSERT、UPDATE和DELETE語句，讓該語句一直處於等待狀態，只要佇列中還有需要訪問同一個表的語句——即使那些比該語句還晚提交到伺服器的語句。

  • DELAYED：

    • 只對INSERT和REPLACE有效。
    • MySQL會將使用該提示的語句立即返回給客戶端，並將插入的行資料放入到緩衝區，然後在表空閒時批量將資料寫入。
    • 日誌系統使用這樣的提示非常有效，或者是其他需要寫入大量資料但是客戶端卻不需要等待單條語句完成IO的應用。
    • 限制：並不是所有的儲存引擎都支援；並且該提示會導致函式LAST_INSERT_ID()無法正常工作

  • STRAIGHT_JOIN:

    當MySQL沒能正確選擇關聯順序的時候，或者由於可能的順序太多導致MySQL無法評估所有的關聯順序的時候，STRAIGNT_JOIN都會很有用。特別是在以下第二種情況，MySQL可能會花費大量時間在”statistics“狀態，加上這個提示會大大減少優化器的搜尋空間。

    可以先使用EXLPAN語句來檢視優化器選擇的關聯順序，然後使用該提示來重寫查詢，確定最優的關聯順序。但是在升級MySQL的時候，要重新審視這類查詢。

    • 放置在SELECT語句的SELECT關鍵字之後：讓查詢中所有的表按照在語句中出現的順序進行關聯
    • 放置在任何兩個關聯表的名字之間：固定其前後兩個表的關聯順序。

  • SQL_SMALL_RESULT和SQL_BIG_RESULT：

    • 只對SELECT語句有效，告訴優化器對GROUP BY或者DISTINCT查詢如何使用臨時表和排序。
    • SQL_SMALL_RESULT告訴優化器結果集很小，可以將結果集放在記憶體中的索引臨時表，避免排序操作
    • SQL_BIG_RESULT告訴優化器結果集可能會非常大，建議使用磁碟臨時表做排序操作

  • SQL_BUFFER_RESULT：

    • 告訴優化器將查詢結果放入到一個臨時表，然後儘可能地釋放表鎖。

  • SQL_CACHE和SQL_NO_CACHE

    • 告訴MySQL這個結果集是否應該快取在查詢快取中

  • SQL_CALC_FOUND_ROWS：

    • 嚴格來說，並不是一個優化器提示，它不會告訴優化器任何關於執行計劃的東西。
    • 讓MySQL返回的結果集包含更多資訊。
    • 查詢中加上該提示MySQL會計算除去LIMIT子句後這個查詢要返回的結果集的總數，而實際上只返回LIMIT要求的結果集

  • FOR UPDATE和LOCK IN SHARE MODE

    • 也不是真正的優化器提示。
    • 主要控制SELECT語句的鎖機制，但只對實現了行級鎖的儲存引擎有效。
    • 會對符合查詢條件的資料行加鎖。對於INSERT...SELECT語句是不需要這兩個提示的，因為5.0及新版本會預設加鎖。
    • InnoDB是唯一內建支援這兩個提示的引擎。但是這兩個提示會讓某些優化無法正常使用，例如索引覆蓋掃描。InnoDB不能在不訪問主鍵的情況下排他地鎖定行，因為行的版本資訊儲存在主鍵中。
    • 這兩個提示經常被濫用，很容易造成伺服器的鎖爭用問題，應該儘可能避免使用。

  • USING INDEX、IGONRE INDEX和FORCE INDEX：

    • 告訴優化器使用或不使用哪些索引來查詢記錄(例如，在決定關聯順序的時候使用哪個索引)。
    • 5.1及新版本可以通過FOR ORDER BY和FOR GROUP BY來制定是否對排序和分組有效。
    • USING INDEX和FORCE INDEX基本相同。但是FORCE INDEX會告訴優化器全表掃描的成本會遠遠高於索引掃描，哪怕實際上該索引的用處不大。當發現優化器選擇了錯誤的索引，或者因為某些原因(比如在不適用ORDER BY的時候希望結果有序)要使用另一個索引時，可以使用該提示。

• 5.0和更新版本新增用來控制優化器行為的引數：

  • optimizer_search_depth:控制優化器在窮舉執行計劃時的限度。如果查詢長時間處於"Statistics"狀態，可以考慮調低
  • optimizer_prune_level:預設開啟，讓優化器根據需要掃描的行數來決定是否跳過某些執行計劃
  • optimizer_swith:包含了一些開啟/關閉優化器特性的標誌位。

4.7 優化特定型別的查詢

4.7.1 優化count()查詢

• count()的作用：

  • 統計某個列值的數量，要求列值是非空的(不統計NULL)。如果在COUNT()的括號中指定了列或者列的表示式，則統計的就是這個表示式有值的結果數(而不是NULL)
  • 統計行數。當MySQL確認括號內的表示式值不可能為空時，實際上就是統計行數。
    • 當使用COUNT()時，並不會像我們猜想的擴充套件成所有的列，實際上，它會忽略所有的列而直接統計所有的行數。
    • 常見錯誤：在括號內指定了一個列卻希望統計結果集的行數。

• 關於MyISAM的神話：

  • 只有沒有任何WHERE條件的count(*)，MyISAM的COUNT()函式才會非常快，因為此時無須實際地去計算表的行數。MySQL可以利用儲存引擎的特性直接獲得這個值。
  • 如果MySQL知道某列col不可能為NULL值，那麼內部會把count(col)優化成count(*)
  • 當統計帶有WHERE子句，MyISAM的COUNT()和其他儲存引擎沒有任何不同，就不會再有神話般的速度。

• 簡單的優化

  • 利用MyISAM在count(*)全表非常快的特性，來加速一些特定條件的查詢。

    -- 使用標準資料據worold
    SELECT count(*) FROM world.city WHERE ID > 5;
    -- 將條件反轉，可很大程度減少掃描行數到5行以內
    SELECT (SELECT count(*) FROM world.city) - COUNT(*) 
    FROM world.city WHERE ID <= 5;複製程式碼

  • 示例：假設可能需要通過一個查詢返回各種不同顏色的商品數量

    -- 使用SUM
    SELECT SUM(IF(color = 'blue', 1, 0)) AS blue,SUM(IF(color = 'red', 1, 0)) AS red FROM items;
    -- 使用COUNT，只需要將滿足條件的設定為真，不滿足設定為NULL
    SELECT COUNT(color = 'blue' OR NULL) AS blue, COUNT(color = 'red' OR NULLASred FROM items;複製程式碼

• 使用近似值：

  • 有時候某些業務場景並不要求完全精確的COUNT值，此時可以用近似值來代替。
  • EXPLAIN出來的優化器估算的行數就是一個不錯的近似值，執行EXPLAIN並不需要真正地去執行查詢，所以成本很低。
  • 例如：如果要精確統計網站線上人數，通常WHERE條件會很複雜，一方面需要過濾當前非活躍使用者，另一方面還需要過濾系統中某些特定ID的預設使用者，去掉這些約束條件對總數的影響很小，但卻可能很好地提高該查詢的效能。更進一步優化則可以嘗試刪除DISTINCT這樣的約束來避免檔案排序。這樣重寫的查詢比精確查詢要快得多，而返回的結果則幾乎相同。

• 更復雜的優化：

  • 通常來說，COUNT()都需要掃描大量的行(意味著要訪問大量資料)才能獲得精確的結果，因此是很難優化的。
  • 優化方法：
    • 前面提到的方法
    • 在MySQL層面能做的只有索引覆蓋掃描
    • 考慮修改應用架構，可以增加彙總表，或者類似Memcached這樣的外部快取系統。可能很快你就會發現陷入到一個熟悉的困境，”快速、精確和實現簡單“，三者永遠只能滿足其二，必須舍掉其中一個。

  4.7.2 優化關聯查詢

  • 確保ON或者USING子句的列上有索引。
    • 在建立索引的時候就需要考慮關聯的順序。當表A和表B用列c關聯的時候，入股優化器的關聯順序是B、A，那麼就不需要在B表的對應列上建立索引。沒有的索引智慧帶來額外的負擔。
    • 一般來說，除非有其他理由，否則只需要在關聯順序的第二個表的響應列上建立索引。
  • 確保任何的GROUP BY和ORDER BY中的表示式只涉及到一個表中的列，這樣MySQL才有可能使用索引來優化這個過程。
  • 當升級MySQL的時候需要注意：關聯語法、運算子優先順序等其他可能會發生變化的地方。因為以前是普通關聯的地方可能會變成笛卡兒積，不同型別的關聯可能會生成不同的結果。

4.7.3 優化子查詢

• 儘可能使用關聯查詢代替。這並不是絕對的，5.6及新版本或者MariaDB，可以直接使用子查詢。

4.7.4 優化GROUP BY和DISTINCT

• 在很多場景下，MySQL都使用同樣的辦法優化這兩種查詢，事實上，MySQL優化器會在內部處理的時候相互轉換這兩類查詢。它們都可以使用索引來優化，這也是最有效的優化辦法。
• 當無法使用索引時，GROUP BY使用兩種策略：使用臨時表或者檔案排序來做分組。可以通過提示SQL_BIG_RESULT和SQL_SMALL_RESULT來讓優化器按照你希望的方式執行。
• 如果需要對關聯查詢做分組GROUP BY，並且是按照查詢表中的某個列進行分組，那麼通常採用查詢表的標識列分組的效率會比其他列更高
  • SELECT後面出現的非分組列一定是直接依賴於分組列，並且在每個組內的值是唯一的，或者是業務上根本不在乎這個值具體是什麼。
  • 在分組查詢的SELECT中直接使用非分組列通常不是什麼好主意，因為這樣的結果通常是不定的，當索引改變，或者優化器選擇不同的優化策略時都可能導致結果不一樣。
• 如果沒有通過ORDER BY子句顯示地制定排序列，當查詢使用GROUP BY子句的時候，結果集會自動按照分組的欄位進行排序。如果不關心結果集的排序，而這種預設排序又導致了需要檔案排序，則可以使用ORDER BY NULL，讓MySQL不再進行檔案排序。
• 優化GROUP BY WITH ROLLUP：
  • 分組查詢的一個變種就按要求MySQL對返回的分組結果在做一次超級聚合。可以使用WITH ROLLUP子句來實現這種邏輯，但可能會不夠優化。
    • 很多時候，如果可以在應用程式中做超級聚合是更好的，雖然這需要返回給客戶端更多的結果。
    • 也可以在FROM子句中巢狀使用子查詢，或者是通過一個臨時表存放中間資料，然後和臨時表執行UNION來得到最終結果
    • 最好的辦法是儘可能將WITH ROLLUP功能轉移到應用程式中處理。

4.7.5 優化LIMIT分頁

• 在系統中需要進行分頁操作的時候，通常會使用LIMIT加上偏移量的辦法來實現，同時加上合適的ORDER BY子句。如果有對應的索引，通常效率會很不錯，否則，MySQL需要做大量的檔案排序操作。
• 偏移量非常大的時候優化辦法：
  • 在頁面中限制分頁的數量
  • 優化大偏移量的效能：
    • 儘可能使用覆蓋索引掃描，而不是查詢所有的列。例如使用延遲關聯
    • 有時可以將LIMIT查詢轉換為已知位置的查詢，讓MySQL通過範圍掃描獲得對應的結果。
    • 使用”書籤”記錄上一次取資料的位置。
    • 使用預先計算的彙總表，或者關聯到一個冗餘表，冗餘表只包含主鍵列和需要做排序的資料列。

4.7.6 優化SQL_CALC_FOUND_ROWS

分頁的時候，另一個常用的技巧是在LIMIT語句中加上SQL_CALC_FOUND_ROWS提示，這樣就可以獲得去掉LIMIT以後滿足條件的行數，因此可以作為分頁的總數。加上這個提示後，MySQL不管是否需要都會掃描所有滿足條件的行，然後拋棄掉不需要的行，而不是在滿足LIMIT的行數後就終止掃描。所以該提示的代價可能非常高。

• 設計方案1：將具體的頁數換成“下一頁”按鈕，假設每頁顯示20條記錄，那麼使用LIMIT返回21條並只顯示20條，如果第21條存在，那麼顯示“下一頁”按鈕，否則說明沒有更多的資料，也就無須顯示
• 設計方案2：先獲取並快取較多的資料，然後每次分頁都從這個快取中獲取。
• 其他設計方案：有時候也可以考慮使用EXPLAIN的結果中的rows列的值來作為結果集總數的近似值(實際上Google的搜尋結果總數也是個近似值)。當需要精確結果時，再單獨使用COUNT(*)來滿足需求，這時候如果能夠使用覆蓋索引則通常也會比SQL_CALC_FOUND_ROWS快得多。

4.7.7 優化UNION查詢

• MySQL總是通過建立並填充臨時表的方式來執行UNION查詢。因此很多優化策略都沒法很好的使用。經常需要手動地將WHERE、LIMIT、ORDER BY子句下推UNION的各個子查詢中，以便優化器可以充分利用這些條件進行優化
• 除非確實需要伺服器消除重複的行，否則就一定要使用UNION ALL。
  • 如果沒有ALL關鍵字，MySQL會給臨時表加上DISTINCT選項，這會導致對整個臨時表做唯一性檢查，這樣做的代價非常高。
  • 即使有ALL關鍵字，MySQL仍然會使用臨時表儲存結果。

4.7.8 靜態查詢分析

Percona Toolkit contains pt-query-advisor, a tool that parses a log of queries, analyzes
the query patterns, and gives annoyingly detailed advice about potentially bad practices
in them.

4.7.9 使用使用者自定義變數

• 使用者自定義變數是一個用來儲存內容的臨時容器，在連線MySQL的整個過程中都存在。在查詢中混合使用過程化和關係化邏輯的時候，該特性非常有用。

• 使用方法：

  SET @one       := 1;
  SET @min_actor := (SELECT MIN(actor_id) FROM sakila.actor);
  SET @last_week := CURRENT_DATE - INTERVAL 1 WEEK;
  SELECT ... WHERE col <= @last_week;
  -- 具有“左值”特性，在給一個變數賦值的同時使用這個變數
  SELECT actor_id, @rownum := @rownum + 1 As rownum ...複製程式碼

• 無法使用的場景：

  • 使用自定義變數的查詢，無法使用查詢快取
  • 不能在使用常量或者識別符號的地方使用自定義變數，例如表名、列名和LIMIT子句中。
  • 使用者自定義變數的生命週期是在一個連線中有效，所以不能用它們來做連線中的通訊。
  • 如果使用連線池或者持久化連線，自定義變數可能讓看起來毫無關係的程式碼互動(如果是這樣，通常是程式碼或連線池bug)
  • 在5.0之前的版本，是大小寫敏感的，所以要注意程式碼在不同MySQL版本間的相容性問題。
  • 不能顯示地宣告自定義變數的型別。確定未定義變數的具體型別的時機在不同MySQL版本中也可能不一樣。如果希望是整形/浮點/字串，最好初始化時0/0.0/‘’。MySQL的使用者自定義變數是一個動態型別，在賦值的時候會改變。
  • MySQL優化器在某些場景下可能會將這些變數優化掉，這可能導致程式碼不按預想的方式執行。
  • 賦值的順序和賦值的時間點並不總是固定的，這依賴於優化器的決定。實際情況可能讓人困惑。
  • 賦值符號:=的優先順序非常低，所以要注意，賦值表示式應該使用明確的括號。
  • 使用未定義變數不會產生任何語法錯誤，如果沒注意到這一點，非常容易犯錯。

• 應用場景：

  • 優化排名語句：

    -- 查詢獲取演過最多電影的前10位演員，然後根據出演電影次數做一個排名，如果出演次數一樣，則排名相同。
    mysql> SET @curr_cnt := 0, @prev_cnt := 0, @rank := 0;
    -> SELECT actor_id,
    -> @curr_cnt := cnt AS cnt,
    -> @rank := IF(@prev_cnt <> @curr_cnt, @rank + 1, @rank) AS rank,
    -> @prev_cnt := @curr_cnt AS dummy
    -> FROM (
    -> SELECT actor_id, COUNT(*) AS cnt
    -> FROM sakila.film_actor
    -> GROUP BY actor_id
    -> ORDER BY cnt DESC
    -> LIMIT 10
    -> ) as der;複製程式碼

  • 避免重複查詢剛剛更新的資料：

    -- 在更新行的同時又希望獲取得到該行的資訊。雖然看起來仍然需要兩個查詢和兩次網路來回，但第二個查詢無須訪問任何資料表，速度會快很多
    UPDATE t1 SET lastUpdated = NOW() WHERE id = 1 AND @now := NOW();
    SELECT @now;複製程式碼

  • 統計更新和插入的數量

    -- 使用了INSERT ON DUPLICATE KEY UPDATE的時候，想統計插入了多少行的資料，並且有多少資料是因為衝突而改寫成更新操作。
    -- 實現該辦法的本質如下，當每次由於衝突導致更新時對變數@x自增一次，然後通過對這個表示式乘以0來讓其不影響要更新的內容
    INSERT INTO t1(c1, c2) VALUES(4, 4), (2, 1), (3, 1)
    ON DUPLICATE KEY UPDATE
    c1 = VALUES(c1) + ( 0 * ( @x := @x +1 ) );複製程式碼

  • 確定取值的順序

    • 一個最常見的問題，沒有注意到在賦值和讀取變數的使用可能是在查詢的不同階段。

      -- WHERE和SELECT是在查詢執行的不同階段被執行的，而WHERE是在ORDER BY檔案排序操作之前執行。
      mysql> SET @rownum := 0;
      mysql> SELECT actor_id, @rownum := @rownum + 1 AS cnt
      -> FROM sakila.actor
      -> WHERE @rownum <= 1;
      +----------+------+
      | actor_id | cnt  |
      +----------+------+
      | 1        | 1    |
      | 2        | 2    |
      +----------+------+複製程式碼

    • 儘量讓變數的賦值和取值發生在執行查詢的同一個階段。

      mysql> SET @rownum := 0;
      mysql> SELECT actor_id, @rownum AS rownum
      -> FROM sakila.actor
      -> WHERE (@rownum := @rownum + 1) <= 1;複製程式碼

    • 將賦值運距放到LEAST()，這樣就可以完全不改變排序順序的時候完成賦值操作。這個技巧在不希望對子句的執行結果有影響卻又要完成變數複製的時候很有用。這樣的函式還有GREATEST(), LENGTH(), ISNULL(), NULLIF(), IF(), 和COALESCE()。

      -- LEAST()總是返回0
      mysql> SET @rownum := 0;
      mysql> SELECT actor_id, first_name, @rownum AS rownum
      -> FROM sakila.actor
      -> WHERE @rownum <= 1
      -> ORDER BY first_name, LEAST(0, @rownum := @rownum + 1);複製程式碼

• 編寫偷懶的UNION:

  假設需要編寫一個UNION查詢，其第一個子查詢作為分支條件先執行，如果找到了匹配的行，則跳過第二個分支。在某些業務場景中確實會有這樣的需求，比如現在一個頻繁訪問的表中查詢“熱”資料，找不到再去另外一個較少訪問的表中查詢“冷資料“。(區分熱冷資料是一個很好提高快取命中率的辦法)。

  -- 在兩個地方查詢一個使用者，一個主使用者表，一個長時間不活躍的使用者表，不活躍的使用者表的目的是為了實現更高效的歸檔。
  -- 舊的UNION查詢，即使在users表中已經找到了記錄，上面的查詢還是會去歸檔表中再查詢一次。
  SELECT id FROM users WHERE id = 123
  UNION ALL
  SELECT id FROM users_archived WHERE id = 123;
  -- 用一個偷懶的UINON查詢來抑制這樣的資料返回，當第一個表中沒有資料時，我們才在第二個表中查詢。一旦在第一個表中找到記錄，就定義一個變數@found，通過在結果列中做一次賦值來實現，然後將賦值放在函式GREATEST中來避免返回額外的資料。為了明確結果來自哪一個表，新增了一個包含表名的列。最後需要在查詢的末尾將變數重置為NULL，保證遍歷時不干擾後面的結果。
  SELECT GREATEST(@found := −1, id) AS id, 'users' AS which_tbl
  FROM users WHERE id = 1
  UNION ALL
  SELECT id, 'users_archived'
  FROM users_archived WHERE id = 1 AND @found IS NULL
  UNION ALL
  SELECT 1, 'reset' FROM DUAL WHERE ( @found := NULL ) IS NOT NULL;複製程式碼

• 使用者自定義變數的其他用處：

  • 不僅是在SELECT語句中，在其他任何型別的SQL語句中都可以對變數進行賦值。例如，可以像前面使用子查詢的方式改進排名語句一樣來改進UPDATE語句。
  • 有時需要使用一些技巧來獲得希望的結果。因為優化器可能會把變數當作一個編譯時常量來對待，而不是對其進行賦值。將函式放在類似LEAST()這樣的函式中通常可以避免這樣的問題。另一個辦法是在查詢被執行前檢查變數是否被賦值。

• 其他用法：

  • 查詢執行時計算總數和平均值
  • 模擬GROUP語句中的函式FIRST()和LAST()
  • 對大量資料做一些資料計算。
  • 計算一個大表的MD5雜湊值
  • 編寫一個樣本處理函式，當樣本中的數值超過某個邊界值的時候將其變為0
  • 模擬讀/寫遊標
  • 在SHOW語句的WEHRE子句中加入變數值。

4.8 案例學習

4.8.1 使用MySQL構建一個佇列表

使用MySQL來實現對列表是一個取巧的做法，很多系統在高流量、高併發的情況下表現並不好。典型的模式是一個表包含多種型別的記錄：未處理記錄、已處理記錄、正在處理的記錄等等。一個或者多個消費者執行緒在表中查詢未處理的記錄，然後聲稱正在處理，當處理完成後，再將記錄更新為已處理狀態。一般的，例如郵件傳送、多命令處理、評論修改等會使用類似模式，但

原有處理方式不合適的原因：

• 隨著對列表越來越大和索引深度的增加，找到未處理記錄的速度會隨之變慢。
• 一般的處理過程分兩步，先找到未處理的記錄然後加鎖。找到記錄會增加伺服器的壓力，而加鎖操作則會讓各個消費者程式增加競爭，因為這是一個序列化操作。

優化過程：

• 將對列表分成兩部分，即將已處理記錄歸檔或者存放到歷史表，這樣始終保證對列表很小。

• 找到未處理記錄一般來說都沒問題，如果有問題則可以通過使用訊息方式來通知各個消費者。

  • 可已使用一個帶有註釋的SLEEP()函式做超時處理。這讓執行緒一直阻塞，直到超時或者另一個執行緒使用KILL QUERY結束當前的SLEEP。因此，當再向對列表中新增一批資料後，可以通過SHOW PROCESSLIST，根據註釋找到當前正在休眠操作的執行緒，並將其KILL。可以使用函式GET_LOCK和RELEASE_LOCK()來實現通知，或者可以在資料庫之外實現，如使用一個訊息服務。

    SELECT /* waiting on unsent_emails */ SLEEP(10000), col1 FROM table;複製程式碼

• 最後一個問題是如何讓消費者標記正在處理的記錄，而不至於讓多個消費者重複處理一個記錄。

  • 儘量避免使用SELECT FOR UPDATE，這通常是擴充套件性問題的根源，這會導致大量的書屋阻塞並等待。不光是佇列表，任何情況下都要避免。

  • 可以直接使用UPDATE來更新記錄，然後檢查是否還有其他的記錄需要處理。(所有的SELECT FOR UPDATE都可以使用類似的方式改寫)

    -- 該表的owner用來儲存當前正在處理這個記錄的連線ID，即由函式CONNECTION_ID()返回額ID，如果當前記錄沒有被任何消費者處理，則該值為0
    CREATE TABLE unsent_emails (
        id INT NOT NULL PRIMARY KEY AUTO_INCREMENT,
        -- columns for the message, from, to, subject, etc.
        status ENUM('unsent', 'claimed', 'sent'),
        owner INT UNSIGNED NOT NULL DEFAULT 0,
        ts TIMESTAMP,
        KEY (owner, status, ts)
    );
    -- 常見的處理辦法。這裡的SELECT查詢使用到索引的兩個列，理論上查詢的效率應該更快。問題是，兩個查詢之間的“間隙時間”，這裡的鎖會讓所有其他同一的查詢全部被阻塞。所有這樣的查詢將使用相同的索引，掃描索引相同結果的部分，所以很可能被阻塞。
    BEGIN;
    SELECT id FROM unsent_emails
        WHERE owner = 0 AND status = 'unsent'
        LIMIT 10 FOR UPDATE;
    -- result: 123, 456, 789
    UPDATE unsent_emails
        SET status = 'claimed', owner = CONNECTION_ID()
        WHERE id IN(123, 456, 789);
    COMMIT;
    -- 改進後更高效的寫法,無須使用SELECT查詢去找到哪些記錄還沒有被處理。客戶端的協議會告訴你更新了幾條記錄，所以可以直到這次需要處理多少條記錄。
    SET AUTOCOMMIT = 1;
    COMMIT;
    UPDATE unsent_emails
        SET status = 'claimed', owner = CONNECTION_ID()
        WHERE owner = 0 AND status = 'unsent'
        LIMIT 10;
    SET AUTOCOMMIT = 0;
    SELECT id FROM unsent_emails
        WHERE owner = CONNECTION_ID() AND status = 'claimed';
    -- result: 123, 456, 789複製程式碼

• 最後還需處理一種特殊情況：那些正在被程式處理，而程式本身卻由於某種原因退出的情況。

  • 只需要定期執行UPDATE語句將它都更新成原始狀態，然後執行SHOW PROCESSLIST，獲取當前正在工作的執行緒ID，並使用一些WHERE條件避免取到那些剛開始處理的程式

    -- 假設獲取的執行緒ID有(10、20、30),下面的更新語句會將處理時間超過10分鐘的記錄狀態更新成初始狀態。
    -- 將範圍條件放在WHERE條件的末尾，這個查詢恰好能勾使用索引的全部列，其它的查詢也都能使用上這個索引，這樣就避免了再新增一個額外的索引來滿足其它的查詢
    UPDATE unsent_emails
        SET owner = 0, status = 'unsent'
      WHERE owner NOT IN(0, 10, 20, 30) AND status = 'cla
        AND ts < CURRENT_TIMESTAMP - INTERVAL 10 MINUTE;複製程式碼

該案例中的一些基礎原則：

• 儘量少做事，可以的話就不要做任何事。除非不得已，否則不要使用輪詢，因為這會增加負載，而且還會帶來很多低產出的工作。
• 儘可能快地完成需要做的事情。儘量使用UPDATE代替先SELECT FOR UPDATE再UPDATE的寫法，因為事務的提交的速度越快，持有的鎖時間就越短，可以大大減少競爭和加速序列執行效率。將已經處理完成和未處理的資料分開，保證資料集足夠小。
• 這個案例的另一個啟發是，某些查詢是無法優化的；考慮使用不同的查詢或者不同的策略去實現相同的目的。通常對於SELECT FOR UPDATE就需要這樣的處理

有時，最好的辦法就是將任務佇列從資料庫中遷移出來，Redis和memcached就是一個很好的佇列容器。

6.8.2 計算兩點之間的距離

不建議使用MySQL做太複雜的空間計算儲存，PostgreSQL在這方面是一個不錯的選擇。一個典型的例子是計算以某個點為中心，一定半徑內的所有點。例如查詢某個點附近所有可以出租的房子，或者社交網站中”匹配“附近的使用者。

假設我們有如下表，這裡經度和緯度的單位都是度：

CREATE TABLE locations (
  id INT NOT NULL PRIMARY KEY AUTO_INCREMENT,
  name VARCHAR(30),
  lat FLOAT NOT NULL,
  lon FLOAT NOT NULL
);
INSERT INTO locations(name, lat, lon)
  VALUES('Charlottesville, Virginia', 38.03, −78.48),
  ('Chicago, Illinois', 41.85, −87.65),
  ('Washington, DC', 38.89, −77.04);複製程式碼

假設地球是圓的，然後使用兩點所在最大圓(半正矢)公式來計算兩點之間的距離。現有座標latA和lonA、latB和lonB，那麼點A和點B的距離計算公式如下:

ACOS(
COS(latA) * COS(latB) * COS(lonA - lonB)
+ SIN(latA) * SIN(latB)
)複製程式碼

計算的結果是一個弧度，如果要將結果轉換成英里或千米，則需要乘以地球的半徑。

SELECT * FROM locations WHERE 3979 * ACOS(
  COS(RADIANS(lat)) * COS(RADIANS(38.03)) * COS(RADIANS(lon) - RADIANS(-78.48))
  + SIN(RADIANS(lat)) * SIN(RADIANS(38.03))
) <= 100;複製程式碼

這類查詢不僅無法使用索引，而且還會非常消耗CPU時間，給伺服器帶來很大的壓力，而且還得反覆計算。

優化地方：

• 看看是否真的需要這麼精確的計算。其實該演算法已經有很多不精確的地方：

  • 直線距離可能是100英里，但實際上它們之間的行走距離很可能不是這個值。
  • 如果根據郵政編碼來確定某個人所在的地區，再根據這個地區的中心位置計算他和別人的距離，這本身就是一個估算。

• 如果不需要太高的精度，可以認為地球是圓的。要想有更多的優化，可以將三角函式的計算放到應用中，而不要在資料庫中計算。

• 看看是否真需要計算一個圓周，可以考慮直接使用一個正方形代替。邊長為200英里的正方形，一個頂點到中心的距離大概是141英里，這和實際計算的100英里相差並不太遠。根據正方形公式來計算弧度為0.0253(100英里)的中心到邊長的距離：

  SELECT * FROM locations
    WHERE lat BETWEEN 38.03 - DEGREES(0.0253) AND 38.03 + DEGREES(0.0253)
    AND lon BETWEEN −78.48 - DEGREES(0.0253) AND −78.48 + DEGREES(0.0253);複製程式碼

現在看看如何用索引來優化這個查詢：

• 增加索引(lat, lon)，這樣做的效果並不會很好，因為使用了範圍查詢。
• 使用IN()優化。

新增兩個列，用來儲存座標的近似值FLOOR()，然後在查詢中使用IN()將所有點的整數值都放到列表中:

mysql> ALTER TABLE locations
-> ADD lat_floor INT NOT NULL DEFAULT 0,
-> ADD lon_floor INT NOT NULL DEFAULT 0,
-> ADD KEY(lat_floor, lon_floor);複製程式碼

現在可以根據座標的一定範圍的近似值來搜尋，這個近似值包括地板值和天花板值，地理上分別對應的是南北:

-- 查詢某個範圍的所有點，數值需要在應用程式中計算而不是MySQL
mysql> SELECT FLOOR( 38.03 - DEGREES(0.0253)) AS lat_lb,
-> CEILING( 38.03 + DEGREES(0.0253)) AS lat_ub,
-> FLOOR(-78.48 - DEGREES(0.0253)) AS lon_lb,
-> CEILING(-78.48 + DEGREES(0.0253)) AS lon_ub;
+--------+--------+--------+--------+
| lat_lb | lat_ub | lon_lb | lon_ub |
+--------+--------+--------+--------+
| 36     | 40     | −80    | −77    |
+--------+--------+--------+--------+
-- 生成IN()列表中的整數：
SELECT * FROM locations
  WHERE lat BETWEEN 38.03 - DEGREES(0.0253) AND 38.03 + DEGREES(0.0253)
  AND lon BETWEEN −78.48 - DEGREES(0.0253) AND −78.48 + DEGREES(0.0253)
  AND lat_floor IN(36,37,38,39,40) AND lon_floor IN(-80,-79,-78,-77);複製程式碼

使用近似值會讓我們的計算結果有偏差，所以我們還需要一些額外的條件過濾在正方形之外的點，這和前面使用CRC32做雜湊索引類似：先建一個索引過濾出近似值，在使用精確條件匹配所有的記錄並移除不滿足條件的記錄。

事實上，到這時就無須根據正方形的近似來過濾資料，可以使用最大圓公式或者畢達哥拉斯定理來計算：

SELECT * FROM locations
  WHERE lat_floor IN(36,37,38,39,40) AND lon_floor IN(-80,-79,-78,-77)
  AND 3979 * ACOS(
  COS(RADIANS(lat)) * COS(RADIANS(38.03)) * COS(RADIANS(lon) - RADIANS(-78.48))
  + SIN(RADIANS(lat)) * SIN(RADIANS(38.03))
) <= 100;複製程式碼

這時計算精度再次回到使用一個精確的圓周，不過現在的做法更快。只要能夠高效地過濾掉大部分的點，例如使用近似整數和索引，之後再做精確數學計算的代價並不大。只要不是使用大圓周的演算法，否則速度會更慢。

該案例使用的優化策略:

• 儘量少做事，可能的話儘量不做事。這個案例中就不要對所有的點計算大圓周公式；先使用簡單的方案過濾大多數資料，然後再到過濾出來的更小的集合上使用複雜的公式運算。
• 快速地完成事情。確保在設計中儘可能地讓查詢都用上合適的索引，使用近似計算來避免複雜計算。
• 需要的時候，儘可能讓應用程式完成一些計算。

4.8.3 使用使用者自定義函式

• 當SQL語句已經無法高效地完成某些任務的時候，如果需要更快的速度，那麼C和C++是很好的選擇。
• 案例：需要根據兩個隨機的64位數字計算它們的XOR值，來看這兩個數值是否匹配。大約有3500萬條的記錄需要在秒級中完成。
  • 經過簡單的計算就知道，當前的硬體條件下，不可能在MySQL中完成。
  • 編寫一個計算程式，以一個後臺程式的方式執行在同一伺服器上，然後編寫一個使用者自定義函式，通過簡單的網路通訊協議和前面的程式進行互動。分散式執行該程式，可以達到在130毫秒內完成4百萬次匹配計算。通過這樣的方式，可以將密集型的計算放到一些通用伺服器上，同時對外界完全透明，看起來是MySQL完成了全部的工作。

4.9 總結

如果把建立高效能應用程式比作是一個環環相扣的”難題“，除了前面介紹的schema、索引和查詢語句設計之外，查詢優化應該是解開”難題“的最後一步。

理解查詢是如何被執行的以及時間都消耗在哪些地方，這依然是前面介紹的響應時間的一部分。再加上一些諸如解析和優化過程的知識，就可以額更進一步地理解上一章討論的MySQL如何訪問表和索引的內容了。這也從另一個維度理解MySQL在訪問表和索引時查詢和索引的關係。

優化通常需要三管齊下：不做、少做、快速地做。