Stan Fast: sql

Friday, June 17, 2011

Another Note:

From

善用 sub query 優化 order by limit

假設 , 我們有個 Table 叫 test , 有 20 萬筆記錄 , test 有 20 個欄位好了 , 其中有一個欄位 post_time 有建立索引可以排序用的 , 而有個主鍵 id

下一道指令

SELECT * FROM test ORDER BY post_time DESC LIMIT 150000,10

這道指令看似簡單 , 理論上應該跑很快 ~ 實際上卻不然 , 也許跑上 20 秒 , 因為這牽扯到 MySQL Server 的 sort buffer 大小 , 因為這道指令可能會因為 buffer 不足而改用 disk 作 sort , 這樣就很慢了

這時候就要搬出 subquery 了 , 怎麼作 ? 看看下面

SELECT * FROM test  AS t INNER JOIN ( SELECT id FROM test ORDER BY post_time DESC LIMIT 15000,10 ) AS s ON t.id=s.id

我相信 , 這道指令應該 1 秒都不用

為何會如此呢 ?

因為我們在 subquery 中只有抓 id , 這樣 mysql 就只需要少量記憶體就可以存放資料列進行排序 , 原始的作法就是每一列有多少資料 , mysql 就必須分配多少記憶體 , 如果資料數太大 , 記憶體不夠分配 , 自然就是用 disk 了

但如果我們要抓的資料只是前面幾筆 , 例如 LIMIT 100,10 , 那麼原本的寫法會比較快 , 因此這可以在程式中判斷而交互使用兩種 QUERY 的方式