MySQL之查詢效能最佳化五（最佳化特定類型的查詢）

標籤：

本文將介紹如何最佳化特定類型的查詢。

1.最佳化count()查詢
count()彙總函式，以及如何最佳化使用了該函數的查詢，很可能是mysql中最容易被誤解的前10個話題之一

count() 是一個特殊的函數，有兩種非常不同的作用。它可以統計某個列值的數量，也可以統計行數。
統計列值 要求列值是非空的。(不統計null，即null值計數為0)

count()的另一個用處是統計結果集的行數。當mysql確認括弧的運算式值不可能為空白時，實際上就是統計
行數。最簡單的就是當我們使用count(*)的時候，這種情況下統配符*並不會像我們猜想的那樣擴充成所有
的列，實際上，它會忽略所有的列而直接統計所有的行數。

假如在同一個查詢中統計同一個列不同值得數量
select sum(if(color = ‘blue‘, 1, 0)) as BLUE, sum(if(color = ‘red‘, 1, 0)) as RED from items

select count(color= ‘blue‘ or null) blue, count(color= ‘red‘ or null) red, from items

2.最佳化關聯查詢
這個話題討論的比較頻繁，這裡需要特別提到的是：
確保on或者using子句中的列上有索引。在建立索引的時候就要考慮到關聯的順序。
當表A和表B用列c關聯的時候，如果最佳化器的關聯順序時B，A，那麼就不需要在B表的對應
列建立索引。一般來說，沒有其他理由，否則只需要在關聯順序中的第二個表的相應列上建立索引。

確保任何的group by 和order by 的運算式只涉及到一個表中的列。這樣mysql才有可能使用索引來最佳化這個過程

當升級mysql的時候需要注意：關聯文法，運算子優先順序等其他可能會發生變化的地方。

3.最佳化子查詢
儘可能用關聯代替。

4.最佳化group by 和 distinct
在很多情境下，mysql都使用同樣的方法最佳化這兩種查詢，事實上，mysql最佳化器會在內部處理的時候
相互轉化這兩類查詢。他們都可以使用索引來最佳化，這也是最有效最佳化方法。

最佳化 group by with rollup
分組查詢的一個變種就是要求mysql對返回的分組結果再做一次超級彙總。你可以使用with rollup
來實現這種最佳化。最好的辦法是儘可能將with rollup 功能轉移到應用程式處理。

5.最佳化limit和offset
在系統需要進行分頁操作的時候，我們通常會使用LIMIT加OFFSET的辦法來實現，同時加上合適order by子句。
如果有對應的索引，通常效率會不錯，否則，mysql需要做大量的檔案排序。

在OFFSET大的時候，查詢會很糟糕。要最佳化這種查詢，要麼是在頁面中限制分頁的數量，要麼最佳化大位移量查詢的效能。

最佳化此類分頁查詢最簡單的一個方法是使用索引覆蓋掃描，而不是查詢所有的列。
然後根據一次關聯操作再返回需要的列。在位移量很大的時候，這樣會大大提升效率：

mysql> SELECT film_id, description FROM sakila.film ORDER BY title LIMIT 50, 5;--最佳化後mysql> SELECT film.film_id, film.description　　-> FROM sakila.film　　-> INNER JOIN (　　-> SELECT film_id FROM sakila.film　　-> ORDER BY title LIMIT 50, 5　　-> ) AS lim USING(film_id);

這裡的"延遲查詢"將大大提升查詢效率，它讓mysql掃描儘可能少的頁面，擷取需要訪問的記錄後再
根據關聯列回原表查詢需要的列。這個技術也可以用於最佳化關聯查詢中的limit子句。

有時候也可以將limit查詢轉換為已知位置的查詢，讓mysql通過範圍掃描獲得到對應的結果。

6.最佳化 SQL_CALC_FOUND_ROWS
分頁的時候，另一個常用的技巧是在limit語句中加上SQL_CALC_FOUND_ROWS提示(hint),
這樣就可以擷取去掉limit以後滿足條件的行數，因此可以作為分頁的總數。看來，mysql做了
非常"高深"的最佳化，像是通過某種方法預測了總行數。但實際上，mysql只有再掃描所有滿足條件的行
以後才知道行數，所以加上這個提示以後，不管是否需要，mysql都會掃描所有滿足條件的行，然後再
拋棄不需要的行，而不是在滿足limit的行數後就終止掃描。所以該提示的代價可能非常高。

7.最佳化union查詢
mysql總是通過建立並填充暫存資料表的方式來執行union查詢。因此很多最佳化策略在union查詢中都沒法
很好的使用。經常需要手工地將where，limit，order by等子句下推到union的各個子查詢中，以便
最佳化器可以充分利用這些條件進行最佳化。

除非確實需要伺服器消除重複的行，否則一定要使用union all ，這一點很重要。
如果沒有ALL關鍵字，mysql會給暫存資料表加上distinct選項，這會導致對整個暫存資料表的
資料唯一檢查。這樣的代價非常高。即使有ALL關鍵字，mysql仍然會使用暫存資料表儲存結果。
事實上，mysql總是將結果放入暫存資料表，然後再讀出，再返回給用戶端，很多時候這樣做是沒有必要的。

8.靜態查詢分析

9.使用使用者自訂變數
set @one := 1

MySQL之查詢效能最佳化五（最佳化特定類型的查詢）