ORM 陷阱

Max發表於2019-03-01
ORM

產品實現中有一類常見的需求是,取出一組資料, 且這組資料中的每一項都攜帶固定數量的關聯資料.

如取出一組熱門作者及他們最近發表的3篇文章

但在MySQL(ORM)中這種需求並不能比較完美的實現.

你看到這種需求的第一眼可能會想到這麼寫

users: id

posts: id,user_id

user 通過外來鍵user_id 一對多關聯 post

$users = \App\Models\User::with(['posts' => function ($query) {
    $query->limit(3);
}])->limit(10)->get();

但這並不能滿足該需求,並且這是一種錯誤的寫法. 這裡的期望是10個作者,每個作者取出3篇帖子,一共取出了30篇帖子,實際生成的sql語句是

select
  *
from
  `posts`
where
  `posts`.`user_id` in (1, 2, 3, 4, 5, 6, 7, 8, 9, 10)
limit
  3

你應該一眼就看出了不可行的原因.雖然知道了這樣不可行,但是仔細去想卻發現,自己也想不出不出滿足這個需求的sql語句,mysql並沒有類似each_limit的語法.

sql大佬請忽略我這句話!!

問題與陷阱應該闡述的比較清楚了,接下來看看幾個可行的解決方案

PLAN A

使用 N + 1的sql查詢方案. 

$users = \App\Models\User::limit(10)->get();

$users = $users->map(function ($user) {
    //可以考慮$user->id快取,在保證了速度的同時避免大面積的快取重建
    $user->posts = $user->posts()->limit(3)->get();

    return $user;
});

return $users;

這種做法的一個好處是思路足夠簡單直白,沒有複雜sql.在快取的加持下可以避免N+1問題.

PLAN B

對PLAN A稍微修改一下就構成PLAN B, 既UNION ALL解決方案. 通過mysql的UNION ALL將上面需要進行的10次查詢聯合成一次查詢.

$users = \App\Models\User::limit(10)->get();

// 拼接 union all
$posts = $users->map(function ($user) {

    return $user->posts()->limit(3);

})->reduce(function ($carry, $query) {

    return $carry ? $carry->unionAll($query) : $query;

})->get();

// 將posts按照一對多的關係relation到users中
$relation = \App\Models\User::query()->getRelation('posts');
$relation->match(
    $relation->initRelation($users->all(), 'posts'),
    $posts, 'posts'
);

return $users;

sql如下

(
  select
    *
  from
    `posts`
  where
    `user_id` = 355
  limit
    3
)
union all
  (
    select
      *
    from
      `posts`
    where
      `user_id` = 234
    limit
      3
  )
union all
  (
    select
      *
    from
      `posts`
    where
      `user_id` = 232
    limit
      3
  )
...

上面的查詢有效的避免了PHP與MySQL之間的I/O耗時,並且UNION ALL可以有效的利用索引.

在50w條posts資料時,查詢平均耗時 0.002s,算是不錯的表現了. Explain如下

PLAN C

合理利用MySQL中的變數語法我們也可以實現這個需求,只需要用一個變數幫我們編碼一下即可

SELECT
    posts.*,
    @number := IF (@current_user_id = `user_id`, @number + 1, 1) AS number,
    @current_user_id := `user_id`
FROM
    (select * from `posts` where `posts`.`user_id` IN (572, 822, 911, 103, 234, 11, 999, 333, 121, 122) order by `posts`.`user_id` ASC) AS posts
HAVING
    number <= 3

簡單解析一下這個sql語句.

FORM 為一個子查詢,初步篩選出我們需要的作者的所有文章, 且正序排列後生成一個臨時表.

SELECT 為上面臨時表新增標號,新增的方式如下. (你需要從上往下一行行一行的觀察,與select的執行方式一致即可)

MySQL中呼叫未定義的變數,其值預設為null.

id user_id @current_user_id if判斷 @number
1 1 null false, number被賦值為1 1
2 1 1 true, @number = @number + 1 2
3 1 1 true, @number = @number + 1 3
4 1 1 true, @number = @number + 1 4
5 2 1 false, number被賦值為1 1
6 2 2 true, @number = @number + 1 2
.. .. .. .. ..

HAVING執行於SELECT之後,其再次篩選上面的臨時表,只取number <= 3的 行.

在執行效率方面,50萬posts資料時,查詢平均耗時在 0.112s,不算太差,也不算太好的一個表現. Explain如下

通過sql語句我們也可以預見. from子查詢的結果集的數量,會直接影響後面標號的效率. 在子查詢的結果集不多時,該查詢會有更好的表現.

PLAN C有一個額外的好處是,其有不錯的laravel擴充套件支援,能夠在不影響原有ORM操作的情況下實現該需求

staudenmeir/eloquent-eager-limit

PLAN D

我們也可以新增一張中間表來維持這種關聯關係,比如作者與其最新發表的文章部分.我們可以建立一張

user_latest_post:user_id,post_id, 然後在User模型中新增一條多對多的關聯關係

# User.php

public function latestPosts()
{
    return $this->belongsToMany(Post::class, 'user_latest_post')
}

使用

$users = \App\Models\User::with(['latestPosts'])->limit(10)->get();

該方案更加的符合ORM的關聯關係模型,查詢的效率上也較高. 但是需要增加對user_latest_post表的維護成本

總結來說上面四種方案都還算不錯,每種都有自己的優勢,可以根據自己的情況選擇合適的方案. 當然如果你有其他的解決方案,歡迎留言~

相關文章