[加快SQL 搜尋] SQL查詢時 當資料量過大的時候，如果直接下ORDER BY 系統會跑很慢，該如何改善呢？ [GOOGLE:SQL 查詢 慢]

資料來源:http://expect7.pixnet.net/blog/post/42409147

在MySQL 中一般我門直接下搜尋，通常都是直接下 SELECT * FROM table WHERE colnumA=”條件” ORDER BY  colnumA。

這樣的搜尋在少量的資料是沒有任何問題，但是當你全部的資料有有兩百萬筆去搜尋的時候，你會發現資料跑得很慢。

當你下ORDER BY的時候，資料庫會把全部兩百萬筆先做排序。排序完成之後，再來比對是否有你要的資料。在列出你要的資料。

查詢資料並不慢，但是會慢的重點在於排序，就可能占了70%的時間。

但是跟同事討論，也拿公司系統資料來做測試，有了以下的結論。

SELECT * FROM table WHERE colnumA=”條件” ORDER BY colnumA

單純直接查詢，大約花了18s。但是改用另一個方法。

SELECT A.* FROM (SELECT * FROM  table WHERE colnumA=”條件”….多個條件 ) AS A ORDER BY A.colnumA

就是先把結果查詢出來，可能查到的資料剩下1萬筆。那這樣的速度會比原來的速度快。

但是還有另一種方法，很奇妙，速度更快。

SELECT  * FROM (SELECT * FROM table WHERE 1) AS T INNER JOIN (SELECT 主鍵 FROM  table WHERE colnumA=”條件” ……) AS S ON T.主鍵=S.主鍵 ORDER BY  T.colnumA

用INNER JOIN 就感覺上，他做了總共做了三次搜尋，應該是更慢才對，可是我會說他很妙是因為，實測之後，他的速度是最快的。

 

在資料量龐大約200萬筆，下相同的條件查詢。

SELECT * FROM table WHERE colnumA=”條件”…多個條件 ORDER BY colnumA    花費時間平均19 s

SELECT A.* FROM (SELECT * FROM table WHERE colnumA=”條件”….多個條件 ) AS A ORDER BY A.colnumA 花費時間平均 8s

SELECT * FROM (SELECT * FROM table WHERE 1) AS T INNER JOIN (SELECT 主鍵 FROM table WHERE colnumA=”條件” ……) AS S ON T.主鍵=S.主鍵 ORDER BY T.colnumA  花費時間平均 7.5s

 

當然這不是唯一解！但是卻是可以參考的資料！