Database Query 最佳化實記

兩三個月前遇到一個嚴重拖垮公司資料庫的query,好死不死那個API又是流量主要會出現的點,身為社畜只好想辦法把它解決掉,就這樣我看了一整周的db console和mysql手冊,最後想出一個只需最小修改就能達成的最佳化方案

先說明一下問題點的架構,這邊的設計以正規化來說我是覺得沒問題的

食譜和分類是傳統的多對多關係,因此會有一張中間表;分類本身則是有自我關聯的上下層關係

應用上的需求是這樣的:給定某個分類,找出該分類(置頂與不置頂)及其子分類(不置頂)下的所有食譜(不能重複),並以「是否置頂」和「發表時間」進行排序

系統原設計是先抓出所有分類ID(某分類及其子分類)後,才進行傳統的join去從分類ID找食譜,這部分因為分類數量不會很大,所以我認為還堪用就不進行修改

真正的瓶頸在後面,當時query長這個樣子

SELECT DISTINCT `recipes`.*
FROM `recipes`
INNER JOIN `category_recipes`
ON `category_recipes`.`recipe_id` = `recipes`.`id`
WHERE ((category_recipes.category_id IN ({category_and_descendants_ids})) AND category_recipes.is_sticky <> 1) OR (category_recipes.category_id = {category_id} AND category_recipes.is_sticky = 1)
ORDER BY category_recipes.is_sticky DESC, recipes.published_at DESC
LIMIT 10
OFFSET 0

一開始的想法是order由兩張表的欄位組成,照手冊的說法不會利用到index,所以試著在category_recipes上面做非正規化,加了一個published_at,值則是利用model callback從recipe同步過來

SELECT DISTINCT `recipes`.*
FROM `recipes`
INNER JOIN `category_recipes`
ON `category_recipes`.`recipe_id` = `recipes`.`id`
WHERE ((category_recipes.category_id IN ({category_and_descendants_ids})) AND category_recipes.is_sticky <> 1) OR (category_recipes.category_id = {category_id} AND category_recipes.is_sticky = 1)
ORDER BY category_recipes.is_sticky DESC, category_recipes.published_at DESC
LIMIT 10
OFFSET 0

可惜這樣跑起來還是不快

後來想到distinct比較的是所有欄位,但index裡面不可能有所有欄位,就往group的方向去思考

反正只要id是唯一的,得到的東西就不會重複

於是query變成這樣

SELECT `recipes`.*
FROM `recipes`
INNER JOIN `category_recipes`
ON `category_recipes`.`recipe_id` = `recipes`.`id`
WHERE ((category_recipes.category_id IN ({category_and_descendants_ids})) AND category_recipes.is_sticky <> 1) OR (category_recipes.category_id = {category_id} AND category_recipes.is_sticky = 1)
GROUP BY category_recipes.recipe_id DESC
ORDER BY category_recipes.is_sticky DESC, category_recipes.published_at DESC
LIMIT 10
OFFSET 0

雖然感覺方向對了,但效能還是沒到理想的狀態

手冊又翻了半天才理解出:
- index 的利用是從左到右
- GROUP BY 和 ORDER BY 共用同一個 index
- GROUP BY 和 ORDER BY 如果有任何一個方向不同就不能用 index

如果能在recipe_id之前用is_sticky和published_at做group的話不是就能用到index了?

很幸運的實驗有成功,資料結果和排序也正確

query最終變成了這樣

SELECT `recipes`.*
FROM `recipes`
INNER JOIN `category_recipes`
ON `category_recipes`.`recipe_id` = `recipes`.`id`
WHERE ((category_recipes.category_id IN ({category_and_descendants_ids})) AND category_recipes.is_sticky <> 1) OR (category_recipes.category_id = {category_id} AND category_recipes.is_sticky = 1)
GROUP BY category_recipes.is_sticky DESC, category_recipes.published_at DESC, category_recipes.recipe_id DESC
ORDER BY category_recipes.is_sticky DESC, category_recipes.published_at DESC
LIMIT 10
OFFSET 0

index的部分我試出這幾種,第一種是效率最高的,雖然只能用到OFFSET 330左右但很夠了

OFFSET如果更大的話,資料庫會選擇其他兩種並建立暫存表和使用filesort

CREATE INDEX {index_name} ON category_recipes(category_id, recipe_id, is_sticky, published_at);
CREATE INDEX {index_name} ON category_recipes(is_sticky, published_at, category_id, recipe_id);
CREATE INDEX {index_name} ON category_recipes(is_sticky, published_at, recipe_id, category_id);

到這邊總算解決query時的效能問題...以為就這樣結束了嗎?

改完後發現分頁套件(kaminari)在計算總數的時候會觸發ActiveRecord組錯SQL的現象,一查才發現是它不支援descending count,原本還想去修,但我才發現group根本不能拿來count我要的東西

# 舉個例子
User.group('email DESC').limit(50).count

# 產生的SQL如下,但語法是錯誤的
# SELECT COUNT(*) AS count_all, email DESC AS email_desc
# FROM `users`
# WHERE `users`.`deleted_at` IS NULL
# GROUP BY email DESC
# LIMIT 50

為什麼?因為我想count的是總數,並不是group完分別的數字,因此計算總數的時候還是要回去用distinct

這邊有個比較簡單的想法,因為ActiveRecord的count屬於terminal method(終點站),所以可以直接在上面做切換機制

但問題又來了,ActiveRecord的語法是用串的,我要怎麼知道先前有串過group?而且不是所有group都需要換回去distinct,該怎麼分辨?

後來想到的方法是透過SQL comment傳遞訊息,直接在group裡面給字串的時候插一個特定的comment進去,再由count那邊去判斷就好了

Recipe.published.joins(:category_recipes).
  where('((category_recipes.category_id IN (?)) AND category_recipes.is_sticky <> 1) OR (category_recipes.category_id = ? AND category_recipes.is_sticky = 1)', {category_and_descendants_ids}, {category_id}).
  group('category_recipes.is_sticky DESC, category_recipes.published_at DESC, category_recipes.recipe_id DESC /*use_distinct_instead_of_group_for_count*/').
  reorder('category_recipes.is_sticky DESC, category_recipes.published_at DESC').
  page(1).per(10)
ActiveSupport.on_load(:active_record) do
  module ActiveRecord
    class Relation
      alias_method :original_count, :count
      def count(column_name = nil)
        if to_sql =~ /use_distinct_instead_of_group_for_count/i
          unscope(:group).distinct.original_count(column_name)
        else
          original_count(column_name)
        end
      end
    end
  end
end

到這邊才成功解決這個query的效能問題,資料庫CPU使用率和反應速度都有大幅改進,使用者終於不用等到斷線了QQ

留言

粗體斜體刪除線連結引用圖片程式碼

注意:您的電子信箱將不會被公開,且網站連結不會被搜尋引擎採計

{124} {123} {122} {121} {120} {119} {118} {117} {116} {115} {114} {113} {112} {111} {100} {025} {024} {023} {022} {021} {020} {019} {018} {017} {016} {015} {014} {013} {012} {011} {010} {009} {008} {007} {006} {005} {004} {003} {002} {001}