mysql查詢最佳化之一：

標籤：關鍵字   進入   變化   阻塞   count   iss   簡單的   任務   完全   

查詢效能最佳化

為什麼查詢速度會慢？ 
一個查詢的生命週期大致可以按照順序來看：從用戶端，到伺服器，然後在伺服器上進行解析，產生執行計畫，執行，並返回結果給用戶端。其中在“執行”階段包含了大量為了檢索資料到儲存引擎的調用以及調用後的資料處理，包括排序、分組。 
查詢速度慢的原因在於：某些不必要的額外操作，某些操作被額外地重複很多次，某些操作執行得太慢。 
最佳化查詢的目的就是減少和消除這些操作所花費的時間。

慢查詢基礎：最佳化資料訪問

查詢效能低下最基本的原因是訪問的資料太多。所以對於低效的查詢可以從下面兩個方面來分析： 
1.確認應用程式是否在檢索大量超過需要的資料。 
2.確認MySQL伺服器層是否在分析大量超過需要的資料行。 
查詢是否只取所需 
低效的查詢表現在如下方面： 
a.查詢了不需要的記錄； 
b.多表關聯時返回全部列； 
c.總是取出所有的列； 
d.重複查詢相同的資料。 
註：在查詢語句中慎用select * 語句，我們對資料庫的資料應該只取所需。 
mysql是否在掃描額外的記錄 
在確定查詢只返回需要的資料之後，那麼查詢為了返回結果是否掃描了過多的資料，有以下三個指標衡量查詢的開銷：

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

通過查詢慢日誌可以找到這三個指標的記錄 
索引對掃描的行數有著很大的影響，當發現查詢需要掃描大量的資料但只返回少數的行時，可以使用索引覆蓋掃描，避免回表查詢。

重構查詢的方式

選擇一個複雜的查詢還是多個簡單的查詢？ 
書中作者的觀點：在MySQL內部每秒能夠掃描記憶體中上百行的資料，相比之下MySQL響應資料給客服端就慢的多了。其他條件都相同的時候，使用儘可能少的查詢是更好的。但是並不否認將一個大的查詢分解為多個小的查詢。 
將查詢分解的方法： 
切分查詢：顧名思義，就是將一個大的查詢切分為許多小的查詢，每個小查詢功能完全一樣，返回一部分結果，我們只需重複執行小查詢就行。（應用：在清除大量的資料時，如果一個大的語句可能一次性要耗費許多資源，阻塞其他查詢，這時我們可以將其切分為多個小的查詢，即每個查詢只刪除適量的查詢，多次進行） 
分解關聯查詢 
可將單條的多表關聯查詢分解為多條查詢，對每一個表進行一次單表查詢，然後將結果在應用程式中進行關聯。（將在資料庫中做的關聯查詢，轉移到了應用程式層） 
優點： 
a.讓緩衝的效率更高。對單表查詢的結果，應用程式可以很方便的緩衝，分解語句之後，我們可以高效的利用緩衝來進行查詢。 
b.將查詢分解之後，執行單個查詢可以減少鎖的競爭。 
c.在應用程式層做關聯，可以更容易對資料庫進行拆分，更容易做到高效能和可擴充。 
d.查詢本身的效率也會得到提升。按照ID順序查詢比隨機的關聯要更加的高效。（使用in()的方式代替關聯查詢的join…on…） 
e.減少冗餘記錄的查詢。在應用程式層做關聯查詢，對於某條記錄應用只需要查詢一次，而在資料庫中做關聯查詢，則可能需要重複的訪問某一部分的資料。

查詢執行的基礎

MySQL是如何最佳化和執行查詢的？ 
 
1.用戶端發送一條查詢給伺服器； 
2.伺服器先檢查查詢快取，如果命中了緩衝，則立刻返回儲存在緩衝中的結果。否則進入下一階段； 
3.伺服器進行SQL解析、預先處理、在由查詢最佳化工具產生對應的執行計畫； 
4.MySQL根據最佳化器產生的執行計畫，調用儲存引擎的API來執行查詢。 
5.將結果返回給客服端，同時也會放入查詢快取中。 
MySQL用戶端/伺服器通訊協定 
MySQL用戶端和伺服器之間的通訊協定是“半雙工”的，這意味著在任何一個時刻，要麼是由伺服器向用戶端發送資料，要麼是由用戶端向伺服器發送資料，這兩個動作不能同時進行。這種通訊方式造成了許多限制。 
在多數串連MySQL的庫函數（調用MySQL資料庫的函數方法）預設一般是獲得全部結果集並緩衝到記憶體裡。 
當然有時候這種全部緩衝的方法並不好，一個很大的結果集在緩衝時會佔有大量的時間和記憶體。不使用緩衝，從伺服器中擷取資料，然後直接處理，不過這樣會加大伺服器的壓力，伺服器只有在查詢完成後才能釋放資源。 
查詢快取（query cache） 
MySQL會通過一個對大小寫敏感的雜湊尋找實現對查詢快取中資料的尋找，是一種精確的匹配尋找。 
查詢最佳化處理 
將SQL轉化為一個執行任務包括“解析SQL、預先處理、最佳化執行計畫”。 
文法解析器：MySQL通過關鍵字將SQL語句進行解析，並產生一顆對應的“解析樹”，MySQL解析器將使用MySQL文法規則驗證和解析查詢（關鍵字的順序是否正確，驗證引號是否能前後正確匹配）。 
前置處理器：前置處理器會進一步的檢查解析是否合法（檢查資料表和資料列是否存在，解析名字、別名看它們是否有歧義）。 
查詢最佳化工具 
查詢最佳化工具是基於成本的最佳化器，它會嘗試預測一個查詢使用某種執行計畫時的成本，並選擇其中成本最下的一個（可通過查詢當前會話的Last_query_cost的值來得知MySQL計算的當前查詢的成本） 
 
上述語句預測了此count（*）操作大概需要做1.8個資料頁的隨機尋找才能完成。 
MySQL能夠處理的最佳化類型： 
1.重新定義關聯表的順序； 
2.將外串連轉化為內串連； 
3.使用等價變化規則；可以合并和減少一些比較，還可以移除一些恒成立和恒不成立的判斷。 
4.最佳化count()、min()和max()；索引和列是否可為空白通常可以協助MySQL最佳化這類的運算式，如尋找最小值，只需找到索引樹最左邊的第一條記錄。 
5.預估並轉化為常數運算式；當MySQL檢測到一個運算式可以轉化為常數時，就會一直把該運算式作為常數進行最佳化處理。 
6.覆蓋索引掃描；當掃描的索引列包含所有查詢中需要的使用的列時，MySQL就可以直接使用索引返回需要的資料。 
7.子查詢最佳化； 
8.提前終止查詢；如使用limit子句尋找限制數量的資料。 
9.等值傳播；如果兩個列的值通過等式關聯，那麼MySQL能夠將其中一個列的where條件傳遞到另一個列上。 
10.列表in()的比較；MySQL對in()列表進行最佳化，先對列表中的值進行排序，然後通過二分尋找的方式來確定列表中的值是否滿足條件。 
MySQL的排序最佳化： 
MySQL除了利用索引進行排序外，還有一種排序方法叫檔案排序（filesort）。 
當需要排序的資料量小於“排序緩衝區”時，MySQL使用記憶體進行“快速排序”，當記憶體不夠時，會將資料區塊進行分塊，每個獨立的塊使用“快速排序”，並將各個塊的排序結果放在磁碟上，然後對結果進行合并。 
MySQL的兩種排序演算法： 
兩次傳輸排序（舊版本使用）：讀取行指標和需要排序的欄位（第一次），對其進行排序，然後根據排序結果讀取所需要的資料行（第二次）。優點是：排序緩衝區能夠容納更多的資料行進行排序；缺點是：第二次讀取會產生大量的隨機I/O。 
單詞傳輸排序（新版本使用）：先讀取查詢所需的所有列，然後再根據給定列進行排序，最後直接返回排序結果。

MySQL不允許在同一個表同時進行查詢和更新，所以我們在寫這類語句時，先處理某個操作，然後關聯此操作產生的暫存資料表，相當於先運行子查詢，後完成整個操作。

最佳化特定類型的查詢

最佳化COUNT（）查詢 
count（*）：統計行數，比統計一般的列值個數要快很多。 
簡單的最佳化：通過修改條件陳述式，減少掃描的次數。（始終記住，計算count(*)是很快的，比計算所有帶條件的統計都要快） 
使用近似值：即count()結果可以用一個最佳化器估算出來的值代替。 
最佳化關聯查詢 
1.確保ON或者USING子句中的列上有索引，一般索引建立在最後個關聯表上的相應列上。 
2.確保任何時候的GROUP BY 和 ORDER BY 中的運算式只涉及到一個表上的列，這樣MySQL才有可能使用索引來最佳化這個過程。 
最佳化子查詢 
儘可能使用關聯查詢代替子查詢。

mysql查詢最佳化之一：