mysql查詢優化之一：mysql查詢優化常用方式

一、為什么查詢速度會慢？?

　　一個查詢的生命周期大致可以按照順序來看：從客戶端，到服務器，然后在服務器上進行解析，生成執行計劃，執行，并返回結果給客戶端。其中在“執行”階段包含了大量為了檢索數據到存儲引擎的調用以及調用后的數據處理，包括排序、分組。?
查詢速度慢的原因在于：某些不必要的額外操作，某些操作被額外地重復很多次，某些操作執行得太慢。?
優化查詢的目的就是減少和消除這些操作所花費的時間。

二、慢查詢基礎：優化數據訪問

查詢性能低下最基本的原因是訪問的數據太多。所以對于低效的查詢可以從下面兩個方面來分析：?
1.確認應用程序是否在檢索大量超過需要的數據。?
2.確認MySQL服務器層是否在分析大量超過需要的數據行。?
2.1、是否向數據庫請求了不需要的數據

請求多余的數據會給MySQL服務器帶來額外的負擔，并增加網絡開銷，另外也會消耗應用服務器的CPU內存和資源。低效的查詢表現在如下方面：?

1、查詢不需要的記錄：例如在新聞網站中取出100條記錄，但是只是在頁面上顯示10條。實際上MySQL會查詢出全部的結果，客戶端的應用程序會接收全部的結果集數據，然后拋棄其中大部分數據。最簡單有效的解決方法就是在這樣的查詢后面加上LIMIT。

2、多表關聯時返回全部列，例如：

3、總是取出全部的列：每次看到SELECT *的時候都需要懷疑是不是真的需要返回全部的列？取出全部列，會主優化器無法完成索引覆蓋掃描這類優化，還會為服務器帶來額外的IO、內存和CPU的消耗。如果應用程序使用了某種緩存機制，或者有其他考慮，獲取超過需要的數據也可能有其好處，但不要忘記這樣做的代價是什么。獲取并緩存所有的列的查詢，相比多個獨立的只獲取部分列的查詢可能就更有好處。

4、重復查詢相同的數據：不要不斷地重復執行相同的查詢，然后每次都返回完全相同的數據。當初次查詢的時候將這個數據緩存起來，需要的時候從緩存中取出，這樣性能顯然更好。

注：在查詢語句中慎用select * 語句，我們對數據庫的數據應該只取所需。?
2.2、mysql是否在掃描額外的記錄?
在確定查詢只返回需要的數據之后，那么查詢為了返回結果是否掃描了過多的數據，有以下三個指標衡量查詢的開銷：

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

通過查詢慢日志可以找到這三個指標的記錄。

響應時間

響應時間是兩個部分之和：服務時間和排隊時間，一般常見和重要的等待是IO和鎖等待。

掃描的行數和返回的行數

分析查詢時，查看該查詢掃描的行數是非常有幫助的。一定程度上能夠說明該查詢找到需要的數據的效率高不高。理想的情況下掃描的行數和返回的行數應該是相同的。當然這只是理想情況。一般來說掃描的行數對返回的行數的比率通常很小，一般在1：1到10：1之間。

掃描的行數和訪問類型

MySQL有好幾種訪問方式可以查找并返回一行結果。有些訪問方式可能需要掃描很多行才能返回一行結果，也有些訪問方式可能無須掃描就能返回結果。

在EXPLAIN語句的TYPE列返回了訪問類型。如果查詢沒有辦法找到合適的訪問類型，那么解決的最好辦法通常就是增加一個合適的索引。索引讓MySQL以最高效、掃描行最少的方式找到需要的記錄。

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

1、在索引中使用WHERE條件來過濾不匹配的記錄。這是在存儲引擎層完成的。

2、使用索引覆蓋掃描（在extra列中出現了using index）來返回記錄，直接從索引中過濾不需要的記錄并返回命中的結果。這是在MySQL服務器層完成的，但無須回表查詢記錄。

3、從數據表中返回數據，然后過濾不滿足條件的記錄（在extra列中出現using where）。這在MySQL服務器層完成，MySQL需要先從數據表讀出記錄然后過濾。

?

三、重構查詢的方式

選擇一個復雜的查詢還是多個簡單的查詢？?
書中作者的觀點：在MySQL內部每秒能夠掃描內存中上百行的數據，相比之下MySQL響應數據給客服端就慢的多了。其他條件都相同的時候，使用盡可能少的查詢是更好的。但是并不否認將一個大的查詢分解為多個小的查詢。?
將查詢分解的方法：?
3.1、切分查詢：顧名思義，就是將一個大的查詢切分為許多小的查詢，每個小查詢功能完全一樣，返回一部分結果，我們只需重復執行小查詢就行。（應用：在清除大量的數據時，如果一個大的語句可能一次性要耗費許多資源，阻塞其他查詢，這時我們可以將其切分為多個小的查詢，即每個查詢只刪除適量的查詢，多次進行）

示例：刪除歷史數據的任務

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

一次刪除一萬行一般來說是一個比較高效而且對服務器影響也最小的做法。需要注意，如果刪除后暫停一會兒，再執行下一次，可以大大降低服務器的影響，還可以大大減少刪除時鎖的持有時間。

?
3.2、分解關聯查詢?
可將單條的多表關聯查詢分解為多條查詢，對每一個表進行一次單表查詢，然后將結果在應用程序中進行關聯。（將在數據庫中做的關聯查詢，轉移到了應用層）?

示例：

優點：?
a.讓緩存的效率更高。對單表查詢的結果，應用程序可以很方便的緩存，分解語句之后，我們可以高效的利用緩存來進行查詢。?
b.將查詢分解之后，執行單個查詢可以減少鎖的競爭。?
c.在應用層做關聯，可以更容易對數據庫進行拆分，更容易做到高性能和可擴展。?
d.查詢本身的效率也會得到提升。按照ID順序查詢比隨機的關聯要更加的高效。（使用in()的方式代替關聯查詢的join…on…）?
e.減少冗余記錄的查詢。在應用層做關聯查詢，對于某條記錄應用只需要查詢一次，而在數據庫中做關聯查詢，則可能需要重復的訪問某一部分的數據。

四、查詢執行的基礎

MySQL是如何優化和執行查詢的？?

?

?
1.客戶端發送一條查詢給服務器；?
2.服務器先檢查查詢緩存，如果命中了緩存，則立刻返回存儲在緩存中的結果。否則進入下一階段；?
3.服務器進行SQL解析、預處理、在由查詢優化器生成對應的執行計劃；?
4.MySQL根據優化器生成的執行計劃，調用存儲引擎的API來執行查詢。?
5.將結果返回給客服端，同時也會放入查詢緩存中。?

4.1、MySQL客戶端/服務器通信協議?

MySQL客戶端和服務器之間的通信協議是“半雙工”的，這意味著在任何一個時刻，要么是由服務器向客戶端發送數據，要么是由客戶端向服務器發送數據，這兩個動作不能同時進行。這種通信方式造成了許多限制。?
在多數連接MySQL的庫函數（調用MySQL數據庫的函數方法）默認一般是獲得全部結果集并緩存到內存里。?
當然有時候這種全部緩存的方法并不好，一個很大的結果集在緩存時會占有大量的時間和內存。不使用緩存，從服務器中獲取數據，然后直接處理，不過這樣會加大服務器的壓力，服務器只有在查詢完成后才能釋放資源。?

查詢狀態：可以使用SHOW FULL PROCESSLIST命令查看查詢的執行狀態。Sleep、Query、Locked、Analyzing and statistics、Copying to tmp table[on disk]、Sorting result、Sending data

詳細見《mysql show processlist命令 詳解》

4.2、查詢緩存（query cache）?

如果查詢緩存打開，那么mysql會優先檢查這個查詢是否命中查詢緩存中的數據。這是檢查是通過一個對大小寫敏感的哈希查找實現的。如果當前的查詢恰好命中了查詢緩存，那么在返回查詢結果之前MySQL會檢查一次用戶權限。如果權限沒有問題，MySQL會跳過執行階段，直接從緩存中拿到結果并返回給客戶端。?

詳細見《MySQL查詢緩存總結》

4.3、查詢優化處理

查詢生命周期的下一步是將一個SQL轉換成一個執行計劃，MySQL再依照這個執行計劃和存儲引擎進行交互。這包括多個子階段：解析SQL、預處理、優化SQL執行計劃。

1、語法解析器和預處理首先MySQL通過關鍵字將SQL語句進行解析，并生成一棵解析樹。MySQL解析器將使用MySQL語法規則驗證和解析查詢。例如是否使用錯誤的關鍵字，或者使用關鍵字的順序是否正確，引號是否能前后正確匹配等。

2、預處理器則根據一些MySQL規則進一步檢查解析樹是否合法，例如檢查數據表和數據列是否存在，還會解析名字和別名看它們是否有歧義。

3、一下步預處理會驗證權限。

查詢優化器?

一條語句 可以有很多種執行方式，最后都返回相同的結果。優化器的作用就是找到最好的執行計劃。MySQL使用基于成本的優化器，它將嘗試預測一個查詢使用某種執行計劃時的成本，并選擇其中成本最小的一個。成本的最小單位是隨機讀取一個4K的數據頁的成本，并加入一些因子來估算某引動操作的代價。可以通過查詢當前會話的Last_query_cost的值來得知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對任何關聯都執行嵌套循環關聯操作，即MySQL先在一個表中循環取出單條數據，然后再嵌套循環到一個表中尋找匹配的行，依次下去直到找到的有匹配的行為止。然后根據各個表匹配的行，返回查詢中需要的各個列。（嵌套循環關聯）

?

執行計劃：MySQL生成查詢的一棵指令樹，然后通過存儲引擎執行完成這棵指令樹并返回結果。最終的執行計劃包含了重構查詢的全部信息。如果對某個查詢執行EXPLAIN EXTENDED，再執行SHOW WARNINGS，就可以看到重構出的查詢。

MySQL的執行計劃是一棵左側深度優先的樹。

不過，如果有超過n個表的關聯，那么需要檢查n的階乘種關聯順序。我們稱之為所有可能的執行計劃的“搜索空間”。實際上，當需要關聯的表超過optimizer_search_depth的限制的時候，就會選擇“貪婪”搜索模式。

MySQL的排序優化：?

無論如何排序都是一個成本很高的操作，所以從性能角度考慮，應盡可能避免排序或者盡可能避免對大量數據進行排序。如果需要排序的數據量小于排序緩沖區，MySQL使用內存進行“快速排序”操作。如果內存不夠排序，那么MySQL會先將數據分塊，對每個獨立的塊使用“快速排序”進行排序，并將各個塊的排序結果存放在磁盤上，然后將各個排序的塊進行合并，最手返回排序結果。

MySQL有兩種排序方法：

? ? 兩次傳輸排序（舊版），讀取行指針和需要排序的字段，對其進行排序，然后再根據排序結果讀取所需要的數據行。顯然是兩次傳輸，特別是讀取排序后的數據時（第二次）大量隨機I/O，所以兩次傳輸成本高。
? ? 單次傳輸排序（新版），一次讀取出所有需要的或SQL查詢指定的列，然后根據排序列，排序，直接返回排序后的結果。順序I/O，缺點：如果列多，額外占用空間。

MySQL在進行文件排序時需要使用的臨時存儲空間可能會比想象的要大得多，因為MySQL在排序時，對每一個排序記錄都會分配一個足夠長的定長空間來存放。這個定長空間必須足夠以容納其中最長的字符串。

在關聯查詢的時候如果需要排序，MySQL會分兩種情況來處理這樣的文件排序。如果ORDER BY子句的所有列都來自關聯的第一個表，那么MySQL在關聯處理第一個表時就進行文件排序。如果是這樣那么在MySQL的EXPLAIN結果中可以看到Extra字段會有Using filesort。除此之外的所有情況，MySQL都會將關聯的結果存放在一個臨時表中，然后在所有的關聯都結束后，再進行文件排序。這種情況下Extra字段可以看到Using temporary;Using filesort。如果查詢中有LIMIT的話，LIMIT也會在排序之后應用，所以即使需要返回較少的數據，臨時表和需要排序的數據量仍然會非常大。

4.4、 查詢執行引擎

相對于查詢優化，查詢執行簡單些了，MySQL只根據執行計劃輸出的指令逐步執行。指令都是調用存儲引擎的API來完成，一般稱為 handler API，實際上，MySQL優化階段為每個表都創建了一個 handler 實例，用 handler 實例獲取表的相關信息（列名、索引統計信息等）。

存儲引擎接口有著非常豐富的功能，但是底層接口卻只有幾十個，這些接口像搭積木一樣能夠完成查詢的大部分操作。例如，有一個查詢某個索引的第一行的接口，再有一個查詢某個索引條件的下一條目的功能，有了這兩個功能就可以完成全索引掃描操作。

?

4.5、 返回結果給客戶端

查詢執行的最后一個階段就是將結果返回給客戶端。即使查詢不需要返回結果集給客戶端，MySQL仍然會返回這個查詢的一些信息，例如該查詢影響到的行數。

MySQL將結果集返回客戶端是一個增量、逐步返回的過程。一旦服務器處理完最后一個關聯表，開始生成第一條結果時，MySQL就可以開始向客戶端逐步返回結果集了。

這樣處理有兩個好處：服務端無須存儲太多的結果，也就不會因為要返回太多結果而消耗太多內存。另外，這樣的處理也讓MySQL客戶端第一時間獲得返回的結果。

?

七、優化特定類型的查詢

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