Mysql語句最佳化

標籤：最佳化   regex   loop   sts   建立   ext   查看   tail   速度   

mysql中的in語句是把外表和內表作hash 串連，而exists語句是對外表作loop迴圈，每次loop迴圈再對內表進行查詢。exists與in效能的好壞不能一概而論，要根據表的大小，索引來決定。

一．

如果查詢的兩個表大小相當，那麼用in和exists差別不大。 

如果兩個表中一個較小，一個是大表，則子查詢表大的用exists，子查詢表小的用in： 

例如：表A（小表），表B（大表）

1：

select * from A where cc in (select cc from B) 效率低，用到了A表上cc列的索引。

上面的查詢首先會從B中查出所有的cc，因為B表是大表，所以會select cc from B會產生許多的cc（而且會全表掃描B表），然後這些cc值按照順序一個一個從A表取出相應的記錄，所以只會用到A表的索引，主要的開銷在全表掃描B上。

select * from A where exists(select cc from B where cc=A.cc) 效率高，用到了B表上cc列的索引。 

上面的查詢首先會全表掃描A，得到A的所有記錄，然後用每條記錄中的cc值作為查詢條件在B表中查詢，由於有索引，所以查詢很快，避免的大表的全表掃描，只掃描了小表A，因此效率比較高。

2：

select * from B where cc in (select cc from A) 效率高，用到了B表上cc列的索引；

同樣，上述查詢只全表掃描了A，大表查詢用的是索引。

select * from B where exists(select cc from A where cc=B.cc) 效率低，用到了A表上cc列的索引。 

當A和B表在cc列上都沒有索引時，in和exists都需要全表掃描A表和B表，查詢速度差別不大。

 not in 和not exists如果查詢語句使用了not in 那麼內外表都進行全表掃描，沒有用到索引；而not extsts 的子查詢依然能用到表上的索引。所以無論那個表大，用not exists都比not in要快。

 

二．最佳化group by和order by

分組和排序算是mysql中比較消耗cpu的操作，當我們用explain工具來查看sql時，往往會看到Extra那一欄都會出現using temporary，using filesort（暫存資料表和檔案排序），因為得到的資料並非是已經分組的和排序的，需要額外的空間來進行分組排序，我們可以通過建立合適的索引來最佳化，因為索引是已經排序的。舉例說明：

假設有表A，有如下幾條資料：欄位是a（主鍵），b，c，d。

a                 b                   c                         d

1                 1                   2                         3

2                 2                   3                         1

3                 1                   2                         2

4                 1                   2                         5

5                 1                   2                         4

我們執行select * from A where b<2 order by d desc

因為b欄位沒有索引，所以需要全表掃描A，那麼掃描過後得到資料

1                 1                   2                         3

3                 1                   2                         2

4                 1                   2                         5

5                 1                   2                         4

此時d欄位是亂序的，那麼mysql需要臨時開闢一段記憶體空間（暫存資料表）來儲存掃描後的結果，然後對d欄位進行排序（檔案排序）。

如果我們在d欄位加上索引，那麼可以按照索引的順序來掃描全表，掃描後的結果就是排序的結果。

d欄位的索引是這樣的：

5  4

4  5

3  1

2  3

1      2

上面每一行的前一個數是d欄位的值，後一個欄位為d欄位所在行的主鍵a的值。按照d欄位索引的順序掃描全表，首先掃描a=4的行，發現這一行的b<2，滿足條件，則保留

4                 1                   2                         5

接下來掃描a=5的行，發現這一行的b<2，滿足條件，則保留

4                 1                   2                         5

5                 1                   2                         4

。。。。。。。。。

接下來掃描a=2的行，發現這一行的b=2，不滿足，則捨棄

4                 1                   2                         5

5                 1                   2                         4

1                 1                   2                         3

3                 1                   2                         2

掃描過後的記錄就是合格記錄，不需要暫存資料表和排序。對於gropup來說，也可以用這樣的方法進行最佳化，因為欄位已經排序，可以直接在獲得的記錄中進行分組計算。

三．

幾個原則：

1. 小表（記錄在幾萬以內）查詢，無論有無索引，查詢效能相差不大。
2. 和大表做join，關聯欄位上一定要加索引
3. join操作一般用小表驅動大表
4. 盡量不要使用like，regex，or
5. 盡量不要在查詢的列上做計算，如where length(detail)>15 detail欄位的長度大於15，這種條件都會進行全表掃描，即使detail有索引。
6. 盡量不給大欄位做索引，比如 detail varchar(256)，這種索引耗費cpu，耗費空間，而且查詢效率低。如果業務允許的話，可以建個首碼索引。

Mysql語句最佳化