查詢性能優化
重構查詢的方式
在優化有問題的查詢時，目標應該是找到一個更優的方法獲得實際需要的記過——而不是一定總是需要從MySQL獲取一模一樣的結果集。有時候，可以將查詢轉換一種寫法讓其返回一樣的結果，但是性能更好。但也可以通過修改應用代碼，用另一種方式完成查詢，最終達到一樣的目的。
一個復雜查詢還是多個簡單查詢
設計查詢的時候一個需要考慮的重要問題是，是否需要將一個復雜的查詢分成多個簡單的查詢，在傳統實現中，總是強調需要數據庫層完成盡可能多的工作，這樣做的邏輯在于以前總是認為網絡通信、查詢解析和優化是一件代價很高的事情。但是這樣的想法對于MySQL并不適用，MySQL從設計上讓連接和斷開連接都很輕量級，在返回一個小的查詢結果方面很高效。現代的網絡速度比以前要快很多，無論是貸款還是延遲。在某些版本的MySQL上，即使在一個通用服務器上，也能夠運行超過10萬的查詢，即使是一個千兆網卡(1000Mbps / 8 bit = 125M/s)也能輕松滿足每秒超過2000次的查詢。所以運行多個小查詢現在已經不是大問題了。MySQL內部每秒能夠掃描內存中上百萬行數據，相比之下，MySQL響應數據給客戶端就慢得多了。在其他條件都相同的時候，適用盡可能少的查詢當然是更好地。但是有時候，將一個大查詢分解為多個小查詢是很有必要的。別害怕這樣做，好好衡量一下這樣做是不是會減少工作量。
不過，在應用設計的時候，如果一個查詢能夠勝任時還寫成多個獨立查詢是不明智的。例如，有些應用對一個數據表做10次獨立的查詢來返回10行數據，每個查詢返回一條結果，查詢10次
切分查詢
有時候對于一個大查詢我們需要"分而治之"，將大查詢切分成小查詢，每個查詢功能完全一樣，只完成一小部分，每次只返回一小部分查詢結果。刪除舊的數據就是一個很好的例子。定期地清除大量數據時，如果用一個大的語句一次性完成的話，則可能需要依次鎖住很多數據、占滿整個事務日志、耗盡系統資源、阻塞很多小的但很重要的查詢。將一個大的DELETE語句切分成多個較小的查詢可以盡可能小地影響MySQL性能，同時還可以減少MySQL復制地延遲。例如，我們需要每個月運行一次下面的查詢:
mysql> DELETE FROM message WHERE created < DATE_SUB(NOW(), INTERVAL 3 MONT
那么可以用類似下面的辦法來完成同樣的工作:
rows_affected=0
do {
rows_affected = do_query(
"DELETE FROM message WHERE created < DATE_SUB(NOW(), INTERVAL 3 MONTH)
LIMIT 10000"
)
} while rows_affected > 0
一次性刪除一萬行數據一般來說是一個比較高效而且對服務器影響也是最小的做法(如果是事務型引擎，很多時候小事務能夠更高效)，同時，需要注意的是，如果每次刪除數據后，都暫停一會兒再做下一次刪除，這樣也可以將服務器上原本一次性的壓力分散到一個很長的時間段中，就可以大大降低對服務器的影響，還可以大大減少刪除時鎖的持有時間
分階關聯查詢
很多高性能的應用都會對關聯查詢進行分解。簡單地，可以對每一個表進行一次單表查詢，然后將結果在應用程序中進行關聯。例如，下面這個查詢:
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 WHERE tag = 'mysql';
mysql> SELECT * FROM tag_post WHERE tag_id = 1234;
mysql> SELECT * FROM post WHERE post.id IN (123,456,567,9098,8904);
到底為什么要這樣做呢？乍一看，這樣做并沒有什么好處，原本一條查詢，這里卻變成多條查詢，返回的結果又是一模一樣的。事實上，用分解關聯查詢的方式重構查詢有如下的優勢:
- 1.讓緩存的效率更高。許多應用程序可以方便地緩存單表查詢對應的結果對象。例如，上面查詢中的tag已經被緩存了，那么應用就可以跳過第一個查詢。再例如，應用中已經緩存了ID為123、567、9098的內容，那么第三個查詢中的IN()中就可以少幾個ID.另外，對MySQL的查詢緩存來說(Query Cache)，如果關聯中的某個表發生了變化，那么就無法適用查詢緩存了，而拆分后，如果某個表很少改變，那么基于該表的查詢就可以重復利用查詢緩存結果了
- 2.將查詢分解后，執行單個查詢可以減少鎖的競爭
- 3.再在應用層做關聯，可以更容易對數據庫進行拆分，更容易做到高性能和可擴展
- 4.查詢本身效率也可能會有所提升。這個例子中，使用IN()代替關聯查詢，可以讓MySQL按照ID順序進行查詢，這可能比隨機關聯要更高效。
- 5.可以減少冗余記錄的查詢。在應用層做關聯查詢，意味著對于某條記錄應用只需要查詢一次，而在數據庫中做關聯查詢，則可能需要重復地訪問一部分數據。從這點看，這樣的重構還可能會減少網絡和內存的消耗
- 6.更進一步，這樣做相當于在應用中實現了哈希關聯，而不是使用MySQL的嵌套循環關聯。某些場景哈希關聯的效率要高很多。
在很多場景下，通過重構查詢將關聯放到應用程序中將會更加高效，這樣的場景有很多。比如，當應用能夠方便地緩存單個查詢的結果的時候、當可以將數據分不到不同的MySQL服務器上的時候、當能夠使用IN()的方式代替關聯查詢的時候、當查詢中使用同一個數據表的時候
查詢執行的基礎
當希望MySQL能夠以更高效的性能運行查詢時，最好的辦法就是弄清楚MySQL是如何優化和執行查詢的。一旦理解這一點，很多查詢優化工作實際上就是遵循一些原則讓優化器能夠按照預想的合理的方式運行。MySQL執行一個查詢的過程。根據如圖所示，我們可以看到當向MySQL發送一個請求的時候，MySQL到底做了些什么。
- 1.客戶端發送一條查詢給服務器
- 2.服務器先檢查查詢緩存，如果命中了緩存，則立刻返回存儲在緩存中的結果。否則進入下一階段
- 3.服務器端進入SQL解析、預處理，再由優化器生成對應的執行計劃
- 4.MySQL根據優化器生成的執行計劃，調用存儲引擎的API來執行查詢
- 5.將結果返回給客戶端
上面的每一步都比想象的復雜，接下來我們會看到在每一個階段查詢處于何種狀態。查詢優化器是其中特別復雜也特別難以理解的部分。還有很多的例外情況，例如，當查詢使用綁定變量后，執行路徑會有所不同
)
