從 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 佔用的記憶體，跟傳輸消耗的時間。