高效能的Mysql讀書筆記系列之六（查詢效能優化）

前言：

即使設計了最優的庫表結構、建立了最好的索引，如果沒有的合理的查詢sql，也是無法實現高效能的。所以除了對索引優化、庫表結構優化，查詢優化也是需要齊頭並進的。

正文：

一、為什麼查詢速度會變慢

在嘗試編寫快速的查詢之前，需要清楚一點，真正重要是響應時間。如果把查詢看作是一個任務，那麼它由一系列子任務組成，每個子任務都會消耗一定的時間。如果要優化查詢，實際上要優化其子任務，要麼消除其中一些子任務，要麼減少子任務的執行次數，要麼讓子任務執行得更快。

MySQL在執行查詢的時候有哪些子任務，哪些子任務執行的速度很慢？通常來說，查詢的生命週期大致可以按照順序來看：從客戶端，到伺服器，然後在伺服器上進行解析，生成執行計劃，執行，並返回結果給客戶端。其中“執行”可以認為是整個生命週期中最重要的階段，這其中包括了大量為了檢索資料到儲存引擎的呼叫以及呼叫後的資料處理，包括排序、分組等。

在完成這些任務的時候，查詢需要在不同的地方花費時間，包括網路，CPU計算，生成統計資訊和執行計劃、鎖等待（互斥等待）等操作，尤其是向底層儲存引擎檢索資料的呼叫操作，這些呼叫需要在記憶體操作、CPU操作和記憶體不足時導致的I/O操作上消耗時間。根據儲存引擎不同，可能還會產生大量的上下文切換以及系統呼叫。

在每一個消耗大量時間的查詢案例中，我們都能看到一些不必要的額外操作、某些操作被額外地重複了很多次、某些操作執行得太慢等。優化查詢的目的就是減少和消除這些操作所花費的時間。

再次申明一點，對於一個查詢的全部生命週期，上面列的並不完整。這裡我們只是想說明：瞭解查詢的生命週期、清楚查詢的時間消耗情況對於優化查詢有很大的意義。有了這些概念，我們再一起來看看如何優化查詢。

二、MySQL執行一個查詢的過程是怎麼樣的？MySQL到底做了什麼？

1.客戶端傳送一條查詢給伺服器。

2.伺服器先檢查查詢快取，如果命中了快取，則立刻返回儲存在快取中的結果。否則進入下一階段。

3.伺服器端進行SQL解析、預處理，再由優化器生成對應的執行計劃。

4.MySQl根據優化器生成的執行計劃，呼叫儲存引擎的API來執行查詢。

5.將結果返回給客戶端。 

三、優化查詢可以從三個角度出發

1.優化資料訪問

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

查詢不需要的記錄
       一個常見的錯誤是常常會誤以為MySQL會只返回需要的資料，實際上MySQL卻是先返回全部結果集再進行計算。我們經常會看到一些瞭解其他資料庫系統的人會設計出這類應用程式。這些開發者習慣使用這樣的技術，先使用SELECT語句查詢大量的結果，然後獲取前面的N行後關閉結果集（例如在新聞網站中取出100條記錄，但是隻是在頁面上顯示前面10條）。他們認為MySQL會執行查詢，並只返回他們需要的10條資料，然後停止查詢。實際情況是MySQL會查詢出全部的結果集，客戶端的應用程式會接收全部的結果集資料，然後拋棄其中大部分資料。最簡單有效的解決方法就是在這樣的查詢後面加上LIMIT。
多表關聯時返回全部列
      如果你想查詢所有在電影Academy Dinosaur中出現的演員，千萬不要按下面的寫法編寫查詢：

     mysql> SELECT * FROM sakila.actor
       -> INNER JOIN sakila.film_actor USING(actor_id)
       -> INNER JOIN sakila.film USING(film_id)
       -> WHERE sakila.film.title = 'Academy Dinosaur';   

     這將返回這三個表的全部資料列。正確的方式應該是像下面這樣只取需要的列：

    mysql> SELECT sakila.actor.* FROM sakila.actor...;

總是取出全部列
       每次看到SELECT *的時候都需要用懷疑的眼光審視，是不是真的需要返回全部的列？很可能不是必需的。取出全部列，會讓優化器無法完成索引覆蓋掃描這類優化，還會為伺服器帶來額外的I/O、記憶體和CPU的消耗。因此，一些DBA是嚴格禁止SELECT *的寫法的，這樣做有時候還能避免某些列被修改帶來的問題。
當然，查詢返回超過需要的資料也不總是壞事。在我們研究過的許多案例中，人們會告訴我們說這種有點浪費資料庫資源的方式可以簡化開發，因為能提高相同程式碼片段的複用性，如果清楚這樣做的效能影響，那麼這種做法也是值得考慮的。如果應用程式使用了某種快取機制，或者有其他考慮，獲取超過需要的資料也可能有其好處，但不要忘記這樣做的代價是什麼。獲取並快取所有的列的查詢，相比多個獨立的只獲取部分列的查詢可能就更有好處。
重複查詢相同的資料
        如果你不太小心，很容易出現這樣的錯誤——不斷地重複執行相同的查詢，然後每次都返回完全相同的資料。例如，在使用者評論的地方需要查詢使用者頭像的URL，那麼使用者多次評論的時候，可能就會反覆查詢這個資料。比較好的方案是，當初次查詢的時候將這個資料快取起來，需要的時候從快取中取出，這樣效能顯然會更好。

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

在確定查詢只返回需要的資料以後，接下來應該看看查詢為了返回結果是否掃描了過多的資料。對於MySQL，最簡單的衡量查詢開銷的三個指標如下：

• 響應時間
• 掃描的行數
• 返回的行數

在EXPLAIN語句中的type列反應了訪問的型別。訪問型別有很多種，從全表掃描到索引掃描、範圍掃描、唯一索引查詢、常數引用等。這裡列的這些，速度是從慢到快，掃描的行數也是小到大。你不需要記住這些訪問型別，但是要明白掃描表，掃描索引，範圍訪問和單值訪問的概念。如果查詢沒有辦法找到合適的訪問型別，那麼最好的辦法通常就是增加一個合適的索引。

一般MySQL能夠使用如下三種應用WHERE條件，從好到壞依次為：

• 在索引中使用WHERE條件來過濾不匹配的記錄。這是在儲存引擎層完成的。
• 使用索引覆蓋掃描來返回記錄，直接從索引中過濾不需要的記錄並返回命中的結果。這是在MySQL伺服器層完成的，但無須在回表查詢記錄。
• 從資料表中返回資料，然後過濾不滿足條件的記錄。這是在MySQL伺服器層完成，MySQL需要先從資料表讀出記錄然後過濾。

如果說發現查詢需要掃描大量的資料但只返回少數的行，那麼通常可以嘗試下面的技巧去優化它：

• 使用索引覆蓋掃描，把所有需要用到的列都放到索引中，這樣儲存引擎無須回表獲取對應行就可以返回結果
• 改變庫表結構。例如使用單獨的彙總表
• 重寫這個複雜的查詢，讓MySQL優化器能夠以更優化的方式執行這個查詢　

2.重構查詢方式

①切分查詢

有時候對於一個大查詢我們需要“分而治之”，將大查詢切分成小查詢，每個查詢功能完全一樣，只完成一小部分，每次只返回一小部分查詢結果。
刪除舊的資料就是一個很好的例子。定期地清除大量資料時，如果用一個大的語句一次性完成的話，則可能需要一次鎖住很多資料、佔滿整個事務日誌、耗盡系統資源、阻塞很多小的但重要的查詢。將一個大的DELETE語句切分成多個較小的查詢可以儘可能小地影響MySQL效能，同時還可以減少MySQL複製的延遲。例如，我們需要每個月執行一次下面的查詢：

    mysql> DELETE FROM messages WHERE created < DATE_SUB(NOW(),INTERVAL 3 MONTH);

那麼可以用類似下面的辦法來完成同樣的工作：

 rows_affected = 0
    do {
       rows_affected = do_query(
          "DELETE FROM messages WHERE created < DATE_SUB(NOW(),INTERVAL 3 MONTH)
          LIMIT 10000")
    } while rows_affected > 0 

一次刪除一萬行資料一般來說是一個比較高效而且對伺服器(5)影響也最小的做法（如果是事務型引擎，很多時候小事務能夠更高效）。同時，需要注意的是，如果每次刪除資料後，都暫停一會兒再做下一次刪除，這樣也可以將伺服器上原本一次性的壓力分散到一個很長的時間段中，就可以大大降低對伺服器的影響，還可以大大減少刪除時鎖的持有時間。

②分解關聯查詢

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

    mysql> 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';

可以分解成下面這些查詢來代替：

    mysql> SELECT * FROM  tag_post WHERE tag_id=1';
    mysql> SELECT * FROM  tag_post WHERE tag_id=1234;
    mysql> SELECT * FROM  post WHERE  post.id in (123,456,567,9098,8904);

到底為什麼要這樣做？乍一看，這樣做並沒有什麼好處，原本一條查詢，這裡卻變成多條查詢，返回的結果又是一模一樣的。事實上，用分解關聯查詢的方式重構查詢有如下的優勢：

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

3.檢視mysql的查詢優化器對sql的優化,從而優化sql

很多時候我們寫的sql，和被查詢優化器優化後的sql的執行順序是不一致的，所以當我們遇到sql效率很低下的時候，我們可以試著使用EXPLAIN EXTENDED來檢視這個查詢被改寫成了什麼樣子。當然在mysql8.0的版本這個命令是會報錯的，只需要先執行

explain，然後可以通過mysql的show warnings命令得到。

比如下面的例子就是沒有按照我們預想執行的：

MySQL的子查詢實現得非常糟糕。最糟糕的一類查詢是WHERE條件中包含IN()的子查詢語句。例如，我們希望找到Sakila資料庫中，演員Penelope Guiness（他的actor_id為1）參演過的所有影片資訊。很自然的，我們會按照下面的方式用子查詢實現：

    mysql> SELECT * FROM sakila.film
        -> WHERE film_id IN(
        ->    SELECT film_id FROM sakila.film_actor WHERE actor_id = 1);

因為MySQL對IN()列表中的選項有專門的優化策略，一般會認為MySQL會先執行子查詢返回所有包含actor_id為1的film_id。一般來說，IN()列表查詢速度很快，所以我們會認為上面的查詢會這樣執行：

    -- SELECT * FROM sakila.film-- SELECT GROUP_CONCAT(film_id) FROM sakila.film_actor WHERE actor_id = 1;
    -- Result: 1,23,25,106,140,166,277,361,438,499,506,509,605,635,749,832,939,970,980
    SELECT * FROM sakila.film
    WHERE film_id
    IN(1,23,25,106,140,166,277,361,438,499,506,509,605,635,749,832,939,970,980);

很不幸，MySQL不是這樣做的。MySQL會將相關的外層表壓到子查詢中，它認為這樣可以更高效率地查詢到資料行。也就是說，MySQL會將查詢改寫成下面的樣子：

    SELECT * FROM sakila.film

 WHERE EXISTS (
       SELECT * FROM sakila.film_actor WHERE actor_id = 1
       AND film_actor.film_id = film.film_id);

這時，子查詢需要根據film_id來關聯外部表film，因為需要film_id欄位，所以MySQL認為無法先執行這個子查詢。通過EXPLAIN我們可以看到子查詢是一個相關子查詢（DEPENDENT SUBQUERY）（可以使用EXPLAIN EXTENDED來檢視這個查詢被改寫成了什麼樣子）：
根據EXPLAIN的輸出我們可以看到，MySQL先選擇對file表進行全表掃描，然後根據返回的flm_id逐個執行子查詢。如果是一個很小的表，這個查詢糟糕的效能可能還不會引起注意，但是如果外層的表是一個非常大的表，那麼這個查詢的效能會非常糟糕。當然我們很容易用下面的辦法來重寫這個查詢：

    mysql> SELECT film.* FROM sakila.film
        ->    INNER JOIN sakila.film_actor USING(film_id)
        -> WHERE actor_id = 1;

另一個優化的辦法是使用函式GROUP_CONCAT()在IN()中構造一個由逗號分隔的列表。有時這比上面的使用關聯改寫更快。因為使用IN()加子查詢，效能經常會非常糟，所以通常建議使用EXISTS()等效的改寫查詢來獲取更好的效率。

總結：

mysql的查詢優化是一個需要長期實踐和總結的過程，不是幾天就可以速成的，所以大家一家要把自己每次優化sql的經驗都形成自己的筆記和心得，長期下來就會越來越得心應手。

我是阿達，一名喜歡分享知識的程式設計師，時不時的也會荒腔走板的聊一聊電影、電視劇、音樂、漫畫，這裡已經有12300位小夥伴在等你們啦，感興趣的就趕緊來點選關注我把，哪裡有不明白或有不同觀點的地方歡迎留言！