MySQL查詢最佳化

標籤：mysql   查詢最佳化   

一、查詢為什麼慢

網路，CPU計算，產生的統計資訊，執行技術，鎖等待，IO等待等。

二、慢查詢最佳化訪問低效查詢的處理辦法：

1. 確認是否訪問了太多行，或者太多列

2. 是否分析了大量的資料

問題及應對辦法

1. 查詢不需要的記錄：使用Limit

2. 避免select *，會影響索引覆蓋掃描

3. 避免重複查詢相同的資料

WHERE語句的三種使用方式

1. 儲存引擎直接使用WHERE過濾無效欄位

2. 使用索引掃描，在索引中過濾無效欄位（Extra出現Using Index）

3. 返回所有資料然後WHERE過濾（Extra中出現Using Where）

查詢大量資料返回少量行是可以考慮的技巧

1. 使用覆蓋索引

2. 修改庫表結構例如增加匯總表

3. 重寫複雜查詢

三、重構查詢方式考慮使用一個複雜查詢還是多個簡單查詢切分查詢

1. 將伺服器的壓力分散到一個時間段

分解關聯查詢

1. 讓緩衝的效率更高

2. 執行單個查詢可以減少所得爭用問題

3. 更易拆分資料表，更容易做到高效能和可擴充

4. 可能讓MySQL按照ID進行順序查詢，而不是隨機關聯

5. 可以減少冗餘資料的查詢

6. 相當於在應用中實現了HASH關聯，而不是使用MySQL的嵌套迴圈關聯

四、查詢執行基礎查詢執行流程

1. 伺服器接收一條查詢命令

2. 如果有查詢快取直接返回結果，否則進入下一階段

3. SQL解析，預先處理

4. 最佳化器產生對應的執行計畫

5. 根據執行計畫調用儲存引擎API，完成查詢

6. 將結果返回

查詢快取

　　使用大小寫敏感的hash尋找實現，只要查詢語句有一點改變，就不會被命中。再此期間還會確認一遍使用者的許可權。這時候查詢語句並未被解析，所以也就解釋好了where a>2執行完之後where a>1+1不會被快取命中的原因。

MySQL執行關聯策略

MySQL對任何關聯都執行嵌套關聯操作。

• MySQL先在一個表中迴圈取出單條資料

• 然後再嵌套迴圈到下一個表中尋找匹配的行

• 依次下去直到找到所有表中匹配的行為止

• 然後根據根據各個表匹配的行，返回查詢中需要的列

• MySQL會根據相應的最佳化策略最佳化關聯的順序，使遍曆多表達到較好的效果

查看重構查詢

對該查詢執行

explain select * from t1;

之後執行

show warning;

排序最佳化   

1. 舊：讀取行指標和需要排序的欄位，對其 進行排序，然後在根據排序結果讀取所需要的行

2. 新：先讀取查詢所需要的所有的列，然後再根據給定列進行排序，最後直接返回排序結果

3. 比較：

• 優點：只需要一次順序IO，比起兩次IO（其中還有一次隨機IO）有了很大優勢

• 缺點：引入很多無用的列，白白佔用著記憶體，對排序無影響

檔案排序：

• 對每一個排序記錄都會分配一個足夠長的定長空間來存放，有時排序佔用的空間比資料存放區佔用的空間還要多

• 對於關聯查詢來數order by子句中所有的列都來自於第一個表，那麼MySQL在關聯第一張表的時候就進行檔案排序（Extra中可看到Using Filesort）

• 否則會先將關聯的結果放到暫存資料表中，然後在所有表都關聯完成後再進行檔案排序，5.6版本之後可以將Limit提前執行，避免了排序大量的資料到最後只需要很少的尷尬處境

MySQL查詢最佳化