最佳化 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 的結果即可。

參與討論

1 則留言

  1. >>1. 使用 :select

    改用 named_scope 做這個更方便

    named_scope : is_short, lambda { {:select => “id, title, description”} }

    Event.is_short.all

發佈留言

發表迴響