最佳化 ActiveRecord SQL 查詢

Update(2008/9/5) 補充 named_scope 也可以用在 :select，感謝 tsechingho++

要看 AciveRecord 產生的 SQL 是什麼，除了可以直接 tail -f log/development.log 之外，也可以在 script/console 的情況下透過以下指令直接把 log 直接顯示出來：

ActiveRecord::Base.logger = Logger.new(STDOUT)
ActiveRecord::Base.clear_active_connections!

如此便可以好好在 console 的環境下實驗 ActiveRecord 囉。

我看到關於 ActiveRecord SQL 查詢最佳化方式有這幾種：

1. 使用 :select

只撈需要用到的欄位，特別是如果不需要用到的 text 或 binary 欄位請排除。

Event.find(:all, :select => "id, title, description")


搭配 named_scope 我們可以把常用的 :select 預先設定好，例如：

class User < ActiveRecord::Base named_scope :short, :select => "id, name, email"
end

User.short.find(:all)


2. 使用 :include

使用 :include 避免 N+1 次 queries 的問題。


@events = Event.find(:all, :include => [:group] )



@events.each do |e|
e.group.title
end


如果沒有加 :include 把相關的 groups 一起載入，在迴圈中就會產生 @event.size 次對 group 的個別 SQL 查詢，會非常傷。加了 :include 之後總共只需查詢兩次。

另外一個比較少人知道的是，在設定 Model associations 時，如果有很明顯的情境一定會順道載入二階 association model，可以設定 :include 在 has_many, belongs_to, has_one 上面，例如：


class User < ActiveRecord::Base has_one :foo, :include => [:bar]
end


如此便會在載入 @user.foo 的同時，也會提早載入 @user.foo.bar。

3. 資料庫索引

針對 foreign key 要加上資料庫索引 index。在 migration 上透過 add_index 就可以加上去了。

4. 特定情況下可用 :joins 取代 :include

在只需要用到 :conditions 而不需要載入該 model 的情況下，可以用 :joins 取代 :include。

Group.find(:all, :include => [ :group_memberships ], :conditions => [ "group_memberships.created_at > ?", Time.now - 30.days ] )

因為其中會載入 group_memberships model 只是為了加條件式，而沒有要撈出裡面的資料，所以可以改用 :joins，這其中的差異你看產生出的SQL就知道了 :)


Group.find(:all, :joins => [ :group_memberships ], :conditions => [ "group_memberships.created_at > ?", Time.now - 30.days ] )


5. 自己寫 SQL

ActiveRecord 可以直接寫 find_by_sql。

6. denormalization 逆正規化

當資料非常多，又要常常查詢其中計算的結果，這時可以考慮使用逆正規化的手法，將計算的結果也當做資料存起來。

一個最常見最基本的用法就是計算總數了，例如以下的例子可以解決需要常常查詢 @topic.posts.size 該篇主題有多少文章的情境：


class Topic < ActiveRecord::Base has_many :posts end class Posts < ActiveRecord::Base belongs_to :topic, :counter_cache => true
end


我們會在 Topic model 新增一個欄位 posts_count，然後 :counter_cache 就會幫我們在新增刪除 Post 時，自動更新所屬 Topic 的 posts_count 欄位資料。這時只要打 @topic.posts.size 就會直接回傳 posts_count，而無需每次都再發一個 SQL query 去 count 有多少筆 posts。

其他逆正規化的方式也包括了統計結算報表等，我們把需要複雜計算的的結果(可以透過非同步的機制如 cron 定期去觸發) 存到 Report model，這樣使用者直接撈 Report 的結果即可。