MySQL優化一般是需要索引優化、查詢優化、庫表結構優化三駕馬車齊頭并進。 本章節開始講查詢優化。 ## 一、為什么查詢速度會慢 可以把查詢當作一個任務，它由一系列子任務組成，每個子任務都會消耗一定的時間。**如果要優化查詢，實際上是優化其子任務，要么消除其中一些子任務，要么減少子任務的執行次數，要么讓子任務運行得更快。** MySQL在執行查詢的時候有哪些子任務，這個是有一定的方法進行剖析的，具體方法下回單獨拿一個章節來分析。 通常來說，查詢的生命周期大致可以按照順序來看：從客戶端，到服務端，然后在服務器上進行解析，生成執行計劃，執行，并返回結果給客戶端。其中“執行”可以認為是整個生命周期中最重要的階段，這其中包括了大量為了檢索數據到存儲引擎的調用以及調用后的數據處理，包括排序、分組等。 在完成這些任務的時候，查詢需要在不同的地方花費時間，包括網絡，CPU計算，生成統計信息和執行計劃、鎖等待（互斥等待）等操作，尤其是向底層存儲引擎檢索數據的調用操作，這些調用需要在內存操作、CPU操作和內存不足時導致的I/O操作上消耗時間。 在每一個消耗大量時間的查詢案例中，都能看到一些不必要的額外操作、某些操作被額外地重復了很多次、某些操作執行得太慢等。**優化查詢的目的就是減少和消除這些操作所花費的時間。** ## 一、首選要優化數據訪問 查詢性能底下最基本的原因是訪問的數據太多。所以，對于低效的查詢，一般通過兩個步驟來分析： 1. 確認應用程序是否在檢索大量超過需要的數據。這通常意味著訪問了太多的行，但有時候也可能是訪問了太多的列。 2. 確認MySQL服務器層是否在分析大量超過需要的數據行。 ### 1.1、是否向數據庫請求了不需要的數據 在訪問數據庫時，應該只請求需要的行和列，請求多余的行和列會消耗MySQL服務器的CPU和內存資源，并增加網絡開銷。 1、在處理分頁時，應該使用LIMIT限制MySQL只返回需要的數據，而不是向應用程序返回全部數據后，再由應用程序過濾不需要的行。 2、多表關聯時，或獲取單表數據時，盡量避免不加思考地使用`SELECT *` 3、當一些數據被多次使用時可以考慮將數據緩存起來，避免每次使用都要到MySQL查詢。 ### 1.2、MySQL是否在掃描額外的記錄，應該讓MySQL使用最合適的方式查詢數據 對于MySQL，最簡單的衡量查詢開銷有三個指標：響應時間、掃描的行數和返回的行數。這里主要考慮提高掃描的方式，即查詢數據的方式。 查詢數據的方式有全表掃描、索引掃描、范圍掃描、唯一索引查詢、常數引用等。這些查詢方式，速度從慢到快，掃描的行數也是從多到少。可以通過EXPLAIN語句中的type列反應查詢采用的是哪種方式。 通常可以通過添加合適的索引改善查詢數據的方式，使其盡可能減少掃描的數據行，加快查詢速度。 例如，當發現查詢需要掃描大量的數據行但只返回少數的行，那么可以考慮使用覆蓋索引，即把所有需要用到的列都放到索引中。這樣存儲引擎無須回表獲取對應行就可以返回結果了。 ## 二、重構查詢的方法 設計查詢的時候需要考慮是否需要把一個復雜的查詢分成多個簡單的查詢。在我的印象中，曾經無數次聽到一個經驗法則：**可以在數據庫中做的事不要放在應用程序中，數據庫比我們想象的要厲害的多。**這個經驗法則是在華夏基金使用Oracle編寫SQL時一位Oracle牛人告訴我的，后來我把它使用到MySQL上，真是吃盡苦頭。 當然這其中的原因有Oracle和MySQL原本就不是一樣的處理邏輯，并且現在的網絡通信、查詢解析和優化的代價并沒有以前那么高啦。再次說明，經驗法則有在某種特定籠子里才有效。 **分解復雜的查詢：** 可以將一個大查詢切分成多個小查詢執行，每個小查詢只完成整個查詢任務的一小部分，每次只返回一小部分結果。 刪除舊的數據是一個很好的例子。 如果只用一條語句一次性執行一個大的刪除操作，則可能需要一次鎖住很多數據，占滿整個事務日志，耗盡系統資源、阻塞很多小的但重要的查詢。將一個大的刪除操作分解成多個較小的刪除操作可以將服務器上原本一次性的壓力分散到多次操作上，盡可能小地影響MySQL性能，減少刪除時鎖的等待時間，同時也減少了MySQL主從復制的延遲。這個方法我一直在用。 另一個例子是分解關聯查詢，即對每個要關聯的表進行單表查詢，然后將結果在應用程序中進行關聯。我在之前一家公司和一位在阿里待過很多年的同事一起編碼時，他就是這么干的。后來我在心中默默地鄙視著他，因為我心里有這么一個經驗法則（可以在數據庫中做的事不要放在應用程序中，數據庫比我們想象的要厲害的多），并且我在行動上也是保持能用一個SQL解決的事絕對不會用兩個SQL。這么做當然處理經驗法則的原因之外還有一個原因是：獲取數據的邏輯盡量與業務代碼分離，這樣以后在切換數據庫時也很方便。實際上是這樣嗎？未必啊。那次的無知讓我吃盡苦頭啊，后來因為SQL的性能問題再把我寫的大部分SQL進行分解。 用分解關聯查詢的方式重構查詢有如下的優勢： 1. 讓緩存的效率更高。許多應用程序可以方便地緩存單表查詢對應的結果對象。 2. 將查詢分解后，執行單個查詢可以減少鎖的競爭。 3. 在應用層做關聯，可以更容易對數據庫進行拆分，更容易做到高性能和可擴展。 4. 查詢本身效率也可能會有所提升。 5. 可以減少冗余記錄的查詢。在應用層做關聯查詢，意味著對于某條記錄應用只需要查詢一次，而在數據庫中做關聯查詢，則可能需要重復地訪問一部分數據。從這點看，這樣的重構還可能會減少網絡和內存的消耗。 6. 更進一步，這樣做相當于在應用中實現了哈希關聯，而不是使用MySQL的嵌套循環關聯。某些場景哈希關聯的效率要高很多。 ## 三、查詢執行的基礎 當向MySQL發送一個請求之后，MySQL到底做了些什么？如下圖所示：  查詢執行路徑 1. 客戶端發送一條查詢給服務器。 2. 服務器先檢查查詢緩存，如果命中了緩存，則立刻返回存儲在緩存中的結果。否則進行下一階段。 3. 服務器端進行SQL解析、預處理，再由優化器生成對應的執行計劃。 4. MySQL根據優化器生成的執行計劃，調用存儲引擎的API來執行查詢。 5. 將結果返回給客戶端。 上述的每一步都比想象的復雜。我們在下一章節來進行分析。