18条优化Laravel 数据库查询的建议（4-6）

4. Count 查询使用query 而非 collection

计算数据库表的记录数，我们可能会这么做：

$posts = Post::all()->count(); //When using eloquent
$posts = DB::table('posts')->get()->count(); //When using query builder

这会产生以下查询

select * from posts

以上查询会先查询所有记录，将他们加载到collection对象中，再计算结果。如果记录少还好，随着表中记录增加，我们可能会耗尽内存。

因此，应该直接在数据库中计算总数：

$posts = Post::count(); //When using eloquent
$posts = DB::table('posts')->count(); //When using query builder

生成如下查询语句

select count(*) from posts

在sql中计算行数是慢处理过程，当数据库吉林市很多时表现不佳。所以需要尽量避免计算行数。

5. 通过渴求式加载避免N+1查询

你可能听过很多次这条建议。因此，长话短说。假设有以下场景：

class PostController extends Controller
{
    public function index()
    {
        $posts = Post::all();
        return view('posts.index', ['posts' => $posts ]);
    }
 }

// posts/index.blade.php file
 
@foreach($posts as $post)
    <li>
        <h3>{{ $post->title }}</h3>
        <p>Author: {{ $post->author->name }}</p>
    </li>
@endforeach

以上代码查询所有记录，在网页中显示标题和作者。假设你的post模型有一个关联模型author。执行以上代码会返回以下查询：

select * from posts // Assume this query returned 5 posts
select * from authors where id = { post1.author_id }
select * from authors where id = { post2.author_id }
select * from authors where id = { post3.author_id }
select * from authors where id = { post4.author_id }
select * from authors where id = { post5.author_id }

如你所见，我们用一个查询来查询posts还有5个查询用来查询authors(假设有 5个 posts)。因此每个post 会单独作出一个查询来查找作者。 

因此如果有N个post, 会产生N+1次查询（1次post, N次查post的作者author）。这就是常见的N+1查询问题。要避免这个，通过如下方式渴求式（eager load）加载：

$posts = Post::all(); // Avoid doing this
$posts = Post::with(['author'])->get(); // Do this instead

执行以上代码会生成以下查询

select * from posts // Assume this query returned 5 posts
select * from authors where id in( { post1.author_id }, { post2.author_id }, { post3.author_id }, { post4.author_id }, { post5.author_id } )

6. 渴求加载嵌入式关联模型Eager load nested relationship

接上例，假设author隶属于某个团队team, 你同时要显示团队名称，试图如下：

@foreach($posts as $post)
    <li>
        <h3>{{ $post->title }}</h3>
        <p>Author: {{ $post->author->name }}</p>
        <p>Author's Team: {{ $post->author->team->name }}</p>
    </li>
@endforeach

执行以下代码

$posts = Post::with(['author'])->get();

会产生以下查询：

select * from posts // Assume this query returned 5 posts
select * from authors where id in( { post1.author_id }, { post2.author_id }, { post3.author_id }, { post4.author_id }, { post5.author_id } )
select * from teams where id = { author1.team_id }
select * from teams where id = { author2.team_id }
select * from teams where id = { author3.team_id }
select * from teams where id = { author4.team_id }
select * from teams where id = { author5.team_id }

如你所见，即便你渴求式加载关联模型authors,仍然会在team上有多的查询。我们可以按照以下方式解决：

$posts = Post::with(['author.team'])->get();

执行以上代码生成查询语句：

select * from posts // Assume this query returned 5 posts
select * from authors where id in( { post1.author_id }, { post2.author_id }, { post3.author_id }, { post4.author_id }, { post5.author_id } )
select * from teams where id in( { author1.team_id }, { author2.team_id }, { author3.team_id }, { author4.team_id }, { author5.team_id } )

通过渴求式加载嵌入式关联模型，我们可以将查询总数从11减少到3。