ADZ 學習筆記

Ruby/Rails, Startup, Life

rails 筆記 - awesome_nested_set 搭配 activerecord-mysql-index-hint

| Comments

最近拿需要做多階層的資料儲存,除了使用 activerecord association 做 自己關聯自己 外,還需要一些進階的查詢,例如跨階層、或查詢整條祖父樹。所以這時候只存 parent_id 就不夠用了,於是找了一套專門處理這種儲存方式的 gem 叫做 awesome_nested_set 這個 gem 在實際儲存資料時增加了欄位 rgt ltf,來記錄祖父、子孫樹的範圍區間,詳細說明可參考這個連結:

http://threebit.net/tutorials/nestedset/tutorial1.html

使用了這個 gem 將會有以下 API 可以做跨層的查詢:

rails c
@category = Category.find(3)

@category.self_and_ancestors # 查詢該筆 record 所有父親包含自己

=> "SELECT `categories`.* FROM `categories` 
WHERE (`categories`.`lft` >= 3) AND (`categories`.`lft` < 4)  
ORDER BY `categories`.`lft`"

@category.descendants # 查詢該筆 record 所有父親

=> "SELECT `categories`.* FROM `categories` 
WHERE (`categories`.`lft` >= 3) AND (`categories`.`lft` < 4) AND `categories`.`id` != 3  
ORDER BY `categories`.`lft`"

@category.self_and_ancestors # 查詢該筆 record 所有子孫包含自己

=> "SELECT `categories`.* FROM `categories` 
WHERE (`categories`.`lft` <= 3) AND (`categories`.`rgt` >= 4)  
ORDER BY `categories`.`lft`"

@category.ancestors # 查詢該筆 record 所有父親

=> "SELECT `categories`.* FROM `categories` 
WHERE (`categories`.`lft` <= 3) AND (`categories`.`rgt` >= 4) AND `categories`.`id` != 3 
ORDER BY `categories`.`lft`"

更多詳細 API: https://github.com/collectiveidea/awesome_nested_set/wiki/Awesome-nested-set-cheat-sheet#advanced-usage

MySQL Index

依照 README.md 上的說明,每次 insert 資料時都需要 query rgt 這個欄位,並且以上 query 都會需要搜尋 lft,於是分別建立了 rgt lft parent_id 這三個 index。

不過利用 explain 實際測試 self_and_ancestors descendants self_and_ancestors ancestors 後發現這些 query 根本不會用到我們設定的 index。雖然說 mysql 提供了 USE INDEX FORCE INDEX 讓我們可以強制、或建議 index 給 mysql,但 activerecord 內並沒有針對 mysql 實作這些功能。

於是找到一個 gem activerecord-mysql-index-hint,它擴充了 mysql 的 index scope 到 activerecord,使用方法如下:

rails c
@category = Category.find(3)
@category.self_and_ancestors.force_index(:index_categories_on_lft) # 強制指定 index

=> "SELECT `categories`.* FROM `categories` FORCE INDEX (`index_categories_on_lft`) 
WHERE (`categories`.`lft` <= 3) AND (`categories`.`rgt` >= 4) AND `categories`.`id` != 3 
ORDER BY `categories`.`lft`"

@category.ancestors.use_index(:index_categories_on_lft, :index_categories_on_rgt) # 建議 index

=> "SELECT `categories`.* FROM `categories` USE INDEX (`index_categories_on_lft`, `index_categories_on_rgt`) 
WHERE (`categories`.`lft` <= 3) AND (`categories`.`rgt` >= 4) AND `categories`.`id` != 3 
ORDER BY `categories`.`lft`"

為了最佳化這幾個 query,經過一番測試後,最後的 index 是這樣建:

migration
add_index  :categories, :parent_id
add_index  :categories, :rgt # for insertion (index_categories_on_rgt)

add_index  :categories, [:lft, :rgt] # for tree query (index_categories_on_lft_and_rgt)

一開始的時候為了方便 override 了 tree query 的 method:

app/model/category.rb
class Category < ActiveRecord::Base
  TREE_INDEX = :index_categories_on_lft_and_rgt
  def self_and_descendants(*)
    super.force_index(TREE_INDEX)
  end

  def descendants(*)
    super.force_index(TREE_INDEX)
  end

  def self_and_ancestors(*)
    super.force_index(TREE_INDEX)
  end

  def ancestors(*)
    super.force_index(TREE_INDEX)
  end 
end

不過之後馬上想到一個問題,如果我呼叫的是: @category.self_and_descendants.find(params[:id]) 這種情況應該使用 PRIMARY key 效能是最好的。但由於我們無法確定這些 query 後面會接其他 scope,所以應該用 use_index 取代 force_index

class Category < ActiveRecord::Base
  TREE_INDEX = :index_categories_on_lft_and_rgt
  def self_and_descendants(*)
    super.use_index(TREE_INDEX, :PRIMARY)
  end
  # .. (略)

end

這樣修改後,無論使用 @category.self_and_descendants@category.self_and_descendants.find(params[:id]) 都會使用速度最快的 index。

不過以上方法僅適用這個 case,因為該 table 的索引單純,只需要 :PRIMARY:index_categories_on_lft_and_rgt 則一即可。如果 index 比較複雜的情況,還需考量各種不同的 query。

最後檢查

由於 descendants 會用 id != 來過濾掉自己,怕 mysql 不夠聰明,有 != 就使用 PRIMARY key 造成效能低落,所以做了以下測試:

rails c
@category.descendants # 使用 :index_categories_on_lft_and_rgt

=> "SELECT `categories`.* FROM `categories` USE INDEX (`index_categories_on_lft_and_rgt`, `PRIMARY`) 
WHERE (`categories`.`lft` >= 3) AND (`categories`.`lft` < 4) AND `categories`.`id` != 3 
ORDER BY `categories`.`lft`"

@category.descendants.find(6) # 使用 PRIMARY KEY

=> "SELECT `categories`.* FROM `categories` USE INDEX (`index_categories_on_lft_and_rgt`, `PRIMARY`) 
WHERE (`categories`.`lft` >= 3) AND (`categories`.`lft` < 4) AND `categories`.`id` != 3 AND `categories`.`id` = 6  
ORDER BY `categories`.`lft`"

兩個 query 都正常使用預期的 index。

Comments

comments powered by Disqus