10/21/2006

從 SQL 思考 ActiveRecord 實做方式

有些時候我在想,到底 Active Record 底層是怎麼幹的呢?在使用方便之餘,有沒有一些底層的作法是不好的呢?今天就特別花點時間去看看到底 Active Record 是怎麼搞的,我先測試看看最多人使用的擷取(Retrieve)功能。

假設有幾個 Model :User,Blog,info,friends。 User 跟 Info 呈現 1:1 關係,User跟Blog呈現 1:n 關係,User跟 Friend 呈現 n:m 關係。所有的Model設定都採取預設值,所有關係都設定OK了。

單獨取一個 entry :
如果我們下達這樣的指令 User.find(1) ,出現的 SQL 是這樣的
SELECT * FROM users WHERE (users.id = 1) LIMIT 1
看到這個SQL,我就對整體效率放心了。因為我本來還以為 Active Record 有可能這個可能性一次 select 出所有相關的 table
SELECT * FROM users WHERE (users.id = 1) LIMIT 1
SELECT * FROM infos WHERE (infos.user_id = 1)
SELECT * FROM blogs WHERE (blogs.user_id = 1)
..........
如果真的這樣作會超級耗 memory ,但是好家在 Active Record 只會 select 有用到的 relation table 。所有的 relation ship 都是有用到才會去 select ,所以我可以盡情的使用 relationship,反正只有需要的時候才會去 call DB。

SQL injection
如果下達 User.find_by_email(" 'abc@abc.com") 這樣SQL injection 的用法,我們發現到 SQL 會變成
SELECT * FROM users WHERE (users.`email` = '\'abc@abc.com' ) LIMIT 1
也就是使用 find_by 是會自動跳脫 SQL 危險符號,值得鼓勵。

以後都在已經使用 a = User.find(1) 的前提下進行。

取出 1:1 relation
如果我們下達這樣的指令 a.info ,出現的 SQL
SELECT * FROM infos WHERE (infos.user_id = 1) LIMIT 1;
不使用 join 而是使用兩個 select 。

取出 1:m relation
如果我們下達這樣的指令 a.blogs ,出現的 SQL 是這樣的
SELECT * FROM blogs WHERE (blogs.user_id = 1)
也是不使用 join 而是使用兩個 select 。

取出 n:m relation
如果我們下達這樣的指令 a.friends ,出現的 SQL 是這樣的
SHOW FIELDS FROM friends
SELECT * FROM users INNER JOIN friends ON users.id = friends.friend_user_id WHERE (friends.user_id = 1 )

他一開始先作 User.find(1) 的工作,然後使用一個 inner join 來表達 n:m 的關係。

當 column 有 BLOB 欄位時
譬如 User 這個 Model ,User 裡面有 photo 這個 Blob column(將圖片存到資料庫),以下是 irb 出現的狀況
>> a = User.find(11)
=> #"\000\004\000\001\001\000@ \000\000\000\002\000\000\000\000\000 .......... 很多頁" , "name"=>"a", "id"=>"11", "email"=>"b"}
Active Record 出現的SQL 是
SELECT * FROM users WHERE (users.id = 11) LIMIT 1
一般來說我們遇到這個欄位都會使用
SELECT name , email  FROM users WHERE (users.id = 11) LIMIT 1
特別指定某些欄位的方式來擷取資料,原因是為了沒用到 BLOB的時候,避免擷取 BLOB 太花時間了。

結語
從上面的結論來看,Active Record 處理各種關係的 擷取(Retrive)的底層作法都不會太差,並且他會根據等到使用到的 relation 才會進行 DB access,而不是一次將所有的 relation table 取出來。

第一個可能遇到的問題在於,畢竟Active Record 是跨DB的pattern ,無法做到針對個別 DB 作最佳化。不過我們還是可以對效能瓶頸的SQL,用
find_by_sql('最佳化的SQL')
來作最佳化的工作。不過這是確定瓶頸在於某個 SQL query 的情況下才需要作的事情。如果每個都要寫 SQL 那還不如回去用PHP。

另外一個可能遇到的問題是因為 Active Record 為了達到方便的用途,直接使用 select * 這樣的作法。當你的 DB column 裡面有 BLOB 這樣的欄位時,不管對 DB 資料傳輸,或是整個 Model 佔用 memory 消耗都會有嚴重的影響。

如果可以的話,盡量將 BLOB 這樣的欄位跟原來的 table 分離成另外一個 table ,並且跟原 table 作 1:1 的關係,等到真的有用到這個欄位時,再去取出這個 table 較好。像是上面的例子,雖然很多頁面都會用到 User 這個 Model ,但是幾乎絕大部份沒有使用到 photo 的屬性。所以我們可以獨立出一個 Photo Model ,跟 User 呈現 1:1 關係,等到有用到的時候再使用 User.photo 這樣的作法來使用。這樣可以有效率的減少 photo 佔用的記憶體,跟傳輸消耗的時間。

2 則留言:

匿名 提到...

相当棒!受益了!感谢!

RainChen 提到...

如何最简单的在页面中查看执行过的SQL呢?