15個優化你的sql Query的方式

http://blog.davidou.org/archives/609

參考用~~~~

這邊先介紹15個優化你的sql Query的方式

1.Indexes

對你的欄位做Index優化是非常常見的優化方式，儘管如此，你還是必須完全的了解indexing在資料庫裡面是如何運作的以便充分的索引。不然濫用index而不懂他是怎運作的會造成反效果。

2.Symbol Operator

我們常用 >,<,=,!= 等的運算符號在我們的查詢裡 ，我們可以透過已經建立過index索引的欄位來加快查詢的速度。例如：

SELECT * FROM TABLE WHERE COLUMN > 16
現在，上面這個查詢式沒優化的。因為DBMS必須去找所有大於小於16的所有資料

。所以我們可以把它改寫成如下：

SELECT * FROM TABLE WHERE COLUMN >= 15
這樣的話DBMS就會直接跳到15去，然後直接找比15小的出來。

3.Wildcard

在sql裡面 我們提供了％來做模糊查詢，然而這個％的查詢會拖慢你的查詢速度，特別是當你的table非常大的時候。我們可以透過前後縋的方式優化我們的查詢而不要全部都使用，例如：

#Full wildcard
SELECT * FROM TABLE WHERE COLUMN LIKE ‘%hello%’;
#Postfix wildcard
SELECT * FROM TABLE WHERE COLUMN LIKE ‘hello%’;
#Prefix wildcard
SELECT * FROM TABLE WHERE COLUMN LIKE ‘%hello’;

這個欄位必須要做index才比較會有效果

ps對一個百萬條資料的table做Full wildcard會killing這個資料庫

4.NOT Operator

嘗試去避免使用 NOT在你的sql裡面，用正向的的方式查詢會快很多。例如用 LIKE, IN, EXIST or = 符號 而不要使用 NOT LIKE, NOT IN, NOT EXIST or != 符號。使用反向符號會導致資料庫會去搜尋每一筆資料去確定他都是真的不屬於或不存在這個table。相反地，假如有這筆資料的話，用正向的方式查詢會直接的跳到這個查詢結果。想像一下，假設你有一百萬筆資料在這個table的話 那GG拉。

5.COUNT VS EXIST

我們常會用 COUNT 去查詢有多少筆的資料存在

1 SELECT COLUMN FROM TABLE WHERE COUNT(COLUMN) > 0
同樣的，這是一個非常差的查詢，因為count會搜尋「所有」存在這個表上的紀錄，所以比較好的方法式寫EXIST ，這個他只要一旦找到就會停止，當然，他是存在的（exist）。

6.Wildcard VS Substr

假如要查詢特定的索引的話，如果那個欄位有做index 那麼最好使用 wildcard 而不是substr 例如

[不好的]SELECT * FROM TABLE WHERE  substr ( COLUMN, 1, 1 ) = 'value'.
上面這個查詢會去substr每一筆資料就為了找‘value’這個字串，而

[好的] SELECT * FROM TABLE WHERE  COLUMN = 'value%'.
Wildcard 會找的比較快，假如value是在每一筆資料的最前面的話

7.Index Unique Column

有些資料庫例如 MySQL對於unique 和indexed的欄位搜尋速度比較好。因此假如這個欄位是unique的，最好記得對他們做index。但是要是那個欄位根本就沒有搜尋的必要的話，就不要做index，縱使他們是unique的。

8.Max and Min Operators

Max 和 Min是為了找到最大跟最小值用的，我們在已經做index的欄位使用他們兩個的話，速度會很快。但是假如這個欄位只想要查詢最大跟最小值的話，就不要去做index了 這不划算，就好比為了一棵樹而放棄整座森林一樣。為了他而讓整個資料庫效能降低。

9. Types

盡量使用最有效（最小的）資料型態。用太大的資料型態去存很小的資料是不必要甚至是危險的。用較小的資料型態可以得到比較小的table空間。例如用MEDIUMINT通常就比用INT好，因為MEDIUMINT少了25%的空間，還有在儲存email 或是少數資料時VARCHAR還是比 longtext 好。

10.Primary Index

主要的Primary欄位通常都用來做index用的，所以盡可能的讓他越小越好。這讓DBMS能更簡單有效的去查詢每一筆資料。

11.String indexing

假如字串他有前後縋詞可以被index的話就不需要對整個字串index。特別是他的前後字串提供一個唯一的識別時。越少的index速度會越快。除了減少硬碟空間之外，還可以提供你更多的index cache使用還有更少的硬碟資料查詢時間

12.Limit The Result

另一個很常優化的方式就是去限定查詢回來的筆數，假如你的table有個幾十億的資料，幾個簡單的查詢就可以攤掉你整個資料庫了

  SELECT * FROM TABLE
因此，不要偷懶不想去限制回傳筆數，在另一方面 在遇到 SQL injection 攻擊時可以降低損害的程度

  SELECT * FROM TABLE WHERE 1 LIMIT 10
13.Use Default Value

如果你使用MySQL時，盡量使用Default值，Insert values 只有當值跟DEfalut不一樣時才用，在mysql裡，這樣可以減少一次解析的時間並增快寫入的速度。

14.In Subquery

我們常用下列in的子查詢來查詢資料

1 SELECT * FROM TABLE WHERE COLUMN IN (SELECT COLUMN FROM TABLE)
這樣做是很號資源的，因為sql會先處理內部查詢，查完才做外面的查詢

1 SELECT * FROM TABLE, (SELECT COLUMN FROM TABLE) as dummytable WHERE dummytable.COLUMN = TABLE.COLUMN;
使用一個自定的表會比用in還要來的好 另外 用 exist 也不錯

15.Utilize Union instead of OR

在MySQL裡面，用了or 會讓整個查詢的速度優勢不見，就算做index也沒啥太大的效用

  SELECT * FROM TABLE WHERE COLUMN_A = 'value' OR COLUMN_B ='value'
我們可以把上面透過union改寫成下面這樣

  SELECT * FROM TABLE WHERE COLUMN_A = 'value'
  UNION
  SELECT * FROM TABLE WHERE COLUMN_B = 'value'
這樣跑比較快

總結

總歸來說，上面這些優化並不能保證你得查詢不會成為系統的瓶頸，這需要更多的剖析研究。然而上述的簡單優化方式，至少可以幫你很多

文章來源：http://hungred.com/useful-information/ways-optimize-sql-queries/

註：我是看文章重點翻譯的，所以部分形容描述就沒翻了。而關於第14點其實真的會差很多，我曾經因為為了一個同樣的查詢結果從in改成join 查詢時間從22秒瞬間下降到0.05秒而已