[技術觀念] Full Table Scan

分類: 技術分享 作者: jaceju

10 1 月 2009

前陣子在 B&Q 遇到了資料庫效能低落的問題,跟客戶一起檢查了一下程式碼,發現我們寫的程式有 Full Table Scan 的問題。

什麼是 Full Table Scan ?簡單來說,就是當在 Table 查詢資料時所下的 SQL 語法沒有用到索引 (Index Scan) ,導致 SQL 引擎要掃瞄整個 Table 的資料。

在資料量少時, Full Table Scan 還不致於影響程式效能;一旦在 Table 中的資料筆數累積到以萬計算時,這時 Full Table Scan 就會開始露出它猙獰的表情了。

註:詳細觀念可以參考此文: Full Table Scan 。裡面有提到很多重要的觀念,尤其是 Full Table Scan 不一定就是邪惡的代表。

問題描述

以我們的程式為例,原本我們希望透過 identity 這個欄位來取得對應的唯一使用者:

$sql = "SELECT * FROM members WHERE identity = '$identity'"; // 這裡的 $identity 有先做 escape 

不過因為我們的 identity 有經過編碼,在某些特殊狀況下會無法被正確抓出,所以後來我們就改用 md5 來判斷:

$identityMD5 = md5($identity);
$sql = "SELECT * FROM members WHERE MD5(identity) = '$identityMD5'"; 

但這樣一來,我們就發現整個資料庫的效能變得異常低落,因為 SQL 語法裡的 MD5 造成了 Full Table Scan !

原因就是我們無法對 SQL 函式所計算出來的值做索引,使得 SQL 引擎要一筆一筆去計算每個 identity 的 MD5 值,然後再跟 $identityMD5 變數做比對。而且 members 的資料已經高達十來萬筆,而上述的語法又幾乎每個頁面都會用到,這樣效能怎會不低落呢?

解決方案

要避免這樣的 Full Table Scan ,我們就要儘量不在 SQL 語法裡做函式計算,尤其是資料量非常大的 Table 。

而且我們也要儘量利用現成的索引,或是想辦法在計算的結果上建立索引。

因此在我們的例子裡,我們多新增一個 identityMD5 的欄位,來直接存放預先以 md5 編碼過的 identity 值,然後對 identityMD5 做索引。

所以我們的程式修改如下:

$identityMD5 = md5($identity);
$sql = "SELECT * FROM members WHERE identityMD5 = '$identityMD5'"; 

然後我們利用 EXPLAIN 指令,觀察該 SQL 語法是真的有用到新建立的索引,也因此大大節省了我們查詢上的時間。

感想

製作資料量非常大的 EC 網站時,資料庫的效能不可置否地是非常重要的一環。任何錯誤的查詢方式,都有可能導致效能低落的狀況發生。

這次的調整,不僅讓我們學到了 Full Table Scan 的原理,也讓我們知道了如何撰寫效率較高的 SQL 語法。

希望這次的經驗,能幫助公司伙伴在處理 SQL 語法時,能有更深一層的體認。

我要留言

關於這裡

這個部落格分享了哇寶在電子商務領域的技術及資訊,希望能讓更多人一起為台灣的網路產業加油。